# Author: Simon-Pierre Boucher
# Contact: contact@spboucher.ai
"""Persistance SQLite — graphe de connaissances du web québécois (v2).

Entités enrichies et normalisées, relations typées (avec rôle + provenance),
mentions (provenance multi-sources), déduplication par résolution d'entités,
plus missions / événements / réglages / file de crawl pour le bot long terme.
"""

from __future__ import annotations

import hashlib
import json
import sqlite3
import time
from typing import Any, Iterable, Optional

from .normalize import canonical_location, domain, normalize_name


def _to_int(v: Any) -> Optional[int]:
    """Parse un nombre d'abonnés : 12000, '12,5 K', '3.2M', '1 200 abonnés' -> int."""
    if v is None or v == "":
        return None
    if isinstance(v, (int, float)):
        return int(v)
    s = str(v).lower().replace(" ", " ").strip()
    import re as _re
    m = _re.search(r"([\d]+(?:[.,]\d+)?)\s*([km])?", s.replace(" ", ""))
    if not m:
        return None
    num = float(m.group(1).replace(",", "."))
    mult = {"k": 1_000, "m": 1_000_000}.get(m.group(2) or "", 1)
    try:
        return int(num * mult)
    except (ValueError, OverflowError):
        return None


REL_TYPES = {
    "WORKS_AT", "FOUNDER_OF", "OWNS", "MEMBER_OF", "PARTNER_OF",
    "SUBSIDIARY_OF", "PARENT_OF", "AFFILIATED_WITH", "LOCATED_IN", "SUPPLIER_OF",
    # relations créateurs / influenceurs
    "REPRESENTED_BY", "COLLABORATES_WITH", "CREATES_ON", "SPONSORED_BY", "PROMOTES",
    "MANAGES", "APPEARS_WITH",
}

SCHEMA = """
CREATE TABLE IF NOT EXISTS entities (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    type         TEXT NOT NULL,
    name         TEXT NOT NULL,
    description  TEXT,
    url          TEXT,
    location     TEXT,
    email        TEXT,
    phone        TEXT,
    confidence   REAL DEFAULT 0.5,
    raw_json     TEXT,
    created_at   REAL NOT NULL,
    updated_at   REAL NOT NULL,
    UNIQUE(type, name, url)
);

CREATE TABLE IF NOT EXISTS social_links (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    entity_id INTEGER NOT NULL, platform TEXT NOT NULL, url TEXT NOT NULL,
    UNIQUE(entity_id, url),
    FOREIGN KEY(entity_id) REFERENCES entities(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS relations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    from_entity INTEGER NOT NULL, to_entity INTEGER NOT NULL, relation_type TEXT NOT NULL,
    UNIQUE(from_entity, to_entity, relation_type)
);

CREATE TABLE IF NOT EXISTS entity_mentions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    entity_id INTEGER NOT NULL, source_url TEXT NOT NULL, seen_at REAL NOT NULL,
    UNIQUE(entity_id, source_url),
    FOREIGN KEY(entity_id) REFERENCES entities(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS sources (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    url TEXT NOT NULL UNIQUE, title TEXT, content_hash TEXT, scraped_at REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS crawl_queue (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    url TEXT NOT NULL UNIQUE, status TEXT NOT NULL DEFAULT 'pending',
    depth INTEGER DEFAULT 0, added_at REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS missions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    goal TEXT NOT NULL, seed TEXT, sector TEXT, region TEXT,
    priority INTEGER DEFAULT 5, status TEXT NOT NULL DEFAULT 'pending',
    runs_count INTEGER DEFAULT 0, last_run REAL, created_at REAL NOT NULL,
    UNIQUE(goal, region)
);

CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts REAL NOT NULL, kind TEXT NOT NULL, message TEXT, url TEXT, data_json TEXT
);

CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT);

CREATE TABLE IF NOT EXISTS archives (
    id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT, created_at REAL NOT NULL,
    entities INTEGER, relations INTEGER, sources INTEGER, payload TEXT
);

CREATE INDEX IF NOT EXISTS idx_entities_type ON entities(type);
CREATE INDEX IF NOT EXISTS idx_queue_status ON crawl_queue(status);
CREATE INDEX IF NOT EXISTS idx_events_ts ON events(ts);
CREATE INDEX IF NOT EXISTS idx_rel_from ON relations(from_entity);
CREATE INDEX IF NOT EXISTS idx_rel_to ON relations(to_entity);
"""

