import sqlite3
import tempfile
import unittest
from pathlib import Path

from server.database.backend.PostgresqlGameplayBackend import PostgresqlGameplayBackend

class TestPostgresqlGameplayMigration(unittest.TestCase):
  def _create_source(self, path:Path, winner_column:str, winner_value:str|None) -> None:
    with sqlite3.connect(path) as connection:
      connection.executescript(f"""
        CREATE TABLE games (
          game_id TEXT PRIMARY KEY, started_at TEXT NOT NULL, ended_at TEXT,
          width INTEGER, height INTEGER, source TEXT, map_name TEXT,
          ruleset_name TEXT, ruleset_version TEXT, your_snake_id TEXT,
          your_snake_name TEXT, your_snake_type TEXT, your_snake_version TEXT,
          game_type TEXT, {winner_column} TEXT, winner_you INTEGER,
          final_turn INTEGER, status TEXT
        );
        CREATE TABLE turns (
          game_id TEXT, turn INTEGER, observed_at TEXT, my_move TEXT,
          my_thinking_json TEXT, board_state_json TEXT, snakes_json TEXT,
          you_json TEXT, food_json TEXT, hazards_json TEXT
        );
        CREATE TABLE snake_turns (
          game_id TEXT, turn INTEGER, snake_id TEXT, snake_name TEXT,
          health INTEGER, length INTEGER, head_x INTEGER, head_y INTEGER,
          body_json TEXT, is_you INTEGER, inferred_move TEXT, latency TEXT
        );
      """)
      connection.execute(
        f"""INSERT INTO games (
          game_id, started_at, {winner_column}, winner_you, final_turn, status
        ) VALUES (?, ?, ?, ?, ?, ?)""",
        ("game-1", "2026-08-01T10:00:00+00:00", winner_value, 1, 4, "finished"),
      )

  def test_reads_current_winner_name_schema(self):
    with tempfile.TemporaryDirectory() as temp_dir:
      path = Path(temp_dir) / "gameplay.sqlite3"
      self._create_source(path, "winner_name", "Prism")
      backend = PostgresqlGameplayBackend("postgresql://example", sqlite_migration_path=str(path))

      games, turns, snake_turns = backend._read_sqlite_data_sync(str(path))

      self.assertEqual(games[0]["winner_name"], "Prism")
      self.assertEqual(turns, [])
      self.assertEqual(snake_turns, [])
      self.assertEqual(backend._migrated_winner_name(games[0]["winner_name"]), "Prism")

  def test_reads_legacy_winner_names_json_schema(self):
    with tempfile.TemporaryDirectory() as temp_dir:
      path = Path(temp_dir) / "gameplay.sqlite3"
      self._create_source(path, "winner_names_json", '["Prism"]')
      backend = PostgresqlGameplayBackend("postgresql://example", sqlite_migration_path=str(path))

      games, _, _ = backend._read_sqlite_data_sync(str(path))

      self.assertEqual(
        backend._migrated_winner_name(games[0]["winner_name"]),
        "Prism",
      )

if __name__ == "__main__":
  unittest.main()