"""add miner control foundation

Revision ID: 0015_miner_control_foundation
Revises: 0014_repair_parts_tracking
Create Date: 2026-08-24
"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql


revision: str = "0015_miner_control_foundation"
down_revision: Union[str, None] = "0014_repair_parts_tracking"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

QUEUE_STATUSES = ("pending", "claimed", "completed", "failed", "cancelled")


def _format_values(values: object) -> str:
    return ", ".join(str(value) for value in (values or []))


def upgrade() -> None:
    connection = op.get_bind()
    unknown_statuses = connection.execute(
        sa.text(
            """
            SELECT array_agg(DISTINCT command_status ORDER BY command_status)
            FROM minerops_agent_command_queue
            WHERE command_status NOT IN ('pending', 'claimed', 'completed', 'failed', 'cancelled')
            """
        ),
    ).scalar()
    if unknown_statuses:
        raise RuntimeError(
            "Cannot upgrade to 0015: minerops_agent_command_queue contains "
            f"unsupported command_status values: {_format_values(unknown_statuses)}. "
            "Correct those rows to pending, claimed, completed, failed, or cancelled "
            "before retrying the migration."
        )

    duplicate_live_miners = connection.execute(
        sa.text(
            """
            SELECT request_payload ->> 'miner_id' AS miner_id
            FROM minerops_agent_command_queue
            WHERE command_type = 'miner_control'
              AND command_status IN ('pending', 'claimed')
              AND nullif(trim(request_payload ->> 'miner_id'), '') IS NOT NULL
            GROUP BY request_payload ->> 'miner_id'
            HAVING count(*) > 1
            ORDER BY request_payload ->> 'miner_id'
            """
        )
    ).scalars().all()
    if duplicate_live_miners:
        raise RuntimeError(
            "Cannot upgrade to 0015: more than one pending or claimed miner_control "
            "command exists for miner UUID(s): "
            f"{_format_values(duplicate_live_miners)}. Resolve or cancel the duplicate "
            "live commands before retrying the migration; no queue history was changed."
        )

    op.add_column(
        "minerops_site_agents",
        sa.Column("site_id", postgresql.UUID(as_uuid=True), nullable=True),
    )
    op.create_foreign_key(
        "fk_minerops_site_agents_site_id",
        "minerops_site_agents",
        "sites",
        ["site_id"],
        ["id"],
        ondelete="RESTRICT",
    )
    op.create_index(
        "uq_minerops_site_agents_site_id",
        "minerops_site_agents",
        ["site_id"],
        unique=True,
        postgresql_where=sa.text("site_id IS NOT NULL"),
    )
    op.create_check_constraint(
        "ck_minerops_agent_command_queue_status",
        "minerops_agent_command_queue",
        "command_status IN ('pending', 'claimed', 'completed', 'failed', 'cancelled')",
    )
    op.create_index(
        "uq_agent_queue_live_miner_control",
        "minerops_agent_command_queue",
        [sa.text("(request_payload ->> 'miner_id')")],
        unique=True,
        postgresql_where=sa.text(
            "command_type = 'miner_control' "
            "AND command_status IN ('pending', 'claimed')"
        ),
    )
    op.create_index(
        "idx_agent_queue_miner_control_history",
        "minerops_agent_command_queue",
        [
            sa.text("(request_payload ->> 'miner_id')"),
            sa.text("created_at DESC"),
            sa.text("id DESC"),
        ],
        postgresql_where=sa.text("command_type = 'miner_control'"),
    )


def downgrade() -> None:
    op.drop_index(
        "idx_agent_queue_miner_control_history",
        table_name="minerops_agent_command_queue",
    )
    op.drop_index(
        "uq_agent_queue_live_miner_control",
        table_name="minerops_agent_command_queue",
    )
    op.drop_constraint(
        "ck_minerops_agent_command_queue_status",
        "minerops_agent_command_queue",
        type_="check",
    )
    op.drop_index(
        "uq_minerops_site_agents_site_id",
        table_name="minerops_site_agents",
    )
    op.drop_constraint(
        "fk_minerops_site_agents_site_id",
        "minerops_site_agents",
        type_="foreignkey",
    )
    op.drop_column("minerops_site_agents", "site_id")