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

base = Path(r"c:\Users\genge\OneDrive\Desktop\Projet\StageEnvie2e\ExportAccess")
all_files = [p.name for p in base.glob("*.csv")]
candidates = [f for f in all_files if any(k in f.lower() for k in ["salari", "contrat", "avenant", "poste", "classif", "diplom"]) ]
print("Found files:", candidates)

month_map = {
    "janv": 1, "jan": 1, "fevr": 2, "fev": 2, "fevrier": 2, "mars": 3, "avr": 4, "avril": 4,
    "mai": 5, "juin": 6, "juil": 7, "juillet": 7, "aout": 8, "sept": 9, "oct": 10, "nov": 11, "dec": 12, "decembre": 12
}

def strip_accents(text: str) -> str:
    normalized = unicodedata.normalize("NFKD", text)
    return "".join(char for char in normalized if not unicodedata.combining(char))

def parse_french_date(value: str):
    raw = value.strip()
    if not raw:
        return ""
    for fmt in ("%d/%m/%Y", "%d-%m-%Y", "%Y-%m-%d", "%d/%m/%y", "%d-%m-%y"):
        try:
            dt = datetime.strptime(raw, fmt)
            return dt.strftime("%Y-%m-%d 00:00:00")
        except Exception:
            pass
    m = re.fullmatch(r"(\d{1,2})-([^\d]+?)-(?:(\d{2})|(\d{4}))", raw, flags=re.IGNORECASE)
    if m:
        day = int(m.group(1))
        mon = strip_accents(m.group(2).lower()).replace('.', '').strip()
        year_text = m.group(3) or m.group(4)
        year = int(year_text)
        if year < 100:
            year += 2000
        month = None
        for key in (mon, mon[:4], mon[:3]):
            if key in month_map:
                month = month_map[key]
                break
        if month:
            return f"{year:04d}-{month:02d}-{day:02d} 00:00:00"
    return raw

def normalize_decimal(v: str):
    if v is None:
        return ""
    s = v.strip()
    if not s:
        return ""
    return s.replace(',', '.')

def normalize_bool(v: str):
    if v is None:
        return ""
    s = v.strip().upper()
    if s in ("TRUE", '1', 'O', 'OUI'):
        return '1'
    if s in ("FALSE", '0', 'N', 'NON'):
        return '0'
    return s

for fname in candidates:
    path = base / fname
    backup = path.with_suffix(path.suffix + '.bak')
    shutil.copy2(path, backup)
    print('Processing', fname)
    with path.open('r', encoding='utf-8-sig', newline='') as f:
        rows = list(csv.reader(f))
    if not rows:
        print(' empty, skip')
        continue
    header = rows[0]
    new_header = [strip_accents(h).replace(' ', '_').replace('-', '_') for h in header]
    output = [new_header]
    date_cols = [i for i,h in enumerate(new_header) if 'date' in h.lower() or h.lower() in ('dae','dme')]
    bool_cols = [i for i,h in enumerate(new_header) if h.lower() in ('smr','cotorep','souhaite_vaccination')]
    dec_cols = [i for i,h in enumerate(new_header) if any(k in h.lower() for k in ('txh','rembmens','smbe','salaire','mensual'))]
    for row in rows[1:]:
        if len(row) < len(new_header):
            row = row + ['']*(len(new_header)-len(row))
        elif len(row) > len(new_header):
            row = row[:len(new_header)]
        newrow = []
        for i,cell in enumerate(row):
            c = cell.strip()
            if i in date_cols:
                newrow.append(parse_french_date(c))
            elif i in dec_cols:
                newrow.append(normalize_decimal(c))
            elif i in bool_cols:
                newrow.append(normalize_bool(c))
            else:
                newrow.append(c)
        output.append(newrow)
    with path.open('w', encoding='utf-8-sig', newline='') as f:
        writer = csv.writer(f, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL, lineterminator='\n')
        writer.writerows(output)
    print(' Done', fname, 'backup at', backup)

print('All done')