<?php
/**
 * Database Migration: Fix Foreign Key Constraints
 * This migration updates foreign keys to use CASCADE on DELETE/UPDATE
 * This allows proper deletion of parent records without constraint errors
 */

require_once __DIR__ . '/../config/database.php';

$conn = getDBConnection();

echo "<h2>Fixing Foreign Key Constraints - Database Migration</h2>";
echo "<p>This will update foreign keys to use CASCADE for proper deletion handling.</p>";

$errors = [];
$success = [];

// Helper function to drop and recreate foreign key
function updateForeignKey($conn, $table, $constraint_name, $column, $ref_table, $ref_column, $on_delete = 'CASCADE', $on_update = 'CASCADE') {
    global $errors, $success;

    // Drop existing constraint
    $drop_sql = "ALTER TABLE `$table` DROP FOREIGN KEY `$constraint_name`";
    $conn->query($drop_sql); // Ignore errors if constraint doesn't exist

    // Add new constraint with CASCADE
    $add_sql = "ALTER TABLE `$table`
                ADD CONSTRAINT `$constraint_name`
                FOREIGN KEY (`$column`)
                REFERENCES `$ref_table`(`$ref_column`)
                ON DELETE $on_delete
                ON UPDATE $on_update";

    if ($conn->query($add_sql)) {
        $success[] = "$table.$constraint_name updated successfully";
        return true;
    } else {
        $errors[] = "$table.$constraint_name: " . $conn->error;
        return false;
    }
}

echo "<h3>Updating Foreign Keys...</h3>";

// 1. ENROLLMENTS table
echo "<h4>1. Enrollments Table</h4>";
updateForeignKey($conn, 'enrollments', 'enrollments_ibfk_1', 'student_id', 'users', 'id');
updateForeignKey($conn, 'enrollments', 'enrollments_ibfk_2', 'subject_id', 'subjects', 'id');

// 2. ACTIVITIES table
echo "<h4>2. Activities Table</h4>";
updateForeignKey($conn, 'activities', 'activities_ibfk_1', 'subject_id', 'subjects', 'id');
updateForeignKey($conn, 'activities', 'activities_ibfk_2', 'created_by', 'users', 'id', 'SET NULL');

// 3. SUBMISSIONS table
echo "<h4>3. Submissions Table</h4>";
updateForeignKey($conn, 'submissions', 'submissions_ibfk_1', 'activity_id', 'activities', 'id');
updateForeignKey($conn, 'submissions', 'submissions_ibfk_2', 'student_id', 'users', 'id');

// 4. ACTIVITY_ATTEMPTS table
echo "<h4>4. Activity Attempts Table</h4>";
updateForeignKey($conn, 'activity_attempts', 'activity_attempts_ibfk_1', 'student_id', 'users', 'id');
updateForeignKey($conn, 'activity_attempts', 'activity_attempts_ibfk_2', 'activity_id', 'activities', 'id');

// 5. NOTIFICATIONS table
echo "<h4>5. Notifications Table</h4>";
updateForeignKey($conn, 'notifications', 'notifications_ibfk_1', 'user_id', 'users', 'id');
updateForeignKey($conn, 'notifications', 'notifications_ibfk_2', 'activity_id', 'activities', 'id');

// 6. CODE_CHALLENGES table
echo "<h4>6. Code Challenges Table</h4>";
// Check if table exists first
$check = $conn->query("SHOW TABLES LIKE 'code_challenges'");
if ($check && $check->num_rows > 0) {
    // No foreign keys to update in code_challenges itself
    $success[] = "code_challenges table checked (no FK updates needed)";
}

// 7. DAILY_CHALLENGES table
echo "<h4>7. Daily Challenges Table</h4>";
$check = $conn->query("SHOW TABLES LIKE 'daily_challenges'");
if ($check && $check->num_rows > 0) {
    updateForeignKey($conn, 'daily_challenges', 'daily_challenges_ibfk_1', 'challenge_id', 'code_challenges', 'id');
}

