import asyncio
import os
import sys
import json
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
from sqlalchemy import text

# Add parent directory to path to allow imports
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from api.items import ItemsManager

# Database connection
DB_USER = os.getenv("POSTGRES_USER", "postgres")
DB_PASS = os.getenv("POSTGRES_PASSWORD", "postgres")
DB_NAME = os.getenv("POSTGRES_DB", "echoes_of_the_ashes")
DB_HOST = os.getenv("POSTGRES_HOST", "localhost")
DB_PORT = os.getenv("POSTGRES_PORT", "5432")

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

async def backfill_unique_stats():
    print(f"Connecting to database at {DB_HOST}...")
    engine = create_async_engine(DATABASE_URL, echo=False)
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
    
    # Load items
    print("Loading item definitions...")
    items_manager = ItemsManager(gamedata_path="gamedata")
    
    async with async_session() as session:
        # Get all unique items
        print("Fetching unique items...")
        result = await session.execute(text("SELECT id, item_id, unique_stats, durability, max_durability, tier FROM unique_items"))
        unique_items = result.fetchall()
        
        print(f"Found {len(unique_items)} unique items.")
        
        updated_count = 0
        
        for row in unique_items:
            uid, item_id, stats, durability, max_durability, tier = row
            
            item_def = items_manager.get_item(item_id)
            if not item_def:
                print(f"⚠️  Unknown item ID: {item_id} (ID: {uid})")
                continue
            
            needs_update = False
            new_stats = stats if stats else {}
            new_durability = durability
            new_max_durability = max_durability
            new_tier = tier
            
            # Check if stats are missing or empty
            if not stats:
                if item_def.stats:
                    new_stats = item_def.stats.copy()
                    needs_update = True
            
            # Check for missing durability/tier
            if durability is None and item_def.durability is not None:
                new_durability = item_def.durability
                needs_update = True
                
            if max_durability is None and item_def.durability is not None:
                new_max_durability = item_def.durability
                needs_update = True
                
            if tier is None:
                new_tier = item_def.tier
                needs_update = True
            
            if needs_update:
                # Update the record
                await session.execute(
                    text("""
                        UPDATE unique_items 
                        SET unique_stats = :stats, 
                            durability = :durability, 
                            max_durability = :max_durability, 
                            tier = :tier
                        WHERE id = :id
                    """),
                    {
                        "stats": json.dumps(new_stats) if new_stats else None,
                        "durability": new_durability,
                        "max_durability": new_max_durability,
                        "tier": new_tier,
                        "id": uid
                    }
                )
                updated_count += 1
                if updated_count % 100 == 0:
                    print(f"Updated {updated_count} items...")
        
        await session.commit()
        print(f"✅ Backfill complete. Updated {updated_count} items.")

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