<?php
/**
 * Fix employee_transfers table - Add AUTO_INCREMENT to transfer_id
 * Run this script once to fix the table structure
 */

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

echo "<h2>Fixing employee_transfers Table</h2>";
echo "<pre>";

try {
    $db = getDB();
    
    // Check if table exists
    $tableExists = $db->query("SHOW TABLES LIKE 'employee_transfers'")->rowCount() > 0;
    
    if (!$tableExists) {
        echo "Table 'employee_transfers' does not exist. Creating it...\n";
        
        $db->exec("
            CREATE TABLE IF NOT EXISTS `employee_transfers` (
              `transfer_id` int(11) NOT NULL AUTO_INCREMENT,
              `employee_id` int(11) NOT NULL,
              `from_site_id` int(11) NOT NULL,
              `to_site_id` int(11) NOT NULL,
              `transfer_date` date NOT NULL,
              `transfer_reason` text,
              `employee_category` varchar(50) DEFAULT NULL,
              `from_location` varchar(255) DEFAULT NULL,
              `to_location` varchar(255) DEFAULT 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_status` enum('pending','approved','completed','cancelled') DEFAULT 'pending',
              `created_by` int(11) DEFAULT NULL,
              `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
              PRIMARY KEY (`transfer_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ");
        
        echo "✅ Table created successfully with AUTO_INCREMENT!\n";
    } else {
        echo "Table 'employee_transfers' exists. Checking structure...\n";
        
        // Check transfer_id column
        $colCheck = $db->query("SHOW COLUMNS FROM employee_transfers LIKE 'transfer_id'");
        $colInfo = $colCheck->fetch(PDO::FETCH_ASSOC);
        
        if ($colInfo) {
            echo "Column 'transfer_id' found:\n";
            echo "  - Type: " . $colInfo['Type'] . "\n";
            echo "  - Key: " . $colInfo['Key'] . "\n";
            echo "  - Extra: " . $colInfo['Extra'] . "\n";
            
            if (strpos($colInfo['Extra'], 'auto_increment') !== false) {
                echo "✅ AUTO_INCREMENT is already set!\n";
            } else {
                echo "⚠️ AUTO_INCREMENT is NOT set. Fixing...\n";
                
                // Check for NULL or 0 values
                $nullCount = $db->query("SELECT COUNT(*) FROM employee_transfers WHERE transfer_id IS NULL OR transfer_id = 0")->fetchColumn();
                echo "Found {$nullCount} rows with NULL or 0 transfer_id\n";
                
                if ($nullCount > 0) {
                    $maxId = (int)$db->query("SELECT COALESCE(MAX(transfer_id), 0) FROM employee_transfers WHERE transfer_id > 0")->fetchColumn();
                    echo "Max existing transfer_id: {$maxId}\n";
                    
                    for ($i = 0; $i < $nullCount; $i++) {
                        $maxId++;
                        $db->exec("UPDATE employee_transfers SET transfer_id = {$maxId} WHERE (transfer_id IS NULL OR transfer_id = 0) LIMIT 1");
                    }
                    echo "Updated {$nullCount} rows with new transfer_id values\n";
                }
                
                // Check for duplicate transfer_id values
                $dupCheck = $db->query("SELECT transfer_id, COUNT(*) as cnt FROM employee_transfers GROUP BY transfer_id HAVING cnt > 1");
                $duplicates = $dupCheck->fetchAll(PDO::FETCH_ASSOC);
                
                if (count($duplicates) > 0) {
                    echo "⚠️ Found duplicate transfer_id values. Fixing...\n";
                    $maxId = (int)$db->query("SELECT COALESCE(MAX(transfer_id), 0) FROM employee_transfers")->fetchColumn();
                    
                    foreach ($duplicates as $dup) {
                        $dupRows = $db->query("SELECT * FROM employee_transfers WHERE transfer_id = " . (int)$dup['transfer_id'])->fetchAll();
                        $first = true;
                        foreach ($dupRows as $row) {
                            if ($first) {
                                $first = false;
                                continue; // Keep first one
                            }
                            $maxId++;
                            // We need to identify the row uniquely - use all columns
                            $db->exec("UPDATE employee_transfers SET transfer_id = {$maxId} WHERE transfer_id = " . (int)$dup['transfer_id'] . " LIMIT 1");
                        }
                    }
                    echo "Fixed duplicate transfer_id values\n";
                }
                
                // Now add AUTO_INCREMENT
                try {
                    $db->exec("ALTER TABLE employee_transfers MODIFY COLUMN `transfer_id` int(11) NOT NULL AUTO_INCREMENT");
                    echo "✅ AUTO_INCREMENT added successfully!\n";
                } catch (Exception $e) {
                    echo "❌ Error adding AUTO_INCREMENT: " . $e->getMessage() . "\n";
                    echo "\nTry running this SQL manually in phpMyAdmin:\n";
                    echo "ALTER TABLE employee_transfers MODIFY COLUMN `transfer_id` int(11) NOT NULL AUTO_INCREMENT;\n";
                }
            }
        } else {
            echo "❌ Column 'transfer_id' not found! Table structure is incorrect.\n";
        }
    }
    
    // Show current table structure
    echo "\n--- Current Table Structure ---\n";
    $structure = $db->query("DESCRIBE employee_transfers");
    while ($row = $structure->fetch(PDO::FETCH_ASSOC)) {
        echo sprintf("%-30s %-20s %-5s %-10s %-20s\n", 
            $row['Field'], $row['Type'], $row['Null'], $row['Key'], $row['Extra']);
    }
    
    // Show row count
    $count = $db->query("SELECT COUNT(*) FROM employee_transfers")->fetchColumn();
    echo "\nTotal rows in table: {$count}\n";
    
    echo "\n✅ Fix script completed!\n";
    
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
}

echo "</pre>";
?>
