"""Use MySQL DOUBLE for approval database clock epochs.

Revision ID: 20260804_01
Revises: 20260731_03
Create Date: 2026-08-04
"""
from collections.abc import Sequence

import sqlalchemy as sa
from alembic import context, op
from sqlalchemy.dialects import mysql

revision: str = "20260804_01"
down_revision: str | Sequence[str] | None = "20260731_03"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None

_EPOCH_COLUMNS: dict[str, tuple[tuple[str, bool], ...]] = {
    "aps_approval_requests": (
        ("created_at_epoch", False),
        ("expires_at_epoch", False),
    ),
    "aps_approval_grants": (
        ("created_at_epoch", False),
        ("expires_at_epoch", False),
        ("consumed_at_epoch", True),
    ),
    "aps_approval_events": (("decided_at_epoch", False),),
}


def _alter_epoch_columns(
    *,
    existing_type: sa.types.TypeEngine,
    target_type: sa.types.TypeEngine,
) -> None:
    for table_name, columns in _EPOCH_COLUMNS.items():
        with op.batch_alter_table(table_name) as batch_op:
            for column_name, nullable in columns:
                batch_op.alter_column(
                    column_name,
                    existing_type=existing_type,
                    type_=target_type,
                    existing_nullable=nullable,
                )



def _assert_no_active_approval_state() -> None:
    if context.is_offline_mode():
        return
    connection = op.get_bind()
    pending_requests = int(
        connection.execute(
            sa.text(
                "SELECT COUNT(*) FROM aps_approval_requests "
                "WHERE status = 'PENDING'"
            )
        ).scalar_one()
    )
    active_grants = int(
        connection.execute(
            sa.text(
                "SELECT COUNT(*) FROM aps_approval_grants "
                "WHERE status = 'ACTIVE'"
            )
        ).scalar_one()
    )
    if pending_requests or active_grants:
        raise RuntimeError(
            "cannot downgrade approval clock precision while active approval "
            f"state exists: pendingRequests={pending_requests}, "
            f"activeGrants={active_grants}"
        )

def upgrade() -> None:
    approval_epoch_type = sa.Float().with_variant(mysql.DOUBLE(asdecimal=False), "mysql")
    _alter_epoch_columns(existing_type=sa.Float(), target_type=approval_epoch_type)


def downgrade() -> None:
    _assert_no_active_approval_state()
    approval_epoch_type = sa.Float().with_variant(mysql.DOUBLE(asdecimal=False), "mysql")
    _alter_epoch_columns(existing_type=approval_epoch_type, target_type=sa.Float())