<?php

function getAvailable_stops($conn_rjllearning_specific, $institution_id, $branch_id, $selected_stops = [])
{
    $sql = "SELECT id, stop_names FROM bus_stops WHERE institution_id = ? AND branch_id = ?";

    if (!empty($selected_stops)) {
        $placeholders = str_repeat('?,', count($selected_stops) - 1) . '?';
        $sql .= "AND id NOT IN ($placeholders)";
    }
    $stmt = $conn_rjllearning_specific->prepare($sql);

    $params = array_merge([$institution_id, $branch_id], $selected_stops);
    $stmt->execute($params);

    return $stmt->fetchAll();
}

function getspecific_route_details($conn_rjllearning_specific, $institution_id, $branch_id, $route_id)
{
$sql = "SELECT * FROM routes WHERE id = ? AND institution_id = ? AND branch_id = ?";
$stmt = $conn_rjllearning_specific->prepare($sql);
$stmt->execute([$route_id, $institution_id, $branch_id]);
$result = $stmt->fetch(PDO::FETCH_ASSOC);



if (!$result) {
    errorreturn("No Route Available");
}

// Fetch bus stop details
$stops_name = $result['stops_name'];
$stop_info = [];

// Decode stops_name from JSON
$stops_name_array = json_decode($stops_name, true);

if (is_array($stops_name_array) && !empty($stops_name_array)) {
    // Convert each element to an integer
    $stops_name_array = array_map('intval', $stops_name_array);
     
    // Create placeholders for the IN clause
    $placeholders = implode(',', array_fill(0, count($stops_name_array), '?'));

    // Prepare SQL query with placeholders
    $sql_stops = "SELECT * FROM bus_stops WHERE institution_id = ? AND id IN ($placeholders)";
 
    $stmt_stops = $conn_rjllearning_specific->prepare($sql_stops);

    // Combine institution_id with stops_name_array for execution
    $params = array_merge([$institution_id], $stops_name_array);
    $stmt_stops->execute($params);
    $stops_name_rows = $stmt_stops->fetchAll(PDO::FETCH_ASSOC);

  

    foreach ($stops_name_rows as $row) {
        $stop_info[] = [
            'stop_id' => $row['id'],
            'stop_name' => ucwords($row['stop_names'])
        ];
    }
}

    // Fetch branch details
    $branch = getBranchInfo($conn_rjllearning_specific, $institution_id, $branch_id);

    // Fetch driver details
    $driver_info = null;
    if (!empty($result['driver_id'])) {
        $sql_driver = "SELECT id, name FROM staff_details WHERE institution_id = ? AND id = ?";
        $stmt_driver = $conn_rjllearning_specific->prepare($sql_driver);
        $stmt_driver->execute([$institution_id, $result['driver_id']]);
        $driver_info = $stmt_driver->fetch();
    }

    // Fetch conductor details
    $conductor_info = null;
    if (!empty($result['conductor_id'])) {
        $sql_conductor = "SELECT id, name FROM staff_details WHERE institution_id = ? AND id = ?";
        $stmt_conductor = $conn_rjllearning_specific->prepare($sql_conductor);
        $stmt_conductor->execute([$institution_id, $result['conductor_id']]);
        $conductor_info = $stmt_conductor->fetch();
    }

    // Fetch incharge details
    $incharge_info = null;
    if (!empty($result['incharge_id'])) {
        $sql_incharge = "SELECT id, name FROM staff_details WHERE institution_id = ? AND id = ?";
        $stmt_incharge = $conn_rjllearning_specific->prepare($sql_incharge);
        $stmt_incharge->execute([$institution_id, $result['incharge_id']]);
        $incharge_info = $stmt_incharge->fetch();
    }

    return [
        'route_name' => $result['route_name'],
        'institution_id' => $result['institution_id'],
        'branch_id' => $branch,
        'bus_driver' => $driver_info ? ['id' => $driver_info['id'], 'name' => $driver_info['name']] : null,
        'bus_conductor' => $conductor_info ? ['id' => $conductor_info['id'], 'name' => $conductor_info['name']] : null,
        'bus_incharge' => $incharge_info ? ['id' => $incharge_info['id'], 'name' => $incharge_info['name']] : null,
        'stop_name' => $stop_info,
        'added_by' => $result['added_by'],
        'date_created' => $result['date_created']
    ];
}

function fetch_stopName($conn_rjllearning_specific, $institution_id, $branch_id, $route_id)
{
    $sql = "SELECT stop_names FROM bus_stops WHERE id = ? AND institution_id = ? AND branch_id = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute(array($route_id, $institution_id, $branch_id));
    $result = $stmt->fetch();

    if (!$result) {
        echo "No Data Available";
    } else {
        return $result['stop_names'];
    }
}
