"""Diagnostic rule to detect queries with multiple execution plans in the last hour."""
from datetime import datetime
from typing import Dict, Any

from app.logcli.doctor_context import DoctorContext


class ExecutionPlanChanges:
    """Detects queries with multiple execution plans in the last hour."""
    
    async def run(self, ctx=None):
        """Run the diagnostic rule."""
        if ctx is None:
            ctx = DoctorContext.create()
        
        try:
            return await self.analyze_plan_changes(ctx)
        except Exception as e:
            return {
                'status': 'error',
                'message': f'Failed to analyze execution plan changes: {str(e)}',
                'issues': []
            }
    
    async def analyze_plan_changes(self, ctx: DoctorContext) -> Dict[str, Any]:
        """Analyze queries with multiple execution plans in the last hour."""
        query = """
        SELECT 
            `Digest` AS `digest`, 
            COUNT(DISTINCT `Plan_digest`) AS `plan_count`, 
            SUBSTRING_INDEX(GROUP_CONCAT(`query`), ';', 1) AS `sample_query`,
            GROUP_CONCAT(DISTINCT `Plan_digest`) AS `plan_digests`
        FROM `slowquery` 
        WHERE FROM_UNIXTIME(`timestamp` / 1000000) >= NOW() - INTERVAL 1 HOUR 
        GROUP BY `Digest` 
        HAVING `plan_count` > 1
        ORDER BY `plan_count` DESC;
        """
        # Debug: Use a fixed time for testing
        # query = query.replace('NOW()', "'2025-06-08T00:00:00'")
        try:
            # Execute the query
            results = ctx.execute_sql(query)
            
            # Debug: Print the raw results
            # print(f"Query returned {len(results)} rows with columns: {results[0].keys() if results else 'no results'}")
            # if results:
            #    print(f"First row sample: {results[0]}")
            
            if not results:
                return {
                    'status': 'success',
                    'message': 'No queries with multiple execution plans found in the last hour',
                    'issues': []
                }
            
            # Prepare the issues
            issues = []
            for row in results:
                try:
                    # Get the column values by dictionary keys
                    digest = row.get('digest', 'unknown')
                    plan_count = row.get('plan_count', 0)
                    sample_query = row.get('sample_query', '')
                    plan_digests_str = row.get('plan_digests', '')
                    
                    # Clean up the sample query
                    if sample_query and len(sample_query) > 1000:
                        sample_query = sample_query[:1000] + '... [truncated]'
                    
                    # Split and clean up plan digests
                    plan_digests = [d.strip() for d in plan_digests_str.split(',') if d.strip()]
                    
                    issues.append({
                        'type': 'multiple_execution_plans',
                        'digest': digest,
                        'plan_count': int(plan_count) if str(plan_count).isdigit() else 0,
                        'sample_query': sample_query,
                        'plan_digests': plan_digests
                    })
                except Exception as e:
                    print(f"Error processing row: {row}. Error: {str(e)}")
            
            return {
                'status': 'success',
                'message': f'Found {len(issues)} queries with multiple execution plans in the last hour',
                'issues': issues,
                'metadata': {
                    'analysis_time': datetime.utcnow().isoformat(),
                    'time_window': 'last_hour',
                    'table': 'slowquery'
                }
            }
            
        except Exception as e:
            return {
                'status': 'error',
                'message': f'Error analyzing execution plan changes: {str(e)}',
                'issues': []
            }