<?php
/**
 * Run Employee Transfers Table Enhancement Migration
 * Adds missing columns for the transfer form
 */

require_once __DIR__ . '/../includes/init.php';

header('Content-Type: text/html; charset=utf-8');
?>
<!DOCTYPE html>
<html>
<head>
    <title>Employee Transfers Table Migration</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; background: #f5f5f5; }
        .container { background: white; padding: 20px; border-radius: 5px; max-width: 900px; margin: 0 auto; }
        .success { color: #28a745; font-weight: bold; }
        .error { color: #dc3545; font-weight: bold; }
        pre { background: #f8f9fa; padding: 15px; border-radius: 3px; overflow-x: auto; }
    </style>
</head>
<body>
    <div class="container">
        <h2>Employee Transfers Table Enhancement Migration</h2>
        <pre>
<?php

try {
    $db = getDB();
    
    echo "Starting migration...\n\n";
    
    // Check if table exists
    $checkTable = $db->query("SHOW TABLES LIKE 'employee_transfers'");
    $tableExists = $checkTable->rowCount() > 0;
    
    if (!$tableExists) {
        echo "✗ Table 'employee_transfers' does not exist. Creating...\n\n";
        
        // Create the table with all fields
        $createTableSQL = "
        CREATE TABLE `employee_transfers` (
          `transfer_id` int(11) NOT NULL AUTO_INCREMENT,
          `employee_id` int(11) NOT NULL,
          `from_site_id` int(11) NOT NULL,
          `from_location` varchar(255) DEFAULT NULL,
          `to_site_id` int(11) NOT NULL,
          `to_location` varchar(255) DEFAULT NULL,
          `transfer_date` date NOT NULL,
          `effective_branch` enum('Yes','No') DEFAULT NULL,
          `effective_attendance` enum('Yes','No') DEFAULT NULL,
          `esi_applicability` enum('Yes','No') DEFAULT NULL,
          `transfer_form_filled` enum('Yes','No') DEFAULT 'No',
          `approved_by_district_head` enum('Yes','No') DEFAULT NULL,
          `transfer_reason` text,
          `old_department_id` int(11) DEFAULT NULL,
          `new_department_id` int(11) DEFAULT NULL,
          `old_designation_id` int(11) DEFAULT NULL,
          `new_designation_id` int(11) DEFAULT NULL,
          `transfer_status` enum('pending','approved','completed','cancelled') DEFAULT 'pending',
          `approved_by` int(11) DEFAULT NULL,
          `approved_at` datetime DEFAULT NULL,
          `created_by` int(11) DEFAULT NULL,
          `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
          `updated_by` int(11) DEFAULT NULL,
          `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`transfer_id`),
          KEY `fk_transfer_emp` (`employee_id`),
          KEY `fk_transfer_from_site` (`from_site_id`),
          KEY `fk_transfer_to_site` (`to_site_id`),
          KEY `fk_transfer_old_dept` (`old_department_id`),
          KEY `fk_transfer_new_dept` (`new_department_id`),
          KEY `fk_transfer_old_desig` (`old_designation_id`),
          KEY `fk_transfer_new_desig` (`new_designation_id`),
          KEY `fk_transfer_approved_by` (`approved_by`),
          KEY `fk_transfer_created_by` (`created_by`),
          KEY `fk_transfer_updated_by` (`updated_by`),
          CONSTRAINT `fk_transfer_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE,
          CONSTRAINT `fk_transfer_from_site` FOREIGN KEY (`from_site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
          CONSTRAINT `fk_transfer_to_site` FOREIGN KEY (`to_site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
          CONSTRAINT `fk_transfer_old_dept` FOREIGN KEY (`old_department_id`) REFERENCES `departments` (`department_id`) ON DELETE SET NULL,
          CONSTRAINT `fk_transfer_new_dept` FOREIGN KEY (`new_department_id`) REFERENCES `departments` (`department_id`) ON DELETE SET NULL,
          CONSTRAINT `fk_transfer_old_desig` FOREIGN KEY (`old_designation_id`) REFERENCES `designations` (`designation_id`) ON DELETE SET NULL,
          CONSTRAINT `fk_transfer_new_desig` FOREIGN KEY (`new_designation_id`) REFERENCES `designations` (`designation_id`) ON DELETE SET NULL,
          CONSTRAINT `fk_transfer_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
          CONSTRAINT `fk_transfer_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
          CONSTRAINT `fk_transfer_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ";
        
        $db->exec($createTableSQL);
        echo "✓ Table created successfully!\n\n";
    } else {
        echo "✓ Table 'employee_transfers' exists. Checking for missing columns...\n\n";
        
        // Get existing columns
        $stmt = $db->query("SHOW COLUMNS FROM employee_transfers");
        $existingColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        $columnsToAdd = [
            ['from_location', 'VARCHAR(255)', 'from_site_id'],
            ['to_location', 'VARCHAR(255)', 'to_site_id'],
            ['effective_branch', "ENUM('Yes','No')", 'transfer_date'],
            ['effective_attendance', "ENUM('Yes','No')", 'effective_branch'],
            ['esi_applicability', "ENUM('Yes','No')", 'effective_attendance'],
            ['transfer_form_filled', "ENUM('Yes','No') DEFAULT 'No'", 'esi_applicability'],
            ['approved_by_district_head', "ENUM('Yes','No')", 'transfer_form_filled']
        ];
        
        $addedCount = 0;
        foreach ($columnsToAdd as $col) {
            $colName = $col[0];
            $colType = $col[1];
            $afterCol = $col[2];
            
            if (!in_array($colName, $existingColumns)) {
                try {
                    // Check if after column exists
                    $afterExists = in_array($afterCol, $existingColumns);
                    if ($afterExists) {
                        $sql = "ALTER TABLE employee_transfers ADD COLUMN `{$colName}` {$colType} AFTER `{$afterCol}`";
                    } else {
                        $sql = "ALTER TABLE employee_transfers ADD COLUMN `{$colName}` {$colType}";
                    }
                    $db->exec($sql);
                    echo "✓ Added column: {$colName}\n";
                    $addedCount++;
                } catch (Exception $e) {
                    echo "✗ Error adding column {$colName}: " . $e->getMessage() . "\n";
                }
            } else {
                echo "  - Column {$colName} already exists\n";
            }
        }
        
        if ($addedCount > 0) {
            echo "\n✓ Added {$addedCount} new column(s)\n";
        } else {
            echo "\n✓ All required columns already exist\n";
        }
    }
    
    // Verify final structure
    echo "\n=== Final Table Structure ===\n";
    $stmt = $db->query("DESCRIBE employee_transfers");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($columns as $col) {
        echo "  ✓ {$col['Field']} ({$col['Type']})";
        if ($col['Null'] === 'NO') echo " NOT NULL";
        if ($col['Default'] !== null) echo " DEFAULT '{$col['Default']}'";
        echo "\n";
    }
    
    echo "\n✅ Migration completed successfully!\n";
    
} catch (Exception $e) {
    echo "\n❌ ERROR: " . $e->getMessage() . "\n";
    echo "\nStack trace:\n" . $e->getTraceAsString() . "\n";
}

?>
        </pre>
        <a href="<?php echo BASE_URL; ?>/modules/transfer/list.php" style="display: inline-block; padding: 10px 20px; background: #007bff; color: white; text-decoration: none; border-radius: 3px; margin-top: 10px;">Go to Transfer Form</a>
    </div>
</body>
</html>

