"""Add invoice tables Revision ID: h6c7d8e9f0a1 Revises: g5b6c7d8e9f0 Create Date: 2025-12-24 This migration adds: - vendor_invoice_settings: Per-vendor invoice configuration - invoices: Invoice records with seller/buyer snapshots """ from collections.abc import Sequence import sqlalchemy as sa from alembic import op # revision identifiers, used by Alembic. revision: str = "h6c7d8e9f0a1" down_revision: str | None = "g5b6c7d8e9f0" branch_labels: str | Sequence[str] | None = None depends_on: str | Sequence[str] | None = None def upgrade() -> None: # Create vendor_invoice_settings table op.create_table( "vendor_invoice_settings", sa.Column("id", sa.Integer(), nullable=False), sa.Column("vendor_id", sa.Integer(), nullable=False), # Company details sa.Column("company_name", sa.String(length=255), nullable=False), sa.Column("company_address", sa.String(length=255), nullable=True), sa.Column("company_city", sa.String(length=100), nullable=True), sa.Column("company_postal_code", sa.String(length=20), nullable=True), sa.Column( "company_country", sa.String(length=2), server_default="LU", nullable=False ), # VAT information sa.Column("vat_number", sa.String(length=50), nullable=True), sa.Column("is_vat_registered", sa.Boolean(), server_default="1", nullable=False), # OSS sa.Column("is_oss_registered", sa.Boolean(), server_default="0", nullable=False), sa.Column("oss_registration_country", sa.String(length=2), nullable=True), # Invoice numbering sa.Column( "invoice_prefix", sa.String(length=20), server_default="INV", nullable=False ), sa.Column("invoice_next_number", sa.Integer(), server_default="1", nullable=False), sa.Column( "invoice_number_padding", sa.Integer(), server_default="5", nullable=False ), # Payment information sa.Column("payment_terms", sa.Text(), nullable=True), sa.Column("bank_name", sa.String(length=255), nullable=True), sa.Column("bank_iban", sa.String(length=50), nullable=True), sa.Column("bank_bic", sa.String(length=20), nullable=True), # Footer sa.Column("footer_text", sa.Text(), nullable=True), # Default VAT rate sa.Column( "default_vat_rate", sa.Numeric(precision=5, scale=2), server_default="17.00", nullable=False ), # Timestamps sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("(CURRENT_TIMESTAMP)"), nullable=False, ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("(CURRENT_TIMESTAMP)"), nullable=False, ), sa.ForeignKeyConstraint( ["vendor_id"], ["vendors.id"], ), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("vendor_id"), ) op.create_index( op.f("ix_vendor_invoice_settings_id"), "vendor_invoice_settings", ["id"], unique=False, ) op.create_index( op.f("ix_vendor_invoice_settings_vendor_id"), "vendor_invoice_settings", ["vendor_id"], unique=True, ) # Create invoices table op.create_table( "invoices", sa.Column("id", sa.Integer(), nullable=False), sa.Column("vendor_id", sa.Integer(), nullable=False), sa.Column("order_id", sa.Integer(), nullable=True), # Invoice identification sa.Column("invoice_number", sa.String(length=50), nullable=False), sa.Column("invoice_date", sa.DateTime(timezone=True), nullable=False), # Status sa.Column( "status", sa.String(length=20), server_default="draft", nullable=False ), # Snapshots (JSON) sa.Column("seller_details", sa.JSON(), nullable=False), sa.Column("buyer_details", sa.JSON(), nullable=False), sa.Column("line_items", sa.JSON(), nullable=False), # VAT information sa.Column( "vat_regime", sa.String(length=20), server_default="domestic", nullable=False ), sa.Column("destination_country", sa.String(length=2), nullable=True), sa.Column("vat_rate", sa.Numeric(precision=5, scale=2), nullable=False), sa.Column("vat_rate_label", sa.String(length=50), nullable=True), # Amounts (in cents) sa.Column("currency", sa.String(length=3), server_default="EUR", nullable=False), sa.Column("subtotal_cents", sa.Integer(), nullable=False), sa.Column("vat_amount_cents", sa.Integer(), nullable=False), sa.Column("total_cents", sa.Integer(), nullable=False), # Payment info sa.Column("payment_terms", sa.Text(), nullable=True), sa.Column("bank_details", sa.JSON(), nullable=True), sa.Column("footer_text", sa.Text(), nullable=True), # PDF sa.Column("pdf_generated_at", sa.DateTime(timezone=True), nullable=True), sa.Column("pdf_path", sa.String(length=500), nullable=True), # Notes sa.Column("notes", sa.Text(), nullable=True), # Timestamps sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.text("(CURRENT_TIMESTAMP)"), nullable=False, ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.text("(CURRENT_TIMESTAMP)"), nullable=False, ), sa.ForeignKeyConstraint( ["vendor_id"], ["vendors.id"], ), sa.ForeignKeyConstraint( ["order_id"], ["orders.id"], ), sa.PrimaryKeyConstraint("id"), ) op.create_index(op.f("ix_invoices_id"), "invoices", ["id"], unique=False) op.create_index(op.f("ix_invoices_vendor_id"), "invoices", ["vendor_id"], unique=False) op.create_index(op.f("ix_invoices_order_id"), "invoices", ["order_id"], unique=False) op.create_index( "idx_invoice_vendor_number", "invoices", ["vendor_id", "invoice_number"], unique=True, ) op.create_index( "idx_invoice_vendor_date", "invoices", ["vendor_id", "invoice_date"], unique=False, ) op.create_index( "idx_invoice_status", "invoices", ["vendor_id", "status"], unique=False, ) def downgrade() -> None: # Drop invoices table op.drop_index("idx_invoice_status", table_name="invoices") op.drop_index("idx_invoice_vendor_date", table_name="invoices") op.drop_index("idx_invoice_vendor_number", table_name="invoices") op.drop_index(op.f("ix_invoices_order_id"), table_name="invoices") op.drop_index(op.f("ix_invoices_vendor_id"), table_name="invoices") op.drop_index(op.f("ix_invoices_id"), table_name="invoices") op.drop_table("invoices") # Drop vendor_invoice_settings table op.drop_index( op.f("ix_vendor_invoice_settings_vendor_id"), table_name="vendor_invoice_settings", ) op.drop_index( op.f("ix_vendor_invoice_settings_id"), table_name="vendor_invoice_settings" ) op.drop_table("vendor_invoice_settings")