"""AI Labs — per-reason failure counters (``ai_labs_failures_daily``).

The fifth AI Labs table, and it sits on the counters side of the domain's
privacy split. ``ai_labs_usage_daily.failures`` counted failures but not
reasons, and the reasons existed only on ``ai_labs_messages.error_code``
and ``ai_labs_generations.error_code`` — rows in the content tables, which
the admin console must never query and which an employee can hard-delete.
So "two turns failed" was the end of the answer.

This table adds the reason dimension without moving that boundary: its one
non-numeric column is an error **code**, and
``app.domains.ai_labs.usage.normalize_error_code`` refuses to store
anything that is not code-shaped (vendor rejections quote the offending
prompt back, and this is the one table an admin reads).

Deliberately a new table rather than a sixth column on the unique key of
``ai_labs_usage_daily``: that table is the quota gate's read model, and
widening its key would multiply the rows every quota check sums.

**Starts empty, and the read path says so.** Failures counted before this
deploy have no reason row, so ``AiLabsService.failure_overview`` reports
``counted_failures`` (authoritative, from the old counter) alongside
``classified_failures`` and surfaces the difference as
``unclassified_failures`` instead of presenting a partial breakdown as a
complete one. No backfill is possible or attempted: the codes for those
failures live in content rows the aggregate is not allowed to read.

Revision ID: 602
Revises: 601
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for, table_kw_for

revision = "602"
down_revision = "601"
branch_labels = None
depends_on = None

T_FAIL = "ai_labs_failures_daily"


def _table_exists(bind, name: str) -> bool:
    """Existence guard scoped to the current database.

    ``DATABASE()`` matters: without it this matches a same-named table in
    any schema on the instance, which on a shared test server is a false
    positive that skips the create and leaves the suite querying nothing.
    """
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.tables "
            "WHERE table_schema = DATABASE() AND table_name = :n"
        ),
        {"n": name},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    # Pins the parent's type *and* collation. No FK is declared on
    # ``person_id`` — matching ``ai_labs_usage_daily``, where a counter
    # outliving its person row is the correct behaviour for a spend
    # statistic — but the column still has to be join-compatible with
    # ``identity_persons.id`` or the read path's directory lookup silently
    # matches zero rows.
    pid = id_type_for("identity_persons")
    kw = table_kw_for("identity_persons")

    if _table_exists(bind, T_FAIL):
        return

    op.create_table(
        T_FAIL,
        sa.Column("id", pid, primary_key=True),
        sa.Column("person_id", pid, nullable=False),
        sa.Column("usage_date", sa.Date(), nullable=False),
        sa.Column("capability", sa.String(32), nullable=False),
        sa.Column("model", sa.String(64), nullable=False),
        sa.Column("error_code", sa.String(64), nullable=False),
        sa.Column(
            "failures", sa.Integer(),
            nullable=False, server_default=sa.text("0"),
        ),
        sa.Column(
            "last_seen_at", sa.DateTime(timezone=True),
            nullable=False, server_default=sa.func.now(),
        ),
        sa.Column(
            "created_at", sa.DateTime(timezone=True),
            nullable=False, server_default=sa.func.now(),
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True),
            nullable=False, server_default=sa.func.now(),
        ),
        # What makes the recorder's INSERT ... ON DUPLICATE KEY UPDATE
        # accumulate instead of racing over a read-modify-write.
        sa.UniqueConstraint(
            "person_id", "usage_date", "capability", "model", "error_code",
            name="uq_alfd_person_date_cap_model_code",
        ),
        **kw,
    )
    # "What is failing across everyone in this window" — the failure card.
    op.create_index("ix_alfd_date_code", T_FAIL, ["usage_date", "error_code"])
    # "What is failing for this person" — the per-person drill.
    op.create_index("ix_alfd_person_date", T_FAIL, ["person_id", "usage_date"])


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, T_FAIL):
        op.drop_table(T_FAIL)