"""Tier the cmdb.resource_sync default cadence: hourly → 4h (hot) / 12h (cold).

2026-07-19 job-inventory audit: 27 enabled ``cmdb.resource_sync`` rows
(one per cloud account × capability) each ran HOURLY — ~1 600 ok-runs
per 3-day window with **100% zero changes written** (``upserted=0
deleted=0`` on every single run). Cloud inventory at a ~500-person
company changes a few times a week, not hourly; that fleet was also the
exact cron cluster that drained the DB connection pool in the
2026-07-19 incident (see #2005/#2006).

New tiered defaults (mirrors ``_CAPABILITY_SYNC_PERIOD_HOURS`` in
``app/domains/cmdb/scheduled_jobs_provisioning.py`` — replicated here
verbatim because migrations must stay standalone):

  * HOT  every 4h  — servers, iam, applications
  * COLD every 12h — domains, dns_records, certificates, cdn,
                     object_storage, load_balancers

This migration rewrites ONLY rows whose ``cron_expr`` still equals the
old code default (``{sha256(name)%60} * * * *``) — a row whose cadence
an admin customised in /admin/scheduled-jobs does not match that shape
and is left untouched. Idempotent: on a second run every row already
carries the tiered expression (or an admin value) and nothing matches.

The reconciler (``sync_jobs_for_integration``) never touches cadence on
existing rows, so without this data migration the new defaults would
only ever apply to future integrations.

Revision ID: 418
Revises: 417
"""
import hashlib

import sqlalchemy as sa

from alembic import op

revision = "418"
down_revision = "417"
branch_labels = None
depends_on = None

# Replicated from app.domains.cmdb.scheduled_jobs_provisioning — keep in
# sync manually; migrations must not import app code.
_PERIOD_HOURS = {
    "servers": 4,
    "iam": 4,
    "applications": 4,
    "domains": 12,
    "dns_records": 12,
    "certificates": 12,
    "cdn": 12,
    "object_storage": 12,
    "load_balancers": 12,
}
_DEFAULT_PERIOD_HOURS = 4


def _offset_minutes(key: str) -> int:
    return int(hashlib.sha256(key.encode()).hexdigest(), 16) % 60


def _capability_cron(name: str, capability: str) -> str:
    minute = _offset_minutes(name)
    period = _PERIOD_HOURS.get(capability, _DEFAULT_PERIOD_HOURS)
    phase = int(hashlib.sha256(f"{name}|phase".encode()).hexdigest(), 16) % period
    return f"{minute} {phase}-23/{period} * * *"


def upgrade() -> None:
    bind = op.get_bind()
    rows = bind.execute(
        sa.text(
            "SELECT id, name, cron_expr FROM scheduled_jobs "
            "WHERE handler_key = 'cmdb.resource_sync'"
        )
    ).fetchall()
    for row_id, name, cron_expr in rows:
        old_default = f"{_offset_minutes(name)} * * * *"
        if (cron_expr or "").strip() != old_default:
            continue  # admin-customised (or already tiered) — hands off
        capability = name.rsplit(".", 1)[-1]
        bind.execute(
            sa.text(
                "UPDATE scheduled_jobs SET cron_expr = :c, "
                "updated_at = NOW() WHERE id = :i"
            ),
            {"c": _capability_cron(name, capability), "i": row_id},
        )


def downgrade() -> None:
    bind = op.get_bind()
    rows = bind.execute(
        sa.text(
            "SELECT id, name, cron_expr FROM scheduled_jobs "
            "WHERE handler_key = 'cmdb.resource_sync'"
        )
    ).fetchall()
    for row_id, name, cron_expr in rows:
        capability = name.rsplit(".", 1)[-1]
        if (cron_expr or "").strip() != _capability_cron(name, capability):
            continue
        bind.execute(
            sa.text(
                "UPDATE scheduled_jobs SET cron_expr = :c, "
                "updated_at = NOW() WHERE id = :i"
            ),
            {"c": f"{_offset_minutes(name)} * * * *", "i": row_id},
        )