"""rev_a - accounting core schema Revision ID: 0002_rev_a Revises: 0001_baseline Create Date: 2026-09-22 """ from alembic import op, context import sqlalchemy as sa revision = "0002_rev_a" down_revision = "0001_baseline" branch_labels = None depends_on = None def upgrade() -> None: op.add_column("accounts", sa.Column("code", sa.String(length=20), nullable=True)) op.add_column("accounts", sa.Column("account_type", sa.String(length=20), nullable=True)) op.add_column("accounts", sa.Column("normal_balance", sa.String(length=1), nullable=True)) op.add_column("accounts", sa.Column("category", sa.String(length=50), nullable=True)) op.add_column("accounts", sa.Column("parent_id", sa.String(length=36), nullable=True)) op.add_column("accounts", sa.Column("branch_id", sa.String(length=36), nullable=True)) op.add_column("accounts", sa.Column("is_system", sa.Boolean(), nullable=True)) op.create_index("ix_accounts_code", "accounts", ["code"]) op.create_index("ix_accounts_parent_id", "accounts", ["parent_id"]) op.create_index("ix_accounts_branch_id", "accounts", ["branch_id"]) op.create_table( "accounting_periods", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("period_code", sa.String(length=7), nullable=False), sa.Column("start_date", sa.Date(), nullable=False), sa.Column("end_date", sa.Date(), nullable=False), sa.Column("status", sa.String(length=10), nullable=False), sa.Column("closed_by", sa.String(length=36), nullable=True), sa.Column("closed_at", sa.DateTime(), nullable=True), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_accounting_periods_period_code", "accounting_periods", ["period_code"]) if not context.is_offline_mode(): bind = op.get_bind() count = bind.execute(sa.text("SELECT COUNT(*) FROM journal_entries")).scalar() if count and int(count) > 0: raise RuntimeError("journal_entries is not empty; refusing destructive redesign") op.drop_table("journal_entries") op.create_table( "journal_entries", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("entry_number", sa.String(length=30), nullable=False), sa.Column("entry_date", sa.Date(), nullable=False), sa.Column("period_id", sa.String(length=36), nullable=False), sa.Column("branch_id", sa.String(length=36), nullable=True), sa.Column("status", sa.String(length=20), nullable=False), sa.Column("source", sa.String(length=30), nullable=False), sa.Column("reference_type", sa.String(length=30), nullable=True), sa.Column("reference_id", sa.String(length=36), nullable=True), sa.Column("description", sa.Text(), nullable=False), sa.Column("is_reversal", sa.Boolean(), nullable=False), sa.Column("reversal_of_id", sa.String(length=36), nullable=True), sa.Column("created_by", sa.String(length=36), nullable=False), sa.Column("posted_by", sa.String(length=36), nullable=True), sa.Column("posted_at", sa.DateTime(), nullable=True), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.ForeignKeyConstraint(["period_id"], ["accounting_periods.id"]), sa.ForeignKeyConstraint(["created_by"], ["users.id"]), sa.ForeignKeyConstraint(["posted_by"], ["users.id"]), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("entry_number"), sa.UniqueConstraint("source", "reference_type", "reference_id", name="uq_journal_idempotency"), ) op.create_index("ix_journal_entries_entry_date", "journal_entries", ["entry_date"]) op.create_index("ix_journal_entries_period_id", "journal_entries", ["period_id"]) op.create_index("ix_journal_entries_branch_id", "journal_entries", ["branch_id"]) op.create_table( "journal_lines", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("entry_id", sa.String(length=36), nullable=False), sa.Column("account_id", sa.String(length=36), nullable=False), sa.Column("debit", sa.Numeric(18, 2), nullable=False), sa.Column("credit", sa.Numeric(18, 2), nullable=False), sa.Column("branch_id", sa.String(length=36), nullable=True), sa.Column("production_batch_id", sa.String(length=36), nullable=True), sa.Column("passport_id", sa.String(length=36), nullable=True), sa.Column("cost_center", sa.String(length=50), nullable=True), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.ForeignKeyConstraint(["entry_id"], ["journal_entries.id"]), sa.ForeignKeyConstraint(["account_id"], ["accounts.id"]), sa.ForeignKeyConstraint(["production_batch_id"], ["production_batches.id"]), sa.ForeignKeyConstraint(["passport_id"], ["plant_passports.id"]), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_journal_lines_entry_id", "journal_lines", ["entry_id"]) op.create_index("ix_journal_lines_account_id", "journal_lines", ["account_id"]) op.create_index("ix_journal_lines_branch_id", "journal_lines", ["branch_id"]) op.create_table( "account_balances", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("account_id", sa.String(length=36), nullable=False), sa.Column("period_id", sa.String(length=36), nullable=False), sa.Column("branch_id", sa.String(length=36), nullable=False), sa.Column("opening_balance", sa.Numeric(18, 2), nullable=False), sa.Column("total_debit", sa.Numeric(18, 2), nullable=False), sa.Column("total_credit", sa.Numeric(18, 2), nullable=False), sa.Column("closing_balance", sa.Numeric(18, 2), nullable=False), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.Column("updated_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.ForeignKeyConstraint(["account_id"], ["accounts.id"]), sa.ForeignKeyConstraint(["period_id"], ["accounting_periods.id"]), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("account_id", "period_id", "branch_id", name="uq_account_balance"), ) op.create_index("ix_account_balances_account_id", "account_balances", ["account_id"]) op.create_index("ix_account_balances_period_id", "account_balances", ["period_id"]) op.create_index("ix_account_balances_branch_id", "account_balances", ["branch_id"]) op.create_table( "inventory_lots", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("item_id", sa.String(length=36), nullable=False), sa.Column("branch_id", sa.String(length=36), nullable=True), sa.Column("batch_id", sa.String(length=36), nullable=True), sa.Column("lot_number", sa.String(length=40), nullable=False), sa.Column("received_date", sa.Date(), nullable=False), sa.Column("received_qty", sa.Numeric(12, 2), nullable=False), sa.Column("remaining_qty", sa.Numeric(12, 2), nullable=False), sa.Column("unit_cost", sa.Numeric(18, 2), nullable=False), sa.Column("is_active", sa.Boolean(), nullable=False), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.Column("updated_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.ForeignKeyConstraint(["item_id"], ["inventory.id"]), sa.ForeignKeyConstraint(["batch_id"], ["production_batches.id"]), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_inventory_lots_item_id", "inventory_lots", ["item_id"]) op.create_index("ix_inventory_lots_batch_id", "inventory_lots", ["batch_id"]) op.create_index("ix_inventory_lots_branch_id", "inventory_lots", ["branch_id"]) op.create_index("ix_inventory_lots_received_date", "inventory_lots", ["received_date"]) op.create_table( "payment_allocations", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("payment_id", sa.String(length=36), nullable=False), sa.Column("document_type", sa.String(length=30), nullable=False), sa.Column("document_id", sa.String(length=36), nullable=False), sa.Column("amount", sa.Numeric(18, 2), nullable=False), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.ForeignKeyConstraint(["payment_id"], ["payments.id"]), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_payment_allocations_payment_id", "payment_allocations", ["payment_id"]) op.create_table( "cashbook_movements", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("movement_number", sa.String(length=30), nullable=False), sa.Column("type", sa.String(length=20), nullable=False), sa.Column("account_id", sa.String(length=36), nullable=False), sa.Column("from_account_id", sa.String(length=36), nullable=True), sa.Column("to_account_id", sa.String(length=36), nullable=True), sa.Column("amount", sa.Numeric(18, 2), nullable=False), sa.Column("movement_date", sa.Date(), nullable=False), sa.Column("branch_id", sa.String(length=36), nullable=True), sa.Column("journal_entry_id", sa.String(length=36), nullable=True), sa.Column("reference_type", sa.String(length=30), nullable=True), sa.Column("reference_id", sa.String(length=36), nullable=True), sa.Column("notes", sa.Text(), nullable=True), sa.Column("created_by", sa.String(length=36), nullable=True), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.ForeignKeyConstraint(["account_id"], ["accounts.id"]), sa.ForeignKeyConstraint(["journal_entry_id"], ["journal_entries.id"]), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("movement_number"), ) op.create_index("ix_cashbook_movements_account_id", "cashbook_movements", ["account_id"]) op.create_table( "bank_reconciliations", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("account_id", sa.String(length=36), nullable=False), sa.Column("period_id", sa.String(length=36), nullable=True), sa.Column("statement_balance", sa.Numeric(18, 2), nullable=False), sa.Column("book_balance", sa.Numeric(18, 2), nullable=False), sa.Column("difference", sa.Numeric(18, 2), nullable=False), sa.Column("status", sa.String(length=20), nullable=False), sa.Column("lines", sa.Text(), nullable=True), sa.Column("reconciled_at", sa.DateTime(), nullable=True), sa.Column("reconciled_by", sa.String(length=36), nullable=True), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.ForeignKeyConstraint(["account_id"], ["accounts.id"]), sa.ForeignKeyConstraint(["period_id"], ["accounting_periods.id"]), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_bank_reconciliations_account_id", "bank_reconciliations", ["account_id"]) op.create_table( "loans", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("loan_number", sa.String(length=30), nullable=False), sa.Column("lender_name", sa.String(length=100), nullable=False), sa.Column("lender_type", sa.String(length=20), nullable=False), sa.Column("principal", sa.Numeric(18, 2), nullable=False), sa.Column("interest_rate_nominal", sa.Numeric(10, 4), nullable=False), sa.Column("interest_rate_period", sa.String(length=10), nullable=False), sa.Column("start_date", sa.Date(), nullable=False), sa.Column("tenure_months", sa.Integer(), nullable=False), sa.Column("repayment_frequency", sa.String(length=20), nullable=False), sa.Column("installment_amount", sa.Numeric(18, 2), nullable=False), sa.Column("total_interest", sa.Numeric(18, 2), nullable=False), sa.Column("status", sa.String(length=20), nullable=False), sa.Column("branch_id", sa.String(length=36), nullable=True), sa.Column("account_id", sa.String(length=36), nullable=True), sa.Column("liability_account_id", sa.String(length=36), nullable=True), sa.Column("interest_expense_account_id", sa.String(length=36), nullable=True), sa.Column("notes", sa.Text(), nullable=True), sa.Column("created_by", sa.String(length=36), nullable=False), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.ForeignKeyConstraint(["created_by"], ["users.id"]), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("loan_number"), ) op.create_table( "loan_schedules", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("loan_id", sa.String(length=36), nullable=False), sa.Column("installment_no", sa.Integer(), nullable=False), sa.Column("due_date", sa.Date(), nullable=False), sa.Column("principal_due", sa.Numeric(18, 2), nullable=False), sa.Column("interest_due", sa.Numeric(18, 2), nullable=False), sa.Column("total_due", sa.Numeric(18, 2), nullable=False), sa.Column("principal_paid", sa.Numeric(18, 2), nullable=False), sa.Column("interest_paid", sa.Numeric(18, 2), nullable=False), sa.Column("paid_date", sa.Date(), nullable=True), sa.Column("status", sa.String(length=20), nullable=False), sa.Column("payment_id", sa.String(length=36), nullable=True), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.ForeignKeyConstraint(["loan_id"], ["loans.id"]), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_loan_schedules_loan_id", "loan_schedules", ["loan_id"]) op.create_table( "production_cost_entries", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("batch_id", sa.String(length=36), nullable=False), sa.Column("cost_type", sa.String(length=30), nullable=False), sa.Column("amount", sa.Numeric(18, 2), nullable=False), sa.Column("quantity", sa.Numeric(12, 2), nullable=True), sa.Column("unit", sa.String(length=20), nullable=True), sa.Column("reference_type", sa.String(length=30), nullable=True), sa.Column("reference_id", sa.String(length=36), nullable=True), sa.Column("journal_line_id", sa.String(length=36), nullable=True), sa.Column("allocated_to_qty", sa.Numeric(12, 2), nullable=True), sa.Column("cost_center", sa.String(length=50), nullable=True), sa.Column("notes", sa.Text(), nullable=True), sa.Column("created_by", sa.String(length=36), nullable=True), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.ForeignKeyConstraint(["batch_id"], ["production_batches.id"]), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_production_cost_entries_batch_id", "production_cost_entries", ["batch_id"]) op.create_table( "cost_centers", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("code", sa.String(length=30), nullable=False), sa.Column("name", sa.String(length=100), nullable=False), sa.Column("branch_id", sa.String(length=36), nullable=True), sa.Column("description", sa.Text(), nullable=True), sa.Column("is_active", sa.Boolean(), nullable=False), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("code"), ) def downgrade() -> None: op.drop_table("cost_centers") op.drop_table("production_cost_entries") op.drop_table("loan_schedules") op.drop_table("loans") op.drop_table("bank_reconciliations") op.drop_table("cashbook_movements") op.drop_table("payment_allocations") op.drop_table("inventory_lots") op.drop_table("account_balances") op.drop_table("journal_lines") op.drop_table("journal_entries") op.create_table( "journal_entries", sa.Column("id", sa.String(length=36), nullable=False), sa.Column("entry_number", sa.String(length=30), nullable=False), sa.Column("date", sa.Date(), nullable=False), sa.Column("debit_account", sa.String(length=36), nullable=False), sa.Column("credit_account", sa.String(length=36), nullable=False), sa.Column("amount", sa.Float(), nullable=False), sa.Column("description", sa.String(length=200), nullable=False), sa.Column("reference", sa.String(length=100), nullable=True), sa.Column("created_by", sa.String(length=36), nullable=False), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("entry_number"), ) op.drop_table("accounting_periods") op.drop_index("ix_accounts_branch_id", table_name="accounts") op.drop_index("ix_accounts_parent_id", table_name="accounts") op.drop_index("ix_accounts_code", table_name="accounts") op.drop_column("accounts", "is_system") op.drop_column("accounts", "branch_id") op.drop_column("accounts", "parent_id") op.drop_column("accounts", "category") op.drop_column("accounts", "normal_balance") op.drop_column("accounts", "account_type") op.drop_column("accounts", "code")