"""add purchases

Revision ID: b1a4c3d2e1f0
Revises: fa3e1939a2a1
"""
from alembic import op
import sqlalchemy as sa

revision = "b1a4c3d2e1f0"
down_revision = "fa3e1939a2a1"
branch_labels = None
depends_on = None

def upgrade():
    op.create_table("purchases",
        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("supplier_id", sa.UUID(), nullable=False), sa.Column("reference_number", sa.String(100), nullable=False), sa.Column("purchase_date", sa.Date(), nullable=False), sa.Column("lifecycle_status", sa.String(20), nullable=False), sa.Column("payment_status", sa.String(20), nullable=False), sa.Column("received_at", sa.DateTime(timezone=True)), 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_purchase_nonnegative"),
        sa.ForeignKeyConstraint(["business_id"],["businesses.id"],ondelete="CASCADE"), sa.ForeignKeyConstraint(["branch_id"],["branches.id"],ondelete="RESTRICT"), sa.ForeignKeyConstraint(["supplier_id"],["suppliers.id"],ondelete="RESTRICT"), sa.ForeignKeyConstraint(["created_by"],["users.id"],ondelete="RESTRICT"), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("business_id","reference_number",name="uq_purchase_business_reference"))
    for col in ("business_id","branch_id","supplier_id"): op.create_index(f"ix_purchases_{col}","purchases",[col])
    op.create_table("purchase_items",
        sa.Column("id",sa.UUID(),nullable=False),sa.Column("purchase_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_cost",sa.Numeric(14,2),nullable=False),sa.Column("line_total",sa.Numeric(14,2),nullable=False),sa.Column("expiry_date",sa.Date()),sa.Column("created_at",sa.DateTime(timezone=True),nullable=False),sa.CheckConstraint("quantity > 0 AND quantity_in_base > 0 AND unit_cost >= 0 AND line_total >= 0",name="ck_purchase_item_valid"),sa.ForeignKeyConstraint(["purchase_id"],["purchases.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"),sa.PrimaryKeyConstraint("id"))
    op.create_index("ix_purchase_items_purchase_id","purchase_items",["purchase_id"])
    op.create_table("purchase_payments",
        sa.Column("id",sa.UUID(),nullable=False),sa.Column("business_id",sa.UUID(),nullable=False),sa.Column("purchase_id",sa.UUID(),nullable=False),sa.Column("supplier_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_purchase_payment_positive"),sa.ForeignKeyConstraint(["business_id"],["businesses.id"],ondelete="CASCADE"),sa.ForeignKeyConstraint(["purchase_id"],["purchases.id"],ondelete="RESTRICT"),sa.ForeignKeyConstraint(["supplier_id"],["suppliers.id"],ondelete="RESTRICT"),sa.ForeignKeyConstraint(["recorded_by"],["users.id"],ondelete="RESTRICT"),sa.PrimaryKeyConstraint("id"))
    op.create_index("ix_purchase_payments_business_id","purchase_payments",["business_id"]);op.create_index("ix_purchase_payments_purchase_id","purchase_payments",["purchase_id"])
def downgrade():
    op.drop_table("purchase_payments");op.drop_table("purchase_items");op.drop_table("purchases")