"""Pure SQL validation and transformation functions.

All methods are stateless and can be tested independently of database access.
"""

from __future__ import annotations

import re

from .exceptions import SandboxError


class SqlValidator:
    """Pure SQL validation and transformation functions.

    All methods are stateless and can be tested independently of database access.
    """

    # SQL keywords that indicate dangerous operations
    DANGEROUS_KEYWORDS: frozenset[str] = frozenset([
        "DROP", "DELETE", "INSERT", "UPDATE", "CREATE", "ALTER", "ATTACH"
    ])

    # Reserved SQL keywords that shouldn't be treated as table aliases
    RESERVED_ALIASES: frozenset[str] = frozenset([
        "where", "join", "left", "right", "inner", "outer",
        "on", "order", "group", "limit", "fts"
    ])

    @staticmethod
    def strip_literals_and_comments(sql: str) -> str:
        """Strip SQL string literals and comments for safer validation.

        This is a pure function that returns a sanitized version of the SQL
        with all string literals and comments replaced with spaces.
        """
        chars = list(sql)
        i = 0
        mode: str | None = None
        length = len(chars)

        while i < length:
            if mode is None:
                if sql.startswith("--", i):
                    chars[i] = " "
                    if i + 1 < length:
                        chars[i + 1] = " "
                    i += 2
                    mode = "line_comment"
                    continue
                if sql.startswith("/*", i):
                    chars[i] = " "
                    if i + 1 < length:
                        chars[i + 1] = " "
                    i += 2
                    mode = "block_comment"
                    continue

                char = sql[i]
                if char == "'":
                    chars[i] = " "
                    i += 1
                    mode = "single"
                    continue
                if char == '"':
                    chars[i] = " "
                    i += 1
                    mode = "double"
                    continue
                if char == "`":
                    chars[i] = " "
                    i += 1
                    mode = "backtick"
                    continue
                if char == "[":
                    chars[i] = " "
                    i += 1
                    mode = "bracket"
                    continue

                i += 1
                continue

            if mode == "line_comment":
                if sql[i] == "\n":
                    mode = None
                    i += 1
                else:
                    chars[i] = " "
                    i += 1
                continue

            if mode == "block_comment":
                if sql.startswith("*/", i):
                    chars[i] = " "
                    if i + 1 < length:
                        chars[i + 1] = " "
                    i += 2
                    mode = None
                else:
                    chars[i] = " "
                    i += 1
                continue

            if mode == "single":
                if sql[i] == "'":
                    if i + 1 < length and sql[i + 1] == "'":
                        chars[i] = " "
                        chars[i + 1] = " "
                        i += 2
                    else:
                        chars[i] = " "
                        i += 1
                        mode = None
                else:
                    chars[i] = " "
                    i += 1
                continue

            if mode == "double":
                if sql[i] == '"':
                    if i + 1 < length and sql[i + 1] == '"':
                        chars[i] = " "
                        chars[i + 1] = " "
                        i += 2
                    else:
                        chars[i] = " "
                        i += 1
                        mode = None
                else:
                    chars[i] = " "
                    i += 1
                continue

            if mode == "backtick":
                if sql[i] == "`":
                    chars[i] = " "
                    i += 1
                    mode = None
                else:
                    chars[i] = " "
                    i += 1
                continue

            if mode == "bracket":
                if sql[i] == "]":
                    chars[i] = " "
                    i += 1
                    mode = None
                else:
                    chars[i] = " "
                    i += 1
                continue

        return "".join(chars)

    @staticmethod
    def strip_trailing_semicolons(sql: str) -> str:
        """Strip trailing semicolons and whitespace from SQL."""
        return sql.rstrip().rstrip(";").rstrip()

    @classmethod
    def validate_select_only(cls, sql: str) -> None:
        """Validate SQL is safe to execute (SELECT only, no dangerous keywords).

        Raises:
            SandboxError: If SQL contains dangerous operations
        """
        sanitized = cls.strip_literals_and_comments(sql)
        sql_upper = sanitized.strip().upper()
        if not sql_upper.startswith("SELECT"):
            raise SandboxError("Only SELECT queries are allowed")

        for keyword in cls.DANGEROUS_KEYWORDS:
            if re.search(rf"\b{keyword}\b", sql_upper):
                raise SandboxError(f"Dangerous SQL keyword not allowed: {keyword}")

    @classmethod
    def inject_guid_if_needed(cls, sql: str) -> str:
        """Auto-inject guid column for tracking if querying messages table.

        Skips injection for DISTINCT, GROUP BY, and aggregate queries since
        adding guid would change their semantics.
        """
        sql_upper = sql.upper()
        sanitized = cls.strip_literals_and_comments(sql)
        sanitized_upper = sanitized.upper()

        if 'GUID' in sql_upper or 'MESSAGES' not in sql_upper:
            return sql

        # Skip injection for queries where adding guid changes semantics
        if re.search(r'\bDISTINCT\b', sanitized_upper):
            return sql
        if re.search(r'\bGROUP\s+BY\b', sanitized_upper):
            return sql
        if re.search(r'\bHAVING\b', sanitized_upper):
            return sql
        # Skip for aggregate-only queries (COUNT, SUM, AVG, MIN, MAX)
        if re.search(r'\b(COUNT|SUM|AVG|MIN|MAX)\s*\(', sanitized_upper):
            return sql

        match = re.match(r'(\s*SELECT\s+)(.*?)(\s+FROM\s+)', sql, re.IGNORECASE | re.DOTALL)
        if not match:
            return sql

        prefix, columns, from_clause = match.groups()
        rest = sql[match.end():]

        # Detect messages_with_contacts view alias in JOINs
        join_view_alias_match = re.search(
            r'JOIN\s+messages_with_contacts\s+(?:AS\s+)?(\w+)\s+ON',
            sql,
            re.IGNORECASE
        )
        if join_view_alias_match:
            alias = join_view_alias_match.group(1).lower()
            if alias not in cls.RESERVED_ALIASES:
                return f"{prefix}{alias}.guid, {columns}{from_clause}{rest}"

        # Detect JOIN messages_with_contacts without alias
        if re.search(r'JOIN\s+messages_with_contacts\s+ON', sql, re.IGNORECASE):
            return f"{prefix}messages_with_contacts.guid, {columns}{from_clause}{rest}"

        # Detect direct FROM messages_with_contacts alias
        from_view_alias_match = re.search(
            r'FROM\s+messages_with_contacts\s+(?:AS\s+)?(\w+)',
            sql,
            re.IGNORECASE
        )
        if from_view_alias_match:
            alias = from_view_alias_match.group(1).lower()
            if alias not in cls.RESERVED_ALIASES:
                return f"{prefix}{alias}.guid, {columns}{from_clause}{rest}"

        if re.search(r'\bFROM\s+messages_with_contacts\b', sql, re.IGNORECASE):
            return f"{prefix}messages_with_contacts.guid, {columns}{from_clause}{rest}"

        # Detect messages table alias in JOINs
        join_alias_match = re.search(
            r'JOIN\s+messages\s+(?:AS\s+)?(\w+)\s+ON',
            sql,
            re.IGNORECASE
        )
        if join_alias_match:
            alias = join_alias_match.group(1).lower()
            if alias not in cls.RESERVED_ALIASES:
                return f"{prefix}{alias}.guid, {columns}{from_clause}{rest}"

        # Detect direct FROM messages alias
        from_alias_match = re.search(
            r'FROM\s+messages\s+(?:AS\s+)?(\w+)',
            sql,
            re.IGNORECASE
        )
        if from_alias_match:
            alias = from_alias_match.group(1).lower()
            if alias not in cls.RESERVED_ALIASES:
                return f"{prefix}{alias}.guid, {columns}{from_clause}{rest}"

        # FTS-only query without JOIN
        if 'MESSAGES_FTS' in sql_upper and 'JOIN' not in sql_upper:
            return sql

        return f"{prefix}messages.guid, {columns}{from_clause}{rest}"
