# -----------------------------------------------------------------------------
# Immo-Ka — Agrégateur de maisons à vendre (province de Québec)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# mortgage/store.py : persistance des taux hypothécaires (data/mortgage.db).
#   Historisation par périodes de validité : une ligne « courante » par produit
#   (valid_to IS NULL) ; un changement de taux ferme la ligne et en ouvre une
#   nouvelle. Aucune donnée n'est jamais écrasée — l'historique complet se
#   reconstruit par produit. Santé des providers dans provider_runs.
# -----------------------------------------------------------------------------
from __future__ import annotations

import hashlib
import json
import sqlite3
import statistics
import time
from pathlib import Path

DB_PATH = Path(__file__).resolve().parent.parent.parent / "data" / "mortgage.db"

# Un taux « courant » plus vieux que STALE_H heures est signalé périmé.
STALE_H = 24
# Rejet des sauts aberrants : variation > JUMP_MAX points en < JUMP_WINDOW_H h.
JUMP_MAX = 2.5
JUMP_WINDOW_H = 48

_SCHEMA = """
CREATE TABLE IF NOT EXISTS rate_observations (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    provider       TEXT NOT NULL,
    institution    TEXT NOT NULL,
    product_key    TEXT NOT NULL,      -- empreinte identité produit
    product_name   TEXT,
    rate_type      TEXT NOT NULL,      -- fixed|variable|adjustable|other
    term_months    INTEGER NOT NULL,
    kind           TEXT NOT NULL,      -- posted|special
    rate           REAL NOT NULL,      -- taux contractuel en % (ex. 4.19)
    apr            REAL,
    insured_status TEXT DEFAULT 'unknown',  -- insured|insurable|uninsured|unknown
    purpose        TEXT DEFAULT 'purchase', -- purchase|renewal|refinance|unknown
    amortization_max_years INTEGER,
    conditions     TEXT,
    source_url     TEXT,
    confidence     REAL DEFAULT 1.0,
    raw            TEXT,               -- JSON : observation brute (audit)
    valid_from     REAL NOT NULL,
    valid_to       REAL,               -- NULL = taux courant
    last_checked   REAL NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_rateobs_current
    ON rate_observations(provider, product_key, valid_to);
CREATE INDEX IF NOT EXISTS idx_rateobs_lookup
    ON rate_observations(rate_type, term_months, valid_to);

CREATE TABLE IF NOT EXISTS provider_runs (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    provider    TEXT NOT NULL,
    ts          REAL NOT NULL,
    ok          INTEGER,
    status      TEXT,     -- success|http_error|parser_error|validation_error|empty
    products    INTEGER,
    changed     INTEGER,
    rejected    INTEGER,
    duration_ms INTEGER,
    message     TEXT
);
CREATE INDEX IF NOT EXISTS idx_provider_runs ON provider_runs(provider, ts);

-- Préparé pour les notifications futures (« alerte-moi si le 5 ans fixe
-- passe sous 3,99 % » / « si le paiement de cette propriété passe sous X $ »).
CREATE TABLE IF NOT EXISTS rate_alerts (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    created   REAL NOT NULL,
    kind      TEXT NOT NULL,   -- rate_below|payment_below
    params    TEXT,            -- JSON : {rate_type, term_months, uid, down…}
    threshold REAL NOT NULL,
    contact   TEXT,
    active    INTEGER DEFAULT 1,
    fired_at  REAL
);
"""

_SCHEMA_READY = False


def connect() -> sqlite3.Connection:
    global _SCHEMA_READY
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    con = sqlite3.connect(DB_PATH, timeout=60)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA journal_mode=WAL")
    con.execute("PRAGMA synchronous=NORMAL")
    con.execute("PRAGMA busy_timeout=120000")
    if not _SCHEMA_READY:
        con.executescript(_SCHEMA)
        con.commit()
        _SCHEMA_READY = True
    return con


def product_key(p: dict) -> str:
    """Identité stable d'un produit : institution + type + terme + nature du
    taux + assurabilité + objet + nom. Deux produits incompatibles ne
    partagent jamais la même clé (règle : ne jamais comparer l'incomparable)."""
    ident = "|".join([
        p["provider"], p["rate_type"], str(p["term_months"]), p["kind"],
        p.get("insured_status") or "unknown", p.get("purpose") or "purchase",
        (p.get("product_name") or "").strip().lower(),
    ])
    return hashlib.sha1(ident.encode()).hexdigest()[:16]


