"""invoices + invoice_lines (02 §3, judgment #24). R1 keeps the CURRENT invoice shape verbatim — name (unique per tenant, case-insensitive), open/sent/paid with independent sent_at/paid_at, invoice_date (the accounting date), created_by, notes, manual_total — plus what R1 adds: from_party_id/to_party_id (seeded from the operator chain at migration) and the lines table, so one invoice can carry time, CC pass-throughs, adjustments, and manual items. Invoices are single-currency documents (02 §3): every line inherits invoice.currency (no per-line currency column); the migration asserts attached entries agree. The richer vocabulary (numbers, draft/issued/void, due dates) is R4. """ import uuid from datetime import date, datetime from sqlalchemy import ( CHAR, CheckConstraint, DateTime, ForeignKey, Index, Numeric, Text, Uuid, func, text, ) from sqlalchemy.orm import Mapped, mapped_column from app.models.base import Base class Invoice(Base): __tablename__ = "invoices" __table_args__ = ( # R1 kept open/sent/paid; R2 (alembic 0007) widened it to the # PlusROI sheet lifecycle (08 §2). The first three are UNTOUCHED — # BW time-invoice flows depend on them. CheckConstraint( "status IN ('open', 'sent', 'paid'," " 'pending_approval', 'ready', 'cancelled', 'delayed')", name="ck_invoices_status", ), Index( "uq_invoices_tenant_lower_name", "tenant_id", text("lower(name)"), unique=True, ), Index("ix_invoices_tenant_status", "tenant_id", "status"), Index("ix_invoices_from_party", "from_party_id"), Index("ix_invoices_to_party", "to_party_id"), # the sheet's "BW Invoice" settlement-month grouping key (§4) Index( "ix_invoices_tenant_settlement_month", "tenant_id", "settlement_month", ), ) id: Mapped[uuid.UUID] = mapped_column(Uuid(), primary_key=True, default=uuid.uuid4) tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id")) name: Mapped[str] = mapped_column(Text()) status: Mapped[str] = mapped_column(Text(), default="open") # single-currency document — lines inherit it (02 §3) currency: Mapped[str] = mapped_column(CHAR(3), default="CAD") # user-facing accounting date; the create form defaults it to the last # day of the previous month (app-side, not a DB default — parity) invoice_date: Mapped[date | None] = mapped_column() # independent of status — no constraint ties them (parity; R4 revisits) sent_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) paid_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) # seeded from the operator chain (05 step 2.7 pinned algorithm); # NULL = flagged for owner review in the migration run report from_party_id: Mapped[uuid.UUID | None] = mapped_column(ForeignKey("parties.id")) to_party_id: Mapped[uuid.UUID | None] = mapped_column(ForeignKey("parties.id")) # owner override when the printed total differs from the computed sum; # NULL = use the sum (carried verbatim from manual_total_usd) manual_total: Mapped[float | None] = mapped_column(Numeric(12, 2)) notes: Mapped[str | None] = mapped_column(Text()) created_by: Mapped[uuid.UUID | None] = mapped_column( ForeignKey("users.id", ondelete="SET NULL") ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now() ) # --- R2 receivables-ledger enrichment (alembic 0007; 08 §2) ---------- # All additive + nullable — R1 rows and the harness are untouched. # Books are CAD: `currency`/`manual_total` above hold the CAD book # value; the original-currency face survives here when USD. fee: Mapped[float | None] = mapped_column(Numeric(12, 2)) commission_pct: Mapped[float | None] = mapped_column(Numeric(7, 4)) commission_amount: Mapped[float | None] = mapped_column(Numeric(12, 2)) commission_party_id: Mapped[uuid.UUID | None] = mapped_column( ForeignKey("parties.id", ondelete="SET NULL") ) # first-of-month convention — the sheet's "BW Invoice" settlement tag settlement_month: Mapped[date | None] = mapped_column() relevant_period: Mapped[str | None] = mapped_column(Text()) original_amount: Mapped[float | None] = mapped_column(Numeric(12, 2)) original_currency: Mapped[str | None] = mapped_column(CHAR(3)) invoice_timing: Mapped[str | None] = mapped_column(Text()) billing_type: Mapped[str | None] = mapped_column(Text()) # the sheet "Invoice ID" / Wave number — NOT unique (sheet may dupe; # reconciliation warns rather than failing) external_id: Mapped[str | None] = mapped_column(Text()) due_date: Mapped[date | None] = mapped_column() # --- USD-payment realized FX (alembic 0008; ADR #022) ---------------- # The CAD actually collected, recorded at Paid for a USD invoice. # `manual_total`/`currency` stay the CAD book value; `original_amount`/ # `original_currency` stay the USD face (08 §2 realized-FX practice). collected_amount: Mapped[float | None] = mapped_column(Numeric(12, 2)) collected_currency: Mapped[str | None] = mapped_column(CHAR(3)) class InvoiceLine(Base): __tablename__ = "invoice_lines" __table_args__ = ( CheckConstraint( "kind IN ('time', 'expense', 'adjustment', 'manual')", name="ck_invoice_lines_kind", ), Index("ix_invoice_lines_invoice", "invoice_id"), ) id: Mapped[uuid.UUID] = mapped_column(Uuid(), primary_key=True, default=uuid.uuid4) invoice_id: Mapped[uuid.UUID] = mapped_column( ForeignKey("invoices.id", ondelete="CASCADE") ) kind: Mapped[str] = mapped_column(Text()) description: Mapped[str | None] = mapped_column(Text()) quantity: Mapped[float | None] = mapped_column(Numeric(12, 2)) rate: Mapped[float | None] = mapped_column(Numeric(12, 2)) amount: Mapped[float | None] = mapped_column(Numeric(12, 2)) # snapshot display fields as of attach (judgment #9) snapshot_client_name: Mapped[str | None] = mapped_column(Text()) snapshot_project_name: Mapped[str | None] = mapped_column(Text()) # carries the legacy invoice_applied_at/_by audit VERBATIM (05 step 2.7) attached_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) attached_by: Mapped[uuid.UUID | None] = mapped_column( ForeignKey("users.id", ondelete="SET NULL") ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now() )