"""
SQLite-backed storage for Fester.

Single database file at FESTER_DB_PATH (default: /var/lib/fester/fester.db,
with fallbacks to ~/.fester/fester.db and /tmp/fester.db).

Tables:
    builds        — build records (one row per build)
    sessions      — replay sessions
    events        — timeline events (one row per bus event)
    node_states   — latest known state per node (snapshot, not journal)

All writes are serialized through a single connection with a threading.Lock.
Reads are concurrent. The database is WAL-mode for better concurrency.

Used by:
    - backend.pipeline.runner.BuildRunner      (builds table)
    - backend.session.db.SessionDB             (sessions + events tables)
    - backend.analysis.timeline_store.TimelineStore (events table)
    - backend.main ambient loop                (node_states table, optional)
"""

from __future__ import annotations

import json
import os
import sqlite3
import threading
import time
from pathlib import Path
from typing import Any, Dict, List, Optional


# ----------------------------------------------------------------
# Database path resolution
# ----------------------------------------------------------------
def _resolve_db_path() -> Path:
    env = os.environ.get("FESTER_DB_PATH")
    if env:
        p = Path(env)
        p.parent.mkdir(parents=True, exist_ok=True)
        return p
    for c in [
        Path("/var/lib/fester/fester.db"),
        Path.home() / ".fester" / "fester.db",
        Path("/tmp/fester.db"),
    ]:
        try:
            c.parent.mkdir(parents=True, exist_ok=True)
            # touch to verify writability
            if not c.exists():
                c.write_text("")
            else:
                with open(c, "a"):
                    pass
            return c
        except (OSError, PermissionError):
            continue
    return Path(":memory:")  # in-memory last resort


DB_PATH = _resolve_db_path()


