<?php 
include_once('shikanisha.kts.php');

if(isset($_POST['clients_names'], $_POST['start'], $_POST['end'])){
    $seller_name = $_POST['clients_names'];
    $start_date = $_POST['start'];
    $end_date = $_POST['end'];
    $start_datetime = date('Y-m-d H:i:s', strtotime($start_date));
    $end_datetime = date('Y-m-d H:i:s', strtotime($end_date));

    // Adjusted SQL query with proper column names based on your schema
    $sql = "
    SELECT s.sale_id, s.product_id, 
           CASE 
               WHEN s.category = 'Food' THEN f.name 
               WHEN s.category = 'Beverage' THEN b.beverage_name
               WHEN s.category = 'Beverages' THEN b.beverage_name
               WHEN s.category = 'Tot' THEN b.beverage_name
               ELSE 'Unknown' 
           END AS product_name,
           s.quantity, 
           CASE 
               WHEN s.category = 'Food' THEN f.price 
               WHEN s.category = 'Beverage' THEN b.selling_price 
               WHEN s.category = 'Beverages' THEN b.selling_price
               WHEN s.category = 'Tot' THEN b.qbselling_price
               ELSE s.selling_price 
           END AS selling_price,
           s.subtotal, s.trans_id, s.category,
           t.seller_name, t.seller_date,
           CASE 
               WHEN s.category = 'Food' THEN f.cost_price
               WHEN s.category = 'Beverage' THEN b.buying_price
               WHEN s.category = 'Beverages' THEN b.buying_price
               WHEN s.category = 'Tot' THEN b.qbprice
               ELSE 0 
           END AS cost_price
    FROM sales s
    LEFT JOIN transaction_receipts t ON s.trans_id = t.trans_id
    LEFT JOIN food f ON s.product_id = f.food_id AND s.category = 'Food'
    LEFT JOIN beverages b ON s.product_id = b.beverage_id AND (s.category = 'Beverage' OR s.category = 'Beverages' OR s.category = 'Tot')
    WHERE t.seller_name = ? AND t.seller_date BETWEEN ? AND ?";

    $stmt = $conn->prepare($sql);
    if ($stmt === false) {
        die('Prepare failed: ' . htmlspecialchars($conn->error));
    }

    $bind = $stmt->bind_param("sss", $seller_name, $start_datetime, $end_datetime);
    if ($bind === false) {
        die('Bind param failed: ' . htmlspecialchars($stmt->error));
    }

    $exec = $stmt->execute();
    if ($exec === false) {
        die('Execute failed: ' . htmlspecialchars($stmt->error));
    }

    $result = $stmt->get_result();
    if ($result === false) {
        die('Get result failed: ' . htmlspecialchars($stmt->error));
    }

    $sales_data = array();
    while($row = $result->fetch_assoc()) {
        $row['start_datetime'] = $start_datetime;
        $row['end_datetime'] = $end_datetime;
        $sales_data[] = $row;
    }

    header('Content-Type: application/json');
    echo json_encode($sales_data);
    $stmt->close();
} else {
    echo 'Required POST parameters: clients_names, start, end.';
}
?>