"""
Validation script to compare Python output with SQL output
"""
import asyncio
import sys
import os
from pathlib import Path

# Add parent directory to path
sys.path.insert(0, str(Path(__file__).parent.parent))

from datetime import datetime, timedelta
from db import get_pool, close_pool
from services.options_flow_processor import OptionsFlowProcessor
from services.price_context import PriceContextService
from services.alert_service import AlertService
from services.output_formatter import OutputFormatter
from utils.validation import compare_outputs, print_comparison_report
from utils.logger import setup_logger
import pandas as pd

logger = setup_logger()


async def run_sql_query(pool, start_date: str, end_date: str):
    """Run the original SQL query"""
    async with pool.acquire() as conn:
        # Read SQL file
        sql_file = Path(__file__).parent.parent.parent / 'database' / 'optionflowrockerscorer.sql'
        
        if not sql_file.exists():
            logger.error(f"SQL file not found: {sql_file}")
            return []
        
        with open(sql_file, 'r') as f:
            sql = f.read()
        
        # Replace date placeholders
        sql = sql.replace('(CURRENT_DATE - INTERVAL \'1 day\')::date', f"'{start_date}'::date")
        sql = sql.replace('(CURRENT_DATE)::date', f"'{end_date}'::date")
        
        # Execute query
        rows = await conn.fetch(sql)
        return [dict(row) for row in rows]


async def run_python_processing(pool, start_date: str, end_date: str):
    """Run Python processing"""
    start_dt = datetime.strptime(start_date, '%Y-%m-%d')
    end_dt = datetime.strptime(end_date, '%Y-%m-%d')
    
    # Load raw data
    async with pool.acquire() as conn:
        query = """
            SELECT *
            FROM "OptionsFlow_monthly"
            WHERE "Premium" IS NOT NULL
              AND TRIM("Premium"::text) <> ''
              AND "StockEtf" = 'STOCK'
              AND "Symbol" NOT IN ('TSLA', 'NVDA')
        """
        rows = await conn.fetch(query)
    
    if not rows:
        return []
    
    df = pd.DataFrame([dict(row) for row in rows])
    
    # Process
    processor = OptionsFlowProcessor(tol_pct=0.20)
    df_processed = processor.process(df, start_dt, end_dt)
    
    # Enrich with prices
    price_service = PriceContextService(pool)
    df_with_prices = await price_service.enrich_flow_with_prices(df_processed, pool)
    
    # Match alerts
    alert_service = AlertService(pool)
    df_with_alerts = await alert_service.match_alerts_to_flows(df_with_prices)
    
    # Recalculate score
    df_final = processor.process_rocket_score(df_with_alerts)
    
    # Format output
    df_final = OutputFormatter.format_final_output(df_final)
    
    return df_final.to_dict('records')


async def main():
    """Main validation function"""
    # Default dates (yesterday to today)
    end_date = datetime.now()
    start_date = end_date - timedelta(days=1)
    
    start_date_str = start_date.strftime('%Y-%m-%d')
    end_date_str = end_date.strftime('%Y-%m-%d')
    
    logger.info(f"Validating Python vs SQL for dates: {start_date_str} to {end_date_str}")
    
    try:
        pool = await get_pool()
        
        logger.info("Running SQL query...")
        sql_output = await run_sql_query(pool, start_date_str, end_date_str)
        logger.info(f"SQL returned {len(sql_output)} rows")
        
        logger.info("Running Python processing...")
        python_output = await run_python_processing(pool, start_date_str, end_date_str)
        logger.info(f"Python returned {len(python_output)} rows")
        
        # Compare outputs
        logger.info("Comparing outputs...")
        report = compare_outputs(
            python_output,
            sql_output,
            key_columns=['CreatedDate', 'CreatedTime', 'Symbol']  # Adjust based on your data
        )
        
        # Print report
        print_comparison_report(report)
        
        # Save detailed report
        import json
        report_file = Path(__file__).parent.parent / 'validation_report.json'
        with open(report_file, 'w') as f:
            json.dump(report, f, indent=2, default=str)
        
        logger.info(f"Detailed report saved to: {report_file}")
        
        await close_pool()
        
    except Exception as e:
        logger.error(f"Validation failed: {e}", exc_info=True)
        await close_pool()
        sys.exit(1)


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