"""
FreeExile Character Stat Formula Persistence Service.
Manages SQLite storage and historical auditing for stat calculation ASTs.
Table: character_stat_calculations in data/character_stat_formulas.db or :memory:.
Strict typing and context-managed transactions following Elite Standards 2026.
"""

from __future__ import annotations
import os
import json
import time
import sqlite3
from contextlib import contextmanager
from typing import Dict, List, Optional, Any, Generator

PROJECT_ROOT = os.path.abspath(os.path.join(os.path.dirname(__file__), "../.."))
DEFAULT_DB_PATH = os.path.join(PROJECT_ROOT, "data", "character_stat_formulas.db")

DDL_STAT_CALCULATIONS = """
CREATE TABLE IF NOT EXISTS character_stat_calculations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    player_id TEXT NOT NULL,
    calculation_id TEXT NOT NULL UNIQUE,
    timestamp REAL NOT NULL,
    formula_ast_json TEXT NOT NULL,
    final_stats_json TEXT NOT NULL,
    context_tags_json TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (DATETIME('now'))
);
CREATE INDEX IF NOT EXISTS idx_stat_calc_player ON character_stat_calculations(player_id);
CREATE INDEX IF NOT EXISTS idx_stat_calc_timestamp ON character_stat_calculations(timestamp);
"""


class FormulaPersistenceService:
    """Authoritative persistence service for character stat calculation ASTs and final stats."""

    def __init__(self, db_path: Optional[str] = None) -> None:
        if db_path is None:
            self.db_path = DEFAULT_DB_PATH
        else:
            self.db_path = db_path

        self._is_memory = (self.db_path == ":memory:")
        self._memory_conn: Optional[sqlite3.Connection] = None

        if self._is_memory:
            self._memory_conn = sqlite3.connect(":memory:")
            self._memory_conn.row_factory = sqlite3.Row
            self._init_db(self._memory_conn)
        else:
            os.makedirs(os.path.dirname(self.db_path), exist_ok=True)
            with self._get_connection() as conn:
                self._init_db(conn)

    def _init_db(self, conn: sqlite3.Connection) -> None:
        """Initializes tables and pragmas."""
        if not self._is_memory:
            conn.execute("PRAGMA journal_mode = WAL;")
        conn.execute("PRAGMA synchronous = NORMAL;")
        conn.execute("PRAGMA foreign_keys = ON;")
        conn.executescript(DDL_STAT_CALCULATIONS)

    @contextmanager
    def _get_connection(self) -> Generator[sqlite3.Connection, None, None]:
        """Provides a database connection with automatic commit and rollback."""
        if self._is_memory and self._memory_conn is not None:
            yield self._memory_conn
            self._memory_conn.commit()
        else:
            conn = sqlite3.connect(self.db_path, timeout=10.0)
            conn.row_factory = sqlite3.Row
            try:
                yield conn
                conn.commit()
            except Exception:
                conn.rollback()
                raise
            finally:
                conn.close()

    def save_calculation(
        self,
        player_id: str,
        calculation_id: str,
        formula_ast: Dict[str, Any],
        final_stats: Dict[str, float],
        context_tags: Any = None,
        timestamp: Optional[float] = None,
    ) -> int:
        """Persists a stat calculation AST and resulting stats. Returns record id."""
        ts = time.time() if timestamp is None else timestamp
        ast_json = json.dumps(formula_ast, ensure_ascii=True)
        stats_json = json.dumps(final_stats, ensure_ascii=True)
        tags_json = json.dumps(context_tags if context_tags is not None else [], ensure_ascii=True)

        query = """
        INSERT OR REPLACE INTO character_stat_calculations
        (player_id, calculation_id, timestamp, formula_ast_json, final_stats_json, context_tags_json)
        VALUES (?, ?, ?, ?, ?, ?);
        """
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (player_id, calculation_id, ts, ast_json, stats_json, tags_json))
            return int(cursor.lastrowid or 0)

    def get_calculation(self, calculation_id: str) -> Optional[Dict[str, Any]]:
        """Retrieves a single calculation record by calculation_id."""
        query = """
        SELECT id, player_id, calculation_id, timestamp, formula_ast_json,
               final_stats_json, context_tags_json, created_at
        FROM character_stat_calculations
        WHERE calculation_id = ?;
        """
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (calculation_id,))
            row = cursor.fetchone()
            if row is None:
                return None
            return {
                "id": row["id"],
                "player_id": row["player_id"],
                "calculation_id": row["calculation_id"],
                "timestamp": row["timestamp"],
                "formula_ast": json.loads(row["formula_ast_json"]),
                "final_stats": json.loads(row["final_stats_json"]),
                "context_tags": json.loads(row["context_tags_json"]),
                "created_at": row["created_at"],
            }

    def get_player_calculations(self, player_id: str, limit: int = 10) -> List[Dict[str, Any]]:
        """Retrieves historical calculations for a player, newest first."""
        query = """
        SELECT id, player_id, calculation_id, timestamp, formula_ast_json,
               final_stats_json, context_tags_json, created_at
        FROM character_stat_calculations
        WHERE player_id = ?
        ORDER BY timestamp DESC
        LIMIT ?;
        """
        results: List[Dict[str, Any]] = []
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (player_id, limit))
            for row in cursor.fetchall():
                results.append({
                    "id": row["id"],
                    "player_id": row["player_id"],
                    "calculation_id": row["calculation_id"],
                    "timestamp": row["timestamp"],
                    "formula_ast": json.loads(row["formula_ast_json"]),
                    "final_stats": json.loads(row["final_stats_json"]),
                    "context_tags": json.loads(row["context_tags_json"]),
                    "created_at": row["created_at"],
                })
        return results

    def close(self) -> None:
        """Closes any persistent memory connection."""
        if self._memory_conn is not None:
            self._memory_conn.close()
            self._memory_conn = None