<?php
/**
 * FIX: attendance_monthly_grid table - PRIMARY KEY + AUTO_INCREMENT
 */
require_once __DIR__ . '/includes/init.php';

echo "<h2>Fixing attendance_monthly_grid Table</h2>";

try {
    $db = getDB();
    
    // Check current state
    $result = $db->query("SHOW COLUMNS FROM attendance_monthly_grid LIKE 'attendance_grid_id'");
    $col = $result->fetch(PDO::FETCH_ASSOC);
    
    echo "<p>Current - Key: " . ($col['Key'] ?: 'NONE') . ", Extra: " . ($col['Extra'] ?: 'NONE') . "</p>";
    
    $hasPK = ($col['Key'] === 'PRI');
    $hasAI = (stripos($col['Extra'], 'auto_increment') !== false);
    
    if ($hasPK && $hasAI) {
        echo "<p style='color:green;'>✅ Table is already OK!</p>";
    } else {
        // Step 1: Add PRIMARY KEY if missing
        if (!$hasPK) {
            echo "<p>Adding PRIMARY KEY...</p>";
            try {
                $db->exec("ALTER TABLE attendance_monthly_grid ADD PRIMARY KEY (attendance_grid_id)");
                echo "<p style='color:green;'>✅ PRIMARY KEY added!</p>";
            } catch (Exception $e) {
                echo "<p style='color:orange;'>PK Error: " . $e->getMessage() . "</p>";
            }
        }
        
        // Step 2: Add AUTO_INCREMENT
        echo "<p>Adding AUTO_INCREMENT...</p>";
        try {
            $db->exec("ALTER TABLE attendance_monthly_grid MODIFY attendance_grid_id INT NOT NULL AUTO_INCREMENT");
            echo "<p style='color:green;'>✅ AUTO_INCREMENT added!</p>";
        } catch (Exception $e) {
            echo "<p style='color:red;'>AI Error: " . $e->getMessage() . "</p>";
        }
    }
    
    // Verify
    $result = $db->query("SHOW COLUMNS FROM attendance_monthly_grid LIKE 'attendance_grid_id'");
    $col = $result->fetch(PDO::FETCH_ASSOC);
    echo "<p>After Fix - Key: " . ($col['Key'] ?: 'NONE') . ", Extra: " . ($col['Extra'] ?: 'NONE') . "</p>";
    
    if (stripos($col['Extra'], 'auto_increment') !== false) {
        echo "<h3 style='color:green;'>✅ SUCCESS! Table Fixed!</h3>";
        echo "<p><a href='modules/attendance/attendance_excel_grid.php'>Go to Attendance →</a></p>";
    } else {
        echo "<h3 style='color:red;'>❌ Please run in phpMyAdmin:</h3>";
        echo "<pre>ALTER TABLE attendance_monthly_grid ADD PRIMARY KEY (attendance_grid_id);\n";
        echo "ALTER TABLE attendance_monthly_grid MODIFY attendance_grid_id INT NOT NULL AUTO_INCREMENT;</pre>";
    }
} catch (Exception $e) {
    echo "<p style='color:red;'>Error: " . $e->getMessage() . "</p>";
}
?>
