"""Seed the ``cmdb.cost.nightly_recompute_allocation`` scheduled job.

Revision ID: 147
Revises: 146
Create Date: 2026-05-11

Why:

* PR #241 added scope-owner attribution fallback to both
  ``ingest_cost_facts`` and ``recompute_allocation``. The ingest
  path stamps NEW facts under the fallback rule; the recompute
  path re-stamps existing facts. Both are correct, but only
  ``recompute_allocation`` rewrites historical rows — and it's
  manual-only today (an admin button on the Allocation tab plus
  the ``cost_recompute_allocation`` MCP tool).
* Prod evidence (2026-05-10): the dashboard reported
  $7,316.10 / 99.8% as 未分摊 because every fact was written
  before the fallback logic existed. The recompute had to be
  pressed by hand, and there's no guarantee the next admin
  remembers after a rule edit or scope-owner reassignment.
* Daily auto-recompute amortises the table rewrite across all
  upstream edits and ensures the "未分摊 KPI" stays accurate
  within 24h of any data change — without admin attention.

Schedule:

* ``30 11 * * *`` (Asia/Shanghai) = 11:30 SH = 03:30 UTC. Chosen
  to run AFTER the per-integration ``cmdb.cost_facts.<slug>``
  daily ingest (seeded at ``{offset} 4 * * *`` in migration 140,
  so 04:00–04:59 SH), giving each ingest several hours to finish
  before the recompute walks the freshly-ingested table.
  ``CronTrigger.from_crontab(cron_expr, timezone=row.timezone)``
  in ``server/app/worker.py`` interprets the cron expression in
  the row's timezone, so ``30 11`` with ``Asia/Shanghai`` means
  11:30 SH (NOT 11:30 UTC). The Asia/Shanghai timezone matches
  every other helix cron baseline.
* Single global cron (no per-integration fan-out): the recompute
  is multi-tenant by design — it walks every active scope across
  every cloud in a single transaction — so one row drives the
  whole platform's re-stamp.

Idempotency: ``WHERE NOT EXISTS`` on
``handler_key='cmdb.cost.nightly_recompute_allocation'`` so a
re-run is a no-op. Downgrade nukes the seeded row.
"""
from __future__ import annotations

import json as _json
import uuid

import sqlalchemy as sa

from alembic import op

revision = "147"
down_revision = "146"
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.nightly_recompute_allocation"
JOB_NAME = "cmdb.cost.nightly_recompute_allocation"


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": (
                "每日 11:30 SH(03:30 UTC)对最近 90 天的 cmdb_cost_facts "
                "重新应用 allocation rules + scope-owner 兜底,确保历史"
                "数据在 ingest 引擎升级 / 规则修改 / scope owner 变更后 "
                "24h 内重新归属。PR #241 的兜底逻辑只对 NEW writes 生效;"
                "存量数据靠本任务夜间重算。"
            ),
            "handler": HANDLER_KEY,
            # 11:30 SH = 03:30 UTC. CronTrigger.from_crontab interprets
            # the expression in the row's timezone column ('Asia/
            # Shanghai'), so ``30 11`` reads as 11:30 SH (NOT 11:30
            # UTC). Daily cost ingests run at 04:00–04:59 SH (migration
            # 140 seeds ``{offset} 4 * * *`` per integration slug),
            # leaving ~7h for ingest queues to drain before recompute
            # walks the table. China has no DST, so the SH↔UTC offset
            # is stable year-round.
            "cron": "30 11 * * *",
            "config": _json.dumps({"days": 90}),
        },
    )


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},
    )