"""Add UUID to file_attachments

Revision ID: e9515e29ef8b
Revises: c5b574480b80
Create Date: 2026-03-20 21:58:22.006727

"""
from alembic import op
import sqlalchemy as sa
import uuid


# revision identifiers, used by Alembic.
revision = 'e9515e29ef8b'
down_revision = 'c5b574480b80'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # Step 1: Add UUID column as nullable
    op.add_column('file_attachments', sa.Column('uuid', sa.String(length=36), nullable=True))
    
    # Step 2: Populate UUID for existing rows
    from sqlalchemy.sql import table, column, select, update
    
    file_attachments_table = table('file_attachments',
        column('id', sa.Integer),
        column('uuid', sa.String(36))
    )
    
    connection = op.get_bind()
    
    # Get all existing file attachments
    result = connection.execute(select(file_attachments_table.c.id))
    for row in result:
        new_uuid = str(uuid.uuid4())
        connection.execute(
            file_attachments_table.update()
            .where(file_attachments_table.c.id == row[0])
            .values(uuid=new_uuid)
        )
    
    # Step 3: Make UUID not null and add constraints
    op.alter_column('file_attachments', 'uuid', nullable=False)
    op.create_index('ix_file_attachments_uuid', 'file_attachments', ['uuid'], unique=False)
    op.create_unique_constraint('uq_file_attachments_uuid', 'file_attachments', ['uuid'])

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('uq_file_attachments_uuid', 'file_attachments', type_='unique')
    op.drop_index('ix_file_attachments_uuid', table_name='file_attachments')
    op.drop_column('file_attachments', 'uuid')

    # ### end Alembic commands ###