"""trade_proposals_multi_requested_cards

Revision ID: 58fc464be769
Revises: cfac344e21b4
Create Date: 2026-03-28 22:09:44.129838

Replace single requested_card_id FK with requested_card_ids JSONB array so proposals
can request zero or more cards, mirroring the real-time trade system's flexibility.
"""
from typing import Sequence, Union

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


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


def upgrade() -> None:
    # Add the new column, migrate existing data, then drop the old column
    op.add_column('trade_proposals',
        sa.Column('requested_card_ids', postgresql.JSONB(astext_type=sa.Text()), nullable=True)
    )
    # Migrate any existing rows: wrap the single FK UUID into a JSON array
    op.execute("""
        UPDATE trade_proposals
        SET requested_card_ids = json_build_array(requested_card_id::text)::jsonb
        WHERE requested_card_id IS NOT NULL
    """)
    op.execute("""
        UPDATE trade_proposals
        SET requested_card_ids = '[]'::jsonb
        WHERE requested_card_ids IS NULL
    """)
    op.alter_column('trade_proposals', 'requested_card_ids', nullable=False)
    op.drop_constraint('trade_proposals_requested_card_id_fkey', 'trade_proposals', type_='foreignkey')
    op.drop_column('trade_proposals', 'requested_card_id')


def downgrade() -> None:
    op.add_column('trade_proposals',
        sa.Column('requested_card_id', sa.UUID(), nullable=True)
    )
    # Best-effort reverse: take first element of the array if present
    op.execute("""
        UPDATE trade_proposals
        SET requested_card_id = (requested_card_ids->0)::text::uuid
        WHERE jsonb_array_length(requested_card_ids) > 0
    """)
    op.drop_column('trade_proposals', 'requested_card_ids')