# Foreign Key Relationship Analysis

## Overview
This document analyzes the foreign key relationships in the target Grazuri schema and maps them to Django model implementations.

## Target Schema Foreign Keys (from xygbfpsg_loans.sql)

### 1. bureau_records Table
```sql
ALTER TABLE `bureau_records`
  ADD CONSTRAINT `FK1_Borrower` FOREIGN KEY (`borrower`) REFERENCES `borrowers` (`id`),
  ADD CONSTRAINT `FK2_BorrowerLoan` FOREIGN KEY (`baccount`) REFERENCES `loan_info` (`baccount`);
```

**Django Implementation:**
- Table: `bureau_records` (needs to be created)
- Foreign Keys:
  - `borrower` → `borrowers.id` (maps to Django `CustomUser` model)
  - `baccount` → `loan_info.baccount` (maps to Django `Loan` model)

### 2. loan_disbursements Table
```sql
ALTER TABLE `loan_disbursements`
  ADD CONSTRAINT `FK_loan_disbursements_loan_info` FOREIGN KEY (`loan`) REFERENCES `loan_info` (`id`);
```

**Django Implementation:**
- Table: `loan_disbursements` (needs to be created)
- Foreign Keys:
  - `loan` → `loan_info.id` (maps to Django `Loan` model)

### 3. loan_fees Table
```sql
ALTER TABLE `loan_fees`
  ADD CONSTRAINT `Loan` FOREIGN KEY (`loan`) REFERENCES `loan_info` (`id`);
```

**Django Implementation:**
- Table: `loan_fees` (needs to be created)
- Foreign Keys:
  - `loan` → `loan_info.id` (maps to Django `Loan` model)

### 4. loan_guarantors Table
```sql
ALTER TABLE `loan_guarantors`
  ADD CONSTRAINT `borrower` FOREIGN KEY (`borrower`) REFERENCES `borrowers` (`id`);
```

**Django Implementation:**
- Table: `loan_guarantors` (needs to be created)
- Foreign Keys:
  - `borrower` → `borrowers.id` (maps to Django `CustomUser` model)

### 5. loan_statuses Table
```sql
ALTER TABLE `loan_statuses`
  ADD CONSTRAINT `loanStatus` FOREIGN KEY (`loan`) REFERENCES `loan_info` (`id`);
```

**Django Implementation:**
- Table: `loan_statuses` (needs to be created)
- Foreign Keys:
  - `loan` → `loan_info.id` (maps to Django `Loan` model)

## Current Django Models Foreign Key Relationships

### Existing Foreign Keys in Django System

#### users.CustomUser Model
- `branch` → `Branch` (SET_NULL)
- `accessible_branches` → `Branch` (ManyToMany)
- `portfolio_manager` → `CustomUser` (self-reference, SET_NULL)
- `verified_by` → `CustomUser` (self-reference, SET_NULL)
- `approved_by` → `CustomUser` (self-reference, SET_NULL)
- `rejected_by` → `CustomUser` (self-reference, SET_NULL)

#### loans.LoanApplication Model
- `borrower` → `CustomUser` (CASCADE)
- `loan_product` → `LoanProduct` (CASCADE)
- `reviewed_by` → `CustomUser` (SET_NULL)

#### loans.Loan Model
- `application` → `LoanApplication` (CASCADE, OneToOne)
- `borrower` → `CustomUser` (CASCADE)
- `deleted_by` → `CustomUser` (SET_NULL)
- `original_loan` → `Loan` (self-reference, SET_NULL)

#### loans.Repayment Model
- `loan` → `Loan` (CASCADE)

## Schema Mapping

### Grazuri → Django Table Mapping
- `borrowers` → `users_customuser` (Django CustomUser model)
- `loan_info` → `loans` (Django Loan model)
- `loan_products` → `loans_loanproduct` (Django LoanProduct model)

## Required Actions

### 1. Create New Django Models

The following models need to be created in the appropriate Django app:

#### BureauRecord Model (in loans app)
```python
class BureauRecord(models.Model):
    """Credit bureau records for borrowers"""
    borrower = models.ForeignKey(
        'users.CustomUser',
        on_delete=models.CASCADE,
        related_name='bureau_records'
    )
    baccount = models.ForeignKey(
        'Loan',
        on_delete=models.CASCADE,
        related_name='bureau_records',
        db_column='baccount'
    )
    # Additional fields from target schema...
```

#### LoanDisbursement Model (in loans app)
```python
class LoanDisbursement(models.Model):
    """Loan disbursement tracking"""
    loan = models.ForeignKey(
        'Loan',
        on_delete=models.CASCADE,
        related_name='disbursements'
    )
    # Additional fields from target schema...
```

#### LoanFee Model (in loans app)
```python
class LoanFee(models.Model):
    """Loan fees tracking"""
    loan = models.ForeignKey(
        'Loan',
        on_delete=models.CASCADE,
        related_name='fees'
    )
    # Additional fields from target schema...
```

#### LoanGuarantor Model (in loans app)
```python
class LoanGuarantor(models.Model):
    """Loan guarantors"""
    borrower = models.ForeignKey(
        'users.CustomUser',
        on_delete=models.CASCADE,
        related_name='guarantor_records'
    )
    # Additional fields from target schema...
```

#### LoanStatus Model (in loans app)
```python
class LoanStatus(models.Model):
    """Loan status history tracking"""
    loan = models.ForeignKey(
        'Loan',
        on_delete=models.CASCADE,
        related_name='status_history'
    )
    # Additional fields from target schema...
```

### 2. Update Existing Models

#### Existing Foreign Keys Review
All existing foreign key relationships in the Django system are properly configured with:
- Appropriate `on_delete` behavior (CASCADE, SET_NULL)
- Proper `related_name` attributes for reverse relationships
- Correct references to related models

**No changes needed to existing foreign keys** - they are already properly configured.

### 3. Migration Strategy

1. **Create new models** with foreign key relationships
2. **Generate Django migrations**: `python manage.py makemigrations`
3. **Review migrations** to ensure foreign key constraints are correct
4. **Apply migrations**: `python manage.py migrate`
5. **Verify foreign keys** in database using MySQL queries

## Foreign Key Constraints Configuration

### Django ForeignKey Parameters Used

- `on_delete=models.CASCADE`: Delete related records when parent is deleted
- `on_delete=models.SET_NULL`: Set to NULL when parent is deleted (requires null=True)
- `related_name`: Name for reverse relationship from related model
- `db_column`: Specify exact database column name (when different from field name)

### Database-Level Constraints

Django will create the following database constraints:
- Foreign key indexes for performance
- Referential integrity constraints
- Cascade delete behavior (where specified)

## Verification Steps

After implementing foreign key relationships:

1. **Check Django models**: Verify all ForeignKey fields are defined
2. **Generate migrations**: Ensure migrations include foreign key constraints
3. **Apply migrations**: Run migrations in development environment
4. **Verify in database**: 
   ```sql
   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 REFERENCED_TABLE_NAME IS NOT NULL;
   ```
5. **Test relationships**: Verify Django ORM can traverse relationships

## Notes

- All new models should be created in the `loans` app since they are loan-related
- Foreign keys use `CASCADE` delete for dependent records (disbursements, fees, statuses)
- Foreign keys use `CASCADE` for borrower relationships to maintain data integrity
- The `db_column` parameter is used where Grazuri schema uses different column names
- All foreign keys include `related_name` for easy reverse lookups

## Status

- ✅ Analysis complete
- ⏳ Models to be created
- ⏳ Migrations to be generated
- ⏳ Database verification pending