<?php
/**
 * Fix ALL tables with AUTO_INCREMENT issues
 * Run this script once to fix all table structures
 */

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

echo "<h2>Fixing ALL Tables with AUTO_INCREMENT Issues</h2>";
echo "<pre>";

$tables = [
    'attendance_periods' => 'period_id',
    'employee_master_change' => 'change_id',
    'employee_transfers' => 'transfer_id',
    'attendance_monthly_grid' => 'attendance_grid_id',
    'employee_master_complete' => 'master_id',
];

try {
    $db = getDB();
    
    foreach ($tables as $tableName => $columnName) {
        echo "\n========================================\n";
        echo "Checking table: {$tableName}\n";
        echo "========================================\n";
        
        // Check if table exists
        $tableExists = $db->query("SHOW TABLES LIKE '{$tableName}'")->rowCount() > 0;
        
        if (!$tableExists) {
            echo "⚠️ Table '{$tableName}' does not exist. Skipping...\n";
            continue;
        }
        
        // Check column
        $colCheck = $db->query("SHOW COLUMNS FROM {$tableName} LIKE '{$columnName}'");
        $colInfo = $colCheck->fetch(PDO::FETCH_ASSOC);
        
        if (!$colInfo) {
            echo "⚠️ Column '{$columnName}' not found in '{$tableName}'. Skipping...\n";
            continue;
        }
        
        echo "Column '{$columnName}' 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";
            continue;
        }
        
        echo "⚠️ AUTO_INCREMENT is NOT set. Fixing...\n";
        
        // Check for NULL or 0 values
        $nullCount = (int)$db->query("SELECT COUNT(*) FROM {$tableName} WHERE {$columnName} IS NULL OR {$columnName} = 0")->fetchColumn();
        echo "Found {$nullCount} rows with NULL or 0 {$columnName}\n";
        
        if ($nullCount > 0) {
            $maxId = (int)$db->query("SELECT COALESCE(MAX({$columnName}), 0) FROM {$tableName} WHERE {$columnName} > 0")->fetchColumn();
            echo "Max existing {$columnName}: {$maxId}\n";
            
            for ($i = 0; $i < $nullCount; $i++) {
                $maxId++;
                $db->exec("UPDATE {$tableName} SET {$columnName} = {$maxId} WHERE ({$columnName} IS NULL OR {$columnName} = 0) LIMIT 1");
            }
            echo "Updated {$nullCount} rows with new {$columnName} values\n";
        }
        
        // Now add AUTO_INCREMENT
        try {
            $db->exec("ALTER TABLE {$tableName} MODIFY COLUMN `{$columnName}` 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 {$tableName} MODIFY COLUMN `{$columnName}` int(11) NOT NULL AUTO_INCREMENT;\n";
        }
    }
    
    echo "\n========================================\n";
    echo "✅ Fix script completed!\n";
    echo "========================================\n";
    
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
}

echo "</pre>";
echo "<p><a href='dashboard.php'>Go to Dashboard</a></p>";
?>
