"""remove sequence_number from workflow_runs

Revision ID: 0ab65e1cc7fa
Revises: 4474872b0ee6
Create Date: 2025-06-19 16:33:13.377215

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


# revision identifiers, used by Alembic.
revision = '0ab65e1cc7fa'
down_revision = '4474872b0ee6'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('workflow_runs', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('workflow_run_tenant_app_sequence_idx'))
        batch_op.drop_column('sequence_number')

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###

    # WARNING: This downgrade CANNOT recover the original sequence_number values!
    # The original sequence numbers are permanently lost after the upgrade.
    # This downgrade will regenerate sequence numbers based on created_at order,
    # which may result in different values than the original sequence numbers.
    #
    # If you need to preserve original sequence numbers, use the alternative
    # migration approach that creates a backup table before removal.

    # Step 1: Add sequence_number column as nullable first
    with op.batch_alter_table('workflow_runs', schema=None) as batch_op:
        batch_op.add_column(sa.Column('sequence_number', sa.INTEGER(), autoincrement=False, nullable=True))

    # Step 2: Populate sequence_number values based on created_at order within each app
    # NOTE: This recreates sequence numbering logic but values will be different
    # from the original sequence numbers that were removed in the upgrade
    connection = op.get_bind()
    connection.execute(sa.text("""
        UPDATE workflow_runs
        SET sequence_number = subquery.row_num
        FROM (
            SELECT id, ROW_NUMBER() OVER (
                PARTITION BY tenant_id, app_id
                ORDER BY created_at, id
            ) as row_num
            FROM workflow_runs
        ) subquery
        WHERE workflow_runs.id = subquery.id
    """))

    # Step 3: Make the column NOT NULL and add the index
    with op.batch_alter_table('workflow_runs', schema=None) as batch_op:
        batch_op.alter_column('sequence_number', nullable=False)
        batch_op.create_index(batch_op.f('workflow_run_tenant_app_sequence_idx'), ['tenant_id', 'app_id', 'sequence_number'], unique=False)

    # ### end Alembic commands ###
