Skip to content

:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.

70.5 KB
Raw Blame
import copy
from dataclasses import dataclass, field
from enum import Enum
from typing import Any, Optional

from posthog.schema import HogQLQuery, HogQLQueryModifiers

from posthog.hogql import ast
from posthog.hogql.context import HogQLContext
from posthog.hogql.functions.aggregations import COMBINATORS
from posthog.hogql.functions.mapping import find_hogql_aggregation
from posthog.hogql.modifiers import create_default_modifiers_for_team
from posthog.hogql.parser import parse_select
from posthog.hogql.placeholders import find_placeholders
from posthog.hogql.printer import prepare_and_print_ast
from posthog.hogql.timings import HogQLTimings
from posthog.hogql.visitor import CloningVisitor, TraversingVisitor, clone_expr

from posthog.dataclasses import frozen
from posthog.exceptions_capture import capture_exception
from posthog.hogql_queries.query_runner import get_query_runner
from posthog.hogql_queries.utils.breakdowns import BREAKDOWN_NULL_STRING_LABEL, BREAKDOWN_OTHER_STRING_LABEL
from posthog.models import User
from posthog.models.team import Team
from posthog.schema_migrations.upgrade import upgrade

ENDPOINT_BREAKDOWN_LIMIT = 10_000


class MaterializationNotSupportedError(ValueError):
    """Raised when a query uses a construct materialization can't transform.

    This is a user-input limitation (a 400), not a server fault — callers should surface
    it as a validation error rather than letting it bubble up as an unhandled exception.
    """


class VariableInHavingClauseError(MaterializationNotSupportedError):
    """Raised when a variable is used in a HAVING clause, which is not supported for materialization."""


class VariableComparedToPlaceholderExprError(MaterializationNotSupportedError):
    """Raised when a variable is compared against an expression that itself reads a variable.

    That expression would be the materialized key column, which has to be fixed at
    materialization time, so it can't depend on a per-request value.
    """


def inject_series_index(query_ast: ast.SelectQuery | ast.SelectSetQuery) -> None:
    """Add a __series_index literal column to each sub-query in a UNION ALL.

    For single SelectQuery: adds ``0 AS __series_index``.
    For SelectSetQuery (multi-series UNION ALL): adds ``N AS __series_index``
    to each sub-query so the materialized table can distinguish series.
    """
    if isinstance(query_ast, ast.SelectQuery):
        _add_series_index_to_select(query_ast, 0)
    elif isinstance(query_ast, ast.SelectSetQuery):
        all_queries = query_ast.select_queries()
        for i, sub_query in enumerate(all_queries):
            if isinstance(sub_query, ast.SelectQuery):
                _add_series_index_to_select(sub_query, i)


def _add_series_index_to_select(query: ast.SelectQuery, index: int) -> None:
    """Add ``{index} AS __series_index`` to a single SELECT query."""
    series_col = ast.Alias(alias="__series_index", expr=ast.Constant(value=index))
    query.select = [*list(query.select or []), series_col]

    if query.group_by is not None:
        query.group_by = [*list(query.group_by), ast.Field(chain=["__series_index"])]


def _print_materialized_hogql(
    query: ast.Expr,
    team: Team,
    modifiers: HogQLQueryModifiers | None = None,
    *,
    user: User | None = None,
    bypass_warehouse_access_control: bool = False,
) -> str:
    """Like to_printed_hogql, but without the implicit top-level row cap — a materialized table must hold every row."""
    return prepare_and_print_ast(
        clone_expr(query),
        dialect="hogql",
        context=HogQLContext(
            team_id=team.pk,
            user=user,
            enable_select_queries=True,
            limit_top_select=False,
            modifiers=create_default_modifiers_for_team(team, modifiers),
            bypass_warehouse_access_control=bypass_warehouse_access_control,
        ),
        pretty=True,
    )[0]


def convert_insight_query_to_hogql(
    query: dict[str, Any],
    team: Team,
    *,
    user: User | None = None,
    bypass_warehouse_access_control: bool = False,
) -> dict[str, Any]:
    query_kind = query.get("kind")

    if query_kind == "HogQLQuery":
        return query

    # Stored endpoint snapshots may predate the current query schema
    query = upgrade(query)

    query_runner = get_query_runner(
        query=query,
        team=team,
        timings=HogQLTimings(),
        modifiers=HogQLQueryModifiers(),
        user=user,
    )

    combined_query_ast = query_runner.to_query()

    # Only inject __series_index for types that have a corresponding transformer
    SERIES_INDEX_QUERY_TYPES = {"TrendsQuery", "LifecycleQuery", "RetentionQuery"}
    if query_kind in SERIES_INDEX_QUERY_TYPES:
        inject_series_index(combined_query_ast)

    hogql_string = _print_materialized_hogql(
        combined_query_ast,
        team,
        query_runner.modifiers,
        user=user,
        bypass_warehouse_access_control=bypass_warehouse_access_control,
    )

    result = HogQLQuery(query=hogql_string, modifiers=query_runner.modifiers).model_dump()
    if "variables" in query:
        result["variables"] = query["variables"]
    return result


@dataclass
class MaterializableVariable:
    """Info about a variable that can be materialized"""

    variable_id: str
    code_name: str
    column_chain: list[str]
    column_expression: str
    operator: ast.CompareOperationOp = ast.CompareOperationOp.Eq
    column_ast: Optional[ast.Expr] = None
    value_wrapper_fns: Optional[list[str]] = None
    cte_name: Optional[str] = None  # CTE containing the variable; None = top-level query

    bucket_fn: Optional[str] = None  # e.g. "toStartOfDay" for range variables on timestamp

    # Plans for propagating this variable's column through each downstream CTE.
    # Keyed by downstream CTE name. Populated by the analyzer; consumed by the transformer.
    downstream_plans: dict[str, "DownstreamCTEPlan"] = field(default_factory=dict)


@dataclass
class VariableUsageInWhere:
    """Details of how a variable is used in a WHERE clause"""

    column_chain: list[str]
    column_expression: str
    operator: ast.CompareOperationOp
    column_ast: Optional[ast.Expr] = None
    value_wrapper_fns: Optional[list[str]] = None
    in_or: bool = False  # True when this usage sits inside an OR within the WHERE clause


@frozen
class VariableWhereUsages:
    """Every supported WHERE usage of one variable, and whether it also appears in WHERE in a form the transform can't lift."""

    usages: list[tuple[Optional[str], VariableUsageInWhere]]
    seen_unmatched: bool


RANGE_OPS = frozenset(
    {
        ast.CompareOperationOp.GtEq,
        ast.CompareOperationOp.Gt,
        ast.CompareOperationOp.Lt,
        ast.CompareOperationOp.LtEq,
    }
)


@dataclass(frozen=True)
class AggregateReaggregation:
    """Defines how a base aggregate function is re-aggregated after bucketed materialization."""

    reaggregate_fn: str  # function to apply at read time (e.g., "sum" for count)


# Base functions that CAN be re-aggregated, and how.
# A combined function (e.g., sumIf) inherits from its base (sum).
REAGGREGATABLE_BASE_FUNCTIONS: dict[str, AggregateReaggregation] = {
    "count": AggregateReaggregation(reaggregate_fn="sum"),
    "sum": AggregateReaggregation(reaggregate_fn="sum"),
    "min": AggregateReaggregation(reaggregate_fn="min"),
    "max": AggregateReaggregation(reaggregate_fn="max"),
}


def _strip_combinators(func_name: str) -> str | None:
    """Strip ClickHouse combinator suffixes to find the base aggregate function.

    Uses the COMBINATORS registry from posthog.hogql.functions.aggregations.
    Returns the base function name (lowercased), or None if no match found.

    Examples:
        "sumIf" -> "sum"
        "countArrayIf" -> "count"
        "uniqMerge" -> "uniq" (but uniq is not in REAGGREGATABLE_BASE_FUNCTIONS)
        "count" -> "count"
    """
    name_lower = func_name.lower()
    if name_lower in REAGGREGATABLE_BASE_FUNCTIONS:
        return name_lower

    sorted_suffixes = sorted(COMBINATORS.keys(), key=len, reverse=True)

    def strip_recursive(name: str) -> str:
        for suffix in sorted_suffixes:
            if name.endswith(suffix.lower()) and len(name) > len(suffix):
                return strip_recursive(name[: -len(suffix)])
        return name

    base = strip_recursive(name_lower)
    return base if base != name_lower or base in REAGGREGATABLE_BASE_FUNCTIONS else None