// 8. REMEMBER_TOKENS table
echo "<h4>8. Remember Tokens Table</h4>";
$check = $conn->query("SHOW TABLES LIKE 'remember_tokens'");
if ($check && $check->num_rows > 0) {
    // Check if FK exists
    $fk_check = $conn->query("SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS
                              WHERE TABLE_NAME = 'remember_tokens'
                              AND CONSTRAINT_TYPE = 'FOREIGN KEY'");
    if ($fk_check && $fk_check->num_rows > 0) {
        while ($fk = $fk_check->fetch_assoc()) {
            $conn->query("ALTER TABLE remember_tokens DROP FOREIGN KEY " . $fk['CONSTRAINT_NAME']);
        }
    }

    // Add proper FK
    $sql = "ALTER TABLE remember_tokens
            ADD CONSTRAINT fk_remember_tokens_user
            FOREIGN KEY (user_id)
            REFERENCES users(id)
            ON DELETE CASCADE
            ON UPDATE CASCADE";
    if ($conn->query($sql)) {
        $success[] = "remember_tokens.fk_remember_tokens_user updated successfully";
    } else {
        $errors[] = "remember_tokens FK: " . $conn->error;
    }
}

// 9. INVITATION_TOKENS table (if created)
echo "<h4>9. Invitation Tokens Table</h4>";
$check = $conn->query("SHOW TABLES LIKE 'invitation_tokens'");
if ($check && $check->num_rows > 0) {
    updateForeignKey($conn, 'invitation_tokens', 'invitation_tokens_ibfk_1', 'created_by', 'users', 'id', 'SET NULL');
    updateForeignKey($conn, 'invitation_tokens', 'invitation_tokens_ibfk_2', 'used_by', 'users', 'id', 'SET NULL');
}

// Summary
echo "<hr>";
echo "<h3>Migration Summary</h3>";

if (count($success) > 0) {
    echo "<div style='background: #d4edda; padding: 15px; border-radius: 5px; border-left: 4px solid #28a745;'>";
    echo "<h4 style='color: #155724; margin-top: 0;'>✓ Success (" . count($success) . " items)</h4>";
    echo "<ul style='color: #155724;'>";
    foreach ($success as $item) {
        echo "<li>$item</li>";
    }
    echo "</ul>";
    echo "</div>";
}

if (count($errors) > 0) {
    echo "<div style='background: #f8d7da; padding: 15px; border-radius: 5px; border-left: 4px solid #dc3545; margin-top: 15px;'>";
    echo "<h4 style='color: #721c24; margin-top: 0;'>⚠ Warnings/Errors (" . count($errors) . " items)</h4>";
    echo "<ul style='color: #721c24;'>";
    foreach ($errors as $error) {
        echo "<li>$error</li>";
    }
    echo "</ul>";
    echo "<p style='color: #721c24;'><strong>Note:</strong> Some errors are expected if constraints don't exist yet. This is normal.</p>";
    echo "</div>";
} else {
    echo "<div style='background: #d1ecf1; padding: 15px; border-radius: 5px; border-left: 4px solid #0c5460; margin-top: 15px;'>";
    echo "<p style='color: #0c5460; margin: 0;'><strong>✓ All foreign keys updated successfully!</strong></p>";
    echo "</div>";
}

echo "<hr>";
echo "<h3>What This Fixes</h3>";
echo "<ul>";
echo "<li>✅ Activities can now be deleted without constraint errors</li>";
echo "<li>✅ Deleting an activity will automatically delete related submissions</li>";
echo "<li>✅ Deleting an activity will automatically delete related notifications</li>";
echo "<li>✅ Deleting an activity will automatically delete related attempts</li>";
echo "<li>✅ Deleting a user will cascade properly to related records</li>";
echo "<li>✅ Deleting a subject will cascade to activities and submissions</li>";
echo "</ul>";

echo "<h3>CASCADE Behavior</h3>";
echo "<div style='background: #fff3cd; padding: 15px; border-radius: 5px; border-left: 4px solid #ffc107;'>";
echo "<p style='color: #856404; margin: 0;'><strong>ON DELETE CASCADE:</strong> When a parent record is deleted, all child records are automatically deleted.</p>";
echo "<p style='color: #856404; margin: 5px 0 0 0;'><strong>ON DELETE SET NULL:</strong> When a parent record is deleted, the foreign key in child records is set to NULL (used for optional relationships like created_by).</p>";
echo "</div>";

echo "<hr>";
echo "<h3>Next Steps</h3>";
echo "<ol>";
echo "<li>Try deleting an activity - it should work now!</li>";
echo "<li>Verify that related submissions and notifications are also deleted</li>";
echo "<li>Test deleting subjects and users</li>";
echo "</ol>";

echo "<p><a href='../admin/activities.php' style='display: inline-block; padding: 10px 20px; background: #667eea; color: white; text-decoration: none; border-radius: 5px; margin-top: 15px;'>← Back to Activities</a></p>";

$conn->close();
?>
