"""
powerbi_store.py — SQLite cache + snapshot store for Power BI data (DVI-1243).

Togen reads the Power BI REST API live-first (org service principal,
client-credentials) and falls back to this local store so dashboard tiles and
Auditor reports never go blank when Power BI is slow, throttled, or down —
the same board-approved hybrid design as the Idencia service (D4 mirrors
DVI-1226 D2). The background vocab sync (org scope) and every successful live
query (any scope) write through here.

Flask-independent (mirrors idencia_store.py / scada_store.py) so app.py, the
togen-worker sync loop, and tests can all import it. The DB lives alongside
the app's other state files (powerbi.db next to powerbi_config.json).

Schema
------
cache(scope, cache_key, payload, fetched_at) — last-good JSON result per
    (connection scope, query key). Scope is "org" today (DVI-1243 D1-A: org
    service principal only); "user:<email>" is reserved for the deferred
    per-user delegated-access follow-up (D1-B) so cache entries stay
    scope-keyed from day one. UPSERT on (scope, cache_key); this is a
    latest-value store, not history.
snapshots(run_ts, scope, kind, payload) — append-only rows written by each
    sync run (workspace/report/dataset inventory counts, dataset refresh
    outcomes), the over-time data source for Auditor Reporting (P3). Kept
    separate from cache so trends survive cache overwrites.
meta(key, value) — sync health bookkeeping (last run, error, counts) for the
    Admin → Services → Power BI pane, mirroring the SCADA collector-health
    pattern.
"""

import json
import os
import sqlite3
import threading
from datetime import datetime
from pathlib import Path

POWERBI_DB_FILE = Path(
    os.environ.get("POWERBI_DB_FILE",
                   Path(__file__).resolve().parent / "powerbi.db"))

# Serialize writes within one process; SQLite handles cross-process locking.
_write_lock = threading.Lock()

ORG_SCOPE = "org"


def _connect():
    conn = sqlite3.connect(str(POWERBI_DB_FILE), timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA busy_timeout=10000")
    return conn


def init_powerbi_db():
    """Create tables/indexes if missing. Idempotent; call at import/startup."""
    with _write_lock, _connect() as conn:
        conn.execute(
            """CREATE TABLE IF NOT EXISTS cache (
                   scope TEXT NOT NULL,
                   cache_key TEXT NOT NULL,
                   payload TEXT NOT NULL,
                   fetched_at TEXT NOT NULL,
                   UNIQUE(scope, cache_key)
               )""")
        conn.execute(
            """CREATE TABLE IF NOT EXISTS snapshots (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   run_ts TEXT NOT NULL,
                   scope TEXT NOT NULL,
                   kind TEXT NOT NULL,
                   payload TEXT NOT NULL
               )""")
        conn.execute(
            """CREATE INDEX IF NOT EXISTS idx_snapshots_kind_ts
               ON snapshots(kind, run_ts)""")
        conn.execute(
            """CREATE TABLE IF NOT EXISTS meta (
                   key TEXT PRIMARY KEY,
                   value TEXT
               )""")


def _now():
    return datetime.now().strftime("%Y-%m-%d %H:%M:%S")


def user_scope(email):
    """Scope key for a personal connection ("user:<email>", lowercased).
    Reserved for the D1-B per-user delegated-access follow-up."""
    return "user:" + str(email or "").strip().lower()


# --- cache -----------------------------------------------------------------

def cache_put(scope, cache_key, payload):
    """Store the last-good payload for (scope, key). payload is any
    JSON-serializable value."""
    with _write_lock, _connect() as conn:
        conn.execute(
            """INSERT INTO cache (scope, cache_key, payload, fetched_at)
               VALUES (?, ?, ?, ?)
               ON CONFLICT(scope, cache_key)
               DO UPDATE SET payload=excluded.payload,
                             fetched_at=excluded.fetched_at""",
            (scope, cache_key, json.dumps(payload), _now()))


def cache_get(scope, cache_key):
    """Return {"payload": ..., "fetched_at": "..."} or None when never
    cached. Corrupt rows read as a miss rather than raising."""
    with _connect() as conn:
        row = conn.execute(
            "SELECT payload, fetched_at FROM cache"
            " WHERE scope=? AND cache_key=?",
            (scope, cache_key)).fetchone()
    if not row:
        return None
    try:
        return {"payload": json.loads(row[0]), "fetched_at": row[1]}
    except (json.JSONDecodeError, TypeError):
        return None


def cache_keys(scope):
    """List cached keys for a scope with their freshness (admin surfacing)."""
    with _connect() as conn:
        rows = conn.execute(
            "SELECT cache_key, fetched_at FROM cache WHERE scope=?"
            " ORDER BY cache_key", (scope,)).fetchall()
    return [{"cache_key": r[0], "fetched_at": r[1]} for r in rows]


# --- snapshots ---------------------------------------------------------------

def add_snapshot(kind, payload, scope=ORG_SCOPE, run_ts=None):
    """Append one snapshot row (payload JSON-serializable)."""
    with _write_lock, _connect() as conn:
        conn.execute(
            "INSERT INTO snapshots (run_ts, scope, kind, payload)"
            " VALUES (?, ?, ?, ?)",
            (run_ts or _now(), scope, kind, json.dumps(payload)))


def snapshots(kind, scope=ORG_SCOPE, since=None, limit=500):
    """Snapshot rows for one kind, oldest→newest (chart-ready). ``since`` is
    an inclusive "YYYY-MM-DD HH:MM:SS" lower bound."""
    limit = max(1, min(int(limit or 500), 5000))
    q = ("SELECT run_ts, payload FROM snapshots"
         " WHERE kind=? AND scope=?")
    args = [kind, scope]
    if since:
        q += " AND run_ts>=?"
        args.append(since)
    q += " ORDER BY run_ts DESC, id DESC LIMIT ?"
    args.append(limit)
    with _connect() as conn:
        rows = conn.execute(q, args).fetchall()
    out = []
    for run_ts, payload in reversed(rows):
        try:
            out.append({"run_ts": run_ts, "payload": json.loads(payload)})
        except (json.JSONDecodeError, TypeError):
            continue
    return out


def snapshot_count():
    with _connect() as conn:
        return conn.execute("SELECT COUNT(*) FROM snapshots").fetchone()[0]


# --- meta / sync health -------------------------------------------------------

def set_meta(key, value):
    with _write_lock, _connect() as conn:
        conn.execute(
            "INSERT INTO meta (key, value) VALUES (?, ?)"
            " ON CONFLICT(key) DO UPDATE SET value=excluded.value",
            (key, json.dumps(value)))


def get_meta(key):
    with _connect() as conn:
        row = conn.execute(
            "SELECT value FROM meta WHERE key=?", (key,)).fetchone()
    if not row:
        return None
    try:
        return json.loads(row[0])
    except (json.JSONDecodeError, TypeError):
        return None


def record_sync(status, actor="", error=None, counts=None):
    """Record one sync run's outcome for the admin pane. status: ok|error."""
    rec = {"status": status, "at": _now(), "actor": actor or "",
           "counts": counts or {}}
    if error:
        rec["error"] = str(error)[:500]
    set_meta("last_sync", rec)
    if status == "ok":
        set_meta("last_sync_ok", rec)


def sync_health():
    """{"last": ..., "last_ok": ...} for the Admin pane (None when never)."""
    return {"last": get_meta("last_sync"), "last_ok": get_meta("last_sync_ok")}


init_powerbi_db()