"""Complete domain_environments.responsable_email / referent_email depuis contacts.

Match sur le nom (contacts.name ≈ responsable_nom ou referent_nom).

Usage:
    python tools/fill_emails_from_contacts.py [--dry-run]
"""
import os
import argparse
import unicodedata
from sqlalchemy import create_engine, text

DATABASE_URL = os.getenv("DATABASE_URL_DEMO") or os.getenv("DATABASE_URL") \
    or "postgresql://patchcenter:PatchCenter2026!@localhost:5432/patchcenter_demo"


def normalize(s):
    """Normalise un nom: ASCII lowercase sans accent ni espaces multiples."""
    if not s:
        return ""
    nfkd = unicodedata.normalize("NFKD", s.strip().lower())
    ascii_str = "".join(c for c in nfkd if not unicodedata.combining(c))
    return " ".join(ascii_str.split())


def synth_email(name):
    """Genere prenom.nom@sanef.com a partir d'un nom complet.
    'Frédéric GRAFFAGNINO' -> 'frederic.graffagnino@sanef.com'
    'Pierre-Louis THOUVENOT' -> 'pierre-louis.thouvenot@sanef.com'
    """
    if not name:
        return None
    norm = normalize(name)
    parts = [p for p in norm.split() if p]
    if len(parts) < 2:
        return None
    first = parts[0]
    last = parts[-1]
    # Nettoie caracteres parasites (garde lettres + tiret + apostrophe)
    import re
    first = re.sub(r"[^a-z\-']", "", first)
    last = re.sub(r"[^a-z\-']", "", last)
    if not first or not last:
        return None
    return f"{first}.{last}@sanef.com"


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--dry-run", action="store_true")
    parser.add_argument("--overwrite", action="store_true",
                        help="Ecrase les emails existants (defaut: ne remplit que les vides)")
    args = parser.parse_args()

    engine = create_engine(DATABASE_URL)
    print(f"[INFO] DB: {DATABASE_URL.split('@')[-1]}")
    conn = engine.connect().execution_options(isolation_level="AUTOCOMMIT")

    # Cache contacts: normalized name -> email
    contacts = conn.execute(text(
        "SELECT name, email FROM contacts WHERE email IS NOT NULL AND email != ''"
    )).fetchall()
    name_to_email = {}
    for c in contacts:
        key = normalize(c.name)
        if key and "no-email" not in (c.email or "").lower():
            name_to_email[key] = c.email
    print(f"[INFO] {len(name_to_email)} contacts avec email")

    # Domain_environments: si overwrite, on prend tous ceux avec un nom renseigne
    if args.overwrite:
        filter_clause = "WHERE responsable_nom IS NOT NULL OR referent_nom IS NOT NULL"
    else:
        filter_clause = ("WHERE (responsable_email IS NULL OR responsable_email = '' "
                         "OR referent_email IS NULL OR referent_email = '')")
    rows = conn.execute(text(f"""
        SELECT id, responsable_nom, responsable_email, referent_nom, referent_email
        FROM domain_environments
        {filter_clause}
    """)).fetchall()
    print(f"[INFO] {len(rows)} (dom,env) a completer")

    updated = 0
    from_contacts = synth = 0
    for r in rows:
        updates = {}
        # En mode overwrite: recalcule toujours. Sinon skip si email existe.
        if r.responsable_nom and (args.overwrite or not (r.responsable_email or "").strip()):
            key = normalize(r.responsable_nom)
            email = name_to_email.get(key)
            new_email = email or synth_email(r.responsable_nom)
            if new_email and new_email != (r.responsable_email or ""):
                updates["responsable_email"] = new_email
                if email:
                    from_contacts += 1
                else:
                    synth += 1
        if r.referent_nom and (args.overwrite or not (r.referent_email or "").strip()):
            key = normalize(r.referent_nom)
            email = name_to_email.get(key)
            new_email = email or synth_email(r.referent_nom)
            if new_email and new_email != (r.referent_email or ""):
                updates["referent_email"] = new_email
                if email:
                    from_contacts += 1
                else:
                    synth += 1

        if not updates:
            continue
        if args.dry_run:
            print(f"  DRY: de_id={r.id}  {updates}")
        else:
            sets = ", ".join(f"{k}=:{k}" for k in updates)
            params = dict(updates); params["id"] = r.id
            conn.execute(text(f"UPDATE domain_environments SET {sets} WHERE id=:id"), params)
        updated += 1

    conn.close()
    print(f"\n[DONE] Maj: {updated} | depuis contacts: {from_contacts} | "
          f"synthetises (prenom.nom@sanef.com): {synth}")


if __name__ == "__main__":
    main()