# Foreign Key Enablement Guide

## Current Status

Foreign key constraints are **DISABLED** in the following migrations:
- ✅ Indexes created and active
- ✅ Unique constraints created and active
- ⚠️ Foreign keys commented out pending data validation

## Why Foreign Keys Are Disabled

Foreign key constraints enforce referential integrity but will fail if:
1. Child records reference non-existent parent records (orphaned records)
2. Data type mismatches between foreign and primary keys
3. Parent tables don't exist yet

To prevent migration failures, foreign keys were commented out until data integrity is verified.

## Migrations with Disabled Foreign Keys

### 1. `2025_11_12_100001_add_indexes_and_foreign_keys_to_groups_table.php`
**Disabled FK:**
```php
// Line 21-24: Uncomment after verifying schools table exists
$table->foreign('school_id', 'fk_groups_school_id')
      ->references('id')
      ->on('schools')
      ->onDelete('cascade');
```

### 2. `2025_11_12_100002_add_indexes_and_foreign_keys_to_group_users_table.php`
**Disabled FKs:**
```php
// Lines 28-31: Uncomment after validating group_id references
$table->foreign('group_id', 'fk_group_users_group_id')
      ->references('id')
      ->on('groups')
      ->onDelete('cascade');

// Lines 33-36: Uncomment after validating user_id references
$table->foreign('user_id', 'fk_group_users_user_id')
      ->references('id')
      ->on('users')
      ->onDelete('cascade');
```

### 3. `2025_11_12_100004_add_indexes_and_foreign_keys_to_group_test_series_table.php`
**Disabled FKs:**
```php
// Lines 26-29: Uncomment after validating group_id references
$table->foreign('group_id', 'fk_group_test_series_group_id')
      ->references('id')
      ->on('groups')
      ->onDelete('cascade');

// Lines 31-34: Uncomment after validating test_series_id references
$table->foreign('test_series_id', 'fk_group_test_series_test_series_id')
      ->references('id')
      ->on('test_series')
      ->onDelete('cascade');
```

### 4. `2025_11_12_100005_create_group_activity_log_table.php`
**Disabled FKs:**
```php
// Lines 47-50: Uncomment after validating group_id
$table->foreign('group_id', 'fk_group_activity_log_group_id')
      ->references('id')
      ->on('groups')
      ->onDelete('cascade');

// Lines 52-55: Uncomment after validating user_id
$table->foreign('user_id', 'fk_group_activity_log_user_id')
      ->references('id')
      ->on('users')
      ->onDelete('cascade');

// Lines 57-60: Uncomment after validating student_id
$table->foreign('student_id', 'fk_group_activity_log_student_id')
      ->references('id')
      ->on('users')
      ->onDelete('set null');

// Lines 62-65: Uncomment after validating performed_by
$table->foreign('performed_by', 'fk_group_activity_log_performed_by')
      ->references('id')
      ->on('users')
      ->onDelete('cascade');
```

## Steps to Enable Foreign Keys

### Step 1: Run Data Validation

```bash
# Check for data integrity issues
php artisan groups:validate-relationships --verbose
```

**What it checks:**
- Invalid group_id references in group_users
- Invalid user_id references in group_users
- Invalid school_id references in groups
- Invalid references in group_test_series
- Invalid references in group_activity_log
- Duplicate records that violate unique constraints

### Step 2: Fix Issues

**Option A: Automatic Fix (recommended for orphaned records)**
```bash
# Automatically delete orphaned records
php artisan groups:validate-relationships --fix
```

**Option B: Manual Fix (recommended for missing parent records)**
If validation shows missing parent records that should exist:
1. Create the missing parent records
2. Or update the foreign key values to valid IDs
3. Or delete the orphaned child records manually

### Step 3: Verify All Issues Resolved

```bash
# Re-run validation to confirm
php artisan groups:validate-relationships
```

Expected output:
```
✓ No issues found! All relationships are valid.
✓ Foreign keys can be safely enabled.
```

### Step 4: Uncomment Foreign Keys

Edit each migration file and uncomment the foreign key constraints:

**Example: `2025_11_12_100002_add_indexes_and_foreign_keys_to_group_users_table.php`**

**Before:**
```php
// $table->foreign('group_id', 'fk_group_users_group_id')
//       ->references('id')
//       ->on('groups')
//       ->onDelete('cascade');
```

**After:**
```php
$table->foreign('group_id', 'fk_group_users_group_id')
      ->references('id')
      ->on('groups')
      ->onDelete('cascade');
```

Repeat for all commented foreign key constraints in all 4 migration files.

### Step 5: Create New Migration to Add Foreign Keys

Instead of editing old migrations (which may have already run), create a new migration:

```bash
php artisan make:migration enable_foreign_keys_for_group_tables
```