# Colonnes ajoutées par migration aux DB existantes
_ENTITY_COLS = [
    ("canonical_name", "TEXT"), ("norm_name", "TEXT"), ("domain", "TEXT"), ("sector", "TEXT"),
    ("address", "TEXT"), ("city", "TEXT"), ("region", "TEXT"), ("postal_code", "TEXT"),
    ("neq", "TEXT"), ("founded", "TEXT"), ("size", "TEXT"), ("tags", "TEXT"),
    ("source_url", "TEXT"), ("first_seen", "REAL"), ("last_seen", "REAL"),
    # champs CRÉATEURS / INFLUENCEURS (ka6)
    ("niche", "TEXT"), ("handle", "TEXT"), ("platform", "TEXT"), ("followers", "INTEGER"),
    ("languages", "TEXT"),
]
_RELATION_COLS = [("role", "TEXT"), ("source_url", "TEXT"), ("confidence", "REAL")]


class Store:
    def __init__(self, path: str):
        self.conn = sqlite3.connect(path, timeout=30, check_same_thread=False)
        self.conn.row_factory = sqlite3.Row
        self.conn.execute("PRAGMA foreign_keys = ON")
        self.conn.execute("PRAGMA journal_mode = WAL")
        self.conn.execute("PRAGMA busy_timeout = 30000")
        self.conn.executescript(SCHEMA)
        self._migrate()
        self.conn.execute("CREATE INDEX IF NOT EXISTS idx_entities_norm ON entities(norm_name)")
        self.conn.execute("CREATE INDEX IF NOT EXISTS idx_entities_domain ON entities(domain)")
        self.conn.commit()

    def _migrate(self) -> None:
        ecols = {r["name"] for r in self.conn.execute("PRAGMA table_info(entities)")}
        for name, typ in _ENTITY_COLS:
            if name not in ecols:
                self.conn.execute(f"ALTER TABLE entities ADD COLUMN {name} {typ}")
        rcols = {r["name"] for r in self.conn.execute("PRAGMA table_info(relations)")}
        for name, typ in _RELATION_COLS:
            if name not in rcols:
                self.conn.execute(f"ALTER TABLE relations ADD COLUMN {name} {typ}")
        # social_links.followers (abonnés par plateforme) + crawl_queue.attempts (robustesse)
        scols = {r["name"] for r in self.conn.execute("PRAGMA table_info(social_links)")}
        if "followers" not in scols:
            self.conn.execute("ALTER TABLE social_links ADD COLUMN followers INTEGER")
        qcols = {r["name"] for r in self.conn.execute("PRAGMA table_info(crawl_queue)")}
        if "attempts" not in qcols:
            self.conn.execute("ALTER TABLE crawl_queue ADD COLUMN attempts INTEGER DEFAULT 0")
        self.conn.commit()
        self._backfill()

    def _backfill(self) -> None:
        """Normalise les entités héritées (norm_name/domain/region) pour la dédup et l'explorer."""
        rows = self.conn.execute(
            "SELECT id, name, url, location FROM entities WHERE norm_name IS NULL"
        ).fetchall()
        for r in rows:
            nn = normalize_name(r["name"]) or domain(r["url"])
            self.conn.execute(
                "UPDATE entities SET norm_name=?, domain=?, region=COALESCE(region,?) WHERE id=?",
                (nn, domain(r["url"]) or None, canonical_location(r["location"]) or None, r["id"]),
            )
        if rows:
            self.conn.commit()

    # -- résolution + upsert d'entités -------------------------------------
    def _resolve(self, etype: str, nn: str, dom: str, region: str) -> Optional[int]:
        if not nn:
            return None
        c = self.conn
        if dom:
            r = c.execute(
                "SELECT id FROM entities WHERE type=? AND norm_name=? AND domain=? LIMIT 1",
                (etype, nn, dom),
            ).fetchone()
            if r:
                return r["id"]
        if region:
            r = c.execute(
                "SELECT id FROM entities WHERE type=? AND norm_name=? AND region=? "
                "AND (domain IS NULL OR domain='') LIMIT 1",
                (etype, nn, region),
            ).fetchone()
            if r:
                return r["id"]
        r = c.execute(
            "SELECT id FROM entities WHERE type=? AND norm_name=? "
            "AND (domain IS NULL OR domain='') AND (region IS NULL OR region='') LIMIT 1",
            (etype, nn),
        ).fetchone()
        return r["id"] if r else None

    def upsert_entity(self, e: dict[str, Any], source_url: Optional[str] = None) -> int:
        now = time.time()
        etype = (e.get("type") or "website").strip().lower()
        name = (e.get("name") or "").strip()
        website = (e.get("website") or e.get("url") or "").strip() or None
        dom = domain(website)
        region = canonical_location(e.get("region") or e.get("city") or e.get("location") or "")
        city = (e.get("city") or "").strip() or None
        nn = normalize_name(name) or dom
        if not name:
            name = website or "inconnu"
        conf = float(e.get("confidence", 0.5) or 0.5)
        tags = json.dumps(e.get("tags"), ensure_ascii=False) if e.get("tags") else None
        langs = e.get("languages")
        languages = ", ".join(langs) if isinstance(langs, list) else (langs or None)
        followers = _to_int(e.get("followers"))

        eid = self._resolve(etype, nn, dom, region)
        vals = {
            "canonical_name": e.get("canonical_name") or name,
            "description": e.get("description"), "url": website, "domain": dom or None,
            "sector": e.get("sector"), "address": e.get("address"), "city": city,
            "region": region or None, "location": e.get("location") or region or city,
            "email": e.get("email"), "phone": e.get("phone"), "postal_code": e.get("postal_code"),
            "neq": e.get("neq"), "founded": e.get("founded"), "size": e.get("size"),
            "tags": tags, "confidence": conf, "source_url": source_url,
            "niche": e.get("niche"), "handle": e.get("handle"), "platform": e.get("platform"),
            "followers": followers, "languages": languages,
        }
        if eid:
            self.conn.execute(
                """UPDATE entities SET
                   canonical_name=COALESCE(canonical_name,?), description=COALESCE(description,?),
                   url=COALESCE(url,?), domain=COALESCE(NULLIF(domain,''),?), sector=COALESCE(sector,?),
                   address=COALESCE(address,?), city=COALESCE(city,?), region=COALESCE(region,?),
                   location=COALESCE(location,?), email=COALESCE(email,?), phone=COALESCE(phone,?),
                   postal_code=COALESCE(postal_code,?), neq=COALESCE(neq,?), founded=COALESCE(founded,?),
                   size=COALESCE(size,?), tags=COALESCE(tags,?), confidence=MAX(IFNULL(confidence,0),?),
                   source_url=COALESCE(source_url,?), niche=COALESCE(niche,?), handle=COALESCE(handle,?),
                   platform=COALESCE(platform,?), followers=MAX(IFNULL(followers,0),?),
                   languages=COALESCE(languages,?), last_seen=?, updated_at=? WHERE id=?""",
                (
                    vals["canonical_name"], vals["description"], vals["url"], vals["domain"],
                    vals["sector"], vals["address"], vals["city"], vals["region"], vals["location"],
                    vals["email"], vals["phone"], vals["postal_code"], vals["neq"], vals["founded"],
                    vals["size"], vals["tags"], vals["confidence"], vals["source_url"],
                    vals["niche"], vals["handle"], vals["platform"], followers or 0, vals["languages"],
                    now, now, eid,
                ),
            )
        else:
            cur = self.conn.execute(
                """INSERT INTO entities(type, name, canonical_name, norm_name, description, url, domain,
                   sector, address, city, region, location, email, phone, postal_code, neq, founded,
                   size, tags, confidence, raw_json, source_url, niche, handle, platform, followers,
                   languages, first_seen, last_seen, created_at, updated_at)
                   VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
                (
                    etype, name, vals["canonical_name"], nn, vals["description"], vals["url"], vals["domain"],
                    vals["sector"], vals["address"], vals["city"], vals["region"], vals["location"],
                    vals["email"], vals["phone"], vals["postal_code"], vals["neq"], vals["founded"],
                    vals["size"], vals["tags"], conf, json.dumps(e, ensure_ascii=False), source_url,
                    vals["niche"], vals["handle"], vals["platform"], followers, vals["languages"],
                    now, now, now, now,
                ),
            )
            eid = cur.lastrowid

        for link in e.get("social_links", []) or []:
            self.add_social_link(eid, link.get("platform", "?"), link.get("url", ""),
                                 _to_int(link.get("followers")))
        if source_url:
            self.conn.execute(
                "INSERT OR IGNORE INTO entity_mentions(entity_id, source_url, seen_at) VALUES(?,?,?)",
                (eid, source_url, now),
            )
        self.conn.commit()
        return eid

    def add_social_link(self, entity_id: int, platform: str, url: str,
                        followers: Optional[int] = None) -> None:
        url = (url or "").strip()
        if not url:
            return
        self.conn.execute(
            "INSERT INTO social_links(entity_id, platform, url, followers) VALUES(?,?,?,?) "
            "ON CONFLICT(entity_id, url) DO UPDATE SET followers=COALESCE(excluded.followers, followers)",
            (entity_id, (platform or "?").strip().lower(), url, followers),
        )

    # -- relations ---------------------------------------------------------
    def add_relation(self, from_id: int, to_id: int, rel_type: str, role: str = "",
                     source_url: str = "", confidence: float = 0.6) -> None:
        rel_type = (rel_type or "").strip().upper()
        if not from_id or not to_id or from_id == to_id or rel_type not in REL_TYPES:
            return
        self.conn.execute(
            """INSERT INTO relations(from_entity, to_entity, relation_type, role, source_url, confidence)
               VALUES(?,?,?,?,?,?)
               ON CONFLICT(from_entity, to_entity, relation_type) DO UPDATE SET
               role=COALESCE(NULLIF(excluded.role,''), role),
               source_url=COALESCE(NULLIF(excluded.source_url,''), source_url),
               confidence=MAX(IFNULL(confidence,0), excluded.confidence)""",
            (from_id, to_id, rel_type, role or None, source_url or None, confidence),
        )
        self.conn.commit()

    def entity_relations(self, entity_id: int) -> list[dict[str, Any]]:
        rows = self.conn.execute(
            """SELECT r.relation_type, r.role, r.confidence, r.from_entity, r.to_entity,
                      e.id oid, e.name oname, e.type otype, e.region oregion, e.sector osector
               FROM relations r
               JOIN entities e ON e.id = CASE WHEN r.from_entity=? THEN r.to_entity ELSE r.from_entity END
               WHERE r.from_entity=? OR r.to_entity=?
               ORDER BY r.confidence DESC""",
            (entity_id, entity_id, entity_id),
        ).fetchall()
        out = []
        for r in rows:
            outgoing = r["from_entity"] == entity_id
            out.append({
                "id": r["oid"], "name": r["oname"], "type": r["otype"],
                "region": r["oregion"], "sector": r["osector"],
                "relation": r["relation_type"], "role": r["role"],
                "direction": "out" if outgoing else "in",
                "confidence": r["confidence"],
            })
        return out

    # -- sources / file ----------------------------------------------------
    def record_source(self, url: str, title: str, content: str) -> None:
        h = hashlib.sha256((content or "").encode("utf-8")).hexdigest()
        self.conn.execute(
            "INSERT OR REPLACE INTO sources(url, title, content_hash, scraped_at) VALUES(?,?,?,?)",
            (url, title, h, time.time()),
        )
        self.conn.commit()

    def source_seen(self, url: str) -> bool:
        return self.conn.execute("SELECT 1 FROM sources WHERE url=?", (url,)).fetchone() is not None

    def enqueue(self, urls: Iterable[str], depth: int = 0) -> int:
        n = 0
        for u in urls:
            u = (u or "").strip()
            if not u:
                continue
            n += self.conn.execute(
                "INSERT OR IGNORE INTO crawl_queue(url, status, depth, added_at) VALUES(?,?,?,?)",
                (u, "pending", depth, time.time()),
            ).rowcount
        self.conn.commit()
        return n

    def next_pending(self) -> Optional[sqlite3.Row]:
        return self.conn.execute(
            "SELECT * FROM crawl_queue WHERE status='pending' ORDER BY depth, id LIMIT 1"
        ).fetchone()

    def pending_batch(self, n: int, max_attempts: int = 99) -> list[sqlite3.Row]:
        return self.conn.execute(
            "SELECT * FROM crawl_queue WHERE status='pending' AND IFNULL(attempts,0) < ? "
            "ORDER BY depth, id LIMIT ?", (max_attempts, max(1, n)),
        ).fetchall()

    def mark(self, queue_id: int, status: str) -> None:
        self.conn.execute("UPDATE crawl_queue SET status=? WHERE id=?", (status, queue_id))
        self.conn.commit()

    def bump_attempt(self, queue_id: int, max_attempts: int) -> None:
        """Incrémente le compteur de tentatives ; passe en 'error' au-delà du seuil (dead-letter)."""
        self.conn.execute(
            "UPDATE crawl_queue SET attempts=IFNULL(attempts,0)+1, "
            "status=CASE WHEN IFNULL(attempts,0)+1 >= ? THEN 'error' ELSE 'pending' END WHERE id=?",
            (max_attempts, queue_id),
        )
        self.conn.commit()

    # -- settings ----------------------------------------------------------
    def get_setting(self, key: str, default: str = "") -> str:
        row = self.conn.execute("SELECT value FROM settings WHERE key=?", (key,)).fetchone()
        return row["value"] if row else default

    def set_setting(self, key: str, value: str) -> None:
        self.conn.execute(
            "INSERT INTO settings(key, value) VALUES(?,?) "
            "ON CONFLICT(key) DO UPDATE SET value=excluded.value",
            (key, value),
        )
        self.conn.commit()

    # -- missions ----------------------------------------------------------
    def add_mission(self, goal: str, seed: str = "", sector: str = "", region: str = "",
                    priority: int = 5) -> int:
        cur = self.conn.execute(
            """INSERT OR IGNORE INTO missions(goal, seed, sector, region, priority, status, created_at)
               VALUES(?,?,?,?,?, 'pending', ?)""",
            (goal, seed or None, sector or None, region or None, priority, time.time()),
        )
        self.conn.commit()
        return cur.lastrowid or 0

    def next_mission(self) -> Optional[sqlite3.Row]:
        return self.conn.execute(
            "SELECT * FROM missions WHERE status='pending' ORDER BY priority, last_run IS NOT NULL, "
            "IFNULL(last_run, 0), id LIMIT 1"
        ).fetchone()

    def due_mission(self, revisit_seconds: float) -> Optional[sqlite3.Row]:
        threshold = time.time() - revisit_seconds
        return self.conn.execute(
            "SELECT * FROM missions WHERE status IN ('pending','done') "
            "AND (last_run IS NULL OR last_run <= ?) "
            "ORDER BY priority, IFNULL(last_run, 0), id LIMIT 1",
            (threshold,),
        ).fetchone()

    def set_mission_status(self, mission_id: int, status: str, bump_run: bool = False) -> None:
        if bump_run:
            self.conn.execute(
                "UPDATE missions SET status=?, runs_count=runs_count+1, last_run=? WHERE id=?",
                (status, time.time(), mission_id),
            )
        else:
            self.conn.execute("UPDATE missions SET status=? WHERE id=?", (status, mission_id))
        self.conn.commit()

    def list_missions(self) -> list[dict[str, Any]]:
        return [dict(r) for r in self.conn.execute("SELECT * FROM missions ORDER BY priority, id")]

    def delete_mission(self, mission_id: int) -> None:
        self.conn.execute("DELETE FROM missions WHERE id=?", (mission_id,))
        self.conn.commit()

    def prioritize_mission(self, mission_id: int) -> None:
        """Passe la mission tout en haut de la file et la rend « due » immédiatement."""
        self.conn.execute(
            "UPDATE missions SET priority=0, status='pending', last_run=NULL WHERE id=?",
            (mission_id,),
        )
        self.conn.commit()

    def next_forced(self) -> Optional[sqlite3.Row]:
        """Mission « lancer maintenant » (priorité 0) à exécuter avant tout le reste."""
        return self.conn.execute(
            "SELECT * FROM missions WHERE status='pending' AND priority <= 0 "
            "ORDER BY IFNULL(last_run, 0), id LIMIT 1"
        ).fetchone()

    def requeue_stale_missions(self) -> None:
        self.conn.execute("UPDATE missions SET status='pending' WHERE status='running'")
        self.conn.commit()

    def pages_since(self, since_ts: float) -> int:
        return self.conn.execute(
            "SELECT COUNT(*) n FROM sources WHERE scraped_at >= ?", (since_ts,)
        ).fetchone()["n"]

    # -- événements --------------------------------------------------------
    def log_event(self, kind: str, message: str = "", url: str = "", data: Any = None) -> None:
        self.conn.execute(
            "INSERT INTO events(ts, kind, message, url, data_json) VALUES(?,?,?,?,?)",
            (time.time(), kind, message, url or None,
             json.dumps(data, ensure_ascii=False) if data is not None else None),
        )
        self.conn.commit()

    def recent_events(self, limit: int = 100, after_id: int = 0) -> list[dict[str, Any]]:
        rows = self.conn.execute(
            "SELECT * FROM events WHERE id > ? ORDER BY id DESC LIMIT ?", (after_id, limit)
        ).fetchall()
        return [dict(r) for r in rows]

    # -- lecture / agrégats ------------------------------------------------
    def get_entity(self, entity_id: int) -> Optional[dict[str, Any]]:
        e = self.conn.execute("SELECT * FROM entities WHERE id=?", (entity_id,)).fetchone()
        if not e:
            return None
        d = dict(e)
        d["social_links"] = [
            {"platform": r["platform"], "url": r["url"], "followers": r["followers"]}
            for r in self.conn.execute(
                "SELECT platform, url, followers FROM social_links WHERE entity_id=?", (entity_id,)
            )
        ]
        d["sources"] = [
            r["source_url"] for r in self.conn.execute(
                "SELECT source_url FROM entity_mentions WHERE entity_id=? ORDER BY seen_at DESC LIMIT 15",
                (entity_id,),
            )
        ]
        return d

    def distinct_locations(self) -> list[dict[str, Any]]:
        rows = self.conn.execute(
            "SELECT COALESCE(NULLIF(region,''), location) loc, COUNT(*) n FROM entities "
            "WHERE COALESCE(NULLIF(region,''), location) IS NOT NULL "
            "AND COALESCE(NULLIF(region,''), location)<>'' GROUP BY loc ORDER BY n DESC"
        ).fetchall()
        return [{"location": r["loc"], "count": r["n"]} for r in rows]

    def platform_counts(self) -> list[dict[str, Any]]:
        rows = self.conn.execute(
            "SELECT platform, COUNT(*) n FROM social_links GROUP BY platform ORDER BY n DESC"
        ).fetchall()
        return [{"platform": r["platform"], "count": r["n"]} for r in rows]

    def degrees(self) -> dict[int, int]:
        deg: dict[int, int] = {}
        for r in self.conn.execute("SELECT from_entity a, to_entity b FROM relations"):
            deg[r["a"]] = deg.get(r["a"], 0) + 1
            deg[r["b"]] = deg.get(r["b"], 0) + 1
        return deg

    def top_connected(self, limit: int = 8) -> list[dict[str, Any]]:
        deg = self.degrees()
        if not deg:
            return []
        top = sorted(deg.items(), key=lambda kv: kv[1], reverse=True)[:limit]
        out = []
        for eid, d in top:
            e = self.conn.execute(
                "SELECT id, name, type, sector, region FROM entities WHERE id=?", (eid,)
            ).fetchone()
            if e:
                out.append({**dict(e), "degree": d})
        return out

    def graph(self, limit: int = 140, etype: str = "", region: str = "", sector: str = "",
              connected_only: bool = True) -> dict[str, Any]:
        q = "SELECT id, name, type, sector, region, location FROM entities WHERE 1=1"
        args: list[Any] = []
        if etype:
            q += " AND type=?"
            args.append(etype)
        if region:
            q += " AND COALESCE(NULLIF(region,''), location)=?"
            args.append(region)
        if sector:
            q += " AND sector LIKE ?"
            args.append(f"%{sector}%")
        rows = self.conn.execute(q, args).fetchall()
        deg = self.degrees()
        if connected_only:
            rows = [r for r in rows if deg.get(r["id"], 0) > 0]
        rows = sorted(rows, key=lambda r: deg.get(r["id"], 0), reverse=True)[:limit]
        ids = {r["id"] for r in rows}
        nodes = [{"id": r["id"], "name": r["name"], "type": r["type"],
                  "degree": deg.get(r["id"], 0)} for r in rows]
        edges = []
        for r in self.conn.execute(
            "SELECT from_entity a, to_entity b, relation_type t, role FROM relations"
        ):
            if r["a"] in ids and r["b"] in ids:
                edges.append({"s": r["a"], "t": r["b"], "type": r["t"], "role": r["role"]})
        return {"nodes": nodes, "edges": edges, "truncated": len(ids)}

    def sector_counts(self) -> list[dict[str, Any]]:
        rows = self.conn.execute(
            "SELECT sector, COUNT(*) n FROM entities WHERE sector IS NOT NULL AND sector<>'' "
            "GROUP BY sector ORDER BY n DESC"
        ).fetchall()
        return [{"sector": r["sector"], "count": r["n"]} for r in rows]

    def stats(self) -> dict[str, Any]:
        c = self.conn
        by_type = {r["type"]: r["n"] for r in c.execute("SELECT type, COUNT(*) n FROM entities GROUP BY type")}
        return {
            "entities": c.execute("SELECT COUNT(*) n FROM entities").fetchone()["n"],
            "by_type": by_type,
            "relations": c.execute("SELECT COUNT(*) n FROM relations").fetchone()["n"],
            "social_links": c.execute("SELECT COUNT(*) n FROM social_links").fetchone()["n"],
            "sources": c.execute("SELECT COUNT(*) n FROM sources").fetchone()["n"],
            "queue_pending": c.execute(
                "SELECT COUNT(*) n FROM crawl_queue WHERE status='pending'"
            ).fetchone()["n"],
        }

    def export(self) -> list[dict[str, Any]]:
        out = []
        for e in self.conn.execute("SELECT * FROM entities ORDER BY type, name"):
            d = dict(e)
            d["social_links"] = [
                {"platform": r["platform"], "url": r["url"], "followers": r["followers"]}
                for r in self.conn.execute(
                    "SELECT platform, url, followers FROM social_links WHERE entity_id=?", (e["id"],)
                )
            ]
            out.append(d)
        return out

    # -- archives (snapshot + reset + réutilisation) -----------------------
    def _snapshot(self) -> dict[str, Any]:
        c = self.conn
        return {
            "entities": self.export(),
            "relations": [dict(r) for r in c.execute(
                "SELECT from_entity, to_entity, relation_type, role, source_url, confidence FROM relations")],
            "missions": [dict(r) for r in c.execute(
                "SELECT goal, seed, sector, region, priority FROM missions")],
            "sources": [dict(r) for r in c.execute("SELECT url, title FROM sources")],
        }

    def archive_and_reset(self, label: str) -> dict[str, Any]:
        """Sauvegarde tout le graphe dans l'historique puis remet l'espace de travail à VIDE."""
        st = self.stats()
        payload = json.dumps(self._snapshot(), ensure_ascii=False)
        lab = (label or "").strip() or time.strftime("Archive %Y-%m-%d %H:%M", time.localtime())
        cur = self.conn.execute(
            "INSERT INTO archives(label, created_at, entities, relations, sources, payload) VALUES(?,?,?,?,?,?)",
            (lab, time.time(), st["entities"], st["relations"], st["sources"], payload),
        )
        for t in ("relations", "social_links", "entity_mentions", "entities", "sources",
                  "crawl_queue", "events", "missions"):
            self.conn.execute(f"DELETE FROM {t}")
        self.conn.execute("DELETE FROM settings WHERE key IN ('active_mission_id','abort_current')")
        self.conn.commit()
        return {"id": cur.lastrowid, "label": lab, "entities": st["entities"], "relations": st["relations"]}

    def list_archives(self) -> list[dict[str, Any]]:
        return [dict(r) for r in self.conn.execute(
            "SELECT id, label, created_at, entities, relations, sources FROM archives ORDER BY id DESC")]

    def get_archive(self, archive_id: int) -> Optional[dict[str, Any]]:
        r = self.conn.execute("SELECT * FROM archives WHERE id=?", (archive_id,)).fetchone()
        if not r:
            return None
        d = dict(r)
        d["data"] = json.loads(d.pop("payload") or "{}")
        return d

    def delete_archive(self, archive_id: int) -> None:
        self.conn.execute("DELETE FROM archives WHERE id=?", (archive_id,))
        self.conn.commit()

    def restore_archive(self, archive_id: int) -> dict[str, Any]:
        """Recharge une archive dans l'espace de travail (remap des identifiants)."""
        a = self.get_archive(archive_id)
        if not a:
            return {"ok": False}
        data = a["data"]
        idmap: dict[Any, int] = {}
        for e in data.get("entities", []):
            old = e.get("id")
            nid = self.upsert_entity(dict(e))
            if old is not None:
                idmap[old] = nid
        rel = 0
        for r in data.get("relations", []):
            f, t = idmap.get(r.get("from_entity")), idmap.get(r.get("to_entity"))
            if f and t:
                self.add_relation(f, t, r.get("relation_type", ""), r.get("role") or "",
                                  r.get("source_url") or "", float(r.get("confidence") or 0.6))
                rel += 1
        for m in data.get("missions", []):
            self.add_mission(m.get("goal", ""), m.get("seed") or "", m.get("sector") or "",
                             m.get("region") or "", int(m.get("priority") or 5))
        for s in data.get("sources", []):
            self.record_source(s.get("url", ""), s.get("title") or "", "")
        return {"ok": True, "entities": len(idmap), "relations": rel}

    def close(self) -> None:
        self.conn.close()