"""Seed the ``cmdb.cost_facts_monthclose`` scheduled job.

A single global monthly job that re-pulls the PREVIOUS complete
calendar month's line-item cost facts for every enabled cloud
integration, so late vendor revisions (month-end finalization,
late-arriving untagged usage, daily-accruing infra like S3/ECR/Cost
Explorer) get reconciled into ``cmdb_cost_facts``.

Why it's needed: the daily ``cmdb.cost_facts.<slug>`` cron only
re-fetches a rolling 3-day window, so any day older than 3 days
freezes at the value the vendor reported ~2-3 days post-usage. Cost
Explorer / other billing APIs keep re-rating after that, leaving a
small systematic drift vs the cloud console (observed: AWS SG May
2026 off by $3.84, entirely in the untagged bucket). This month-close
re-pull, run after the vendor finalizes the prior month (~5th),
closes that gap to the cent.

NOT the same as ``cmdb.cost.nightly_recompute_allocation`` (which
re-APPLIES allocation rules to existing facts) — this re-PULLS
amounts from the vendor. Both are needed.

Single global row (handler iterates every enabled cloud integration
internally), so it does not add to the per-account job fan-out.

Cron: ``0 7 5 * *`` (Asia/Shanghai) = 07:00 SH on the 5th. After the
daily 04:xx / 06:xx ticks and after typical vendor month-end
finalization. China has no DST so SH↔UTC is stable.

Handler: ``cmdb.cost_facts_monthclose``
([handlers.py:run_cost_facts_monthclose]).
"""
import json as _json
import uuid

import sqlalchemy as sa

from alembic import op

revision = "247"
down_revision = "246"
branch_labels = None
depends_on = None


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


HANDLER_KEY = "cmdb.cost_facts_monthclose"
JOB_NAME = "cmdb.cost_facts_monthclose"

_DESC = (
    "每月 5 号 07:00(Asia/Shanghai)对所有启用的云账号,把【上一个完整自然月】"
    "的 line-item 成本明细整月重新拉取并 upsert 进 cmdb_cost_facts —— 对账用的"
    "「月结重拉」。为什么需要:每日 cmdb.cost_facts.<slug> 只滚动重拉「昨天起往前 "
    "3 天」,使用日过了 3 天就不再重拉;而 AWS Cost Explorer / 各云账单在月末 "
    "finalize 时还会继续核价修订(尤其日累积的 S3/ECR/Cost Explorer、未打 Owner "
    "标签的 Bedrock 用量),导致 Helix 冻结的旧值与云控制台终值出现小额漂移"
    "(2026-06 实测 AWS SG 5 月差 $3.84)。本任务在云厂商完成上月账单 finalize "
    "之后(~5 号)整月重灌一次,把所有晚到的修订追平,使 cmdb_cost_facts 与云控制台"
    "对账到分。与 cmdb.cost.nightly_recompute_allocation 不同:那个重「归属」已有"
    "数据,这个重「拉取」厂商金额,两者都需要、互不替代。单例任务,内部遍历所有 "
    "enabled 云账号,不随云账号数膨胀;逐账号走 cloud_sync:cost_facts:{id} 锁,绝不"
    "与每日 tick 并发。窗口可用 config={\"start_date\",\"end_date\"} 覆写做任意区间回填。"
)


def upgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "scheduled_jobs"):
        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) "
            "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": JOB_NAME,
            "display": "云成本明细 · 月结重拉(对账)",
            "desc": _DESC,
            "handler": HANDLER_KEY,
            # 07:00 SH on the 5th — after daily ticks + vendor month-end
            # finalization. Row timezone='Asia/Shanghai' so CronTrigger
            # reads ``0 7 5`` as 07:00 SH (= 23:00 UTC on the 4th).
            "cron": "0 7 5 * *",
            "config": _json.dumps({}),
        },
    )


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