<?php
/**
 * Migration Script: Add manager_id to employees table
 * Run this file via browser or CLI: php database/migrations/run_manager_id_migration.php
 */

require_once __DIR__ . '/../../config/config.php';
require_once __DIR__ . '/../../includes/database.php';

try {
    $db = getDB();
    
    echo "Starting migration: Add manager_id to employees table...\n\n";
    
    // Check if column already exists
    $stmt = $db->query("SHOW COLUMNS FROM employees LIKE 'manager_id'");
    if ($stmt->rowCount() > 0) {
        echo "✓ Column 'manager_id' already exists.\n";
    } else {
        // Add manager_id column
        $db->exec("ALTER TABLE employees ADD COLUMN manager_id INT(11) NULL AFTER designation_id");
        echo "✓ Added manager_id column.\n";
    }
    
    // Check if foreign key exists
    $stmt = $db->query("
        SELECT CONSTRAINT_NAME 
        FROM information_schema.KEY_COLUMN_USAGE 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'employees' 
        AND CONSTRAINT_NAME = 'fk_emp_manager'
    ");
    
    if ($stmt->rowCount() == 0) {
        // Add foreign key constraint
        $db->exec("
            ALTER TABLE employees 
            ADD CONSTRAINT fk_emp_manager 
            FOREIGN KEY (manager_id) REFERENCES employees (employee_id) ON DELETE SET NULL
        ");
        echo "✓ Added foreign key constraint.\n";
    } else {
        echo "✓ Foreign key constraint already exists.\n";
    }
    
    // Check if indexes exist
    $stmt = $db->query("SHOW INDEXES FROM employees WHERE Key_name = 'idx_emp_manager'");
    if ($stmt->rowCount() == 0) {
        $db->exec("CREATE INDEX idx_emp_manager ON employees (manager_id)");
        echo "✓ Added index idx_emp_manager.\n";
    } else {
        echo "✓ Index idx_emp_manager already exists.\n";
    }
    
    $stmt = $db->query("SHOW INDEXES FROM employees WHERE Key_name = 'idx_emp_manager_status'");
    if ($stmt->rowCount() == 0) {
        $db->exec("CREATE INDEX idx_emp_manager_status ON employees (manager_id, employment_status)");
        echo "✓ Added index idx_emp_manager_status.\n";
    } else {
        echo "✓ Index idx_emp_manager_status already exists.\n";
    }
    
    echo "\n✅ Migration completed successfully!\n";
    echo "\nNext steps:\n";
    echo "1. Set manager_id = NULL for HR Admin employees\n";
    echo "2. Set manager_id = hr_employee_id for employees under each HR\n";
    
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
    exit(1);
}

