# Database Schema Migration Tools

This directory contains tools for managing and verifying database schema migrations between the old and new database structures.

## Quick Start

### 1. Check Current Schema Health
```bash
python check_schema_health.py
```
This performs a quick health check to ensure your database schema is in good shape.

### 2. Run Full Verification
```bash
python verify_schema_migration.py
```
This runs a comprehensive verification of all tables, columns, foreign keys, and model definitions.

### 3. Fix Any Issues
```bash
python fix_schema_migration_comprehensive.py
```
This automatically fixes any missing tables, columns, or indexes identified by the verification script.

## Available Scripts

### `check_schema_health.py`
**Purpose:** Quick health check for daily/regular use

**What it checks:**
- Critical tables exist
- Foreign key columns exist
- Data integrity (no orphaned records)
- Critical indexes exist
- Database statistics

**When to use:**
- Daily health checks
- Before deployments
- After database updates
- When troubleshooting issues

**Output:** Pass/Fail with specific issues listed

---

### `verify_schema_migration.py`
**Purpose:** Comprehensive schema verification

**What it checks:**
- All core tables exist
- All foreign key columns exist
- Users table structure
- Loans table structure
- Python code references to database fields
- Django model definitions match database

**When to use:**
- After schema changes
- Before major releases
- When investigating schema-related bugs
- After running migration scripts

**Output:** Detailed report with 100+ verification checks

---

### `fix_schema_migration_comprehensive.py`
**Purpose:** Automated schema migration and fixes

**What it does:**
- Creates missing tables (loans_repayment, receipts, loan_statements, offer_letters)
- Adds missing columns to loans table
- Adds missing columns to loan_applications table
- Updates outstanding_balance for existing loans
- Creates database indexes for performance

**When to use:**
- When verification script reports issues
- After pulling new code with schema changes
- When setting up a new environment
- When migrating from old to new schema

**Safety:** All changes are wrapped in a database transaction

---

## Migration History

### May 8, 2026 - Comprehensive Schema Migration

**Changes Made:**

1. **Created Missing Tables:**
   - `loans_repayment` - Tracks loan repayments
   - `receipts` - Stores payment receipts
   - `loan_statements` - Stores generated loan statements
   - `offer_letters` - Stores loan offer letters

2. **Added Columns to `loans` Table:**
   - `loan_officer_id` - Loan officer reference
   - `approved_by_id` - Approver reference
   - `disbursed_by_id` - Disburser reference
   - `created_by_id` - Creator reference
   - `updated_by_id` - Last updater reference
   - `outstanding_balance` - Remaining balance
   - `registration_fee` - Registration fee amount

3. **Added Columns to `loan_applications` Table:**
   - `loan_officer_id` - Assigned loan officer
   - `approved_by_id` - Approver reference
   - `disbursed_by_id` - Disburser reference

4. **Updated Django Models:**
   - Added ForeignKey fields to Loan model
   - Added ForeignKey fields to LoanApplication model
   - Added outstanding_balance and registration_fee fields

5. **Created Database Indexes:**
   - Indexes on all foreign key columns for performance

**Verification Status:** ✅ All 109 checks passed

See `SCHEMA_MIGRATION_SUMMARY.md` for complete details.

---

## Common Issues and Solutions

### Issue: "Table 'X' is missing"
**Solution:** Run `python fix_schema_migration_comprehensive.py`

### Issue: "Column 'X.Y' is missing"
**Solution:** Run `python fix_schema_migration_comprehensive.py`

### Issue: "Orphaned records detected"
**Solution:** 
1. Identify the orphaned records
2. Either delete them or fix the references manually
3. Run health check again

### Issue: "Django model doesn't match database"
**Solution:**
1. Check if database columns exist: `python verify_schema_migration.py`
2. If columns exist, update Django models in `loans/models.py`
3. If columns don't exist, run `python fix_schema_migration_comprehensive.py`

---

## Best Practices

### Before Making Schema Changes

1. **Backup the database:**
   ```bash
   mysqldump -u username -p database_name > backup_$(date +%Y%m%d).sql
   ```

2. **Run health check:**
   ```bash
   python check_schema_health.py
   ```

3. **Document the changes** in this README

### After Making Schema Changes

1. **Update Django models** to match database schema

2. **Run verification:**
   ```bash
   python verify_schema_migration.py
   ```

3. **Fix any issues:**
   ```bash
   python fix_schema_migration_comprehensive.py
   ```

4. **Run health check:**
   ```bash
   python check_schema_health.py
   ```

5. **Test the application** thoroughly

6. **Update documentation**

### Regular Maintenance

- Run `check_schema_health.py` daily or before deployments
- Run `verify_schema_migration.py` weekly or after any schema changes
- Keep the template SQL file updated: `templates/users/acbptxvs_branch_system.sql`
- Document all schema changes in this README

---

## Database Schema Overview

### Core Tables

1. **users** - User accounts (borrowers, staff, admins)
2. **branches** - Branch locations
3. **loans** - Active loans
4. **loan_applications** - Loan applications
5. **loan_products** - Loan product definitions
6. **loans_repayment** - Repayment records
7. **receipts** - Payment receipts
8. **loan_statements** - Generated statements
9. **offer_letters** - Loan offer letters
10. **rollover_requests** - Loan rollover requests
11. **customer_requests** - Customer service requests
12. **mpesa_transactions** - M-Pesa payment transactions
13. **expenses** - Business expenses

### Key Relationships

```
users (borrowers)
  ├─> loan_applications
  │     └─> loans
  │           ├─> loans_repayment
  │           │     └─> receipts
  │           ├─> loan_statements
  │           └─> rollover_requests
  └─> customer_requests

users (staff)
  ├─> loans (as loan_officer, approved_by, disbursed_by)
  ├─> loan_applications (as loan_officer, approved_by, reviewed_by)
  └─> expenses (as staff, approved_by)

branches
  └─> users (branch_id)
        └─> expenses (branch_id)

loan_products
  └─> loan_applications (loan_product_id)
```

---

## Troubleshooting

### Script Won't Run

**Error:** `ModuleNotFoundError: No module named 'django'`

**Solution:** Ensure Django is installed and virtual environment is activated:
```bash
pip install django
```

### Database Connection Error

**Error:** `Can't connect to MySQL server`

**Solution:** Check database settings in `branch_system/settings.py`:
- Database name
- Username
- Password
- Host
- Port

### Permission Denied

**Error:** `Access denied for user`

**Solution:** Ensure database user has proper permissions:
```sql
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
FLUSH PRIVILEGES;
```

---

## Support

For issues or questions:
1. Check this README first
2. Review `SCHEMA_MIGRATION_SUMMARY.md` for detailed migration info
3. Run verification scripts to identify specific issues
4. Contact the development team

---

## Files in This Directory

- `check_schema_health.py` - Quick health check script
- `verify_schema_migration.py` - Comprehensive verification script
- `fix_schema_migration_comprehensive.py` - Automated migration script
- `SCHEMA_MIGRATION_SUMMARY.md` - Detailed migration documentation
- `SCHEMA_MIGRATION_README.md` - This file
- `verify_fix.py` - Legacy verification script (deprecated)

---

**Last Updated:** May 8, 2026
**Migration Status:** ✅ Complete and Verified