<?php
require_once 'api.php';

try {
    $pdo = db();
    
    // 1. Create leave_policies table
    $pdo->exec("CREATE TABLE IF NOT EXISTS `leave_policies` (
      `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      `company_id` VARCHAR(36) NOT NULL,
      `policy_name` VARCHAR(100) NOT NULL,
      `weekend_days` JSON COMMENT 'e.g. [\"Friday\", \"Saturday\"]',
      `annual_leave_days` INT DEFAULT 21,
      `casual_leave_days` INT DEFAULT 7,
      `sick_leave_days` INT DEFAULT 15,
      `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (`company_id`) REFERENCES `companies`(`company_id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
    echo "Table 'leave_policies' created or already exists.\n";

    // 2. Add policy_id to employees
    try {
        $pdo->exec("ALTER TABLE `employees` ADD COLUMN `policy_id` INT UNSIGNED DEFAULT NULL AFTER `branch_id` ");
        $pdo->exec("ALTER TABLE `employees` ADD FOREIGN KEY (`policy_id`) REFERENCES `leave_policies`(`id`) ON DELETE SET NULL");
        echo "Column 'policy_id' added to 'employees'.\n";
    } catch (Exception $e) {
        echo "Column 'policy_id' might already exist: " . $e->getMessage() . "\n";
    }

    // 3. Add leave_type to leave_requests
    try {
        $pdo->exec("ALTER TABLE `leave_requests` ADD COLUMN `leave_type` ENUM('annual', 'casual', 'sick') DEFAULT 'annual' AFTER `end_date` ");
        echo "Column 'leave_type' added to 'leave_requests'.\n";
    } catch (Exception $e) {
        echo "Column 'leave_type' might already exist: " . $e->getMessage() . "\n";
    }

    echo "Migration completed successfully.";
} catch (Exception $e) {
    echo "Migration failed: " . $e->getMessage();
}
