<?php
// Clean database schema fix for voucher_codes table
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "admission_system";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "<h2>Fixing voucher_codes table schema...</h2>";

// Step 1: Check current table structure
echo "<h3>Current table structure:</h3>";
$result = $conn->query("DESCRIBE voucher_codes");
echo "<table border='1'><tr><th>Field</th><th>Type</th><th>Null</th></tr>";
while($row = $result->fetch_assoc()) {
    echo "<tr><td>" . $row['Field'] . "</td><td>" . $row['Type'] . "</td><td>" . $row['Null'] . "</td></tr>";
}
echo "</table>";

// Step 2: Drop existing foreign key constraints
echo "<h3>Dropping foreign key constraints...</h3>";
$conn->query("ALTER TABLE voucher_codes DROP FOREIGN KEY IF EXISTS voucher_codes_ibfk_1");
$conn->query("ALTER TABLE voucher_codes DROP FOREIGN KEY IF EXISTS voucher_codes_ibfk_2");
$conn->query("ALTER TABLE voucher_codes DROP FOREIGN KEY IF EXISTS voucher_codes_ibfk_3");
echo "Foreign key constraints dropped.<br>";

// Step 3: Allow NULL values for student_id and program_id
echo "<h3>Allowing NULL values for student_id and program_id...</h3>";
$conn->query("ALTER TABLE voucher_codes MODIFY student_id INT NULL");
$conn->query("ALTER TABLE voucher_codes MODIFY program_id INT NULL");
echo "NULL values allowed.<br>";

// Step 4: Re-add foreign key constraints with proper settings
echo "<h3>Re-adding foreign key constraints...</h3>";
$conn->query("ALTER TABLE voucher_codes ADD CONSTRAINT voucher_codes_ibfk_1 
              FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE SET NULL");
$conn->query("ALTER TABLE voucher_codes ADD CONSTRAINT voucher_codes_ibfk_2 
              FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE SET NULL");
$conn->query("ALTER TABLE voucher_codes ADD CONSTRAINT voucher_codes_ibfk_3 
              FOREIGN KEY (generated_by) REFERENCES users(id) ON DELETE CASCADE");
echo "Foreign key constraints re-added.<br>";

// Step 5: Verify the changes
echo "<h3>Updated table structure:</h3>";
$result = $conn->query("DESCRIBE voucher_codes");
echo "<table border='1'><tr><th>Field</th><th>Type</th><th>Null</th></tr>";
while($row = $result->fetch_assoc()) {
    echo "<tr><td>" . $row['Field'] . "</td><td>" . $row['Type'] . "</td><td>" . $row['Null'] . "</td></tr>";
}
echo "</table>";

echo "<h2>Schema fix completed successfully!</h2>";
echo "<p>Bulk voucher generation should now work properly.</p>";
echo "<p><a href='admin/voucher_management.php'>Go to Voucher Management</a></p>";

$conn->close();
?>
