"""SQLite-backed tenant assignments, isolated memory, and execution receipts."""

from __future__ import annotations

import hashlib
import json
import secrets
import sqlite3
from contextlib import contextmanager
from pathlib import Path
from typing import Any, Iterator

from .errors import AuthenticationError


def _derive_key(raw: str, salt: bytes) -> bytes:
    return hashlib.scrypt(
        raw.encode("utf-8"),
        salt=salt,
        n=2**14,
        r=8,
        p=1,
        dklen=32,
    )


class StateStore:
    def __init__(self, path: str | Path):
        self.path = Path(path)
        self.path.parent.mkdir(parents=True, exist_ok=True)
        self._init_schema()

    def _connect(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self.path, timeout=10)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA foreign_keys=ON")
        conn.execute("PRAGMA journal_mode=WAL")
        return conn

    @contextmanager
    def _session(self) -> Iterator[sqlite3.Connection]:
        conn = self._connect()
        try:
            with conn:
                yield conn
        finally:
            conn.close()

    def _init_schema(self) -> None:
        with self._session() as conn:
            conn.executescript(
                """
                CREATE TABLE IF NOT EXISTS tenants (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    key_salt BLOB NOT NULL,
                    key_hash BLOB NOT NULL,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                );
                CREATE TABLE IF NOT EXISTS assignments (
                    tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
                    card_id TEXT NOT NULL,
                    ordinal INTEGER NOT NULL,
                    PRIMARY KEY (tenant_id, card_id)
                );
                CREATE TABLE IF NOT EXISTS memory (
                    tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
                    key TEXT NOT NULL,
                    value TEXT NOT NULL,
                    provenance TEXT NOT NULL,
                    PRIMARY KEY (tenant_id, key)
                );
                CREATE TABLE IF NOT EXISTS receipts (
                    id TEXT PRIMARY KEY,
                    tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
                    body TEXT NOT NULL,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                );
                """
            )

    def create_tenant(self, tenant_id: str, name: str) -> str:
        raw_key = f"bp_{secrets.token_urlsafe(24)}"
        salt = secrets.token_bytes(16)
        digest = _derive_key(raw_key, salt)
        with self._session() as conn:
            conn.execute(
                "INSERT INTO tenants(id, name, key_salt, key_hash) VALUES (?, ?, ?, ?)",
                (tenant_id, name, salt, digest),
            )
        return raw_key

    def ensure_demo_tenant(self, tenant_id: str, name: str) -> None:
        with self._session() as conn:
            existing = conn.execute(
                "SELECT 1 FROM tenants WHERE id=?", (tenant_id,)
            ).fetchone()
            if existing:
                return
            salt = secrets.token_bytes(16)
            conn.execute(
                "INSERT INTO tenants(id, name, key_salt, key_hash) VALUES (?, ?, ?, ?)",
                (tenant_id, name, salt, _derive_key(secrets.token_urlsafe(24), salt)),
            )

    def tenant_exists(self, tenant_id: str) -> bool:
        with self._session() as conn:
            return (
                conn.execute("SELECT 1 FROM tenants WHERE id=?", (tenant_id,)).fetchone()
                is not None
            )

    def verify_tenant_key(self, tenant_id: str, raw_key: str) -> None:
        with self._session() as conn:
            row = conn.execute(
                "SELECT key_salt, key_hash FROM tenants WHERE id=?", (tenant_id,)
            ).fetchone()
        if not row:
            raise AuthenticationError("unknown tenant")
        supplied = _derive_key(raw_key, bytes(row["key_salt"]))
        if not secrets.compare_digest(supplied, bytes(row["key_hash"])):
            raise AuthenticationError("invalid tenant credential")

    def assign_cards(self, tenant_id: str, card_ids: list[str]) -> None:
        with self._session() as conn:
            conn.execute("DELETE FROM assignments WHERE tenant_id=?", (tenant_id,))
            conn.executemany(
                "INSERT INTO assignments(tenant_id, card_id, ordinal) VALUES (?, ?, ?)",
                [(tenant_id, card_id, index) for index, card_id in enumerate(card_ids)],
            )

    def card_ids_for(self, tenant_id: str) -> list[str]:
        with self._session() as conn:
            rows = conn.execute(
                "SELECT card_id FROM assignments WHERE tenant_id=? ORDER BY ordinal",
                (tenant_id,),
            ).fetchall()
        return [str(row["card_id"]) for row in rows]

    def put_memory(self, tenant_id: str, key: str, value: str, provenance: str) -> None:
        with self._session() as conn:
            conn.execute(
                """
                INSERT INTO memory(tenant_id, key, value, provenance)
                VALUES (?, ?, ?, ?)
                ON CONFLICT(tenant_id, key)
                DO UPDATE SET value=excluded.value, provenance=excluded.provenance
                """,
                (tenant_id, key, value, provenance),
            )

    def memory_for(self, tenant_id: str) -> dict[str, dict[str, str]]:
        with self._session() as conn:
            rows = conn.execute(
                "SELECT key, value, provenance FROM memory WHERE tenant_id=?",
                (tenant_id,),
            ).fetchall()
        return {
            str(row["key"]): {
                "value": str(row["value"]),
                "provenance": str(row["provenance"]),
            }
            for row in rows
        }

    def save_receipt(self, receipt_id: str, tenant_id: str, body: dict[str, Any]) -> None:
        with self._session() as conn:
            conn.execute(
                "INSERT INTO receipts(id, tenant_id, body) VALUES (?, ?, ?)",
                (
                    receipt_id,
                    tenant_id,
                    json.dumps(body, sort_keys=True, separators=(",", ":")),
                ),
            )

    def get_receipt(self, receipt_id: str, tenant_id: str) -> dict[str, Any] | None:
        with self._session() as conn:
            row = conn.execute(
                "SELECT body FROM receipts WHERE id=? AND tenant_id=?",
                (receipt_id, tenant_id),
            ).fetchone()
        return json.loads(row["body"]) if row else None

    def list_receipts(self, tenant_id: str, *, limit: int = 50, before: str | None = None) -> dict[str, Any]:
        """Stable newest-first pagination, scoped to one authenticated tenant."""
        if type(limit) is not int or not 1 <= limit <= 200:
            raise ValueError("receipt limit must be an integer from 1 to 200")
        with self._session() as conn:
            parameters: list[Any] = [tenant_id]
            condition = "tenant_id=?"
            if before is not None:
                cursor = conn.execute("SELECT rowid FROM receipts WHERE id=? AND tenant_id=?", (before, tenant_id)).fetchone()
                if cursor is None:
                    raise ValueError("receipt cursor not found for this tenant")
                condition += " AND rowid<?"
                parameters.append(cursor["rowid"])
            parameters.append(limit + 1)
            rows = conn.execute(f"SELECT id, body FROM receipts WHERE {condition} ORDER BY rowid DESC LIMIT ?", parameters).fetchall()
        page = rows[:limit]
        return {"receipts": [json.loads(row["body"]) for row in page], "next_cursor": page[-1]["id"] if len(rows) > limit else None}
