import logging
from typing import Tuple

import sqlglot
from sqlglot import exp

logger = logging.getLogger(__name__)

NON_METRIC_AGGREGATES = (
    exp.Sum,
    exp.Count,
    exp.Avg,
    exp.Min,
    exp.Max,
    exp.StddevPop,
    exp.StddevSamp,
    exp.VariancePop,
    exp.Variance,
    exp.ArrayAgg,
    exp.GroupConcat,
)

METRIC_AGGREGATES = NON_METRIC_AGGREGATES + (
    exp.ApproxDistinct,
    exp.ApproxQuantile,
    exp.Corr,
    exp.CovarPop,
    exp.CovarSamp,
)


def validate_attribute_expression(expression: str) -> Tuple[bool, str]:
    if not expression or not expression.strip():
        return False, "Expression cannot be empty"

    try:
        # Parse as SELECT expression
        parsed = sqlglot.parse_one(f"SELECT {expression} AS x FROM t")
        select = parsed.find(exp.Select)

        if not select:
            return False, "Failed to parse as SELECT expression"

        # Check for multiple expressions (e.g., "a, b")
        if len(select.expressions) != 1:
            return False, "Attribute must be a single expression"

        expr = select.expressions[0]
        # Unwrap alias if present
        if isinstance(expr, exp.Alias):
            expr = expr.this

        # Reject subqueries
        if expr.find(exp.Subquery):
            return False, "Subqueries not allowed in attribute expressions"

        # Reject aggregate functions (those belong in metrics)
        if expr.find(*NON_METRIC_AGGREGATES):
            return False, "Aggregate functions not allowed in attributes (use metrics instead)"

        return True, "valid"

    except sqlglot.errors.ParseError as e:
        return False, f"Invalid SQL expression: {e}"
    except Exception as e:
        logger.warning("Unexpected error validating attribute expression: %s", e)
        return False, f"Invalid SQL expression: {e}"


def validate_metric_expression(expression: str) -> Tuple[bool, str]:
    if not expression or not expression.strip():
        return False, "Expression cannot be empty"

    try:
        parsed = sqlglot.parse_one(f"SELECT {expression} AS x FROM t")
        select = parsed.find(exp.Select)

        if not select:
            return False, "Failed to parse as SELECT expression"

        if len(select.expressions) != 1:
            return False, "Metric must be a single expression"

        expr = select.expressions[0]
        if isinstance(expr, exp.Alias):
            expr = expr.this

        # Reject subqueries
        if expr.find(exp.Subquery):
            return False, "Subqueries not allowed in metric expressions"

        # Must contain at least one aggregate function
        found_aggregate = expr.find(*METRIC_AGGREGATES)
        if not found_aggregate:
            return False, "Metric must contain aggregate function (SUM, COUNT, AVG, MIN, MAX, etc.)"

        # Check for nested aggregates (e.g., SUM(COUNT(*)))
        for agg in expr.find_all(*METRIC_AGGREGATES):
            # Check if any child of this aggregate is also an aggregate
            for child in agg.walk():
                if child is not agg and isinstance(child, METRIC_AGGREGATES):
                    return False, "Nested aggregates not allowed (e.g., SUM(COUNT(*)))"

        return True, "valid"

    except sqlglot.errors.ParseError as e:
        return False, f"Invalid SQL expression: {e}"
    except Exception as e:
        logger.warning("Unexpected error validating metric expression: %s", e)
        return False, f"Invalid SQL expression: {e}"


def validate_filter_expression(expression: str) -> Tuple[bool, str]:
    if not expression or not expression.strip():
        return False, "Expression cannot be empty"

    try:
        parsed = sqlglot.parse_one(f"SELECT * FROM t WHERE {expression}")
        where = parsed.find(exp.Where)

        if not where:
            return False, "Invalid filter expression - could not parse WHERE clause"

        predicate = where.this

        # Reject subqueries
        if predicate.find(exp.Subquery):
            return False, "Subqueries not allowed in filter expressions"

        # Reject aggregate functions (filters run before GROUP BY)
        if predicate.find(*NON_METRIC_AGGREGATES):
            return False, "Aggregate functions not allowed in filter expressions"

        return True, "valid"

    except sqlglot.errors.ParseError as e:
        return False, f"Invalid SQL expression: {e}"
    except Exception as e:
        logger.warning("Unexpected error validating filter expression: %s", e)
        return False, f"Invalid SQL expression: {e}"


def validate_relationship_expression(expression: str, left_entity: str, right_entity: str) -> Tuple[bool, str]:
    if not expression or not expression.strip():
        return False, "Expression cannot be empty"

    # Sanitize entity IDs for use in parsing (replace dots with underscores)
    safe_left = left_entity.replace(".", "_").replace("-", "_")
    safe_right = right_entity.replace(".", "_").replace("-", "_")

    try:
        sql = f"SELECT * FROM {safe_left} JOIN {safe_right} ON {expression}"
        parsed = sqlglot.parse_one(sql)

        join = parsed.find(exp.Join)
        if not join:
            return False, "Failed to parse as JOIN expression"

        on_clause = join.args.get("on")
        if not on_clause:
            return False, "JOIN expression must have an ON clause"

        # Reject subqueries
        if on_clause.find(exp.Subquery):
            return False, "Subqueries not allowed in relationship expressions"

        # Reject aggregate functions
        if on_clause.find(*NON_METRIC_AGGREGATES):
            return False, "Aggregate functions not allowed in relationship expressions"

        return True, "valid"

    except sqlglot.errors.ParseError as e:
        return False, f"Invalid JOIN expression: {e}"
    except Exception as e:
        logger.warning("Unexpected error validating relationship expression: %s", e)
        return False, f"Invalid JOIN expression: {e}"
