#!/usr/bin/env python3
"""Réorganise un export Call Center (cc-client-export) pour aapvayrhhn_crm.clients."""
from __future__ import annotations

import csv
import json
import re
import secrets
import time
from datetime import datetime, timezone
from pathlib import Path

# Schéma cible : aapvayrhhn_crm.clients (dump aapvayrhhn_crm (1).sql)
SRC = Path(__file__).resolve().parent / "cc_export_1742852516005" / "cc-client-export-1742852516005 (1).csv"
OUT_DIR = Path(__file__).resolve().parent / "cc_export_1742852516005"
ADMIN_ID = "663acce29da39364"
SOURCE_FILE = "cc-client-export-1742852516005"

# Ordre exact des colonnes du dump CREATE TABLE `clients`
CLIENT_COLS = [
    "id", "name", "email", "phone", "company", "address", "status", "source",
    "product", "budget", "priority", "notes", "assigned_to", "created_by",
    "last_contact", "updated_at", "created_at", "first_name", "last_name",
    "lead_date", "industry", "country", "language", "age_group", "broker",
    "ftd", "deposit_amount", "interest", "pain_point", "decision_maker",
    "objections", "next_step", "callback_requested", "urgency", "scam_type",
    "scam_company", "amount_lost", "payment_method", "bank_name",
    "dispute_filed", "chargeback_status", "recovery_method", "case_status",
    "summary", "file_name", "security_code", "ftd_stage", "preferred_contact",
    "score", "recovery_amount", "followup_date", "tags", "data_language",
]


def uid() -> str:
    return secrets.token_hex(8) + format(int(time.time()), "x")


def sql_escape(v: str) -> str:
    # Échapper les retours à la ligne pour phpMyAdmin (une instruction = lignes SQL propres)
    return (
        (v or "")
        .replace("\\", "\\\\")
        .replace("'", "''")
        .replace("\r\n", "\\n")
        .replace("\n", "\\n")
        .replace("\r", "\\n")
    )


def sql_str(v: str | None) -> str:
    if v is None or str(v).strip() == "":
        return "NULL"
    return f"'{sql_escape(str(v).strip())}'"


def digits(phone: str) -> str:
    return "".join(ch for ch in (phone or "") if ch.isdigit())


def phone_plus(phone: str) -> str:
    d = digits(phone)
    if not d:
        return ""
    return d if phone.strip().startswith("+") else f"+{d}"


def clean(v) -> str:
    return (v or "").strip()


def map_priority(status: str) -> str:
    s = status.lower()
    if s in ("transaction", "deposit", "ftd", "funded"):
        return "haute"
    if s in ("verification", "callback", "active"):
        return "haute"
    if s in ("preparation", "prepare", "pending", "new"):
        return "moyenne"
    return "basse"


def map_type(dataset: str, client_type: str) -> str:
    blob = f"{dataset} {client_type}".lower()
    if re.search(r"(^|[^a-z])rec([^a-z]|$)|recover|récup|recup|_rec_", blob):
        return "recovery"
    if re.search(r"forex|ftd|deposit|dp unique|trading|mix|landing", blob):
        return "forex"
    if client_type and client_type.lower() not in ("no type", "notype", ""):
        return client_type.lower()
    return "forex"


def map_crm_status(status: str) -> str:
    # Tous les imports CC → statut CRM « nouveau »
    return "nouveau"


def lang_fr(code: str) -> str:
    c = (code or "").upper()
    return {"FRE": "Français", "FR": "Français", "ENG": "Anglais", "EN": "Anglais"}.get(c, code or "Français")


def gender_fr(g: str) -> str:
    g = (g or "").lower()
    if g == "male":
        return "Homme"
    if g == "female":
        return "Femme"
    return g or "—"


def parse_birthday(birthday: str):
    """Birthday CC format DD/MM/YYYY → date or None."""
    m = re.match(r"^(\d{1,2})/(\d{1,2})/(\d{4})$", (birthday or "").strip())
    if not m:
        return None
    d, mo, y = int(m.group(1)), int(m.group(2)), int(m.group(3))
    try:
        return datetime(y, mo, d).date()
    except ValueError:
        return None


