# ==============================================================================
# Author: Simon-Pierre Boucher <contact@spboucher.ai>
# File:   creaka/db.py
# Desc:   Stockage SQLite — fiches créateurs canoniques, comptes rattachés,
#         journal des synchronisations (fraîcheur §13-14, opt-out §15)
# ==============================================================================
"""Base de données Créa-Ka (SQLite, WAL).

- `creators` : une ligne = une fiche canonique (document JSON + colonnes
  indexées pour la recherche).
- `accounts` : un compte (plateforme+handle) pointe vers SA fiche — c'est
  l'index qui permet la déduplication inter-sources (§12.2).
- `sync_log` : statistiques par passage de connecteur (§16 logging structuré).

Politique de grâce (§13) : on ne supprime jamais brutalement — `last_seen`
horodate chaque passage ; un créateur disparu passe `inactive`, un opt-out
passe `opted_out` (masqué, jamais ré-agrégé).
"""
from __future__ import annotations

import json
import sqlite3
import sys
import time
from dataclasses import asdict
from datetime import datetime, timedelta, timezone
from pathlib import Path

from . import ethics
from .dedup import merge_creators
from .identity import needs_review
from .normalize import slugify
from .schema import Creator, PlatformAccount, now_iso

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

_SCHEMA = """
CREATE TABLE IF NOT EXISTS creators (
  id TEXT PRIMARY KEY,
  display_name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  is_minor INTEGER NOT NULL DEFAULT 0,
  region TEXT, city TEXT,
  niches TEXT, languages TEXT,
  creator_type TEXT, primary_platform TEXT,
  audience_tier TEXT, total_reach INTEGER,
  bio TEXT,
  first_seen TEXT, last_seen TEXT, updated_at TEXT,
  doc TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS accounts (
  platform TEXT NOT NULL,
  handle TEXT NOT NULL,
  creator_id TEXT NOT NULL REFERENCES creators(id) ON DELETE CASCADE,
  url TEXT NOT NULL,
  followers INTEGER,
  verified INTEGER,
  confidence REAL NOT NULL,
  signal TEXT,
  needs_review INTEGER NOT NULL DEFAULT 0,
  last_checked TEXT,
  metrics TEXT,
  PRIMARY KEY (platform, handle)
);
CREATE INDEX IF NOT EXISTS idx_accounts_creator ON accounts(creator_id);
CREATE INDEX IF NOT EXISTS idx_creators_status ON creators(status);
CREATE INDEX IF NOT EXISTS idx_creators_tier ON creators(audience_tier);
CREATE TABLE IF NOT EXISTS snapshots (
  day TEXT NOT NULL,
  platform TEXT NOT NULL,
  handle TEXT NOT NULL,
  creator_id TEXT NOT NULL,
  followers INTEGER,
  engagement REAL,
  PRIMARY KEY (day, platform, handle)
);
CREATE INDEX IF NOT EXISTS idx_snapshots_creator ON snapshots(creator_id, day);
CREATE TABLE IF NOT EXISTS sync_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  ts TEXT NOT NULL,
  source TEXT NOT NULL,
  creators INTEGER, accounts INTEGER, avg_confidence REAL,
  added INTEGER, updated INTEGER, errors INTEGER, seconds REAL,
  alert TEXT
);
"""


def connect(path: Path | str = DB_PATH) -> sqlite3.Connection:
    Path(path).parent.mkdir(parents=True, exist_ok=True)
    con = sqlite3.connect(path, check_same_thread=False)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA journal_mode=WAL")
    con.execute("PRAGMA foreign_keys=ON")
    con.executescript(_SCHEMA)
    # migrations légères (bases existantes)
    try:
        con.execute("ALTER TABLE accounts ADD COLUMN metrics TEXT")
    except sqlite3.OperationalError:
        pass
    return con


# --- (dé)sérialisation ----------------------------------------------------------

def creator_to_doc(cr: Creator) -> str:
    return json.dumps(asdict(cr), ensure_ascii=False)


def creator_from_row(row: sqlite3.Row) -> Creator:
    doc = json.loads(row["doc"])
    doc["platforms"] = [PlatformAccount(**a) for a in doc.get("platforms", [])]
    cr = Creator(**doc)
    return cr


