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

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

header('Content-Type: text/html; charset=utf-8');
?>
<!DOCTYPE html>
<html>
<head>
    <title>Overtime Designation 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 Designation Rates Migration</h2>
        <pre>
<?php

try {
    $db = getDB();
    
    echo "Starting migration...\n\n";
    
    // Check if table exists
    $checkTable = $db->query("SHOW TABLES LIKE 'overtime_designation_rates'");
    $tableExists = $checkTable->rowCount() > 0;
    
    if ($tableExists) {
        echo "✓ Table 'overtime_designation_rates' already exists.\n";
    } else {
        echo "✗ Table does not exist. Creating...\n\n";
        
        $createTableSQL = "
        CREATE TABLE IF NOT EXISTS `overtime_designation_rates` (
          `rate_id` int(11) NOT NULL AUTO_INCREMENT,
          `site_id` int(11) NOT NULL,
          `designation_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),
          `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
          `updated_by` int(11),
          `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`rate_id`),
          UNIQUE KEY `unique_site_designation` (`site_id`, `designation_id`),
          KEY `fk_ot_dr_site` (`site_id`),
          KEY `fk_ot_dr_designation` (`designation_id`),
          KEY `fk_ot_dr_created_by` (`created_by`),
          KEY `fk_ot_dr_updated_by` (`updated_by`),
          CONSTRAINT `fk_ot_dr_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
          CONSTRAINT `fk_ot_dr_designation` FOREIGN KEY (`designation_id`) REFERENCES `designations` (`designation_id`) ON DELETE CASCADE,
          CONSTRAINT `fk_ot_dr_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
          CONSTRAINT `fk_ot_dr_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";
    }
    
    echo "✅ Migration completed successfully!\n";
    
} catch (Exception $e) {
    echo "\n❌ ERROR: " . $e->getMessage() . "\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 Designation Rates Settings</a>
    </div>
</body>
</html>

