"""A read-only SQL gateway for the model (lessons 07 and 16).

The model writes SQL; this module makes sure that SQL can only READ, only a bounded amount, and only
for a bounded time. Three independent layers, because any one of them might have a gap:
  1. the database file is opened read-only (SQLite URI mode=ro)
  2. an authorizer callback rejects every operation except SELECT/READ/FUNCTION
  3. results are capped at MAX_ROWS rows and queries are cut off after MAX_SECONDS
"""

import sqlite3
import time
from dataclasses import dataclass
from pathlib import Path

DEFAULT_DB = Path(__file__).resolve().parents[3] / "sql" / "project" / "retail.db"
MAX_ROWS = 50
MAX_SECONDS = 2.0

_ALLOWED = {sqlite3.SQLITE_SELECT, sqlite3.SQLITE_READ, sqlite3.SQLITE_FUNCTION,
            sqlite3.SQLITE_RECURSIVE}          # recursive CTEs only read


class QueryRejected(ValueError):
    """The query was refused before or during execution. The message is safe to show the model."""


@dataclass
class QueryResult:
    columns: list[str]
    rows: list[tuple]
    truncated: bool

    def as_text(self) -> str:
        """Compact, token-cheap table for the model (lesson 13: keep tool outputs small)."""
        lines = [" | ".join(self.columns)]
        lines += [" | ".join("" if v is None else str(v) for v in row) for row in self.rows]
        if self.truncated:
            lines.append(f"... (truncated to {MAX_ROWS} rows; aggregate or add LIMIT)")
        return "\n".join(lines)


class ReadOnlyDB:
    def __init__(self, path: Path | str = DEFAULT_DB) -> None:
        self.path = Path(path)
        if not self.path.exists():
            raise FileNotFoundError(f"database not found: {self.path} (build it with sql/project/build_data.py)")
        self._con = sqlite3.connect(f"file:{self.path.as_posix()}?mode=ro", uri=True, check_same_thread=False)
        self._con.set_authorizer(self._authorize)

    @staticmethod
    def _authorize(action, *_args):
        return sqlite3.SQLITE_OK if action in _ALLOWED else sqlite3.SQLITE_DENY

    def schema(self) -> str:
        """CREATE statements, so the model knows the tables and columns (a describe_tables tool)."""
        rows = self._con.execute("SELECT sql FROM sqlite_master WHERE type = 'table' ORDER BY name").fetchall()
        return "\n\n".join(r[0] for r in rows)

    def all_products(self) -> list[tuple]:
        """Trusted internal read for indexing (not exposed to the model, so no row cap)."""
        return self._con.execute(
            "SELECT product_id, product_name, category, sub_category FROM products ORDER BY product_id").fetchall()

    def query(self, sql: str) -> QueryResult:
        text = sql.strip().rstrip(";").strip()
        if not text:
            raise QueryRejected("empty query")
        if ";" in text:
            raise QueryRejected("only one statement is allowed")
        if not text.lower().startswith(("select", "with")):
            raise QueryRejected("only SELECT (or WITH ... SELECT) queries are allowed")

        deadline = time.monotonic() + MAX_SECONDS
        self._con.set_progress_handler(lambda: 1 if time.monotonic() > deadline else 0, 10_000)
        try:
            cur = self._con.execute(text)
            rows = cur.fetchmany(MAX_ROWS + 1)
        except sqlite3.DatabaseError as e:
            msg = str(e)
            if "interrupted" in msg:
                msg = f"query took longer than {MAX_SECONDS}s"
            raise QueryRejected(msg) from None
        finally:
            self._con.set_progress_handler(None, 0)
        columns = [d[0] for d in cur.description]
        return QueryResult(columns, rows[:MAX_ROWS], truncated=len(rows) > MAX_ROWS)