#!/usr/bin/env python3
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
"""Construit la base SQLite de ValoPlex (plex seulement) + stats provinciales plex.

units        : 393 867 plex (CUBF 1000, 2+ logements, rôle 2026) + estimations
               2021-2026, P10/P90 conformes.
transactions : 91 060+ ventes du domaine plex (avec nombre de portes).
market_index : indice mensuel $/m² des ventes de plex.
Sortie       : app/data/valoplex.db + app/src/data/stats.json
"""
import json
import os
import sqlite3

import numpy as np
import pandas as pd

QC = "/Users/simon-pierreboucher/Desktop/qc_house_eval"
BASE = "/Users/simon-pierreboucher/Desktop/valoplex"
DB = f"{BASE}/app/data/valoplex.db"
os.makedirs(os.path.dirname(DB), exist_ok=True)
if os.path.exists(DB):
    os.remove(DB)

con = sqlite3.connect(DB)
con.executescript("""
PRAGMA journal_mode=OFF; PRAGMA synchronous=OFF; PRAGMA temp_store=MEMORY;
CREATE TABLE units (
  id_provinc TEXT PRIMARY KEY, adresse TEXT, apt TEXT, municipalite TEXT,
  arrond TEXT, code_mun TEXT, lat REAL, lng REAL, cubf INTEGER,
  cubf_libelle TEXT, type_prop TEXT, annee_construction INTEGER,
  annee_estimee TEXT, aire_etages_m2 REAL, superficie_terrain_m2 REAL,
  front_terrain_m REAL, nb_etages REAL, nb_logements INTEGER,
  nb_locaux_non_resid INTEGER, nb_chambres_locatives INTEGER,
  lien_physique TEXT, genre_construction TEXT,
  matricule TEXT, unite_voisinage TEXT, n_adresses INTEGER,
  dat_cond_marche TEXT,
  valeur_terrain REAL, valeur_batiment REAL, valeur_role REAL,
  valeur_anterieure REAL, adresses_json TEXT,
  est_2021 REAL, est_2022 REAL, est_2023 REAL, est_2024 REAL,
  est_2025 REAL, est_2026 REAL, p10 REAL, p90 REAL, est_hedo REAL
);
CREATE TABLE transactions (
  id TEXT PRIMARY KEY, date TEXT, amount REAL, street TEXT, city TEXT,
  lat REAL, lng REAL, property_type TEXT, year_built INTEGER,
  floor_area REAL, building_type TEXT, id_provinc TEXT, valeur_role REAL,
  land_area REAL, portes INTEGER
);
CREATE TABLE market_index (
  month TEXT, type_prop TEXT, ppm2 REAL, idx REAL, n INTEGER,
  PRIMARY KEY (month, type_prop)
);
CREATE TABLE leads (
  id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT, unit_id TEXT,
  estimate REAL, created_at TEXT DEFAULT (datetime('now'))
);
""")

# ---- units : plex du rôle 2026 + estimations ----------------------------------
print("units plex...", flush=True)
role = pd.read_parquet(f"{QC}/data/parquet/role_2026.parquet")
cu = pd.to_numeric(role["rl0105a"], errors="coerce")
lg = pd.to_numeric(role["rl0311a"], errors="coerce")
role = role[(cu == 1000) & (lg >= 2)].reset_index(drop=True)

est = pd.read_parquet(f"{BASE}/data/estimations/plex_estimes_2026.parquet",
                      columns=["id_provinc", "municipalite", "valeur_role",
                               "valeur_estimee", "valeur_estimee_p10",
                               "valeur_estimee_p90"])
df = role.merge(est, on="id_provinc", how="inner")
for y in range(2021, 2026):
    e = pd.read_parquet(f"{BASE}/data/estimations/plex_estimes_{y}.parquet",
                        columns=["id_provinc", "valeur_estimee"]) \
        .rename(columns={"valeur_estimee": f"est_{y}"})
    df = df.merge(e, on="id_provinc", how="left")

# ---- adresses de chaque porte (table des adresses du rôle) --------------------
print("adresses par porte...", flush=True)
import pyogrio
gen_dom = pyogrio.read_dataframe(
    f"{QC}/data/Role2026_geopackage/Domaines de valeurs/CODE_GENERIQUE_ADRESSE.dbf",
    read_geometry=False)
gen_map = dict(zip(gen_dom.iloc[:, 0].astype(str).str.strip(),
                   gen_dom.iloc[:, 1].astype(str).str.strip().str.title()))
adr = pyogrio.read_dataframe(
    f"{QC}/data/Role2026_geopackage/Role_2026_2.gpkg",
    layer="b05v_adr_unite_evaln_2026",
    columns=["id_provinc", "no_seq_adr", "rl0101a", "rl0101c", "rl0101e",
             "rl0101g", "rl0101h", "rl0101i"],
    read_geometry=False, use_arrow=True)
