102 lines
4.2 KiB
Python
102 lines
4.2 KiB
Python
"""Product library — products, output_types, order_lines tables
|
|
|
|
Revision ID: 011
|
|
Revises: 010
|
|
Create Date: 2026-03-02
|
|
|
|
"""
|
|
from typing import Sequence, Union
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision: str = "011"
|
|
down_revision: Union[str, None] = "010"
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.execute(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS products (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
pim_id VARCHAR(500) UNIQUE NOT NULL,
|
|
name VARCHAR(500),
|
|
category_key VARCHAR(100),
|
|
ebene1 VARCHAR(500),
|
|
ebene2 VARCHAR(500),
|
|
baureihe VARCHAR(500),
|
|
produkt_baureihe VARCHAR(500),
|
|
lagertyp VARCHAR(500),
|
|
name_cad_modell VARCHAR(500),
|
|
components JSONB NOT NULL DEFAULT '[]',
|
|
cad_part_materials JSONB NOT NULL DEFAULT '[]',
|
|
cad_file_id UUID REFERENCES cad_files(id) ON DELETE SET NULL,
|
|
notes TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
source_excel VARCHAR(1000),
|
|
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW()
|
|
)
|
|
"""
|
|
)
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_products_category_key ON products (category_key)")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_products_name_cad_modell ON products (name_cad_modell)")
|
|
|
|
op.execute(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS output_types (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(200) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
renderer VARCHAR(50) NOT NULL DEFAULT 'threejs',
|
|
render_settings JSONB NOT NULL DEFAULT '{}',
|
|
output_format VARCHAR(20) NOT NULL DEFAULT 'png',
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW()
|
|
)
|
|
"""
|
|
)
|
|
|
|
op.execute(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS order_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
|
|
product_id UUID NOT NULL REFERENCES products(id),
|
|
output_type_id UUID REFERENCES output_types(id),
|
|
gewuenschte_bildnummer VARCHAR(500),
|
|
item_status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
render_status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
result_path VARCHAR(1000),
|
|
render_log JSONB,
|
|
ai_validation_status VARCHAR(20) NOT NULL DEFAULT 'not_started',
|
|
ai_validation_result JSONB,
|
|
notes TEXT,
|
|
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW()
|
|
)
|
|
"""
|
|
)
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_order_lines_order_id ON order_lines (order_id)")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_order_lines_product_id ON order_lines (product_id)")
|
|
# Partial unique indexes to handle NULL output_type_id correctly
|
|
op.execute(
|
|
"CREATE UNIQUE INDEX IF NOT EXISTS uq_order_lines_tracking "
|
|
"ON order_lines (order_id, product_id) "
|
|
"WHERE output_type_id IS NULL"
|
|
)
|
|
op.execute(
|
|
"CREATE UNIQUE INDEX IF NOT EXISTS uq_order_lines_render "
|
|
"ON order_lines (order_id, product_id, output_type_id) "
|
|
"WHERE output_type_id IS NOT NULL"
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.execute("DROP TABLE IF EXISTS order_lines")
|
|
op.execute("DROP TABLE IF EXISTS output_types")
|
|
op.execute("DROP TABLE IF EXISTS products")
|