"""
Simulation test for seeder robustness and migration reseed fix.
Verifies the proposed code change for Worker M1.
"""

import os
import sys
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), "..", "..", "..")))
import sqlite3
import tempfile
from typing import Dict, Any

from server.world.game_design_matrix_service import GameDesignMatrixService
from server.world.game_design_matrix_seeder import seed_all_canonical_data
from server.world.game_design_matrix_types import ProgressionBenchmarkRow


def proposed_seed_canonical_data(service: GameDesignMatrixService, force: bool = False) -> Dict[str, Any]:
    """The proposed implementation of seed_canonical_data."""
    with service._get_connection() as conn:
        cur = conn.cursor()
        if not force:
            cur.execute("SELECT COUNT(*) as cnt FROM story_acts")
            acts_cnt = cur.fetchone()["cnt"]
            cur.execute("SELECT COUNT(*) as cnt FROM progression_benchmarks")
            prog_cnt = cur.fetchone()["cnt"]
            if acts_cnt > 0 and prog_cnt > 0:
                return {"status": "already_seeded"}
        return seed_all_canonical_data(conn)


def test_simulation_migration_reseed():
    with tempfile.NamedTemporaryFile(suffix='.db', delete=False) as f:
        temp_db = f.name

    try:
        # 1. Create a pre-existing DB with old schema (< 13 columns) and pre-seeded story_acts
        conn = sqlite3.connect(temp_db)
        from server.world.game_design_matrix_schema import SQL_CREATE_TABLES
        conn.executescript(SQL_CREATE_TABLES)
        conn.execute("INSERT OR REPLACE INTO story_acts VALUES ('ACT_I_HUNGER', 1, 'Act 1', 'Sub', 1, 20, 'zone_tang_kiem_nhai', 'boss_strand_executioner', 'Boss', 'Desc')")
        conn.execute("DROP TABLE IF EXISTS progression_benchmarks")
        conn.execute('''
            CREATE TABLE progression_benchmarks (
                level INTEGER PRIMARY KEY,
                target_exp INTEGER NOT NULL,
                player_base_hp REAL NOT NULL,
                player_benchmark_dps REAL NOT NULL,
                monster_base_hp REAL NOT NULL,
                monster_base_dps REAL NOT NULL,
                max_affix_tier_allowed INTEGER NOT NULL
            )
        ''')
        conn.execute("INSERT INTO progression_benchmarks VALUES (1, 0, 100.0, 20.0, 80.0, 12.0, 15)")
        conn.commit()
        conn.close()

        # 2. Initialize service -> triggers _check_and_migrate_schema -> drops stale table and recreates 13-col table
        service = GameDesignMatrixService(db_path=temp_db)

        with service._get_connection() as c:
            cols = [r[1] for r in c.execute("PRAGMA table_info(progression_benchmarks)").fetchall()]
            cnt_before = c.execute("SELECT COUNT(*) FROM progression_benchmarks").fetchone()[0]
        
        assert len(cols) == 13, f"Expected 13 columns, got {len(cols)}"
        assert cnt_before == 0, f"Expected 0 rows post-migration, got {cnt_before}"

        # 3. Call proposed seed_canonical_data(force=False)
        result = proposed_seed_canonical_data(service, force=False)
        print(f"Result of proposed seed_canonical_data(force=False): {result.get('status', 'seeded_ok')}")

        with service._get_connection() as c:
            cnt_after = c.execute("SELECT COUNT(*) FROM progression_benchmarks").fetchone()[0]
        
        print(f"Row count after proposed seed: {cnt_after}")
        assert cnt_after == 100, f"Expected 100 rows, got {cnt_after}"

        # 4. Verify benchmark query succeeds
        bench = service.get_level_progression_benchmark(1)
        assert bench is not None
        assert isinstance(bench, ProgressionBenchmarkRow)
        assert bench.level == 1
        assert bench.exp_to_next_level == 600
        assert bench.cumulative_exp == 0
        assert bench.death_penalty_ratio == 0.0

        # 5. Call again with force=False -> should now return {"status": "already_seeded"}
        second_result = proposed_seed_canonical_data(service, force=False)
        assert second_result == {"status": "already_seeded"}, f"Expected already_seeded, got {second_result}"
        print("Second call correctly returned already_seeded!")

        print("ALL SIMULATION CHECKS PASSED 100%!")
        service.close()
    finally:
        if os.path.exists(temp_db):
            os.remove(temp_db)


if __name__ == "__main__":
    test_simulation_migration_reseed()