"""Add v2 foundation tables.

Revision ID: 0067_add_v2_foundation_tables
Revises: 0066_add_manual_variation_assignment_tables
Create Date: 2026-08-06 17:30:00.000000
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = "0067_add_v2_foundation_tables"
down_revision = "0066_add_manual_variation_assignment_tables"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "v2_category",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("name", sa.Text(), nullable=False),
        sa.Column("parent_id", sa.BigInteger(), sa.ForeignKey("v2_category.id", ondelete="RESTRICT"), nullable=True),
        sa.Column("level", sa.Integer(), nullable=True),
        sa.Column("node_type", sa.String(length=32), nullable=False, server_default="category"),
        sa.Column("created_by", sa.Text(), nullable=True),
        sa.Column("merged_into_id", sa.BigInteger(), sa.ForeignKey("v2_category.id", ondelete="RESTRICT"), nullable=True),
        sa.Column(
            "normalized_name",
            sa.Text(),
            sa.Computed(r"lower(regexp_replace(trim(name), '\s+', ' ', 'g'))", persisted=True),
            nullable=False,
        ),
        sa.Column(
            "dedupe_scope",
            sa.BigInteger(),
            sa.Computed("COALESCE(parent_id, 0)", persisted=True),
            nullable=False,
        ),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.CheckConstraint("node_type IN ('hub','category','collection')", name="v2_category_node_type_valid"),
    )
    op.create_index(
        "uq_v2_category_no_duplicate_siblings",
        "v2_category",
        ["dedupe_scope", "node_type", "normalized_name"],
        unique=True,
        postgresql_where=sa.text("merged_into_id IS NULL"),
    )

    op.create_table(
        "v2_attribute",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("code", sa.String(length=255), nullable=False),
        sa.Column("label", sa.Text(), nullable=False),
        sa.Column("data_type", sa.String(length=64), nullable=False),
        sa.Column("is_variant_axis", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("is_channel_required", sa.Boolean(), nullable=False, server_default=sa.text("true")),
        sa.Column("applies_to_product_types", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.UniqueConstraint("code", name="uq_v2_attribute_code"),
    )

    op.create_table(
        "v2_attribute_value",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("attribute_id", sa.BigInteger(), sa.ForeignKey("v2_attribute.id", ondelete="CASCADE"), nullable=False),
        sa.Column("value_code", sa.String(length=255), nullable=False),
        sa.Column("display_label", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.UniqueConstraint("attribute_id", "value_code", name="uq_v2_attribute_value_attr_code"),
    )

    op.create_table(
        "v2_product",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("sku", sa.String(length=128), nullable=False),
        sa.Column("product_type", sa.String(length=32), nullable=False),
        sa.Column("lifecycle_status", sa.String(length=32), nullable=False, server_default="draft"),
        sa.Column("title", sa.Text(), nullable=False),
        sa.Column("slug", sa.Text(), nullable=True),
        sa.Column("seo_title", sa.Text(), nullable=True),
        sa.Column("seo_description", sa.Text(), nullable=True),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("primary_category_id", sa.BigInteger(), sa.ForeignKey("v2_category.id", ondelete="RESTRICT"), nullable=True),
        sa.Column("shopping_l1_category_id", sa.BigInteger(), sa.ForeignKey("v2_category.id", ondelete="RESTRICT"), nullable=True),
        sa.Column("shopping_l2_category_id", sa.BigInteger(), sa.ForeignKey("v2_category.id", ondelete="RESTRICT"), nullable=True),
        sa.Column("capabilities", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("attributes", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("source", sa.Text(), nullable=False, server_default="manual"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.CheckConstraint("product_type IN ('simple','configurable','bundle')", name="v2_product_type_valid"),
        sa.CheckConstraint("lifecycle_status IN ('draft','active','disabled','archived')", name="v2_product_lifecycle_status_valid"),
        sa.UniqueConstraint("sku", name="uq_v2_product_sku"),
    )

    op.create_table(
        "v2_product_category",
        sa.Column("product_id", sa.BigInteger(), sa.ForeignKey("v2_product.id", ondelete="CASCADE"), primary_key=True),
        sa.Column("category_id", sa.BigInteger(), sa.ForeignKey("v2_category.id", ondelete="RESTRICT"), primary_key=True),
    )

    op.create_table(
        "v2_configurable_link",
        sa.Column("parent_id", sa.BigInteger(), sa.ForeignKey("v2_product.id", ondelete="CASCADE"), primary_key=True),
        sa.Column("child_id", sa.BigInteger(), sa.ForeignKey("v2_product.id", ondelete="CASCADE"), primary_key=True),
        sa.Column("axis_attributes", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
    )

    op.create_index("ix_v2_configurable_link_parent_id", "v2_configurable_link", ["parent_id"])

    op.create_table(
        "v2_bundle_component",
        sa.Column("bundle_id", sa.BigInteger(), sa.ForeignKey("v2_product.id", ondelete="CASCADE"), primary_key=True),
        sa.Column("component_id", sa.BigInteger(), sa.ForeignKey("v2_product.id", ondelete="RESTRICT"), primary_key=True),
        sa.Column("qty", sa.Numeric(12, 4), nullable=False, server_default=sa.text("1")),
        sa.Column("is_required", sa.Boolean(), nullable=False, server_default=sa.text("true")),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default=sa.text("0")),
    )

    op.create_table(
        "v2_override",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("product_id", sa.BigInteger(), sa.ForeignKey("v2_product.id", ondelete="CASCADE"), nullable=False),
        sa.Column("channel", sa.Text(), nullable=True),
        sa.Column("field_path", sa.Text(), nullable=False),
        sa.Column("value", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column("created_by", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
    )
    op.create_index("ix_v2_override_product_id", "v2_override", ["product_id"])

    op.create_table(
        "v2_channel_attribute_mapping",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("channel", sa.String(length=64), nullable=False),
        sa.Column("canonical_attribute_id", sa.BigInteger(), sa.ForeignKey("v2_attribute.id", ondelete="CASCADE"), nullable=False),
        sa.Column("channel_attribute_code", sa.Text(), nullable=True),
        sa.Column("channel_attribute_id", sa.Text(), nullable=True),
        sa.Column("sync_mode", sa.String(length=64), nullable=True),
        sa.Column("is_optional", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.UniqueConstraint("channel", "canonical_attribute_id", name="uq_v2_channel_attribute_mapping"),
    )

    op.create_table(
        "v2_channel_attribute_value_mapping",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("channel", sa.String(length=64), nullable=False),
        sa.Column("canonical_attribute_value_id", sa.BigInteger(), sa.ForeignKey("v2_attribute_value.id", ondelete="CASCADE"), nullable=False),
        sa.Column("channel_option_id", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.UniqueConstraint("channel", "canonical_attribute_value_id", name="uq_v2_channel_attribute_value_mapping"),
    )

    op.create_table(
        "v2_media_asset",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("product_id", sa.BigInteger(), sa.ForeignKey("v2_product.id", ondelete="CASCADE"), nullable=False),
        sa.Column("r2_key", sa.Text(), nullable=False),
        sa.Column("public_url", sa.Text(), nullable=True),
        sa.Column("sha256", sa.String(length=128), nullable=False),
        sa.Column("mime_type", sa.String(length=255), nullable=False),
        sa.Column("width", sa.Integer(), nullable=True),
        sa.Column("height", sa.Integer(), nullable=True),
        sa.Column("alt_text", sa.Text(), nullable=True),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("role_flags", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'[]'::jsonb")),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("true")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.UniqueConstraint("product_id", "r2_key", name="uq_v2_media_asset_product_r2_key"),
    )
    op.create_index("ix_v2_media_asset_product_active", "v2_media_asset", ["product_id", "is_active", "sort_order"])

    op.create_table(
        "v2_product_collection",
        sa.Column("product_id", sa.BigInteger(), sa.ForeignKey("v2_product.id", ondelete="CASCADE"), primary_key=True),
        sa.Column("collection_id", sa.BigInteger(), sa.ForeignKey("v2_category.id", ondelete="RESTRICT"), primary_key=True),
        sa.Column("is_primary", sa.Boolean(), nullable=False, server_default=sa.text("true")),
    )
    op.create_index("ix_v2_product_collection_collection_id", "v2_product_collection", ["collection_id"])

    op.create_table(
        "v2_shopping_attribute_option_map",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("channel", sa.String(length=64), nullable=False),
        sa.Column("shopping_attribute", sa.String(length=64), nullable=False),
        sa.Column("category_id", sa.BigInteger(), sa.ForeignKey("v2_category.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("channel_option_id", sa.Text(), nullable=False),
        sa.Column("status", sa.String(length=32), nullable=False, server_default="active"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.CheckConstraint(
            "shopping_attribute IN ('shopping_l1','shopping_l2','shopping_collection')",
            name="v2_shopping_attribute_option_map_attribute_valid",
        ),
        sa.CheckConstraint(
            "status IN ('active','deprecated')",
            name="v2_shopping_attribute_option_map_status_valid",
        ),
        sa.UniqueConstraint("channel", "shopping_attribute", "category_id", name="uq_v2_shopping_attribute_option_map"),
    )

    op.create_table(
        "v2_duplicate_review_queue",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("category_id_a", sa.BigInteger(), sa.ForeignKey("v2_category.id", ondelete="CASCADE"), nullable=False),
        sa.Column("category_id_b", sa.BigInteger(), sa.ForeignKey("v2_category.id", ondelete="CASCADE"), nullable=False),
        sa.Column("similarity_score", sa.Float(), nullable=False),
        sa.Column("status", sa.String(length=32), nullable=False, server_default="pending"),
        sa.Column("reviewed_by", sa.Text(), nullable=True),
        sa.Column("reviewed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.CheckConstraint(
            "status IN ('pending','confirmed_duplicate','not_duplicate')",
            name="v2_duplicate_review_queue_status_valid",
        ),
        sa.UniqueConstraint("category_id_a", "category_id_b", name="uq_v2_duplicate_review_queue_pair"),
    )

    op.create_table(
        "v2_shopping_cascade_job",
        sa.Column("id", postgresql.UUID(as_uuid=False), primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column("operation", sa.String(length=32), nullable=False),
        sa.Column("source_category_id", sa.BigInteger(), sa.ForeignKey("v2_category.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("target_category_id", sa.BigInteger(), sa.ForeignKey("v2_category.id", ondelete="RESTRICT"), nullable=True),
        sa.Column("delete_downstream_option", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("status", sa.String(length=32), nullable=False, server_default="pending"),
        sa.Column("batch_id", postgresql.UUID(as_uuid=False), nullable=False, server_default=sa.text("gen_random_uuid()")),
        sa.Column("affected_product_count", sa.Integer(), nullable=True),
        sa.Column("created_by", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
        sa.CheckConstraint(
            "operation IN ('rename','merge','reparent','deprecate')",
            name="v2_shopping_cascade_job_operation_valid",
        ),
        sa.CheckConstraint(
            "status IN ('pending','running','completed','failed')",
            name="v2_shopping_cascade_job_status_valid",
        ),
    )

    op.create_table(
        "v2_change_event",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("product_id", sa.BigInteger(), sa.ForeignKey("v2_product.id", ondelete="CASCADE"), nullable=False),
        sa.Column("domain", sa.String(length=32), nullable=False),
        sa.Column("change_type", sa.String(length=64), nullable=False),
        sa.Column("payload", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column("batch_id", postgresql.UUID(as_uuid=False), nullable=True),
        sa.Column("triggered_by", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.CheckConstraint(
            "domain IN ('product_data','media','relations')",
            name="v2_change_event_domain_valid",
        ),
    )
    op.create_index("ix_v2_change_event_product_created", "v2_change_event", ["product_id", "created_at"])

    op.create_table(
        "v2_sync_job",
        sa.Column("id", postgresql.UUID(as_uuid=False), primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column("source", sa.Text(), nullable=False),
        sa.Column("channel", sa.String(length=64), nullable=True),
        sa.Column("domain", sa.String(length=32), nullable=True),
        sa.Column("batch_id", postgresql.UUID(as_uuid=False), nullable=True),
        sa.Column("priority_lane", sa.String(length=16), nullable=False, server_default="normal"),
        sa.Column("status", sa.String(length=32), nullable=False, server_default="pending"),
        sa.Column("triggered_by", sa.Text(), nullable=False),
        sa.Column("item_count", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("success_count", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("failure_count", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("blocked_count", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("started_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("metadata", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.CheckConstraint(
            "priority_lane IN ('urgent','normal','bulk')",
            name="v2_sync_job_priority_lane_valid",
        ),
        sa.CheckConstraint(
            "status IN ('pending','running','completed','failed','cancelled')",
            name="v2_sync_job_status_valid",
        ),
    )

    for table_name in ("v2_queue_product_data", "v2_queue_media", "v2_queue_relations"):
        op.create_table(
            table_name,
            sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
            sa.Column("sync_job_id", postgresql.UUID(as_uuid=False), sa.ForeignKey("v2_sync_job.id", ondelete="SET NULL"), nullable=True),
            sa.Column("product_id", sa.BigInteger(), sa.ForeignKey("v2_product.id", ondelete="CASCADE"), nullable=False),
            sa.Column("channel", sa.String(length=64), nullable=False),
            sa.Column("priority", sa.SmallInteger(), nullable=False, server_default=sa.text("5")),
            sa.Column("priority_lane", sa.String(length=16), nullable=False, server_default="normal"),
            sa.Column("status", sa.String(length=32), nullable=False, server_default="pending"),
            sa.Column("block_reason", sa.Text(), nullable=True),
            sa.Column("payload", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
            sa.Column("attempt_count", sa.Integer(), nullable=False, server_default=sa.text("0")),
            sa.Column("max_attempts", sa.Integer(), nullable=False, server_default=sa.text("5")),
            sa.Column("last_error", sa.Text(), nullable=True),
            sa.Column("last_response", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
            sa.Column("locked_by", sa.Text(), nullable=True),
            sa.Column("locked_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("coalesce_key", sa.Text(), nullable=False),
            sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
            sa.Column("scheduled_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
            sa.CheckConstraint(
                "priority_lane IN ('urgent','normal','bulk')",
                name=f"{table_name}_priority_lane_valid",
            ),
            sa.CheckConstraint(
                "status IN ('pending','processing','blocked','failed','dead_letter','done')",
                name=f"{table_name}_status_valid",
            ),
        )
        op.create_index(
            f"uq_{table_name}_pending_coalesce",
            table_name,
            ["coalesce_key"],
            unique=True,
            postgresql_where=sa.text("status = 'pending'"),
        )
        op.create_index(
            f"ix_{table_name}_status_priority_scheduled",
            table_name,
            ["status", "priority", "scheduled_at"],
        )

    op.create_table(
        "v2_channel_publish_state",
        sa.Column("product_id", sa.BigInteger(), sa.ForeignKey("v2_product.id", ondelete="CASCADE"), primary_key=True),
        sa.Column("channel", sa.String(length=64), primary_key=True),
        sa.Column("domain", sa.String(length=32), primary_key=True),
        sa.Column("status", sa.String(length=32), nullable=False, server_default="pending"),
        sa.Column("block_reason", sa.Text(), nullable=True),
        sa.Column("channel_ref_id", sa.Text(), nullable=True),
        sa.Column("last_event_id", sa.BigInteger(), sa.ForeignKey("v2_change_event.id", ondelete="SET NULL"), nullable=True),
        sa.Column("last_queue_item_id", sa.BigInteger(), nullable=True),
        sa.Column("last_pushed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_verified_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("verification_mode", sa.String(length=32), nullable=True),
        sa.Column("verification_result", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("last_error", sa.Text(), nullable=True),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.CheckConstraint(
            "domain IN ('product_data','media','relations')",
            name="v2_channel_publish_state_domain_valid",
        ),
        sa.CheckConstraint(
            "status IN ('pending','processing','blocked','failed','verified')",
            name="v2_channel_publish_state_status_valid",
        ),
        sa.CheckConstraint(
            "verification_mode IN ('full','sampled','escalated') OR verification_mode IS NULL",
            name="v2_channel_publish_state_verification_mode_valid",
        ),
    )

    op.create_table(
        "v2_staging_batch",
        sa.Column("id", postgresql.UUID(as_uuid=False), primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column("filename", sa.Text(), nullable=False),
        sa.Column("uploaded_by", sa.Text(), nullable=False),
        sa.Column("row_count", sa.Integer(), nullable=False),
        sa.Column("status", sa.String(length=32), nullable=False, server_default="processing"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.CheckConstraint(
            "status IN ('processing','needs_review','promotable','completed')",
            name="v2_staging_batch_status_valid",
        ),
    )

    op.create_table(
        "v2_staging_row",
        sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("batch_id", postgresql.UUID(as_uuid=False), sa.ForeignKey("v2_staging_batch.id", ondelete="CASCADE"), nullable=False),
        sa.Column("row_number", sa.Integer(), nullable=False),
        sa.Column("raw_data", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column("normalized_data", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("resolved_data", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("status", sa.String(length=32), nullable=False, server_default="pending"),
        sa.Column("blocking_fields", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.CheckConstraint(
            "status IN ('pending','blocked','resolved','promoted','skipped')",
            name="v2_staging_row_status_valid",
        ),
        sa.UniqueConstraint("batch_id", "row_number", name="uq_v2_staging_row_batch_row_number"),
    )
    op.create_index("ix_v2_staging_row_batch_status", "v2_staging_row", ["batch_id", "status"])


def downgrade() -> None:
    op.drop_index("ix_v2_staging_row_batch_status", table_name="v2_staging_row")
    op.drop_table("v2_staging_row")
    op.drop_table("v2_staging_batch")
    op.drop_table("v2_channel_publish_state")

    for table_name in ("v2_queue_relations", "v2_queue_media", "v2_queue_product_data"):
        op.drop_index(f"ix_{table_name}_status_priority_scheduled", table_name=table_name)
        op.drop_index(f"uq_{table_name}_pending_coalesce", table_name=table_name)
        op.drop_table(table_name)

    op.drop_table("v2_sync_job")
    op.drop_index("ix_v2_change_event_product_created", table_name="v2_change_event")
    op.drop_table("v2_change_event")
    op.drop_table("v2_shopping_cascade_job")
    op.drop_table("v2_duplicate_review_queue")
    op.drop_table("v2_shopping_attribute_option_map")
    op.drop_index("ix_v2_product_collection_collection_id", table_name="v2_product_collection")
    op.drop_table("v2_product_collection")
    op.drop_index("ix_v2_media_asset_product_active", table_name="v2_media_asset")
    op.drop_table("v2_media_asset")
    op.drop_table("v2_channel_attribute_value_mapping")
    op.drop_table("v2_channel_attribute_mapping")
    op.drop_index("ix_v2_override_product_id", table_name="v2_override")
    op.drop_table("v2_override")
    op.drop_table("v2_bundle_component")
    op.drop_index("ix_v2_configurable_link_parent_id", table_name="v2_configurable_link")
    op.drop_table("v2_configurable_link")
    op.drop_table("v2_product_category")
    op.drop_table("v2_product")
    op.drop_table("v2_attribute_value")
    op.drop_table("v2_attribute")
    op.drop_index("uq_v2_category_no_duplicate_siblings", table_name="v2_category")
    op.drop_table("v2_category")