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

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

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

try {
    $db = getDB();
    
    // Check if table exists
    $tableExists = $db->query("SHOW TABLES LIKE 'employee_master_change'")->rowCount() > 0;
    
    if (!$tableExists) {
        echo "Table 'employee_master_change' does not exist. Creating it...\n";
        
        $db->exec("
            CREATE TABLE `employee_master_change` (
                `change_id` int(11) NOT NULL AUTO_INCREMENT,
                `site_id` int(11) NOT NULL,
                `employee_id` int(11) DEFAULT NULL,
                `sl_no` int(11) DEFAULT NULL,
                `emp_id` varchar(50) DEFAULT NULL,
                `name` varchar(255) DEFAULT NULL,
                `designation` varchar(255) DEFAULT NULL,
                `site_name` varchar(255) DEFAULT NULL,
                `category` varchar(100) DEFAULT NULL,
                `change_month` int(11) DEFAULT NULL,
                `change_year` int(11) DEFAULT NULL,
                `present_address` text,
                `permanent_address` text,
                `pay_mode` enum('Bank','Hold') DEFAULT 'Bank',
                `bank_name` varchar(255) DEFAULT NULL,
                `bank_account_number` varchar(100) DEFAULT NULL,
                `bank_ref_number` varchar(100) DEFAULT NULL,
                `bank_ifsc_code` varchar(50) DEFAULT NULL,
                `esic_number` varchar(50) DEFAULT NULL,
                `pf_number` varchar(50) DEFAULT NULL,
                `remarks` text,
                `created_by` int(11) DEFAULT NULL,
                `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`change_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        ");
        
        echo "✅ Table created successfully with AUTO_INCREMENT!\n";
    } else {
        echo "Table 'employee_master_change' exists. Checking structure...\n";
        
        // Check change_id column
        $colCheck = $db->query("SHOW COLUMNS FROM employee_master_change LIKE 'change_id'");
        $colInfo = $colCheck->fetch(PDO::FETCH_ASSOC);
        
        if ($colInfo) {
            echo "Column 'change_id' found:\n";
            echo "  - Type: " . $colInfo['Type'] . "\n";
            echo "  - Key: " . $colInfo['Key'] . "\n";
            echo "  - Extra: " . $colInfo['Extra'] . "\n";
            
            if (strpos(strtolower($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 = (int)$db->query("SELECT COUNT(*) FROM employee_master_change WHERE change_id IS NULL OR change_id = 0")->fetchColumn();
                echo "Found {$nullCount} rows with NULL or 0 change_id\n";
                
                if ($nullCount > 0) {
                    $maxId = (int)$db->query("SELECT COALESCE(MAX(change_id), 0) FROM employee_master_change WHERE change_id > 0")->fetchColumn();
                    echo "Max existing change_id: {$maxId}\n";
                    
                    for ($i = 0; $i < $nullCount; $i++) {
                        $maxId++;
                        $db->exec("UPDATE employee_master_change SET change_id = {$maxId} WHERE (change_id IS NULL OR change_id = 0) LIMIT 1");
                    }
                    echo "Updated {$nullCount} rows with new change_id values\n";
                }
                
                // Now add AUTO_INCREMENT
                try {
                    $db->exec("ALTER TABLE employee_master_change MODIFY COLUMN `change_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_master_change MODIFY COLUMN `change_id` int(11) NOT NULL AUTO_INCREMENT;\n";
                }
            }
        } else {
            echo "❌ Column 'change_id' not found! Table structure is incorrect.\n";
        }
    }
    
    // Show current table structure
    echo "\n--- Current Table Structure ---\n";
    $structure = $db->query("DESCRIBE employee_master_change");
    while ($row = $structure->fetch(PDO::FETCH_ASSOC)) {
        echo sprintf("%-25s %-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_master_change")->fetchColumn();
    echo "\nTotal rows in table: {$count}\n";
    
    echo "\n✅ Fix script completed!\n";
    
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
}

echo "</pre>";
echo "<p><a href='modules/employee/employee_master_change_list.php'>Go to Employee Master Change List</a></p>";
?>
