<?php

// function approve_gatepass_request($request_id, $conn_rjllearning_specific, $username, $institution_id, $branch_id, $status)
// {
//     // try {
//         // If status is 1, approve the request 
//         if ($status == 1) {
//             $approved_time = time();
            
//             $sql = "UPDATE gate_pass 
//                     SET status = 1, approved_time = ?, authorized_by = ? 
//                     WHERE id = ? AND institution_id = ? AND branch_id = ? AND status = 0";
                    
//             // errorreturn(implode(", ",[$approved_time, $username, $request_id, $institution_id, $branch_id]));
            
//             $stmt = $conn_rjllearning_specific->prepare($sql);
//             $stmt->execute([$approved_time, $username, $request_id, $institution_id, $branch_id]);
            
//             if ($stmt->rowCount() > 0) {
//                 return "Gate pass request approved successfully.";  // Success message
//             } else {
//                 return "Failed to approve the request. It might already be approved or does not exist.";  
//             }
        
//         // If status is -1, decline the request 
//         } elseif ($status == -1) {
//             $sql = "UPDATE gate_pass 
//                     SET status = -1 
//                     WHERE id = ? AND institution_id = ? AND branch_id = ? AND status = 0";
            
//             $stmt = $conn_rjllearning_specific->prepare($sql);
//             $stmt->execute([$request_id, $institution_id, $branch_id]);
            
//             if ($stmt->rowCount() > 0) {
//                 return "Gate pass request declined successfully.";  
//             } else {
//                 return "Failed to decline the request. It might already be processed or does not exist.";  // Error message
//             }
//         } else {
//             return "Invalid status value.";  // Error message for invalid status
//         }

//     // } catch (PDOException $e) {
//     //     return "Database error: " . $e->getMessage();  // Database error message
//     // }
// }



function approve_gatepass_request($request_id, $conn_rjllearning, $conn_rjllearning_specific, $username, $institution_id, $branch_id, $status)
{
    global $workspace_id;
    require_once $GLOBALS['base_path_to_learning_resources'] . 'lib/cron_jobs/function/function.php';
    require_once $GLOBALS['base_path_to_learning_resources'] . 'lib/cron_jobs/firebase_notification/fcm_notifications_function.php';

    if ($status == 1) {
        
        $approved_time = time();

        // ✅ Approve the request
        $sql = "UPDATE gate_pass 
                SET status = 1, approved_time = ?, authorized_by = ? 
                WHERE id = ? AND institution_id = ? AND branch_id = ? AND status = 0";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$approved_time, $username, $request_id, $institution_id, $branch_id]);

        if ($stmt->rowCount() > 0) {
            // ✅ Fetch student/class details
            $sql_details = "
                SELECT s.id AS student_id, s.class_id, s.section_id
                FROM gate_pass gp
                INNER JOIN students s 
                    ON gp.user_id = s.id 
                   AND gp.institution_id = s.institution_id 
                   AND gp.branch_id = s.branch_id
                WHERE gp.id = ? 
                  AND gp.institution_id = ? 
                  AND gp.branch_id = ?";
            $stmt_details = $conn_rjllearning_specific->prepare($sql_details);
            $array=[$request_id, $institution_id, $branch_id];
            $stmt_details->execute([$request_id, $institution_id, $branch_id]);
            $details = $stmt_details->fetch(PDO::FETCH_ASSOC);
     
            if (!$details) {
                return "Student details not found.";
            }

            $student_id = $details['student_id'];
            $class_id   = $details['class_id'];
            $section_id = $details['section_id'];

            // ✅ Step 1: Get faculty IDs from timetable (class_incharge)
            $sql_incharge = "SELECT DISTINCT faculty_id 
                             FROM timetable 
                             WHERE institution_id = ? 
                               AND branch_id = ? 
                               AND class_id = ? 
                               AND section_id = ? 
                               AND type = 'class_incharge'";
            $stmt_incharge = $conn_rjllearning_specific->prepare($sql_incharge);
            $stmt_incharge->execute([$institution_id, $branch_id, $class_id, $section_id]);
            $faculty_ids = $stmt_incharge->fetchAll(PDO::FETCH_COLUMN);

            if (empty($faculty_ids)) {
                return "Gate pass approved, but no incharge faculty found.";
            }

            // ✅ Step 2: Get function_code.id for "approve gate pass"
            $sql_func = "SELECT id FROM function_codes WHERE LOWER(code) = 'approve_gate_pass_request' LIMIT 1";
            $stmt_func = $conn_rjllearning->prepare($sql_func);
            $stmt_func->execute();
            $function_code_id = $stmt_func->fetchColumn();

            if (!$function_code_id) {
                return "Gate pass approved, but no matching function code found.";
            }

            // ✅ Step 3: Filter faculty IDs based on staff_access_support
            $allowed_incharge_ids = [];
            $sql_access = "SELECT function_code 
                           FROM staff_access_support 
                           WHERE institution_id = ? 
                             AND branch_id = ? 
                             AND faculty_id = ?";
            $stmt_access = $conn_rjllearning_specific->prepare($sql_access);

            foreach ($faculty_ids as $fid) {
                $stmt_access->execute([$institution_id, $branch_id, $fid]);
                $func_keys = $stmt_access->fetchColumn();

                if ($func_keys) {
                    $func_ids = is_string($func_keys) && strpos($func_keys, '[') !== false
                        ? json_decode($func_keys, true)
                        : explode(',', $func_keys);

                    $func_ids = array_map('intval', $func_ids);

                    if (in_array((int)$function_code_id, $func_ids)) {
                        $allowed_incharge_ids[] = $fid;
                    }
                }
            }

            // ✅ Step 4: Send notifications & count
            $notification_count = 0;
            if (!empty($allowed_incharge_ids)) {
                $title = "Gate Pass Approved";
                $body  = "Gate pass for a student in your class has been approved.";

                $notification_count = sendNotification(
                    $conn_rjllearning,
                    $allowed_incharge_ids,
                    $title,
                    $body,
                    "gate_pass_approved",
                    $student_id
                );

                // If you want to log mail/SMS count
                updateMailSmsCount(
                    $workspace_id, 
                    $institution_id, 
                    $branch_id, 
                    null, 
                    null, 
                    $notification_count, 
                    'notification', 
                    $conn_rjllearning
                );
            }

            return "Gate pass request approved successfully.";
        } else {
            return "Failed to approve the request. It might already be approved or does not exist.";
        }

    } elseif ($status == -1) {
        // ✅ Decline request
        $sql = "UPDATE gate_pass 
                SET status = -1 
                WHERE id = ? AND institution_id = ? AND branch_id = ? AND status = 0";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$request_id, $institution_id, $branch_id]);

        if ($stmt->rowCount() > 0) {
            return "Gate pass request declined successfully.";
        } else {
            return "Failed to decline the request. It might already be processed or does not exist.";
        }
    } else {
        return "Invalid status value.";
    }
}
