<?php
// Database Setup Script
// Run this once to create/update the database schema

$host = 'localhost';
$username = 'root';
$password = '';

try {
    // Connect to MySQL server
    $pdo = new PDO("mysql:host=$host", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Create database if it doesn't exist
    $pdo->exec("CREATE DATABASE IF NOT EXISTS recruitment_system");
    $pdo->exec("USE recruitment_system");
    
    echo "Database connected successfully.\n";
    
    // Read and execute the database schema
    $sql_file = file_get_contents('database.sql');
    
    // Split SQL commands by semicolon
    $sql_commands = array_filter(array_map('trim', explode(';', $sql_file)));
    
    foreach ($sql_commands as $command) {
        if (!empty($command) && !preg_match('/^--/', $command)) {
            try {
                $pdo->exec($command);
            } catch (PDOException $e) {
                // Ignore table already exists errors
                if (strpos($e->getMessage(), 'already exists') === false) {
                    echo "Error executing command: " . $e->getMessage() . "\n";
                }
            }
        }
    }
    
    echo "Database schema updated successfully!\n";
    echo "You can now use the application.\n";
    
} catch (PDOException $e) {
    echo "Database connection failed: " . $e->getMessage() . "\n";
}
?>