def _public_dict(row: sqlite3.Row, accounts: list[sqlite3.Row]) -> dict:
    """Fiche publique (API) : comptes « à vérifier » EXCLUS (§12.1)."""
    doc = json.loads(row["doc"])
    doc["id"] = row["id"]
    doc["first_seen"] = row["first_seen"]
    doc["last_seen"] = row["last_seen"]
    doc["updated_at"] = row["updated_at"]
    doc["platforms"] = [
        {"platform": a["platform"], "handle": a["handle"], "url": a["url"],
         "followers": a["followers"],
         "verified": bool(a["verified"]) if a["verified"] is not None else None,
         "confidence": a["confidence"], "last_checked": a["last_checked"],
         "metrics": json.loads(a["metrics"]) if a["metrics"] else {}}
        for a in accounts if not a["needs_review"]]
    # champs internes / sensibles jamais exposés tels quels
    doc.pop("source", None)
    doc.pop("external_id", None)
    doc.pop("legal_name", None)
    return doc


# --- écriture -------------------------------------------------------------------

def _unique_id(con: sqlite3.Connection, base: str) -> str:
    cid, n = base, 2
    while con.execute("SELECT 1 FROM creators WHERE id=?", (cid,)).fetchone():
        cid = f"{base}-{n}"
        n += 1
    return cid


def _write_creator(con: sqlite3.Connection, cid: str, cr: Creator,
                   first_seen: str, ts: str) -> None:
    # comptes retirés de la fiche : jamais en silence (§13 — historique) ;
    # merge_accounts fait l'union, donc une perte ici est anormale → journalisée
    old_keys = {f"{r['platform']}:{r['handle']}" for r in con.execute(
        "SELECT platform, handle FROM accounts WHERE creator_id=?", (cid,))}
    lost = old_keys - {a.key for a in cr.platforms}
    if lost:
        print(f"[crea-ka] ⚠ {cid} : compte(s) retiré(s) de la fiche : "
              f"{', '.join(sorted(lost))}", file=sys.stderr)
    con.execute(
        """INSERT INTO creators (id, display_name, status, is_minor, region, city,
             niches, languages, creator_type, primary_platform, audience_tier,
             total_reach, bio, first_seen, last_seen, updated_at, doc)
           VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
           ON CONFLICT(id) DO UPDATE SET
             display_name=excluded.display_name, status=excluded.status,
             is_minor=excluded.is_minor, region=excluded.region, city=excluded.city,
             niches=excluded.niches, languages=excluded.languages,
             creator_type=excluded.creator_type,
             primary_platform=excluded.primary_platform,
             audience_tier=excluded.audience_tier, total_reach=excluded.total_reach,
             bio=excluded.bio, last_seen=excluded.last_seen,
             updated_at=excluded.updated_at, doc=excluded.doc""",
        (cid, cr.display_name, cr.status, int(cr.is_minor), cr.region, cr.city,
         ",".join(cr.niches), ",".join(cr.languages), cr.creator_type,
         cr.primary_platform, cr.audience_tier, cr.total_reach, cr.bio,
         first_seen, ts, ts, creator_to_doc(cr)))
    con.execute("DELETE FROM accounts WHERE creator_id=?", (cid,))
    for acc in cr.platforms:
        con.execute(
            """INSERT INTO accounts (platform, handle, creator_id, url, followers,
                 verified, confidence, signal, needs_review, last_checked, metrics)
               VALUES (?,?,?,?,?,?,?,?,?,?,?)
               ON CONFLICT(platform, handle) DO UPDATE SET
                 creator_id=excluded.creator_id, url=excluded.url,
                 followers=excluded.followers, verified=excluded.verified,
                 confidence=excluded.confidence, signal=excluded.signal,
                 needs_review=excluded.needs_review,
                 last_checked=excluded.last_checked, metrics=excluded.metrics""",
            (acc.platform, acc.handle, cid, acc.url, acc.followers,
             None if acc.verified is None else int(acc.verified),
             acc.confidence, acc.signal, int(needs_review(acc)), acc.last_checked,
             json.dumps(acc.metrics, ensure_ascii=False) if acc.metrics else None))
        # historisation (§13-14) : un point par jour et par compte — matière
        # première des courbes de croissance et des insights (7 j / 30 j)
        if acc.followers:
            con.execute(
                """INSERT INTO snapshots (day, platform, handle, creator_id,
                     followers, engagement)
                   VALUES (?,?,?,?,?,?)
                   ON CONFLICT(day, platform, handle) DO UPDATE SET
                     creator_id=excluded.creator_id,
                     followers=excluded.followers,
                     engagement=COALESCE(excluded.engagement,
                                         snapshots.engagement)""",
                (ts[:10], acc.platform, acc.handle, cid, acc.followers,
                 (acc.metrics or {}).get("engagement_rate_pct")))


