<?php
ini_set('display_errors', 1);
// this file is used to create all other users except superadmin
require_once '../../../../resources/base_path_include.php';
require_once $base_path_to_learning_resources  . "includes/connect.php";
require_once $base_path_to_learning_resources . 'lib/function.php';
require_once $base_path_to_learning_resources . 'lib/validate_api_signature.php';
//valdation file
require_once $base_path_to_learning_resources . 'lib/validate_function.php';
require_once $base_path_to_learning . 'user_management/authenticate.php';
require_once $base_path_to_learning_resources . 'external_requests/finware_curl/groups_ledgers/curl_get_specific_student_ledgers_finware.php';
//db access function
require_once $base_path_to_learning_resources . 'includes/specific_db_config.php';

$postdata = json_decode(file_get_contents("php://input"), true);

validateCurlSignature($postdata['api_signature']);

$data = $postdata['student_stop'];
// echo json_encode($data);die();
$workspace_id = $data['workspace_id'];
$branch_id = $data['branch_id'];
$student_id = $data['student_id'];
$class_section = $data['class_section'];

// Split the string into an array by spaces
$parts = explode(' ', $class_section);
// Check if we have at least 2 parts for section and class
if (count($parts) >= 2) {
    // Assume the last two parts form the section
    $section = array_splice($parts, -2);  // This will take the last 2 elements as the section
    $section = implode(' ', $section);  // Join the section parts into one string

    // The remaining parts form the class
    $class = implode(' ', $parts);  // Join the remaining parts into one string
}
// echo json_encode($class);die();

$sql = "Select db_name from workspace_database_info where workspace_id = ? and  status = ?";
$fetch_workspace = $conn_rjllearning->prepare($sql);
$fetch_workspace->execute(array($workspace_id, 1));
$result_workspace = $fetch_workspace->fetchAll();
// echo json_encode($result_workspace);die();
if (count($result_workspace) < 1) {
    errorreturn('Fatal error (1). Access denied.');
}
// user level restriction
$specific_db_name = $result_workspace[0]['db_name'];

$conn_rjllearning_specific = dynamicDbConnection($specific_db_name);


$sqlbranch = $conn_rjllearning_specific->prepare("Select * from  branch WHERE id = ? and status =? ");
$sqlbranch->execute(array($branch_id, '1'));
$result_branch = $sqlbranch->fetchAll();
//  echo json_encode($result_branch);die();
if (count($result_branch) < 1) {
    echo json_encode([
        'error' => true,
        'message' => 'Branch Not Found.'
    ]);
    die();
}

$institution_id =  $result_branch[0]['institution_id'];
// echo json_encode($section);die();
// SQL query for fetching class ID
// $sql_class = "SELECT id FROM class_info WHERE institution_id = ? AND branch_id LIKE ? AND classname = ?";
// $stmt_class = $conn_rjllearning_specific->prepare($sql_class);
// $stmt_class->execute([$institution_id, '%' . $branch_id . '%', $class]);
// $result_class = $stmt_class->fetchAll();
// // echo json_encode($result_class);die();
// // Check if class exists and get the class_id
// if ($result_class) {

//     $class_id = $result_class[0]['id'];
// } else {
//     // Handle case where class is not found
//     errorreturn("Class not found for the specified branch.");
// }

// SQL query for fetching section ID
// $sql_section = "SELECT id FROM section_info WHERE institution_id = ? AND branch_id LIKE ? AND section_name = ?";
// $stmt_section = $conn_rjllearning_specific->prepare($sql_section);
// $stmt_section->execute([$institution_id, '%' . $branch_id . '%', $section]);
// $result_section = $stmt_section->fetch(PDO::FETCH_ASSOC);

// // Check if section exists and get the section_id
// if ($result_section) {
//     $section_id = $result_section['id'];
// } else {
//     // Handle case where section is not found
//     errorreturn("Section not found for the specified branch.");
// }
// Remove parentheses and split the string into an array
$student_ids_array = explode(',', str_replace(['(', ')', "'"], '', $student_id));

$imploded_student_ids = "('" . implode("','", $student_ids_array) . "')";
//  echo json_encode($imploded_student_ids);
// die();
// Count the number of IDs
$count_ids = count($student_ids_array);
//check student 
// $sqlStudents = "SELECT count(*) as total FROM students WHERE institution_id = ? AND branch_id = ? AND class_id = ? AND section_id = ? AND id IN $student_id";
// $array_params = [$institution_id, $branch_id, $class_id, $section_id];
// $stmtStudents = $conn_rjllearning_specific->prepare($sqlStudents);
// $stmtStudents->execute($array_params);
// $ResultStudents = $stmtStudents->fetchAll()[0]['total']; 
// //  echo json_encode($ResultStudents);die();
// if ($ResultStudents != $count_ids) {
//     errorreturn("Student Not Found.");
// }

// SQL to get student stop details
$sqlStops = "SELECT stop_id, student_id FROM student_stops WHERE institution_id = ? AND branch_id = ? AND student_id IN $imploded_student_ids";
$array_params = [$institution_id, $branch_id];
$stmtStops = $conn_rjllearning_specific->prepare($sqlStops);
$stmtStops->execute($array_params);
$ResultStops = $stmtStops->fetchAll();

// Check if any student stop details were found
if ($ResultStops) {
    // Prepare a single SQL query to fetch all bus stop charges at once
    $stop_ids = array_column($ResultStops, 'stop_id'); // Extract stop_ids from the result

    if (!empty($stop_ids)) {
        // SQL to fetch charges for the relevant stops
        $placeholders = implode(',', array_fill(0, count($stop_ids), '?')); // Generate placeholders for the query
        $sql_bus_stops = "SELECT id, charges FROM bus_stops WHERE id IN ($placeholders) 
                          AND institution_id = ? AND branch_id = ?";
        
        // Prepare the query to fetch charges
        $stmtBusStops = $conn_rjllearning_specific->prepare($sql_bus_stops);
        $stmtBusStops->execute(array_merge($stop_ids, [$institution_id, $branch_id]));
        $bus_stop_charges = $stmtBusStops->fetchAll(); // Fetch all charges for stops
        
        // Map the stop_id to charges for quick lookup
        $charges_map = [];
        foreach ($bus_stop_charges as $stop_charge) {
            $charges_map[$stop_charge['id']] = $stop_charge['charges'] ?? 0; // Default to 0 if no charge
        }

        // Loop through the student stops and add charges to the array
        foreach ($ResultStops as $stops) {
            $stop_id = $stops['stop_id'];
            $studentid = $stops['student_id'];
            $charges = $charges_map[$stop_id] ?? 0; // Get the charges from the map or default to 0
            
            // Add data to the array
            $student_stops_charges[] = [
                'student_id' => $studentid,
                'stop_id' => $stop_id,
                'charges' => $charges
            ];
        }
    }
}


// Output the result
echo json_encode($student_stops_charges);
die();
