# Deployment Guide: Foreign Key Relationships Update

## Overview
This guide provides step-by-step instructions for deploying the foreign key relationship updates (Task 4.3) to the production environment.

## Prerequisites
- Access to the production server (cPanel or SSH)
- Database backup completed
- Django environment set up on production

## Deployment Steps

### Step 1: Backup Current State
Before making any changes, create a backup:

```bash
# Backup database
mysqldump -u root -p acbptxvs_branch_system > backup_before_fk_update_$(date +%Y%m%d_%H%M%S).sql

# Backup Django files (if not already done)
tar -czf django_backup_$(date +%Y%m%d_%H%M%S).tar.gz /path/to/branchsystem
```

### Step 2: Upload Modified Files

Upload the following files to the production server:

1. **loans/models.py** - Contains the 5 new models
2. **loans/migrations/0025_add_grazuri_foreign_key_models.py** - Migration file
3. **loans/admin.py** - Updated admin registrations

**Via cPanel File Manager**:
1. Navigate to your Django project directory
2. Upload each file to its respective location
3. Overwrite existing files when prompted

**Via SSH/SCP**:
```bash
scp loans/models.py user@server:/path/to/branchsystem/loans/
scp loans/migrations/0025_add_grazuri_foreign_key_models.py user@server:/path/to/branchsystem/loans/migrations/
scp loans/admin.py user@server:/path/to/branchsystem/loans/
```

### Step 3: Apply Database Migration

**Option A - Via SSH**:
```bash
cd /path/to/branchsystem
source venv/bin/activate  # Activate virtual environment
python manage.py migrate loans
```

**Option B - Via Django Shell in cPanel**:
```python
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.core.management import call_command
call_command('migrate', 'loans')
```

**Option C - Via phpMyAdmin (Manual SQL)**:
If Django migration fails, you can create the tables manually using the SQL below.

### Step 4: Verify Migration

After applying the migration, verify that the tables were created:

```sql
-- Check that all tables exist
SHOW TABLES LIKE 'bureau_records';
SHOW TABLES LIKE 'loan_disbursements';
SHOW TABLES LIKE 'loan_fees';
SHOW TABLES LIKE 'loan_guarantors';
SHOW TABLES LIKE 'loan_statuses';

-- Check table structures
DESCRIBE bureau_records;
DESCRIBE loan_disbursements;
DESCRIBE loan_fees;
DESCRIBE loan_guarantors;
DESCRIBE loan_statuses;

-- Verify foreign key constraints
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'acbptxvs_branch_system'
AND TABLE_NAME IN ('bureau_records', 'loan_disbursements', 'loan_fees', 'loan_guarantors', 'loan_statuses')
AND REFERENCED_TABLE_NAME IS NOT NULL;
```

### Step 5: Restart Application

**Via cPanel**:
1. Go to "Setup Python App"
2. Find your application
3. Click "Restart"

**Via SSH**:
```bash
touch /path/to/branchsystem/tmp/restart.txt
```

**Via Passenger**:
```bash
touch /path/to/branchsystem/passenger_wsgi.py
```

### Step 6: Verify Admin Interface

1. Log in to Django admin: `https://yourdomain.com/admin/`
2. Check that the following sections appear:
   - Bureau Records
   - Loan Disbursements
   - Loan Fees
   - Loan Guarantors
   - Loan Statuses
3. Try creating a test record in each section to verify functionality

### Step 7: Test Foreign Key Relationships

Use Django shell to test the relationships:

```python
from loans.models import Loan, BureauRecord, LoanDisbursement
from users.models import CustomUser

# Get test data
loan = Loan.objects.first()
borrower = loan.borrower if loan else CustomUser.objects.first()

# Test creating records with foreign keys
if loan and borrower:
    # Create bureau record
    bureau = BureauRecord.objects.create(
        borrower=borrower,
        baccount=loan,
        bureau_name="Test Bureau",
        credit_score=700
    )
    print(f"Created bureau record: {bureau}")
    
    # Test reverse relationships
    print(f"Loan bureau records: {loan.bureau_records.count()}")
    print(f"Borrower bureau records: {borrower.bureau_records.count()}")
    
    # Clean up test data
    bureau.delete()
```

## Manual SQL Creation (Fallback)

If the Django migration fails, you can create the tables manually using this SQL:

```sql
-- Create bureau_records table
CREATE TABLE IF NOT EXISTS `bureau_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `borrower` char(32) NOT NULL,
  `baccount` char(32) NOT NULL,
  `bureau_name` varchar(100) DEFAULT NULL,
  `record_date` datetime(6) DEFAULT NULL,
  `credit_score` int(11) DEFAULT NULL,
  `report_data` longtext DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `bureau_records_borrower_idx` (`borrower`),
  KEY `bureau_records_baccount_idx` (`baccount`),
  CONSTRAINT `FK1_Borrower` FOREIGN KEY (`borrower`) REFERENCES `users_customuser` (`id`),
  CONSTRAINT `FK2_BorrowerLoan` FOREIGN KEY (`baccount`) REFERENCES `loans` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create loan_disbursements table
