# Complete Grazuri Data Import Guide

## Overview

This guide will help you import **EVERYTHING** from your Grazuri database to Haven Grazuri system:
- ✅ Users (staff and admins)
- ✅ Borrowers (clients)
- ✅ Loans (all loan records)
- ✅ Payments (repayment history)
- ✅ Branches (office locations)
- ✅ All related data

---

## Quick Start (One Command)

```powershell
python run_complete_import.py
```

This will:
1. Import the SQL file (`xygbfpsg_loans_populated.sql`)
2. Migrate all data to Haven Grazuri system
3. Show detailed import summary

---

## Manual Process (Step by Step)

### Step 1: Import SQL File

```powershell
Get-Content "c:\Users\Teamjoint company\Desktop\branchsystem\xygbfpsg_loans_populated.sql" | mysql -u root -ppassword acbptxvs_branch_system
```

**What this does:**
- Imports all Grazuri tables into your database
- Populates tables with all data
- May show "table exists" warnings (normal)

### Step 2: Import All Data

```powershell
python import_complete_grazuri_data.py
```

**What this does:**
- Reads all data from Grazuri tables
- Migrates to Haven Grazuri format
- Shows progress for each category
- Displays detailed summary

---

## What Gets Imported

### 1. Branches
- **From:** `branches` table
- **To:** Haven Grazuri `users_branch` table
- **Fields:** Name, code, location, status
- **Mapping:** Direct mapping with validation

### 2. Users (Staff/Admins)
- **From:** `user` table
- **To:** Haven Grazuri `users` table
- **Fields:** Name, username, email, phone, role, password
- **Transformations:**
  - Passwords: Base64 decoded → Django secure hash
  - Phone: Formatted to +254...
  - Roles: Mapped to Haven Grazuri roles
  - Permissions: Configured based on role

### 3. Borrowers (Clients)
- **From:** `borrowers` table
- **To:** Haven Grazuri `borrowers` table
- **Fields:** Name, ID number, phone, email, employment, salary
- **Transformations:**
  - Account numbers preserved
  - Contact info formatted
  - Employment details migrated

### 4. Loans
- **From:** `loan_info` table
- **To:** Haven Grazuri `loans` table
- **Fields:** Amount, interest, duration, status, dates
- **Transformations:**
  - Loan numbers preserved
  - Status mapped correctly
  - Dates converted
  - Balances calculated

### 5. Payments
- **From:** `payments` table
- **To:** Haven Grazuri `repayments` table
- **Fields:** Amount, date, method, reference
- **Transformations:**
  - Linked to correct loans
  - Payment methods mapped
  - References preserved

---

## Data Transformations

### Role Mapping
| Grazuri Role | Haven Grazuri Role |
|--------------|-------------------|
| Admin | admin |
| Manager | team_leader |
| Credit Controller | loan_officer |
| Loan Officer | loan_officer |
| Secretary | secretary |
| Auditor | auditor |
| (other) | borrower |

### Status Mapping
| Grazuri Status | Haven Grazuri Status |
|----------------|---------------------|
| approved | approved |
| disbursed | disbursed |
| active | active |
| closed | closed |
| rejected | rejected |
| pending | pending |

### Phone Number Formatting
- **Input:** `0711925530`, `711925530`, `254711925530`
- **Output:** `+254711925530`

### Password Handling
- **Input:** Base64 encoded (e.g., `MTIzMTIz`)
- **Process:** Decode to plain text
- **Output:** Django PBKDF2 secure hash

---

## Import Limits

To prevent memory issues, the import script limits:
- **Borrowers:** 1,000 per run
- **Loans:** 1,000 per run
- **Payments:** 1,000 per run

**To import more:**
1. Run the script multiple times
2. Or modify the `LIMIT` in the script
3. Or remove the LIMIT for complete import

---

## Expected Output

```
================================================================================
COMPLETE GRAZURI DATA IMPORT
Importing: Users, Borrowers, Loans, Payments, Branches, and ALL related data
================================================================================

================================================================================
STEP 1: Importing Branches
================================================================================
Found 5 branches in Grazuri database
  ✓ Imported branch: Main Branch (MAIN)
  ✓ Imported branch: Nairobi Branch (NRB)
  ...

================================================================================
STEP 2: Importing Users
================================================================================
Found 50 users in Grazuri database
  ✓ Imported user: John Doe (jdoe) - admin
  ✓ Imported user: Jane Smith (jsmith) - loan_officer
  ...

================================================================================
STEP 3: Importing Borrowers
================================================================================
Found 500 borrowers in Grazuri database
  ✓ Imported 100 borrowers...
  ✓ Imported 200 borrowers...
  ...

================================================================================
STEP 4: Importing Loans
================================================================================
Found 300 loans in Grazuri database
  ✓ Imported 100 loans...
  ✓ Imported 200 loans...
  ...

================================================================================
STEP 5: Importing Payments
================================================================================
Found 1000 payments in Grazuri database
  ✓ Imported 100 payments...
  ✓ Imported 200 payments...
  ...

================================================================================
IMPORT SUMMARY
================================================================================

Category             Imported        Skipped         Errors         
-----------------------------------------------------------------
Branches             5               0               0              
Users                46              4               0              
Borrowers            500             0               0              
Loans                300             0               0              
Payments             1000            0               0              
-----------------------------------------------------------------
TOTAL                1851            4               0              

================================================================================
✅ COMPLETE DATA IMPORT FINISHED!
================================================================================

📊 Total Records Imported: 1851
⚠️  Total Records Skipped: 4
❌ Total Errors: 0

🎉 SUCCESS! Your Grazuri data has been imported to Haven Grazuri system.
```

