<?php
header('Content-Type: application/json');
include '../../config/config.php'; // Adjust path if necessary

// Function to Calculate Rate
function calculateRate($entered_weight, $rate_card) {
    $min_wt = $rate_card['min_wt'];
    $min_rate = $rate_card['min_rate'];
    $addon_wt = $rate_card['addon_wt'];
    $addon_rate = $rate_card['addon_rate'];

    // Base charge for min_wt
    $total_rate = $min_rate;

    // Calculate additional charges
    if ($entered_weight > $min_wt) {
        $remaining_weight = $entered_weight - $min_wt;
        $additional_units = ceil($remaining_weight / $addon_wt);
        $total_rate += $additional_units * $addon_rate;
    }

    return $total_rate;
}

// User Input with Sanitization
$entered_weight = isset($_GET['wt']) ? floatval($_GET['wt']) : 3;
$client_id = isset($_GET['client_id']) ? intval($_GET['client_id']) : 1;
$product_id = isset($_GET['product_id']) ? intval($_GET['product_id']) : 0;
$state_id = isset($_GET['state_id']) ? htmlspecialchars($_GET['state_id']) : 'ss';
$place = isset($_GET['place']) ? htmlspecialchars($_GET['place']) : 'sdf';
$ship_type = isset($_GET['ship_type']) ? htmlspecialchars($_GET['ship_type']) : 'sf';
$parcel_type = isset($_GET['parcel_type']) ? htmlspecialchars($_GET['parcel_type']) : 'sds';

// Function to Fetch Rate Card
function fetchRateCard($pdo, $sql, $params) {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// First Query - Check Place
$sql = "
    SELECT * FROM tbl_rate_card 
    WHERE client_id = :client_id 
    AND product_id = :product_id
    AND place = :place 
    AND start_wt <= :weight 
    AND end_wt >= :weight 
    AND ship_type = :ship_type 
    AND parcel_type = :parcel_type 
    ORDER BY client_id DESC 
    LIMIT 1;
";

$rate_card = fetchRateCard($pdo, $sql, [
    'client_id' => $client_id,
    'product_id' => $product_id,
    'place' => $place,
    'weight' => $entered_weight,
    'ship_type' => $ship_type,
    'parcel_type' => $parcel_type
]);

if ($rate_card) {
    $total_cost = calculateRate($entered_weight, $rate_card);
    echo json_encode(['rate_card' => $rate_card, 'total_cost' => "₹" . $total_cost]);
    exit();
}

// Second Query - Check State ID
$sql = "
    SELECT * FROM tbl_rate_card 
    WHERE (client_id = :client_id OR client_id = 0) 
    AND (product_id = :product_id OR product_id = '') 
    AND FIND_IN_SET(:state_id, state_ids) > 0 
    AND start_wt <= :weight 
    AND end_wt >= :weight 
    AND ship_type = :ship_type 
    AND parcel_type = :parcel_type 
    ORDER BY client_id DESC 
    LIMIT 1;
";

$rate_card = fetchRateCard($pdo, $sql, [
    'client_id' => $client_id,
    'product_id' => $product_id,
    'state_id' => $state_id,
    'weight' => $entered_weight,
    'ship_type' => $ship_type,
    'parcel_type' => $parcel_type
]);

if ($rate_card) {
    $total_cost = calculateRate($entered_weight, $rate_card);
    echo json_encode(['rate_card' => $rate_card, 'total_cost' => "₹" . $total_cost]);
    exit();
}

// Third Query - Default Rate
$sql = "
    SELECT * FROM tbl_rate_card 
    WHERE client_id = 0
    AND product_id = :product_id 
    AND start_wt <= :weight 
    AND end_wt >= :weight 
    AND ship_type = :ship_type 
    AND parcel_type = :parcel_type 
    ORDER BY client_id DESC 
    LIMIT 1;
";

$rate_card = fetchRateCard($pdo, $sql, [
    'product_id' => $product_id,
    'weight' => $entered_weight,
    'ship_type' => $ship_type,
    'parcel_type' => $parcel_type
]);

if ($rate_card) {
    $total_cost = calculateRate($entered_weight, $rate_card);
    echo json_encode(['rate_card' => $rate_card, 'total_cost' => "₹" . $total_cost]);
    exit();
}

// No Rate Found
echo json_encode(['error' => 'No rate card available']);
exit();
?>