plex_ids = set(df["id_provinc"])
adr = adr[adr["id_provinc"].isin(plex_ids)].copy()

def s_(c):
    return adr[c].fillna("").astype(str).str.strip()

civ = s_("rl0101a").str.lstrip("0")
civ_sup = s_("rl0101c").str.lstrip("0")
num = np.where((civ_sup != "") & (civ_sup != civ), civ + "-" + civ_sup, civ)
generique = s_("rl0101e").map(lambda c: gen_map.get(c, c))
voie = (generique + " " + s_("rl0101g").str.title() + " " + s_("rl0101h")).str.strip()
apt = s_("rl0101i")
full = pd.Series(num, index=adr.index).str.cat(voie, sep=" ").str.strip()
full = np.where(apt != "", full + " app. " + apt, full)
adr["ligne"] = full
adr = adr.sort_values(["id_provinc", "no_seq_adr"])
adr_lists = adr.groupby("id_provinc")["ligne"].apply(
    lambda x: json.dumps(list(dict.fromkeys(x))[:24], ensure_ascii=False))
print(f"  {len(adr_lists)} plex avec adresses détaillées", flush=True)

lien_map = {"1": "Détaché", "2": "Jumelé", "3": "En rangée 1 côté",
            "4": "En rangée plus de 1 côté", "5": "Intégré"}
genre_map = {"1": "De plain-pied", "2": "À étage mansardé", "3": "À étages entiers",
             "4": "À niveaux décalés", "5": "Unimodulaire"}

out = pd.DataFrame({
    "id_provinc": df["id_provinc"], "adresse": df["adresse"], "apt": df["apt"],
    "municipalite": df["municipalite"], "arrond": df["arrond"],
    "code_mun": df["code_mun"], "lat": df["lat"], "lng": df["lng"],
    "cubf": pd.to_numeric(df["rl0105a"], errors="coerce"),
    "cubf_libelle": "Logement",
    "type_prop": "plex",
    "annee_construction": pd.to_numeric(df["rl0307a"], errors="coerce"),
    "annee_estimee": df["rl0307b"],
    "aire_etages_m2": pd.to_numeric(df["rl0308a"], errors="coerce"),
    "superficie_terrain_m2": pd.to_numeric(df["rl0302a"], errors="coerce"),
    "front_terrain_m": pd.to_numeric(df["rl0301a"], errors="coerce"),
    "nb_etages": pd.to_numeric(df["rl0306a"], errors="coerce"),
    "nb_logements": pd.to_numeric(df["rl0311a"], errors="coerce"),
    "nb_locaux_non_resid": pd.to_numeric(df["rl0312a"], errors="coerce"),
    "nb_chambres_locatives": pd.to_numeric(df["rl0313a"], errors="coerce"),
    "lien_physique": df["rl0309a"].astype(str).map(lien_map),
    "genre_construction": df["rl0310a"].astype(str).map(genre_map),
    "matricule": df["mat18"],
    "unite_voisinage": df["rl0106a"],
    "n_adresses": pd.to_numeric(df["n_adresses"], errors="coerce"),
    "dat_cond_marche": df["dat_cond_mrche"].astype(str).str[:10],
    "valeur_terrain": pd.to_numeric(df["rl0402a"], errors="coerce"),
    "valeur_batiment": pd.to_numeric(df["rl0403a"], errors="coerce"),
    "valeur_role": df["valeur_role"],
    "valeur_anterieure": pd.to_numeric(df["rl0405a"], errors="coerce"),
    "adresses_json": df["id_provinc"].map(adr_lists),
    "est_2021": df["est_2021"], "est_2022": df["est_2022"],
    "est_2023": df["est_2023"], "est_2024": df["est_2024"],
    "est_2025": df["est_2025"], "est_2026": df["valeur_estimee"],
    "p10": df["valeur_estimee_p10"], "p90": df["valeur_estimee_p90"],
    "est_hedo": None,
})
print(f"écriture de {len(out)} plex...", flush=True)
out.to_sql("units", con, if_exists="append", index=False, chunksize=100_000)

# ---- transactions du domaine plex ---------------------------------------------
print("transactions plex...", flush=True)
tx = pd.read_parquet(f"{QC}/province_transactions_enrichi.parquet")
lgx = pd.to_numeric(tx["role_nb_logements"], errors="coerce")
cux = pd.to_numeric(tx["role_cubf"], errors="coerce")
good = (cux == 1000) & (lgx >= 2) & tx["role_id_provinc"].notna() & \
    (tx["match_valeur_exacte"] | (tx["match_dist_m"] <= 50))
