"""Auditor — auto-open quarterly privileged review cycle.

Revision ID: 094
Revises: 093
Create Date: 2026-05-01

Adds one new scheduled job:

  * ``auditor.privileged_review_auto_open_quarterly`` — cron
    ``0 9 1 1,4,7,10 *`` (UTC). Fires at 09:00 UTC on the first day of
    January / April / July / October. Service implementation
    (``PrivilegedReviewService.auto_open_quarterly_cycle``) skips if a
    cycle already exists for the current quarter, so the job is fully
    idempotent — the only thing the job does is *start* the cycle so
    auditors don't have to remember to do it.

The eight existing ``auditor.*`` cron rows from migration 093 are
already seeded with ``enabled=1``, so this migration is purely additive.

═══════════════════════════════════════════════════════════════════════
Idempotency
═══════════════════════════════════════════════════════════════════════

Single ``INSERT INTO scheduled_jobs`` guarded by an existence check.
Re-running ``alembic upgrade head`` is safe.
"""
from __future__ import annotations

import json
import logging
import uuid

import sqlalchemy as sa

from alembic import op

logger = logging.getLogger(__name__)

revision = "094"
down_revision = "093"
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


def upgrade() -> None:
    bind = op.get_bind()

    if not _table_exists(bind, "scheduled_jobs"):
        return

    name = "auditor.privileged_review_auto_open_quarterly"
    exists = bind.execute(
        sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"),
        {"n": name},
    ).scalar()
    if exists:
        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) "
            "VALUES (:id, :n, :dn, :d, :hk, 'cron', :ce, "
            " 'UTC', 1, :cfg, 0, 0, NOW(), NOW())"
        ),
        {
            "id": str(uuid.uuid4()),
            "n": name,
            "dn": "Auditor — privileged review quarterly auto-open",
            "d": (
                "Auto-open a privileged-role review cycle on the first day "
                "of each calendar quarter (Jan/Apr/Jul/Oct, 09:00 UTC). "
                "Idempotent: if a cycle for the current quarter already "
                "exists, the job is a no-op."
            ),
            "hk": name,
            "ce": "0 9 1 1,4,7,10 *",
            "cfg": json.dumps({"due_after_days": 30}),
        },
    )


def downgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "scheduled_jobs"):
        return
    bind.execute(
        sa.text(
            "DELETE FROM scheduled_jobs "
            "WHERE name = 'auditor.privileged_review_auto_open_quarterly'"
        )
    )