fix_feedback_replies_table.php

<?php
// fix_feedback_replies_table.php
// Script to fix feedback_replies table schema

include 'connection.php';

// Disable foreign key checks to allow altering
$db->query("SET FOREIGN_KEY_CHECKS=0;");

// Change staff_id column type to INT NOT NULL
$alter1 = "ALTER TABLE feedback_replies MODIFY COLUMN staff_id INT NOT NULL;";
if ($db->query($alter1) === TRUE) {
    echo "Column staff_id modified to INT NOT NULL successfully.\n";
} else {
    echo "Error modifying staff_id column: " . $db->error . "\n";
}

// Drop existing foreign key on staff_id if exists
$drop_fk = "ALTER TABLE feedback_replies DROP FOREIGN KEY IF EXISTS fk_staff_id;";
$db->query($drop_fk); // Ignore errors if FK does not exist

// Add foreign key constraint on staff_id referencing staff_users(id)
$add_fk = "ALTER TABLE feedback_replies ADD CONSTRAINT fk_staff_id FOREIGN KEY (staff_id) REFERENCES staff_users(id) ON DELETE CASCADE;";
if ($db->query($add_fk) === TRUE) {
    echo "Foreign key constraint on staff_id added successfully.\n";
} else {
    echo "Error adding foreign key constraint: " . $db->error . "\n";
}

// Re-enable foreign key checks
$db->query("SET FOREIGN_KEY_CHECKS=1;");

$db->close();
?>