# ----------------------------------------------------------------
# Storage backend
# ----------------------------------------------------------------
class Storage:
    """SQLite-backed storage with a serialization lock for writes."""

    def __init__(self, path: Path = DB_PATH):
        self.path = path
        self._lock = threading.Lock()
        self._conn = self._open(path)
        self._migrate()

    def _open(self, path: Path) -> sqlite3.Connection:
        # Allow in-memory for tests
        if str(path) == ":memory:":
            conn = sqlite3.connect(":memory:", check_same_thread=False)
        else:
            conn = sqlite3.connect(str(path), check_same_thread=False)
        conn.row_factory = sqlite3.Row
        # WAL mode for better read/write concurrency
        try:
            conn.execute("PRAGMA journal_mode=WAL")
            conn.execute("PRAGMA synchronous=NORMAL")
        except sqlite3.OperationalError:
            pass  # in-memory db doesn't support WAL
        return conn

    def _migrate(self):
        """Create tables if they don't exist. Idempotent."""
        with self._lock:
            cur = self._conn.cursor()
            cur.executescript("""
                CREATE TABLE IF NOT EXISTS builds (
                    build_id    TEXT PRIMARY KEY,
                    cmd         TEXT,
                    dir         TEXT,
                    project     TEXT,
                    arch        TEXT,
                    target      TEXT,
                    toolchain   TEXT,
                    started_at  REAL,
                    ended_at    REAL,
                    status      TEXT,
                    actions     TEXT,  -- JSON array
                    error       TEXT
                );

                CREATE TABLE IF NOT EXISTS sessions (
                    session_id  TEXT PRIMARY KEY,
                    source      TEXT,
                    created_at  REAL,
                    event_count INTEGER DEFAULT 0
                );

                CREATE TABLE IF NOT EXISTS events (
                    id          INTEGER PRIMARY KEY AUTOINCREMENT,
                    session_id  TEXT,           -- nullable (live events have no session)
                    ts          REAL,
                    type        TEXT,
                    node        TEXT,
                    action      TEXT,
                    state       TEXT,
                    reason      TEXT,
                    target      TEXT,
                    payload     TEXT            -- full JSON event
                );
                CREATE INDEX IF NOT EXISTS idx_events_session ON events(session_id);
                CREATE INDEX IF NOT EXISTS idx_events_node ON events(node);
                CREATE INDEX IF NOT EXISTS idx_events_action ON events(action);
                CREATE INDEX IF NOT EXISTS idx_events_ts ON events(ts);

                CREATE TABLE IF NOT EXISTS node_states (
                    name            TEXT PRIMARY KEY,
                    state           TEXT,
                    cpu_load        REAL,
                    memory_load     REAL,
                    temp            REAL,
                    instability     REAL,
                    active_jobs     INTEGER,
                    max_jobs        INTEGER,
                    last_seen       REAL,
                    labels          TEXT,
                    updated_at      REAL
                );
            """)
            self._conn.commit()

    # ----------------------------------------------------------------
    # BUILDS
    # ----------------------------------------------------------------
    def save_build(self, build: Dict[str, Any]) -> bool:
        try:
            with self._lock:
                self._conn.execute("""
                    INSERT OR REPLACE INTO builds
                    (build_id, cmd, dir, project, arch, target, toolchain,
                     started_at, ended_at, status, actions, error)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    build.get("build_id"),
                    build.get("cmd"),
                    build.get("dir"),
                    build.get("project"),
                    build.get("arch"),
                    build.get("target"),
                    build.get("toolchain"),
                    build.get("started_at"),
                    build.get("ended_at"),
                    build.get("status"),
                    json.dumps(build.get("actions", [])),
                    build.get("error"),
                ))
                self._conn.commit()
            return True
        except Exception:
            return False

    def get_build(self, build_id: str) -> Optional[Dict[str, Any]]:
        with self._lock:
            row = self._conn.execute(
                "SELECT * FROM builds WHERE build_id = ?", (build_id,)
            ).fetchone()
        if not row:
            return None
        return _row_to_build(row)

    def list_builds(self, limit: int = 100) -> List[Dict[str, Any]]:
        with self._lock:
            rows = self._conn.execute(
                "SELECT * FROM builds ORDER BY started_at DESC LIMIT ?", (limit,)
            ).fetchall()
        return [_row_to_build(r) for r in rows]

    def delete_build(self, build_id: str) -> bool:
        with self._lock:
            cur = self._conn.execute(
                "DELETE FROM builds WHERE build_id = ?", (build_id,)
            )
            self._conn.commit()
            return cur.rowcount > 0

    # ----------------------------------------------------------------
    # SESSIONS
    # ----------------------------------------------------------------
    def create_session(self, session_id: str, source: str = "live",
                       created_at: Optional[float] = None) -> bool:
        try:
            with self._lock:
                self._conn.execute("""
                    INSERT OR REPLACE INTO sessions
                    (session_id, source, created_at, event_count)
                    VALUES (?, ?, ?, 0)
                """, (session_id, source, created_at or time.time()))
                self._conn.commit()
            return True
        except Exception:
            return False

    def list_sessions(self) -> List[Dict[str, Any]]:
        with self._lock:
            rows = self._conn.execute(
                "SELECT * FROM sessions ORDER BY created_at DESC"
            ).fetchall()
        return [dict(r) for r in rows]

    def get_session(self, session_id: str) -> Optional[Dict[str, Any]]:
        with self._lock:
            row = self._conn.execute(
                "SELECT * FROM sessions WHERE session_id = ?", (session_id,)
            ).fetchone()
        return dict(row) if row else None

    def delete_session(self, session_id: str) -> bool:
        with self._lock:
            cur = self._conn.execute(
                "DELETE FROM sessions WHERE session_id = ?", (session_id,)
            )
            self._conn.commit()
            return cur.rowcount > 0

    # ----------------------------------------------------------------
    # EVENTS
    # ----------------------------------------------------------------
    def append_event(self, event: Dict[str, Any], session_id: Optional[str] = None) -> int:
        """Insert an event. Returns the new row id."""
        ts = event.get("timestamp") or time.time()
        etype = event.get("type", "event")
        payload = json.dumps(event, default=str)
        try:
            with self._lock:
                cur = self._conn.execute("""
                    INSERT INTO events
                    (session_id, ts, type, node, action, state, reason, target, payload)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    session_id,
                    ts,
                    etype,
                    event.get("node") or (event.get("data", {}) or {}).get("node"),
                    event.get("action") or (event.get("data", {}) or {}).get("action"),
                    event.get("state") or (event.get("data", {}) or {}).get("state"),
                    event.get("reason") or (event.get("data", {}) or {}).get("reason"),
                    event.get("target") or (event.get("data", {}) or {}).get("target"),
                    payload,
                ))
                self._conn.commit()
                if session_id:
                    self._conn.execute(
                        "UPDATE sessions SET event_count = event_count + 1 WHERE session_id = ?",
                        (session_id,),
                    )
                    self._conn.commit()
                return cur.lastrowid or 0
        except Exception:
            return 0

    def get_events(self, session_id: Optional[str] = None, limit: int = 1000,
                   offset: int = 0) -> List[Dict[str, Any]]:
        with self._lock:
            if session_id:
                rows = self._conn.execute(
                    "SELECT * FROM events WHERE session_id = ? ORDER BY ts ASC LIMIT ? OFFSET ?",
                    (session_id, limit, offset),
                ).fetchall()
            else:
                rows = self._conn.execute(
                    "SELECT * FROM events ORDER BY ts ASC LIMIT ? OFFSET ?",
                    (limit, offset),
                ).fetchall()
        return [_row_to_event(r) for r in rows]

    def get_node_events(self, node: str, limit: int = 100) -> List[Dict[str, Any]]:
        with self._lock:
            rows = self._conn.execute(
                "SELECT * FROM events WHERE node = ? ORDER BY ts ASC LIMIT ?",
                (node, limit),
            ).fetchall()
        return [_row_to_event(r) for r in rows]

    def get_action_events(self, action: str, limit: int = 100) -> List[Dict[str, Any]]:
        with self._lock:
            rows = self._conn.execute(
                "SELECT * FROM events WHERE action = ? ORDER BY ts ASC LIMIT ?",
                (action, limit),
            ).fetchall()
        return [_row_to_event(r) for r in rows]

    def find_failures(self, limit: int = 100) -> List[Dict[str, Any]]:
        with self._lock:
            rows = self._conn.execute(
                "SELECT * FROM events WHERE state = 'failed' OR type = 'failure' ORDER BY ts DESC LIMIT ?",
                (limit,),
            ).fetchall()
        return [_row_to_event(r) for r in rows]

    def event_count(self, session_id: Optional[str] = None) -> int:
        with self._lock:
            if session_id:
                row = self._conn.execute(
                    "SELECT COUNT(*) FROM events WHERE session_id = ?", (session_id,)
                ).fetchone()
            else:
                row = self._conn.execute("SELECT COUNT(*) FROM events").fetchone()
        return row[0] if row else 0

    # ----------------------------------------------------------------
    # NODE STATES (snapshot — not journal)
    # ----------------------------------------------------------------
    def save_node_state(self, name: str, state: Dict[str, Any]) -> bool:
        try:
            with self._lock:
                self._conn.execute("""
                    INSERT OR REPLACE INTO node_states
                    (name, state, cpu_load, memory_load, temp, instability,
                     active_jobs, max_jobs, last_seen, labels, updated_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    name,
                    state.get("state", "online"),
                    float(state.get("cpu_load", 0)),
                    float(state.get("memory_load", 0)),
                    float(state.get("temp", 0)),
                    float(state.get("instability", 0)),
                    int(state.get("active_jobs", 0)),
                    int(state.get("max_jobs", 0)),
                    float(state.get("last_seen", 0)),
                    json.dumps(state.get("labels", {})),
                    time.time(),
                ))
                self._conn.commit()
            return True
        except Exception:
            return False

    def get_node_state(self, name: str) -> Optional[Dict[str, Any]]:
        with self._lock:
            row = self._conn.execute(
                "SELECT * FROM node_states WHERE name = ?", (name,)
            ).fetchone()
        return _row_to_node_state(row) if row else None

    def list_node_states(self) -> List[Dict[str, Any]]:
        with self._lock:
            rows = self._conn.execute(
                "SELECT * FROM node_states ORDER BY name"
            ).fetchall()
        return [_row_to_node_state(r) for r in rows]

    # ----------------------------------------------------------------
    # MAINTENANCE
    # ----------------------------------------------------------------
    def vacuum(self):
        with self._lock:
            self._conn.execute("VACUUM")
            self._conn.commit()

    def close(self):
        with self._lock:
            self._conn.close()


# ----------------------------------------------------------------
# Row converters
# ----------------------------------------------------------------
def _row_to_build(row: sqlite3.Row) -> Dict[str, Any]:
    d = dict(row)
    try:
        d["actions"] = json.loads(d.get("actions") or "[]")
    except (json.JSONDecodeError, TypeError):
        d["actions"] = []
    return d


def _row_to_event(row: sqlite3.Row) -> Dict[str, Any]:
    payload = row["payload"]
    try:
        return json.loads(payload)
    except (json.JSONDecodeError, TypeError):
        return {"type": row["type"], "timestamp": row["ts"], "node": row["node"],
                "action": row["action"], "state": row["state"]}


def _row_to_node_state(row: sqlite3.Row) -> Dict[str, Any]:
    d = dict(row)
    try:
        d["labels"] = json.loads(d.get("labels") or "{}")
    except (json.JSONDecodeError, TypeError):
        d["labels"] = {}
    return d


# ----------------------------------------------------------------
# Process-wide singleton
# ----------------------------------------------------------------
STORAGE = Storage()