#!/usr/bin/env python3
# =============================================================================
# Job·Ka — Groupe KA
# Auteur  : Simon-Pierre Boucher
# Contact : contact@spboucher.ai
# Fichier : scripts/backfill_phase2.py
# Rôle    : Backfill unique de la Phase 2 (enrichissement des connecteurs) —
#           réparations de données + peuplement des nouvelles colonnes.
#           Idempotent ; les valeurs recalculées ici sont IDENTIQUES à celles
#           que le pipeline produit désormais au sync (mêmes fonctions).
# Créé    : 2026-08-19   Modifié : 2026-08-19
# =============================================================================
"""Usage : .venv/bin/python scripts/backfill_phase2.py

Étapes (chacune consignée) :
 1. villes SuccessFactors cassées (« St ») : re-parse du slug d'URL ;
 2. dates Taleo/agnico_eagle : colonne = date LIMITE, pas publication ;
 3. salaires aberrants : re-normalisation (sanitize) puis re-extraction
    depuis la description, sinon remise à NULL ;
 4. HTML résiduel dans les descriptions : re-nettoyage (clean_html itératif) ;
 5. descriptions vides : purge du detail_cache -> re-fetch au prochain sync ;
 6. région administrative (17 régions, MAMH) depuis la ville ;
 7. langue de l'offre (heuristique) là où aucune valeur source ;
 8. avantages depuis les blocs structurés des descriptions ;
 9. titre d'affichage normalisé (title_clean) ;
10. apply_url depuis details.apply_url (njoyn) ;
11. quarantaine qualité (motifs JSON) sur toutes les offres actives.
"""
from __future__ import annotations

import json
import re
import sys
import urllib.parse
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent.parent))

from jobka import db  # noqa: E402
from jobka.normalize import (  # noqa: E402
    clean_html, clean_title, detect_language, extract_benefits,
    salary_from_text, salary_to_hourly, salary_to_yearly, sanitize_salary,
)
from jobka.quality import issues  # noqa: E402
from jobka.regions import region_for_city  # noqa: E402


def log(step: str, n: int) -> None:
    print(f"[backfill] {step}: {n}")