def age_from_birthday(birthday: str) -> int | None:
    born = parse_birthday(birthday)
    if not born:
        return None
    today = datetime.now(timezone.utc).date()
    age = today.year - born.year - ((today.month, today.day) < (born.month, born.day))
    if age < 0 or age > 120:
        return None
    return age


def age_group_from_birthday(birthday: str) -> str | None:
    """Birthday CC format DD/MM/YYYY → tranche d'âge."""
    age = age_from_birthday(birthday)
    if age is None:
        return None
    if age < 35:
        return "18-34"
    if age < 45:
        return "35-44"
    if age < 55:
        return "45-54"
    return "Senior (55+)"


def build_notes(c: dict) -> str:
    addr_parts = [c.get("address") or "", c.get("zip") or "", c.get("city") or ""]
    adresse = ", ".join(p for p in addr_parts if p) or "-"
    # ASCII uniquement (évite les erreurs d'encodage phpMyAdmin)
    return "\n".join(
        [
            "=== FICHE CONTACT (export Call Center) ===",
            f"Source fichier : {SOURCE_FILE}.csv",
            f"Jeu de donnees : {c.get('dataset_name') or '-'}",
            f"Source dataset : {c.get('dataset_source') or '-'}",
            f"Statut client CC : {c.get('client_status') or '-'}",
            f"Priorite CRM : {c.get('priority')}",
            f"Type : {c.get('type')}",
            "",
            "--- Identite ---",
            f"Nom : {c.get('name')}",
            f"Telephone : {c.get('phone') or '-'}",
            f"E-mail : {c.get('email') or '-'}",
            f"Pays : {c.get('country') or 'France'}",
            f"Langue : {c.get('language') or '-'}",
            f"Genre : {c.get('gender_fr') or '-'}",
            f"Date de naissance : {c.get('birthday') or '-'}",
            f"Adresse : {adresse}",
            "",
            "--- References ---",
            f"Client ID (CC) : {c.get('cc_client_id') or '-'}",
            f"Agent ID (CC) : {c.get('cc_agent_id') or '-'}",
            f"Type client CC : {c.get('client_type') or '-'}",
        ]
    )


