"""Month-end book-close automation crons (book-close PR6).

Seeds two scheduled jobs, both **DISABLED** (enabled=0) — the controller turns
them on when ready (same posture as the NetSuite GL sync cron):

  * finance.close_auto_roll_forward  — month-start: clone the previous period's
                                       checklist into the current period
                                       (idempotent; China-TZ month boundary).
  * finance.close_deadline_reminder  — daily: remind close-task OWNERS of
                                       overdue / due-soon tasks (pull-first;
                                       opt-in push, finance owners only).

Handlers live in app/scheduled_jobs/finance_handlers.py (imported by both
worker._run() and main.py, so they auto-register). Idempotent: skips a job
whose name already exists.

Revision ID: 358
Revises: 357
"""
from __future__ import annotations

import json
import uuid

import sqlalchemy as sa

from alembic import op

revision = "358"
down_revision = "357"
branch_labels = None
depends_on = None

_JOBS = [
    {
        "name": "finance.close_auto_roll_forward",
        "display_name": "财务关账 · 月初自动滚动开新月",
        "description": (
            "月初自动为当前关账期克隆上一期清单(滚月):状态重置、常态任务起始"
            "进行中、按新期锚点重算截止日。幂等(当前期已有任务则跳过)。中国时区"
            "判月界。默认禁用,控制器就绪后开启。"
        ),
        "cron": "0 2 1-6 * *",
        "config": {},
    },
    {
        "name": "finance.close_deadline_reminder",
        "display_name": "财务关账 · 截止日提醒",
        "description": (
            "为当前关账期内逾期/即将到期(默认 2 天)且未完成的关账任务向负责人发"
            "汇总提醒。纯拉取为主,opt-in 推送,仅推送关账任务负责人(财务团队),"
            "绝不打扰普通员工/经理。默认禁用。"
        ),
        "cron": "0 1 * * *",
        "config": {"within_days": 2, "push_feishu": False},
    },
]


def _table_exists(bind, table: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t"
        ),
        {"t": table},
    ).fetchone() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "scheduled_jobs"):
        return
    for job in _JOBS:
        exists = bind.execute(
            sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"),
            {"n": job["name"]},
        ).scalar()
        if exists:
            continue
        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, 'Asia/Shanghai', 0, :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()
    if not _table_exists(bind, "scheduled_jobs"):
        return
    for job in _JOBS:
        bind.execute(
            sa.text("DELETE FROM scheduled_jobs WHERE name = :n"),
            {"n": job["name"]},
        )