"""Initial MySQL schema for accounting API. Revision ID: 001_initial_mysql Revises: Create Date: 2026-08-01 """ from typing import Sequence, Union import sqlalchemy as sa from alembic import op from sqlalchemy.dialects import mysql revision: str = "001_initial_mysql" down_revision: Union[str, None] = None branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None def upgrade() -> None: op.create_table( "user_registrations", sa.Column("id", sa.String(length=36), primary_key=True), sa.Column("username", sa.String(length=100), nullable=False), sa.Column("email", sa.String(length=255), nullable=False), sa.Column("full_name", sa.String(length=200), nullable=False), sa.Column("password_hash", sa.String(length=255), nullable=False), 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("username"), sa.UniqueConstraint("email"), ) op.create_index("ix_user_registrations_username", "user_registrations", ["username"]) op.create_index("ix_user_registrations_email", "user_registrations", ["email"]) op.create_index("ix_user_registrations_is_active", "user_registrations", ["is_active"]) op.create_table( "refresh_tokens", sa.Column("id", sa.String(length=36), primary_key=True), sa.Column("user_id", sa.String(length=36), nullable=False), sa.Column("jti", sa.String(length=64), nullable=False), sa.Column("expires_at", sa.DateTime(), nullable=False), sa.Column("is_revoked", sa.Boolean(), nullable=False, server_default=sa.text("0")), sa.Column("created_at", sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(["user_id"], ["user_registrations.id"], ondelete="CASCADE"), sa.UniqueConstraint("jti"), ) op.create_index("ix_refresh_tokens_user_id", "refresh_tokens", ["user_id"]) op.create_index("ix_refresh_tokens_jti", "refresh_tokens", ["jti"]) op.create_index("ix_refresh_tokens_expires_at", "refresh_tokens", ["expires_at"]) op.create_table( "companies", sa.Column("id", sa.String(length=36), primary_key=True), sa.Column("company_id", sa.String(length=50), nullable=False), sa.Column("name", sa.String(length=200), nullable=False), sa.Column("address", sa.String(length=500), nullable=False), sa.Column("logo", sa.String(length=500), nullable=False), sa.Column("favicon", sa.String(length=500), nullable=False), sa.Column("user_id", sa.String(length=36), nullable=False), sa.Column("created_at", sa.DateTime(), nullable=False), sa.Column("updated_at", sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(["user_id"], ["user_registrations.id"], ondelete="CASCADE"), sa.UniqueConstraint("user_id", "company_id", name="uq_companies_user_slug"), ) op.create_index("ix_companies_user_id", "companies", ["user_id"]) op.create_table( "chart_of_accounts", sa.Column("id", sa.String(length=36), primary_key=True), sa.Column("company_id", sa.String(length=36), nullable=False), sa.Column("code", sa.String(length=20), nullable=False), sa.Column("name", sa.String(length=200), nullable=False), sa.Column("account_type", sa.String(length=20), nullable=False), sa.Column("nature", sa.String(length=10), nullable=False), sa.Column("parent_id", sa.String(length=36), nullable=True), sa.Column("level", sa.Integer(), nullable=False, server_default="1"), sa.Column("is_group", sa.Boolean(), nullable=False, server_default=sa.text("0")), sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("1")), sa.Column("opening_balance", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column("current_balance", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column("description", sa.Text(), 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.UniqueConstraint("company_id", "code", name="uq_coa_company_code"), ) op.create_index("ix_coa_company_id", "chart_of_accounts", ["company_id"]) op.create_index("ix_coa_account_type", "chart_of_accounts", ["account_type"]) op.create_index("ix_coa_parent_id", "chart_of_accounts", ["parent_id"]) op.create_table( "vouchers", sa.Column("id", sa.String(length=36), primary_key=True), sa.Column("company_id", sa.String(length=36), nullable=False), sa.Column("voucher_number", sa.String(length=50), nullable=False), sa.Column("voucher_type", sa.String(length=20), nullable=False), sa.Column("voucher_date", sa.Date(), nullable=False), sa.Column("reference", sa.String(length=200), nullable=True), sa.Column("narration", sa.Text(), nullable=True), sa.Column("status", sa.String(length=20), nullable=False, server_default="draft"), sa.Column("total_debit", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column("total_credit", sa.Numeric(18, 2), nullable=False, server_default="0.00"), 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.UniqueConstraint("company_id", "voucher_number", name="uq_vouchers_company_number"), ) op.create_index("ix_vouchers_company_id", "vouchers", ["company_id"]) op.create_index("ix_vouchers_voucher_date", "vouchers", ["voucher_date"]) op.create_index("ix_vouchers_status", "vouchers", ["status"]) op.create_table( "voucher_entries", sa.Column("id", sa.String(length=36), primary_key=True), sa.Column("voucher_id", sa.String(length=36), nullable=False), sa.Column("line_number", sa.Integer(), nullable=False), sa.Column("account_id", sa.String(length=36), nullable=False), sa.Column("account_code", sa.String(length=20), nullable=False), sa.Column("account_name", sa.String(length=200), nullable=False), sa.Column("description", sa.Text(), nullable=True), sa.Column("debit_amount", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column("credit_amount", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.ForeignKeyConstraint(["voucher_id"], ["vouchers.id"], ondelete="CASCADE"), ) op.create_index("ix_voucher_entries_voucher_id", "voucher_entries", ["voucher_id"]) op.create_table( "ledger_entries", sa.Column("id", sa.String(length=36), primary_key=True), sa.Column("company_id", sa.String(length=36), nullable=False), sa.Column("account_id", sa.String(length=36), nullable=False), sa.Column("account_code", sa.String(length=20), nullable=False), sa.Column("account_name", sa.String(length=200), nullable=False), sa.Column("voucher_id", sa.String(length=36), nullable=False), sa.Column("voucher_number", sa.String(length=50), nullable=False), sa.Column("voucher_date", sa.Date(), nullable=False), sa.Column("entry_date", sa.Date(), nullable=False), sa.Column("description", sa.Text(), nullable=True), sa.Column("debit_amount", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column("credit_amount", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column("running_balance", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column("created_at", sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(["company_id"], ["companies.id"], ondelete="CASCADE"), sa.ForeignKeyConstraint(["voucher_id"], ["vouchers.id"], ondelete="CASCADE"), ) op.create_index( "ix_ledger_company_account_date", "ledger_entries", ["company_id", "account_id", "entry_date"], ) op.create_index("ix_ledger_company_id", "ledger_entries", ["company_id"]) op.create_index("ix_ledger_voucher_id", "ledger_entries", ["voucher_id"]) op.create_table( "reports", sa.Column("id", sa.String(length=36), primary_key=True), sa.Column("company_id", sa.String(length=36), nullable=False), sa.Column("report_type", sa.String(length=40), nullable=False), sa.Column("report_title", sa.String(length=255), nullable=False), sa.Column("from_date", sa.Date(), nullable=True), sa.Column("to_date", sa.Date(), nullable=True), sa.Column("report_date", sa.Date(), nullable=False), sa.Column("account_id", sa.String(length=36), nullable=True), sa.Column("account_code", sa.String(length=20), nullable=True), sa.Column("totals", mysql.JSON(), nullable=False), sa.Column("parameters", mysql.JSON(), nullable=False), sa.Column("generated_at", sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(["company_id"], ["companies.id"], ondelete="CASCADE"), ) op.create_index( "ix_reports_company_type_generated", "reports", ["company_id", "report_type", "generated_at"], ) op.create_index("ix_reports_company_id", "reports", ["company_id"]) op.create_index("ix_reports_report_date", "reports", ["report_date"]) op.create_table( "report_line_items", sa.Column("id", sa.String(length=36), primary_key=True), sa.Column("report_id", sa.String(length=36), nullable=False), sa.Column("account_id", sa.String(length=36), nullable=True), sa.Column("account_code", sa.String(length=20), nullable=False), sa.Column("account_name", sa.String(length=200), nullable=False), sa.Column("account_type", sa.String(length=20), nullable=True), sa.Column("debit", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column("credit", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column("balance", sa.Numeric(18, 2), nullable=False, server_default="0.00"), sa.Column("metadata", mysql.JSON(), nullable=False), sa.ForeignKeyConstraint(["report_id"], ["reports.id"], ondelete="CASCADE"), ) op.create_index("ix_report_line_items_report_id", "report_line_items", ["report_id"]) def downgrade() -> None: op.drop_table("report_line_items") op.drop_table("reports") op.drop_table("ledger_entries") op.drop_table("voucher_entries") op.drop_table("vouchers") op.drop_table("chart_of_accounts") op.drop_table("companies") op.drop_table("refresh_tokens") op.drop_table("user_registrations")