def get_reaggregation(func_name: str) -> AggregateReaggregation | None:
    """Look up how to re-aggregate a (possibly combined) aggregate function.

    Returns AggregateReaggregation if the function can be re-aggregated, None otherwise.
    Handles ClickHouse combinators by stripping suffixes to find the base function.

    Examples:
        "count" -> AggregateReaggregation(reaggregate_fn="sum")
        "sumIf" -> AggregateReaggregation(reaggregate_fn="sum")
        "avg" -> None (not re-aggregatable)
        "uniqArrayIf" -> None (base "uniq" not in registry)
    """
    base = _strip_combinators(func_name)
    if base is None:
        return None
    return REAGGREGATABLE_BASE_FUNCTIONS.get(base)


def _is_aggregate(func_name: str) -> bool:
    sorted_suffixes = sorted(COMBINATORS.keys(), key=len, reverse=True)
    name = func_name
    while find_hogql_aggregation(name) is None:
        suffix = next((s for s in sorted_suffixes if name.lower().endswith(s.lower()) and len(name) > len(s)), None)
        if suffix is None:
            return False
        name = name[: -len(suffix)]
    return True


SUPPORTED_MATERIALIZATION_OPS = frozenset(
    {
        ast.CompareOperationOp.Eq,
        ast.CompareOperationOp.GtEq,
        ast.CompareOperationOp.Gt,
        ast.CompareOperationOp.Lt,
        ast.CompareOperationOp.LtEq,
        ast.CompareOperationOp.Like,
        ast.CompareOperationOp.ILike,
        ast.CompareOperationOp.NotLike,
        ast.CompareOperationOp.NotILike,
    }
)


def analyze_variables_for_materialization(
    hogql_query: dict[str, Any],
    bucket_overrides: dict[str, str] | None = None,
) -> tuple[bool, str, list[MaterializableVariable]]:
    """
    Check if query variables can be materialized.

    Each variable must be used in a WHERE clause with a supported operator
    (=, >=, >, <, <=). Multiple variables are supported.

    Returns:
        (can_materialize, reason, variable_infos)
    """
    query_str = hogql_query.get("query")
    if not query_str:
        return False, "No query string found", []

    try:
        ast_node = parse_select(query_str)
    except Exception as e:
        capture_exception(e)
        return False, "Failed to parse query.", []

    finder = VariablePlaceholderFinder()
    finder.visit(ast_node)

    if not finder.variable_placeholders:
        return False, "No variables found", []

    result_vars, reason = _collect_materializable_variables(
        ast_node, finder.variable_placeholders, hogql_query.get("variables", {})
    )
    if reason is not None:
        return False, reason, []

    # Detect range variables and set bucket_fn for bucketed materialization.
    # Single-bound ranges (e.g., just >= start) are supported — we materialize all data
    # bucketed and filter at read time with the user's value.
    _detect_range_variables(result_vars, bucket_overrides=bucket_overrides)

    # Whole-query gates, in the order their messages should win. Each returns the reason
    # the query as a whole can't be materialized, or None.
    rejection = _reaggregation_rejection(ast_node, result_vars) or _cte_join_rejection(ast_node, result_vars)
    if rejection is not None:
        return False, rejection, []

    # Kept out of the gate chain because it also writes each CTE-bound variable's
    # downstream_plans, which the transformer later reads.
    rejection = _plan_cte_propagation(ast_node, result_vars) or _find_alias_conflict(ast_node, result_vars)
    if rejection is not None:
        return False, rejection, []

    return True, "OK", result_vars


def _collect_materializable_variables(
    ast_node: ast.SelectQuery | ast.SelectSetQuery,
    placeholders: list[ast.Placeholder],
    variables_dict: dict[str, Any],
) -> tuple[list[MaterializableVariable], Optional[str]]:
    """One MaterializableVariable per distinct variable, or the first reason one can't be built."""
    result_vars: list[MaterializableVariable] = []
    seen_code_names: set[str] = set()

    for placeholder in placeholders:
        if not placeholder.chain or len(placeholder.chain) < 2:
            return [], "Invalid variable placeholder format"

        code_name = str(placeholder.chain[1])
        if code_name in seen_code_names:
            continue
        seen_code_names.add(code_name)

        var, reason = _materializable_variable(ast_node, placeholder, code_name, variables_dict)
        if var is None:
            return [], reason
        result_vars.append(var)

    return result_vars, None


def _materializable_variable(
    ast_node: ast.SelectQuery | ast.SelectSetQuery,
    placeholder: ast.Placeholder,
    code_name: str,
    variables_dict: dict[str, Any],
) -> tuple[Optional[MaterializableVariable], Optional[str]]:
    """Build one variable's materialization info, or the reason it can't be materialized."""
    where_usages, reason = _variable_usages(ast_node, placeholder)
    if reason is not None:
        return None, reason

    all_usages = where_usages.usages
    cte_names = {cte for cte, _ in all_usages}
    reason = _usage_rejection(where_usages) or _context_rejection(cte_names)
    if reason is not None:
        return None, reason

    cte_name = next(iter(cte_names))
    # Use the first usage for column info (all usages of the same variable should be consistent)
    variable_usage = all_usages[0][1]

    if variable_usage.operator not in SUPPORTED_MATERIALIZATION_OPS:
        return None, f"Unsupported operator {variable_usage.operator}, supported: =, >=, >, <, <="

    variable_id = next(
        (var_id for var_id, var_data in variables_dict.items() if var_data.get("code_name") == code_name),
        None,
    )
    if not variable_id:
        return None, "Variable metadata not found"

    return (
        MaterializableVariable(
            variable_id=variable_id,
            code_name=code_name,
            column_chain=variable_usage.column_chain,
            column_expression=variable_usage.column_expression,
            operator=variable_usage.operator,
            column_ast=variable_usage.column_ast,
            value_wrapper_fns=variable_usage.value_wrapper_fns,
            cte_name=cte_name,
        ),
        None,
    )


def _variable_usages(
    ast_node: ast.SelectQuery | ast.SelectSetQuery, placeholder: ast.Placeholder
) -> tuple[VariableWhereUsages, Optional[str]]:
    """Every WHERE usage of one variable, or the reason its usage can't be read."""
    no_usages = VariableWhereUsages(usages=[], seen_unmatched=False)
    try:
        return find_all_variable_usages(ast_node, placeholder), None
    except VariableInHavingClauseError:
        return no_usages, "Variable used in HAVING clause are not supported for materialization."
    except VariableComparedToPlaceholderExprError:
        return (
            no_usages,
            "Variable compared against an expression containing another variable is not supported for materialization",
        )
    except ValueError as e:
        capture_exception(e)
        return no_usages, "Invalid variable usage in WHERE clause."


def _usage_rejection(where_usages: VariableWhereUsages) -> Optional[str]:
    """Reasons a variable's WHERE usages can't collapse into one materialized key column."""
    all_usages = where_usages.usages
    # A variable nested in an OR can't be lifted into a single materialized key column:
    # removing its predicate (as the transform does) would change the result set.
    if any(usage.in_or for _, usage in all_usages):
        return "Variables in OR conditions are not supported for materialization"

    # The transform removes the whole predicate and filters `column <op> value` at read time,
    # so any other use of the variable would be silently dropped.
    if where_usages.seen_unmatched:
        return (
            "Variable must be compared directly to a column, for example column = {variables.name}. "
            "Using a variable inside another function or expression in WHERE is not supported for materialization."
        )

    if not all_usages:
        return "Variable not used in WHERE clause"

    # A variable compared against more than one column has no single bucket key to
    # materialize on (the transform would silently keep only the first usage).
    distinct_columns = {
        tuple(usage.column_chain) if usage.column_chain else usage.column_expression for _, usage in all_usages
    }
    if len(distinct_columns) > 1:
        return "Variable compared against multiple columns is not supported for materialization"

    return None


def _context_rejection(cte_names: set[Optional[str]]) -> Optional[str]:
    """Reject a variable used in more than one place (two CTEs, or a CTE and the top level)."""
    if len(cte_names) <= 1:
        return None
    if None in cte_names:
        return "Variable used in both CTE and top-level query is not yet supported"
    return "Variable used in multiple CTEs is not yet supported"


