# TANRAIL ONE — Phase 4.0 Data Model Specification

## 1. Schema Overview
Phase 4.0 introduces 7 database tables under `app/Domains/Core/` and modifies 2 existing payment tables. All tables enforce strict multi-tenant scoping via `organization_id` and `business_unit_id`.

```text
organizations ────► business_units ────► branches
     │                   │                   │
     ├──► customers ─────┼───────────────────┼──► invoices ────► invoice_lines
     │                   │                   │        │
     └──► suppliers ─────┼───────────────────┼──► supplier_invoices ──► supplier_invoice_lines
                         │                   │        │
                         └───────────────────┴──► payment_allocations ◄── payments
```

---

## 2. Table Specifications

### 2.1 Table: `invoices`
Stores customer sales billing headers.

| Column | Type | Nullable | Constraints / Index | Description |
|---|---|---|---|---|
| `id` | BigInt (PK) | No | Auto-increment | Unique Invoice Identifier |
| `organization_id` | ForeignId | No | `constrained('organizations')` | Multi-tenant scope |
| `business_unit_id` | ForeignId | No | `constrained('business_units')` | BU Scoped |
| `branch_id` | ForeignId | No | `constrained('branches')` | Operational branch |
| `customer_id` | ForeignId | No | `constrained('customers')` | Billed customer |
| `order_id` | ForeignId | Yes | `constrained('orders')->nullOnDelete()` | Optional source order |
| `invoice_number` | String | No | Unique index `(business_unit_id, invoice_number)` | Sequential legal invoice number |
| `issue_date` | Date | No | Index | Date invoice was issued |
| `due_date` | Date | No | Index | Payment due date |
| `currency` | String(3) | No | Default 'TZS' | ISO Currency code |
| `subtotal` | Decimal(15,4) | No | Default 0.0000 | Line subtotals before tax |
| `discount_amount` | Decimal(15,4) | No | Default 0.0000 | Total discount applied |
| `tax_amount` | Decimal(15,4) | No | Default 0.0000 | Total tax calculated |
| `total` | Decimal(15,4) | No | Default 0.0000 | Grand total (`subtotal - discount + tax`) |
| `amount_paid` | Decimal(15,4) | No | Default 0.0000 | Total payments allocated |
| `balance_due` | Decimal(15,4) | No | Default 0.0000 | Outstanding balance (`total - amount_paid`) |
| `status` | String | No | Enum: `DRAFT`, `ISSUED`, `PARTIALLY_PAID`, `PAID`, `CANCELLED`, `VOIDED` | Lifecycle status |
| `notes` | Text | Yes | None | Customer notes or terms |
| `created_by` | ForeignId | Yes | `constrained('users')` | User who created invoice |
| `timestamps` | Timestamps | No | None | Standard Laravel timestamps |

---

### 2.2 Table: `invoice_lines`
Stores detailed commercial line items for an invoice.

| Column | Type | Nullable | Constraints | Description |
|---|---|---|---|---|
| `id` | BigInt (PK) | No | Auto-increment | Line ID |
| `invoice_id` | ForeignId | No | `constrained('invoices')->cascadeOnDelete()` | Parent invoice |
| `item_id` | ForeignId | Yes | `constrained('items')->nullOnDelete()` | Optional master item |
| `unit_id` | ForeignId | Yes | `constrained('units')` | Unit of measure |
| `tax_category_id` | ForeignId | Yes | `constrained('tax_categories')` | Applicable tax category |
| `item_name_snapshot` | String | No | None | Snapshot of item name at billing |
| `description` | String | Yes | None | Line description |
| `quantity` | Decimal(15,4) | No | Default 1.0000 | Billed quantity |
| `unit_price` | Decimal(15,4) | No | Default 0.0000 | Unit price snapshot |
| `discount_amount` | Decimal(15,4) | No | Default 0.0000 | Line discount |
| `tax_rate_snapshot` | Decimal(5,2) | No | Default 0.00 | Tax rate percentage snapshot |
| `tax_amount` | Decimal(15,4) | No | Default 0.0000 | Line tax amount |
| `subtotal` | Decimal(15,4) | No | Default 0.0000 | Line subtotal (`qty * price - discount`) |
| `total` | Decimal(15,4) | No | Default 0.0000 | Line total (`subtotal + tax_amount`) |
| `timestamps` | Timestamps | No | None | Standard timestamps |

