<?php
include 'includes/db.php';

echo "Fixing voucher_codes table schema to allow NULL values for bulk generation...\n";

// Check current table structure
echo "Current table structure:\n";
$result = $conn->query("DESCRIBE voucher_codes");
while($row = $result->fetch_assoc()) {
    echo "- " . $row['Field'] . ": " . $row['Type'] . " (Null: " . $row['Null'] . ")\n";
}

// Drop foreign key constraints if they exist
echo "\nDropping foreign key constraints...\n";
$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");

// Allow NULL values for student_id and program_id
echo "\nAllowing NULL values for student_id and program_id...\n";
$conn->query("ALTER TABLE voucher_codes MODIFY student_id INT NULL");
$conn->query("ALTER TABLE voucher_codes MODIFY program_id INT NULL");

// Re-add foreign key constraints with ON DELETE SET NULL
echo "\nRe-adding foreign key constraints with ON DELETE SET NULL...\n";
$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");

// Verify changes
echo "\nUpdated table structure:\n";
$result = $conn->query("DESCRIBE voucher_codes");
while($row = $result->fetch_assoc()) {
    echo "- " . $row['Field'] . ": " . $row['Type'] . " (Null: " . $row['Null'] . ")\n";
}

echo "\nSchema fixed successfully! Bulk voucher generation should now work.\n";
?>
