"""add products and inventory foundation

Revision ID: 8ed7e92275d5
Revises: f5c94e7b02a1
Create Date: 2026-09-20 14:13:26.354807

"""
from typing import Sequence, Union

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

# revision identifiers, used by Alembic.
revision: str = '8ed7e92275d5'
down_revision: Union[str, Sequence[str], None] = 'f5c94e7b02a1'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('product_categories',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('business_id', sa.UUID(), nullable=False),
    sa.Column('name', sa.String(length=150), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['business_id'], ['businesses.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('business_id', 'name', name='uq_product_category_business_name')
    )
    op.create_index(op.f('ix_product_categories_business_id'), 'product_categories', ['business_id'], unique=False)
    op.create_table('units',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('business_id', sa.UUID(), nullable=True),
    sa.Column('name', sa.String(length=100), nullable=False),
    sa.Column('symbol', sa.String(length=30), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['business_id'], ['businesses.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_units_business_id'), 'units', ['business_id'], unique=False)
    op.create_table('products',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('business_id', sa.UUID(), nullable=False),
    sa.Column('category_id', sa.UUID(), nullable=True),
    sa.Column('base_unit_id', sa.UUID(), nullable=True),
    sa.Column('name', sa.String(length=200), nullable=False),
    sa.Column('product_type', sa.String(length=30), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('sku', sa.String(length=100), nullable=True),
    sa.Column('barcode', sa.String(length=100), nullable=True),
    sa.Column('purchase_price', sa.Numeric(precision=14, scale=2), nullable=True),
    sa.Column('selling_price', sa.Numeric(precision=14, scale=2), nullable=False),
    sa.Column('low_stock_alert', sa.Numeric(precision=14, scale=3), nullable=True),
    sa.Column('track_stock', sa.Boolean(), nullable=False),
    sa.Column('expiry_date', sa.Date(), nullable=True),
    sa.Column('image_url', sa.String(length=500), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['base_unit_id'], ['units.id'], ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['business_id'], ['businesses.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['category_id'], ['product_categories.id'], ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('business_id', 'barcode', name='uq_product_business_barcode'),
    sa.UniqueConstraint('business_id', 'sku', name='uq_product_business_sku')
    )
    op.create_index(op.f('ix_products_business_id'), 'products', ['business_id'], unique=False)
    op.create_table('product_unit_conversions',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('product_id', sa.UUID(), nullable=False),
    sa.Column('unit_id', sa.UUID(), nullable=False),
    sa.Column('quantity_in_base', sa.Numeric(precision=14, scale=4), nullable=False),
    sa.Column('selling_price', sa.Numeric(precision=14, scale=2), nullable=True),
    sa.Column('is_purchase_unit', sa.Boolean(), nullable=False),
    sa.Column('is_selling_unit', sa.Boolean(), nullable=False),
    sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['unit_id'], ['units.id'], ondelete='RESTRICT'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('product_id', 'unit_id', name='uq_product_unit_conversion')
    )
    op.create_table('product_variants',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('product_id', sa.UUID(), nullable=False),
    sa.Column('business_id', sa.UUID(), nullable=False),
    sa.Column('name', sa.String(length=200), nullable=False),
    sa.Column('attributes', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
    sa.Column('sku', sa.String(length=100), nullable=True),
    sa.Column('barcode', sa.String(length=100), nullable=True),
    sa.Column('selling_price', sa.Numeric(precision=14, scale=2), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.ForeignKeyConstraint(['business_id'], ['businesses.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('business_id', 'barcode', name='uq_variant_business_barcode'),
    sa.UniqueConstraint('business_id', 'sku', name='uq_variant_business_sku')
    )
    op.create_index(op.f('ix_product_variants_business_id'), 'product_variants', ['business_id'], unique=False)
    op.create_index(op.f('ix_product_variants_product_id'), 'product_variants', ['product_id'], unique=False)
    op.create_table('branch_inventory',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('business_id', sa.UUID(), nullable=False),
    sa.Column('branch_id', sa.UUID(), nullable=False),
    sa.Column('product_id', sa.UUID(), nullable=False),
    sa.Column('variant_id', sa.UUID(), nullable=True),
    sa.Column('inventory_key', sa.String(length=36), nullable=False),
    sa.Column('quantity', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('low_stock_threshold', sa.Numeric(precision=14, scale=3), nullable=True),
    sa.Column('expiry_date', sa.Date(), nullable=True),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['branch_id'], ['branches.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['business_id'], ['businesses.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['variant_id'], ['product_variants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('branch_id', 'inventory_key', name='uq_branch_inventory_item')
    )
    op.create_index(op.f('ix_branch_inventory_branch_id'), 'branch_inventory', ['branch_id'], unique=False)
    op.create_index(op.f('ix_branch_inventory_business_id'), 'branch_inventory', ['business_id'], unique=False)
    op.create_table('inventory_transfers',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('business_id', sa.UUID(), nullable=False),
    sa.Column('source_branch_id', sa.UUID(), nullable=False),
    sa.Column('destination_branch_id', sa.UUID(), nullable=False),
    sa.Column('product_id', sa.UUID(), nullable=False),
    sa.Column('variant_id', sa.UUID(), nullable=True),
    sa.Column('quantity', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('performed_by_user_id', sa.UUID(), nullable=False),
    sa.Column('comment', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['business_id'], ['businesses.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['destination_branch_id'], ['branches.id'], ondelete='RESTRICT'),
    sa.ForeignKeyConstraint(['performed_by_user_id'], ['users.id'], ondelete='RESTRICT'),
    sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['source_branch_id'], ['branches.id'], ondelete='RESTRICT'),
    sa.ForeignKeyConstraint(['variant_id'], ['product_variants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_inventory_transfers_business_id'), 'inventory_transfers', ['business_id'], unique=False)
    op.create_table('stock_adjustments',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('business_id', sa.UUID(), nullable=False),
    sa.Column('branch_id', sa.UUID(), nullable=False),
    sa.Column('product_id', sa.UUID(), nullable=False),
    sa.Column('variant_id', sa.UUID(), nullable=True),
    sa.Column('quantity_changed', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('previous_quantity', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('resulting_quantity', sa.Numeric(precision=14, scale=3), nullable=False),
    sa.Column('reason', sa.String(length=20), nullable=False),
    sa.Column('comment', sa.Text(), nullable=True),
    sa.Column('performed_by_user_id', sa.UUID(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['branch_id'], ['branches.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['business_id'], ['businesses.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['performed_by_user_id'], ['users.id'], ondelete='RESTRICT'),
    sa.ForeignKeyConstraint(['product_id'], ['products.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['variant_id'], ['product_variants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_stock_adjustments_business_id'), 'stock_adjustments', ['business_id'], unique=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_stock_adjustments_business_id'), table_name='stock_adjustments')
    op.drop_table('stock_adjustments')
    op.drop_index(op.f('ix_inventory_transfers_business_id'), table_name='inventory_transfers')
    op.drop_table('inventory_transfers')
    op.drop_index(op.f('ix_branch_inventory_business_id'), table_name='branch_inventory')
    op.drop_index(op.f('ix_branch_inventory_branch_id'), table_name='branch_inventory')
    op.drop_table('branch_inventory')
    op.drop_index(op.f('ix_product_variants_product_id'), table_name='product_variants')
    op.drop_index(op.f('ix_product_variants_business_id'), table_name='product_variants')
    op.drop_table('product_variants')
    op.drop_table('product_unit_conversions')
    op.drop_index(op.f('ix_products_business_id'), table_name='products')
    op.drop_table('products')
    op.drop_index(op.f('ix_units_business_id'), table_name='units')
    op.drop_table('units')
    op.drop_index(op.f('ix_product_categories_business_id'), table_name='product_categories')
    op.drop_table('product_categories')
    # ### end Alembic commands ###