"""add expenses and other income

Revision ID: b9c0d1e2f3a4
Revises: a7b8c9d0e1f2
"""
from alembic import op
import sqlalchemy as sa

revision = "b9c0d1e2f3a4"
down_revision = "a7b8c9d0e1f2"
branch_labels = None
depends_on = None

def category(name):
    op.create_table(name, sa.Column("id",sa.UUID(),primary_key=True),sa.Column("business_id",sa.UUID(),nullable=False),sa.Column("name",sa.String(200),nullable=False),sa.Column("description",sa.Text()),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.UniqueConstraint("business_id","name",name=f"uq_{name[:-1]}_business_name")); op.create_index(f"ix_{name}_business_id",name,["business_id"])
def upgrade():
    category("expense_categories"); category("other_income_categories")
    op.create_table("expenses",sa.Column("id",sa.UUID(),primary_key=True),sa.Column("business_id",sa.UUID(),nullable=False),sa.Column("branch_id",sa.UUID()),sa.Column("category_id",sa.UUID(),nullable=False),sa.Column("description",sa.String(500),nullable=False),sa.Column("amount",sa.Numeric(14,2),nullable=False),sa.Column("expense_date",sa.Date(),nullable=False),sa.Column("payment_method",sa.String(20),nullable=False),sa.Column("reference",sa.String(200)),sa.Column("notes",sa.Text()),sa.Column("recorded_by",sa.UUID(),nullable=False),sa.Column("status",sa.String(20),nullable=False),sa.Column("created_at",sa.DateTime(timezone=True),nullable=False),sa.Column("updated_at",sa.DateTime(timezone=True),nullable=False),sa.CheckConstraint("amount > 0",name="ck_expense_amount_positive"),sa.CheckConstraint("status IN ('ACTIVE', 'CANCELLED')",name="ck_expense_status"),sa.CheckConstraint("payment_method IN ('CASH', 'POS', 'TRANSFER', 'OTHER')",name="ck_expense_payment_method"),sa.ForeignKeyConstraint(["business_id"],["businesses.id"],ondelete="CASCADE"),sa.ForeignKeyConstraint(["branch_id"],["branches.id"],ondelete="RESTRICT"),sa.ForeignKeyConstraint(["category_id"],["expense_categories.id"],ondelete="RESTRICT"),sa.ForeignKeyConstraint(["recorded_by"],["users.id"],ondelete="RESTRICT"))
    op.create_table("other_income",sa.Column("id",sa.UUID(),primary_key=True),sa.Column("business_id",sa.UUID(),nullable=False),sa.Column("branch_id",sa.UUID()),sa.Column("category_id",sa.UUID(),nullable=False),sa.Column("description",sa.String(500),nullable=False),sa.Column("amount",sa.Numeric(14,2),nullable=False),sa.Column("income_date",sa.Date(),nullable=False),sa.Column("reference",sa.String(200)),sa.Column("notes",sa.Text()),sa.Column("recorded_by",sa.UUID(),nullable=False),sa.Column("status",sa.String(20),nullable=False),sa.Column("created_at",sa.DateTime(timezone=True),nullable=False),sa.Column("updated_at",sa.DateTime(timezone=True),nullable=False),sa.CheckConstraint("amount > 0",name="ck_other_income_amount_positive"),sa.CheckConstraint("status IN ('ACTIVE', 'CANCELLED')",name="ck_other_income_status"),sa.ForeignKeyConstraint(["business_id"],["businesses.id"],ondelete="CASCADE"),sa.ForeignKeyConstraint(["branch_id"],["branches.id"],ondelete="RESTRICT"),sa.ForeignKeyConstraint(["category_id"],["other_income_categories.id"],ondelete="RESTRICT"),sa.ForeignKeyConstraint(["recorded_by"],["users.id"],ondelete="RESTRICT"))
    for table in ("expenses","other_income"):
        for col in ("business_id","branch_id","category_id"): op.create_index(f"ix_{table}_{col}",table,[col])
def downgrade():
    op.drop_table("other_income"); op.drop_table("expenses"); op.drop_table("other_income_categories"); op.drop_table("expense_categories")