"""成就徽章 — gamification_unlocks 表 + 每日扫描 cron 种子.

Only unlocks are persisted (the badge catalog lives in code — criteria are code
logic). No FK columns → TiDB-safe; id reuses the identity id type + table
charset for consistency. (person_id, badge_code) is unique → unlock idempotent.

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

TABLE = "gamification_unlocks"
JOB = "gamification.scan_unlocks"


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("person_id", pid, nullable=False),
            sa.Column("badge_code", sa.String(64), nullable=False),
            sa.Column(
                "unlocked_at", sa.DateTime(timezone=True),
                nullable=False, server_default=sa.func.now(),
            ),
            sa.UniqueConstraint(
                "person_id", "badge_code", name="uq_gamif_unlock_person_badge"
            ),
            **kw,
        )
        op.create_index("ix_gamif_unlock_person", TABLE, ["person_id"])

    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, '15 0 * * *', 'UTC',
                     1, '{}', 0, 0, NOW(), NOW())
                """
            ),
            {
                "id": str(uuid.uuid4()),
                "name": JOB,
                "display_name": "成就徽章 · 每日扫描解锁",
                "description": (
                    "每日 00:15 UTC 评估全体在职员工的成就徽章并持久化新解锁"
                    "(unlocked_at 取真实达成日)。逐人 SAVEPOINT 隔离;读路径懒评估兜底。"
                ),
            },
        )


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_gamif_unlock_person", table_name=TABLE)
        op.drop_table(TABLE)