<?php
/**
 * Fix attendance_monthly_grid table - ensure attendance_grid_id is AUTO_INCREMENT
 */

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

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

try {
    $db = getDB();
    
    // Check current table structure
    echo "<h3>Current Table Structure:</h3>";
    $result = $db->query("SHOW CREATE TABLE attendance_monthly_grid");
    $tableInfo = $result->fetch(PDO::FETCH_ASSOC);
    echo "<pre>" . htmlspecialchars($tableInfo['Create Table'] ?? 'Table not found') . "</pre>";
    
    // Check if attendance_grid_id has AUTO_INCREMENT
    $result = $db->query("SHOW COLUMNS FROM attendance_monthly_grid LIKE 'attendance_grid_id'");
    $columnInfo = $result->fetch(PDO::FETCH_ASSOC);
    
    echo "<h3>Column Info:</h3>";
    echo "<pre>" . print_r($columnInfo, true) . "</pre>";
    
    $hasAutoIncrement = stripos($columnInfo['Extra'] ?? '', 'auto_increment') !== false;
    
    if (!$hasAutoIncrement) {
        echo "<p style='color: red;'><strong>Issue Found:</strong> attendance_grid_id does NOT have AUTO_INCREMENT!</p>";
        echo "<p>Attempting to fix...</p>";
        
        // Try to fix the column
        try {
            $db->exec("ALTER TABLE `attendance_monthly_grid` MODIFY COLUMN `attendance_grid_id` INT(11) NOT NULL AUTO_INCREMENT");
            echo "<p style='color: green;'><strong>SUCCESS:</strong> Column fixed with AUTO_INCREMENT!</p>";
        } catch (Exception $e) {
            echo "<p style='color: orange;'>Method 1 failed: " . $e->getMessage() . "</p>";
            
            // Try alternative method
            try {
                $db->exec("ALTER TABLE `attendance_monthly_grid` CHANGE `attendance_grid_id` `attendance_grid_id` INT(11) NOT NULL AUTO_INCREMENT FIRST");
                echo "<p style='color: green;'><strong>SUCCESS:</strong> Column fixed with alternative method!</p>";
            } catch (Exception $e2) {
                echo "<p style='color: red;'>Method 2 also failed: " . $e2->getMessage() . "</p>";
            }
        }
        
        // Verify fix
        $result = $db->query("SHOW COLUMNS FROM attendance_monthly_grid LIKE 'attendance_grid_id'");
        $columnInfo = $result->fetch(PDO::FETCH_ASSOC);
        echo "<h3>After Fix - Column Info:</h3>";
        echo "<pre>" . print_r($columnInfo, true) . "</pre>";
        
    } else {
        echo "<p style='color: green;'><strong>OK:</strong> attendance_grid_id already has AUTO_INCREMENT.</p>";
    }
    
    // Show final table structure
    echo "<h3>Final Table Structure:</h3>";
    $result = $db->query("SHOW CREATE TABLE attendance_monthly_grid");
    $tableInfo = $result->fetch(PDO::FETCH_ASSOC);
    echo "<pre>" . htmlspecialchars($tableInfo['Create Table'] ?? 'Error') . "</pre>";
    
    echo "<p><a href='modules/attendance/attendance_excel_grid.php'>Go to Attendance Page</a></p>";
    
} catch (Exception $e) {
    echo "<p style='color: red;'><strong>Error:</strong> " . $e->getMessage() . "</p>";
}
?>