def record_observations(con: sqlite3.Connection, provider: str,
                        products: list[dict]) -> dict:
    """Enregistre une passe de collecte validée.

    Pour chaque produit : si le taux courant est identique → simple mise à
    jour de last_checked ; s'il a changé → fermeture de la période et
    insertion d'une nouvelle ligne. Rejette les sauts aberrants (> JUMP_MAX
    points en < JUMP_WINDOW_H h) sans écraser la bonne donnée précédente.
    Retourne {seen, changed, rejected, rejected_details}."""
    now = time.time()
    changed = 0
    rejected: list[str] = []
    for p in products:
        pk = product_key(p)
        cur = con.execute(
            "SELECT id, rate, last_checked FROM rate_observations "
            "WHERE provider=? AND product_key=? AND valid_to IS NULL",
            (provider, pk)).fetchone()
        if cur is not None:
            if abs(cur["rate"] - p["rate"]) < 1e-9:
                con.execute(
                    "UPDATE rate_observations SET last_checked=?, apr=?, "
                    "conditions=?, source_url=? WHERE id=?",
                    (now, p.get("apr"), p.get("conditions"),
                     p.get("source_url"), cur["id"]))
                continue
            # Garde-fou anti-aberration : ne jamais écraser une donnée saine
            # par un saut manifestement impossible.
            age_h = (now - (cur["last_checked"] or now)) / 3600.0
            if abs(cur["rate"] - p["rate"]) > JUMP_MAX and age_h < JUMP_WINDOW_H:
                rejected.append(
                    f"{p.get('product_name') or pk}: {cur['rate']} -> "
                    f"{p['rate']} (saut aberrant)")
                continue
            con.execute("UPDATE rate_observations SET valid_to=? WHERE id=?",
                        (now, cur["id"]))
            changed += 1
        con.execute(
            "INSERT INTO rate_observations (provider, institution, "
            "product_key, product_name, rate_type, term_months, kind, rate, "
            "apr, insured_status, purpose, amortization_max_years, "
            "conditions, source_url, confidence, raw, valid_from, "
            "valid_to, last_checked) "
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL,?)",
            (provider, p["institution"], pk, p.get("product_name"),
             p["rate_type"], p["term_months"], p["kind"], p["rate"],
             p.get("apr"), p.get("insured_status") or "unknown",
             p.get("purpose") or "purchase", p.get("amortization_max_years"),
             p.get("conditions"), p.get("source_url"),
             p.get("confidence", 1.0),
             json.dumps(p.get("raw"), ensure_ascii=False) if p.get("raw") else None,
             now, now))
    con.commit()
    return {"seen": len(products), "changed": changed,
            "rejected": len(rejected), "rejected_details": rejected}


def _row(r: sqlite3.Row) -> dict:
    d = dict(r)
    d.pop("raw", None)
    now = time.time()
    d["age_minutes"] = round((now - (d.get("last_checked") or now)) / 60)
    d["stale"] = d["age_minutes"] > STALE_H * 60
    return d


def current_rates(con: sqlite3.Connection, rate_type: str | None = None,
                  term_months: int | None = None, kind: str | None = None,
                  insured_status: str | None = None,
                  purpose: str | None = None,
                  provider: str | None = None) -> list[dict]:
    """Taux courants (dernière donnée valide par produit), filtrables."""
    q = ("SELECT * FROM rate_observations WHERE valid_to IS NULL")
    args: list = []
    for col, val in (("rate_type", rate_type), ("term_months", term_months),
                     ("kind", kind), ("provider", provider),
                     ("purpose", purpose)):
        if val is not None:
            q += f" AND {col}=?"
            args.append(val)
    if insured_status is not None:
        q += " AND insured_status IN (?, 'unknown')"
        args.append(insured_status)
    q += " ORDER BY provider, term_months, rate"
    return [_row(r) for r in con.execute(q, args).fetchall()]


def best_rate(con: sqlite3.Connection, rate_type: str = "fixed",
              term_months: int = 60, insured_status: str | None = None,
              purpose: str = "purchase") -> dict | None:
    """Meilleur taux courant pour un produit comparable (privilégie les taux
    « special », sinon posted). Retourne la ligne complète + contexte."""
    rows = current_rates(con, rate_type=rate_type, term_months=term_months,
                         insured_status=insured_status, purpose=purpose)
    if not rows:
        return None
    # Un seul candidat par institution : special prioritaire, sinon posted.
    by_inst: dict[str, dict] = {}
    for r in rows:
        cur = by_inst.get(r["provider"])
        if cur is None:
            by_inst[r["provider"]] = r
        elif r["kind"] == "special" and cur["kind"] == "posted":
            by_inst[r["provider"]] = r
        elif r["kind"] == cur["kind"] and r["rate"] < cur["rate"]:
            by_inst[r["provider"]] = r
    candidates = sorted(by_inst.values(), key=lambda r: r["rate"])
    best = candidates[0]
    rates = [r["rate"] for r in candidates]
    return {
        **best,
        "median_rate": round(statistics.median(rates), 2) if rates else None,
        "institutions_count": len(candidates),
        "per_institution": candidates,
    }


