"""Creator-analytics table / column whitelist.

Every DSL request is constrained by :data:`WHITELIST`. The set of tables, the
columns that may appear in ``select`` / ``where`` / ``group_by``, and the
aggregations allowed per column are declared here. Several columns are not
exposed in the DSL surface and are instead injected automatically by the SQL
builder, driven by :class:`TableSpec` flags:

* ``shifu_bid`` — injected when :attr:`TableSpec.has_shifu_bid` is True.
* ``deleted = 0`` — injected when :attr:`TableSpec.has_deleted` is True.
* ``<creator_scoped_column> = caller_user_id`` — injected when
  :attr:`TableSpec.creator_scoped_column` is non-None (used by the metadata
  tables ``shifu_published_shifus`` / ``shifu_draft_shifus`` to enforce row
  ownership in addition to the shifu-scope check).
* ``status = 1`` — injected when :attr:`TableSpec.auto_filter_status_active`
  is True (used by ``learn_generated_blocks`` to exclude rerolled history).
"""

from __future__ import annotations

from dataclasses import dataclass
from typing import FrozenSet, Mapping, Optional, Type

from flaskr.service.learn.models import (
    LearnGeneratedBlock,
    LearnLessonFeedback,
    LearnProgressRecord,
)
from flaskr.service.billing.models import BillingDailyUsageMetric
from flaskr.service.order.models import Order
from flaskr.service.profile.models import VariableValue
from flaskr.service.shifu.models import DraftShifu, PublishedShifu, ShifuUserArchive
from flaskr.service.user.models import UserInfo


ALLOWED_AGGREGATE_FUNCTIONS: FrozenSet[str] = frozenset(
    {"count", "count_distinct", "sum", "avg", "min", "max"}
)

ALLOWED_OPERATORS: FrozenSet[str] = frozenset(
    {
        "=",
        "!=",
        ">",
        ">=",
        "<",
        "<=",
        "in",
        "not_in",
        "between",
        "like",
        "is_null",
        "is_not_null",
    }
)


@dataclass(frozen=True)
class TableSpec:
    """Declarative whitelist for one analyzable table."""

    table_key: str
    model: Type
    selectable: FrozenSet[str]
    filterable: FrozenSet[str]
    groupable: FrozenSet[str]
    aggregatable: Mapping[str, FrozenSet[str]]
    has_deleted: bool
    # True for shifu-scoped tables (sql_builder injects WHERE shifu_bid=:sb).
    # False for global tables like user_users — permission is still enforced
    # by funcs.run_dsl using get_user_shifu_permissions, but the SQL cannot
    # filter by a column the table does not have.
    has_shifu_bid: bool = True
    # When non-None, sql_builder injects WHERE <col> = :__user_id (the caller).
    # Used for creator-owned metadata tables (shifu_published_shifus /
    # shifu_draft_shifus) so the row's creator must match the caller. Pairs
    # with has_shifu_bid for a double-gate: shifu permission AND row ownership.
    creator_scoped_column: Optional[str] = None
    # When True, sql_builder injects AND status = 1 to exclude rerolled /
    # superseded history rows. Only enabled where status semantics are
    # "1 = current, 0 = history" (e.g. learn_generated_blocks).
    auto_filter_status_active: bool = False


_DIMENSION_AGGS: FrozenSet[str] = frozenset({"count", "count_distinct"})
_NUMERIC_AGGS: FrozenSet[str] = frozenset({"count", "sum", "avg", "min", "max"})
_TIMESTAMP_AGGS: FrozenSet[str] = frozenset({"count", "min", "max"})


