#!/usr/bin/env python3
"""
Migration: Add combat effect fields to player_status_effects table.
Adds effect_type, value, persist_after_combat, and source columns.
"""
import asyncio
import os
import sys

# Add parent directory to path
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy import text

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

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


async def run_migration():
    engine = create_async_engine(DATABASE_URL, echo=True)
    
    async with engine.begin() as conn:
        print("Adding new columns to player_status_effects table...")
        
        # Add effect_type column (damage, buff, debuff)
        try:
            await conn.execute(text("""
                ALTER TABLE player_status_effects 
                ADD COLUMN IF NOT EXISTS effect_type VARCHAR(20) DEFAULT 'damage'
            """))
            print("✓ Added effect_type column")
        except Exception as e:
            print(f"Note: effect_type column may already exist: {e}")
        
        # Add value column (generic value for effect - damage amount, buff %, etc.)
        # This replaces/supplements damage_per_tick for more flexibility
        try:
            await conn.execute(text("""
                ALTER TABLE player_status_effects 
                ADD COLUMN IF NOT EXISTS value INTEGER DEFAULT 0
            """))
            print("✓ Added value column")
        except Exception as e:
            print(f"Note: value column may already exist: {e}")
        
        # Add persist_after_combat column
        try:
            await conn.execute(text("""
                ALTER TABLE player_status_effects 
                ADD COLUMN IF NOT EXISTS persist_after_combat BOOLEAN DEFAULT FALSE
            """))
            print("✓ Added persist_after_combat column")
        except Exception as e:
            print(f"Note: persist_after_combat column may already exist: {e}")
        
        # Add source column to track where effect came from
        try:
            await conn.execute(text("""
                ALTER TABLE player_status_effects 
                ADD COLUMN IF NOT EXISTS source VARCHAR(50) DEFAULT NULL
            """))
            print("✓ Added source column")
        except Exception as e:
            print(f"Note: source column may already exist: {e}")
        
        # Create index on persist_after_combat for background task queries
        try:
            await conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_status_effects_persist 
                ON player_status_effects(persist_after_combat) 
                WHERE persist_after_combat = TRUE
            """))
            print("✓ Created persist_after_combat index")
        except Exception as e:
            print(f"Note: Index may already exist: {e}")
        
        print("\n✓ Migration completed successfully!")
    
    await engine.dispose()


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