<?php
$host = "localhost";
$user = "root";
$password = "";
$dbname = "admission_system";

$conn = new mysqli($host, $user, $password, $dbname);

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

echo "Creating voucher codes system...\n\n";

// Create voucher_codes table
echo "=== Creating voucher_codes table ===\n";
$sql = "CREATE TABLE IF NOT EXISTS voucher_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) UNIQUE NOT NULL,
    student_id INT NOT NULL,
    program_id INT NOT NULL,
    status ENUM('active', 'used', 'expired') DEFAULT 'active',
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    used_at TIMESTAMP NULL,
    expires_at TIMESTAMP NULL,
    generated_by INT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES users(id),
    FOREIGN KEY (program_id) REFERENCES programs(id),
    FOREIGN KEY (generated_by) REFERENCES users(id)
)";

if ($conn->query($sql) === TRUE) {
    echo "voucher_codes table created successfully\n";
} else {
    echo "Error creating voucher_codes table: " . $conn->error . "\n";
}

// Add voucher_code column to applications table
echo "\n=== Adding voucher_code column to applications table ===\n";
$sql = "ALTER TABLE applications ADD COLUMN voucher_code VARCHAR(20) NULL AFTER program_id";
$result = $conn->query("SHOW COLUMNS FROM applications LIKE 'voucher_code'");
if ($result->num_rows == 0) {
    if ($conn->query($sql) === TRUE) {
        echo "voucher_code column added to applications table\n";
    } else {
        echo "Error adding voucher_code column: " . $conn->error . "\n";
    }
} else {
    echo "voucher_code column already exists in applications table\n";
}

// Check if super_admin role exists in users table
echo "\n=== Checking super_admin role ===\n";
$check_role = $conn->query("DESCRIBE users");
$role_field_found = false;
while($row = $check_role->fetch_assoc()) {
    if ($row['Field'] == 'role') {
        $role_field_found = true;
        $enum_values = $row['Type'];
        if (strpos($enum_values, 'super_admin') === false) {
            echo "Adding super_admin to role enum...\n";
            $alter_sql = "ALTER TABLE users MODIFY COLUMN role ENUM('student','admin','system_admin','super_admin') DEFAULT 'student'";
            if ($conn->query($alter_sql) === TRUE) {
                echo "super_admin role added to enum\n";
            } else {
                echo "Error adding super_admin role: " . $conn->error . "\n";
            }
        } else {
            echo "super_admin role already exists in enum\n";
        }
        break;
    }
}

if (!$role_field_found) {
    echo "Role field not found in users table\n";
}

// Create or update super admin account
echo "\n=== Creating super admin account ===\n";
$check_admin = $conn->query("SELECT id FROM users WHERE email = 'superadmin@uol.edu.pk'");
if ($check_admin->num_rows == 0) {
    $password = password_hash('superadmin123', PASSWORD_DEFAULT);
    $sql = "INSERT INTO users (full_name, email, password, role, cnic, phone) 
            VALUES ('Super Administrator', 'superadmin@uol.edu.pk', '$password', 'super_admin', '9999999999999', '99999999999')";
    
    if ($conn->query($sql) === TRUE) {
        echo "Super admin account created successfully\n";
        echo "Email: superadmin@uol.edu.pk\n";
        echo "Password: superadmin123\n";
        echo "Role: super_admin\n";
    } else {
        echo "Error creating super admin account: " . $conn->error . "\n";
    }
} else {
    echo "Super admin account already exists\n";
}

$conn->close();
?>