---

### 2.3 Table: `supplier_invoices` (Vendor Bills)
Stores supplier bills against purchase orders and receipts.

| Column | Type | Nullable | Constraints / Index | Description |
|---|---|---|---|---|
| `id` | BigInt (PK) | No | Auto-increment | Vendor Bill ID |
| `organization_id` | ForeignId | No | `constrained('organizations')` | Multi-tenant scope |
| `business_unit_id` | ForeignId | No | `constrained('business_units')` | BU Scoped |
| `branch_id` | ForeignId | No | `constrained('branches')` | Operational branch |
| `supplier_id` | ForeignId | No | `constrained('suppliers')` | Billing supplier |
| `purchase_order_id` | ForeignId | Yes | `constrained('purchase_orders')->nullOnDelete()` | Linked PO |
| `supplier_bill_number` | String | No | Index `(supplier_id, supplier_bill_number)` | Supplier's bill/invoice number |
| `internal_reference` | String | No | Unique index `(business_unit_id, internal_reference)` | System reference code |
| `bill_date` | Date | No | Index | Vendor invoice date |
| `due_date` | Date | No | Index | Payment due date |
| `subtotal` | Decimal(15,4) | No | Default 0.0000 | Subtotal before tax |
| `tax_amount` | Decimal(15,4) | No | Default 0.0000 | Total tax charged |
| `total` | Decimal(15,4) | No | Default 0.0000 | Grand total |
| `amount_paid` | Decimal(15,4) | No | Default 0.0000 | Payments allocated |
| `balance_due` | Decimal(15,4) | No | Default 0.0000 | Outstanding payable balance |
| `status` | String | No | Enum: `DRAFT`, `RECEIVED`, `APPROVED`, `PARTIALLY_PAID`, `PAID`, `VOIDED` | Lifecycle status |
| `match_status` | String | No | Default 'UNMATCHED'. Enum: `UNMATCHED`, `MATCHED`, `DISCREPANCY` | 3-Way match status |
| `created_by` | ForeignId | Yes | `constrained('users')` | Entry user |
| `approved_by` | ForeignId | Yes | `constrained('users')` | Approving manager |
| `approved_at` | Timestamp | Yes | None | Approval timestamp |
| `timestamps` | Timestamps | No | None | Standard timestamps |

---

### 2.4 Table: `supplier_invoice_lines`

| Column | Type | Nullable | Constraints | Description |
|---|---|---|---|---|
| `id` | BigInt (PK) | No | Auto-increment | Line ID |
| `supplier_invoice_id` | ForeignId | No | `constrained('supplier_invoices')->cascadeOnDelete()` | Parent bill |
| `item_id` | ForeignId | Yes | `constrained('items')` | Linked item |
| `purchase_receipt_line_id` | ForeignId | Yes | `constrained('purchase_receipt_lines')` | Linked receipt line |
| `description` | String | No | None | Line description |
| `quantity` | Decimal(15,4) | No | Default 1.0000 | Billed quantity |
| `unit_price` | Decimal(15,4) | No | Default 0.0000 | Unit price |
| `tax_amount` | Decimal(15,4) | No | Default 0.0000 | Line tax |
| `total` | Decimal(15,4) | No | Default 0.0000 | Line total |
| `timestamps` | Timestamps | No | None | Standard timestamps |

---

### 2.5 Table: `credit_notes`
Stores customer credit notes for billing corrections.

