from typing import List, Optional, Tuple
from slowlogs.ruler import Rule, TimeRange, DeltaLakeStore
from dataclasses import dataclass
import time
import re
from app.logcli.utils.logger import log_error


class RuleDeepOffset(Rule):
    @dataclass
    class Result:
        rule: str
        message: str
        issues_count: int
        metadata: "RuleDeepOffset.MetaData"
        issues: "List[RuleDeepOffset.Issue]"

    @dataclass
    class MetaData:
        analysis_time: float
        time_window: TimeRange
        comparison_window: TimeRange = None

    @dataclass
    class Issue:
        type: str
        digest: str
        query: str
        query_time: float
        offset_value: int
        level: str  # 'critical' or 'major'

    @property
    def name(self):
        return "deep_offset"

    @property
    def description(self):
        return "Detect queries with deep OFFSET values (OFFSET > 10,000)"

    def _extract_offset_value(self, query: str) -> Optional[Tuple[int, str]]:
        """Extract the OFFSET value from the SQL query.

        Returns:
            A tuple of (offset_value, level) if found, None otherwise.
            level is 'critical' if offset >= 100000, 'major' if offset >= 10000
        """
        # Simple regex to find OFFSET clause, case insensitive
        # This handles both OFFSET X and LIMIT Y OFFSET X patterns
        offset_pattern = r'\bOFFSET\s+(\d+)\b'
        match = re.search(offset_pattern, query, re.IGNORECASE)

        if not match:
            return None

        try:
            offset = int(match.group(1))
            if offset >= 100000:
                return offset, 'critical'
            elif offset >= 10000:
                return offset, 'major'
        except (ValueError, IndexError):
            pass

        return None

    def check(self, store: DeltaLakeStore, window: TimeRange, comparison: TimeRange = None) -> Result:
        # First, get all slow queries that might contain OFFSET
        query = """
        SELECT 
            digest,
            first(query) as query,
            max(query_time) as query_time
        FROM slowlogs
        WHERE 
            time >= ? 
            AND time < ?
            AND query IS NOT NULL
            AND lower(query) LIKE '%offset%'
        GROUP BY digest
        """

        try:
            rows = store.execute(
                query, (window.start_ts, window.end_ts)).fetchall()

            issues = []
            for row in rows:
                query_text = row[1]
                if not query_text:
                    continue

                offset_result = self._extract_offset_value(query_text)
                if offset_result:
                    offset_value, level = offset_result
                    issues.append(RuleDeepOffset.Issue(
                        type=self.name,
                        digest=row[0],
                        query=query_text,
                        query_time=row[2],
                        offset_value=offset_value,
                        level=level
                    ))

            return RuleDeepOffset.Result(
                rule=RuleDeepOffset.__name__,
                message=f"Found {len(issues)} queries with large OFFSET values (>10,000)",
                issues_count=len(issues),
                issues=issues,
                metadata=RuleDeepOffset.MetaData(
                    analysis_time=time.time(),
                    time_window=window,
                    comparison_window=comparison
                )
            )

        except Exception as e:
            log_error(f"Error checking for large OFFSET values: {str(e)}")
            return None