<?php
/**
 * Enterprise HRM ERP System - Overtime Rates by Designation
 * Admin/HR can set overtime rates based on designations
 */

require_once __DIR__ . '/../../includes/init.php';
requireLogin();

if (!hasAdminAccess() && !hasPermission('OVERTIME_RATES_MANAGE')) {
    $_SESSION['error'] = 'You do not have permission to access this resource.';
    header('Location: ' . BASE_URL . '/dashboard.php');
    exit;
}

$pageTitle = 'Overtime Rates by Designation';
$currentModule = 'overtime';
$currentTab = 'rates';

$siteId = getCurrentSiteId();
$db = getDB();
$errors = [];
$success = false;

// Auto-create tables if they don't exist
try {
    // Create overtime_designation_rates table
    $checkTable = $db->query("SHOW TABLES LIKE 'overtime_designation_rates'");
    if ($checkTable->rowCount() == 0) {
        $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`),
          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
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ";
        $db->exec($createTableSQL);
    }
    
    // Create overtime_default_rates table
    $checkDefaultTable = $db->query("SHOW TABLES LIKE 'overtime_default_rates'");
    if ($checkDefaultTable->rowCount() == 0) {
        // Drop if exists to avoid conflicts
        try {
            $db->exec("DROP TABLE IF EXISTS `overtime_default_rates`");
        } catch (Exception $e) {
            // Ignore if drop fails
        }
        
        $createDefaultTableSQL = "
        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($createDefaultTableSQL);
    }
} catch (Exception $e) {
    error_log("Error creating overtime tables: " . $e->getMessage());
}

// Handle form submission - Save/Update rates
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_rates'])) {
    try {
        $userId = getCurrentUserId();
        $defaultRate = isset($_POST['default_rate']) ? (float)$_POST['default_rate'] : 0;
        $rates = $_POST['rates'] ?? [];
        
        $db->beginTransaction();
        
        // Save default rate to overtime_default_rates table
        try {
            // Check if record exists
            $checkStmt = $db->prepare("SELECT default_rate_id FROM overtime_default_rates WHERE site_id = ?");
            $checkStmt->execute([$siteId]);
            $existing = $checkStmt->fetch();
            
            if ($existing) {
                // Update existing record
                $updateStmt = $db->prepare("
                    UPDATE overtime_default_rates 
                    SET overtime_rate = ?, is_active = 1, updated_by = ?, updated_at = NOW()
                    WHERE site_id = ?
                ");
                $updateStmt->execute([$defaultRate, $userId, $siteId]);
            } else {
                // Insert new record
                $insertStmt = $db->prepare("
                    INSERT INTO overtime_default_rates 
                    (site_id, overtime_rate, is_active, created_by, updated_by)
                    VALUES (?, ?, 1, ?, ?)
                ");
                $insertStmt->execute([$siteId, $defaultRate, $userId, $userId]);
            }
        } catch (Exception $e) {
            error_log("Error saving default rate: " . $e->getMessage());
            throw $e; // Re-throw to trigger rollback
        }
        
        // Save designation-specific rates
        foreach ($rates as $designationId => $rateData) {
            $designationId = (int)$designationId;
            $overtimeRate = isset($rateData['rate']) ? (float)$rateData['rate'] : 0;
            $isActive = isset($rateData['active']) ? 1 : 0;
            
            if ($designationId <= 0) continue;
            
            // Check if rate exists
            $checkStmt = $db->prepare("SELECT rate_id FROM overtime_designation_rates WHERE site_id = ? AND designation_id = ?");
            $checkStmt->execute([$siteId, $designationId]);
            $existing = $checkStmt->fetch();
            
            if ($existing) {
                // Update
                $updateStmt = $db->prepare("
                    UPDATE overtime_designation_rates 
                    SET overtime_rate = ?, is_active = ?, updated_by = ?, updated_at = NOW()
                    WHERE site_id = ? AND designation_id = ?
                ");
                $updateStmt->execute([$overtimeRate, $isActive, $userId, $siteId, $designationId]);
            } else {
                // Insert
                $insertStmt = $db->prepare("
                    INSERT INTO overtime_designation_rates 
                    (site_id, designation_id, overtime_rate, is_active, created_by, updated_by)
                    VALUES (?, ?, ?, ?, ?, ?)
                ");
                $insertStmt->execute([$siteId, $designationId, $overtimeRate, $isActive, $userId, $userId]);
            }
        }
        
        $db->commit();
        $success = true;
        $_SESSION['success_message'] = 'Overtime rates saved successfully!';
        
    } catch (Exception $e) {
        if ($db->inTransaction()) {
            $db->rollBack();
        }
        error_log("Error saving designation rates: " . $e->getMessage());
        $errors[] = 'Error saving rates: ' . $e->getMessage();
    }
}

// Get all designations
try {
    $stmt = $db->prepare("
        SELECT d.designation_id, d.designation_name
        FROM designations d
        WHERE d.site_id = ?
        ORDER BY d.designation_name
    ");
    $stmt->execute([$siteId]);
    $designations = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
    error_log("Error loading designations: " . $e->getMessage());
    $designations = [];
}

// Get default rate from overtime_default_rates table
$defaultRate = 0;
try {
    $stmt = $db->prepare("SELECT overtime_rate FROM overtime_default_rates WHERE site_id = ? AND is_active = 1");
    $stmt->execute([$siteId]);
    $defaultRateData = $stmt->fetch();
    if ($defaultRateData) {
        $defaultRate = (float)$defaultRateData['overtime_rate'];
    }
} catch (Exception $e) {
    error_log("Error loading default rate: " . $e->getMessage());
}

// Get existing rates
$existingRates = [];
try {
    $stmt = $db->prepare("
        SELECT designation_id, overtime_rate, is_active
        FROM overtime_designation_rates
        WHERE site_id = ?
    ");
    $stmt->execute([$siteId]);
    $rates = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($rates as $rate) {
        $existingRates[$rate['designation_id']] = $rate;
    }
} catch (Exception $e) {
    error_log("Error loading rates: " . $e->getMessage());
}

include __DIR__ . '/../../includes/header.php';
include __DIR__ . '/../payroll/_navigation.php';
?>

<style>
.rates-container {
    padding: 20px;
    background: #f5f5f5;
}

.card {
    background: white;
    border-radius: 8px;
    box-shadow: 0 2px 4px rgba(0,0,0,0.1);
    padding: 20px;
    margin-bottom: 20px;
}

.rates-table {
    width: 100%;
    border-collapse: collapse;
    margin-top: 20px;
}

.rates-table th {
    background: #1e3a5f;
    color: white;
    padding: 12px;
    text-align: left;
    font-weight: 600;
}

.rates-table td {
    padding: 12px;
    border-bottom: 1px solid #e5e7eb;
}

.rates-table tr:hover {
    background: #f9fafb;
}

.rate-input {
    width: 150px;
    padding: 8px;
    border: 1px solid #d1d5db;
    border-radius: 4px;
    font-size: 14px;
}

.rate-input:focus {
    outline: none;
    border-color: #1e293b;
    box-shadow: 0 0 0 3px rgba(37, 99, 235, 0.1);
}

.btn-save-all {
    background: #28a745;
    color: white;
    border: none;
    padding: 10px 20px;
    border-radius: 4px;
    cursor: pointer;
    font-size: 14px;
    font-weight: 500;
}

.btn-save-all:hover {
    background: #218838;
}

.info-box {
    background: #d1ecf1;
    border-left: 4px solid #17a2b8;
    padding: 15px;
    margin-bottom: 20px;
    border-radius: 4px;
}
</style>

<div class="rates-container">
    <div class="card">
        <h2 style="margin-top: 0;">Overtime Rates by Designation</h2>
        <p style="color: #6b7280; margin-bottom: 20px;">
            Set overtime rates for each designation. These rates will be automatically applied to employees based on their designation.
        </p>
        
        <?php if ($success || isset($_SESSION['success_message'])): ?>
            <div class="alert alert-success" style="background: #d4edda; color: #155724; padding: 15px; border-radius: 4px; margin-bottom: 20px; border-left: 4px solid #28a745;">
                <strong>✓ Success:</strong> <?php echo htmlspecialchars($_SESSION['success_message'] ?? 'Rates saved successfully!'); unset($_SESSION['success_message']); ?>
            </div>
        <?php endif; ?>
        
        <?php if (!empty($errors)): ?>
            <div class="alert alert-danger" style="background: #f8d7da; color: #721c24; padding: 15px; border-radius: 4px; margin-bottom: 20px; border-left: 4px solid #dc3545;">
                <strong>✗ Error:</strong>
                <ul style="margin: 5px 0 0 20px; padding: 0;">
                    <?php foreach ($errors as $error): ?>
                        <li><?php echo htmlspecialchars($error); ?></li>
                    <?php endforeach; ?>
                </ul>
            </div>
        <?php endif; ?>
        
        <div class="info-box">
            <strong>ℹ Info:</strong> Set the overtime rate (in currency per hour) for each designation. 
            When employees are loaded in the overtime sheet, their rates will be automatically applied based on their designation.
            If a designation doesn't have a specific rate, the default rate will be used.
        </div>
        
        <!-- Default Rate Section -->
        <div style="background: #fff3cd; border-left: 4px solid #ffc107; padding: 20px; margin-bottom: 30px; border-radius: 4px;">
            <h3 style="margin-top: 0; color: #856404;">Default Overtime Rate (For All Employees)</h3>
            <p style="color: #856404; margin-bottom: 15px;">
                This rate will be applied to all employees who don't have a designation-specific rate set.
            </p>
            <form method="POST" style="display: flex; align-items: center; gap: 15px; flex-wrap: wrap;">
                <div style="display: flex; align-items: center; gap: 10px;">
                    <label for="default_rate" style="font-weight: 600; color: #856404;">Default Rate (per hour):</label>
                    <input type="number" 
                           id="default_rate" 
                           name="default_rate" 
                           class="rate-input"
                           value="<?php echo number_format($defaultRate, 2, '.', ''); ?>"
                           step="0.01"
                           min="0"
                           placeholder="0.00"
                           style="width: 150px; padding: 8px; border: 2px solid #ffc107; border-radius: 4px; font-size: 14px; font-weight: 500;">
                </div>
                <button type="submit" name="save_rates" class="btn-save-all" style="margin: 0;">
                    Save Default Rate
                </button>
            </form>
        </div>
        
        <!-- Designation-Specific Rates Section -->
        <div style="margin-top: 30px;">
            <h3 style="margin-bottom: 15px;">Overtime Rates by Designation</h3>
            <p style="color: #6b7280; margin-bottom: 20px;">
                Set specific overtime rates for each designation. These rates will override the default rate for employees with that designation.
            </p>
        
        <?php if (empty($designations)): ?>
            <div style="text-align: center; padding: 40px; color: #6b7280;">
                <p>No designations found. Please create designations first.</p>
                <a href="<?php echo BASE_URL; ?>/modules/settings/index.php" class="btn btn-primary">Go to Settings</a>
            </div>
        <?php else: ?>
            <form method="POST">
                <table class="rates-table">
                    <thead>
                        <tr>
                            <th>Designation</th>
                            <th>Overtime Rate (per hour)</th>
                            <th>Active</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($designations as $designation): 
                            $desigId = $designation['designation_id'];
                            $existingRate = $existingRates[$desigId] ?? null;
                            $rate = $existingRate ? number_format($existingRate['overtime_rate'], 2, '.', '') : '0.00';
                            $isActive = $existingRate ? (bool)$existingRate['is_active'] : true;
                        ?>
                            <tr>
                                <td><strong><?php echo htmlspecialchars($designation['designation_name']); ?></strong></td>
                                <td>
                                    <input type="number" 
                                           name="rates[<?php echo $desigId; ?>][rate]" 
                                           class="rate-input"
                                           value="<?php echo htmlspecialchars($rate); ?>"
                                           step="0.01"
                                           min="0"
                                           placeholder="0.00"
                                           required>
                                </td>
                                <td>
                                    <label style="display: flex; align-items: center; gap: 8px; cursor: pointer;">
                                        <input type="checkbox" 
                                               name="rates[<?php echo $desigId; ?>][active]" 
                                               value="1"
                                               <?php echo $isActive ? 'checked' : ''; ?>>
                                        <span>Active</span>
                                    </label>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
                
                <div style="margin-top: 20px; text-align: right;">
                    <input type="hidden" name="default_rate" value="<?php echo htmlspecialchars($defaultRate); ?>">
                    <button type="submit" name="save_rates" class="btn-save-all">Save All Designation Rates</button>
                </div>
            </form>
        <?php endif; ?>
        </div>
    </div>
    
    <div style="text-align: center; margin-top: 20px;">
        <a href="<?php echo BASE_URL; ?>/modules/overtime/list.php" class="btn btn-secondary">Back to Overtime Sheet</a>
    </div>
</div>

<?php include __DIR__ . '/../../includes/footer.php'; ?>