def _find_existing(con: sqlite3.Connection, cr: Creator) -> str | None:
    """Fiche existante possédant déjà un des comptes entrants.

    Même plateforme + même handle = MÊME compte : la table accounts n'admet
    qu'un propriétaire par compte (UNIQUE platform,handle). Le rapprochement se
    fait donc sur l'identité exacte du compte, sans condition de confiance —
    exiger `confidence >= STRONG_THRESHOLD` des deux côtés (version d'avant le
    2026-08-20) faisait rater les fiches à signal faible (mention 0.60, ex.
    snowball-ig) : sync_source créait alors une SECONDE fiche et l'upsert des
    comptes lui réassignait le compte, laissant l'originale orpheline
    (691 doublons constatés). Ce n'est PAS une fusion sur le nom (§12.2) :
    l'égalité stricte plateforme+handle identifie le compte lui-même.
    """
    for acc in cr.platforms:
        row = con.execute(
            "SELECT creator_id FROM accounts WHERE platform=? AND handle=?",
            (acc.platform, acc.handle)).fetchone()
        if row:
            return row["creator_id"]
    return None


def sync_source(con: sqlite3.Connection, source_id: str,
                creators: list[Creator], *, started: float | None = None,
                errors: int = 0) -> dict:
    """Synchronise le lot d'une source : ajouts, fusions, mises à jour, opt-out.

    `started` (time.time() du début du passage) → durée réelle persistée dans
    sync_log.seconds ; `errors` → erreurs non bloquantes signalées par le
    connecteur (§16 logging structuré).
    """
    ts = now_iso()
    added = updated = skipped_optout = 0
    confs: list[float] = []
    for cr in creators:
        cr = ethics.scrub(cr)
        # opt-out : jamais ré-agrégé (§15) ; si une fiche existe, la masquer
        if ethics.is_opted_out(cr):
            skipped_optout += 1
            existing = _find_existing(con, cr)
            if existing:
                con.execute("UPDATE creators SET status='opted_out', updated_at=? "
                            "WHERE id=?", (ts, existing))
            continue
        confs.extend(a.confidence or 0 for a in cr.platforms)
        existing_id = _find_existing(con, cr)
        if existing_id:
            row = con.execute("SELECT * FROM creators WHERE id=?",
                              (existing_id,)).fetchone()
            current = creator_from_row(row)
            if current.status == "opted_out":
                continue  # masqué : on ne ré-agrège pas
            # _find_existing garantit un compte IDENTIQUE (plateforme+handle)
            # partagé : c'est le même compte, la fusion est toujours sûre —
            # pas une fusion sur le nom (§12.2)
            merged = merge_creators(current, cr)
            _write_creator(con, existing_id, merged, row["first_seen"], ts)
            updated += 1
            continue
        cid = _unique_id(con, slugify(cr.display_name))
        _write_creator(con, cid, cr, ts, ts)
        added += 1
    con.commit()
    n_acc = sum(len(c.platforms) for c in creators)
    stats = {"source": source_id, "creators": len(creators), "accounts": n_acc,
             "added": added, "updated": updated, "optout_skipped": skipped_optout,
             "avg_confidence": round(sum(confs) / len(confs), 3) if confs else None}
    alert = None
    if not creators:
        alert = "0 créateur retourné — source possiblement bloquée"
        stats["alert"] = alert
    seconds = round(time.time() - started, 1) if started else 0.0
    stats["seconds"] = seconds
    if errors:
        stats["errors"] = errors
    con.execute(
        "INSERT INTO sync_log (ts, source, creators, accounts, avg_confidence,"
        " added, updated, errors, seconds, alert) VALUES (?,?,?,?,?,?,?,?,?,?)",
        (ts, source_id, len(creators), n_acc, stats["avg_confidence"],
         added, updated, errors, seconds, alert))
    con.commit()
    return stats


