"""Final Phase 2 business schema."""

from alembic import op
import sqlalchemy as sa

revision = "20260919_final_phase2"
down_revision = "20260919_initial_phase1"
branch_labels = None
depends_on = None
money = sa.Numeric(18, 2)
quantity = sa.Numeric(18, 3)


def upgrade() -> None:
    op.add_column("products", sa.Column("brand", sa.String(120), nullable=True))
    op.add_column("products", sa.Column("packaging", sa.String(120), nullable=True))
    op.add_column("products", sa.Column("unit", sa.String(32), nullable=False, server_default="pcs"))
    op.add_column("products", sa.Column("sale_price", money, nullable=False, server_default="0.00"))
    op.create_table("sales", sa.Column("id", sa.String(64), primary_key=True), sa.Column("invoice_number", sa.String(64), unique=True, nullable=False), sa.Column("customer_id", sa.String(64), sa.ForeignKey("customers.id")), sa.Column("customer_name", sa.String(200), nullable=False), sa.Column("sale_type", sa.String(32), nullable=False), sa.Column("sale_date", sa.DateTime(), nullable=False), sa.Column("subtotal", money, nullable=False), sa.Column("discount", money, nullable=False), sa.Column("total", money, nullable=False), sa.Column("paid", money, nullable=False), sa.Column("remaining", money, nullable=False), sa.Column("payment_status", sa.String(32), nullable=False), sa.Column("notes", sa.String(1000)), sa.Column("status", sa.String(32), nullable=False), sa.Column("created_at", sa.DateTime(), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=False))
    op.create_table("sale_items", sa.Column("id", sa.String(64), primary_key=True), sa.Column("sale_id", sa.String(64), sa.ForeignKey("sales.id"), nullable=False), sa.Column("product_id", sa.String(64), sa.ForeignKey("products.id"), nullable=False), sa.Column("product_name", sa.String(200), nullable=False), sa.Column("quantity", quantity, nullable=False), sa.Column("rate", money, nullable=False), sa.Column("discount", money, nullable=False), sa.Column("amount", money, nullable=False))
    op.create_table("purchases", sa.Column("id", sa.String(64), primary_key=True), sa.Column("purchase_number", sa.String(64), unique=True, nullable=False), sa.Column("supplier_id", sa.String(64), sa.ForeignKey("suppliers.id"), nullable=False), sa.Column("purchase_date", sa.DateTime(), nullable=False), sa.Column("subtotal", money, nullable=False), sa.Column("discount", money, nullable=False), sa.Column("total", money, nullable=False), sa.Column("paid", money, nullable=False), sa.Column("remaining", money, nullable=False), sa.Column("payment_status", sa.String(32), nullable=False), sa.Column("notes", sa.String(1000)), sa.Column("status", sa.String(32), nullable=False), sa.Column("created_at", sa.DateTime(), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=False))
    op.create_table("purchase_items", sa.Column("id", sa.String(64), primary_key=True), sa.Column("purchase_id", sa.String(64), sa.ForeignKey("purchases.id"), nullable=False), sa.Column("product_id", sa.String(64), sa.ForeignKey("products.id"), nullable=False), sa.Column("product_name", sa.String(200), nullable=False), sa.Column("quantity", quantity, nullable=False), sa.Column("rate", money, nullable=False), sa.Column("amount", money, nullable=False))
    op.create_table("customer_ledger_entries", sa.Column("id", sa.String(64), primary_key=True), sa.Column("customer_id", sa.String(64), sa.ForeignKey("customers.id"), nullable=False), sa.Column("entry_date", sa.DateTime(), nullable=False), sa.Column("description", sa.String(255), nullable=False), sa.Column("sale_amount", money, nullable=False), sa.Column("payment_amount", money, nullable=False), sa.Column("return_amount", money, nullable=False), sa.Column("balance_after", money, nullable=False), sa.Column("reference_type", sa.String(64)), sa.Column("reference_id", sa.String(64)), sa.Column("created_at", sa.DateTime(), nullable=False))
    op.create_table("supplier_ledger_entries", sa.Column("id", sa.String(64), primary_key=True), sa.Column("supplier_id", sa.String(64), sa.ForeignKey("suppliers.id"), nullable=False), sa.Column("entry_date", sa.DateTime(), nullable=False), sa.Column("description", sa.String(255), nullable=False), sa.Column("purchase_amount", money, nullable=False), sa.Column("payment_amount", money, nullable=False), sa.Column("return_amount", money, nullable=False), sa.Column("balance_after", money, nullable=False), sa.Column("reference_type", sa.String(64)), sa.Column("reference_id", sa.String(64)), sa.Column("created_at", sa.DateTime(), nullable=False))
    op.create_table("bank_accounts", sa.Column("id", sa.String(64), primary_key=True), sa.Column("name", sa.String(200), unique=True, nullable=False), sa.Column("opening_balance", money, nullable=False), sa.Column("created_at", sa.DateTime(), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=False))
    op.create_table("bank_transactions", sa.Column("id", sa.String(64), primary_key=True), sa.Column("bank_account_id", sa.String(64), sa.ForeignKey("bank_accounts.id"), nullable=False), sa.Column("transaction_date", sa.DateTime(), nullable=False), sa.Column("type", sa.String(32), nullable=False), sa.Column("amount", money, nullable=False), sa.Column("reference", sa.String(200)), sa.Column("note", sa.String(500)), sa.Column("reference_type", sa.String(64)), sa.Column("reference_id", sa.String(64)), sa.Column("created_at", sa.DateTime(), nullable=False))
    op.create_table("customer_payments", sa.Column("id", sa.String(64), primary_key=True), sa.Column("customer_id", sa.String(64), sa.ForeignKey("customers.id"), nullable=False), sa.Column("payment_date", sa.DateTime(), nullable=False), sa.Column("amount", money, nullable=False), sa.Column("payment_method", sa.String(32), nullable=False), sa.Column("reference", sa.String(200)), sa.Column("notes", sa.String(1000)), sa.Column("sale_id", sa.String(64), sa.ForeignKey("sales.id")), sa.Column("bank_account_id", sa.String(64), sa.ForeignKey("bank_accounts.id")), sa.Column("status", sa.String(32), nullable=False), sa.Column("created_at", sa.DateTime(), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=False))
    op.create_table("supplier_payments", sa.Column("id", sa.String(64), primary_key=True), sa.Column("supplier_id", sa.String(64), sa.ForeignKey("suppliers.id"), nullable=False), sa.Column("payment_date", sa.DateTime(), nullable=False), sa.Column("amount", money, nullable=False), sa.Column("payment_method", sa.String(32), nullable=False), sa.Column("reference", sa.String(200)), sa.Column("notes", sa.String(1000)), sa.Column("purchase_id", sa.String(64), sa.ForeignKey("purchases.id")), sa.Column("bank_account_id", sa.String(64), sa.ForeignKey("bank_accounts.id")), sa.Column("status", sa.String(32), nullable=False), sa.Column("created_at", sa.DateTime(), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=False))
    op.create_table("expenses", sa.Column("id", sa.String(64), primary_key=True), sa.Column("category", sa.String(64), nullable=False), sa.Column("subtype", sa.String(64), nullable=False), sa.Column("custom_subtype", sa.String(200)), sa.Column("amount", money, nullable=False), sa.Column("expense_date", sa.DateTime(), nullable=False), sa.Column("description", sa.String(1000)), sa.Column("payment_method", sa.String(32), nullable=False), sa.Column("bank_account_id", sa.String(64), sa.ForeignKey("bank_accounts.id")), sa.Column("created_at", sa.DateTime(), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=False))
    op.create_table("cash_transactions", sa.Column("id", sa.String(64), primary_key=True), sa.Column("transaction_date", sa.DateTime(), nullable=False), sa.Column("type", sa.String(32), nullable=False), sa.Column("category", sa.String(64)), sa.Column("description", sa.String(500), nullable=False), sa.Column("amount", money, nullable=False), sa.Column("reference_type", sa.String(64)), sa.Column("reference_id", sa.String(64)), sa.Column("created_at", sa.DateTime(), nullable=False))
    op.create_table("cash_day_closes", sa.Column("date", sa.DateTime(), primary_key=True), sa.Column("opening_balance", money, nullable=False), sa.Column("cash_in", money, nullable=False), sa.Column("cash_out", money, nullable=False), sa.Column("closing_balance", money, nullable=False), sa.Column("actual_cash", money), sa.Column("difference", money), sa.Column("finalized_at", sa.DateTime(), nullable=False))
    op.create_table("cash_credit_people", sa.Column("id", sa.String(64), primary_key=True), sa.Column("name", sa.String(200), nullable=False), sa.Column("phone", sa.String(50)), sa.Column("address", sa.String(500)), sa.Column("total_cash_given", money, nullable=False), sa.Column("total_received", money, nullable=False), sa.Column("created_at", sa.DateTime(), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=False))
    op.create_table("cash_credit_transactions", sa.Column("id", sa.String(64), primary_key=True), sa.Column("person_id", sa.String(64), sa.ForeignKey("cash_credit_people.id"), nullable=False), sa.Column("transaction_date", sa.DateTime(), nullable=False), sa.Column("type", sa.String(32), nullable=False), sa.Column("amount", money, nullable=False), sa.Column("notes", sa.String(500)), sa.Column("created_at", sa.DateTime(), nullable=False))
    op.create_table("sale_returns", sa.Column("id", sa.String(64), primary_key=True), sa.Column("sale_id", sa.String(64), sa.ForeignKey("sales.id"), nullable=False), sa.Column("customer_id", sa.String(64), sa.ForeignKey("customers.id")), sa.Column("return_date", sa.DateTime(), nullable=False), sa.Column("amount", money, nullable=False), sa.Column("reference", sa.String(200)), sa.Column("notes", sa.String(500)), sa.Column("status", sa.String(32), nullable=False), sa.Column("created_at", sa.DateTime(), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=False))
    op.create_table("sale_return_items", sa.Column("id", sa.String(64), primary_key=True), sa.Column("return_id", sa.String(64), sa.ForeignKey("sale_returns.id"), nullable=False), sa.Column("sale_item_id", sa.String(64), nullable=False), sa.Column("product_id", sa.String(64), sa.ForeignKey("products.id"), nullable=False), sa.Column("product_name", sa.String(200), nullable=False), sa.Column("quantity", quantity, nullable=False), sa.Column("rate", money, nullable=False), sa.Column("amount", money, nullable=False))
    op.create_table("purchase_returns", sa.Column("id", sa.String(64), primary_key=True), sa.Column("purchase_id", sa.String(64), sa.ForeignKey("purchases.id"), nullable=False), sa.Column("supplier_id", sa.String(64), sa.ForeignKey("suppliers.id"), nullable=False), sa.Column("return_date", sa.DateTime(), nullable=False), sa.Column("amount", money, nullable=False), sa.Column("reference", sa.String(200)), sa.Column("notes", sa.String(500)), sa.Column("status", sa.String(32), nullable=False), sa.Column("created_at", sa.DateTime(), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=False))
    op.create_table("purchase_return_items", sa.Column("id", sa.String(64), primary_key=True), sa.Column("return_id", sa.String(64), sa.ForeignKey("purchase_returns.id"), nullable=False), sa.Column("purchase_item_id", sa.String(64), nullable=False), sa.Column("product_id", sa.String(64), sa.ForeignKey("products.id"), nullable=False), sa.Column("product_name", sa.String(200), nullable=False), sa.Column("quantity", quantity, nullable=False), sa.Column("rate", money, nullable=False), sa.Column("amount", money, nullable=False))
    op.create_table("settings", sa.Column("key", sa.String(128), primary_key=True), sa.Column("value", sa.String(1024), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=False))
    op.create_table("audit_logs", sa.Column("id", sa.String(64), primary_key=True), sa.Column("user_id", sa.String(64)), sa.Column("action", sa.String(64), nullable=False), sa.Column("entity_type", sa.String(64), nullable=False), sa.Column("entity_id", sa.String(64)), sa.Column("metadata_json", sa.JSON), sa.Column("created_at", sa.DateTime(), nullable=False))
    op.create_table("idempotency_records", sa.Column("id", sa.String(64), primary_key=True), sa.Column("user_id", sa.String(64)), sa.Column("key", sa.String(255), nullable=False), sa.Column("request_hash", sa.String(64), nullable=False), sa.Column("response_json", sa.Text(), nullable=False), sa.Column("created_at", sa.DateTime(), nullable=False), sa.UniqueConstraint("user_id", "key", name="uq_idempotency_user_key"))


def downgrade() -> None:
    for table in ("idempotency_records", "audit_logs", "settings", "purchase_return_items", "purchase_returns", "sale_return_items", "sale_returns", "cash_credit_transactions", "cash_credit_people", "cash_day_closes", "cash_transactions", "expenses", "supplier_payments", "customer_payments", "bank_transactions", "bank_accounts", "supplier_ledger_entries", "customer_ledger_entries", "purchase_items", "purchases", "sale_items", "sales"):
        op.drop_table(table)
    for column in ("sale_price", "unit", "packaging", "brand"):
        op.drop_column("products", column)