<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
$_SERVER['DOCUMENT_ROOT'] = 'c:/Users/light/sam/5130';
require_once 'c:/Users/light/sam/5130/lib/mydb.php';

echo "Starting DB Schema Fix...\n";

try {
    // Connect directly to avoid .env overwriting with 'mysql' host
    $dsn = "mysql:host=127.0.0.1;dbname=chandj;charset=utf8";
    $pdo = new PDO($dsn, 'root', 'root');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $DB = 'chandj';

    // 1. Fix output table
    echo "Fixing 'output' table...\n";
    // Check if auto_increment exists or just try to apply it
    // Usually 'MODIFY num INT AUTO_INCREMENT' works. Assuming 'num' is int.
    // We also need to make sure we don't break existing keys.
    // Safest is "MODIFY num INT NOT NULL AUTO_INCREMENT"
    $sql = "ALTER TABLE {$DB}.output MODIFY num INT NOT NULL AUTO_INCREMENT";
    $pdo->exec($sql);
    echo " - 'output.num' set to AUTO_INCREMENT successfully.\n";
    
} catch (Exception $e) {
    echo " - Error fixing 'output': " . $e->getMessage() . "\n";
}

try {
    // 2. Fix geo_attendance table
    // First, find the primary key column name
    echo "Fixing 'geo_attendance' table...\n";
    $stmt = $pdo->query("SHOW KEYS FROM {$DB}.geo_attendance WHERE Key_name = 'PRIMARY'");
    $pk = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($pk) {
        $pkColumn = $pk['Column_name'];
        echo " - Found Primary Key column: $pkColumn\n";
        
        $sql = "ALTER TABLE {$DB}.geo_attendance MODIFY $pkColumn INT NOT NULL AUTO_INCREMENT";
        $pdo->exec($sql);
        echo " - 'geo_attendance.$pkColumn' set to AUTO_INCREMENT successfully.\n";
    } else {
        echo " - Could not find Primary Key for geo_attendance.\n";
    }

} catch (Exception $e) {
    echo " - Error fixing 'geo_attendance': " . $e->getMessage() . "\n";
}

echo "Done.\n";
?>