"""Half-yearly all-employee access self-attestation cron.

Seeds the ``auditor.access_self_attestation_auto_open_semiannual`` job into
``scheduled_jobs``. It runs daily at 08:00 UTC but only opens a campaign
inside the 7-day window after a boundary (Jun 1 → H1, Dec 1 → H2), so a
mid-cycle deploy does not retroactively blast every employee. The completed
campaign is the SOC2 CC6.3 / ISO A.5.15 user-access-review evidence.

No schema change — reuses the existing ``access_certifications`` /
``access_certification_items`` tables (campaign_type=``self_attestation``).
Idempotent: skips if a job row with the same name already exists.
"""
from __future__ import annotations

import json
import uuid

import sqlalchemy as sa

from alembic import op

revision = "352"
down_revision = "351"
branch_labels = None
depends_on = None

_JOB = {
    "name": "auditor.access_self_attestation_auto_open_semiannual",
    "display_name": "安全员 · 全员访问自查半年度自动开盘",
    "description": (
        "每日 08:00 UTC 运行,但仅在边界后 7 天窗口内开盘:6 月 1 日开 H1、"
        "12 月 1 日开 H2(campaign_type=self_attestation)。快照每位在职员工"
        "的完整访问足迹并发批量确认卡。该期已有则幂等跳过;刻意不追溯历史"
        "边界(防中途上线全员推送风暴)。完成的活动 = SOC2 CC6.3 / ISO "
        "A.5.15 用户访问复核证据。"
    ),
    "cron": "0 8 * * *",
    "config": {"due_after_days": 21, "window_days": 7},
}


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 not _table_exists(bind, "scheduled_jobs"):
        return
    exists = bind.execute(
        sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"),
        {"n": _JOB["name"]},
    ).scalar()
    if exists:
        return
    bind.execute(
        sa.text(
            "INSERT INTO scheduled_jobs "
            "(id, name, display_name, description, handler_key, "
            " trigger_type, cron_expr, timezone, enabled, config, "
            " run_count, failure_count, created_at, updated_at) "
            "VALUES (:id, :n, :dn, :d, :hk, 'cron', :ce, 'UTC', 1, :cfg, "
            " 0, 0, NOW(), NOW())"
        ),
        {
            "id": str(uuid.uuid4()),
            "n": _JOB["name"],
            "dn": _JOB["display_name"],
            "d": _JOB["description"],
            "hk": _JOB["name"],
            "ce": _JOB["cron"],
            "cfg": json.dumps(_JOB["config"]),
        },
    )


def downgrade() -> None:
    bind = op.get_bind()
    bind.execute(
        sa.text("DELETE FROM scheduled_jobs WHERE name = :n"),
        {"n": _JOB["name"]},
    )