"""
Migration script to add from_wandering_enemy column to active_combats table.
Run this once to update the database schema.
"""
import asyncio
import os
from dotenv import load_dotenv
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy import text

load_dotenv()

DB_USER = os.getenv("POSTGRES_USER")
DB_PASS = os.getenv("POSTGRES_PASSWORD")
DB_NAME = os.getenv("POSTGRES_DB")
DB_HOST = os.getenv("POSTGRES_HOST")
DB_PORT = os.getenv("POSTGRES_PORT")

DATABASE_URL = f"postgresql+psycopg://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

async def migrate():
    """Add from_wandering_enemy column to active_combats table."""
    engine = create_async_engine(DATABASE_URL)
    
    async with engine.begin() as conn:
        # Check if column already exists
        result = await conn.execute(text("""
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_name='active_combats' 
            AND column_name='from_wandering_enemy'
        """))
        
        exists = result.fetchone()
        
        if exists:
            print("✅ Column 'from_wandering_enemy' already exists. No migration needed.")
        else:
            print("🔧 Adding 'from_wandering_enemy' column to active_combats table...")
            
            # Add the column with default value False
            await conn.execute(text("""
                ALTER TABLE active_combats 
                ADD COLUMN from_wandering_enemy BOOLEAN DEFAULT FALSE
            """))
            
            print("✅ Column added successfully!")
        
        # Also check and create wandering_enemies table if it doesn't exist
        result = await conn.execute(text("""
            SELECT table_name 
            FROM information_schema.tables 
            WHERE table_name='wandering_enemies'
        """))
        
        table_exists = result.fetchone()
        
        if table_exists:
            print("✅ Table 'wandering_enemies' already exists.")
        else:
            print("🔧 Creating 'wandering_enemies' table...")
            
            await conn.execute(text("""
                CREATE TABLE wandering_enemies (
                    id SERIAL PRIMARY KEY,
                    npc_id VARCHAR NOT NULL,
                    location_id VARCHAR NOT NULL,
                    spawn_timestamp FLOAT NOT NULL,
                    despawn_timestamp FLOAT NOT NULL
                )
            """))
            
            print("✅ Table 'wandering_enemies' created successfully!")
    
    await engine.dispose()
    print("🎉 Migration complete!")

if __name__ == "__main__":
    asyncio.run(migrate())