# -----------------------------------------------------------------------------
# Lou-Ka — Agrégateur de logements à louer (province de Québec)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# scripts/merge_quartier.py : fusionne les bases de préparation
#   data/staging-{recensement,contexte,environnement}.db -> data/quartier.db
#   (la base statique servie en production ; voir louka/quartier.py)
# Usage : .venv/bin/python scripts/merge_quartier.py
# -----------------------------------------------------------------------------
from __future__ import annotations

import sqlite3
import sys
from pathlib import Path

DATA = Path(__file__).resolve().parent.parent / "data"
CIBLE = DATA / "quartier.db"

# staging -> tables attendues
SOURCES = {
    "staging-recensement.db": ["da_poly", "da_stats"],
    "staging-contexte.db": ["da_pmd", "da_defav", "ecoles"],
    "staging-environnement.db": ["heat", "crime_mtl", "igc"],
}


def main() -> None:
    if CIBLE.exists():
        CIBLE.unlink()
    out = sqlite3.connect(CIBLE)
    out.execute("CREATE TABLE meta (cle TEXT PRIMARY KEY, valeur TEXT)")

    for fichier, tables in SOURCES.items():
        chemin = DATA / fichier
        if not chemin.exists():
            print(f"⚠ {fichier} absent — tables {tables} sautées", file=sys.stderr)
            continue
        out.execute("ATTACH DATABASE ? AS src", (str(chemin),))
        for t in tables:
            existe = out.execute(
                "SELECT name FROM src.sqlite_master WHERE type='table' AND name=?",
                (t,)).fetchone()
            if not existe:
                print(f"⚠ table {t} absente de {fichier}", file=sys.stderr)
                continue
            schema = out.execute(
                "SELECT sql FROM src.sqlite_master WHERE type='table' AND name=?",
                (t,)).fetchone()[0]
            out.execute(schema)
            out.execute(f"INSERT INTO {t} SELECT * FROM src.{t}")
            n = out.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]
            print(f"✓ {t}: {n} lignes (depuis {fichier})")
        # préfixer les métadonnées de provenance
        if out.execute("SELECT name FROM src.sqlite_master WHERE name='meta'").fetchone():
            for cle, val in out.execute("SELECT cle, valeur FROM src.meta"):
                out.execute("INSERT OR REPLACE INTO meta VALUES (?,?)",
                            (f"{fichier.replace('.db', '')}:{cle}", val))
        out.commit()
        out.execute("DETACH DATABASE src")

    # scores PMD -> rangs centiles parmi les AD du Québec (l'échelle brute est
    # normalisée nationalement et écrase les valeurs urbaines : un Saint-Roch
    # très marchable afficherait 17/100 ; son centile québécois le rétablit)
    import bisect
    cols = [r[1] for r in out.execute("PRAGMA table_info(da_pmd)") if r[1] != "dauid"]
    out.execute("DROP TABLE IF EXISTS da_pmd_pct")
    out.execute("CREATE TABLE da_pmd_pct (dauid TEXT PRIMARY KEY, "
                + ", ".join(f"{c} INTEGER" for c in cols) + ")")
    lignes = out.execute(f"SELECT dauid, {', '.join(cols)} FROM da_pmd").fetchall()
    tries = {c: sorted(v for (v,) in
                       out.execute(f"SELECT {c} FROM da_pmd WHERE {c} IS NOT NULL"))
             for c in cols}
    q = (f"INSERT INTO da_pmd_pct VALUES (?{',?' * len(cols)})")
    for ligne in lignes:
        vals = [ligne[0]]
        for i, c in enumerate(cols):
            v = ligne[i + 1]
            t = tries[c]
            if v is None or len(t) < 2:
                vals.append(None)
            else:   # rang centile parmi les valeurs non nulles du Québec
                vals.append(round(100 * bisect.bisect_left(t, v) / (len(t) - 1)))
        out.execute(q, vals)
    print(f"✓ da_pmd_pct: {len(lignes)} lignes (rangs centiles QC, non-nuls)")

    # index utiles au runtime
    for idx in [
        "CREATE INDEX IF NOT EXISTS idx_poly_bbox ON da_poly(lat_min, lat_max)",
        "CREATE INDEX IF NOT EXISTS idx_crime_lat2 ON crime_mtl(lat)",
    ]:
        try:
            out.execute(idx)
        except sqlite3.Error as e:
            print(f"⚠ index: {e}", file=sys.stderr)
    out.commit()
    out.execute("VACUUM")
    out.close()
    print(f"→ {CIBLE} : {CIBLE.stat().st_size / 1e6:.1f} Mo")


if __name__ == "__main__":
    main()