| Column | Type | Nullable | Constraints / Index | Description |
|---|---|---|---|---|
| `id` | BigInt (PK) | No | Auto-increment | Credit Note ID |
| `organization_id` | ForeignId | No | `constrained('organizations')` | Multi-tenant scope |
| `business_unit_id` | ForeignId | No | `constrained('business_units')` | BU Scoped |
| `branch_id` | ForeignId | No | `constrained('branches')` | Branch |
| `customer_id` | ForeignId | No | `constrained('customers')` | Customer |
| `invoice_id` | ForeignId | Yes | `constrained('invoices')` | Original invoice |
| `credit_note_number` | String | No | Unique `(business_unit_id, credit_note_number)` | Unique credit number |
| `issue_date` | Date | No | Index | Issue date |
| `subtotal` | Decimal(15,4) | No | Default 0.0000 | Subtotal reversed |
| `tax_amount` | Decimal(15,4) | No | Default 0.0000 | Tax reversed |
| `total` | Decimal(15,4) | No | Default 0.0000 | Total credit amount |
| `amount_allocated` | Decimal(15,4) | No | Default 0.0000 | Amount applied to invoices |
| `remaining_credit` | Decimal(15,4) | No | Default 0.0000 | Unapplied credit balance |
| `status` | String | No | Enum: `DRAFT`, `ISSUED`, `APPLIED`, `VOIDED` | Status |
| `reason` | String | No | None | Reason for issuance |
| `created_by` | ForeignId | Yes | `constrained('users')` | Issuer |
| `timestamps` | Timestamps | No | None | Standard timestamps |

---

### 2.6 Table: `credit_note_lines`

| Column | Type | Nullable | Constraints | Description |
|---|---|---|---|---|
| `id` | BigInt (PK) | No | Auto-increment | Line ID |
| `credit_note_id` | ForeignId | No | `constrained('credit_notes')->cascadeOnDelete()` | Parent credit note |
| `item_id` | ForeignId | Yes | `constrained('items')` | Item reference |
| `description` | String | No | None | Line explanation |
| `quantity` | Decimal(15,4) | No | Default 0.0000 | Quantity reversed |
| `unit_price` | Decimal(15,4) | No | Default 0.0000 | Price applied |
| `tax_amount` | Decimal(15,4) | No | Default 0.0000 | Tax reversed |
| `total` | Decimal(15,4) | No | Default 0.0000 | Line total |
| `timestamps` | Timestamps | No | None | Standard timestamps |

---

### 2.7 Table: `payment_allocations`
Generic allocation pivot linking payment sources to commercial billing targets.

| Column | Type | Nullable | Constraints / Index | Description |
|---|---|---|---|---|
| `id` | BigInt (PK) | No | Auto-increment | Allocation ID |
| `organization_id` | ForeignId | No | `constrained('organizations')` | Multi-tenant scope |
| `business_unit_id` | ForeignId | No | `constrained('business_units')` | BU Scoped |
| `payment_type` | String | No | Index `(payment_type, payment_id)` | Polymorphic source: `Payment` or `SupplierPayment` |
| `payment_id` | BigInt | No | Index | Source Payment ID |
| `allocatable_type` | String | No | Index `(allocatable_type, allocatable_id)` | Target: `Invoice`, `SupplierInvoice`, `CreditNote` |
| `allocatable_id` | BigInt | No | Index | Target Document ID |
| `amount` | Decimal(15,4) | No | Default 0.0000 | Amount allocated |
| `allocation_date` | Date | No | Index | Date allocation was recorded |
| `created_by` | ForeignId | Yes | `constrained('users')` | User who allocated |
| `timestamps` | Timestamps | No | None | Standard timestamps |

---

## 3. Schema Modifications to Existing Tables

### 3.1 Table: `payments` (Customer Payments)
Add nullable `invoice_id` foreign key and unallocated balance field:
- `invoice_id` (nullable foreignId -> `invoices.id` nullOnDelete)
- `unallocated_amount` (`Decimal 15,4`, default 0.0000)

### 3.2 Table: `supplier_payments` (Supplier Payments)
Add nullable `supplier_invoice_id` foreign key and unallocated balance field:
- `supplier_invoice_id` (nullable foreignId -> `supplier_invoices.id` nullOnDelete)
- `unallocated_amount` (`Decimal 15,4`, default 0.0000)

---

## 4. Eloquent Models & Scopes

- `Invoice` & `SupplierInvoice`: Include `OrganizationScope` and `BusinessUnitScope`.
- `Invoice`: `belongsTo(Customer::class)`, `belongsTo(Order::class)`, `hasMany(InvoiceLine::class)`, `hasMany(PaymentAllocation::class, 'allocatable')`.
- `SupplierInvoice`: `belongsTo(Supplier::class)`, `belongsTo(PurchaseOrder::class)`, `hasMany(SupplierInvoiceLine::class)`, `hasMany(PaymentAllocation::class, 'allocatable')`.
- `PaymentAllocation`: Polymorphic relations `payment()` and `allocatable()`.