def _reaggregation_rejection(
    ast_node: ast.SelectQuery | ast.SelectSetQuery, result_vars: list[MaterializableVariable]
) -> Optional[str]:
    """With range variables the table is bucketed, so every SELECT aggregate must re-aggregate."""
    if not any(v.bucket_fn is not None for v in result_vars):
        return None
    if not isinstance(ast_node, ast.SelectQuery):
        return None

    for expr in ast_node.select or []:
        agg_name = _extract_aggregate_name(expr)
        if agg_name and get_reaggregation(agg_name) is None:
            return f"Aggregate function '{agg_name}' cannot be re-aggregated for range variable materialization"
    return None


def _cte_join_rejection(
    ast_node: ast.SelectQuery | ast.SelectSetQuery, result_vars: list[MaterializableVariable]
) -> Optional[str]:
    """CTE variables + top-level JOINs produce wrong results.

    Removing a CTE's WHERE changes its row cardinality, which changes JOIN
    output. Filtering after materialization can't recover the original semantics
    (e.g. LEFT JOIN non-matches get NULL for the variable column and are lost).
    """
    if not any(v.cte_name is not None for v in result_vars):
        return None
    if isinstance(ast_node, ast.SelectQuery) and _has_joins(ast_node):
        return "CTE variables with JOINs in the top-level query are not supported for materialization"
    return None


def _plan_cte_propagation(
    ast_node: ast.SelectQuery | ast.SelectSetQuery, result_vars: list[MaterializableVariable]
) -> Optional[str]:
    """Fill in each CTE-bound variable's downstream plans, or return why they can't be built.

    For every CTE-bound variable, classify each downstream CTE so the transformer can
    rewrite SELECT / GROUP BY / WHERE to carry the variable column from the
    variable-carrying CTE to the terminal CTE.
    """
    if not any(v.cte_name is not None for v in result_vars):
        return None
    if not isinstance(ast_node, ast.SelectQuery) or not ast_node.ctes:
        return None

    ctes = ast_node.ctes
    graph = _build_cte_read_graph(ast_node)
    all_code_names = [v.code_name for v in result_vars if v.cte_name is not None]

    for var in result_vars:
        if var.cte_name is None:
            continue
        downstream = _downstream_ctes(graph, var.cte_name)
        propagating = downstream | {var.cte_name}

        if _top_level_reads_propagating_cte_outside_from(ast_node, propagating):
            return (
                "Scalar subquery in top-level query references a CTE downstream of "
                "the variable-carrying CTE; the transformer cannot rewrite it to "
                "carry the variable column. Move the reference to the top-level FROM."
            )

        plans: dict[str, DownstreamCTEPlan] = {}
        for d_cte_name in _topological_order(graph, downstream):
            plan = _classify_downstream_cte(d_cte_name, ctes[d_cte_name].expr, propagating, all_code_names)
            if plan.reject_reason:
                return plan.reject_reason
            plans[d_cte_name] = plan
        var.downstream_plans = plans

    return None


def _top_level_reads_propagating_cte_outside_from(ast_node: ast.SelectQuery, propagating: set[str]) -> bool:
    """Whether the top-level query reads a propagating CTE anywhere but its own FROM chain.

    Propagation adds a column + GROUP BY to propagating CTEs, breaking any top-level scalar
    usage of them (`WHERE x = (SELECT col FROM cte)` ends up returning N rows × 2 cols). No
    correct rewrite exists — ClickHouse doesn't support correlated subqueries. Hide the outer
    WITH so the finder doesn't treat propagating CTEs as shadowed by their own definitions.
    """
    original_ctes = ast_node.ctes
    ast_node.ctes = None
    try:
        return _body_has_non_top_from_propagating_reference(ast_node, propagating)
    finally:
        ast_node.ctes = original_ctes


def _alias_conflict_reason(code_name: str) -> str:
    """Single source of truth for the variable/alias-conflict message, so the analyzer's
    rejection reason and the transformer's exception always read identically."""
    return (
        f"Variable '{code_name}' conflicts with an existing SELECT alias for a different expression. "
        f"Rename the variable or the conflicting column alias."
    )


def _variable_column_expr(var: MaterializableVariable) -> ast.Expr:
    """Expression a variable contributes to SELECT/GROUP BY, without aliasing.

    Uses the original AST expression when available (e.g. function calls like
    toDate(timestamp) that can't be reconstructed from column_chain). Always returns a fresh
    copy so SELECT and GROUP BY don't share nodes. Wraps with bucket_fn when set.
    """
    if var.column_ast is not None:
        base = CloningVisitor().visit(var.column_ast)
    else:
        base = ast.Field(chain=list(var.column_chain))

    if var.bucket_fn:
        return ast.Call(name=var.bucket_fn, args=[base])

    return base


def _select_alias_conflict(select: ast.SelectQuery, vars_in_context: list[MaterializableVariable]) -> Optional[str]:
    """code_name of the first variable colliding with a SELECT alias bound to a *different*
    expression, or None. Shared by the analyzer (pre-flight reject) and the transformer
    (enable-time backstop) so both agree on what's unmaterializable."""
    existing_aliases = {
        alias.alias: alias.expr.to_hogql() for alias in select.select or [] if isinstance(alias, ast.Alias)
    }
    for var in vars_in_context:
        existing = existing_aliases.get(var.code_name)
        if existing is not None and existing != _variable_column_expr(var).to_hogql():
            return var.code_name
    return None


class _AliasConflictFinder(TraversingVisitor):
    def __init__(self, contexts: dict[Optional[str], list[MaterializableVariable]]) -> None:
        super().__init__()
        self._contexts = contexts
        self._current_cte_name: Optional[str] = None
        self.conflict: Optional[str] = None

    def visit_select_query(self, node: ast.SelectQuery) -> None:
        for cte_name, cte in (node.ctes or {}).items():
            prev_cte = self._current_cte_name
            self._current_cte_name = cte_name
            self.visit(cte.expr)
            self._current_cte_name = prev_cte

        original_ctes = node.ctes
        node.ctes = None
        try:
            super().visit_select_query(node)
        finally:
            node.ctes = original_ctes

        vars_in_context = self._contexts.get(self._current_cte_name)
        if self.conflict is None and vars_in_context:
            self.conflict = _select_alias_conflict(node, vars_in_context)


def _find_alias_conflict(
    ast_node: ast.SelectQuery | ast.SelectSetQuery, result_vars: list[MaterializableVariable]
) -> Optional[str]:
    """Pre-flight equivalent of the transformer's per-context alias-conflict check: group
    variables by their context and run the shared conflict check on each.

    A variable whose code_name collides with a SELECT alias for a different expression
    can't be materialized (the table would need two columns of that name). This is the one
    limitation only the transform could detect, so reject it here — sharing the check with
    the transformer — instead of letting enable_materialization fail mid-transform.
    """
    contexts: dict[Optional[str], list[MaterializableVariable]] = {}
    for var in result_vars:
        contexts.setdefault(var.cte_name, []).append(var)
    finder = _AliasConflictFinder(contexts)
    finder.visit(ast_node)
    return _alias_conflict_reason(finder.conflict) if finder.conflict is not None else None


def _has_joins(node: ast.SelectQuery) -> bool:
    """Check if a SelectQuery has any JOINs (next_join on select_from)."""
    return node.select_from is not None and node.select_from.next_join is not None


# ---------------------------------------------------------------------------
# CTE variable propagation: graph helpers + downstream shape classifier
# ---------------------------------------------------------------------------
#
# When a variable lives inside a CTE and downstream CTEs read from it,
# the materialized terminal table must still carry the variable column so the
# read-time filter can slice rows per-variable-value. These helpers walk the
# CTE reference graph, classify each downstream CTE's shape, and return a
# plan object the transformer uses to rewrite SELECT / GROUP BY / WHERE.


# Join types between propagating CTEs that preserve per-value row identity
# under a CROSS-JOIN + equi-predicate rewrite. Outer joins break this (see
# product_data reasoning in the plan doc), so they're explicitly rejected.
_SAFE_PROPAGATION_JOIN_TYPES = frozenset({None, "JOIN", "INNER JOIN", "CROSS JOIN"})


class DownstreamCTEShape(Enum):
    PROJECTION = "projection"
    AGGREGATION = "aggregation"
    DISTINCT = "distinct"
    MULTI_JOIN = "multi_join"
    UNION_ALL = "union_all"


@frozen
class PropagatingSource:
    """A propagating CTE read by a downstream CTE, under the name it is read as.

    Both fields are names of the same shape, so they are keyword-only to stop a
    read alias and a CTE name swapping places at a call site.
    """

    alias: str
    cte_name: str


