<?php
/**
 * Run Overtime Default Rates Table Migration
 */

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

header('Content-Type: text/html; charset=utf-8');
?>
<!DOCTYPE html>
<html>
<head>
    <title>Overtime Default Rates 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; }
        pre { background: #f8f9fa; padding: 15px; border-radius: 3px; overflow-x: auto; }
    </style>
</head>
<body>
    <div class="container">
        <h2>Overtime Default Rates Table Migration</h2>
        <pre>
<?php

try {
    $db = getDB();
    
    echo "Starting migration...\n\n";
    
    // Check if table exists
    $checkTable = $db->query("SHOW TABLES LIKE 'overtime_default_rates'");
    $tableExists = $checkTable->rowCount() > 0;
    
    if ($tableExists) {
        echo "✓ Table 'overtime_default_rates' already exists.\n";
        
        // Show current structure
        echo "\n=== Current Table Structure ===\n";
        $stmt = $db->query("DESCRIBE overtime_default_rates");
        $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";
        }
        
        // Show existing data
        $stmt = $db->query("SELECT * FROM overtime_default_rates");
        $existing = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (count($existing) > 0) {
            echo "\n=== Existing Default Rates ===\n";
            foreach ($existing as $row) {
                echo "  Site ID: {$row['site_id']}, Rate: {$row['overtime_rate']}, Active: " . ($row['is_active'] ? 'Yes' : 'No') . "\n";
            }
        } else {
            echo "\nNo default rates found in table.\n";
        }
        
        echo "\n✅ SUCCESS: Table already exists!\n";
    } else {
        echo "✗ Table does not exist. Creating...\n\n";
        
        // Drop table if exists to avoid constraint conflicts
        try {
            $db->exec("DROP TABLE IF EXISTS `overtime_default_rates`");
            echo "✓ Dropped existing table (if any) to avoid conflicts\n";
        } catch (Exception $e) {
            echo "Note: Could not drop table (may not exist): " . $e->getMessage() . "\n";
        }
        
        $createTableSQL = "
        CREATE TABLE `overtime_default_rates` (
          `default_rate_id` int(11) NOT NULL AUTO_INCREMENT,
          `site_id` int(11) NOT NULL,
          `overtime_rate` decimal(10,2) NOT NULL DEFAULT 0.00,
          `is_active` tinyint(1) DEFAULT 1,
          `created_by` int(11) DEFAULT NULL,
          `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
          `updated_by` int(11) DEFAULT NULL,
          `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`default_rate_id`),
          UNIQUE KEY `unique_site_default_rate` (`site_id`),
          KEY `fk_ot_def_rate_site` (`site_id`),
          KEY `fk_ot_def_rate_created_by` (`created_by`),
          KEY `fk_ot_def_rate_updated_by` (`updated_by`),
          CONSTRAINT `fk_ot_def_rate_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
          CONSTRAINT `fk_ot_def_rate_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
          CONSTRAINT `fk_ot_def_rate_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ";
        
        $db->exec($createTableSQL);
        echo "✓ Table created successfully!\n\n";
        
        // Verify table structure
        echo "=== Verifying Table Structure ===\n";
        $stmt = $db->query("DESCRIBE overtime_default_rates");
        $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_default_rates\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/rates_designation.php" style="display: inline-block; padding: 10px 20px; background: #007bff; color: white; text-decoration: none; border-radius: 3px; margin-top: 10px;">Go to Overtime Rates Settings</a>
    </div>
</body>
</html>

