"""add note slot

Revision ID: c85fda110379
Revises: 415cd00cc261
Create Date: 2026-07-13 14:49:55.869364

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


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


def upgrade() -> None:
    """Upgrade schema."""
    with op.batch_alter_table('note') as batch_op:
        batch_op.add_column(sa.Column('slot', sa.Integer(), nullable=True))

    # Backfill: number each user's existing notes 1..N in id order (rows
    # with a NULL user_id, if any, are numbered together as their own
    # group - the uniqueness constraint added below only applies within
    # matching user_id values, same as SQL's NULL-is-distinct behavior).
    conn = op.get_bind()
    rows = conn.execute(sa.text("SELECT id, user_id FROM note ORDER BY user_id, id")).fetchall()
    counters: dict = {}
    for note_id, user_id in rows:
        counters[user_id] = counters.get(user_id, 0) + 1
        conn.execute(
            sa.text("UPDATE note SET slot = :slot WHERE id = :id"),
            {"slot": counters[user_id], "id": note_id},
        )

    with op.batch_alter_table('note') as batch_op:
        batch_op.alter_column('slot', nullable=False)
        batch_op.create_unique_constraint('uq_note_user_id_slot', ['user_id', 'slot'])


def downgrade() -> None:
    """Downgrade schema."""
    with op.batch_alter_table('note') as batch_op:
        batch_op.drop_constraint('uq_note_user_id_slot', type_='unique')
        batch_op.drop_column('slot')