---

## Verification

### Check Imported Data

```powershell
# Verify users
python verify_imported_users.py

# List all users
python list_all_users.py

# Check database counts
python -c "import django; import os; os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings'); django.setup(); from users.models import CustomUser; from loans.models import Borrower, Loan, LoanRepayment; print(f'Users: {CustomUser.objects.count()}'); print(f'Borrowers: {Borrower.objects.count()}'); print(f'Loans: {Loan.objects.count()}'); print(f'Payments: {LoanRepayment.objects.count()}')"
```

### Test in Browser

1. Go to http://127.0.0.1:8000/
2. Login with imported user credentials
3. Check:
   - Clients section (borrowers)
   - Loans section (loan records)
   - Payments section (repayment history)
   - Reports (data analytics)

---

## Troubleshooting

### Issue: "Table doesn't exist"
**Solution:** Import the SQL file first
```powershell
Get-Content "xygbfpsg_loans_populated.sql" | mysql -u root -ppassword acbptxvs_branch_system
```

### Issue: "Duplicate entry" errors
**Solution:** Normal - script skips existing records automatically

### Issue: "Borrower not found" for loans
**Solution:** Borrowers must be imported before loans. The script handles this automatically.

### Issue: Import is slow
**Solution:** 
- Normal for large datasets
- Script shows progress every 100 records
- Can take 5-10 minutes for thousands of records

### Issue: Some records skipped
**Solution:** 
- Records already exist in system
- Check "Skipped" count in summary
- Re-running is safe (won't create duplicates)

---

## Re-running the Import

**Safe to re-run:** Yes! The script:
- Checks for existing records
- Skips duplicates automatically
- Won't create duplicate data
- Shows what was skipped

**When to re-run:**
- After adding more data to SQL file
- If first import had errors
- To import additional records

---

## Performance Tips

### For Large Datasets (10,000+ records)

1. **Increase Limits:**
   Edit `import_complete_grazuri_data.py`:
   ```python
   LIMIT 1000  →  LIMIT 5000
   ```

2. **Run in Batches:**
   ```powershell
   # Import first 1000
   python import_complete_grazuri_data.py
   
   # Modify script to skip first 1000, then run again
   # Repeat as needed
   ```

3. **Monitor Progress:**
   - Script shows progress every 100 records
   - Check database counts periodically

---

## Data Integrity

### Preserved Data
- ✅ Account numbers
- ✅ Loan numbers
- ✅ Payment references
- ✅ ID numbers
- ✅ Phone numbers
- ✅ Email addresses
- ✅ Dates and timestamps
- ✅ Amounts and balances

### Relationships Maintained
- ✅ Borrowers → Loans
- ✅ Loans → Payments
- ✅ Users → Branches
- ✅ Loans → Branches

---

## Security Notes

### Passwords
- Original passwords decoded from base64
- Re-hashed with Django's secure PBKDF2
- Users should change passwords on first login

### Data Privacy
- All personal data migrated securely
- No data sent externally
- Local database only

### Access Control
- Role-based permissions applied
- Branch restrictions enforced
- Admin access limited

---

## Post-Import Tasks

### Immediate
- [ ] Verify all data imported correctly
- [ ] Test login with imported users
- [ ] Check loan records in system
- [ ] Verify payment history

### Short Term
- [ ] Have users change passwords
- [ ] Review and adjust permissions
- [ ] Configure email notifications
- [ ] Set up automated backups

### Long Term
- [ ] Train staff on new system
- [ ] Migrate remaining data (if any)
- [ ] Decommission old Grazuri system
- [ ] Monitor system performance

---

## Support

**Haven Grazuri Investment Limited**
- **Email:** havenin2023@gmail.com
- **Phone:** +254112941830
- **WhatsApp:** +254112941830

---

## Files Created

### Import Scripts
- **run_complete_import.py** - One-command complete import
- **import_complete_grazuri_data.py** - Main import script
- **import_grazuri_users.py** - Users only import

### Verification Scripts
- **verify_imported_users.py** - Verify users
- **list_all_users.py** - List all users
- **test_import_system.py** - System test

### Documentation
- **COMPLETE_IMPORT_GUIDE.md** - This guide
- **FINAL_USER_IMPORT_VERIFICATION.md** - Verification details
- **IMPORTED_GRAZURI_USERS.md** - User credentials

---

**Status:** ✅ READY FOR COMPLETE IMPORT  
**Date:** May 8, 2026  
**System:** Haven Grazuri Investment Limited