@frozen
class DownstreamCTEPlan:
    """How the transformer should propagate a variable through a downstream CTE."""

    cte_name: str
    shape: DownstreamCTEShape
    # Every propagating CTE read in this CTE.
    # For UNION_ALL, this is left empty; each leg carries its own nested plan.
    propagating_sources: list[PropagatingSource] = field(default_factory=list)
    leg_plans: list["DownstreamCTEPlan"] = field(default_factory=list)
    reject_reason: Optional[str] = None


class _CTEReferenceCollector(TraversingVisitor):
    """Collect names of sibling CTEs referenced anywhere in an expression subtree.

    A nested ``WITH`` defining the same name shadows the outer sibling, so references
    inside the nested scope are excluded.
    """

    def __init__(self, known_ctes: set[str]):
        super().__init__()
        self._known_stack: list[set[str]] = [known_ctes]
        self.referenced: set[str] = set()

    def visit_select_query(self, node: ast.SelectQuery) -> None:
        shadowed = (set(node.ctes.keys()) & self._known_stack[-1]) if node.ctes else set()
        if shadowed:
            self._known_stack.append(self._known_stack[-1] - shadowed)
            try:
                super().visit_select_query(node)
            finally:
                self._known_stack.pop()
        else:
            super().visit_select_query(node)

    def visit_join_expr(self, node: ast.JoinExpr):
        if isinstance(node.table, ast.Field) and len(node.table.chain) == 1:
            name = str(node.table.chain[0])
            if name in self._known_stack[-1]:
                self.referenced.add(name)
        super().visit_join_expr(node)


def _build_cte_read_graph(node: ast.SelectQuery) -> dict[str, set[str]]:
    """Map each CTE name to the sibling CTE names its body references (transitively via subtree walk)."""
    if not node.ctes:
        return {}
    cte_names = set(node.ctes.keys())
    graph: dict[str, set[str]] = {}
    for name, cte in node.ctes.items():
        collector = _CTEReferenceCollector(cte_names - {name})
        collector.visit(cte.expr)
        graph[name] = collector.referenced
    return graph


def _reads_from(graph: dict[str, set[str]], source: str, target: str, visited: set[str]) -> bool:
    if source in visited:
        return False
    visited.add(source)
    direct = graph.get(source, set())
    if target in direct:
        return True
    return any(_reads_from(graph, ref, target, visited) for ref in direct)


def _downstream_ctes(graph: dict[str, set[str]], start: str) -> set[str]:
    """Return every CTE that transitively reads from ``start`` (excludes ``start`` itself)."""
    return {name for name in graph if name != start and _reads_from(graph, name, start, set())}


def _topological_order(graph: dict[str, set[str]], subset: set[str]) -> list[str]:
    """Order CTEs so each appears after any CTE it reads from (within ``subset``)."""
    result: list[str] = []
    visited: set[str] = set()

    def visit(n: str) -> None:
        if n in visited or n not in subset:
            return
        visited.add(n)
        for dep in graph.get(n, set()):
            visit(dep)
        result.append(n)

    for n in subset:
        visit(n)
    return result


def _normalize_join_type(join_type: Optional[str]) -> Optional[str]:
    """Strip a leading ``GLOBAL `` prefix so classification keys match the base join type."""
    return join_type.removeprefix("GLOBAL ") if join_type is not None else None


def _select_column_name(expr: ast.Expr) -> Optional[str]:
    """The name a SELECT expression emits — alias, or a single-segment field chain."""
    if isinstance(expr, ast.Alias):
        return expr.alias
    if isinstance(expr, ast.Field) and len(expr.chain) == 1:
        return str(expr.chain[0])
    return None


def _collect_propagating_sources_top_level(
    select_from: Optional[ast.JoinExpr],
    propagating: set[str],
) -> tuple[list[PropagatingSource], Optional[str]]:
    """Walk the top-level ``select_from`` chain, collecting propagating CTE refs.

    Returns ``(sources, reject_reason)``. ``reject_reason`` is non-None if any
    JoinExpr in the chain references a propagating CTE via an unsupported join type.
    """
    sources: list[PropagatingSource] = []
    cur = select_from
    while cur is not None:
        if isinstance(cur.table, ast.Field) and len(cur.table.chain) == 1:
            cte_name = str(cur.table.chain[0])
            if cte_name in propagating:
                join_type = _normalize_join_type(cur.join_type)
                if join_type not in _SAFE_PROPAGATION_JOIN_TYPES:
                    return (
                        [],
                        f"CTE variable propagation requires CROSS/INNER joins between propagating CTEs; "
                        f"{cur.join_type} not supported",
                    )
                sources.append(PropagatingSource(alias=cur.alias or cte_name, cte_name=cte_name))
        cur = cur.next_join
    return sources, None


class _NestedPropagatingReferenceFinder(TraversingVisitor):
    """Follow only FROM chains, so a propagating name used elsewhere does not count.

    Unlike the other finders here, this one ignores nested ``WITH`` shadowing, so a local
    CTE that reuses a propagating name is still reported. That over-rejects rather than
    letting an unpropagated query through.
    """

    def __init__(self, propagating: set[str]) -> None:
        super().__init__()
        self.propagating = propagating
        self.found = False

    def visit_select_query(self, n: ast.SelectQuery) -> None:
        if n.select_from is not None:
            self.visit(n.select_from)

    def visit_select_set_query(self, n: ast.SelectSetQuery) -> None:
        for leg in n.select_queries():
            self.visit(leg)

    def visit_join_expr(self, n: ast.JoinExpr) -> None:
        if isinstance(n.table, ast.Field) and len(n.table.chain) == 1:
            if str(n.table.chain[0]) in self.propagating:
                self.found = True
                return
        if isinstance(n.table, ast.SelectQuery | ast.SelectSetQuery):
            self.visit(n.table)
        if n.next_join is not None:
            self.visit(n.next_join)


def _has_nested_propagating_reference(node: Optional[ast.Expr], propagating: set[str]) -> bool:
    """Detect a propagating-CTE reference inside a subquery (e.g. ``FROM (SELECT * FROM prop)``)."""
    if node is None:
        return False
    finder = _NestedPropagatingReferenceFinder(propagating)
    finder.visit(node)
    return finder.found


def _select_from_has_nested_reference(
    select_from: Optional[ast.JoinExpr],
    propagating: set[str],
) -> bool:
    """Return True if any join in the top chain uses a subquery that references a propagating CTE."""
    cur = select_from
    while cur is not None:
        if isinstance(cur.table, ast.SelectQuery | ast.SelectSetQuery):
            if _has_nested_propagating_reference(cur.table, propagating):
                return True
        cur = cur.next_join
    return False


class _NonTopPropagatingReferenceFinder(TraversingVisitor):
    """Flag a propagating-CTE reference outside the top-level FROM chain.

    Top-level FROM references are valid propagation sources (handled separately).
    Anywhere else — scalar subquery in WHERE/SELECT, JOIN ON, LIMIT BY — would need
    a correlated subquery to carry the variable column, which the transformer can't
    produce, so the caller must reject. Nested ``WITH`` shadowing an outer name is
    honored.
    """

    def __init__(self, propagating: set[str], top_chain_join_ids: set[int]) -> None:
        super().__init__()
        self._propagating_stack: list[set[str]] = [propagating]
        self.top_chain_join_ids = top_chain_join_ids
        self.found = False

    def visit(self, node: ast.AST | None) -> None:
        if self.found or node is None:
            return
        super().visit(node)

    def visit_select_query(self, node: ast.SelectQuery) -> None:
        shadowed = (set(node.ctes.keys()) & self._propagating_stack[-1]) if node.ctes else set()
        if shadowed:
            self._propagating_stack.append(self._propagating_stack[-1] - shadowed)
            try:
                super().visit_select_query(node)
            finally:
                self._propagating_stack.pop()
        else:
            super().visit_select_query(node)

    def visit_join_expr(self, n: ast.JoinExpr) -> None:
        if self.found:
            return
        if id(n) not in self.top_chain_join_ids:
            if (
                isinstance(n.table, ast.Field)
                and len(n.table.chain) == 1
                and str(n.table.chain[0]) in self._propagating_stack[-1]
            ):
                self.found = True
                return
        super().visit_join_expr(n)


