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

header('Content-Type: application/json'); // Set header for JSON response

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['start_date']) && isset($_POST['end_date']) && isset($_POST['category'])) {
    $start_date = $conn->real_escape_string($_POST['start_date']);
    $end_date = $conn->real_escape_string($_POST['end_date']);
    $category = $conn->real_escape_string($_POST['category']);

    // Adjusted SQL query to join the products table, calculate profit, and include the sum of amounts for each payment method
    $sql = "SELECT
                t.trans_id,
                s.product_id, 
                p.product_name,
                p.category1,
                p.buying_price, 
                s.selling_price, 
                SUM(s.quantity) AS total_quantity, 
                SUM(s.quantity * p.buying_price) AS total_bp, 
                SUM(s.subtotal) AS total_subtotal, 
                pm.payment_method,
                SUM(pm.amount) AS sum_amount,
                (SUM(s.subtotal) - SUM(s.quantity * p.buying_price)) AS profit
            FROM sales s
            INNER JOIN transaction_receipts t ON s.trans_id = t.trans_id
            INNER JOIN products p ON s.product_id = p.product_id
            LEFT JOIN payments pm ON t.trans_id = pm.trans_id
            WHERE t.seller_date >= ? AND t.seller_date <= ?";

    if ($category != 'All') {
        $sql .= " AND p.category1 = ?";
    }

    $sql .= " GROUP BY t.trans_id, s.product_id, p.product_name, p.buying_price, s.selling_price, pm.payment_method";

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        // Handle error properly
        echo json_encode(['error' => "Prepare failed: (" . $conn->errno . ") " . $conn->error]);
        exit();
    }

    if ($category != 'All') {
        $stmt->bind_param("sss", $start_date, $end_date, $category);
    } else {
        $stmt->bind_param("ss", $start_date, $end_date);
    }

    $stmt->execute();
    $result = $stmt->get_result();

    $data = [];
    while ($row = $result->fetch_assoc()) {
        // Append new fields to the row array
        $row['bp'] = $row['total_bp']; // Buying price total
        $row['profit'] = $row['profit']; // Profit

        // Add the row to the data array
        $data[] = $row;
    }

    $stmt->close();

    echo json_encode($data); // Return the data as JSON
} else {
    echo json_encode(['error' => 'Invalid request']); // Return error if conditions not met
}
?>
