<?php
$host = '127.0.0.1'; // แก้ไขเป็น Host ของคุณ (เช่น localhost)
$user = 'root';      // แก้ไขเป็น Username ของ MySQL
$pass = '';          // แก้ไขเป็น Password ของ MySQL
$dbname = 'org_chart_db'; 

try {
    // 1. Connect to MySQL server first to create DB if it doesn't exist
    $pdo_server = new PDO("mysql:host=$host;charset=utf8mb4", $user, $pass);
    $pdo_server->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo_server->exec("CREATE DATABASE IF NOT EXISTS `$dbname` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    
    // 2. Connect to the specific database
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 3. Create tables if they don't exist
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS departments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL UNIQUE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS employees (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            position VARCHAR(255) NOT NULL,
            department_id INT NOT NULL,
            parent_id INT NULL,
            image_url VARCHAR(500),
            FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE RESTRICT,
            FOREIGN KEY (parent_id) REFERENCES employees(id) ON DELETE RESTRICT
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ");

} catch(PDOException $e) {
    die(json_encode([
        'status' => 'error', 
        'message' => 'Database connection failed: ' . $e->getMessage() . '. โปรดตรวจสอบ username/password ในไฟล์ db.php'
    ]));
}
?>