"""Add item transactions (Add Item Transaction form). Revision ID: 019_item_transactions Revises: 018_sales_order_workflow Create Date: 2026-08-07 """ from typing import Sequence, Union import sqlalchemy as sa from alembic import op revision: str = "019_item_transactions" down_revision: Union[str, None] = "018_sales_order_workflow" branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None def upgrade() -> None: op.create_table( "item_transactions", sa.Column("id", sa.String(length=36), primary_key=True), sa.Column("company_id", sa.String(length=36), nullable=False), sa.Column("txn_number", sa.String(length=50), nullable=False), sa.Column("txn_type", sa.String(length=40), nullable=False), sa.Column("txn_date", sa.DateTime(), nullable=False), sa.Column("reference_type", sa.String(length=40), nullable=True), sa.Column("reference_number", sa.String(length=100), nullable=True), sa.Column("direction", sa.String(length=10), nullable=False), sa.Column("warehouse_id", sa.String(length=36), nullable=False), sa.Column("vendor_id", sa.String(length=36), nullable=True), sa.Column("po_date", sa.Date(), nullable=True), sa.Column("expected_date", sa.Date(), nullable=True), sa.Column("grn_number", sa.String(length=50), nullable=True), sa.Column("remarks", sa.Text(), nullable=True), sa.Column("internal_note", sa.Text(), nullable=True), sa.Column("tags", sa.Text(), nullable=True), sa.Column("attachments", sa.Text(), nullable=True), sa.Column("status", sa.String(length=20), nullable=False, server_default="draft"), sa.Column( "total_quantity", sa.Numeric(18, 4), nullable=False, server_default="0.0000", ), sa.Column("subtotal", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column( "discount_amount", 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( "transport_charges", sa.Numeric(18, 2), nullable=False, server_default="0.00", ), sa.Column("rounding", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column("grand_total", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column("created_by", sa.String(length=36), nullable=True), sa.Column("posted_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.ForeignKeyConstraint( ["company_id"], ["companies.id"], ondelete="CASCADE" ), sa.ForeignKeyConstraint( ["warehouse_id"], ["warehouses.id"], ondelete="RESTRICT" ), sa.ForeignKeyConstraint(["vendor_id"], ["vendors.id"], ondelete="SET NULL"), sa.UniqueConstraint("company_id", "txn_number", name="uq_item_txn_company_number"), ) op.create_index("ix_item_txn_company_id", "item_transactions", ["company_id"]) op.create_index("ix_item_txn_status", "item_transactions", ["status"]) op.create_index("ix_item_txn_warehouse_id", "item_transactions", ["warehouse_id"]) op.create_index("ix_item_txn_vendor_id", "item_transactions", ["vendor_id"]) op.create_table( "item_transaction_lines", sa.Column("id", sa.String(length=36), primary_key=True), sa.Column("item_transaction_id", sa.String(length=36), nullable=False), sa.Column("line_number", sa.Integer(), nullable=False), sa.Column("item_id", sa.String(length=36), nullable=False), sa.Column("description", sa.Text(), nullable=True), sa.Column("batch_lot_no", sa.String(length=100), nullable=True), sa.Column("expiry_date", sa.Date(), nullable=True), sa.Column("base_unit_id", sa.String(length=36), nullable=True), sa.Column("quantity", sa.Numeric(18, 4), nullable=False), sa.Column( "unit_cost", sa.Numeric(18, 4), nullable=False, server_default="0.0000", ), sa.Column( "discount_type", sa.String(length=20), nullable=False, server_default="percent", ), sa.Column( "discount_value", sa.Numeric(18, 4), nullable=False, server_default="0.0000", ), sa.Column( "discount_amount", sa.Numeric(18, 2), nullable=False, server_default="0.00", ), sa.Column( "tax_type", sa.String(length=20), nullable=False, server_default="percent", ), sa.Column( "tax_rate", sa.Numeric(18, 4), nullable=False, server_default="0.0000", ), sa.Column( "tax_amount", sa.Numeric(18, 2), nullable=False, server_default="0.00", ), sa.Column("line_total", sa.Numeric(18, 2), nullable=False), sa.ForeignKeyConstraint( ["item_transaction_id"], ["item_transactions.id"], ondelete="CASCADE", ), sa.ForeignKeyConstraint(["item_id"], ["items.id"], ondelete="RESTRICT"), sa.ForeignKeyConstraint( ["base_unit_id"], ["base_units.id"], ondelete="SET NULL" ), ) op.create_index( "ix_item_txn_lines_txn_id", "item_transaction_lines", ["item_transaction_id"] ) op.create_index( "ix_item_txn_lines_item_id", "item_transaction_lines", ["item_id"] ) op.create_index( "ix_item_txn_lines_base_unit_id", "item_transaction_lines", ["base_unit_id"], ) op.alter_column("item_transactions", "status", server_default=None) op.alter_column("item_transactions", "total_quantity", server_default=None) op.alter_column("item_transactions", "subtotal", server_default=None) op.alter_column("item_transactions", "discount_amount", server_default=None) op.alter_column("item_transactions", "tax_amount", server_default=None) op.alter_column("item_transactions", "transport_charges", server_default=None) op.alter_column("item_transactions", "rounding", server_default=None) op.alter_column("item_transactions", "grand_total", server_default=None) op.alter_column("item_transaction_lines", "unit_cost", server_default=None) op.alter_column("item_transaction_lines", "discount_type", server_default=None) op.alter_column("item_transaction_lines", "discount_value", server_default=None) op.alter_column("item_transaction_lines", "discount_amount", server_default=None) op.alter_column("item_transaction_lines", "tax_type", server_default=None) op.alter_column("item_transaction_lines", "tax_rate", server_default=None) op.alter_column("item_transaction_lines", "tax_amount", server_default=None) def downgrade() -> None: op.drop_table("item_transaction_lines") op.drop_table("item_transactions")