"""add sales POS foundation

Revision ID: c2d3e4f5a6b7
Revises: b1a4c3d2e1f0
"""
from alembic import op
import sqlalchemy as sa
revision='c2d3e4f5a6b7';down_revision='b1a4c3d2e1f0';branch_labels=None;depends_on=None
def upgrade():
 op.create_table('sales',sa.Column('id',sa.UUID(),primary_key=True),sa.Column('business_id',sa.UUID(),nullable=False),sa.Column('branch_id',sa.UUID(),nullable=False),sa.Column('customer_id',sa.UUID()),sa.Column('receipt_number',sa.String(100),nullable=False),sa.Column('sale_datetime',sa.DateTime(timezone=True),nullable=False),sa.Column('lifecycle_status',sa.String(20),nullable=False),sa.Column('payment_status',sa.String(20),nullable=False),sa.Column('subtotal',sa.Numeric(14,2),nullable=False),sa.Column('discount',sa.Numeric(14,2),nullable=False),sa.Column('tax',sa.Numeric(14,2),nullable=False),sa.Column('total',sa.Numeric(14,2),nullable=False),sa.Column('amount_paid',sa.Numeric(14,2),nullable=False),sa.Column('balance',sa.Numeric(14,2),nullable=False),sa.Column('notes',sa.Text()),sa.Column('created_by',sa.UUID(),nullable=False),sa.Column('created_at',sa.DateTime(timezone=True),nullable=False),sa.Column('updated_at',sa.DateTime(timezone=True),nullable=False),sa.CheckConstraint('subtotal >= 0 AND discount >= 0 AND tax >= 0 AND total >= 0 AND amount_paid >= 0 AND balance >= 0',name='ck_sale_nonnegative'),sa.ForeignKeyConstraint(['business_id'],['businesses.id'],ondelete='CASCADE'),sa.ForeignKeyConstraint(['branch_id'],['branches.id'],ondelete='RESTRICT'),sa.ForeignKeyConstraint(['customer_id'],['customers.id'],ondelete='RESTRICT'),sa.ForeignKeyConstraint(['created_by'],['users.id'],ondelete='RESTRICT'),sa.UniqueConstraint('business_id','receipt_number',name='uq_sale_business_receipt'))
 for c in ('business_id','branch_id','customer_id'):op.create_index('ix_sales_'+c,'sales',[c])
 op.create_table('sale_items',sa.Column('id',sa.UUID(),primary_key=True),sa.Column('sale_id',sa.UUID(),nullable=False),sa.Column('product_id',sa.UUID(),nullable=False),sa.Column('variant_id',sa.UUID()),sa.Column('unit_id',sa.UUID()),sa.Column('quantity',sa.Numeric(14,4),nullable=False),sa.Column('quantity_in_base',sa.Numeric(14,4),nullable=False),sa.Column('unit_price',sa.Numeric(14,2),nullable=False),sa.Column('discount',sa.Numeric(14,2),nullable=False),sa.Column('line_total',sa.Numeric(14,2),nullable=False),sa.CheckConstraint('quantity > 0 AND quantity_in_base > 0 AND unit_price >= 0 AND discount >= 0 AND line_total >= 0',name='ck_sale_item_valid'),sa.ForeignKeyConstraint(['sale_id'],['sales.id'],ondelete='RESTRICT'),sa.ForeignKeyConstraint(['product_id'],['products.id'],ondelete='RESTRICT'),sa.ForeignKeyConstraint(['variant_id'],['product_variants.id'],ondelete='RESTRICT'),sa.ForeignKeyConstraint(['unit_id'],['units.id'],ondelete='RESTRICT'))
 op.create_index('ix_sale_items_sale_id','sale_items',['sale_id'])
 op.create_table('sale_payments',sa.Column('id',sa.UUID(),primary_key=True),sa.Column('business_id',sa.UUID(),nullable=False),sa.Column('sale_id',sa.UUID(),nullable=False),sa.Column('amount',sa.Numeric(14,2),nullable=False),sa.Column('payment_method',sa.String(20),nullable=False),sa.Column('reference',sa.String(150)),sa.Column('recorded_by',sa.UUID(),nullable=False),sa.Column('paid_at',sa.DateTime(timezone=True),nullable=False),sa.CheckConstraint('amount > 0',name='ck_sale_payment_positive'),sa.ForeignKeyConstraint(['business_id'],['businesses.id'],ondelete='CASCADE'),sa.ForeignKeyConstraint(['sale_id'],['sales.id'],ondelete='RESTRICT'),sa.ForeignKeyConstraint(['recorded_by'],['users.id'],ondelete='RESTRICT'))
 op.create_index('ix_sale_payments_business_id','sale_payments',['business_id']);op.create_index('ix_sale_payments_sale_id','sale_payments',['sale_id'])
 op.create_table('sale_idempotency',sa.Column('id',sa.UUID(),primary_key=True),sa.Column('business_id',sa.UUID(),nullable=False),sa.Column('user_id',sa.UUID(),nullable=False),sa.Column('idempotency_key',sa.String(255),nullable=False),sa.Column('sale_id',sa.UUID(),nullable=False),sa.ForeignKeyConstraint(['business_id'],['businesses.id'],ondelete='CASCADE'),sa.ForeignKeyConstraint(['user_id'],['users.id'],ondelete='CASCADE'),sa.ForeignKeyConstraint(['sale_id'],['sales.id'],ondelete='RESTRICT'),sa.UniqueConstraint('business_id','user_id','idempotency_key',name='uq_sale_idempotency'))
def downgrade():
 op.drop_table('sale_idempotency');op.drop_table('sale_payments');op.drop_table('sale_items');op.drop_table('sales')