# Relationship Validation Report
**Date**: January 2026
**Status**: ⚠️ ISSUES FOUND

## Summary
- Total SQL Tables: 52
- Total Migrations Created: 49
- **Missing Migrations: 3** ❌
- Foreign Key Syntax Issues: **Multiple** ❌

---

## 🔴 CRITICAL ISSUES

### 1. Missing Migration Files
The following tables defined in SQL schema have NO migration files:

| Table Name | SQL Defined | Migration Created | Status |
|-----------|-----------|------------------|--------|
| invoices | ✅ Yes | ❌ No | **MISSING** |
| feature_toggles | ✅ Yes | ❌ No | **MISSING** |
| dispatcher_shifts | ✅ Yes | ❌ No | **MISSING** |

### 2. Foreign Key Syntax Errors in Migrations
Laravel syntax issue found in migrations: `onDelete('setNull')` should be `nullOnDelete()` or `onDelete('set null')`

**Affected migrations:**
- 2026_01_19_000011_create_drivers_table.php (operator_id)
- 2026_01_19_000010_create_vehicles_table.php (driver_id)
- 2026_01_19_000014_create_bookings_table.php (passenger_id, driver_id, vehicle_id, operator_id)
- And others...

---

## Foreign Key Relationships Validation

### Table: user_profiles
**SQL Definition:**
```sql
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
```
**Migration Status:** ✅ CORRECT
**Model Relationship Status:** ✅ CORRECT (BelongsTo User)

### Table: api_keys  
**SQL Definition:**
```sql
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
FOREIGN KEY (created_by) REFERENCES users(id)
```
**Migration Status:** ✅ CORRECT
**Model Relationship Status:** Need to verify

### Table: tenant_settings
**SQL Definition:**
```sql
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
```
**Migration Status:** ✅ CORRECT
**Model Relationship Status:** ✅ CORRECT

### Table: roles
**SQL Definition:**
```sql
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
```
**Migration Status:** ✅ CORRECT
**Model Relationship Status:** ✅ CORRECT

### Table: permissions
**SQL Definition:**
```sql
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
```
**Migration Status:** ✅ CORRECT
**Model Relationship Status:** Need to verify

### Table: user_roles
**SQL Definition:**
```sql
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL
FOREIGN KEY (assigned_by) REFERENCES users(id)
```
**Migration Status:** ❓ NEEDS VERIFICATION
**Model Relationship Status:** Need to verify

### Table: operators
**SQL Definition:**
```sql
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
```
**Migration Status:** ✅ CORRECT
**Model Relationship Status:** ✅ CORRECT

### Table: drivers
**SQL Definition:**
```sql
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
FOREIGN KEY (operator_id) REFERENCES operators(id) ON DELETE SET NULL
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
```
**Migration Status:** ⚠️ Issue with onDelete('setNull') syntax
**Model Relationship Status:** ✅ Need to verify all three relationships

### Table: vehicles
**SQL Definition:**
```sql
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
FOREIGN KEY (operator_id) REFERENCES operators(id) ON DELETE CASCADE
FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL
```
**Migration Status:** ⚠️ Issue with onDelete('setNull') syntax
**Model Relationship Status:** Need to verify

### Table: passengers
**SQL Definition:**
```sql
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
```
**Migration Status:** ✅ CORRECT
**Model Relationship Status:** ✅ CORRECT

### Table: bookings
**SQL Definition:**
```sql
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
FOREIGN KEY (passenger_id) REFERENCES passengers(id) ON DELETE SET NULL
FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL
FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL
FOREIGN KEY (operator_id) REFERENCES operators(id) ON DELETE SET NULL
```
**Migration Status:** ⚠️ Uses onDelete('setNull') - syntax issue
**Model Relationship Status:** ✅ Relationships defined

### Table: corporate_accounts
**SQL Definition:**
```sql
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
FOREIGN KEY (account_manager_id) REFERENCES users(id) ON DELETE SET NULL
```
**Migration Status:** ⚠️ Issue with onDelete('setNull') syntax
**Model Relationship Status:** Need to verify

