"""
Diagnostic rule to detect queries with high execution time variability.
"""
from typing import Dict, Any
from datetime import datetime
import json
from app.logcli.doctor_context import DoctorContext


class QueryTimeVariability:
    """
    Detects queries with high execution time variability.
    
    Flags queries where the standard deviation of execution time is more than 20%
    of the average execution time, indicating inconsistent performance.
    """
    
    async def run(self, ctx=None):
        """Run the diagnostic rule."""
        if ctx is None:
            ctx = DoctorContext.create()
            
        try:
            return await self.analyze_time_variability(ctx)
        except Exception as e:
            print(json.dumps({
                'error': f'Failed to analyze query time variability: {str(e)}',
                'status': 'error',
                'timestamp': datetime.utcnow().isoformat()
            }, indent=2))
    
    async def analyze_time_variability(self, ctx: DoctorContext) -> Dict[str, Any]:
        """
        Analyze queries for high execution time variability.
        
        Args:
            ctx: The doctor context with database connection and other utilities.
            
        Returns:
            Dict containing the analysis results.
        """
        
        # Build the query
        query = """
        SELECT
            `digest`,
            AVG(`query_time`) AS avg_query_time,
            STDDEV(`query_time`) AS stddev_query_time,
            COUNT(*) AS query_count,
            SUBSTRING_INDEX(GROUP_CONCAT(`query`), ';', 1) AS sample_query
        FROM `slowquery`
        WHERE 
            FROM_UNIXTIME(`timestamp` / 1000000) >= NOW() - INTERVAL 1 HOUR AND
            FROM_UNIXTIME(`timestamp` / 1000000) < NOW() AND
            `query` LIKE 'SELECT%'
        GROUP BY `digest`
        HAVING 
            COUNT(*) > 1 AND
            (STDDEV(`query_time`) / AVG(`query_time`)) > 0.2 AND
            STDDEV(`query_time`) > 1
        ORDER BY (STDDEV(`query_time`) / AVG(`query_time`)) DESC
        LIMIT 100
        """
        # Debug: Use a fixed time for testing
        # query = query.replace('NOW()', "'2025-06-08T00:00:00'")
        try:
            # Execute the query with parameters
            results = ctx.execute_sql(query)
            
            if not results:
                return {
                    'status': 'success',
                    'message': 'No queries with high time variability found in the last hour',
                    'issues': []
                }
            
            # Prepare the issues
            issues = []
            for row in results:
                try:
                    digest = row.get('digest', 'unknown')
                    avg_time = float(row.get('avg_query_time', 0))
                    stddev = float(row.get('stddev_query_time', 0))
                    count = int(row.get('query_count', 0))
                    sample_query = row.get('sample_query', '')
                    
                    # Calculate variability percentage
                    variability_pct = (stddev / avg_time) * 100 if avg_time > 0 else 0
                    
                    # Clean up the sample query
                    if sample_query and len(sample_query) > 500:
                        sample_query = sample_query[:500] + '... [truncated]'
                    
                    issues.append({
                        'type': 'high_time_variability',
                        'digest': digest,
                        'avg_query_time_seconds': round(avg_time, 4),
                        'stddev_seconds': round(stddev, 4),
                        'variability_pct': round(variability_pct, 2),
                        'query_count': count,
                        'sample_query': sample_query,
                        'severity': self._determine_severity(variability_pct, avg_time)
                    })
                except Exception as e:
                    print(f"Error processing row: {row}. Error: {str(e)}")
            
            return {
                'status': 'success',
                'message': f'Found {len(issues)} queries with high time variability in the last hour',
                'issues': issues,
                'metadata': {
                    'analysis_time': datetime.utcnow().isoformat(),
                    'time_window': 'last_hour',
                    'table': 'slowquery',
                    'variability_threshold_pct': 20.0,
                    'min_stddev_seconds': 1.0
                }
            }
            
        except Exception as e:
            return {
                'status': 'error',
                'error': str(e),
                'message': f'Error analyzing query time variability: {str(e)}'
            }
    
    @staticmethod
    def _determine_severity(variability_pct: float, avg_time: float) -> str:
        """
        Determine severity based on variability and average query time.
        """
        if variability_pct > 100 or avg_time > 10:
            return 'high'
        elif variability_pct > 50 or avg_time > 5:
            return 'medium'
        return 'low'