"""Attendance pending-confirmation reminder: last_reminded_at column +
daily reminder cron seed.

Revision ID: 340
Revises: 339
"""
from __future__ import annotations

import json as _json
import uuid

import sqlalchemy as sa

from alembic import op

revision = "340"
down_revision = "339"
branch_labels = None
depends_on = None

REPORTS = "attendance_reports"
COLUMN = "last_reminded_at"
HANDLER_KEY = "attendance.pending_reminder"

_JOB_DESC = (
    "每日提醒在「待实习生确认 / 待 leader 确认」状态停留超过阈值(默认 48 小时)的"
    "考勤月报相关人。仅提醒已发送过的在途报告(不新增受众),每轮有上限,且不会比"
    "阈值更频繁地重复提醒。可经 attendance.reminder_enabled 平台开关整体关闭。"
)


def _column_exists(bind, table: str, column: str) -> bool:
    return bool(bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.columns "
            "WHERE table_schema = DATABASE() AND table_name = :t "
            "AND column_name = :c LIMIT 1"
        ),
        {"t": table, "c": column},
    ).scalar())


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, REPORTS) and not _column_exists(bind, REPORTS, COLUMN):
        op.add_column(
            REPORTS,
            sa.Column(COLUMN, sa.DateTime(timezone=True), nullable=True),
        )
    if _table_exists(bind, "scheduled_jobs"):
        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) "
                "SELECT :id, :name, :display, :desc, :handler, 'cron', "
                ":cron, 'Asia/Shanghai', 1, :config, 0, 0, NOW(), NOW() "
                "FROM DUAL WHERE NOT EXISTS "
                "(SELECT 1 FROM scheduled_jobs WHERE handler_key = :handler)"
            ),
            {
                "id": str(uuid.uuid4()),
                "name": HANDLER_KEY,
                "display": "考勤月报 · 待确认提醒",
                "desc": _JOB_DESC,
                "handler": HANDLER_KEY,
                # 10:00 Asia/Shanghai daily — after the morning roster /
                # identity ticks, well within working hours.
                "cron": "0 10 * * *",
                "config": _json.dumps({"max_per_run": 50, "remind_after_hours": 48}),
            },
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "scheduled_jobs"):
        bind.execute(
            sa.text("DELETE FROM scheduled_jobs WHERE handler_key = :k"),
            {"k": HANDLER_KEY},
        )
    if _table_exists(bind, REPORTS) and _column_exists(bind, REPORTS, COLUMN):
        op.drop_column(REPORTS, COLUMN)