"""Give the month close a second pass, and let the BQ recon wait for it.

Revision ID: 481
Revises: 480

2026-08-16, measured on prod. ``cmdb.cost_facts_monthclose`` re-pulls the
previous month on the 5th, and ``cmdb.cost_recon.aliyun_bq`` compares the
result against the finance warehouse on the 8th. For Aliyun the 5th is
too early: at 2026-08-05 00:28 BSS returned **133** line items for
2026-07-31 on account ``5753857577068077``; the same query on 2026-08-16
returns **536** (\\$473.88 vs the \\$91.93 we stored). That single
unsettled day is the whole \\$381.95 the July reconciliation flagged.

So the close was reading a bill the vendor had not finished writing, and
then the recon compared that half-written month and reported the vendor's
lag as our drift. Both cron rows move:

  * close  ``0 7 5 * *``  → ``0 7 5,12 * *`` — keep the 5th (finance gets
    an early view) and add a 12th pass to converge on late settlement.
    The re-pull is idempotent (per-(scope, date) evict + insert), so a
    second pass costs vendor API time and changes nothing where the
    vendor already agreed with us.
  * recon  ``0 9 8 * *`` → ``0 9 14 * *`` — after the second pass. A
    recon that runs before the data it audits is finished manufactures
    false drift, which is worse than no recon: it trains us to ignore it.

12 days is a bound, not a measurement — nobody knows the exact hour
Aliyun finalises. That is precisely why the recon moves rather than
disappears: if 12 days is still short, next month's report says so in
dollars instead of leaving it invisible.

Only rewrites a row that still carries the seeded expression, so a
deliberate operator change in the admin console is never clobbered.
"""
import sqlalchemy as sa

from alembic import op

revision = "481"
down_revision = "480"
branch_labels = None
depends_on = None

# (handler_key, expected-current, new)
_MOVES = (
    ("cmdb.cost_facts_monthclose", "0 7 5 * *", "0 7 5,12 * *"),
    ("cmdb.cost_recon.aliyun_bq", "0 9 8 * *", "0 9 14 * *"),
)


# The console renders ``description``, which was seeded by migrations 384 /
# 478 and states the old times in prose. Leaving it is worse than leaving
# the cron alone would have been: an operator reads "每月 8 号 09:00" and
# plans around a run that no longer happens then. Patched with REPLACE on
# the exact stale phrases rather than an overwrite, so anything an operator
# appended survives.
_PHRASES = (
    ("cmdb.cost_facts_monthclose",
     "每月 5 号 07:00", "每月 5 号 / 12 号 07:00"),
    ("cmdb.cost_recon.aliyun_bq",
     "每月 8 号 09:00", "每月 14 号 09:00"),
    ("cmdb.cost_recon.aliyun_bq",
     "(5 号 07:00 月结重拉)之后三天",
     "(5 号 / 12 号 07:00 月结重拉)的第二遍之后两天"),
)


def _retime(pairs: tuple[tuple[str, str, str], ...]) -> None:
    for handler_key, expect, want in pairs:
        op.get_bind().execute(
            sa.text(
                "UPDATE scheduled_jobs SET cron_expr = :want, "
                "updated_at = NOW() "
                "WHERE handler_key = :k AND cron_expr = :expect"
            ),
            {"k": handler_key, "expect": expect, "want": want},
        )


def _reword(pairs: tuple[tuple[str, str, str], ...]) -> None:
    for handler_key, old, new in pairs:
        op.get_bind().execute(
            sa.text(
                "UPDATE scheduled_jobs "
                "SET description = REPLACE(description, :old, :new) "
                "WHERE handler_key = :k AND description LIKE :like"
            ),
            {"k": handler_key, "old": old, "new": new, "like": f"%{old}%"},
        )


def upgrade() -> None:
    _retime(_MOVES)
    _reword(_PHRASES)


def downgrade() -> None:
    _retime(tuple((k, new, old) for k, old, new in _MOVES))
    _reword(tuple((k, new, old) for k, old, new in _PHRASES))