def _body_has_non_top_from_propagating_reference(
    cte: ast.SelectQuery,
    propagating: set[str],
) -> bool:
    """Return True if any part of ``cte`` other than its top-level ``select_from`` chain
    references a propagating CTE."""
    top_chain_join_ids: set[int] = set()
    cur: Optional[ast.JoinExpr] = cte.select_from
    while cur is not None:
        top_chain_join_ids.add(id(cur))
        cur = cur.next_join

    finder = _NonTopPropagatingReferenceFinder(propagating, top_chain_join_ids)
    finder.visit(cte)
    return finder.found


def _emits_column(select_query: ast.SelectQuery, column_name: str) -> bool:
    return any(_select_column_name(expr) == column_name for expr in select_query.select or [])


def _rejected_plan(
    cte_name: str,
    reason: str,
    shape: DownstreamCTEShape = DownstreamCTEShape.PROJECTION,
) -> DownstreamCTEPlan:
    return DownstreamCTEPlan(cte_name=cte_name, shape=shape, reject_reason=reason)


def _classify_downstream_cte(
    cte_name: str,
    cte_expr: ast.Expr,
    propagating: set[str],
    code_names: list[str],
) -> DownstreamCTEPlan:
    """Classify a downstream CTE's shape; produce a plan or a rejection reason."""
    if isinstance(cte_expr, ast.SelectSetQuery):
        return _classify_union_cte(cte_name, cte_expr, propagating, code_names)

    if not isinstance(cte_expr, ast.SelectQuery):
        return _rejected_plan(cte_name, f"Unsupported CTE body type: {type(cte_expr).__name__}")

    sources, reject = _propagating_sources_or_rejection(cte_name, cte_expr, propagating, code_names)
    if reject is not None:
        return _rejected_plan(cte_name, reject)

    return DownstreamCTEPlan(
        cte_name=cte_name,
        shape=_downstream_shape(cte_expr, sources),
        propagating_sources=sources,
    )


def _classify_union_cte(
    cte_name: str,
    cte_expr: ast.SelectSetQuery,
    propagating: set[str],
    code_names: list[str],
) -> DownstreamCTEPlan:
    """Classify each leg of a set query; every leg has to carry the variable column itself."""
    if any(node.set_operator != "UNION ALL" for node in cte_expr.subsequent_select_queries):
        return _rejected_plan(
            cte_name,
            "Only UNION ALL is supported for propagation across set operations",
            DownstreamCTEShape.UNION_ALL,
        )

    leg_plans: list[DownstreamCTEPlan] = []
    for i, leg in enumerate(cte_expr.select_queries()):
        leg_name = f"{cte_name}#leg{i}"
        if not isinstance(leg, ast.SelectQuery):
            leg_plan = _rejected_plan(leg_name, "nested set queries are not supported")
        else:
            leg_plan = _classify_downstream_cte(leg_name, leg, propagating, code_names)
            # Unreachable today: a SELECT leg with no source is already rejected one level down.
            # Kept because a leg the transformer can't source the column from would silently emit
            # rows without it, and that failure is invisible until the read-time filter drops data.
            if not leg_plan.reject_reason and not leg_plan.propagating_sources:
                leg_plan = _rejected_plan(leg_name, "leg has no propagating CTE source")

        if leg_plan.reject_reason:
            return _rejected_plan(
                cte_name,
                f"Variable propagation failed on UNION leg: {leg_plan.reject_reason}",
                DownstreamCTEShape.UNION_ALL,
            )
        leg_plans.append(leg_plan)

    return DownstreamCTEPlan(cte_name=cte_name, shape=DownstreamCTEShape.UNION_ALL, leg_plans=leg_plans)


def _propagating_sources_or_rejection(
    cte_name: str,
    cte_expr: ast.SelectQuery,
    propagating: set[str],
    code_names: list[str],
) -> tuple[list[PropagatingSource], Optional[str]]:
    """The propagating CTEs this SELECT reads, or the first reason it can't carry the column."""
    if cte_expr.window_exprs:
        return [], "Window functions in downstream CTEs of a variable CTE are not yet supported for materialization"

    sources, reject = _collect_propagating_sources_top_level(cte_expr.select_from, propagating)
    if reject:
        return [], reject

    if _select_from_has_nested_reference(cte_expr.select_from, propagating):
        return (
            [],
            "CTE variable propagation requires top-level FROM references; nested subquery reference not supported",
        )

    if _body_has_non_top_from_propagating_reference(cte_expr, propagating):
        return [], (
            "CTE variable propagation requires top-level FROM references; "
            "scalar subquery reading from a propagating CTE not supported"
        )

    if not sources:
        return [], "Downstream CTE does not read from any propagating CTE"

    collision = next((code_name for code_name in code_names if _emits_column(cte_expr, code_name)), None)
    if collision is not None:
        return [], f"Variable code_name '{collision}' collides with existing column in downstream CTE {cte_name}"

    return sources, None


def _downstream_shape(cte_expr: ast.SelectQuery, sources: list[PropagatingSource]) -> DownstreamCTEShape:
    """Pick the rewrite shape from the CTE's own structure."""
    if len(sources) >= 2:
        return DownstreamCTEShape.MULTI_JOIN
    if cte_expr.distinct:
        return DownstreamCTEShape.DISTINCT
    if cte_expr.group_by or _select_has_aggregate(cte_expr):
        return DownstreamCTEShape.AGGREGATION
    return DownstreamCTEShape.PROJECTION


class _AggregateFinder(TraversingVisitor):
    def __init__(self) -> None:
        super().__init__()
        self.found = False

    def visit_call(self, n: ast.Call) -> None:
        if _is_aggregate(n.name):
            self.found = True
            return
        super().visit_call(n)


def _select_has_aggregate(node: ast.SelectQuery) -> bool:
    """Check if any SELECT expression uses an aggregate function (sum, count, avg, etc.)."""
    finder = _AggregateFinder()
    for expr in node.select or []:
        finder.visit(expr)
        if finder.found:
            return True
    return False


class VariablePlaceholderFinder(TraversingVisitor):
    """Find all variable placeholders in the AST"""

    def __init__(self):
        super().__init__()
        self.variable_placeholders: list[ast.Placeholder] = []

    def visit_placeholder(self, node: ast.Placeholder):
        if node.chain and node.chain[0] == "variables":
            self.variable_placeholders.append(node)


def _contains_placeholder(node: ast.Expr) -> bool:
    found = find_placeholders(node)
    return bool(found.has_filters or found.placeholder_fields or found.placeholder_expressions)


def find_variable_in_where(
    ast_node: ast.SelectQuery | ast.SelectSetQuery, placeholder: ast.Placeholder
) -> Optional[VariableUsageInWhere]:
    """
    Walk AST to find where the placeholder is used in WHERE clause.
    Returns column being compared and operator.
    """
    if not isinstance(ast_node, ast.SelectQuery):
        return None
    finder = VariableInWhereFinder(placeholder)
    finder.visit(ast_node)
    return finder.result


def find_all_variable_usages(
    ast_node: ast.SelectQuery | ast.SelectSetQuery, placeholder: ast.Placeholder
) -> VariableWhereUsages:
    """Find all usages of a variable in WHERE clauses, including inside CTEs.

    Each usage is a (cte_name, usage) tuple, where cte_name is None for the top-level query.
    """
    if not isinstance(ast_node, ast.SelectQuery):
        return VariableWhereUsages(usages=[], seen_unmatched=False)
    finder = VariableInWhereFinder(placeholder)
    finder.visit(ast_node)
    return VariableWhereUsages(usages=finder.all_results, seen_unmatched=finder.seen_unmatched_in_where)