**Migration content:**
```php
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        // Enable foreign keys for groups table
        Schema::table('groups', function (Blueprint $table) {
            $table->foreign('school_id', 'fk_groups_school_id')
                  ->references('id')
                  ->on('schools')
                  ->onDelete('cascade');
        });

        // Enable foreign keys for group_users table
        Schema::table('group_users', function (Blueprint $table) {
            $table->foreign('group_id', 'fk_group_users_group_id')
                  ->references('id')
                  ->on('groups')
                  ->onDelete('cascade');

            $table->foreign('user_id', 'fk_group_users_user_id')
                  ->references('id')
                  ->on('users')
                  ->onDelete('cascade');
        });

        // Enable foreign keys for group_test_series table
        Schema::table('group_test_series', function (Blueprint $table) {
            $table->foreign('group_id', 'fk_group_test_series_group_id')
                  ->references('id')
                  ->on('groups')
                  ->onDelete('cascade');

            $table->foreign('test_series_id', 'fk_group_test_series_test_series_id')
                  ->references('id')
                  ->on('test_series')
                  ->onDelete('cascade');
        });

        // Enable foreign keys for group_activity_log table
        Schema::table('group_activity_log', function (Blueprint $table) {
            $table->foreign('group_id', 'fk_group_activity_log_group_id')
                  ->references('id')
                  ->on('groups')
                  ->onDelete('cascade');

            $table->foreign('user_id', 'fk_group_activity_log_user_id')
                  ->references('id')
                  ->on('users')
                  ->onDelete('cascade');

            $table->foreign('student_id', 'fk_group_activity_log_student_id')
                  ->references('id')
                  ->on('users')
                  ->onDelete('set null');

            $table->foreign('performed_by', 'fk_group_activity_log_performed_by')
                  ->references('id')
                  ->on('users')
                  ->onDelete('cascade');
        });
    }

    public function down(): void
    {
        Schema::table('groups', function (Blueprint $table) {
            $table->dropForeign('fk_groups_school_id');
        });

        Schema::table('group_users', function (Blueprint $table) {
            $table->dropForeign('fk_group_users_group_id');
            $table->dropForeign('fk_group_users_user_id');
        });

        Schema::table('group_test_series', function (Blueprint $table) {
            $table->dropForeign('fk_group_test_series_group_id');
            $table->dropForeign('fk_group_test_series_test_series_id');
        });

        Schema::table('group_activity_log', function (Blueprint $table) {
            $table->dropForeign('fk_group_activity_log_group_id');
            $table->dropForeign('fk_group_activity_log_user_id');
            $table->dropForeign('fk_group_activity_log_student_id');
            $table->dropForeign('fk_group_activity_log_performed_by');
        });
    }
};
```

### Step 6: Run Migrations

```bash
php artisan migrate
```

If migration succeeds, foreign keys are now active!

### Step 7: Verify Foreign Keys

```bash
# Check that foreign keys exist (MySQL)
SHOW CREATE TABLE group_users;

# Or use Laravel schema inspection
php artisan tinker
>>> Schema::getConnection()->getDoctrineSchemaManager()->listTableForeignKeys('group_users');
```

## Troubleshooting

### Error: "Cannot add foreign key constraint"

**Cause:** Orphaned records still exist in child tables.

**Solution:**
1. Re-run validation: `php artisan groups:validate-relationships --verbose`
2. Identify specific orphaned records
3. Delete them: `php artisan groups:validate-relationships --fix`
4. Try migration again

### Error: "Table 'schools' doesn't exist"

**Cause:** Parent table (schools) hasn't been created yet.

**Solution:**
1. Create schools table first
2. Or skip that foreign key for now
3. Or create a migration to create schools table

### Error: "Duplicate foreign key constraint name"

**Cause:** Foreign key already exists from previous migration attempt.

**Solution:**
```bash
# Drop existing foreign key first
php artisan tinker
>>> DB::statement('ALTER TABLE group_users DROP FOREIGN KEY fk_group_users_group_id');
```

## Benefits of Foreign Keys

Once enabled, foreign keys provide:

1. **Data Integrity:** Prevents orphaned records automatically
2. **Referential Actions:** Cascading deletes/updates
3. **Database-Level Validation:** Faster than application-level checks
4. **Query Optimization:** Database can use FKs for better query plans
5. **Documentation:** Schema clearly shows relationships

## Risks Without Foreign Keys

Currently, without foreign keys:
- ⚠️ Orphaned records can accumulate
- ⚠️ No automatic cascade on delete
- ⚠️ Data integrity depends on application code
- ⚠️ Must manually clean up related records

**Recommendation:** Enable foreign keys as soon as data validation passes.

## Maintenance

After enabling foreign keys:

1. **Deletions are automatic:** Deleting a group will cascade delete all:
   - group_users records
   - group_test_series records
   - group_activity_log records

2. **Insertions are validated:** Cannot create group_users without valid group_id and user_id

3. **Monitor for FK violations:** Any code that tries to insert invalid references will fail with FK constraint error

## Questions?

See documentation: `/docs/GROUP_DATA_FLOW.md`
Run validation: `php artisan groups:validate-relationships --help`