<?php
// leave_integration.php
if (!defined('CONN_PROTECT_OK') && !isset($conn)) {
    die('Direct access block violation.');
}

/**
 * Define the active approval stages required for a leave to be valid.
 * Modify this array anytime your business workflow changes.
 */
define('ACTIVE_APPROVAL_ROLES', ['manager_status', 'hr_status']);

/**
 * Helper to build dynamic SQL WHERE conditions for required approvals.
 */
function getApprovalSqlConditions(array $roles = ACTIVE_APPROVAL_ROLES): string {
    if (empty($roles)) {
        return "1=1";
    }
    $conditions = array_map(function($role) {
        // Sanitize column names against an allowed list to prevent injection
        $allowed = ['manager_status', 'hr_status', 'gm_status', 'ops_status'];
        if (!in_array($role, $allowed, true)) {
            throw new InvalidArgumentException("Invalid approval status column: $role");
        }
        return "lr.{$role} = 'Approved'";
    }, $roles);

    return implode(' AND ', $conditions);
}

/**
 * Fetches all fully approved leave segments overlapping the active schedule window matrix.
 */
function fetchApprovedLeavesScope($conn, array $days, $windowStart, $windowEnd, array $activeRoles = ACTIVE_APPROVAL_ROLES) {
    $approvedLeaves = [];
    $approvalConditions = getApprovalSqlConditions($activeRoles);

    $leaveQuery = "
        SELECT lr.employee_id, lr.start_date, lr.end_date, lt.name AS leave_name
        FROM leave_requests lr
        JOIN leave_types lt ON lr.leave_type_id = lt.id
        WHERE {$approvalConditions}
          AND lr.start_date <= ? 
          AND lr.end_date >= ?
    ";
    
    if ($lStmt = $conn->prepare($leaveQuery)) {
        $lStmt->bind_param("ss", $windowEnd, $windowStart);
        $lStmt->execute();
        $lRes = $lStmt->get_result();
        
        while ($row = $lRes->fetch_assoc()) {
            $empId  = intval($row['employee_id']);
            $lStart = strtotime($row['start_date']);
            $lEnd   = strtotime($row['end_date']);
            $abbr   = strtoupper(substr(trim($row['leave_name']), 0, 2));

            foreach ($days as $dayStr) {
                $currentDayTime = strtotime($dayStr);
                if ($currentDayTime >= $lStart && $currentDayTime <= $lEnd) {
                    $approvedLeaves[$empId][$dayStr] = [
                        'name' => $row['leave_name'],
                        'abbr' => $abbr
                    ];
                }
            }
        }
        $lStmt->close();
    }
    return $approvedLeaves;
}

/**
 * Validates whether an employee is currently locked on an approved leave day.
 */
function isEmployeeLeaveLocked($conn, $empId, $date, array $activeRoles = ACTIVE_APPROVAL_ROLES) {
    $approvalConditions = getApprovalSqlConditions($activeRoles);

    $chkLeave = $conn->prepare("
        SELECT lr.id FROM leave_requests lr 
        WHERE lr.employee_id = ? AND lr.start_date <= ? AND lr.end_date >= ?
          AND {$approvalConditions} LIMIT 1
    ");
    $chkLeave->bind_param("iss", $empId, $date, $date);
    $chkLeave->execute();
    $isLocked = ($chkLeave->get_result()->num_rows > 0);
    $chkLeave->close();
    
    return $isLocked;
}