<?php
// Direct database schema fix without requiring user interaction
$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 "Starting voucher_codes table schema fix...\n";

// Step 1: Check if table exists
$table_check = $conn->query("SHOW TABLES LIKE 'voucher_codes'");
if ($table_check->num_rows == 0) {
    echo "Creating voucher_codes table...\n";
    $create_sql = "CREATE TABLE voucher_codes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(50) UNIQUE NOT NULL,
        student_id INT NULL,
        program_id INT NULL,
        generated_by INT NOT NULL,
        generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        used_at TIMESTAMP NULL,
        expires_at TIMESTAMP NULL,
        status ENUM('active', 'used', 'expired') DEFAULT 'active',
        FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE SET NULL,
        FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE SET NULL,
        FOREIGN KEY (generated_by) REFERENCES users(id)
    )";
    $conn->query($create_sql);
    echo "Table created successfully.\n";
} else {
    echo "Table exists, modifying schema...\n";
    
    // Step 2: Drop existing foreign keys
    echo "Dropping 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");
    $conn->query("ALTER TABLE voucher_codes DROP FOREIGN KEY IF EXISTS voucher_codes_ibfk_3");
    
    // Step 3: Allow NULL values
    echo "Allowing NULL values...\n";
    $conn->query("ALTER TABLE voucher_codes MODIFY student_id INT NULL");
    $conn->query("ALTER TABLE voucher_codes MODIFY program_id INT NULL");
    
    // Step 4: Re-add foreign keys with ON DELETE SET NULL
    echountu
   andin
    
    vouchers
   做强
foreign key constraints with ON 
student_id
program_id
_variable
generated_by
 Ras
foreign
   zusammensetzen

foreign
key
constraints
with
ON
DELETE
SET
NULL
and
ON
DELETE
CASCADE
respectively

echo "Re-adding foreign key constraints...\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");
$conn->query("ALTER TABLE voucher_codes ADD CONSTRAINT voucher_codes_ibfk_3 
              FOREIGN KEY (generated_by) REFERENCES users(id) ON DELETE CASCADE");
}

// Step 5: Verify the changes
echo "\nVerifying updated 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 fix completed successfully!\n";
echo "Bulk voucher generation should now work properly.\n";

$conn->close();

// Redirect to voucher management after 3 seconds
echo "<script>
setTimeout(function() {
    window.location.href = 'admin/voucher_management.php';
}, 3000);
</script>";
echo "<p>Redirecting to voucher management in 3 seconds...</p>";
?>
