#!/usr/bin/env python3
"""Régénère import_ready.sql pour le schéma aapvayrhhn_crm.clients"""
from __future__ import annotations

import json
import secrets
import time
from datetime import datetime, timezone
from pathlib import Path

DIR = Path(__file__).resolve().parent
JSON_PATH = DIR / "classified_all.json"
OUT_SQL = DIR / "import_ready.sql"
ADMIN_ID = "663acce29da39364"  # admin@online-trade.net du dump


def uid() -> str:
    return secrets.token_hex(8) + format(int(time.time()), "x")


def sql_escape(val: str) -> str:
    return (val or "").replace("\\", "\\\\").replace("'", "''")


def sql_str(val: str | None) -> str:
    if val is None or val == "":
        return "NULL"
    return f"'{sql_escape(str(val))}'"


def norm_phone(phone: str) -> str:
    return "".join(ch for ch in (phone or "") if ch.isdigit())


def display_phone(phone: str) -> str:
    p = (phone or "").strip()
    if not p:
        return ""
    if p.startswith("+"):
        return p
    digits = norm_phone(p)
    return f"+{digits}" if digits else p


def main() -> None:
    contacts = json.loads(JSON_PATH.read_text(encoding="utf-8"))
    lines = [
        "-- Import batch → table clients (schéma aapvayrhhn_crm)",
        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",
        "-- Base cible : aapvayrhhn_crm",
        "SET NAMES utf8mb4;",
        "",
    ]

    for c in contacts:
        phone_disp = display_phone(c.get("phone") or "")
        phone_norm = norm_phone(c.get("phone") or "")
        if not phone_norm:
            continue

        industry = c.get("industry") or c.get("type") or ""
        ftd_stage = c.get("ftd_stage") or ""
        ftd = 1 if ftd_stage else 0
        tags_json = json.dumps(c.get("tags") or [], ensure_ascii=False)
        lead_date = c.get("lead_date") or None
        # status: hot → hot si présent dans statuses, sinon nouveau
        status_temp = (c.get("status_temp") or "").lower()
        status = "hot" if status_temp == "hot" else "nouveau"
        # product = type métier
        product = c.get("type") or industry

        cols = (
            "id, name, email, phone, company, address, status, source, product, budget, priority, notes, "
            "first_name, last_name, lead_date, industry, country, language, age_group, broker, "
            "deposit_amount, urgency, pain_point, objections, summary, next_step, security_code, "
            "ftd_stage, ftd, amount_lost, scam_type, case_status, bank_name, payment_method, "
            "file_name, tags, data_language, created_by, created_at, updated_at"
        )

        vals = ", ".join([
            sql_str(uid()),
            sql_str(c.get("name") or f"Lead {phone_disp}"),
            sql_str(c.get("email") or None),
            sql_str(phone_disp),
            "NULL",
            "NULL",
            sql_str(status),
            sql_str(c.get("source_file") or "batch_import"),
            sql_str(product or None),
            sql_str(c.get("budget") or None),
            sql_str(c.get("priority") or "moyenne"),
            sql_str(c.get("notes") or None),
            sql_str(c.get("first_name") or None),
            sql_str(c.get("last_name") or None),
            sql_str(lead_date),
            sql_str(industry or None),
            sql_str(c.get("country") or "France"),
            sql_str(c.get("language") or None),
            "NULL",
            sql_str(c.get("broker") or None),
            sql_str(c.get("deposit") or None),
            sql_str(c.get("urgency") or None),
            sql_str(c.get("pain_point") or None),
            sql_str(c.get("objections") or None),
            sql_str(c.get("summary") or None),
            sql_str(c.get("next_step") or None),
            sql_str(c.get("security_code") or None),
            sql_str(ftd_stage or None),
            str(ftd),
            sql_str(c.get("amount_lost") or None),
            sql_str(c.get("scam_type") or None),
            sql_str(c.get("case_status") or None),
            sql_str(c.get("bank") or None),
            sql_str(c.get("payment") or None),
            sql_str(c.get("source_file") or None),
            sql_str(tags_json),
            sql_str("fr"),
            sql_str(ADMIN_ID),
            "NOW()",
            "NOW()",
        ])

        lines.append(
            f"INSERT INTO clients ({cols})\n"
            f"SELECT {vals}\n"
            f"FROM DUAL\n"
            f"WHERE NOT EXISTS (\n"
            f"  SELECT 1 FROM clients\n"
            f"  WHERE REPLACE(REPLACE(REPLACE(IFNULL(phone,''), '+', ''), ' ', ''), '-', '') = '{sql_escape(phone_norm)}'\n"
            f"  LIMIT 1\n"
            f");\n"
        )

    OUT_SQL.write_text("\n".join(lines), encoding="utf-8")
    print(f"OK {len(contacts)} -> {OUT_SQL}")


if __name__ == "__main__":
    main()
