"""Read legacy-layout games from a SQLite file (opened read-only)."""

from pathlib import Path
import sqlite3

from server.database.compact.games import GAME_COLUMNS
from server.database.compact.records import GameBundle

from .convert import build_legacy_bundle, parse_json

_TURN_COLUMNS = {
  "turn": "turn", "observed_at": "observed_at", "my_move": "my_move",
  "my_thinking": "my_thinking_json", "board_state": "board_state_json",
  "food": "food_json", "hazards": "hazards_json",
}
_SNAKE_COLUMNS = {
  "snake_id": "snake_id", "turn": "turn", "snake_name": "snake_name",
  "health": "health", "length": "length", "head_x": "head_x", "head_y": "head_y",
  "body": "body_json", "is_you": "is_you", "latency": "latency",
}

class LegacySqliteReader:
  def __init__(self, path:str|Path):
    self.path = Path(path)
    self._connection = sqlite3.connect(f"file:{self.path}?mode=ro", uri=True, timeout=60)
    self._connection.row_factory = sqlite3.Row
    self._connection.execute("PRAGMA query_only = ON")

  def __enter__(self) -> "LegacySqliteReader":
    return self

  def __exit__(self, *exc) -> None:
    self.close()

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

  def _columns(self, table:str) -> set[str]:
    return {row[1] for row in self._connection.execute(f"PRAGMA table_info({table})")}

  def _select(self, table:str, mapping:dict[str, str]) -> str:
    available = self._columns(table)
    return ", ".join(
      f"{source} AS {name}" if source in available else f"NULL AS {name}"
      for name, source in mapping.items()
    )

  def has_legacy_tables(self) -> bool:
    return bool(self._columns("turns")) and bool(self._columns("snake_turns"))

  def game_ids(self, since:str|None=None) -> list[str]:
    if since:
      rows = self._connection.execute(
        "SELECT game_id FROM games WHERE started_at >= ? ORDER BY started_at, game_id", (since,),
      )
    else:
      rows = self._connection.execute("SELECT game_id FROM games ORDER BY started_at, game_id")
    return [row[0] for row in rows]

  def read_bundle(self, game_id:str) -> GameBundle|None:
    available = self._columns("games")
    selected = ", ".join(
      column if column in available
      else "quality_reasons_json AS quality_reasons" if column == "quality_reasons" and "quality_reasons_json" in available
      else f"NULL AS {column}"
      for column in GAME_COLUMNS
    )
    game = self._connection.execute(f"SELECT {selected} FROM games WHERE game_id = ?", (game_id,)).fetchone()
    if game is None:
      return None
    game = dict(game)
    game["quality_reasons"] = parse_json(game.get("quality_reasons"))

    identities = []
    snake_columns = self._columns("game_snakes")
    if snake_columns:
      customizations = "customizations_json" if "customizations_json" in snake_columns else "NULL"
      identities = [
        dict(row) for row in self._connection.execute(f"""
          SELECT snake_id, snake_name, is_you, {customizations} AS customizations
          FROM game_snakes WHERE game_id = ?
          """, (game_id,))
      ]
    turns = [
      dict(row) for row in self._connection.execute(
        f"SELECT {self._select('turns', _TURN_COLUMNS)} FROM turns WHERE game_id = ?", (game_id,),
      )
    ]
    snakes = [
      dict(row) for row in self._connection.execute(
        f"SELECT {self._select('snake_turns', _SNAKE_COLUMNS)} FROM snake_turns WHERE game_id = ?", (game_id,),
      )
    ]
    return build_legacy_bundle(game, identities, turns, snakes)

  def turn_counts(self) -> dict[str, int]:
    return {
      row[0]: int(row[1])
      for row in self._connection.execute("SELECT game_id, COUNT(*) FROM turns GROUP BY game_id")
    }