"""support_deflection_events — pre-file self-service funnel (求助域 AI-activation PR4).

Revision ID: 307
Revises: 306
Create Date: 2026-06-18

Records each pre-file deflection attempt: the employee's draft problem, the KB
articles / AI answer shown, and the OUTCOME (deflected = no ticket filed, vs.
escalated = filed anyway). This is the only honest measure of real deflection
(tickets prevented) — distinct from the proxy metrics (AI-sourced share, KB
growth). FKs to identity_persons / support_tickets built with the parent
collation; out-of-line (TiDB 3780-safe). Idempotent + re-runnable.
"""
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 = "307"
down_revision = "306"
branch_labels = None
depends_on = None

TABLE = "support_deflection_events"


def _table_exists(bind, name: str) -> bool:
    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()
    if _table_exists(bind, TABLE):
        return
    pid = id_type_for("identity_persons")
    kw = table_kw_for("identity_persons")
    op.create_table(
        TABLE,
        sa.Column("id", pid, primary_key=True),
        sa.Column("person_id", pid, nullable=False),
        sa.Column("query", sa.Text(), nullable=False),
        sa.Column("kb_article_ids", sa.JSON(), nullable=True),
        sa.Column("copilot_answered", sa.Boolean(), nullable=False, server_default=sa.false()),
        sa.Column("outcome", sa.String(16), nullable=False, server_default="open"),
        sa.Column("ticket_id", pid, nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(["person_id"], ["identity_persons.id"], name="fk_support_deflect_person"),
        sa.ForeignKeyConstraint(["ticket_id"], ["support_tickets.id"], name="fk_support_deflect_ticket"),
        **kw,
    )
    op.create_index("ix_support_deflect_person", TABLE, ["person_id", "created_at"])
    op.create_index("ix_support_deflect_outcome", TABLE, ["outcome", "created_at"])


def downgrade() -> None:
    op.drop_table(TABLE)