<?php
/**
 * Fix employee_master_sheet table - Add AUTO_INCREMENT to master_id
 */

require_once __DIR__ . '/includes/init.php';
requireLogin();

if (!isSuperAdmin()) {
    die('Access denied. Super Admin only.');
}

$db = getDB();

echo "<h2>Fixing Employee Master Sheet Table</h2>";
echo "<pre>";

try {
    // Check if table exists
    $tableExists = $db->query("SHOW TABLES LIKE 'employee_master_sheet'")->rowCount() > 0;
    
    if (!$tableExists) {
        echo "Table 'employee_master_sheet' does not exist. Creating it...\n";
        
        $db->exec("
            CREATE TABLE IF NOT EXISTS `employee_master_sheet` (
                `master_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,
                `employee_name` varchar(255) DEFAULT NULL,
                `category` varchar(100) DEFAULT NULL,
                `bank_ac_no` varchar(100) DEFAULT NULL,
                `bank_name` varchar(255) DEFAULT NULL,
                `department` varchar(255) DEFAULT NULL,
                `designation` varchar(255) DEFAULT NULL,
                `site` varchar(255) DEFAULT NULL,
                `contact_number` varchar(50) DEFAULT NULL,
                `fathers_name` varchar(255) DEFAULT NULL,
                `date_of_birth` date DEFAULT NULL,
                `date_of_joining` date DEFAULT NULL,
                `date_of_leaving` date DEFAULT NULL,
                `reason_for_leaving` text DEFAULT NULL,
                `pf_number` varchar(100) DEFAULT NULL,
                `uan_number` varchar(100) DEFAULT NULL,
                `insurance_number` varchar(100) DEFAULT NULL,
                `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`master_id`),
                KEY `idx_site_id` (`site_id`),
                KEY `idx_emp_id` (`emp_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        
        echo "Table created successfully!\n";
    } else {
        echo "Table exists. Checking structure...\n\n";
        
        // Show current structure
        $columns = $db->query("SHOW COLUMNS FROM employee_master_sheet")->fetchAll(PDO::FETCH_ASSOC);
        echo "Current columns:\n";
        foreach ($columns as $col) {
            echo "  - {$col['Field']}: {$col['Type']} | Key: {$col['Key']} | Extra: {$col['Extra']}\n";
        }
        echo "\n";
        
        // Check master_id column
        $masterIdCol = null;
        foreach ($columns as $col) {
            if ($col['Field'] === 'master_id') {
                $masterIdCol = $col;
                break;
            }
        }
        
        if (!$masterIdCol) {
            echo "ERROR: master_id column not found!\n";
            echo "Adding master_id column...\n";
            
            $db->exec("ALTER TABLE employee_master_sheet ADD COLUMN `master_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST");
            echo "master_id column added successfully!\n";
        } else {
            echo "master_id column found.\n";
            echo "  Type: {$masterIdCol['Type']}\n";
            echo "  Key: {$masterIdCol['Key']}\n";
            echo "  Extra: {$masterIdCol['Extra']}\n\n";
            
            if (strpos($masterIdCol['Extra'], 'auto_increment') === false) {
                echo "WARNING: master_id does NOT have AUTO_INCREMENT!\n";
                echo "Fixing...\n\n";
                
                // First, check for NULL or 0 values
                $nullCount = $db->query("SELECT COUNT(*) FROM employee_master_sheet WHERE master_id IS NULL OR master_id = 0")->fetchColumn();
                echo "Records with NULL/0 master_id: {$nullCount}\n";
                
                if ($nullCount > 0) {
                    echo "Updating NULL/0 values...\n";
                    $maxId = (int)$db->query("SELECT COALESCE(MAX(master_id), 0) FROM employee_master_sheet WHERE master_id > 0")->fetchColumn();
                    echo "Current max master_id: {$maxId}\n";
                    
                    for ($i = 0; $i < $nullCount; $i++) {
                        $maxId++;
                        $db->exec("UPDATE employee_master_sheet SET master_id = {$maxId} WHERE (master_id IS NULL OR master_id = 0) LIMIT 1");
                    }
                    echo "Updated {$nullCount} records.\n";
                }
                
                // Now add AUTO_INCREMENT
                echo "\nAdding AUTO_INCREMENT to master_id...\n";
                
                try {
                    $db->exec("ALTER TABLE employee_master_sheet MODIFY COLUMN `master_id` int(11) NOT NULL AUTO_INCREMENT");
                    echo "SUCCESS: AUTO_INCREMENT added!\n";
                } catch (Exception $e) {
                    echo "Error: " . $e->getMessage() . "\n";
                    
                    // Try alternative approach
                    echo "\nTrying alternative approach...\n";
                    try {
                        // Check if there's a primary key
                        $pkCheck = $db->query("SHOW KEYS FROM employee_master_sheet WHERE Key_name = 'PRIMARY'")->fetchAll();
                        
                        if (empty($pkCheck)) {
                            echo "No primary key found. Adding primary key...\n";
                            $db->exec("ALTER TABLE employee_master_sheet ADD PRIMARY KEY (master_id)");
                        }
                        
                        $db->exec("ALTER TABLE employee_master_sheet MODIFY COLUMN `master_id` int(11) NOT NULL AUTO_INCREMENT");
                        echo "SUCCESS: AUTO_INCREMENT added!\n";
                    } catch (Exception $e2) {
                        echo "Error: " . $e2->getMessage() . "\n";
                    }
                }
            } else {
                echo "master_id already has AUTO_INCREMENT. No fix needed.\n";
            }
        }
    }
    
    // Verify final structure
    echo "\n\nFinal verification:\n";
    $finalCheck = $db->query("SHOW COLUMNS FROM employee_master_sheet LIKE 'master_id'")->fetch(PDO::FETCH_ASSOC);
    echo "master_id: {$finalCheck['Type']} | Key: {$finalCheck['Key']} | Extra: {$finalCheck['Extra']}\n";
    
    if (strpos($finalCheck['Extra'], 'auto_increment') !== false) {
        echo "\n✅ Table is now fixed and ready to use!\n";
    } else {
        echo "\n❌ AUTO_INCREMENT still not set. Please run the SQL manually in phpMyAdmin:\n";
        echo "ALTER TABLE employee_master_sheet MODIFY COLUMN `master_id` int(11) NOT NULL AUTO_INCREMENT;\n";
    }
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

echo "</pre>";
echo "<p><a href='" . BASE_URL . "/modules/employee/employee_master_complete.php'>Back to Employee Master Sheet</a></p>";
?>
