<?php
// system/helper.php

/**
 * Validates if an employee belongs to the current logged-in branch.
 * Handles duplicate Employee IDs across different branches correctly.
 */
function validateEmployeeScope($conn, $e_id, $current_branch_id) {
    
    // ---------------------------------------------------------
    // STEP 1: Check for the employee in the CURRENT logged-in branch
    // ---------------------------------------------------------
    $sqlLocal = "SELECT e_id, e_name 
                 FROM emp_details 
                 WHERE e_id = ? AND branch = ? AND status = '1' 
                 LIMIT 1";

    $stmt = $conn->prepare($sqlLocal);
    $stmt->execute([$e_id, $current_branch_id]);
    $localEmp = $stmt->fetch(PDO::FETCH_ASSOC);

    // If found in the correct branch, return VALID immediately
    if ($localEmp) {
        return [
            'status' => 'valid',
            'emp_name' => $localEmp['e_name']
        ];
    }

    // ---------------------------------------------------------
    // STEP 2: If not found locally, check globally (to give specific error)
    // ---------------------------------------------------------
    $sqlGlobal = "SELECT e.e_id, e.branch, b.name as branch_name 
                  FROM emp_details e 
                  LEFT JOIN branch b ON e.branch = b.id 
                  WHERE e.e_id = ? AND e.status = '1' 
                  LIMIT 1";

    $stmtGlobal = $conn->prepare($sqlGlobal);
    $stmtGlobal->execute([$e_id]);
    $globalEmp = $stmtGlobal->fetch(PDO::FETCH_ASSOC);

    // Case: Exists but in a different branch
    if ($globalEmp) {
        $b_name = !empty($globalEmp['branch_name']) ? $globalEmp['branch_name'] : "Unknown Branch"; 
        $b_id = $globalEmp['branch'];
        
        return [
            'status' => 'wrong_branch',
            'message' => "<strong>Access Denied:</strong> This Employee ID ($e_id) belongs to <u>$b_name</u> (Branch Code: $b_id). <br>You cannot manage data for other branches.",
            'actual_branch' => $b_name
        ];
    }

    // Case: Does not exist anywhere
    return [
        'status' => 'not_found',
        'message' => "Employee ID <b>$e_id</b> was not found in the database or is inactive."
    ];
}

/**
 * Fetch Full Designation Name from Code
 * * @param object $conn Database connection object
 * @param string $code The short designation code (e.g., 'HK')
 * @return string The full designation name or the original code if not found.
 */
function get_designation_name($conn, $code) {
    // Return empty if code is missing
    if (empty($code)) {
        return '';
    }

    try {
        // Prepare statement to prevent SQL injection
        $stmt = $conn->prepare("SELECT details FROM designation WHERE name = ? LIMIT 1");
        $stmt->execute([trim($code)]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        // Return the full details if found, otherwise return the code itself
        if ($row && !empty($row['details'])) {
            return htmlspecialchars($row['details']);
        } else {
            return htmlspecialchars($code);
        }
    } catch (PDOException $e) {
        // Fallback in case of database error
        return htmlspecialchars($code);
    }
}
?>