"""安全段位擂台 — 部门周快照表 secquiz_team_snapshots + 周 cron 种子.

Adds the weekly department-standings snapshot table written by the
``secquiz.team_battle_snapshot`` cron (freezes last ISO-week's 部门擂台 so the
page can show last week's winner + trend). No FK columns (department is an
org-tree node name, not a first-class table) → TiDB-safe; id reuses the
identity id type + the table charset for consistency with the other secquiz
tables.

Revision ID: 318
Revises: 317
"""
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 = "318"
down_revision = "317"
branch_labels = None
depends_on = None

TABLE = "secquiz_team_snapshots"
JOB = "secquiz.team_battle_snapshot"


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()
    pid = id_type_for("identity_persons")
    kw = table_kw_for("identity_persons")

    if not _table_exists(bind, TABLE):
        op.create_table(
            TABLE,
            sa.Column("id", pid, primary_key=True),
            sa.Column("week_start", sa.Date(), nullable=False),
            sa.Column("department", sa.String(128), nullable=False),
            sa.Column("headcount", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("participants", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("answered", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("correct", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("rank", sa.Integer(), nullable=False, server_default="0"),
            sa.Column(
                "created_at", sa.DateTime(timezone=True),
                nullable=False, server_default=sa.func.now(),
            ),
            sa.UniqueConstraint(
                "week_start", "department", name="uq_secquiz_team_snap_week_dept"
            ),
            **kw,
        )
        op.create_index("ix_secquiz_team_snap_week", TABLE, ["week_start"])

    # Seed the weekly snapshot cron — Monday 00:20 UTC (after the day rolls in
    # HQ). Idempotent; the read path falls back to the live computation when no
    # snapshot exists, so a missed run never blanks the page.
    job_exists = bind.execute(
        sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"),
        {"n": JOB},
    ).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, :name,
                     'cron', NULL, '20 0 * * 1', 'UTC',
                     1, '{}', 0, 0, NOW(), NOW())
                """
            ),
            {
                "id": str(uuid.uuid4()),
                "name": JOB,
                "display_name": "安全段位擂台 · 周快照",
                "description": (
                    "每周一 00:20 UTC 冻结上一个 ISO 周的部门擂台排名到 "
                    "secquiz_team_snapshots(供页面展示上周冠军 + 周环比趋势)。"
                    "按 (week_start, department) 幂等;读路径无快照时回落实时计算。"
                ),
            },
        )


def downgrade() -> None:
    bind = op.get_bind()
    bind.execute(
        sa.text("DELETE FROM scheduled_jobs WHERE name = :n"), {"n": JOB},
    )
    if _table_exists(bind, TABLE):
        op.drop_index("ix_secquiz_team_snap_week", table_name=TABLE)
        op.drop_table(TABLE)