"""SQLite cache of raw provider responses keyed by (provider, model, prompt_version, sha256(system+user)).

A cache hit replays the *recorded* text; it is counted separately (`cached_hits`) so a run can report
how many decisions were genuinely online. The same store serves as the frozen decision library."""
from __future__ import annotations

import json
import sqlite3
import threading
import time
from pathlib import Path


class DecisionCache:
    def __init__(self, path: str | Path) -> None:
        self.path = Path(path)
        self.path.parent.mkdir(parents=True, exist_ok=True)
        self._lock = threading.Lock()
        self._conn = sqlite3.connect(str(self.path), check_same_thread=False, timeout=60.0)
        self._conn.execute("""CREATE TABLE IF NOT EXISTS decisions (
            key TEXT PRIMARY KEY, provider TEXT, model TEXT, prompt_version TEXT, prompt_id TEXT,
            system_prompt TEXT, user_prompt TEXT, response_text TEXT, usage_json TEXT, created REAL)""")
        self._conn.commit()

    @staticmethod
    def key(provider: str, model: str, prompt_version: str, system_prompt: str, user_prompt: str) -> str:
        import hashlib
        h = hashlib.sha256((provider + "|" + model + "|" + prompt_version + "|" + system_prompt + "|" + user_prompt).encode()).hexdigest()
        return h

    def get(self, key: str) -> tuple[str, dict] | None:
        with self._lock:
            row = self._conn.execute("SELECT response_text, usage_json FROM decisions WHERE key=?", (key,)).fetchone()
        if row is None:
            return None
        return row[0], json.loads(row[1] or "{}")

    def put(self, key: str, provider: str, model: str, prompt_version: str, prompt_id: str,
            system_prompt: str, user_prompt: str, response_text: str, usage: dict) -> None:
        with self._lock:
            self._conn.execute("INSERT OR REPLACE INTO decisions VALUES (?,?,?,?,?,?,?,?,?,?)",
                               (key, provider, model, prompt_version, prompt_id, system_prompt, user_prompt,
                                response_text, json.dumps(usage), time.time()))
            self._conn.commit()

    def count(self) -> int:
        with self._lock:
            return int(self._conn.execute("SELECT COUNT(*) FROM decisions").fetchone()[0])

    def close(self) -> None:
        self._conn.close()