"""Procurement budget alert edge-trigger state column.

Adds ``procurement_budgets.last_alerted_threshold`` (nullable INT) so the
budget-threshold reminder cron can fire on transition only — the highest
utilization tier (80 / 95) already alerted to the owner is persisted here,
and the cron pushes ONLY when the live tier rises above it. This replaces
the previous level-triggered behavior where any pool sitting above 80%
re-notified its owner every ISO week forever.

Purely additive + idempotent (column-exists guard) — safe to re-run.

Revision ID: 225
Revises: 224
Create Date: 2026-05-30
"""
from __future__ import annotations

import logging

import sqlalchemy as sa
from alembic import op


revision = "225"
down_revision = "224"
branch_labels = None
depends_on = None


_logger = logging.getLogger("alembic.runtime.migration")


def _column_exists(bind, table: str, column: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND COLUMN_NAME = :c LIMIT 1"
        ),
        {"t": table, "c": column},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _column_exists(bind, "procurement_budgets", "last_alerted_threshold"):
        op.add_column(
            "procurement_budgets",
            sa.Column("last_alerted_threshold", sa.Integer(), nullable=True),
        )
        _logger.info(
            "procurement migration 225: "
            "procurement_budgets.last_alerted_threshold added."
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _column_exists(bind, "procurement_budgets", "last_alerted_threshold"):
        op.drop_column("procurement_budgets", "last_alerted_threshold")