# Fix Foreign Key Constraint Error

## Error Message
```
#3780 - Referencing column 'approved_by_id' and referenced column 'id' 
in foreign key constraint 'expenses_expense_approved_by_id_f377ac9a_fk_users_customuser_id' 
are incompatible.
```

## Root Cause
The `users_customuser.id` column type doesn't match the `expenses_expense.approved_by_id` column type.

Your database likely uses:
- `users_customuser.id` as `varchar(32)` or `char(32)` (UUID)
- But the SQL tried to create foreign keys with `char(32)` which might have different collation

## Solution: Use Django Migrations (RECOMMENDED)

Django will automatically handle the correct column types and foreign keys.

### Method 1: Django Migrations (Best)

**Run this command:**
```bash
python deploy_expenses_django_migrate.py
```

This script will:
1. ✓ Check database connection
2. ✓ Create migrations with correct column types
3. ✓ Apply migrations
4. ✓ Verify table creation
5. ✓ Check foreign keys

### Method 2: Manual SQL Without Foreign Keys

If Django migrations don't work, use the table without foreign keys:

**Step 1: Run this SQL in phpMyAdmin:**
```sql
-- Use the file: create_expenses_table_no_fk.sql
```

**Step 2: Add foreign keys manually later (optional)**

First, check your actual column types:
```sql
-- Run this in phpMyAdmin
SHOW COLUMNS FROM users_customuser WHERE Field = 'id';
SHOW COLUMNS FROM branch_system_branch WHERE Field = 'id';
SHOW COLUMNS FROM loans_loan WHERE Field = 'id';
```

Then create foreign keys with matching types.

### Method 3: Let Django Handle It Automatically

**Step 1: Just run migrations:**
```bash
cd /home/acbptxvs/public_html/branchbusinessadvance.co.ke
source /home/acbptxvs/virtualenv/public_html/branchbusinessadvance.co.ke/3.13/bin/activate
python manage.py makemigrations expenses
python manage.py migrate expenses
```

Django will automatically:
- Detect the correct column types
- Create matching foreign key columns
- Add constraints properly

## Why This Happened

The SQL file assumed `char(32)` for UUID fields, but your database might use:
- `varchar(32)` instead of `char(32)`
- Different character set/collation
- Different storage format

Django's ORM handles this automatically by inspecting the existing tables.

## Quick Fix Commands

### Option A: Use Django (Recommended)
```bash
# SSH into your server
cd /home/acbptxvs/public_html/branchbusinessadvance.co.ke
source /home/acbptxvs/virtualenv/public_html/branchbusinessadvance.co.ke/3.13/bin/activate

# Run migrations
python manage.py makemigrations expenses
python manage.py migrate expenses

# Verify
python manage.py dbshell
> SHOW TABLES LIKE 'expenses%';
> DESCRIBE expenses_expense;
> exit
```

### Option B: Use SQL Without Foreign Keys
```bash
# In phpMyAdmin, run:
# File: create_expenses_table_no_fk.sql

# Then verify in Django shell:
python manage.py shell
>>> from expenses.models import Expense
>>> Expense.objects.count()
0
>>> exit()
```

## Verification

After deployment, verify it works:

```python
# Run this in Django shell
python manage.py shell

from expenses.models import Expense
from users.models import CustomUser
from branch_system.models import Branch

# Check if model is accessible
print(Expense._meta.db_table)  # Should print: expenses_expense

# Check if we can query
print(Expense.objects.count())  # Should print: 0

# Try to create a test expense (will fail if foreign keys are wrong)
user = CustomUser.objects.first()
branch = Branch.objects.first()

if user and branch:
    expense = Expense(
        title="Test Expense",
        category="operations",
        amount=1000.00,
        expense_date="2024-11-29",
        payment_method="cash",
        paid_to="Test Vendor",
        branch=branch,
        staff=user,
        status="pending"
    )
    expense.save()
    print(f"✓ Test expense created with ID: {expense.id}")
    expense.delete()
    print("✓ Test expense deleted")
else:
    print("⚠ No users or branches found for testing")
```

## Expected Output

If successful, you should see:
```
expenses_expense
0
✓ Test expense created with ID: 1
✓ Test expense deleted
```

## Troubleshooting

### Error: "No module named 'expenses'"
**Solution:** Make sure 'expenses' is in INSTALLED_APPS in settings.py

### Error: "Table already exists"
**Solution:** 
```sql
DROP TABLE IF EXISTS expenses_expense;
```
Then run migrations again.

### Error: "Cannot add foreign key constraint"
**Solution:** Use `create_expenses_table_no_fk.sql` which creates the table without foreign keys. The application will still work, just without database-level constraint enforcement.

## Files to Use

1. **deploy_expenses_django_migrate.py** - Automated Django migration (BEST)
2. **create_expenses_table_no_fk.sql** - SQL without foreign keys (FALLBACK)
3. **check_user_id_type.sql** - Check your column types

## Recommendation

**Use Django migrations** - it's the safest and most reliable method because:
- ✓ Automatically detects correct column types
- ✓ Handles foreign keys properly
- ✓ Creates indexes correctly
- ✓ Maintains database integrity
- ✓ Can be rolled back if needed

Run:
```bash
python deploy_expenses_django_migrate.py
```

---

**Last Updated:** November 29, 2024  
**Status:** Ready to deploy with Django migrations