"""Add pr_number sequential integer to proposals.

Each proposal now gets a per-repository sequential integer (pr_number) in
addition to its UUID primary key, mirroring idiomatic proposal numbering. The
combination (repo_id, pr_number) is unique and indexed for fast lookup.

Revision ID: 0009
Revises: 0008
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision: str = "0009"
down_revision: str = "0008"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "musehub_pull_requests",
        sa.Column("pr_number", sa.Integer(), nullable=False, server_default="0"),
    )
    op.create_index(
        "ix_musehub_pull_requests_repo_pr_number",
        "musehub_pull_requests",
        ["repo_id", "pr_number"],
    )
    op.create_unique_constraint(
        "uq_musehub_pull_requests_repo_pr_number",
        "musehub_pull_requests",
        ["repo_id", "pr_number"],
    )
    # Remove the temporary server_default now that the column exists.
    op.alter_column(
        "musehub_pull_requests",
        "pr_number",
        server_default=None,
    )


def downgrade() -> None:
    op.drop_constraint(
        "uq_musehub_pull_requests_repo_pr_number",
        "musehub_pull_requests",
        type_="unique",
    )
    op.drop_index(
        "ix_musehub_pull_requests_repo_pr_number",
        table_name="musehub_pull_requests",
    )
    op.drop_column("musehub_pull_requests", "pr_number")