"""IT-side AI suggestion ledger + weekly KB gap digest cron.

``support_ai_suggestions`` records every draft the IT-side ai-suggest
produced (which the agent may edit and send), and the adoption signal —
an agent reply on the same ticket that is textually close to a recent
suggestion marks it adopted. Adoption rate is the only honest number for
"does the AI actually save IT time" (服务台收编 PR-4).

Also seeds ``support.kb_gap_digest`` (Mondays 09:00 Asia/Shanghai):
deflection sessions that escalated/expired WITHOUT any KB hit are the
questions the knowledge base cannot answer — clustered and digested to
IT admins (IT-only; silent when the week has no gaps).

Revision ID: 490
Revises: 489
"""

from __future__ import annotations

import uuid

import sqlalchemy as sa

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

revision = "490"
down_revision = "489"
branch_labels = None
depends_on = None

_JOB_NAME = "support.kb_gap_digest"
_TABLE = "support_ai_suggestions"


def upgrade() -> None:
    bind = op.get_bind()
    persons_id = id_type_for("identity_persons")
    tickets_id = id_type_for("support_tickets")
    kw = table_kw_for("identity_persons")

    exists = bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.tables "
            "WHERE table_schema = DATABASE() AND table_name = :n LIMIT 1"
        ),
        {"n": _TABLE},
    ).scalar()
    if not exists:
        op.create_table(
            _TABLE,
            sa.Column("id", persons_id, primary_key=True),
            sa.Column("ticket_id", tickets_id, nullable=False),
            sa.Column("agent_id", persons_id, nullable=True),
            sa.Column("suggestion", sa.Text(), nullable=False),
            sa.Column("model", sa.String(64), nullable=True),
            sa.Column(
                "created_at", sa.DateTime(timezone=True),
                nullable=False, server_default=sa.func.now(),
            ),
            sa.Column("adopted_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("adopted_similarity", sa.Float(), nullable=True),
            sa.ForeignKeyConstraint(
                ["ticket_id"], ["support_tickets.id"],
                name="fk_support_ai_suggestions_ticket",
            ),
            sa.ForeignKeyConstraint(
                ["agent_id"], ["identity_persons.id"],
                name="fk_support_ai_suggestions_agent",
            ),
            **kw,
        )
        op.create_index(
            "ix_support_ai_suggestions_ticket", _TABLE, ["ticket_id", "created_at"],
        )

    job_exists = bind.execute(
        sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"),
        {"n": _JOB_NAME},
    ).scalar()
    if not job_exists:
        bind.execute(
            sa.text(
                """
                INSERT INTO scheduled_jobs
                    (id, name, display_name, description, handler_key,
                     trigger_type, interval_seconds, cron_expr, timezone,
                     enabled, config, run_count, failure_count,
                     created_at, updated_at)
                VALUES
                    (:id, :name, :display_name, :description, :handler_key,
                     'cron', NULL, '0 9 * * 1', 'Asia/Shanghai',
                     1, '{}', 0, 0, NOW(), NOW())
                """
            ),
            {
                # Pre-mint UUID — TiDB's UUID() is unreliable (see
                # 077_seed_jira_integration.py / scheduled_jobs no-dash bug).
                "id": str(uuid.uuid4()),
                "name": _JOB_NAME,
                "display_name": "求助 · 知识库缺口周报(IT-only)",
                "description": (
                    "每周一 09:00(上海)聚合过去 7 天「检索零命中且员工最终提单/"
                    "放弃」的自助求助问题,聚类后作为「该补的文档清单」推送给"
                    " IT 管理员。当周无缺口时静默不发。"
                ),
                "handler_key": _JOB_NAME,
            },
        )


def downgrade() -> None:
    bind = op.get_bind()
    bind.execute(
        sa.text("DELETE FROM scheduled_jobs WHERE name = :n"), {"n": _JOB_NAME},
    )
    op.drop_index("ix_support_ai_suggestions_ticket", table_name=_TABLE)
    op.drop_table(_TABLE)