<?php 
require 'Prozigzig.php';
$h = new Prozigzig();
header('Content-type: application/json');

// Decode request
$data = json_decode(file_get_contents('php://input'), true);

// Get setting (for currency)
$set = $h->fetchData("SELECT * FROM `tbl_setting`");

// Validate input
if (empty($data['uid']) || empty($data['boarding_id']) || empty($data['drop_id']) || empty($data['trip_date']) || empty($data['weight']) || empty($data['distance'])) {
    $returnArr = array("ResponseCode" => "401", "Result" => "false", "ResponseMsg" => "Missing Required Fields!");
    echo json_encode($returnArr);
    exit;
}

// Extract input
$boarding_id = intval($data['boarding_id']);
$from_point_id = intval($data['from_point_id']);
$to_point_id = intval($data['to_point_id']);
$drop_id = intval($data['drop_id']);
$weight = floatval($data['weight']);
$distance = floatval($data['distance']);
$trip_date = $data['trip_date'];
$day_of_week = date('l', strtotime($trip_date));

// Fetch buses that match criteria
$sql = "
SELECT 
    bp.bus_id,
    bp.id AS id_pickup_drop,
    bus.operator_id,
    bus.title AS bus_title,
    bus.bno AS bus_no,
    bus.bus_img,
    bp.btime AS bus_picktime,
    bp.dtime AS bus_droptime,
    bus.parcel_weight_limit,
    bus.parcel_count_limit
FROM tbl_board_drop_points AS bp
JOIN tbl_bus AS bus ON bp.bus_id = bus.id
WHERE bp.bpoint = $boarding_id 
  AND bp.dpoint = $drop_id
  AND bus.bstatus = 1
  AND FIND_IN_SET('$day_of_week', bus.offday) = 0
";

$busfetch = $h->queryfire($sql);
$vo = [];

while ($row = $busfetch->fetch_assoc()) {
    $bus_id = $row['bus_id'];
    $opr = intval($row['operator_id']);

    // Check how many parcels are already booked for this bus on this trip_date
    $parcelCheckQry = "
        SELECT 
            COUNT(*) as total_count, 
            IFNULL(SUM(parcel_weight), 0) as total_weight 
        FROM tbl_parcel 
        WHERE bus_id = $bus_id 
          AND trip_date = '$trip_date'
    ";
    $parcelStats = $h->queryfire($parcelCheckQry)->fetch_assoc();
    $total_weight = floatval($parcelStats['total_weight']);
    $total_count = intval($parcelStats['total_count']);

    // Remaining capacity
    $remaining_weight = floatval($row['parcel_weight_limit']) - $total_weight;
    $remaining_count = intval($row['parcel_count_limit']) - $total_count;

    // Skip if this bus can't accommodate the parcel
    if ($weight > $remaining_weight || $remaining_count <= 0) {
        continue;
    }

    // Get matched rate
    $matchedRateQry = "SELECT * FROM tbl_parcel_rate WHERE operator_id = $opr AND $weight BETWEEN min_weight AND max_weight LIMIT 1";
    $matchedRate = $h->queryfire($matchedRateQry)->fetch_assoc();

    // Get all rates for this operator
    $allRates = [];
    $allRatesQry = $h->queryfire("SELECT * FROM tbl_parcel_rate WHERE operator_id = $opr");
    while ($rate = $allRatesQry->fetch_assoc()) {
        $allRates[] = array(
            "min_weight" => floatval($rate['min_weight']),
            "max_weight" => floatval($rate['max_weight']),
            "price_per_km" => floatval($rate['price_per_km'])
        );
    }

    if ($matchedRate) {
        $price_per_km = floatval($matchedRate['price_per_km']);
        $parcel_price = round($price_per_km * $distance, 2);

        // Check if pickup and drop points are valid in sub-routes
        $countpickup = $h->queryfire("SELECT * FROM `tbl_sub_route_time` WHERE board_id = ".$row['id_pickup_drop']." AND point_id = $from_point_id")->num_rows;
        $countdrop = $h->queryfire("SELECT * FROM `tbl_drop_sub_route` WHERE board_id = ".$row['id_pickup_drop']." AND point_id = $to_point_id")->num_rows;

        if ($countpickup != 0 && $countdrop != 0) {
            $vo[] = array(
                'bus_id' => $bus_id,
                'id_pickup_drop' => $row['id_pickup_drop'],
                'operator_id' => $opr,
                'bus_title' => $row['bus_title'],
                'bus_no' => $row['bus_no'],
                'bus_img' => $row['bus_img'],
                'bus_picktime' => $row['bus_picktime'],
                'bus_droptime' => $row['bus_droptime'],
                'price_per_km' => $price_per_km,
                'distance' => $distance,
                'parcel_price' => $parcel_price,
                'remaining_weight' => $remaining_weight,
                'remaining_count' => $remaining_count,
                'rate_info' => array(
                    'matched_rate' => array(
                        'min_weight' => $matchedRate['min_weight'],
                        'max_weight' => $matchedRate['max_weight'],
                        'price_per_km' => $matchedRate['price_per_km'],
                        'total_price' => $parcel_price
                    ),
                    'all_rates' => $allRates
                )
            );
        }
    }
}

// Final response
$returnArr = array(
    "BusData" => $vo,
    "currency" => $set['currency'],
    "ResponseCode" => "200",
    "Result" => "true",
    "ResponseMsg" => "Parcel Buses fetched successfully!"
);

echo json_encode($returnArr);