CREATE TABLE IF NOT EXISTS `loan_disbursements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loan` char(32) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `disbursement_date` datetime(6) NOT NULL,
  `disbursement_method` varchar(50) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `notes` longtext DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `loan_disbursements_loan_idx` (`loan`),
  CONSTRAINT `FK_loan_disbursements_loan_info` FOREIGN KEY (`loan`) REFERENCES `loans` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create loan_fees table
CREATE TABLE IF NOT EXISTS `loan_fees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loan` char(32) NOT NULL,
  `fee_type` varchar(50) NOT NULL,
  `fee_name` varchar(100) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `is_paid` tinyint(1) NOT NULL DEFAULT 0,
  `paid_date` datetime(6) DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `loan_fees_loan_idx` (`loan`),
  CONSTRAINT `Loan` FOREIGN KEY (`loan`) REFERENCES `loans` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create loan_guarantors table
CREATE TABLE IF NOT EXISTS `loan_guarantors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `borrower` char(32) NOT NULL,
  `guarantor_name` varchar(200) NOT NULL,
  `guarantor_phone` varchar(17) NOT NULL,
  `guarantor_email` varchar(254) DEFAULT NULL,
  `guarantor_id_number` varchar(50) DEFAULT NULL,
  `relationship` varchar(100) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `loan_guarantors_borrower_idx` (`borrower`),
  CONSTRAINT `borrower` FOREIGN KEY (`borrower`) REFERENCES `users_customuser` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create loan_statuses table
CREATE TABLE IF NOT EXISTS `loan_statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loan` char(32) NOT NULL,
  `status` varchar(50) NOT NULL,
  `status_date` datetime(6) NOT NULL,
  `changed_by_id` char(32) DEFAULT NULL,
  `notes` longtext DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `loan_statuses_loan_idx` (`loan`),
  KEY `loan_statuses_changed_by_idx` (`changed_by_id`),
  CONSTRAINT `loanStatus` FOREIGN KEY (`loan`) REFERENCES `loans` (`id`),
  CONSTRAINT `loan_statuses_changed_by_fk` FOREIGN KEY (`changed_by_id`) REFERENCES `users_customuser` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Record migration in django_migrations table
INSERT INTO django_migrations (app, name, applied) 
VALUES ('loans', '0025_add_grazuri_foreign_key_models', NOW());
```

## Troubleshooting

### Issue: Migration fails with "table already exists"
**Solution**: The tables may have been created manually. Check if they exist and match the expected structure. If they do, you can fake the migration:
```bash
python manage.py migrate loans 0025 --fake
```

### Issue: Foreign key constraint fails
**Solution**: Ensure that the referenced tables (`users_customuser` and `loans`) exist and have the correct structure. Check that the `id` columns are of type `char(32)` (UUID).

### Issue: Models don't appear in admin
**Solution**: 
1. Verify that `loans/admin.py` was uploaded correctly
2. Clear Django cache: `python manage.py clear_cache`
3. Restart the application
4. Check for Python syntax errors in admin.py

### Issue: "No module named 'loans.models'"
**Solution**: 
1. Verify that `loans/models.py` was uploaded correctly
2. Check file permissions (should be readable)
3. Restart the application
4. Check for Python syntax errors in models.py

## Rollback Procedure

If you need to rollback the changes:

### Step 1: Restore Database
```bash
mysql -u root -p acbptxvs_branch_system < backup_before_fk_update_YYYYMMDD_HHMMSS.sql
```

### Step 2: Revert Django Files
```bash
# Restore from backup
tar -xzf django_backup_YYYYMMDD_HHMMSS.tar.gz
```

### Step 3: Rollback Migration
```bash
python manage.py migrate loans 0024
```

### Step 4: Restart Application
Follow Step 5 from the deployment steps above.

## Post-Deployment Checklist

- [ ] Database backup created
- [ ] Files uploaded to production
- [ ] Migration applied successfully
- [ ] All 5 tables created in database
- [ ] Foreign key constraints verified
- [ ] Application restarted
- [ ] Admin interface accessible
- [ ] New models visible in admin
- [ ] Test records can be created
- [ ] Foreign key relationships work correctly
- [ ] No errors in application logs

## Support

If you encounter issues during deployment:

1. Check application logs for errors
2. Verify database connection settings
3. Ensure all files were uploaded correctly
4. Review the troubleshooting section above
5. Refer to `FOREIGN_KEY_RELATIONSHIPS_UPDATE.md` for technical details

## Summary

This deployment adds 5 new models to support Grazuri schema compatibility:
- BureauRecord
- LoanDisbursement
- LoanFee
- LoanGuarantor
- LoanStatus

All models include proper foreign key relationships with CASCADE and SET_NULL behaviors, ensuring referential integrity is maintained throughout the system.