# Mwamba Loan Calculation Issue - Root Cause and Fix

## Problem Summary

For Mwamba loan LOAN-000131 (and potentially other Mwamba loans):
- **Principal Amount**: KES 20,000.00 ✓ (Correct)
- **Interest Amount**: KES 6,000.00 ✓ (Correct - 30% for 90 days at 10% per month)
- **Processing Fee**: KES 1,000.00 ✓ (Correct - 5% of principal)
- **Total Amount (Stored)**: KES 22,400.00 ✗ (WRONG!)
- **Total Amount (Correct)**: KES 27,000.00 (20,000 + 6,000 + 1,000)

This causes:
- **Outstanding Balance (Displayed)**: KES 19,200.00 ✗ (WRONG!)
- **Outstanding Balance (Correct)**: KES 23,800.00 (27,000 - 3,200 paid)

## Root Cause

The `total_amount` field in the `loans` table is **incorrectly stored as 22,400** instead of 27,000. This appears to be a data entry issue where:
- The interest amount (6,000) was somehow not fully included in the total
- The calculation stored: 20,000 + 1,400 + 1,000 = 22,400 (missing 4,600 of interest)

The code logic in `loans/models.py` is **correct**:
```python
# In LoanApplication.approve() method (line 413)
calculated_total = self.requested_amount + self.interest_amount + self.processing_fee_amount
```

However, the database record has an incorrect value, possibly due to:
1. Manual database edit
2. A bug that existed when this loan was created (now fixed)
3. Data migration issue

## Impact

This affects:
1. **Outstanding Balance Calculation** - Shows KES 4,600 less than actual
2. **Principal Amount Display** - May show incorrect breakdown
3. **Payment Progress** - Shows incorrect percentage
4. **Reports** - All financial reports will be inaccurate

## Solution

### Option 1: SQL Fix (Immediate - Recommended)

Run this SQL to fix LOAN-000131 and check all Mwamba loans:

```sql
-- Fix LOAN-000131 specifically
UPDATE loans 
SET total_amount = principal_amount + interest_amount + processing_fee
WHERE loan_number = 'LOAN-000131';

-- Also fix the application record
UPDATE loan_applications la
INNER JOIN loans l ON l.application_id = la.id
SET la.total_amount = l.principal_amount + l.interest_amount + l.processing_fee
WHERE l.loan_number = 'LOAN-000131';

-- Check all loans for similar issues
SELECT 
    l.loan_number,
    CONCAT(u.first_name, ' ', u.last_name) as borrower,
    lp.name as product,
    l.principal_amount,
    l.interest_amount,
    l.processing_fee,
    l.total_amount as stored_total,
    (l.principal_amount + l.interest_amount + l.processing_fee) as correct_total,
    (l.principal_amount + l.interest_amount + l.processing_fee - l.total_amount) as difference
FROM loans l
INNER JOIN loan_applications la ON l.application_id = la.id
INNER JOIN loan_products lp ON la.loan_product_id = lp.id
INNER JOIN users u ON l.borrower_id = u.id
WHERE ABS(l.total_amount - (l.principal_amount + l.interest_amount + l.processing_fee)) > 0.01
ORDER BY l.created_at DESC;

-- Fix all loans with incorrect total_amount
UPDATE loans 
SET total_amount = principal_amount + interest_amount + processing_fee
WHERE ABS(total_amount - (principal_amount + interest_amount + processing_fee)) > 0.01;

-- Fix corresponding applications
UPDATE loan_applications la
INNER JOIN loans l ON l.application_id = la.id
SET la.total_amount = l.principal_amount + l.interest_amount + l.processing_fee
WHERE ABS(la.total_amount - (l.principal_amount + l.interest_amount + l.processing_fee)) > 0.01;
```

### Option 2: Python Script (For Production Server)

Use the provided `diagnose_and_fix_mwamba_loan_131.py` script:

```bash
python diagnose_and_fix_mwamba_loan_131.py
```

This script will:
1. Diagnose LOAN-000131 specifically
2. Show the mismatch
3. Ask for confirmation before fixing
4. Check all Mwamba loans for similar issues
5. Fix all affected loans

## Verification

After applying the fix, verify:

```sql
-- Verify LOAN-000131
SELECT 
    loan_number,
    principal_amount,
    interest_amount,
    processing_fee,
    total_amount,
    (principal_amount + interest_amount + processing_fee) as calculated_total,
    (SELECT SUM(amount) FROM repayments WHERE loan_id = loans.id) as amount_paid,
    (total_amount - (SELECT COALESCE(SUM(amount), 0) FROM repayments WHERE loan_id = loans.id)) as outstanding
FROM loans
WHERE loan_number = 'LOAN-000131';
```

Expected result:
- `total_amount`: 27000.00
- `calculated_total`: 27000.00
- `amount_paid`: 3200.00
- `outstanding`: 23800.00

## Prevention

The code already has the correct logic. To prevent future issues:

1. **Add Database Constraint** (Optional):
```sql
-- Add a check constraint to ensure total_amount is always correct
-- Note: This might cause issues with existing bad data, so fix data first
ALTER TABLE loans ADD CONSTRAINT chk_total_amount 
CHECK (ABS(total_amount - (principal_amount + interest_amount + processing_fee)) < 0.01);
```

2. **Add Model Validation** - Already exists in the code:
```python
@property
def calculated_total_amount(self):
    """Calculate total amount using current system rates"""
    return self.principal_amount + self.get_display_interest_amount() + self.get_display_processing_fee_amount()
```

3. **Use Calculated Properties** - The code already uses `loan.outstanding_amount` property which calculates correctly, but it relies on the stored `total_amount` field.

## Code Analysis

The calculation logic is **correct** in the codebase:

### LoanApplication.save() (lines 360-375)
```python
if not self.interest_amount:
    months = max(1, self.requested_duration / 30)
    self.interest_amount = self.loan_product.calculate_interest(
        self.requested_amount, months
    )

if not self.processing_fee_amount:
    months = max(1, self.requested_duration / 30)
    self.processing_fee_amount = self.loan_product.calculate_processing_fee(
        self.requested_amount, months
    )

if not self.total_amount:
    self.total_amount = self.requested_amount + self.interest_amount + self.processing_fee_amount
```

### LoanApplication.approve() (lines 413-415)
```python
# Always calculate total_amount to ensure it's correct
calculated_total = self.requested_amount + self.interest_amount + self.processing_fee_amount
loan = Loan.objects.create(..., total_amount=calculated_total, ...)
```

### LoanCalculationService.calculate_outstanding_amount() (lines 60-103)
```python
outstanding = total_amount + total_penalties - amount_paid
return max(outstanding, Decimal('0.00'))
```

All logic is correct. The issue is **bad data in the database**.

## Recommendation

**Run the SQL fix immediately** to correct LOAN-000131 and any other affected loans. This is a data integrity issue, not a code bug.