WHITELIST: Mapping[str, TableSpec] = {
    "learn_progress_records": TableSpec(
        table_key="learn_progress_records",
        model=LearnProgressRecord,
        selectable=frozenset(
            {
                "progress_record_bid",
                "user_bid",
                "outline_item_bid",
                "status",
                "block_position",
                "created_at",
                "updated_at",
            }
        ),
        filterable=frozenset(
            {
                "user_bid",
                "outline_item_bid",
                "status",
                "block_position",
                "created_at",
                "updated_at",
            }
        ),
        groupable=frozenset(
            {"user_bid", "outline_item_bid", "status", "block_position"}
        ),
        aggregatable={
            "progress_record_bid": _DIMENSION_AGGS,
            "user_bid": _DIMENSION_AGGS,
            "outline_item_bid": _DIMENSION_AGGS,
            "created_at": _TIMESTAMP_AGGS,
            "updated_at": _TIMESTAMP_AGGS,
        },
        has_deleted=True,
    ),
    "learn_generated_blocks": TableSpec(
        table_key="learn_generated_blocks",
        model=LearnGeneratedBlock,
        selectable=frozenset(
            {
                "generated_block_bid",
                "user_bid",
                "progress_record_bid",
                "outline_item_bid",
                "type",
                "role",
                "status",
                "position",
                "liked",
                "created_at",
                "generated_content",
            }
        ),
        filterable=frozenset(
            {
                "user_bid",
                "progress_record_bid",
                "outline_item_bid",
                "type",
                "role",
                "status",
                "position",
                "liked",
                "created_at",
            }
        ),
        groupable=frozenset(
            {"user_bid", "outline_item_bid", "type", "role", "status", "liked"}
        ),
        aggregatable={
            "generated_block_bid": _DIMENSION_AGGS,
            "user_bid": _DIMENSION_AGGS,
            "outline_item_bid": _DIMENSION_AGGS,
            "liked": _NUMERIC_AGGS,
            "created_at": _TIMESTAMP_AGGS,
        },
        has_deleted=True,
        auto_filter_status_active=True,
    ),
    "learn_lesson_feedbacks": TableSpec(
        table_key="learn_lesson_feedbacks",
        model=LearnLessonFeedback,
        selectable=frozenset(
            {
                "lesson_feedback_bid",
                "user_bid",
                "progress_record_bid",
                "mode",
                "score",
                "created_at",
            }
        ),
        filterable=frozenset(
            {
                "user_bid",
                "progress_record_bid",
                "mode",
                "score",
                "created_at",
            }
        ),
        groupable=frozenset({"user_bid", "progress_record_bid", "mode", "score"}),
        aggregatable={
            "lesson_feedback_bid": _DIMENSION_AGGS,
            "user_bid": _DIMENSION_AGGS,
            "score": _NUMERIC_AGGS,
            "created_at": _TIMESTAMP_AGGS,
        },
        has_deleted=True,
    ),
    "order_orders": TableSpec(
        table_key="order_orders",
        model=Order,
        selectable=frozenset(
            {
                "order_bid",
                "user_bid",
                "status",
                "payment_channel",
                "paid_price",
                "created_at",
            }
        ),
        filterable=frozenset(
            {
                "user_bid",
                "status",
                "payment_channel",
                "paid_price",
                "created_at",
            }
        ),
        groupable=frozenset({"user_bid", "status", "payment_channel"}),
        aggregatable={
            "order_bid": _DIMENSION_AGGS,
            "user_bid": _DIMENSION_AGGS,
            "paid_price": _NUMERIC_AGGS,
            "created_at": _TIMESTAMP_AGGS,
        },
        has_deleted=True,
    ),
    "var_variable_values": TableSpec(
        table_key="var_variable_values",
        model=VariableValue,
        selectable=frozenset(
            {
                "variable_value_bid",
                "user_bid",
                "variable_bid",
                "value",
                "updated_at",
            }
        ),
        filterable=frozenset({"user_bid", "variable_bid", "value", "updated_at"}),
        groupable=frozenset({"user_bid", "variable_bid", "value"}),
        aggregatable={
            "variable_value_bid": _DIMENSION_AGGS,
            "user_bid": _DIMENSION_AGGS,
            "updated_at": _TIMESTAMP_AGGS,
        },
        has_deleted=True,
    ),
    "shifu_user_archives": TableSpec(
        table_key="shifu_user_archives",
        model=ShifuUserArchive,
        selectable=frozenset({"user_bid", "archived", "archived_at", "created_at"}),
        filterable=frozenset({"user_bid", "archived", "archived_at", "created_at"}),
        groupable=frozenset({"user_bid", "archived"}),
        aggregatable={
            "user_bid": _DIMENSION_AGGS,
            "archived_at": _TIMESTAMP_AGGS,
            "created_at": _TIMESTAMP_AGGS,
        },
        has_deleted=False,
    ),
    # ------------------------------------------------------------------
    # Daily pre-aggregated credit usage — one row per (stat_date, shifu,
    # usage_scene, usage_type, provider, model, billing_metric).
    # `consumed_credits` is the authoritative credit-cost figure; it is
    # already rate-adjusted by the billing settlement job.
    # Unlike bill_usage.total (raw token/char count), consumed_credits
    # reflects the actual deduction from the creator's wallet.
    # ------------------------------------------------------------------
    "bill_daily_usage_metrics": TableSpec(
        table_key="bill_daily_usage_metrics",
        model=BillingDailyUsageMetric,
        selectable=frozenset(
            {
                "stat_date",
                "creator_bid",
                "usage_scene",
                "usage_type",
                "provider",
                "model",
                "billing_metric",
                "consumed_credits",
                "record_count",
            }
        ),
        filterable=frozenset(
            {
                "stat_date",
                "usage_scene",
                "usage_type",
                "provider",
                "model",
                "billing_metric",
            }
        ),
        groupable=frozenset(
            {
                "stat_date",
                "creator_bid",
                "usage_scene",
                "usage_type",
                "provider",
                "model",
                "billing_metric",
            }
        ),
        aggregatable={
            "consumed_credits": _NUMERIC_AGGS,
            "record_count": _NUMERIC_AGGS,
            "stat_date": _TIMESTAMP_AGGS,
            "billing_metric": _DIMENSION_AGGS,
        },
        has_deleted=True,
        has_shifu_bid=True,
    ),
    # ------------------------------------------------------------------
    # Global user table — limited to nickname/identify lookup by a known
    # user_bid list or an exact user_identify (phone/email) match.
    # Compared to the other tables this one is special:
    #   - selectable {user_bid, nickname, user_identify}
    #   - filterable {user_bid, user_identify}; DSL enforces that at least
    #     one anchor filter is present: user_bid (= or in) or
    #     user_identify (= only — no in/like/range to block enumeration)
    #   - groupable / aggregatable empty (no distribution probing)
    #   - has_shifu_bid=False — table has no shifu_bid column; permission
    #     is still gated by funcs.run_dsl via get_user_shifu_permissions
    #   - per-query limit hard-capped to 50 (see dsl._USER_USERS_LIMIT_MAX)
    #   - nickname values pass through PII redaction in funcs
    #   - user_identify values pass through PII masking in funcs
    #     (middle digits replaced with *****, not fully redacted)
    #   - access is audit-logged
    # ------------------------------------------------------------------
    "user_users": TableSpec(
        table_key="user_users",
        model=UserInfo,
        selectable=frozenset({"user_bid", "nickname", "user_identify"}),
        filterable=frozenset({"user_bid", "user_identify"}),
        groupable=frozenset(),
        aggregatable={},
        has_deleted=True,
        has_shifu_bid=False,
    ),
    # ------------------------------------------------------------------
    # Course metadata tables — answer "what is shifu_bid X currently
    # called", "did I rename it", "which of my courses match this title".
    # Two-table layout: published_shifus is the live learner-facing title;
    # draft_shifus is the in-progress editor title. They can diverge after
    # rename (draft updated, not yet republished).
    # Security model — double-gate:
    #   1. funcs.run_dsl checks get_user_shifu_permissions for view access
    #      on dsl.shifu_bid (caller is owner or co-author).
    #   2. sql_builder injects WHERE created_user_bid = :__user_id so the
    #      row must belong to the caller. Co-authors with view permission
    #      can run analytics queries on shared courses but cannot read the
    #      title metadata — that stays scoped to the original author per
    #      PDF §7.2 "current course attribution = published.created_user_bid".
    # Hard restrictions:
    #   - aggregatable={} / groupable=frozenset(): no aggregate / group_by;
    #     prevents using count_distinct(shifu_bid) as a permission-probe
    #     side channel.
    #   - selectable / filterable expose only the minimum fields needed
    #     for title lookup. Sensitive author secrets (llm_system_prompt,
    #     ask_llm_system_prompt, ask_provider_config, etc.) are NEVER on
    #     this list — those are creator IP and must not flow through the
    #     analytics DSL even to the owner.
    #   - title supports op=like, but dsl.py enforces a minimum
    #     non-wildcard length (>= 2) on top of the existing leading-%
    #     guard, so callers cannot scan with `like "a%"`.
    #   - limit hard-capped to 50 (see dsl._SHIFU_META_LIMIT_MAX).
    # ------------------------------------------------------------------
    "shifu_published_shifus": TableSpec(
        table_key="shifu_published_shifus",
        model=PublishedShifu,
        # shifu_bid is intentionally NOT exposed — the sql_builder injects it
        # from the CLI's positional argument; selecting / filtering it again
        # would be pure echo and breaks the global "shifu_bid stays out of the
        # DSL surface" invariant (see test_shifu_bid_and_deleted_are_never_
        # user_addressable). created_user_bid is exposed so the caller can
        # confirm ownership in the response payload.
        selectable=frozenset({"title", "created_user_bid", "created_at", "updated_at"}),
        filterable=frozenset({"title", "created_user_bid", "created_at", "updated_at"}),
        groupable=frozenset(),
        aggregatable={},
        has_deleted=True,
        has_shifu_bid=True,
        creator_scoped_column="created_user_bid",
    ),
    "shifu_draft_shifus": TableSpec(
        table_key="shifu_draft_shifus",
        model=DraftShifu,
        selectable=frozenset({"title", "created_user_bid", "created_at", "updated_at"}),
        filterable=frozenset({"title", "created_user_bid", "created_at", "updated_at"}),
        groupable=frozenset(),
        aggregatable={},
        has_deleted=True,
        has_shifu_bid=True,
        creator_scoped_column="created_user_bid",
    ),
}


def get_table_spec(table_key: str) -> TableSpec:
    """Return the spec for ``table_key`` or raise :class:`KeyError`."""

    return WHITELIST[table_key]
