<?php
/**
 * Run Overtime Monthly Grid Table Migration
 * This script creates the overtime_monthly_grid table if it doesn't exist
 */

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

header('Content-Type: text/html; charset=utf-8');
?>
<!DOCTYPE html>
<html>
<head>
    <title>Overtime Monthly Grid Migration</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; background: #f5f5f5; }
        .container { background: white; padding: 20px; border-radius: 5px; max-width: 800px; margin: 0 auto; }
        .success { color: #28a745; font-weight: bold; }
        .error { color: #dc3545; font-weight: bold; }
        .info { color: #17a2b8; }
        pre { background: #f8f9fa; padding: 15px; border-radius: 3px; overflow-x: auto; }
        .btn { display: inline-block; padding: 10px 20px; background: #007bff; color: white; text-decoration: none; border-radius: 3px; margin-top: 10px; }
        .btn:hover { background: #0056b3; }
    </style>
</head>
<body>
    <div class="container">
        <h2>Overtime Monthly Grid Table Migration</h2>
        <pre>
<?php

try {
    $db = getDB();
    
    echo "Starting migration...\n\n";
    
    // Check if table exists
    $checkTable = $db->query("SHOW TABLES LIKE 'overtime_monthly_grid'");
    $tableExists = $checkTable->rowCount() > 0;
    
    if ($tableExists) {
        echo "✓ Table 'overtime_monthly_grid' already exists.\n";
        echo "\n=== Current Table Structure ===\n";
        
        $stmt = $db->query("DESCRIBE overtime_monthly_grid");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        foreach ($columns as $col) {
            echo "  {$col['Field']} ({$col['Type']})";
            if ($col['Null'] === 'NO') echo " NOT NULL";
            if ($col['Default'] !== null) echo " DEFAULT '{$col['Default']}'";
            echo "\n";
        }
        
        echo "\n✅ SUCCESS: Table already exists!\n";
    } else {
        echo "✗ Table 'overtime_monthly_grid' does not exist. Creating...\n\n";
        
        // Create the table
        $createTableSQL = "
        CREATE TABLE IF NOT EXISTS `overtime_monthly_grid` (
          `overtime_grid_id` int(11) NOT NULL AUTO_INCREMENT,
          `site_id` int(11) NOT NULL,
          `employee_id` int(11) NOT NULL,
          `attendance_month` varchar(20) NOT NULL COMMENT 'Format: Jan-26',
          `emp_id` varchar(50),
          `designation` varchar(255),
          `day_1` decimal(5,2) DEFAULT 0.00,
          `day_2` decimal(5,2) DEFAULT 0.00,
          `day_3` decimal(5,2) DEFAULT 0.00,
          `day_4` decimal(5,2) DEFAULT 0.00,
          `day_5` decimal(5,2) DEFAULT 0.00,
          `day_6` decimal(5,2) DEFAULT 0.00,
          `day_7` decimal(5,2) DEFAULT 0.00,
          `day_8` decimal(5,2) DEFAULT 0.00,
          `day_9` decimal(5,2) DEFAULT 0.00,
          `day_10` decimal(5,2) DEFAULT 0.00,
          `day_11` decimal(5,2) DEFAULT 0.00,
          `day_12` decimal(5,2) DEFAULT 0.00,
          `day_13` decimal(5,2) DEFAULT 0.00,
          `day_14` decimal(5,2) DEFAULT 0.00,
          `day_15` decimal(5,2) DEFAULT 0.00,
          `day_16` decimal(5,2) DEFAULT 0.00,
          `day_17` decimal(5,2) DEFAULT 0.00,
          `day_18` decimal(5,2) DEFAULT 0.00,
          `day_19` decimal(5,2) DEFAULT 0.00,
          `day_20` decimal(5,2) DEFAULT 0.00,
          `day_21` decimal(5,2) DEFAULT 0.00,
          `day_22` decimal(5,2) DEFAULT 0.00,
          `day_23` decimal(5,2) DEFAULT 0.00,
          `day_24` decimal(5,2) DEFAULT 0.00,
          `day_25` decimal(5,2) DEFAULT 0.00,
          `day_26` decimal(5,2) DEFAULT 0.00,
          `day_27` decimal(5,2) DEFAULT 0.00,
          `day_28` decimal(5,2) DEFAULT 0.00,
          `day_29` decimal(5,2) DEFAULT 0.00,
          `day_30` decimal(5,2) DEFAULT 0.00,
          `day_31` decimal(5,2) DEFAULT 0.00,
          `overtime_rate` decimal(10,2) DEFAULT 0.00,
          `earn_amount` decimal(15,2) DEFAULT 0.00,
          `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
          `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`overtime_grid_id`),
          UNIQUE KEY `unique_emp_ot_month` (`employee_id`,`attendance_month`),
          KEY `fk_omg_site` (`site_id`),
          KEY `fk_omg_emp` (`employee_id`),
          CONSTRAINT `fk_omg_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
          CONSTRAINT `fk_omg_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ";
        
        $db->exec($createTableSQL);
        
        echo "✓ Table 'overtime_monthly_grid' created successfully!\n\n";
        
        // Verify table structure
        echo "=== Verifying Table Structure ===\n";
        $stmt = $db->query("DESCRIBE overtime_monthly_grid");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        foreach ($columns as $col) {
            echo "  ✓ {$col['Field']} ({$col['Type']})\n";
        }
        
        echo "\n✅ SUCCESS: Table created successfully!\n";
    }
    
    echo "\n=== Summary ===\n";
    echo "Table: overtime_monthly_grid\n";
    echo "Status: " . ($tableExists ? "Already exists" : "Created") . "\n";
    echo "\n✅ Migration completed successfully!\n";
    
} catch (Exception $e) {
    echo "\n❌ ERROR: " . $e->getMessage() . "\n";
    echo "\nStack trace:\n" . $e->getTraceAsString() . "\n";
}

?>
        </pre>
        <a href="<?php echo BASE_URL; ?>/modules/overtime/list.php" class="btn">Go to Overtime Sheet</a>
    </div>
</body>
</html>