class VariableInWhereFinder(TraversingVisitor):
    """Find how a variable is used in WHERE clause, including inside CTEs."""

    def __init__(self, target_placeholder: ast.Placeholder):
        super().__init__()
        self.target = target_placeholder
        self.all_results: list[tuple[Optional[str], VariableUsageInWhere]] = []
        self.seen_unmatched_in_where = False
        self.in_where = False
        self._removable_compare_ids: set[int] = set()
        self._or_depth = 0
        self._current_cte_name: Optional[str] = None

    @property
    def result(self) -> Optional[VariableUsageInWhere]:
        """Backward-compat: return first match's usage."""
        return self.all_results[0][1] if self.all_results else None

    def visit_select_query(self, node: ast.SelectQuery):
        if node.having:
            finder = VariablePlaceholderFinder()
            finder.visit(node.having)
            if any(p.chain == self.target.chain for p in finder.variable_placeholders):
                raise VariableInHavingClauseError()

        # Visit CTEs first (they're part of this SelectQuery)
        if node.ctes:
            for cte_name, cte in node.ctes.items():
                prev_cte = self._current_cte_name
                self._current_cte_name = cte_name
                self.visit(cte.expr)
                self._current_cte_name = prev_cte

        if node.where:
            prev_in_where, prev_or_depth, prev_removable = self.in_where, self._or_depth, self._removable_compare_ids
            self.in_where, self._or_depth = True, 0
            # Mirrors MaterializationTransformer._remove_variable_from_where: it only removes a
            # comparison that is the whole WHERE or a direct term of a top-level AND.
            terms = node.where.exprs if isinstance(node.where, ast.And) else [node.where]
            self._removable_compare_ids = {id(term) for term in terms if isinstance(term, ast.CompareOperation)}
            self.visit(node.where)
            self.in_where, self._or_depth, self._removable_compare_ids = prev_in_where, prev_or_depth, prev_removable

    def visit_or(self, node: ast.Or):
        # Only OR nesting within a WHERE clause matters for materialization.
        track = self.in_where
        if track:
            self._or_depth += 1
        for expr in node.exprs:
            self.visit(expr)
        if track:
            self._or_depth -= 1

    def visit_compare_operation(self, node: ast.CompareOperation):
        if not self.in_where:
            return

        field_side = None
        variable_side = None
        if self._contains_target_placeholder(node.right):
            field_side = node.left
            variable_side = node.right
        elif self._contains_target_placeholder(node.left):
            field_side = node.right
            variable_side = node.left

        if not isinstance(field_side, ast.Field | ast.Call):
            # Flag without descending: a full visit would record comparisons inside subqueries as usages here.
            finder = VariablePlaceholderFinder()
            finder.visit(node)
            if any(p.chain == self.target.chain for p in finder.variable_placeholders):
                self.seen_unmatched_in_where = True
            return

        if isinstance(field_side, ast.Call) and _contains_placeholder(field_side):
            raise VariableComparedToPlaceholderExprError()

        # Only single-argument wrappers can be reapplied to the value at read time. An OR is left
        # to record its usage, so the OR rejection message wins.
        is_removable = self._or_depth > 0 or id(node) in self._removable_compare_ids
        if not is_removable or not self._is_wrapped_target(variable_side):
            self.seen_unmatched_in_where = True
            return

        wrapper_fns = self._extract_wrapper_fns(variable_side)

        if isinstance(field_side, ast.Field):
            column_chain = [str(item) for item in field_side.chain]
            self.all_results.append(
                (
                    self._current_cte_name,
                    VariableUsageInWhere(
                        column_chain=column_chain,
                        column_expression=".".join(column_chain),
                        operator=node.op,
                        value_wrapper_fns=wrapper_fns,
                        in_or=self._or_depth > 0,
                    ),
                )
            )
        elif isinstance(field_side, ast.Call):
            column_chain = self._extract_column_chain_from_call(field_side)
            self.all_results.append(
                (
                    self._current_cte_name,
                    VariableUsageInWhere(
                        column_chain=column_chain,
                        column_expression=".".join(column_chain) if column_chain else str(field_side),
                        operator=node.op,
                        column_ast=field_side if not column_chain else None,
                        value_wrapper_fns=wrapper_fns,
                        in_or=self._or_depth > 0,
                    ),
                )
            )

    def visit_placeholder(self, node: ast.Placeholder):
        if self.in_where and node.chain == self.target.chain:
            self.seen_unmatched_in_where = True

    def _is_wrapped_target(self, node: Optional[ast.Expr]) -> bool:
        """Whether the node is the target placeholder, optionally inside single-argument calls."""
        while isinstance(node, ast.Call) and len(node.args) == 1:
            node = node.args[0]
        return isinstance(node, ast.Placeholder) and node.chain == self.target.chain

    def _contains_target_placeholder(self, node: ast.Expr) -> bool:
        """Check if an expression is or contains the target placeholder (e.g. inside toDate(...))."""
        if isinstance(node, ast.Placeholder) and node.chain == self.target.chain:
            return True
        if isinstance(node, ast.Call):
            return any(self._contains_target_placeholder(arg) for arg in node.args)
        return False

    @staticmethod
    def _extract_wrapper_fns(node: Optional[ast.Expr]) -> Optional[list[str]]:
        """Extract the chain of wrapping function names from outermost to innermost.

        For toDate(toStartOfMonth({variables.x})), returns ["toDate", "toStartOfMonth"].
        """
        fns: list[str] = []
        current = node
        while isinstance(current, ast.Call) and len(current.args) == 1:
            fns.append(current.name)
            current = current.args[0]
        return fns or None

    def _extract_column_chain_from_call(self, call: ast.Call) -> list[str]:
        if call.name == "JSONExtractString" and len(call.args) >= 2:
            if isinstance(call.args[0], ast.Field) and isinstance(call.args[1], ast.Constant):
                field_chain = [str(item) for item in call.args[0].chain]
                return [*field_chain, str(call.args[1].value)]
        return []


SUPPORTED_BUCKET_FUNCTIONS: dict[str, str] = {
    "minute": "toStartOfMinute",
    "fifteen_minutes": "toStartOfFifteenMinutes",
    "hour": "toStartOfHour",
    "day": "toStartOfDay",
    "week": "toStartOfWeek",
    "month": "toStartOfMonth",
}


def _is_property_column(column_chain: list[str]) -> bool:
    """Check if a column chain references a properties field (e.g. properties.price)."""
    return "properties" in column_chain


def _detect_range_variables(
    variables: list[MaterializableVariable],
    bucket_overrides: dict[str, str] | None = None,
) -> None:
    """Detect range variables and set bucket_fn for bucketed materialization.

    Any variable with a range operator on a plain column (no column_ast)
    gets bucket_fn set. For single-bound ranges, we materialize all data
    bucketed and filter at read time with the user's value.

    Properties columns (e.g. properties.price) are skipped unless the user
    explicitly provides a bucket_override — bucket functions like toStartOfDay
    only make sense on DateTime columns, not on string/numeric properties.
    """
    for var in variables:
        if var.column_ast is not None:
            continue
        if var.operator not in RANGE_OPS:
            continue

        col_key = ".".join(var.column_chain) if var.column_chain else var.column_expression

        if bucket_overrides and col_key in bucket_overrides:
            override = bucket_overrides[col_key]
            if override not in SUPPORTED_BUCKET_FUNCTIONS:
                raise ValueError(
                    f"Unsupported bucket override '{override}'. Supported: {list(SUPPORTED_BUCKET_FUNCTIONS.keys())}"
                )
            var.bucket_fn = SUPPORTED_BUCKET_FUNCTIONS[override]
        elif not _is_property_column(var.column_chain):
            var.bucket_fn = "toStartOfDay"


def _extract_aggregate_name(expr: ast.Expr) -> Optional[str]:
    """Extract the aggregate function name from a SELECT expression, if any.

    Returns lowercased names so downstream lookups (REAGGREGATABLE_BASE_FUNCTIONS,
    get_reaggregation) match regardless of source casing. Both count(DISTINCT x) and
    countDistinct(x) (in any case) collapse to the camelCase sentinel "countDistinct"
    so they stay out of REAGGREGATABLE_BASE_FUNCTIONS — merging distinct counts via
    sum would double-count across partitions.
    """
    if isinstance(expr, ast.Alias):
        return _extract_aggregate_name(expr.expr)
    if isinstance(expr, ast.Call):
        name_lower = expr.name.lower()
        if name_lower == "count" and getattr(expr, "distinct", False):
            return "countDistinct"
        if name_lower == "countdistinct":
            return "countDistinct"
        if _is_aggregate(expr.name):
            return name_lower
    return None


@frozen
class MaterializedColumn:
    """A column in the materialized table with metadata for read-time re-aggregation."""

    expr: ast.Expr
    is_aggregate: bool
    name: str  # the column name the endpoint declares, which a materialized read must keep
    reaggregate_fn: Optional[str] = None  # e.g. "sum" for count/sum, "min" for min, etc.

    @property
    def labeled_expr(self) -> ast.Expr:
        if isinstance(self.expr, ast.Field) and self.expr.chain == [self.name]:
            return self.expr
        return ast.Alias(alias=self.name, expr=self.expr)


def _declared_name(expr: ast.Expr) -> str:
    return expr.alias if isinstance(expr, ast.Alias) else expr.to_hogql()