def main() -> None:
    con = db.connect()
    con.execute("PRAGMA busy_timeout=30000")

    # 1. villes/titres SuccessFactors (villes à trait d'union tronquées) --------
    from jobka.regions import city_from_slug_tokens
    n = 0
    for r in con.execute(
            "SELECT uid, url, city, title FROM jobs"
            " WHERE ats='successfactors'").fetchall():
        m = re.search(r"/job/([^/]+)/(\d+)/?$", r["url"])
        if not m:
            continue
        parts = [urllib.parse.unquote(p) for p in m.group(1).split("-")]
        city = city_from_slug_tokens(parts)
        if not city or len(city.split("-")) <= len((r["city"] or "").split("-")):
            continue      # ville actuelle déjà complète (ou détail plus précis)
        if (r["city"] or "") != city.split("-")[0]:
            continue      # la ville actuelle n'est pas le tronçon cassé attendu
        n_city = len(city.split("-"))
        title = " ".join(parts[n_city:-3])
        con.execute(
            "UPDATE jobs SET city=?, title=CASE WHEN ?<>'' THEN ? ELSE title END"
            " WHERE uid=?", (city, title, title, r["uid"]))
        n += 1
    log("villes SuccessFactors réparées", n)

    # 2. dates agnico_eagle (colonne = date limite) -----------------------------
    cur = con.execute(
        "UPDATE jobs SET date_deadline=date_posted, date_posted=NULL"
        " WHERE source='agnico_eagle' AND date_posted IS NOT NULL"
        " AND date_deadline IS NULL")
    log("dates agnico_eagle déplacées vers date_deadline", cur.rowcount)

    # 3. salaires aberrants ------------------------------------------------------
    n_fixed = n_cleared = 0
    for r in con.execute(
            """SELECT uid, salary_min, salary_max, salary_unit, salary_label,
                      salary_hour_min, salary_hour_max, salary_year_min,
                      salary_year_max, description FROM jobs
               WHERE salary_min IS NOT NULL AND (
                     salary_hour_min<14 OR salary_hour_max>250 OR
                     salary_year_min<20000 OR salary_year_max>600000 OR
                     salary_hour_min>250 OR salary_year_min>600000)""").fetchall():
        lo, hi, unit = sanitize_salary(r["salary_min"], r["salary_max"],
                                       r["salary_unit"])
        label = r["salary_label"] or ""
        if lo is None:
            lo, hi, unit, label = salary_from_text(r["description"] or "")
            lo, hi, unit = sanitize_salary(lo, hi, unit)
        if lo is not None:
            con.execute(
                """UPDATE jobs SET salary_min=?, salary_max=?, salary_unit=?,
                   salary_label=?, salary_year_min=?, salary_year_max=?,
                   salary_hour_min=?, salary_hour_max=? WHERE uid=?""",
                (lo, hi, unit, label,
                 salary_to_yearly(lo, unit), salary_to_yearly(hi, unit),
                 salary_to_hourly(lo, unit), salary_to_hourly(hi, unit),
                 r["uid"]))
            n_fixed += 1
        else:
            con.execute(
                """UPDATE jobs SET salary_min=NULL, salary_max=NULL,
                   salary_unit=NULL, salary_label='', salary_year_min=NULL,
                   salary_year_max=NULL, salary_hour_min=NULL,
                   salary_hour_max=NULL WHERE uid=?""", (r["uid"],))
            n_cleared += 1
    log("salaires aberrants re-normalisés", n_fixed)
    log("salaires aberrants remis à NULL", n_cleared)

    # 4. HTML résiduel -----------------------------------------------------------
    n = 0
    for r in con.execute(
            """SELECT uid, description FROM jobs WHERE description IS NOT NULL
               AND (description LIKE '%<p%' OR description LIKE '%<br%'
                    OR description LIKE '%<li%' OR description LIKE '%&nbsp;%'
                    OR description LIKE '%&amp;%' OR description LIKE '%&lt;%')
            """).fetchall():
        cleaned = clean_html(r["description"])
        if cleaned != r["description"]:
            con.execute("UPDATE jobs SET description=? WHERE uid=?",
                        (cleaned, r["uid"]))
            n += 1
    log("descriptions re-nettoyées (HTML résiduel)", n)

    # 5. descriptions vides : purge du detail_cache -> re-fetch au prochain sync
    n = 0
    for r in con.execute(
            """SELECT source, external_id FROM jobs WHERE active=1
               AND LENGTH(COALESCE(description,''))<50""").fetchall():
        cur = con.execute(
            "DELETE FROM detail_cache WHERE source=? AND external_id=?",
            (r["source"], r["external_id"]))
        n += cur.rowcount
    log("detail_cache purgé (descriptions vides, re-fetch au prochain sync)", n)

    # 6-10. région / langue / avantages / title_clean / apply_url ----------------
    n_region = n_lang = n_ben = n_title = n_apply = 0
    for r in con.execute(
            """SELECT uid, title, city, region, description, language,
                      benefits, details, apply_url FROM jobs""").fetchall():
        sets, args = [], []
        reg = region_for_city(r["city"] or "")
        if reg and reg != r["region"]:
            sets.append("region=?"); args.append(reg); n_region += 1
        if not r["language"]:
            lang = detect_language(r["title"] or "", r["description"] or "")
            if lang:
                sets.append("language=?"); args.append(lang); n_lang += 1
        if (r["benefits"] or "[]") in ("[]", ""):
            ben = extract_benefits(r["description"] or "")
            if ben:
                sets.append("benefits=?")
                args.append(json.dumps(ben, ensure_ascii=False))
                n_ben += 1
        tc = clean_title(r["title"] or "", r["city"] or "")
        if tc and tc != (r["title"] or ""):
            sets.append("title_clean=?"); args.append(tc); n_title += 1
        if not r["apply_url"]:
            try:
                details = json.loads(r["details"] or "{}")
            except ValueError:
                details = {}
            if details.get("apply_url"):
                sets.append("apply_url=?"); args.append(details["apply_url"])
                n_apply += 1
        if sets:
            con.execute(f"UPDATE jobs SET {', '.join(sets)} WHERE uid=?",
                        args + [r["uid"]])
    log("régions administratives attribuées", n_region)
    log("langues détectées", n_lang)
    log("avantages extraits", n_ben)
    log("titres d'affichage normalisés", n_title)
    log("apply_url repris de details", n_apply)

    # 11. quarantaine qualité (actives) ------------------------------------------
    n_q = n_ok = 0
    for r in con.execute(
            """SELECT uid, title, description, city, date_posted, date_deadline,
                      salary_hour_min, salary_hour_max, salary_year_min,
                      salary_year_max, salary_unit, quarantine FROM jobs
               WHERE active=1""").fetchall():
        defects = issues(
            title=r["title"] or "", description=r["description"] or "",
            city=r["city"] or "", date_posted=r["date_posted"],
            date_deadline=r["date_deadline"],
            salary_hour_min=r["salary_hour_min"],
            salary_hour_max=r["salary_hour_max"],
            salary_year_min=r["salary_year_min"],
            salary_year_max=r["salary_year_max"],
            salary_unit=r["salary_unit"])
        val = json.dumps(defects, ensure_ascii=False) if defects else None
        if val != r["quarantine"]:
            con.execute("UPDATE jobs SET quarantine=? WHERE uid=?",
                        (val, r["uid"]))
        if defects:
            n_q += 1
        else:
            n_ok += 1
    log("offres actives en quarantaine", n_q)
    log("offres actives publiables", n_ok)

    con.commit()
    con.close()
    print("[backfill] terminé.")


if __name__ == "__main__":
    main()