### Table: disputes
**SQL Definition:**
```sql
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE SET NULL
FOREIGN KEY (reported_by_id) REFERENCES users(id) ON DELETE CASCADE
FOREIGN KEY (reported_against_id) REFERENCES users(id) ON DELETE CASCADE
```
**Migration Status:** Need to verify
**Model Relationship Status:** Need to verify

---

## Action Items

### Priority 1: Create Missing Migrations 🔴
- [ ] Create migration for `invoices` table
- [ ] Create migration for `feature_toggles` table  
- [ ] Create migration for `dispatcher_shifts` table

### Priority 2: Fix Foreign Key Syntax 🟠
- [ ] Update all `onDelete('setNull')` to `nullOnDelete()` in migrations
- [ ] Verify all foreign key constraints match SQL schema

### Priority 3: Validate Model Relationships 🟡
- [ ] Ensure all BelongsTo relationships exist
- [ ] Ensure all HasMany relationships exist
- [ ] Verify pivot table relationships (if applicable)
- [ ] Check cascade delete behavior matches SQL

### Priority 4: Testing
- [ ] Run Laravel migrations
- [ ] Verify no foreign key constraint errors
- [ ] Test cascade delete functionality
- [ ] Validate model relationship loading

---

## Foreign Keys by Deletion Action Type

### ON DELETE CASCADE (Auto-delete child records)
1. user_profiles → users
2. api_keys → users
3. tenant_settings → tenants
4. roles → tenants
5. permissions → roles
6. user_roles → users, roles
7. operators → tenants, users
8. drivers → tenants, users
9. vehicles → tenants, operators
10. passengers → tenants, users
11. saved_locations → passengers
12. bookings → tenants
13. trip_tracking → bookings
14. trip_ratings → bookings
15. corporate_accounts → tenants, users
16. corporate_travelers → corporate_accounts, users
17. travel_policies → corporate_accounts
18. booking_approvals → bookings, corporate_travelers, users
19. department_budgets → corporate_accounts
20. affiliates → tenants, users
21. affiliate_leads → affiliates
22. payments → tenants
23. commissions → tenants, bookings
24. rfq_requests → tenants
25. bids → tenants, rfq_requests
26. crm_leads → tenants
27. crm_quotes → tenants
28. crm_contracts → tenants, crm_quotes, corporate_accounts
29. corporate_clients → corporate_accounts
30. marketing_campaigns → corporate_accounts
31. customer_surveys → corporate_accounts
32. survey_responses → customer_surveys
33. dispatcher_shifts → tenants
34. sla_metrics → tenants, bookings
35. partners → tenants, users
36. partner_staff → partners, users
37. daily_analytics → tenants
38. performance_metrics → tenants
39. audit_logs → tenants
40. compliance_checks → tenants
41. documents → tenants

### ON DELETE SET NULL (Keep child records, clear reference)
1. api_keys created_by → users
2. drivers operator_id → operators
3. vehicles driver_id → drivers
4. bookings passenger_id, driver_id, vehicle_id, operator_id → passengers, drivers, vehicles, operators
5. affiliate_leads converted_booking_id → bookings
6. corporate_accounts account_manager_id → users
7. corporate_travelers manager_id → users
8. payments booking_id → bookings
9. payouts (no explicit deletes defined)
10. commissions payout_id → payouts
11. bids reviewed_by → users
12. crm_leads assigned_to, converted_to_account_id → users, corporate_accounts
13. crm_quotes lead_id, corporate_account_id, generated_by → crm_leads, corporate_accounts, users
14. crm_contracts signed_by → users
15. disputes booking_id → bookings
16. documents uploaded_by, verified_by → users
17. user_roles tenant_id → tenants
18. user_roles assigned_by → users
19. sla_violations sla_metric_id → sla_metrics
20. feature_toggles tenant_id → tenants
21. invoices → (requires migration)

---

## Recommendations

1. **Fix Syntax Immediately**: The `onDelete('setNull')` syntax will cause Laravel migration failures. Change to `nullOnDelete()`.

2. **Create Missing Migrations**: Add the 3 missing migration files before running any migrations.

3. **Test Migrations**: Run `php artisan migrate:refresh` to ensure all migrations execute without errors.

4. **Verify Model Relationships**: Load models via Tinker to ensure relationships work correctly.

5. **Database Constraints**: After migration, verify foreign key constraints in the database match the schema.