"""Add UNIQUE constraint on musehub_issues (repo_id, number)

musehub#184 (critical data-integrity bug): _next_issue_number() computes
the next per-repo issue number via SELECT MAX(number) then INSERT max+1,
with no locking and no unique constraint. Reproduced directly: firing 10
concurrent `muse hub issue create` calls against the same repo produced
genuine duplicate numbers (two rows at #197, three rows at #199) -- all
rows persisted (nothing was silently lost), but the per-repo number is
no longer a reliable unique identifier once two creates race.

This migration adds the missing UNIQUE constraint so any future race
fails loudly (IntegrityError) instead of silently producing a duplicate.
The application-level fix (row-locking musehub_repos to serialize number
allocation, so races don't even reach this constraint under normal load)
lives in musehub/services/musehub_issues.py, committed alongside this
migration.

Prerequisite: verified staging currently has zero duplicate (repo_id,
number) pairs before this migration runs (the 12 duplicate test rows
created while reproducing this bug were deleted first) -- this migration
will fail loudly on apply if any repo still has a duplicate, which is the
correct, safe behavior rather than silently dropping rows.

Revision ID: 0076
Revises: 0075
"""
from __future__ import annotations

from typing import Sequence, Union

from alembic import op


# revision identifiers, used by Alembic.
revision: str = '0076'
down_revision: Union[str, None] = '0075'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_unique_constraint(
        'uq_musehub_issues_repo_id_number', 'musehub_issues', ['repo_id', 'number'],
    )


def downgrade() -> None:
    op.drop_constraint('uq_musehub_issues_repo_id_number', 'musehub_issues', type_='unique')