"""MCP metadata, separate from the schema-hashed product databases and their backups."""
import hashlib
import json
import os
import secrets
import sqlite3
import time
from pathlib import Path


def encode(value):
    return json.dumps(value, sort_keys=True, ensure_ascii=True, separators=(",", ":"))


def digest(value):
    return hashlib.sha256(encode(value).encode()).hexdigest()


class Store:
    def __init__(self, path=None):
        self.path = Path(path or os.getenv("FIREGEX_MCP_DB", "db/mcp/control.sqlite3"))
        self.conn = None

    def open(self):
        if self.conn is not None:
            return
        self.path.parent.mkdir(parents=True, exist_ok=True)
        self.conn = sqlite3.connect(self.path, timeout=10)
        self.conn.row_factory = sqlite3.Row
        self.conn.executescript("""
            CREATE TABLE IF NOT EXISTS tokens (
                id TEXT PRIMARY KEY, name TEXT NOT NULL, hash TEXT UNIQUE NOT NULL,
                role TEXT NOT NULL, expires REAL NOT NULL, revoked INTEGER NOT NULL DEFAULT 0
            );
            CREATE TABLE IF NOT EXISTS records (
                kind TEXT NOT NULL, id TEXT NOT NULL, created REAL NOT NULL,
                value TEXT NOT NULL, PRIMARY KEY(kind, id)
            );
            CREATE TABLE IF NOT EXISTS requests (
                actor TEXT NOT NULL, id TEXT NOT NULL, fingerprint TEXT NOT NULL,
                change_id TEXT NOT NULL, PRIMARY KEY(actor, id)
            );
        """)
        self.conn.commit()
        os.chmod(self.path, 0o600)

    def close(self):
        if self.conn is not None:
            self.conn.close()
            self.conn = None

    def token_create(self, name, role="operator", hours=168):
        if role not in {"observer", "operator", "admin"} or not 0 < hours <= 8760:
            raise ValueError("Use observer/operator/admin and a TTL between 0 and 8760 hours")
        raw = "fgx_" + secrets.token_urlsafe(32)
        token_id = secrets.token_hex(8)
        with self.conn:
            self.conn.execute("INSERT INTO tokens VALUES (?, ?, ?, ?, ?, 0)",
                              (token_id, name, hashlib.sha256(raw.encode()).hexdigest(),
                               role, time.time() + hours * 3600))
        return {"id": token_id, "name": name, "role": role, "token": raw}

    def token_verify(self, raw):
        row = self.conn.execute(
            "SELECT id,name,role,expires FROM tokens WHERE hash=? AND revoked=0 AND expires>?",
            (hashlib.sha256(raw.encode()).hexdigest(), time.time()),
        ).fetchone()
        return dict(row) if row else None

    def token_list(self):
        return [dict(r) for r in self.conn.execute("SELECT id,name,role,expires,revoked FROM tokens")]

    def token_revoke(self, token_id):
        with self.conn:
            cur = self.conn.execute("UPDATE tokens SET revoked=1 WHERE id=?", (token_id,))
        return bool(cur.rowcount)

    def put(self, kind, record_id, value):
        with self.conn:
            self.conn.execute("INSERT INTO records VALUES (?, ?, ?, ?) ON CONFLICT(kind,id) "
                              "DO UPDATE SET value=excluded.value", (kind, record_id, time.time(), encode(value)))

    def get(self, kind, record_id):
        row = self.conn.execute("SELECT value FROM records WHERE kind=? AND id=?", (kind, record_id)).fetchone()
        return json.loads(row[0]) if row else None

    def list(self, kind, limit=100, offset=0):
        return [json.loads(r[0]) for r in self.conn.execute(
            "SELECT value FROM records WHERE kind=? ORDER BY created DESC,id LIMIT ? OFFSET ?",
            (kind, limit, offset))]

    def delete(self, kind, record_id):
        with self.conn:
            self.conn.execute("DELETE FROM records WHERE kind=? AND id=?", (kind, record_id))

    def pending(self, *states):
        return [json.loads(r[0]) for r in self.conn.execute(
            "SELECT value FROM records WHERE kind='change' AND json_extract(value,'$.status') IN ("
            + ",".join("?" for _ in states) + ") ORDER BY created", states)]

    def expire_artifacts(self):
        with self.conn:
            self.conn.execute("DELETE FROM records WHERE kind='artifact' AND json_extract(value,'$.expires_at') < ?",
                              (time.time(),))

    def request_get(self, actor, request_id):
        row = self.conn.execute("SELECT fingerprint,change_id FROM requests WHERE actor=? AND id=?",
                                (actor, request_id)).fetchone()
        return dict(row) if row else None

    def request_put(self, actor, request_id, fingerprint, change_id):
        with self.conn:
            self.conn.execute("INSERT INTO requests VALUES (?, ?, ?, ?)",
                              (actor, request_id, fingerprint, change_id))

    def queue_change(self, change, request):
        """Persist the work and its retry key in the same SQLite transaction."""
        with self.conn:
            self.conn.execute("INSERT INTO records VALUES ('change', ?, ?, ?)",
                              (change["id"], time.time(), encode(change)))
            if request:
                self.conn.execute("INSERT INTO requests VALUES (?, ?, ?, ?)",
                                  (request["actor"], request["id"], request["fingerprint"], change["id"]))