"""Add magento_sync_pending for connection-scoped resumable sync.

Work to be posted to Magento is stored here. UPSERT on plan: if pending exists
for (connection_id, sku, action), update payload; else insert. Enables resume
after restart and merging new feed data into existing pending.

Revision ID: 0023_add_magento_sync_pending
Revises: 0022_add_queue_job_id
Create Date: 2026-03-17 00:00:00.000000
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB

revision = "0023_add_magento_sync_pending"
down_revision = "0022_add_queue_job_id"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "magento_sync_pending",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("connection_id", sa.Integer(), sa.ForeignKey("magento_connections.id"), nullable=False),
        sa.Column("sku", sa.String(128), nullable=False),
        sa.Column("action", sa.String(16), nullable=False),
        sa.Column("payload", JSONB, nullable=True),
        sa.Column("status", sa.String(32), nullable=False, server_default="pending"),
        sa.Column("pass_number", sa.Integer(), nullable=False, server_default="1"),
        sa.Column("idempotency_key", sa.Text, nullable=True),
        sa.Column("error", sa.Text, nullable=True),
        sa.Column("duration_ms", sa.Integer, nullable=True),
        sa.Column("http_status", sa.Integer, nullable=True),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint("connection_id", "sku", "action", name="uq_magento_sync_pending_conn_sku_action"),
    )
    op.create_index(
        "ix_magento_sync_pending_conn_status",
        "magento_sync_pending",
        ["connection_id", "status"],
        unique=False,
    )


def downgrade() -> None:
    op.drop_index("ix_magento_sync_pending_conn_status", table_name="magento_sync_pending")
    op.drop_table("magento_sync_pending")