"""Seed the software_billing.auto_close scheduled job row.

Revision ID: 064
Revises: 063
Create Date: 2026-04-24

Note on numbering: see sibling migration 063 for the full story.
Originally 064 → renumbered to 066 during a failed concurrent-fix
dance → now restored to 064. Final chain:
062 → 063 (software_monthly_bills) → 064 (this) → 065 (cmdb-grants).

Adds a ``scheduled_jobs`` row so the worker picks up the new
``software_billing.auto_close`` handler (see
``app/scheduled_jobs/handlers.py``). The handler computes "last
month" anchored to Asia/Shanghai and calls
``billing_service.close_month`` — creating the immutable bill
snapshot before any downstream plan-price edit can pollute history.

Cron: ``3 3 1 * *`` → every 1st at 03:03 Asia/Shanghai. Two minutes
after ``cmdb.billing_sync`` (``2 2 1 * *``) to avoid overlapping IO
peaks on the scheduler instance; ``billing_sync`` talks to external
cloud billing APIs and can take minutes on big accounts.

Idempotent at two layers:
* This migration: ``SELECT ... WHERE name = ...`` short-circuit.
* The handler: if the month was already closed (admin preempted the
  cron) it returns ``status=skipped``, never raises.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "064"
down_revision = "063"
branch_labels = None
depends_on = None

_JOB_NAME = "software_billing.auto_close"


def upgrade() -> None:
    bind = op.get_bind()
    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,
             interval_seconds, cron_expr, timezone, enabled, config,
             run_count, failure_count, created_at, updated_at)
        VALUES
            (UUID(), :n, :dn, :desc, :hk, 'cron',
             NULL, :cron, 'Asia/Shanghai', 1, JSON_OBJECT(),
             0, 0, NOW(), NOW())
        """,
    ), {
        "n": _JOB_NAME,
        "dn": "软件月账单自动关账",
        "desc": (
            "每月 1 号凌晨 3:03(亚洲/上海)把上个月关账,生成 "
            "software_monthly_bills 快照。幂等:管理员已手动关账的月份跳过。"
            "关账后修改 plan 价格不会回溯影响已结算月份。"
        ),
        "hk": _JOB_NAME,
        "cron": "3 3 1 * *",
    })


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