"""Add inventory masters and purchase flow tables.

Revision ID: 002_inventory_purchase
Revises: 001_initial_mysql
Create Date: 2026-08-02

"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "002_inventory_purchase"
down_revision: Union[str, None] = "001_initial_mysql"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "item_categories",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("company_id", sa.String(36), sa.ForeignKey("companies.id", ondelete="CASCADE"), nullable=False),
        sa.Column("code", sa.String(30), nullable=False),
        sa.Column("name", sa.String(200), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.UniqueConstraint("company_id", "code", name="uq_item_categories_company_code"),
    )
    op.create_index("ix_item_categories_company_id", "item_categories", ["company_id"])

    op.create_table(
        "item_groups",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("company_id", sa.String(36), sa.ForeignKey("companies.id", ondelete="CASCADE"), nullable=False),
        sa.Column("code", sa.String(30), nullable=False),
        sa.Column("name", sa.String(200), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.UniqueConstraint("company_id", "code", name="uq_item_groups_company_code"),
    )
    op.create_index("ix_item_groups_company_id", "item_groups", ["company_id"])

    op.create_table(
        "unit_types",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("company_id", sa.String(36), sa.ForeignKey("companies.id", ondelete="CASCADE"), nullable=False),
        sa.Column("code", sa.String(30), nullable=False),
        sa.Column("name", sa.String(100), nullable=False),
        sa.Column("unit_kind", sa.String(20), nullable=False, server_default="individual"),
        sa.Column("pack_size", sa.Numeric(18, 4), nullable=False, server_default="1.0000"),
        sa.Column("base_unit_id", sa.String(36), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.UniqueConstraint("company_id", "code", name="uq_unit_types_company_code"),
    )
    op.create_index("ix_unit_types_company_id", "unit_types", ["company_id"])

    op.create_table(
        "items",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("company_id", sa.String(36), sa.ForeignKey("companies.id", ondelete="CASCADE"), nullable=False),
        sa.Column("sku", sa.String(50), nullable=False),
        sa.Column("name", sa.String(200), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("category_id", sa.String(36), sa.ForeignKey("item_categories.id", ondelete="SET NULL"), nullable=True),
        sa.Column("group_id", sa.String(36), sa.ForeignKey("item_groups.id", ondelete="SET NULL"), nullable=True),
        sa.Column("unit_type_id", sa.String(36), sa.ForeignKey("unit_types.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("pricing_model", sa.String(30), nullable=False, server_default="average_cost"),
        sa.Column("purchase_price", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("sale_price", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("reorder_level", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("track_inventory", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("inventory_account_id", sa.String(36), nullable=True),
        sa.Column("expense_account_id", sa.String(36), nullable=True),
        sa.Column("cogs_account_id", sa.String(36), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.UniqueConstraint("company_id", "sku", name="uq_items_company_sku"),
    )
    op.create_index("ix_items_company_id", "items", ["company_id"])
    op.create_index("ix_items_category_id", "items", ["category_id"])
    op.create_index("ix_items_group_id", "items", ["group_id"])

    op.create_table(
        "inventory_balances",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("company_id", sa.String(36), sa.ForeignKey("companies.id", ondelete="CASCADE"), nullable=False),
        sa.Column("item_id", sa.String(36), sa.ForeignKey("items.id", ondelete="CASCADE"), nullable=False),
        sa.Column("quantity_on_hand", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("quantity_reserved", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("average_cost", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("last_cost", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.UniqueConstraint("company_id", "item_id", name="uq_inventory_balances_company_item"),
    )
    op.create_index("ix_inventory_balances_company_id", "inventory_balances", ["company_id"])

    op.create_table(
        "inventory_transactions",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("company_id", sa.String(36), sa.ForeignKey("companies.id", ondelete="CASCADE"), nullable=False),
        sa.Column("item_id", sa.String(36), sa.ForeignKey("items.id", ondelete="CASCADE"), nullable=False),
        sa.Column("txn_type", sa.String(40), nullable=False),
        sa.Column("txn_date", sa.Date(), nullable=False),
        sa.Column("quantity_in", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("quantity_out", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("unit_cost", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("total_cost", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("balance_after", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("reference_type", sa.String(40), nullable=True),
        sa.Column("reference_id", sa.String(36), nullable=True),
        sa.Column("reference_number", sa.String(50), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
    )
    op.create_index("ix_inv_txn_company_item_date", "inventory_transactions", ["company_id", "item_id", "txn_date"])
    op.create_index("ix_inv_txn_reference", "inventory_transactions", ["reference_type", "reference_id"])

    op.create_table(
        "vendors",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("company_id", sa.String(36), sa.ForeignKey("companies.id", ondelete="CASCADE"), nullable=False),
        sa.Column("code", sa.String(30), nullable=False),
        sa.Column("name", sa.String(200), nullable=False),
        sa.Column("email", sa.String(255), nullable=True),
        sa.Column("phone", sa.String(50), nullable=True),
        sa.Column("address", sa.String(500), nullable=True),
        sa.Column("account_id", sa.String(36), nullable=True),
        sa.Column("payment_terms_days", sa.Integer(), nullable=False, server_default="30"),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.UniqueConstraint("company_id", "code", name="uq_vendors_company_code"),
    )
    op.create_index("ix_vendors_company_id", "vendors", ["company_id"])

    op.create_table(
        "purchase_orders",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("company_id", sa.String(36), sa.ForeignKey("companies.id", ondelete="CASCADE"), nullable=False),
        sa.Column("po_number", sa.String(50), nullable=False),
        sa.Column("vendor_id", sa.String(36), sa.ForeignKey("vendors.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("order_date", sa.Date(), nullable=False),
        sa.Column("expected_date", sa.Date(), nullable=True),
        sa.Column("status", sa.String(30), nullable=False, server_default="draft"),
        sa.Column("currency", sa.String(10), nullable=False, server_default="PKR"),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("subtotal", sa.Numeric(18, 2), nullable=False, server_default="0.00"),
        sa.Column("tax_amount", sa.Numeric(18, 2), nullable=False, server_default="0.00"),
        sa.Column("total_amount", sa.Numeric(18, 2), nullable=False, server_default="0.00"),
        sa.Column("created_by", sa.String(36), nullable=True),
        sa.Column("approved_at", sa.DateTime(), nullable=True),
        sa.Column("cancelled_at", sa.DateTime(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.UniqueConstraint("company_id", "po_number", name="uq_po_company_number"),
    )
    op.create_index("ix_po_company_id", "purchase_orders", ["company_id"])
    op.create_index("ix_po_vendor_id", "purchase_orders", ["vendor_id"])
    op.create_index("ix_po_status", "purchase_orders", ["status"])

    op.create_table(
        "purchase_order_lines",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("purchase_order_id", sa.String(36), sa.ForeignKey("purchase_orders.id", ondelete="CASCADE"), nullable=False),
        sa.Column("line_number", sa.Integer(), nullable=False),
        sa.Column("item_id", sa.String(36), sa.ForeignKey("items.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("quantity", sa.Numeric(18, 4), nullable=False),
        sa.Column("received_quantity", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("billed_quantity", sa.Numeric(18, 4), nullable=False, server_default="0.0000"),
        sa.Column("unit_price", sa.Numeric(18, 4), nullable=False),
        sa.Column("tax_rate", sa.Numeric(8, 4), nullable=False, server_default="0.0000"),
        sa.Column("line_total", sa.Numeric(18, 2), nullable=False),
    )
    op.create_index("ix_po_lines_po_id", "purchase_order_lines", ["purchase_order_id"])

    op.create_table(
        "goods_receipts",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("company_id", sa.String(36), sa.ForeignKey("companies.id", ondelete="CASCADE"), nullable=False),
        sa.Column("grn_number", sa.String(50), nullable=False),
        sa.Column("purchase_order_id", sa.String(36), sa.ForeignKey("purchase_orders.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("vendor_id", sa.String(36), sa.ForeignKey("vendors.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("receipt_date", sa.Date(), nullable=False),
        sa.Column("status", sa.String(30), nullable=False, server_default="draft"),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("confirmed_at", sa.DateTime(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.UniqueConstraint("company_id", "grn_number", name="uq_grn_company_number"),
    )
    op.create_index("ix_grn_company_id", "goods_receipts", ["company_id"])
    op.create_index("ix_grn_po_id", "goods_receipts", ["purchase_order_id"])

    op.create_table(
        "goods_receipt_lines",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("goods_receipt_id", sa.String(36), sa.ForeignKey("goods_receipts.id", ondelete="CASCADE"), nullable=False),
        sa.Column("purchase_order_line_id", sa.String(36), sa.ForeignKey("purchase_order_lines.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("line_number", sa.Integer(), nullable=False),
        sa.Column("item_id", sa.String(36), sa.ForeignKey("items.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("quantity_ordered", sa.Numeric(18, 4), nullable=False),
        sa.Column("quantity_received", sa.Numeric(18, 4), nullable=False),
        sa.Column("unit_cost", sa.Numeric(18, 4), nullable=False),
        sa.Column("notes", sa.Text(), nullable=True),
    )
    op.create_index("ix_grn_lines_grn_id", "goods_receipt_lines", ["goods_receipt_id"])

    op.create_table(
        "vendor_bills",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("company_id", sa.String(36), sa.ForeignKey("companies.id", ondelete="CASCADE"), nullable=False),
        sa.Column("bill_number", sa.String(50), nullable=False),
        sa.Column("vendor_invoice_number", sa.String(100), nullable=True),
        sa.Column("vendor_id", sa.String(36), sa.ForeignKey("vendors.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("purchase_order_id", sa.String(36), sa.ForeignKey("purchase_orders.id", ondelete="SET NULL"), nullable=True),
        sa.Column("goods_receipt_id", sa.String(36), sa.ForeignKey("goods_receipts.id", ondelete="SET NULL"), nullable=True),
        sa.Column("bill_date", sa.Date(), nullable=False),
        sa.Column("due_date", sa.Date(), nullable=True),
        sa.Column("status", sa.String(30), nullable=False, server_default="draft"),
        sa.Column("ap_account_id", sa.String(36), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("subtotal", sa.Numeric(18, 2), nullable=False, server_default="0.00"),
        sa.Column("tax_amount", sa.Numeric(18, 2), nullable=False, server_default="0.00"),
        sa.Column("total_amount", sa.Numeric(18, 2), nullable=False, server_default="0.00"),
        sa.Column("amount_paid", sa.Numeric(18, 2), nullable=False, server_default="0.00"),
        sa.Column("voucher_id", sa.String(36), nullable=True),
        sa.Column("posted_at", sa.DateTime(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.UniqueConstraint("company_id", "bill_number", name="uq_vendor_bills_company_number"),
    )
    op.create_index("ix_vendor_bills_company_id", "vendor_bills", ["company_id"])
    op.create_index("ix_vendor_bills_vendor_id", "vendor_bills", ["vendor_id"])
    op.create_index("ix_vendor_bills_status", "vendor_bills", ["status"])

    op.create_table(
        "vendor_bill_lines",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("vendor_bill_id", sa.String(36), sa.ForeignKey("vendor_bills.id", ondelete="CASCADE"), nullable=False),
        sa.Column("line_number", sa.Integer(), nullable=False),
        sa.Column("item_id", sa.String(36), sa.ForeignKey("items.id", ondelete="SET NULL"), nullable=True),
        sa.Column("account_id", sa.String(36), nullable=True),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("quantity", sa.Numeric(18, 4), nullable=False, server_default="1.0000"),
        sa.Column("unit_price", sa.Numeric(18, 4), nullable=False),
        sa.Column("tax_rate", sa.Numeric(8, 4), nullable=False, server_default="0.0000"),
        sa.Column("line_total", sa.Numeric(18, 2), nullable=False),
        sa.Column("purchase_order_line_id", sa.String(36), nullable=True),
        sa.Column("goods_receipt_line_id", sa.String(36), nullable=True),
    )
    op.create_index("ix_vendor_bill_lines_bill_id", "vendor_bill_lines", ["vendor_bill_id"])

    op.create_table(
        "purchase_payments",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("company_id", sa.String(36), sa.ForeignKey("companies.id", ondelete="CASCADE"), nullable=False),
        sa.Column("payment_number", sa.String(50), nullable=False),
        sa.Column("vendor_id", sa.String(36), sa.ForeignKey("vendors.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("payment_date", sa.Date(), nullable=False),
        sa.Column("status", sa.String(30), nullable=False, server_default="draft"),
        sa.Column("payment_method", sa.String(30), nullable=False, server_default="bank"),
        sa.Column("bank_account_id", sa.String(36), nullable=True),
        sa.Column("ap_account_id", sa.String(36), nullable=True),
        sa.Column("reference", sa.String(200), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("total_amount", sa.Numeric(18, 2), nullable=False, server_default="0.00"),
        sa.Column("voucher_id", sa.String(36), nullable=True),
        sa.Column("posted_at", sa.DateTime(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.UniqueConstraint("company_id", "payment_number", name="uq_purchase_payments_company_number"),
    )
    op.create_index("ix_purchase_payments_company_id", "purchase_payments", ["company_id"])
    op.create_index("ix_purchase_payments_vendor_id", "purchase_payments", ["vendor_id"])

    op.create_table(
        "purchase_payment_allocations",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("purchase_payment_id", sa.String(36), sa.ForeignKey("purchase_payments.id", ondelete="CASCADE"), nullable=False),
        sa.Column("vendor_bill_id", sa.String(36), sa.ForeignKey("vendor_bills.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("amount", sa.Numeric(18, 2), nullable=False),
    )
    op.create_index("ix_payment_alloc_payment_id", "purchase_payment_allocations", ["purchase_payment_id"])


def downgrade() -> None:
    op.drop_table("purchase_payment_allocations")
    op.drop_table("purchase_payments")
    op.drop_table("vendor_bill_lines")
    op.drop_table("vendor_bills")
    op.drop_table("goods_receipt_lines")
    op.drop_table("goods_receipts")
    op.drop_table("purchase_order_lines")
    op.drop_table("purchase_orders")
    op.drop_table("vendors")
    op.drop_table("inventory_transactions")
    op.drop_table("inventory_balances")
    op.drop_table("items")
    op.drop_table("unit_types")
    op.drop_table("item_groups")
    op.drop_table("item_categories")