"""Add status to magento_product_versions.

Revision ID: 0015_add_product_version_status_backfill
Revises: 0014_queue_attempts_stale_recovery
Create Date: 2026-03-04
"""

from alembic import op
import sqlalchemy as sa

revision = "0015_add_product_version_status_backfill"
down_revision = "0014_queue_attempts_stale_recovery"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "magento_product_versions",
        sa.Column("status", sa.String(length=16), nullable=False, server_default="applied"),
    )
    # Backfill historical failures: if a sync item failed for a version_id, mark version failed.
    op.execute(
        """
        UPDATE magento_product_versions AS v
        SET status = 'failed'
        FROM magento_sync_items AS i
        WHERE i.version_id = v.id
          AND i.status = 'failed'
        """
    )


def downgrade() -> None:
    op.drop_column("magento_product_versions", "status")