#!/usr/bin/env python3
import csv
import re
from pathlib import Path
from unicodedata import normalize

base = Path(r"c:\Users\genge\OneDrive\Desktop\Projet\StageEnvie2e\ExportAccess")
folders = sorted([d for d in base.iterdir() if d.is_dir()])

MONTHS = {
    "janv": "01", "février": "02", "mars": "03", "avril": "04",
    "mai": "05", "juin": "06", "juil": "07", "août": "08",
    "sept": "09", "octobre": "10", "novembre": "11", "décembre": "12",
    "février": "02", "avr": "04", "juil": "07", "août": "08", "sept": "09", "oct": "10", "nov": "11", "déc": "12"
}

def strip_accents(s):
    return "".join(c for c in normalize("NFD", s) if c.isascii() or c in "éèêëàâäùûüôöçœæ")

def parse_date(date_str):
    if not date_str or str(date_str).strip() == "":
        return ""
    date_str = str(date_str).strip()
    match = re.search(r"(\d{1,2})-([^\d]+?)-(?:(\d{2})|(\d{4}))", date_str)
    if match:
        day, month_str, year2, year4 = match.groups()
        month_str = month_str.lower().strip(".")
        month = MONTHS.get(month_str, "")
        if not month:
            for key, val in MONTHS.items():
                if month_str.startswith(key):
                    month = val
                    break
        if month and year2:
            year = f"20{year2}" if int(year2) < 50 else f"19{year2}"
        elif month and year4:
            year = year4
        else:
            return ""
        return f"{year}-{month}-{int(day):02d} 00:00:00"
    return ""

def normalize_value(col_name, value):
    if value == "" or value is None:
        return ""
    col_lower = col_name.lower()
    if isinstance(value, bool):
        return "1" if value else "0"
    value_str = str(value).strip().upper()
    if value_str in ("TRUE", "OUI", "T", "O", "1"):
        return "1"
    if value_str in ("FALSE", "NON", "F", "N", "0"):
        return "0"
    if "date" in col_lower:
        return parse_date(value)
    if isinstance(value, str) and "," in value and re.match(r"^\d+,\d+$", value):
        return value.replace(",", ".")
    return str(value)

print(f"Processing {len(folders)} folders...\n")

for folder in folders:
    csv_files = sorted(folder.glob("*.csv"))
    if not csv_files:
        print(f"📁 {folder.name}/ - no CSV files")
        continue
    
    print(f"📁 {folder.name}/")
    
    for csv_path in csv_files:
        backup_path = csv_path.with_suffix(".bak")
        if backup_path.exists():
            print(f"   ✓ {csv_path.name} → backup exists, skip")
            continue
        
        try:
            with csv_path.open("r", encoding="utf-8-sig") as f:
                reader = csv.reader(f, delimiter=",")
                rows = list(reader)
            
            if not rows:
                print(f"   ⚠ {csv_path.name} → empty file, skip")
                continue
            
            headers = rows[0]
            norm_headers = [strip_accents(h).replace(" ", "_").replace("-", "_").lower() for h in headers]
            
            normalized_rows = [norm_headers]
            for row in rows[1:]:
                normalized_row = [normalize_value(norm_headers[i] if i < len(norm_headers) else "", 
                                                   row[i] if i < len(row) else "") 
                                 for i in range(max(len(headers), len(row)))]
                normalized_rows.append(normalized_row)
            
            csv_path.rename(backup_path)
            with csv_path.open("w", encoding="utf-8-sig", newline="") as f:
                writer = csv.writer(f, delimiter=",", quotechar='"', quoting=csv.QUOTE_MINIMAL, lineterminator="\n")
                writer.writerows(normalized_rows)
            
            print(f"   ✓ {csv_path.name} → {len(normalized_rows)-1} data rows normalized")
        except Exception as e:
            print(f"   ✗ {csv_path.name} → ERROR: {e}")

print("\n✅ All CSV files normalized")