def archive_missing(con: sqlite3.Connection, *, hours: int = 72) -> int:
    """Archivage des disparus (§13, politique de grâce).

    Un créateur qu'AUCUNE source de découverte n'a revu depuis ~3 passages
    complets (cadence quotidienne §14 → 72 h) passe `status='inactive'`.
    Réversible : dès qu'une source le revoit, la fusion (sync_source →
    _write_creator) réécrit son statut actif et son `last_seen`.
    Les opt-out ne sont JAMAIS touchés (status != 'active').
    """
    cutoff = (datetime.now(timezone.utc) - timedelta(hours=hours)) \
        .strftime("%Y-%m-%dT%H:%M:%SZ")
    ts = now_iso()
    cur = con.execute(
        "UPDATE creators SET status='inactive', updated_at=? "
        "WHERE status='active' AND last_seen IS NOT NULL AND last_seen<?",
        (ts, cutoff))
    con.commit()
    if cur.rowcount:
        print(f"[crea-ka] archivage §13 : {cur.rowcount} fiche(s) non revue(s) "
              f"depuis {hours} h → inactive (réversible au retour)")
    return cur.rowcount


def log_failure(con: sqlite3.Connection, source_id: str, message: str) -> None:
    con.execute(
        "INSERT INTO sync_log (ts, source, creators, accounts, errors, alert)"
        " VALUES (?,?,0,0,1,?)", (now_iso(), source_id, message[:500]))
    con.commit()


def apply_optout(con: sqlite3.Connection, *, name: str | None = None,
                 account: str | None = None) -> int:
    """Masque immédiatement les fiches correspondant à une demande de retrait."""
    ids: set[str] = set()
    if account and ":" in account:
        platform, handle = account.lower().split(":", 1)
        for row in con.execute(
                "SELECT creator_id FROM accounts WHERE platform=? AND handle=?",
                (platform, handle.lstrip("@"))):
            ids.add(row["creator_id"])
    if name:
        from .dedup import name_key
        for row in con.execute("SELECT id, display_name FROM creators"):
            if name_key(row["display_name"]) == name_key(name):
                ids.add(row["id"])
    ts = now_iso()
    for cid in ids:
        con.execute("UPDATE creators SET status='opted_out', updated_at=? WHERE id=?",
                    (ts, cid))
    con.commit()
    return len(ids)


# --- lecture (API) ----------------------------------------------------------------

_TIERS = ("nano", "micro", "macro", "mega")


def search(con: sqlite3.Connection, *, q: str = "", niche: str = "",
           region: str = "", langue: str = "", plateforme: str = "",
           tier: str = "", sort: str = "reach", limit: int = 60,
           offset: int = 0) -> dict:
    """Recherche annuaire : filtres niche/région/langue/plateforme/taille + texte.

    Les fiches `opted_out` sont TOUJOURS exclues ; les fiches de mineurs sont
    exclues de l'annuaire public (prudence extrême, §15).
    """
    where = ["status='active'", "is_minor=0"]
    params: list = []
    if q:
        where.append("(lower(display_name) LIKE ? OR lower(bio) LIKE ?)")
        needle = f"%{q.lower()}%"
        params += [needle, needle]
    if niche:
        where.append("(','||niches||',') LIKE ?")
        params.append(f"%,{niche},%")
    if region:
        where.append("region=?")
        params.append(region)
    if langue:
        where.append("(','||languages||',') LIKE ?")
        params.append(f"%,{langue},%")
    if tier in _TIERS:
        where.append("audience_tier=?")
        params.append(tier)
    if plateforme:
        where.append("id IN (SELECT creator_id FROM accounts WHERE platform=? "
                     "AND needs_review=0)")
        params.append(plateforme)
    order = {"reach": "total_reach IS NULL, total_reach DESC",
             "nom": "display_name COLLATE NOCASE ASC",
             "recent": "updated_at DESC"}.get(sort, "total_reach IS NULL, total_reach DESC")
    sql_where = " AND ".join(where)
    total = con.execute(f"SELECT COUNT(*) c FROM creators WHERE {sql_where}",
                        params).fetchone()["c"]
    rows = con.execute(
        f"SELECT * FROM creators WHERE {sql_where} ORDER BY {order} LIMIT ? OFFSET ?",
        params + [max(1, min(200, limit)), max(0, offset)]).fetchall()
    items = []
    for row in rows:
        accounts = con.execute(
            "SELECT * FROM accounts WHERE creator_id=? ORDER BY followers DESC",
            (row["id"],)).fetchall()
        items.append(_public_dict(row, accounts))
    return {"total": total, "count": len(items), "offset": offset, "items": items}


