"""Diagnostic rule to analyze slow queries that appeared in the last hour but not in the past 24 hours."""
import json
from datetime import datetime
from typing import Dict, Any

from app.logcli.doctor_context import DoctorContext


class SlowQueryAnalysis:
    """Analyzes slow queries that appeared in the last hour but not in the past 24 hours."""
    
    async def run(self, ctx=None):
        """Run the diagnostic rule."""
        if ctx is None:
            ctx = DoctorContext.create()
        
        try:
            return await self.analyze_slow_queries(ctx)
        except Exception as e:
            print(json.dumps({
                'error': f'Failed to analyze slow queries: {str(e)}',
                'status': 'error',
                'timestamp': datetime.utcnow().isoformat()
            }, indent=2))
    
    async def analyze_slow_queries(self, ctx) -> Dict[str, Any]:
        """
        Find SELECT queries that appeared in the last hour but not in the past 24 hours.
        
        Args:
            ctx: DoctorContext with database connection
            
        Returns:
            Dictionary containing analysis results with issues list
        """
        current_time = datetime.utcnow()
        issues = []
        
        try:
            # Format the query with proper time ranges
            query = """
            SELECT
                FROM_UNIXTIME(T1.timestamp / 1000000) as timestamp,
                T1.Query_time,
                T1.Mem_max,
                T1.Process_keys,
                T1.Result_rows,
                T1.digest,
                T1.query
            FROM
                slowquery AS T1
            WHERE
                FROM_UNIXTIME(T1.timestamp / 1000000) >= NOW() - INTERVAL 1 HOUR
                AND FROM_UNIXTIME(T1.timestamp / 1000000) <= NOW() 
                AND T1.Query LIKE 'SELECT%'
                AND NOT EXISTS (
                    SELECT 1
                    FROM slowquery AS T2
                    WHERE T2.digest = T1.digest
                    AND FROM_UNIXTIME(T2.timestamp / 1000000) >= NOW() - INTERVAL 24 HOUR
                    AND FROM_UNIXTIME(T2.timestamp / 1000000) < NOW() - INTERVAL 1 HOUR
                )
            ORDER BY T1.Query_time DESC;
            """
            
            # Debug: Use a fixed time for testing
            # query = query.replace('NOW()', "'2025-06-08T00:00:00'")
            # Execute the raw SQL query
            results = ctx.execute_sql(query)
            # Process results
            for row in results:
                query_time = float(row.get('Query_time', 0)) if row.get('Query_time') is not None else 0
                issues.append({
                    'type': 'new_slow_query',
                    'digest': row.get('digest', 'unknown'),
                    'query': row.get('query', ''),
                    'query_time': query_time,
                    'timestamp': row.get('timestamp', current_time.isoformat())
                })
            
            return {
                'status': 'success',
                'message': f'Found {len(issues)} new slow queries in the last hour',
                'issues': issues,
                'metadata': {
                    'analysis_time': current_time.isoformat(),
                    'time_window': 'last_hour',
                    'comparison_window': 'previous_23_hours',
                    'table': 'slowquery'
                }
            }
            
        except Exception as e:
            return {
                'status': 'error',
                'message': 'Failed to analyze slow queries',
                'error': str(e),
                'issues': [{
                    'type': 'analysis_error',
                    'error': str(e)
                }]
            }