def main() -> None:
    OUT_DIR.mkdir(parents=True, exist_ok=True)
    with SRC.open(encoding="utf-8-sig", newline="") as f:
        rows = list(csv.DictReader(f))

    by_phone: dict[str, dict] = {}
    skipped = 0
    for r in rows:
        phone_raw = clean(r.get("Phone"))
        ph = digits(phone_raw)
        if not ph:
            skipped += 1
            continue
        first = clean(r.get("First Name"))
        last = clean(r.get("Last Name"))
        name = " ".join(p for p in (first, last) if p).strip() or f"Lead {phone_plus(phone_raw)}"
        status = clean(r.get("Client status"))
        dataset = clean(r.get("Dataset Name"))
        client_type = clean(r.get("Client Type"))
        country = clean(r.get("Country")) or "France"
        if country.upper() == "FR":
            country = "France"

        city = clean(r.get("City"))
        address = clean(r.get("Address"))
        zipc = clean(r.get("ZIP"))
        # Adresse postale seulement (sans le pays seul)
        addr_full = ", ".join(p for p in (address, zipc, city) if p)

        priority = map_priority(status)
        typ = map_type(dataset, client_type)
        # en cas de doublon : garder priorité la plus haute
        rank = {"haute": 3, "moyenne": 2, "basse": 1}
        contact = {
            "name": name,
            "first_name": first,
            "last_name": last,
            "phone": phone_plus(phone_raw),
            "email": clean(r.get("Email")),
            "country": country,
            "city": city,
            "address": address,
            "zip": zipc,
            "address_full": addr_full,
            "language": lang_fr(clean(r.get("Language"))),
            "birthday": clean(r.get("Birthday")),
            "gender_fr": gender_fr(clean(r.get("Gender"))),
            "client_status": status,
            "client_type": client_type,
            "dataset_name": dataset,
            "dataset_source": clean(r.get("Dataset Source Name")),
            "cc_client_id": clean(r.get("Client ID")),
            "cc_agent_id": clean(r.get("Agent ID")),
            "priority": priority,
            "type": typ,
            "industry": typ,
            "status": map_crm_status(status),
            "source_file": SOURCE_FILE + ".csv",
            "tags": [
                "callcenter",
                SOURCE_FILE,
                priority,
                typ,
                status.lower() if status else "unknown",
            ],
        }
        contact["notes"] = build_notes(contact)

        if ph in by_phone:
            if rank.get(priority, 0) > rank.get(by_phone[ph]["priority"], 0):
                # fusion tags
                old_tags = by_phone[ph].get("tags") or []
                contact["tags"] = sorted(set(old_tags + contact["tags"]))
                by_phone[ph] = contact
            else:
                by_phone[ph]["tags"] = sorted(set((by_phone[ph].get("tags") or []) + contact["tags"]))
        else:
            by_phone[ph] = contact

    contacts = list(by_phone.values())
    # tri par âge décroissant (plus âgé → plus jeune), sans date en dernier
    def sort_by_age(c: dict):
        age = age_from_birthday(c.get("birthday") or "")
        born = parse_birthday(c.get("birthday") or "")
        # age None → en bas ; sinon âge desc, puis date naissance asc, puis nom
        return (
            age is None,
            -(age if age is not None else 0),
            born or datetime.max.date(),
            (c.get("name") or "").lower(),
        )

    contacts.sort(key=sort_by_age)

    # JSON
    json_path = OUT_DIR / "classified.json"
    json_path.write_text(json.dumps(contacts, ensure_ascii=False, indent=2), encoding="utf-8")

    # résumé
    matrix: dict[str, dict[str, int]] = {}
    for c in contacts:
        matrix.setdefault(c["type"], {"haute": 0, "moyenne": 0, "basse": 0})
        matrix[c["type"]][c["priority"]] += 1
    status_counts: dict[str, int] = {}
    for c in contacts:
        status_counts[c["client_status"] or "?"] = status_counts.get(c["client_status"] or "?", 0) + 1

    md = [
        "# Export Call Center → CRM",
        f"Source : `{SRC.name}`",
        f"Généré : {datetime.now(timezone.utc).strftime('%Y-%m-%d %H:%M UTC')}",
        "",
        f"- Lignes brutes : **{len(rows)}**",
        f"- Sans téléphone : **{skipped}**",
        f"- Uniques (tél.) : **{len(contacts)}**",
        "",
        "## Matrice type × priorité",
        "",
        "| Type | Haute | Moyenne | Basse | Total |",
        "|------|------:|--------:|------:|------:|",
    ]
    for typ, counts in sorted(matrix.items()):
        tot = sum(counts.values())
        md.append(f"| {typ} | {counts['haute']} | {counts['moyenne']} | {counts['basse']} | {tot} |")
    md += ["", "## Statuts Call Center", ""]
    for k, v in sorted(status_counts.items(), key=lambda x: -x[1]):
        md.append(f"- **{k}** : {v}")
    (OUT_DIR / "rapport.md").write_text("\n".join(md) + "\n", encoding="utf-8")

    # CSV par priorité
    fields = [
        "name", "first_name", "last_name", "phone", "email", "country", "city",
        "priority", "type", "status", "client_status", "dataset_name", "birthday",
    ]
    for prio in ("haute", "moyenne", "basse"):
        subset = [c for c in contacts if c["priority"] == prio]
        path = OUT_DIR / f"export_{prio}.csv"
        with path.open("w", encoding="utf-8-sig", newline="") as f:
            w = csv.DictWriter(f, fieldnames=fields, extrasaction="ignore")
            w.writeheader()
            w.writerows(subset)

    # SQL clients — colonnes alignées sur dump aapvayrhhn_crm (1).sql
    cols_sql = ", ".join(f"`{c}`" for c in CLIENT_COLS)
    lines = [
        "-- Import Call Center → clients (aapvayrhhn_crm)",
        "-- Schema : aapvayrhhn_crm (1).sql — table clients (53 colonnes)",
        f"-- Généré le {datetime.now(timezone.utc).strftime('%Y-%m-%d %H:%M UTC')}",
        f"-- {len(contacts)} contacts uniques — dédup téléphone",
        f"-- Source : {SRC.name}",
        "SET NAMES utf8mb4;",
        "",
    ]
    for c in contacts:
        tags_json = json.dumps(c["tags"], ensure_ascii=False)
        age_g = age_group_from_birthday(c.get("birthday") or "")
        is_callback = 1 if c["status"] == "callback" else 0
        summary = (
            f"CC {c.get('client_status') or '-'} | {c['priority']} | {c['type']} | "
            f"{c.get('dataset_name') or '-'}"
        )
        # Valeurs dans l'ordre CLIENT_COLS
        row = {
            "id": sql_str(uid()),
            "name": sql_str(c["name"]),
            "email": sql_str(c["email"] or None),
            "phone": sql_str(c["phone"]),
            "company": "NULL",
            "address": sql_str(c.get("address_full") or None),
            "status": sql_str(c["status"]),
            "source": sql_str(SOURCE_FILE),
            "product": sql_str(c["type"]),
            "budget": "NULL",
            "priority": sql_str(c["priority"]),
            "notes": sql_str(c["notes"]),
            "assigned_to": "NULL",
            "created_by": sql_str(ADMIN_ID),
            "last_contact": "NULL",
            "updated_at": "NOW()",
            "created_at": "NOW()",
            "first_name": sql_str(c["first_name"] or None),
            "last_name": sql_str(c["last_name"] or None),
            "lead_date": "NULL",
            "industry": sql_str(c["industry"]),
            "country": sql_str(c["country"]),
            "language": sql_str(c["language"] or None),
            "age_group": sql_str(age_g),
            "broker": "NULL",
            "ftd": "0",
            "deposit_amount": "NULL",
            "interest": "NULL",
            "pain_point": "NULL",
            "decision_maker": "0",
            "objections": "NULL",
            "next_step": "NULL",
            "callback_requested": str(is_callback),
            "urgency": "NULL",
            "scam_type": "NULL",
            "scam_company": "NULL",
            "amount_lost": "NULL",
            "payment_method": "NULL",
            "bank_name": "NULL",
            "dispute_filed": "0",
            "chargeback_status": "NULL",
            "recovery_method": "NULL",
            "case_status": "NULL",
            "summary": sql_str(summary),
            "file_name": sql_str(c["source_file"]),
            "security_code": "NULL",
            "ftd_stage": "NULL",
            "preferred_contact": "NULL",
            "score": "NULL",
            "recovery_amount": "NULL",
            "followup_date": "NULL",
            "tags": sql_str(tags_json),
            "data_language": sql_str("fr"),
        }
        vals = ", ".join(row[col] for col in CLIENT_COLS)
        # INSERT VALUES simple (fiable sous phpMyAdmin)
        lines.append(f"INSERT INTO `clients` ({cols_sql}) VALUES ({vals});")
        lines.append("")

    sql_path = OUT_DIR / "import_ready.sql"
    sql_path.write_text("\n".join(lines), encoding="utf-8")
    # Copie aussi à la racine batch_import pour accès rapide
    (OUT_DIR.parent / "import_cc_clients.sql").write_text("\n".join(lines), encoding="utf-8")

    print(f"OK unique={len(contacts)} skipped={skipped}")
    print(f"SQL={sql_path}")
    print(f"SQL_COPY={OUT_DIR.parent / 'import_cc_clients.sql'}")
    for typ, counts in sorted(matrix.items()):
        print(f"  {typ}: H={counts['haute']} M={counts['moyenne']} B={counts['basse']}")


if __name__ == "__main__":
    main()