def follower_history(con: sqlite3.Connection, cid: str,
                     days: int = 90) -> dict:
    """Historique d'audience d'une fiche (snapshots quotidiens, §13-14).

    Retourne les séries par plateforme (jour → abonnés) + la série totale
    (somme des plateformes connues ce jour-là), prêtes pour une sparkline.
    """
    cutoff = (datetime.now(timezone.utc) - timedelta(days=days)) \
        .strftime("%Y-%m-%d")
    rows = con.execute(
        "SELECT day, platform, handle, followers FROM snapshots "
        "WHERE creator_id=? AND day>=? ORDER BY day", (cid, cutoff)).fetchall()
    by_platform: dict[str, dict[str, int]] = {}
    for r in rows:
        by_platform.setdefault(r["platform"], {})[r["day"]] = r["followers"]
    total: dict[str, int] = {}
    for series in by_platform.values():
        # report de la dernière valeur connue pour que la somme quotidienne
        # ne s'effondre pas quand une plateforme n'a pas de point ce jour-là
        last = None
        for day in sorted({d for s in by_platform.values() for d in s}):
            last = series.get(day, last)
            if last is not None:
                total[day] = total.get(day, 0) + last
    return {
        "platforms": {p: [{"day": d, "followers": f}
                          for d, f in sorted(s.items())]
                      for p, s in by_platform.items()},
        "total": [{"day": d, "followers": f} for d, f in sorted(total.items())],
    }


def get_creator(con: sqlite3.Connection, cid: str) -> dict | None:
    row = con.execute(
        "SELECT * FROM creators WHERE id=? AND status='active' AND is_minor=0",
        (cid,)).fetchone()
    if not row:
        return None
    accounts = con.execute(
        "SELECT * FROM accounts WHERE creator_id=? ORDER BY followers DESC",
        (row["id"],)).fetchall()
    return _public_dict(row, accounts)


def stats(con: sqlite3.Connection) -> dict:
    """Statistiques d'ensemble pour l'accueil de l'annuaire."""
    base = "FROM creators WHERE status='active' AND is_minor=0"
    n = con.execute(f"SELECT COUNT(*) c {base}").fetchone()["c"]
    n_acc = con.execute(
        "SELECT COUNT(*) c FROM accounts a JOIN creators c2 ON c2.id=a.creator_id"
        " WHERE a.needs_review=0 AND c2.status='active' AND c2.is_minor=0"
    ).fetchone()["c"]
    by_platform = {r["platform"]: r["c"] for r in con.execute(
        "SELECT a.platform, COUNT(*) c FROM accounts a JOIN creators c2 "
        "ON c2.id=a.creator_id WHERE a.needs_review=0 AND c2.status='active' "
        "AND c2.is_minor=0 GROUP BY a.platform ORDER BY c DESC")}
    by_tier = {r["audience_tier"]: r["c"] for r in con.execute(
        f"SELECT audience_tier, COUNT(*) c {base} GROUP BY audience_tier")}
    niches: dict[str, int] = {}
    for row in con.execute(f"SELECT niches {base}"):
        for nch in (row["niches"] or "").split(","):
            if nch:
                niches[nch] = niches.get(nch, 0) + 1
    regions: dict[str, int] = {}
    for row in con.execute(f"SELECT region {base} AND region IS NOT NULL"):
        regions[row["region"]] = regions.get(row["region"], 0) + 1
    last_sync = con.execute(
        "SELECT ts FROM sync_log ORDER BY id DESC LIMIT 1").fetchone()
    return {"creators": n, "accounts": n_acc, "by_platform": by_platform,
            "by_tier": by_tier,
            "by_niche": dict(sorted(niches.items(), key=lambda x: -x[1])),
            "by_region": dict(sorted(regions.items(), key=lambda x: -x[1])),
            "last_sync": last_sync["ts"] if last_sync else None}