def transform_select_for_materialized_table(
    select_exprs: list[ast.Expr], output_names: list[str]
) -> list[MaterializedColumn]:
    """
    Transform SELECT expressions to reference pre-computed columns in materialized table.

    `output_names` are the names the materialized table stores, in SELECT order. When their
    count does not match `select_exprs`, each column falls back to its alias or HogQL text.

    Returns list of MaterializedColumn with re-aggregation metadata.

    Examples, given the stored column each expression reads and the name it declares:
    - count() -> reads "count()", name "count()", is_aggregate=True, reaggregate_fn="sum"
    - count() as total -> reads "total", name "total", is_aggregate=True, reaggregate_fn="sum"
    - toStartOfDay(timestamp) as date -> reads "date", name "date", is_aggregate=False
    - toStartOfDay(timestamp) -> reads "toStartOfDay(toTimeZone(timestamp, 'UTC'))", name "toStartOfDay(timestamp)"
    - e.event -> reads "event", name "event", is_aggregate=False
    - properties.foo.bar -> reads "foo__bar", name "foo__bar", is_aggregate=False
    """
    stored_names = (
        output_names if len(output_names) == len(select_exprs) else [_declared_name(expr) for expr in select_exprs]
    )
    result: list[MaterializedColumn] = []
    for expr, stored_name in zip(select_exprs, stored_names):
        agg_name = _extract_aggregate_name(expr)
        is_agg = agg_name is not None
        reagg = get_reaggregation(agg_name) if agg_name else None
        reaggregate_fn = reagg.reaggregate_fn if reagg else None
        result.append(
            MaterializedColumn(
                expr=ast.Field(chain=[stored_name]),
                is_aggregate=is_agg,
                name=stored_name if isinstance(expr, ast.Field) else _declared_name(expr),
                reaggregate_fn=reaggregate_fn,
            )
        )

    return result


def transform_query_for_materialization(
    hogql_query: dict[str, Any],
    variable_infos: MaterializableVariable | list[MaterializableVariable],
    team: Team,
    bucket_overrides: dict[str, str] | None = None,
    *,
    user: User | None = None,
    bypass_warehouse_access_control: bool = False,
) -> dict[str, Any]:
    """
    Transform query by:
    1. Removing WHERE clauses with variables
    2. Adding variable columns to SELECT (aliased by code_name) and GROUP BY (deduplicated)

    Example (single):
        Before: SELECT count(), date FROM events WHERE event = {variables.event_name} GROUP BY date
        After:  SELECT count(), date, event AS event_name FROM events GROUP BY date, event

    Example (multi, same column):
        Before: SELECT count() FROM events WHERE hour >= {variables.start} AND hour < {variables.end}
        After:  SELECT count(), hour AS start, hour AS end FROM events GROUP BY hour

    If bucket_overrides is provided, re-runs range pair detection with the overrides
    so that bucket_fn on the variable_infos is updated before the transform.
    """

    if isinstance(variable_infos, MaterializableVariable):
        variable_infos = [variable_infos]

    variable_infos = copy.deepcopy(variable_infos)

    # Re-apply range variable detection with overrides if provided
    if bucket_overrides:
        # Reset existing bucket_fn values so detection can re-apply with overrides
        for v in variable_infos:
            if v.bucket_fn is not None:
                v.bucket_fn = None
        _detect_range_variables(variable_infos, bucket_overrides=bucket_overrides)

    query_str = hogql_query.get("query")
    if not query_str:
        raise ValueError("No query string found")
    parsed_ast = parse_select(query_str)

    transformer = MaterializationTransformer(variable_infos)
    transformed_ast = transformer.visit(parsed_ast)

    transformed_query_str = _print_materialized_hogql(
        transformed_ast,
        team,
        user=user,
        bypass_warehouse_access_control=bypass_warehouse_access_control,
    )

    return {
        **hogql_query,
        "query": transformed_query_str,
        "variables": {},
    }