p = tx[good]
txo = pd.DataFrame({
    "id": p["id"], "date": p["date"], "amount": p["amount"],
    "street": p["street"], "city": p["city"], "lat": p["lat"], "lng": p["lng"],
    "property_type": "plex",
    "year_built": pd.to_numeric(p["yearBuilt"], errors="coerce"),
    "floor_area": pd.to_numeric(p["floorArea"], errors="coerce"),
    "building_type": p["buildingType"],
    "id_provinc": p["role_id_provinc"],
    "valeur_role": pd.to_numeric(p["role_valeur_immeuble"], errors="coerce"),
    "land_area": pd.to_numeric(p["role_superficie_terrain_m2"], errors="coerce"),
    "portes": lgx[good].astype(int),
})
txo.to_sql("transactions", con, if_exists="append", index=False, chunksize=100_000)

# ---- indice de marché plex -----------------------------------------------------
print("market_index...", flush=True)
t = txo.dropna(subset=["floor_area"])
t = t[(t["floor_area"] > 40) & (t["amount"] > 60_000)]
t = t.assign(month=t["date"].str[:7], ppm2=t["amount"] / t["floor_area"])
gi = t.groupby("month").agg(ppm2=("ppm2", "median"), n=("ppm2", "size")).reset_index()
gi = gi[gi["n"] >= 5].sort_values("month")
gi["ppm2"] = gi["ppm2"].rolling(3, min_periods=1, center=True).median()
gi["idx"] = gi["ppm2"] / gi["ppm2"].iloc[-1]
gi["type_prop"] = "plex"
gi.to_sql("market_index", con, if_exists="append", index=False)

con.executescript("""
CREATE INDEX idx_units_geo ON units(lat, lng);
CREATE INDEX idx_units_mun ON units(municipalite);
CREATE INDEX idx_tx_geo ON transactions(lat, lng);
CREATE VIRTUAL TABLE units_fts USING fts5(
  adresse, municipalite, content='units', content_rowid='rowid',
  tokenize='unicode61 remove_diacritics 2');
INSERT INTO units_fts(rowid, adresse, municipalite)
  SELECT rowid, adresse, municipalite FROM units;
""")
con.commit()

# ---- stats plex provinciales ---------------------------------------------------
print("stats.json...", flush=True)
u = pd.read_sql("""SELECT municipalite, nb_logements, valeur_role,
                          aire_etages_m2, superficie_terrain_m2,
                          est_2021, est_2022, est_2023, est_2024, est_2025, est_2026
                   FROM units""", con)
years = [f"est_{y}" for y in range(2021, 2027)]
both = u[["est_2021", "est_2026"]].dropna()
growth = float(both["est_2026"].sum() / both["est_2021"].sum() - 1) * 100

bands = pd.cut(u["nb_logements"], [1, 2, 3, 5, 11, 10_000],
               labels=["Duplex (2)", "Triplex (3)", "4-5 portes",
                       "6-11 portes", "12+ portes"])
by_band = u.groupby(bands, observed=True).agg(
    n=("est_2026", "count"), total=("est_2026", "sum"),
    mediane=("est_2026", "median")).reset_index()
by_mun = u.groupby("municipalite").agg(
    n=("est_2026", "count"), total=("est_2026", "sum"),
    mediane=("est_2026", "median")).reset_index().dropna() \
    .sort_values("total", ascending=False)

stats = {
    "generated": "2026-08-09",
    "unites": int(len(u)),
    "valeur_totale_2026": float(u["est_2026"].sum()),
    "valeur_role_totale": float(u["valeur_role"].sum()),
    "valeur_mediane_2026": float(u["est_2026"].median()),
    "croissance_2021_2026_pct": round(growth, 1),
    "logements": int(u["nb_logements"].sum()),
    "aire_etages_km2": round(float(u["aire_etages_m2"].sum()) / 1e6, 1),
    "terrain_km2": round(float(u["superficie_terrain_m2"].sum()) / 1e6, 1),
    "municipalites": int(by_mun["municipalite"].nunique()),
    "totaux_annee": [
        {"year": int(c[4:]), "total": float(u[c].sum()),
         "n": int(u[c].notna().sum())} for c in years
    ],
    "par_type": [
        {"type": str(r.nb_logements), "n": int(r.n), "total": float(r.total),
         "mediane": float(r.mediane) if pd.notna(r.mediane) else None}
        for r in by_band.itertuples()
    ],
    "par_ville": [
        {"ville": r.municipalite, "n": int(r.n), "total": float(r.total),
         "mediane": float(r.mediane) if pd.notna(r.mediane) else None}
        for r in by_mun.head(200).itertuples()
    ],
}
os.makedirs(f"{BASE}/app/src/data", exist_ok=True)
with open(f"{BASE}/app/src/data/stats.json", "w") as f:
    json.dump(stats, f, ensure_ascii=False)

n_u = con.execute("SELECT COUNT(*) FROM units").fetchone()[0]
n_t = con.execute("SELECT COUNT(*) FROM transactions").fetchone()[0]
print(f"OK — units={n_u}, tx={n_t}")
print(f"VALEUR TOTALE PLEX QC 2026 : {stats['valeur_totale_2026']:,.0f} $ | portes: {stats['logements']:,}")
con.execute("VACUUM")
con.close()
print("DB_DONE")