import ast import os import sys import types import unittest from decimal import Decimal from pathlib import Path from sqlalchemy import Boolean, Date, DateTime, ForeignKey, Integer, Numeric, String, Text, UniqueConstraint ROOT = Path(__file__).resolve().parents[1] def source(path: str) -> str: return (ROOT / path).read_text(encoding="utf-8") def class_names(path: str) -> set[str]: parsed = ast.parse(source(path)) return {node.name for node in ast.walk(parsed) if isinstance(node, ast.ClassDef)} def import_models(): sys.path.insert(0, str(ROOT)) os.environ.setdefault("DB_NAME", "test.db") dotenv = types.ModuleType("dotenv") dotenv.load_dotenv = lambda *args, **kwargs: None sys.modules.setdefault("dotenv", dotenv) from app.core import models return models class FinanceCenterModelTests(unittest.TestCase): def test_models_define_finance_entities(self) -> None: models = source("app/core/models.py") names = class_names("app/core/models.py") for expected in [ "Expense", "ReceiptAsset", "Employee", "PayrollRecord", "GstSetting", "GstReturn", "GstReturnSnapshotRow", "GstAdjustment", ]: self.assertIn(expected, names) self.assertIn('ird_number = Column(String(100))', models) self.assertIn('cycle_months = Column(Integer, nullable=False)', models) self.assertIn('source = Column(String(50), default="manual")', models) self.assertIn('status = Column(String(30), default="draft")', models) self.assertIn('receipt_asset_id = Column(Integer, ForeignKey("receipt_assets.id"', models) self.assertIn('tax_mode = Column(String(30), default="gst_15")', models) self.assertIn("custom_tax_rate = Column(DECIMAL(8, 4), nullable=True)", models) self.assertIn("received_date = Column(Date, nullable=True)", models) self.assertIn("gst_amount_override = Column(DECIMAL(12, 2), nullable=True)", models) self.assertIn("gst_amount_overridden = Column(Boolean, default=False)", models) self.assertIn('__tablename__ = "gst_returns"', models) self.assertIn('UniqueConstraint("tenant_id", "period_start", "period_end", name="uq_gst_returns_tenant_period")', models) self.assertIn('__tablename__ = "gst_return_snapshot_rows"', models) self.assertIn('__tablename__ = "gst_adjustments"', models) self.assertIn('gst_output = Column(DECIMAL(12, 2), default=0)', models) self.assertIn('total_purchases_expenses = Column(DECIMAL(12, 2), default=0)', models) self.assertIn('gst_payable = Column(DECIMAL(12, 2), default=0)', models) self.assertIn('gst_return_id = Column(Integer, ForeignKey("gst_returns.id", ondelete="CASCADE")', models) self.assertIn('gst_return_id = Column(Integer, ForeignKey("gst_returns.id"', models) self.assertIn('snapshot_rows = relationship("GstReturnSnapshotRow", back_populates="gst_return"', models) self.assertIn('adjustments = relationship("GstAdjustment", back_populates="gst_return"', models) self.assertNotIn("gst_treatment = Column", models) self.assertNotIn("gst_rate = Column", models) self.assertNotIn("received_at = Column", models) def test_gst_models_are_registered_in_sqlalchemy_metadata(self) -> None: models = import_models() metadata = models.Base.metadata for table_name in [ "invoice_items", "payments", "expenses", "gst_returns", "gst_return_snapshot_rows", "gst_adjustments", ]: self.assertIn(table_name, metadata.tables) invoice_items = metadata.tables["invoice_items"] self.assertIsInstance(invoice_items.c.tax_mode.type, String) self.assertEqual(invoice_items.c.tax_mode.type.length, 30) self.assertEqual(invoice_items.c.tax_mode.default.arg, "gst_15") self.assertIsInstance(invoice_items.c.custom_tax_rate.type, Numeric) self.assertEqual(invoice_items.c.custom_tax_rate.type.precision, 8) self.assertEqual(invoice_items.c.custom_tax_rate.type.scale, 4) self.assertTrue(invoice_items.c.custom_tax_rate.nullable) payments = metadata.tables["payments"] self.assertIsInstance(payments.c.received_date.type, Date) self.assertTrue(payments.c.received_date.nullable) expenses = metadata.tables["expenses"] self.assertEqual(expenses.c.tax_mode.default.arg, "gst_15") self.assertEqual(expenses.c.custom_tax_rate.type.precision, 8) self.assertEqual(expenses.c.custom_tax_rate.type.scale, 4) self.assertIsInstance(expenses.c.gst_amount_override.type, Numeric) self.assertEqual(expenses.c.gst_amount_override.type.precision, 12) self.assertEqual(expenses.c.gst_amount_override.type.scale, 2) self.assertTrue(expenses.c.gst_amount_override.nullable) self.assertIsInstance(expenses.c.gst_amount_overridden.type, Boolean) self.assertFalse(expenses.c.gst_amount_overridden.default.arg) def test_gst_return_and_adjustment_mapper_structure(self) -> None: models = import_models() gst_returns = models.Base.metadata.tables["gst_returns"] snapshot_rows = models.Base.metadata.tables["gst_return_snapshot_rows"] gst_adjustments = models.Base.metadata.tables["gst_adjustments"] self.assertIsInstance(gst_returns.c.period_start.type, Date) self.assertFalse(gst_returns.c.period_start.nullable) self.assertIsInstance(gst_returns.c.period_end.type, Date) self.assertFalse(gst_returns.c.period_end.nullable) self.assertEqual(gst_returns.c.status.default.arg, "draft") unique_constraints = [ constraint for constraint in gst_returns.constraints if isinstance(constraint, UniqueConstraint) ] self.assertTrue( any( constraint.name == "uq_gst_returns_tenant_period" and [column.name for column in constraint.columns] == ["tenant_id", "period_start", "period_end"] for constraint in unique_constraints ) ) for column_name in [ "total_sales_income", "zero_rated_supplies", "gst_taxable_income", "gst_output", "debit_adjustments", "total_purchases_expenses", "gst_input", "credit_adjustments", "gst_payable", ]: column = gst_returns.c[column_name] self.assertIsInstance(column.type, Numeric) self.assertEqual(column.type.precision, 12) self.assertEqual(column.type.scale, 2) self.assertEqual(column.default.arg, 0) self.assertTrue(gst_returns.c.prepared_at.nullable) self.assertTrue(gst_returns.c.locked_at.nullable) self.assertTrue(gst_returns.c.filed_at.nullable) self.assertIsInstance(gst_returns.c.notes.type, Text) self.assertIsInstance(snapshot_rows.c.gst_return_id.type, Integer) self.assertFalse(snapshot_rows.c.gst_return_id.nullable) snapshot_foreign_keys: set[ForeignKey] = set(snapshot_rows.c.gst_return_id.foreign_keys) self.assertEqual(len(snapshot_foreign_keys), 1) snapshot_fk = next(iter(snapshot_foreign_keys)) self.assertEqual(snapshot_fk.target_fullname, "gst_returns.id") self.assertEqual(snapshot_fk.ondelete, "CASCADE") self.assertFalse(snapshot_rows.c.row_type.nullable) self.assertTrue(snapshot_rows.c.source_id.nullable) self.assertFalse(snapshot_rows.c.source_date.nullable) self.assertIsInstance(snapshot_rows.c.customer_or_vendor.type, String) self.assertIsInstance(snapshot_rows.c.description.type, Text) self.assertEqual(snapshot_rows.c.tax_mode.default.arg, "gst_15") self.assertEqual(snapshot_rows.c.tax_rate.type.precision, 8) self.assertEqual(snapshot_rows.c.tax_rate.type.scale, 4) self.assertIsInstance(snapshot_rows.c.metadata_json.type, Text) self.assertIsInstance(gst_adjustments.c.gst_return_id.type, Integer) self.assertTrue(gst_adjustments.c.gst_return_id.nullable) foreign_keys: set[ForeignKey] = set(gst_adjustments.c.gst_return_id.foreign_keys) self.assertEqual(len(foreign_keys), 1) self.assertEqual(next(iter(foreign_keys)).target_fullname, "gst_returns.id") self.assertFalse(gst_adjustments.c.adjustment_type.nullable) self.assertFalse(gst_adjustments.c.reason.nullable) self.assertFalse(gst_adjustments.c.amount.nullable) self.assertTrue(gst_adjustments.c.source_period_start.nullable) self.assertTrue(gst_adjustments.c.source_period_end.nullable) self.assertFalse(gst_adjustments.c.target_period_start.nullable) self.assertFalse(gst_adjustments.c.target_period_end.nullable) self.assertTrue(gst_adjustments.c.linked_invoice_id.nullable) self.assertTrue(gst_adjustments.c.linked_payment_id.nullable) self.assertTrue(gst_adjustments.c.linked_expense_id.nullable) self.assertTrue(gst_adjustments.c.direction.nullable) self.assertTrue(gst_adjustments.c.source_type.nullable) self.assertTrue(gst_adjustments.c.source_id.nullable) self.assertTrue(gst_adjustments.c.adjustment_date.nullable) self.assertEqual(models.GstReturn.snapshot_rows.property.mapper.class_, models.GstReturnSnapshotRow) self.assertEqual(models.GstReturn.snapshot_rows.property.back_populates, "gst_return") self.assertEqual(models.GstReturnSnapshotRow.gst_return.property.mapper.class_, models.GstReturn) self.assertEqual(models.GstReturnSnapshotRow.gst_return.property.back_populates, "snapshot_rows") self.assertEqual(models.GstReturn.adjustments.property.mapper.class_, models.GstAdjustment) self.assertEqual(models.GstReturn.adjustments.property.back_populates, "gst_return") self.assertEqual(models.GstAdjustment.gst_return.property.mapper.class_, models.GstReturn) self.assertEqual(models.GstAdjustment.gst_return.property.back_populates, "adjustments") def test_init_sql_creates_finance_tables(self) -> None: sql = source("init_db.sql") self.assertIn("CREATE TABLE IF NOT EXISTS expenses", sql) self.assertIn("CREATE TABLE IF NOT EXISTS receipt_assets", sql) self.assertIn("CREATE TABLE IF NOT EXISTS employees", sql) self.assertIn("CREATE TABLE IF NOT EXISTS payroll_records", sql) self.assertIn("CREATE TABLE IF NOT EXISTS gst_settings", sql) self.assertIn("tax_mode VARCHAR(30) DEFAULT 'gst_15'", sql) self.assertIn("custom_tax_rate DECIMAL(8,4) NULL", sql) self.assertIn("received_date DATE NULL", sql) self.assertIn("gst_amount_override DECIMAL(12,2) NULL", sql) self.assertIn("gst_amount_overridden BOOLEAN DEFAULT FALSE", sql) self.assertIn("CREATE TABLE IF NOT EXISTS gst_returns", sql) self.assertIn("CONSTRAINT uq_gst_returns_tenant_period UNIQUE (tenant_id, period_start, period_end)", sql) self.assertIn("CREATE TABLE IF NOT EXISTS gst_return_snapshot_rows", sql) self.assertIn("CREATE TABLE IF NOT EXISTS gst_adjustments", sql) self.assertIn("gst_output DECIMAL(12,2) DEFAULT 0", sql) self.assertIn("total_purchases_expenses DECIMAL(12,2) DEFAULT 0", sql) self.assertIn("gst_payable DECIMAL(12,2) DEFAULT 0", sql) self.assertIn("CONSTRAINT fk_gst_snapshot_return", sql) self.assertIn("CONSTRAINT fk_gst_adjustments_return", sql) def test_migration_file_contains_finance_tables(self) -> None: migration = source("migrations/2026-05-31-finance-center.sql") self.assertIn("Run this once on an already-created database", migration) self.assertIn("USE erp;", migration) self.assertIn("INFORMATION_SCHEMA.COLUMNS", migration) self.assertIn("INFORMATION_SCHEMA.STATISTICS", migration) self.assertIn("PREPARE stmt FROM @sql", migration) self.assertIn("EXECUTE stmt", migration) self.assertNotIn("ADD COLUMN IF NOT EXISTS", migration) self.assertNotIn("CREATE INDEX IF NOT EXISTS", migration) self.assertIn("CREATE TABLE IF NOT EXISTS receipt_assets", migration) self.assertIn("CREATE TABLE IF NOT EXISTS expenses", migration) self.assertIn("CREATE TABLE IF NOT EXISTS employees", migration) self.assertIn("CREATE TABLE IF NOT EXISTS payroll_records", migration) self.assertIn("CREATE TABLE IF NOT EXISTS gst_settings", migration) self.assertIn("ADD COLUMN tax_mode VARCHAR(30) DEFAULT", migration) self.assertIn("ADD COLUMN custom_tax_rate DECIMAL(8,4) NULL", migration) self.assertIn("ADD COLUMN received_date DATE NULL", migration) self.assertIn("ADD COLUMN gst_amount_override DECIMAL(12,2) NULL", migration) self.assertIn("ADD COLUMN gst_amount_overridden BOOLEAN DEFAULT FALSE", migration) self.assertIn("CREATE TABLE IF NOT EXISTS gst_returns", migration) self.assertIn("CONSTRAINT uq_gst_returns_period UNIQUE (period_start, period_end)", migration) self.assertIn("INDEX_NAME = 'uq_gst_returns_period'", migration) self.assertIn("CREATE UNIQUE INDEX uq_gst_returns_period ON gst_returns(period_start, period_end)", migration) self.assertIn("CREATE TABLE IF NOT EXISTS gst_return_snapshot_rows", migration) self.assertIn("CREATE TABLE IF NOT EXISTS gst_adjustments", migration) self.assertIn("ADD COLUMN gst_output DECIMAL(12,2) DEFAULT 0", migration) self.assertIn("ADD COLUMN total_purchases_expenses DECIMAL(12,2) DEFAULT 0", migration) self.assertIn("ADD COLUMN gst_payable DECIMAL(12,2) DEFAULT 0", migration) self.assertIn("CONSTRAINT fk_gst_snapshot_return", migration) self.assertIn("CONSTRAINT fk_gst_adjustments_return", migration) if __name__ == "__main__": unittest.main()