# Invoice Amount Edit from Ledger — Feasibility Analysis Report

> **Read-only analysis** — No code was changed. This report predicts whether this logic can be implemented.

---

## Current System Architecture

### Files Analyzed

| File | Lines | Role |
|------|-------|------|
| [supplier-transaction-ledger.html](file:///c:/xamppp/htdocs/projects/Ninja/ninja/supplier-transaction-ledger.html) | 373 | Ledger UI (debit click target) |
| [supplier_transaction_ledger.php](file:///c:/xamppp/htdocs/projects/Ninja/ninja/api/supplier_transaction_ledger.php) | 93 | Ledger data API |
| [forex_update.php](file:///c:/xamppp/htdocs/projects/Ninja/ninja/api/forex_update.php) | 256 | Current inline edit API |
| [invoices.php](file:///c:/xamppp/htdocs/projects/Ninja/ninja/api/invoices.php) | 429 | Invoice CRUD API |
| [invoice-new.html](file:///c:/xamppp/htdocs/projects/Ninja/ninja/invoice-new.html) | 1146 | Invoice create/edit UI |
| [shipments.php](file:///c:/xamppp/htdocs/projects/Ninja/ninja/api/shipments.php) | 509 | Shipment CRUD API |
| [supplier_payment.php](file:///c:/xamppp/htdocs/projects/Ninja/ninja/api/supplier_payment.php) | 267 | Invoice payment from supplier balance |

### Data Model (Current State)

```mermaid
erDiagram
    invoices {
        int id PK
        string invoice_no
        int supplier_id FK
        string currency
        decimal exchange_rate
        decimal discount
        decimal current_expense
        decimal paid_amount "Tracks total paid so far"
    }
    invoice_items {
        int id PK
        int invoice_id FK
        decimal quantity
        decimal unit_price
        decimal total_amount
    }
    supplier_transactions {
        int id PK
        int supplier_id FK
        int invoice_id FK "nullable - links payment to invoice"
        string transaction_type "'Payment' or 'Usage'"
        decimal amount "FC amount"
        decimal exchange_rate
        decimal amount_pkr
        string transaction_uid
    }
    shipment_details {
        int id PK
        int shipment_id FK
        int invoice_id FK
    }
    shipments {
        int id PK
        string status "Pending/Received etc"
    }
    supplier_balances {
        int supplier_id FK
        string currency_code
        decimal balance "FC balance cache"
    }

    invoices ||--o{ invoice_items : "has"
    invoices ||--o{ supplier_transactions : "invoice_id"
    invoices ||--o{ shipment_details : "linked to"
    shipments ||--o{ shipment_details : "contains"
```

### Key Formulas (Current)

| Concept | Formula |
|---------|---------|
| **Payable Before Discount** | `SUM(invoice_items.total_amount)` |
| **Payable After Discount** | `items_total - discount` |
| **Remaining** | `payable_after_discount - paid_amount` |
| **Payment Status** | Derived: `paid=0` → Unpaid, `0 < paid < total` → Partially Paid, `paid ≥ total` → Fully Paid |
| **Invoice Status** | Derived from `shipment_details` — if invoice exists in `shipment_details` → Shipped, otherwise → Unshipped |

> [!IMPORTANT]
> Currently there is **no `payment_status` column** in the `invoices` table. Payment status is calculated on-the-fly from `paid_amount` vs `items_total - discount`. Similarly, "shipped" status is derived by checking if the invoice appears in the `shipment_details` table.

---

## Proposed Change: Debit Click → Navigate to Invoice

### Current Behavior
- Double-click on a **debit** amount cell → inline `<input>` appears → updates `supplier_transactions.amount` via `forex_update.php`
- This only edits the **transaction** amount, **NOT** the invoice

### Proposed Behavior
- Double-click on a **debit** amount cell (invoice-related) → navigate to `invoice-new.html?id=XX`
- User edits invoice items/amounts inside the invoice form
- On save → system applies all the business rules below

---

## ✅ Feasibility Verdict: **YES — POSSIBLE** (with significant new logic)

The existing data model has **all the information needed** to implement this. Here's why:

1. ✅ `supplier_transactions.invoice_id` already links debit rows to invoices
2. ✅ `invoices.paid_amount` tracks how much has been paid
3. ✅ `shipment_details` table can determine shipped/unshipped status
4. ✅ `supplier_balances` table provides current FC balance for credit-back
5. ✅ `invoice-new.html` already supports edit mode (`?id=XX`)

---

## 16 Scenarios Analysis

### Legend
- **Old Value** = previous `items_total` (sum of all invoice_items.total_amount)
- **New Value** = new `items_total` after editing items
- **Payable** = `items_total - discount` (both before and after discount update)
- **Paid** = `invoices.paid_amount`

---

### A. UNSHIPPED + UNPAID (paid_amount = 0)

| # | New vs Old | Action on Invoice | Action on Supplier Balance | Feasible? |
|---|-----------|-------------------|---------------------------|-----------|
| 1 | New < Old | Reduce payable before/after discount | Supplier can pay up to new reduced value | ✅ Simple |
| 2 | New > Old | Increase payable before/after discount | Supplier can pay up to new increased value | ✅ Simple |

> [!NOTE]
> **Easiest cases** — no paid amount, no shipment. Just update the invoice items. The `items_total` changes automatically since items are re-inserted. Only need to ensure `discount` is ≤ new `items_total`.

---

### B. UNSHIPPED + PARTIALLY PAID (0 < paid_amount < payable_after_discount)

| # | New vs Old | Paid vs New | Action on Invoice | Action on Supplier Balance | Feasible? |
|---|-----------|-------------|-------------------|---------------------------|-----------|
| 3 | New < Old | Paid < New payable | Reduce payable | Supplier can pay remaining (new payable - paid) | ✅ Yes |
| 4 | New < Old | Paid > New payable | Reduce payable | **Excess** = `paid - new_payable`. Credit excess back to supplier at exchange_rate. Add note "Excess amount of Invoice #XX" | ✅ Yes — requires new transaction |
| 5 | New > Old | Paid < New payable | Increase payable. Change from "fully paid" → "partially paid" if was close to boundary | Supplier can pay up to new increased remaining | ✅ Yes |

> [!WARNING]  
> **Scenario #4 is the trickiest** — when paid amount exceeds the new payable after discount, we must:
> 1. Update `invoices.paid_amount = new_payable_after_discount` (cap it)
> 2. Calculate excess FC: `paid_amount - new_payable`
> 3. Convert excess to PKR using the invoice's `exchange_rate`
> 4. Insert new `supplier_transactions` row: type=`Payment`, amount=excess, details="Excess amount of Invoice #XX"
> 5. Update `supplier_balances` += excess

---

### C. UNSHIPPED + FULLY PAID (paid_amount ≥ payable_after_discount)

| # | New vs Old | Action on Invoice | Action on Supplier Balance | Feasible? |
|---|-----------|-------------------|---------------------------|-----------|
| 6 | New < Old | Reduce payable. Excess = old_payable - new_payable | Credit excess back to supplier. Note: "Excess amount of Invoice #XX" | ✅ Yes — same as #4 |
| 7 | New > Old | Increase payable. Status changes: Fully Paid → Partially Paid | Supplier can pay remaining (new payable - paid) | ✅ Yes |

---

### D. SHIPPED + UNPAID (paid_amount = 0)

| # | New vs Old | Action on Invoice | Action on Supplier Balance | Feasible? |
|---|-----------|-------------------|---------------------------|-----------|
| 8 | New < Old | Reduce payable before/after discount | Supplier can pay up to new reduced value | ✅ Same as #1 |
| 9 | New > Old | Increase payable before/after discount | Supplier can pay up to new increased value | ✅ Same as #2 |

---

### E. SHIPPED + PARTIALLY PAID

| # | New vs Old | Paid vs New | Action on Invoice | Action on Supplier/Shipment | Feasible? |
|---|-----------|-------------|-------------------|----------------------------|-----------|
| 10 | New < Old | Paid < New payable | Reduce payable | Supplier can pay remaining | ✅ Yes |
| 11 | New < Old | Paid > New payable | Reduce payable. Credit excess back to supplier | "Excess amount of Invoice #XX" | ✅ Yes — same as #4 |
| 12 | New > Old | Paid < New payable | Increase payable. If status was "fully paid" → change to "partially paid" on **both invoice AND shipment** | Supplier can pay remaining | ✅ Yes — needs shipment status update |

---

### F. SHIPPED + FULLY PAID (Invoice fully paid, Shipment still partially paid)

| # | New vs Old | Action on Invoice | Action on Shipment | Feasible? |
|---|-----------|-------------------|--------------------|-----------|
| 13 | New < Old | Reduce payable. Credit excess to supplier | "Excess amount of Invoice #XX" | ✅ Yes |
| 14 | New > Old | Increase payable. Invoice: Fully Paid → Partially Paid | Shipment status unchanged (still partial) | ✅ Yes |

---

### G. SHIPPED + FULLY PAID (Both Invoice and Shipment fully paid)

| # | New vs Old | Action on Invoice | Action on Shipment | Feasible? |
|---|-----------|-------------------|--------------------|-----------|
| 15 | New < Old | Reduce payable. Credit excess using **shipment's exchange rate** | "Excess amount of Invoice #XX" | ✅ Yes — needs shipment exchange rate |
| 16 | New > Old | Increase payable. **Both** Invoice + Shipment → Partially Paid | Supplier can pay remaining | ✅ Yes — needs both status updates |

---

## Required Changes Summary

### New Columns Needed

| Table | Column | Type | Purpose |
|-------|--------|------|---------|
| `invoices` | `payment_status` | `VARCHAR(20)` | `Unpaid` / `Partially Paid` / `Fully Paid` — calculated and stored |
| `shipments` | `payment_status` | `VARCHAR(20)` | `Unpaid` / `Partially Paid` / `Fully Paid` — for shipment-level tracking |

> [!TIP]
> These columns are **optional** but recommended. The status can be derived from `paid_amount` vs `items_total - discount`, but storing it explicitly makes queries simpler and the update logic clearer.

### Files That Need Changes

| File | Change Type | Description |
|------|-------------|-------------|
| [supplier-transaction-ledger.html](file:///c:/xamppp/htdocs/projects/Ninja/ninja/supplier-transaction-ledger.html) | **Modify** | Debit cell click → check if `invoice_id` exists → navigate to `invoice-new.html?id=XX` instead of inline edit |
| [invoice-new.html](file:///c:/xamppp/htdocs/projects/Ninja/ninja/invoice-new.html) | **Modify** | On save (edit mode), detect value change → call new API to apply business rules |
| [invoices.php](file:///c:/xamppp/htdocs/projects/Ninja/ninja/api/invoices.php) | **Modify** | New action `update_with_balance_adjustment` — the core logic handling all 16 scenarios |
| [supplier_transaction_ledger.php](file:///c:/xamppp/htdocs/projects/Ninja/ninja/api/supplier_transaction_ledger.php) | **Modify** | Pass `invoice_id` to frontend so debit click knows which invoice to link to |
| [shipments.php](file:///c:/xamppp/htdocs/projects/Ninja/ninja/api/shipments.php) | **Minor** | Helper to update shipment payment status when needed |

### New API Endpoint Needed

A new action in [invoices.php](file:///c:/xamppp/htdocs/projects/Ninja/ninja/api/invoices.php): `action=update_with_balance_adjustment`

This endpoint would:

```
1. Fetch OLD invoice data (items_total, discount, paid_amount)
2. Calculate OLD payable = items_total - discount
3. Update invoice items (same as current update logic)
4. Calculate NEW items_total from new items
5. Calculate NEW payable = new_items_total - (possibly updated) discount
6. Determine invoice_status (check shipment_details)
7. Determine payment_status (from paid_amount vs old_payable)

8. Apply rules:
   IF new_payable < old_payable:
     IF paid_amount > new_payable:
       excess = paid_amount - new_payable
       Credit excess back to supplier_balances
       Insert supplier_transaction (Payment type, "Excess of Invoice #XX")
       Set paid_amount = new_payable
       Set payment_status = 'Fully Paid'
     ELSE:
       Keep paid_amount unchanged
       Recalculate payment_status
   
   IF new_payable > old_payable:
     IF payment_status was 'Fully Paid':
       Change to 'Partially Paid'
       IF shipped: also update shipment payment_status
     Keep paid_amount unchanged
     Supplier can now pay up to (new_payable - paid_amount)

9. Commit all changes in single transaction
```

### Data Flow (Proposed)

```mermaid
flowchart TD
    A["User double-clicks DEBIT cell in ledger"] --> B{Is invoice_id present?}
    B -->|No| C["Keep current inline edit behavior"]
    B -->|Yes| D["Navigate to invoice-new.html?id=XX"]
    D --> E["User edits invoice items/amounts"]
    E --> F["User clicks Save"]
    F --> G["API: update_with_balance_adjustment"]
    G --> H{New value vs Old value?}
    H -->|Decreased| I{Paid > New Payable?}
    I -->|Yes| J["Credit excess back to supplier\nInsert 'Excess' transaction\nCap paid_amount"]
    I -->|No| K["Just update payable\nKeep paid_amount"]
    H -->|Increased| L{Was Fully Paid?}
    L -->|Yes| M["Change to Partially Paid\nUpdate shipment if shipped"]
    L -->|No| N["Just update payable\nRemaining increases"]
```

---

## Risk Assessment

| Risk | Level | Mitigation |
|------|-------|------------|
| Excess credit-back calculation accuracy | 🟡 Medium | Use the **invoice's exchange rate** for unshipped, **shipment's exchange rate** for shipped scenarios |
| Concurrent edits | 🟡 Medium | Use `FOR UPDATE` locks on invoice and supplier_balances rows |
| Discount becomes > new items_total | 🟢 Low | Add validation: `discount ≤ new_items_total` |
| Breaking existing invoice edit flow | 🟡 Medium | The new balance adjustment runs **after** the normal update, so existing logic is preserved |
| Shipment payment status tracking | 🟡 Medium | Need a reliable way to determine if **all** invoices in a shipment are fully paid |

---

## Conclusion

> [!IMPORTANT]
> **All 16 scenarios are feasible** with the existing data model. The key new component needed is a single backend handler (`update_with_balance_adjustment`) that wraps the current invoice update with balance-adjustment logic. The hardest scenarios involve the "excess credit-back" (scenarios 4, 6, 11, 13, 15) which require inserting new `supplier_transactions` entries and updating `supplier_balances`.
>
> **Estimated effort**: Medium-Large (~4-6 hours of implementation + testing across all 16 scenarios)
