"""Fold the maintenance re-validation batch from app_state into sysconfig (B-core-11)

The admin-tunable `maintenance_revalidate_batch` lived in its own `app_state` column with a
hand-rolled override-or-default reader in `app.state`, duplicating the sysconfig SPECS pattern. It's
now a sysconfig key (`system_config` store), so every admin knob lives in one place. This migration
carries any existing override into the sysconfig store, then drops the dead column. Reversible.
"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "0070_fold_maintenance_batch"
down_revision: Union[str, None] = "0069_job_index_hygiene"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Preserve a customized override: copy it into the sysconfig store before the column goes.
    op.execute(
        """
        INSERT INTO system_config (key, value, encrypted)
        SELECT 'maintenance_revalidate_batch', maintenance_revalidate_batch::text, false
        FROM app_state
        WHERE id = 1 AND maintenance_revalidate_batch IS NOT NULL
        ON CONFLICT (key) DO NOTHING
        """
    )
    op.drop_column("app_state", "maintenance_revalidate_batch")


def downgrade() -> None:
    op.add_column(
        "app_state",
        sa.Column("maintenance_revalidate_batch", sa.Integer(), nullable=True),
    )
    # Carry the value back onto the row, then drop the sysconfig override (symmetric restore).
    op.execute(
        """
        UPDATE app_state SET maintenance_revalidate_batch =
            (SELECT value::int FROM system_config WHERE key = 'maintenance_revalidate_batch')
        WHERE id = 1
          AND EXISTS (SELECT 1 FROM system_config WHERE key = 'maintenance_revalidate_batch')
        """
    )
    op.execute("DELETE FROM system_config WHERE key = 'maintenance_revalidate_batch'")