import os
import sys
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), "..", "..", "..")))
import sqlite3
import tempfile
from server.world.game_design_matrix_service import GameDesignMatrixService

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

    try:
        conn = sqlite3.connect(temp_db)
        conn.execute('''
            CREATE TABLE story_acts (
                act_id TEXT PRIMARY KEY,
                act_number INTEGER NOT NULL,
                title TEXT NOT NULL,
                level_min INTEGER NOT NULL,
                level_max INTEGER NOT NULL,
                primary_zone_id TEXT NOT NULL,
                climax_boss_id TEXT NOT NULL
            )
        ''')
        conn.execute("INSERT INTO story_acts VALUES ('ACT_I', 1, 'Act 1', 1, 20, 'z1', 'b1')")
        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()

        # Instantiate service on the pre-existing database
        service = GameDesignMatrixService(db_path=temp_db)
        
        # Test 1: Does initialization drop the table and recreate it?
        with service._get_connection() as c:
            cols = [r[1] for r in c.execute("PRAGMA table_info(progression_benchmarks)").fetchall()]
            cnt = c.execute("SELECT COUNT(*) FROM progression_benchmarks").fetchone()[0]
        
        print(f"Post-init columns ({len(cols)}): {cols}")
        print(f"Post-init progression_benchmarks row count: {cnt}")
        
        # Test 2: Standard startup call
        status = service.seed_canonical_data(force=False)
        print(f"seed_canonical_data(force=False) result: {status}")

        with service._get_connection() as c:
            cnt_after = c.execute("SELECT COUNT(*) FROM progression_benchmarks").fetchone()[0]
        print(f"Post-seeding progression_benchmarks row count: {cnt_after}")
        
        bench = service.get_level_progression_benchmark(1)
        print(f"Benchmark level 1: {bench}")

        service.close()
    finally:
        if os.path.exists(temp_db):
            os.remove(temp_db)

if __name__ == "__main__":
    test_migration_old_schema()