def history(con: sqlite3.Connection, rate_type: str, term_months: int,
            provider: str | None = None, kind: str | None = None,
            days: int = 365) -> list[dict]:
    """Historique : périodes de validité (valid_from/valid_to) par produit."""
    since = time.time() - days * 86400
    q = ("SELECT provider, institution, product_name, kind, rate, "
         "insured_status, valid_from, valid_to, last_checked, source_url "
         "FROM rate_observations WHERE rate_type=? AND term_months=? "
         "AND (valid_to IS NULL OR valid_to >= ?)")
    args: list = [rate_type, term_months, since]
    if provider:
        q += " AND provider=?"
        args.append(provider)
    if kind:
        q += " AND kind=?"
        args.append(kind)
    q += " ORDER BY provider, valid_from"
    return [dict(r) for r in con.execute(q, args).fetchall()]


def rate_at(con: sqlite3.Connection, rate_type: str, term_months: int,
            ts: float, kind: str = "special") -> float | None:
    """Meilleur taux observé (toutes institutions) à un instant donné."""
    rows = con.execute(
        "SELECT MIN(rate) AS r FROM rate_observations "
        "WHERE rate_type=? AND term_months=? AND kind=? AND valid_from<=? "
        "AND (valid_to IS NULL OR valid_to>?) AND last_checked>=?",
        (rate_type, term_months, kind, ts, ts, ts - 14 * 86400)).fetchone()
    if rows is None or rows["r"] is None:
        # repli : posted si aucun special à cette date
        rows = con.execute(
            "SELECT MIN(rate) AS r FROM rate_observations "
            "WHERE rate_type=? AND term_months=? AND valid_from<=? "
            "AND (valid_to IS NULL OR valid_to>?)",
            (rate_type, term_months, ts, ts)).fetchone()
    return rows["r"] if rows else None


def market_stats(con: sqlite3.Connection, rate_type: str = "fixed",
                 term_months: int = 60) -> dict | None:
    """Métriques Mortgage Intelligence : meilleur, médian, spread,
    variations 7/30/90 jours, plus bas observé 6 mois."""
    best = best_rate(con, rate_type=rate_type, term_months=term_months)
    if best is None:
        return None
    now = time.time()
    out = {
        "rate_type": rate_type,
        "term_months": term_months,
        "best": best["rate"],
        "best_provider": best["provider"],
        "best_institution": best["institution"],
        "best_kind": best["kind"],
        "median": best["median_rate"],
        "spread": (round(best["median_rate"] - best["rate"], 2)
                   if best["median_rate"] is not None else None),
        "institutions_count": best["institutions_count"],
    }
    for label, days in (("var_7d", 7), ("var_30d", 30), ("var_90d", 90)):
        past = rate_at(con, rate_type, term_months, now - days * 86400)
        out[label] = round(best["rate"] - past, 2) if past is not None else None
    low = con.execute(
        "SELECT MIN(rate) AS r FROM rate_observations "
        "WHERE rate_type=? AND term_months=? AND kind='special' "
        "AND last_checked >= ?",
        (rate_type, term_months, now - 182 * 86400)).fetchone()
    out["lowest_6m"] = low["r"] if low and low["r"] is not None else None
    return out


def log_run(con: sqlite3.Connection, provider: str, ok: bool, status: str,
            products: int = 0, changed: int = 0, rejected: int = 0,
            duration_ms: int = 0, message: str = "") -> None:
    con.execute(
        "INSERT INTO provider_runs (provider, ts, ok, status, products, "
        "changed, rejected, duration_ms, message) VALUES (?,?,?,?,?,?,?,?,?)",
        (provider, time.time(), 1 if ok else 0, status, products, changed,
         rejected, duration_ms, message[:500]))
    con.commit()


def provider_health(con: sqlite3.Connection) -> list[dict]:
    """Dernier état de chaque provider : OK / WARNING / ERROR + fraîcheur."""
    rows = con.execute(
        "SELECT p.* FROM provider_runs p JOIN (SELECT provider, MAX(ts) AS m "
        "FROM provider_runs GROUP BY provider) x "
        "ON p.provider=x.provider AND p.ts=x.m ORDER BY p.provider").fetchall()
    now = time.time()
    out = []
    for r in rows:
        d = dict(r)
        n_current = con.execute(
            "SELECT COUNT(*) AS n, MAX(last_checked) AS mc "
            "FROM rate_observations WHERE provider=? AND valid_to IS NULL",
            (r["provider"],)).fetchone()
        age_min = round((now - r["ts"]) / 60)
        if r["ok"] and n_current["n"] > 0:
            level = "WARNING" if age_min > STALE_H * 60 or r["rejected"] else "OK"
        elif n_current["n"] > 0:
            level = "WARNING"   # échec récent mais données valides conservées
        else:
            level = "ERROR"
        d.update({"level": level, "age_minutes": age_min,
                  "current_products": n_current["n"],
                  "last_data_at": n_current["mc"]})
        out.append(d)
    return out