<?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'])) {
    $start_date = $conn->real_escape_string($_POST['start_date']);
    $end_date = $conn->real_escape_string($_POST['end_date']);

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

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

    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();

    $data = [];
    $total_profit = 0; // Initialize total profit
    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;
        $total_profit += $row['profit'];
    }

    $stmt->close();

    // Add total profit to the response array
    $response = ['data' => $data, 'total_profit' => $total_profit];

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