"""SQLite-backed persistence for saved games."""
from __future__ import annotations

import sqlite3
from pathlib import Path

from schema import CanvasGame

_DEFAULT_DB = Path(__file__).parent / "games.db"


class GameStore:
    def __init__(self, db_path: Path = _DEFAULT_DB) -> None:
        self.db_path = db_path
        self._init_db()

    def _init_db(self) -> None:
        with sqlite3.connect(self.db_path) as conn:
            conn.execute(
                """
                CREATE TABLE IF NOT EXISTS games (
                    id         INTEGER PRIMARY KEY AUTOINCREMENT,
                    name       TEXT    NOT NULL,
                    config_json TEXT   NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
                """
            )

    def save_game(self, name: str, game: CanvasGame) -> int:
        with sqlite3.connect(self.db_path) as conn:
            cur = conn.execute(
                "INSERT INTO games (name, config_json) VALUES (?, ?)",
                (name, game.model_dump_json()),
            )
            return cur.lastrowid  # type: ignore[return-value]

    def load_game(self, game_id: int) -> CanvasGame:
        with sqlite3.connect(self.db_path) as conn:
            row = conn.execute(
                "SELECT config_json FROM games WHERE id = ?", (game_id,)
            ).fetchone()
        if not row:
            raise KeyError(f"Game {game_id} not found")
        return CanvasGame.model_validate_json(row[0])

    def list_games(self) -> list[dict]:
        with sqlite3.connect(self.db_path) as conn:
            rows = conn.execute(
                "SELECT id, name, created_at FROM games ORDER BY created_at DESC"
            ).fetchall()
        return [{"id": r[0], "name": r[1], "created_at": r[2]} for r in rows]

    def delete_game(self, game_id: int) -> None:
        with sqlite3.connect(self.db_path) as conn:
            conn.execute("DELETE FROM games WHERE id = ?", (game_id,))