# Production Database Fix - Critical Issues Resolution

## Issues Identified

Based on the production errors, the following critical database issues need immediate attention:

### 1. Missing `penalty_charges` Table
- **Error**: `(1146, "Table 'acbptxvs_branch_system.penalty_charges' doesn't exist")`
- **Impact**: Loan detail pages fail when trying to calculate outstanding amounts with penalties
- **Cause**: Migration `0002_add_penalty_charges.py` was not applied in production

### 2. Missing `loan_app_id` Column in `utils_notification` Table  
- **Error**: `(1054, "Unknown column 'loan_app_id' in 'field list'")`
- **Impact**: Notification system fails when trying to link notifications to loan applications
- **Cause**: Migration for adding `loan_app_id` foreign key was not applied

### 3. Missing Registration Fee Columns
- **Potential Issue**: `registration_fee_amount` in `loan_applications` and `registration_fee` in `loans` tables
- **Impact**: Registration fee calculations may fail

## Solution

Two scripts have been created to address these issues:

### 1. Test Script: `test_database_fix.py`
- **Purpose**: Safely check database structure without making changes
- **Usage**: Run first to identify what needs to be fixed
- **Safe**: Read-only operations, no database modifications

### 2. Fix Script: `fix_production_database_complete.py`
- **Purpose**: Apply all necessary database structure fixes
- **Features**: 
  - Atomic transactions (all-or-nothing)
  - Comprehensive logging
  - Verification of fixes
  - Safe rollback on errors

## Deployment Steps

### Step 1: Backup Database
```bash
# Create a full database backup before applying fixes
mysqldump -u username -p database_name > backup_before_fix_$(date +%Y%m%d_%H%M%S).sql
```

### Step 2: Test Current State
```bash
# Run the test script to see what needs fixing
python test_database_fix.py
```

### Step 3: Apply Fixes
```bash
# Apply the database fixes
python fix_production_database_complete.py
```

### Step 4: Verify Application
- Test the loan detail page that was failing: `/loans/b178829b-74ca-43a5-931d-a45bf0b9f062/`
- Check notification system functionality
- Verify no new errors in Django logs

## What the Fix Script Does

### 1. Creates `penalty_charges` Table
```sql
CREATE TABLE IF NOT EXISTS `penalty_charges` (
    `id` char(32) NOT NULL,
    `amount` decimal(12,2) NOT NULL,
    `penalty_rate` decimal(5,2) NOT NULL,
    `days_overdue` int unsigned NOT NULL,
    `outstanding_amount` decimal(12,2) NOT NULL,
    `applied_date` datetime(6) NOT NULL,
    `loan_id` char(32) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `penalty_charges_loan_id_idx` (`loan_id`),
    CONSTRAINT FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`)
);
```

### 2. Adds `loan_app_id` Column to `utils_notification`
```sql
ALTER TABLE `utils_notification` 
ADD COLUMN `loan_app_id` char(32) NULL,
ADD KEY `utils_notification_loan_app_id_idx` (`loan_app_id`),
ADD CONSTRAINT FOREIGN KEY (`loan_app_id`) REFERENCES `loan_applications` (`id`);
```

### 3. Adds Missing Registration Fee Columns
- `registration_fee_amount` to `loan_applications` table
- `registration_fee` to `loans` table

### 4. Updates Existing Data
- Sets default values for new columns in existing records

## Safety Features

- **Atomic Transactions**: All changes are wrapped in a transaction that rolls back on any error
- **Existence Checks**: Only creates/adds structures that don't already exist
- **Comprehensive Logging**: All operations are logged to `production_database_fix.log`
- **Verification**: Script verifies all fixes were applied correctly before completing

## Post-Fix Verification

After running the fix script, verify:

1. **No Database Errors**: Check Django application logs for database-related errors
2. **Loan Details Work**: Test the previously failing loan detail page
3. **Notifications Work**: Test notification creation and display
4. **Registration Fees**: Test loan applications with registration fees

## Rollback Plan

If issues occur after applying the fix:

1. **Restore from Backup**:
   ```bash
   mysql -u username -p database_name < backup_before_fix_YYYYMMDD_HHMMSS.sql
   ```

2. **Check Migration State**:
   ```bash
   python manage.py showmigrations
   ```

3. **Re-run Specific Migrations** if needed:
   ```bash
   python manage.py migrate loans 0002_add_penalty_charges
   python manage.py migrate utils --fake-initial
   ```

## Contact Information

If you encounter any issues during deployment:
- Check the log file: `production_database_fix.log`
- Verify database connection settings in `settings.py`
- Ensure proper database permissions for DDL operations

## Files Created

1. `test_database_fix.py` - Test script (safe to run)
2. `fix_production_database_complete.py` - Production fix script
3. `PRODUCTION_DATABASE_FIX_README.md` - This documentation

---

**IMPORTANT**: Always run the test script first and create a database backup before applying the fix script in production.