class MaterializationTransformer(CloningVisitor):
    """AST transformer that removes variable WHERE clauses and adds columns to SELECT and GROUP BY.

    Each variable gets an aliased column in SELECT (aliased by code_name).
    GROUP BY is deduplicated by column_chain to handle same-column range variables
    (e.g., hour >= start AND hour < end → GROUP BY hour, not GROUP BY hour, hour).

    CTE-aware: when variables live in a CTE, the CTE gets the column addition + WHERE removal,
    and the top-level query gets a passthrough column from the CTE.
    """

    def __init__(self, variable_infos: list[MaterializableVariable]):
        super().__init__()
        self.variable_infos = variable_infos
        self._current_cte_name: Optional[str] = None

    def visit_select_query(self, node: ast.SelectQuery):
        new_ctes = self._process_ctes(node)

        # Visit the select query itself (without re-visiting CTEs)
        original_ctes = node.ctes
        node.ctes = None
        new_node = super().visit_select_query(node)
        node.ctes = original_ctes  # Restore original
        new_node.ctes = new_ctes

        # Add variable columns + remove variable WHERE clauses for current context
        vars_for_context = self._vars_for_current_context()
        if vars_for_context:
            self._add_variable_columns(new_node, vars_for_context)

        # Top-level query: add passthrough columns for CTE variables
        cte_vars = [v for v in self.variable_infos if v.cte_name is not None]
        if self._current_cte_name is None and cte_vars:
            self._add_cte_passthrough_columns(new_node, cte_vars)

        return new_node

    def _process_ctes(self, node: ast.SelectQuery) -> Optional[dict[str, ast.CTE]]:
        """Process CTEs with proper context tracking, returning transformed CTE dict."""
        if not node.ctes:
            return None
        new_ctes: dict[str, ast.CTE] = {}
        for cte_name, cte in node.ctes.items():
            prev_cte = self._current_cte_name
            self._current_cte_name = cte_name
            new_expr = self.visit(cte.expr)
            self._current_cte_name = prev_cte

            # After the CTE body has been cloned/visited, propagate any variable
            # whose plan targets this CTE. Definition order is also topological,
            # so upstream CTEs have already been rewritten to emit code_name.
            for var in self.variable_infos:
                if var.cte_name is None:
                    continue
                plan = var.downstream_plans.get(cte_name)
                if plan is None:
                    continue
                new_expr = self._apply_downstream_plan(new_expr, var, plan)

            new_ctes[cte_name] = ast.CTE(name=cte_name, expr=new_expr, cte_type=cte.cte_type)
        return new_ctes

    def _apply_downstream_plan(
        self,
        cte_expr: ast.Expr,
        var: MaterializableVariable,
        plan: DownstreamCTEPlan,
    ) -> ast.Expr:
        """Rewrite a cloned downstream CTE body so it emits ``var.code_name``."""
        if isinstance(cte_expr, ast.SelectSetQuery):
            if plan.shape != DownstreamCTEShape.UNION_ALL or not plan.leg_plans:
                return cte_expr
            new_initial = self._apply_downstream_plan(cte_expr.initial_select_query, var, plan.leg_plans[0])
            assert isinstance(new_initial, (ast.SelectQuery, ast.SelectSetQuery))
            new_subsequent: list[ast.SelectSetNode] = []
            for i, node in enumerate(cte_expr.subsequent_select_queries):
                leg_plan = plan.leg_plans[i + 1]
                rewritten = self._apply_downstream_plan(node.select_query, var, leg_plan)
                assert isinstance(rewritten, (ast.SelectQuery, ast.SelectSetQuery))
                new_subsequent.append(ast.SelectSetNode(select_query=rewritten, set_operator=node.set_operator))
            return ast.SelectSetQuery(
                initial_select_query=new_initial,
                subsequent_select_queries=new_subsequent,
                limit=cte_expr.limit,
                offset=cte_expr.offset,
                limit_percent=cte_expr.limit_percent,
                limit_with_ties=cte_expr.limit_with_ties,
            )

        if not isinstance(cte_expr, ast.SelectQuery):
            return cte_expr

        sources = plan.propagating_sources
        if not sources:
            return cte_expr
        first_alias = sources[0].alias

        # Add `first_alias.code_name AS code_name` to SELECT.
        select_addition = ast.Alias(
            alias=var.code_name,
            expr=ast.Field(chain=[first_alias, var.code_name]),
        )
        cte_expr.select = [*list(cte_expr.select or []), select_addition]

        if plan.shape == DownstreamCTEShape.AGGREGATION:
            group_by_field = ast.Field(chain=[var.code_name])
            if cte_expr.group_by:
                cte_expr.group_by = [*list(cte_expr.group_by), group_by_field]
            else:
                cte_expr.group_by = [group_by_field]

        if plan.shape == DownstreamCTEShape.MULTI_JOIN and len(sources) > 1:
            for source in sources[1:]:
                predicate = ast.CompareOperation(
                    op=ast.CompareOperationOp.Eq,
                    left=ast.Field(chain=[first_alias, var.code_name]),
                    right=ast.Field(chain=[source.alias, var.code_name]),
                )
                cte_expr.where = self._append_and(cte_expr.where, predicate)

        return cte_expr

    @staticmethod
    def _append_and(existing: Optional[ast.Expr], new_predicate: ast.Expr) -> ast.Expr:
        """AND-flatten ``new_predicate`` into ``existing``, collapsing nested ``ast.And``."""
        if existing is None:
            return new_predicate
        if isinstance(existing, ast.And):
            return ast.And(exprs=[*list(existing.exprs), new_predicate])
        return ast.And(exprs=[existing, new_predicate])

    def _add_variable_columns(self, node: ast.SelectQuery, vars_for_context: list[MaterializableVariable]) -> None:
        """Add aliased variable columns to SELECT, update GROUP BY, and remove variable WHERE clauses."""
        # Backstop: pre-flight analysis normally rejects this first (analyze_variables_for_materialization),
        # so reaching here means a pre-flight gap. Surfaced as a 400, not a server fault.
        conflict = _select_alias_conflict(node, vars_for_context)
        if conflict is not None:
            raise MaterializationNotSupportedError(_alias_conflict_reason(conflict))

        # A code_name already aliased here is — after the conflict check — the same expression
        # already exposed under that name, so skip it; add the rest.
        existing_alias_names = {expr.alias for expr in node.select or [] if isinstance(expr, ast.Alias)}
        vars_to_add = [var for var in vars_for_context if var.code_name not in existing_alias_names]

        select_additions = [self._create_column_field(var) for var in vars_to_add]
        if select_additions:
            node.select = [*list(node.select or []), *select_additions]

        if vars_to_add and (node.group_by is not None or _select_has_aggregate(node)):
            self._add_group_by(node, vars_to_add)

        if node.where:
            node.where = self._remove_variable_from_where(node.where)

    def _add_cte_passthrough_columns(self, node: ast.SelectQuery, cte_vars: list[MaterializableVariable]) -> None:
        """Add passthrough columns + GROUP BY at top level for CTE-resident variables."""
        passthrough_additions: list[ast.Expr] = [ast.Field(chain=[var.code_name]) for var in cte_vars]
        if node.select:
            node.select = [*list(node.select), *passthrough_additions]
        else:
            node.select = passthrough_additions

        if node.group_by is not None or _select_has_aggregate(node):
            self._add_group_by(node, cte_vars, use_field_ref=True)

    def _vars_for_current_context(self) -> list[MaterializableVariable]:
        """Return variables that apply to the current CTE/top-level context."""
        return [v for v in self.variable_infos if v.cte_name == self._current_cte_name]

    def _add_group_by(
        self,
        node: ast.SelectQuery,
        vars_to_add: list[MaterializableVariable],
        use_field_ref: bool = False,
    ) -> None:
        """Add unique columns to GROUP BY, deduplicating by column_chain."""
        existing_keys: set[str] = set()
        if node.group_by:
            for expr in node.group_by:
                if isinstance(expr, ast.Field):
                    existing_keys.add(".".join(str(c) for c in expr.chain))

        seen_keys: set[str] = set()
        group_by_additions: list[ast.Expr] = []
        for var in vars_to_add:
            dedup_key = ".".join(var.column_chain) if var.column_chain else var.column_expression
            if use_field_ref:
                dedup_key = var.code_name
            if dedup_key not in seen_keys and dedup_key not in existing_keys:
                seen_keys.add(dedup_key)
                if use_field_ref:
                    group_by_additions.append(ast.Field(chain=[var.code_name]))
                else:
                    group_by_additions.append(_variable_column_expr(var))

        if node.group_by:
            node.group_by = [*list(node.group_by), *group_by_additions]
        elif group_by_additions:
            node.group_by = group_by_additions

    def _create_column_field(self, var: MaterializableVariable) -> ast.Expr:
        return ast.Alias(
            alias=var.code_name,
            expr=_variable_column_expr(var),
        )

    def _remove_variable_from_where(self, where_node: Optional[ast.Expr]) -> Optional[ast.Expr]:
        if where_node is None:
            return None

        if isinstance(where_node, ast.CompareOperation):
            return None if self._is_variable_comparison(where_node) else where_node

        if isinstance(where_node, ast.And):
            filtered_exprs = [
                expr
                for expr in where_node.exprs
                if not (isinstance(expr, ast.CompareOperation) and self._is_variable_comparison(expr))
            ]
            if not filtered_exprs:
                return None
            if len(filtered_exprs) == 1:
                return filtered_exprs[0]
            return ast.And(exprs=filtered_exprs)

        if isinstance(where_node, ast.Or):
            raise MaterializationNotSupportedError("Variables in OR conditions not supported")

        return where_node

    def _is_variable_comparison(self, node: ast.CompareOperation) -> bool:
        return any(self._expr_contains_variable(side) for side in (node.left, node.right))

    def _expr_contains_variable(self, node: ast.Expr) -> bool:
        if isinstance(node, ast.Placeholder) and node.chain and node.chain[0] == "variables":
            return True
        if isinstance(node, ast.Call):
            return any(self._expr_contains_variable(arg) for arg in node.args)
        return False


def prepare_insight_query_for_endpoint(query: dict) -> dict:
    """Override breakdown_limit to surface all values; keep breakdown_hide_other_aggregation=False so the
    'Other' bucket appears in results if the limit is ever exceeded."""
    breakdown_filter = query.get("breakdownFilter")
    if not breakdown_filter:
        return query

    return {
        **query,
        "breakdownFilter": {
            **breakdown_filter,
            "breakdown_hide_other_aggregation": False,
            "breakdown_limit": ENDPOINT_BREAKDOWN_LIMIT,
        },
    }


def replace_breakdown_sentinels_in_query(hogql_query: dict) -> dict:
    """Strip internal sentinel string literals from HogQL so S3-stored data is clean. The 'other' sentinel
    is embedded in the HogQL by the query builder (in if() and ORDER BY expressions) regardless of
    breakdown_hide_other_aggregation, which only affects post-processing."""
    query_text = hogql_query.get("query")
    if not query_text or not isinstance(query_text, str):
        return hogql_query

    replacements = {
        f"'{BREAKDOWN_NULL_STRING_LABEL}'": "''",
        f"'{BREAKDOWN_OTHER_STRING_LABEL}'": "'Other'",
    }
    for old, new in replacements.items():
        query_text = query_text.replace(old, new)

    return {**hogql_query, "query": query_text}


def _has_variable_placeholders(hogql_query: dict[str, Any]) -> bool:
    query_str = hogql_query.get("query")
    if not isinstance(query_str, str) or not query_str:
        return False
    try:
        ast_node = parse_select(query_str)
    except Exception:
        return False
    finder = VariablePlaceholderFinder()
    finder.visit(ast_node)
    return bool(finder.variable_placeholders)


def build_endpoint_hogql(
    insight_query: dict,
    team: Team,
    bucket_overrides: dict[str, str] | None = None,
    *,
    user: User | None = None,
    bypass_warehouse_access_control: bool = False,
) -> dict:
    """Run the full endpoint conversion pipeline: prepare → convert insight to HogQL → apply variable
    materialization plan → strip breakdown sentinels. Pure function; no DB side effects."""
    mat_query = prepare_insight_query_for_endpoint(insight_query)
    hogql_query = convert_insight_query_to_hogql(
        mat_query,
        team,
        user=user,
        bypass_warehouse_access_control=bypass_warehouse_access_control,
    )

    if insight_query.get("variables"):
        can_materialize, reason, variable_infos = analyze_variables_for_materialization(
            insight_query, bucket_overrides=bucket_overrides
        )
        # Untransformed placeholders can't resolve at materialization time, so the run would fail.
        if not can_materialize and _has_variable_placeholders(insight_query):
            raise MaterializationNotSupportedError(reason)
        if can_materialize and variable_infos:
            hogql_query = transform_query_for_materialization(
                hogql_query,
                variable_infos,
                team,
                bucket_overrides=bucket_overrides,
                user=user,
                bypass_warehouse_access_control=bypass_warehouse_access_control,
            )

    return replace_breakdown_sentinels_in_query(hogql_query)