88 lines
3.1 KiB
Python
88 lines
3.1 KiB
Python
"""Product variants — per-product material variant support
|
|
|
|
Revision ID: 022
|
|
Revises: 021
|
|
Create Date: 2026-03-03
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
from sqlalchemy.dialects.postgresql import UUID, JSONB
|
|
|
|
revision = "022"
|
|
down_revision = "021"
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
# --- New table: product_variants ---
|
|
op.create_table(
|
|
"product_variants",
|
|
sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
|
|
sa.Column("product_id", UUID(as_uuid=True), sa.ForeignKey("products.id", ondelete="CASCADE"), nullable=False, index=True),
|
|
sa.Column("name", sa.String(500), nullable=False),
|
|
sa.Column("gewuenschte_bildnummer", sa.String(500), nullable=True),
|
|
sa.Column("components", JSONB, nullable=False, server_default="[]"),
|
|
sa.Column("is_default", sa.Boolean, nullable=False, server_default="false"),
|
|
sa.Column("source_excel", sa.String(1000), nullable=True),
|
|
sa.Column("created_at", sa.DateTime, server_default=sa.func.now(), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime, server_default=sa.func.now(), nullable=False),
|
|
)
|
|
# Unique constraint: (product_id, lower(name))
|
|
op.create_index(
|
|
"uq_product_variants_product_name",
|
|
"product_variants",
|
|
[sa.text("product_id"), sa.text("lower(name)")],
|
|
unique=True,
|
|
)
|
|
|
|
# --- Alter products ---
|
|
op.add_column("products", sa.Column("arbeitspaket", sa.String(500), nullable=True))
|
|
|
|
# Drop existing unique constraint on pim_id — PIM-ID is a class-level
|
|
# identifier shared by many products so it must NOT be unique.
|
|
op.drop_constraint("products_pim_id_key", "products", type_="unique")
|
|
op.create_index(
|
|
"uq_products_produkt_baureihe",
|
|
"products",
|
|
[sa.text("lower(produkt_baureihe)")],
|
|
unique=True,
|
|
postgresql_where=sa.text("produkt_baureihe IS NOT NULL AND is_active = true"),
|
|
)
|
|
|
|
# --- Alter order_lines ---
|
|
op.add_column(
|
|
"order_lines",
|
|
sa.Column(
|
|
"variant_id",
|
|
UUID(as_uuid=True),
|
|
sa.ForeignKey("product_variants.id", ondelete="SET NULL"),
|
|
nullable=True,
|
|
),
|
|
)
|
|
|
|
# --- Backfill: create default variants for existing products ---
|
|
op.execute("""
|
|
INSERT INTO product_variants (id, product_id, name, components, is_default, source_excel, created_at, updated_at)
|
|
SELECT
|
|
gen_random_uuid(),
|
|
p.id,
|
|
COALESCE(p.name, p.pim_id),
|
|
COALESCE(p.components, '[]'::jsonb),
|
|
true,
|
|
p.source_excel,
|
|
NOW(),
|
|
NOW()
|
|
FROM products p
|
|
WHERE p.name IS NOT NULL OR p.pim_id IS NOT NULL
|
|
""")
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_column("order_lines", "variant_id")
|
|
op.drop_index("uq_products_produkt_baureihe", "products")
|
|
op.create_unique_constraint("products_pim_id_key", "products", ["pim_id"])
|
|
op.drop_column("products", "arbeitspaket")
|
|
op.drop_index("uq_product_variants_product_name", "product_variants")
|
|
op.drop_table("product_variants")
|