<?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 = $_POST['start_date'];
    $end_date = $_POST['end_date'];

    // SQL query to join tables and calculate profits
    $sql = "SELECT 
                s.product_id AS Product_ID,
                s.category AS category, 
                CASE 
                    WHEN s.category = 'Food' THEN f.name 
                    WHEN s.category IN ('Beverages', 'Beverage') THEN s.product_name
                    WHEN s.category = 'Tot' THEN s.product_name
                    ELSE 'Unknown Product' 
                END AS Product_Name,
                CASE 
                    WHEN s.category = 'Food' THEN f.cost_price 
                    WHEN s.category IN ('Beverages', 'Beverage') THEN b.buying_price 
                    WHEN s.category = 'Tot' THEN b.qbprice
                    ELSE NULL 
                END AS Buying_Price,
                SUM(s.quantity) AS Quantity,
                CASE 
                    WHEN s.category = 'Food' THEN f.price 
                    WHEN s.category IN ('Beverages', 'Beverage') THEN b.selling_price 
                    WHEN s.category = 'Tot' THEN b.qbselling_price
                    ELSE NULL 
                END AS Selling_Price,
                SUM(
                    CASE 
                        WHEN s.category = 'Food' THEN f.price * s.quantity
                        WHEN s.category IN ('Beverages', 'Beverage') THEN b.selling_price * s.quantity
                        WHEN s.category = 'Tot' THEN b.qbselling_price * s.quantity
                        ELSE 0 
                    END
                ) AS Total_Sales_Subtotal,
                SUM(
                    CASE
                        WHEN s.category = 'Food' THEN s.quantity * (f.price - f.cost_price)
                        WHEN s.category IN ('Beverages', 'Beverage') THEN s.quantity * (b.selling_price - b.buying_price)
                        WHEN s.category = 'Tot' THEN s.quantity * (b.qbselling_price - b.qbprice)
                        ELSE 0 
                    END
                ) AS Sub_Profit
            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 IN ('Beverages', 'Beverage')
            LEFT JOIN 
                tots t_tot ON b.tot_id = t_tot.tot_id AND s.category = 'Tot'
            WHERE 
                t.seller_date BETWEEN ? AND ?
            GROUP BY 
                s.product_id, s.category
            ORDER BY 
                s.category";

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        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;
    $total_sales_subtotal = 0;

    while ($row = $result->fetch_assoc()) {
        // Calculate row sales subtotal as Quantity * Selling Price
        $row_sales_subtotal = 0;
        if ($row['category'] == 'Food') {
            $row_sales_subtotal = $row['Quantity'] * $row['Selling_Price'];
        } elseif (in_array($row['category'], ['Beverages', 'Beverage'])) {
            $row_sales_subtotal = $row['Quantity'] * $row['Selling_Price'];
        } elseif ($row['category'] == 'Tot') {
            $row_sales_subtotal = $row['Quantity'] * $row['Selling_Price'];
        }

        // Format numbers for the current row
        $row['Row_Sales_Subtotal'] = number_format((float)$row_sales_subtotal, 2, '.', ',');
        $row['Buying_Price'] = number_format((float)$row['Buying_Price'], 2, '.', ',');
        $row['Selling_Price'] = number_format((float)$row['Selling_Price'], 2, '.', ',');
        $row['Sub_Profit'] = number_format((float)$row['Sub_Profit'], 2, '.', ',');

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

        // Accumulate totals for profit and sales subtotal
        $total_profit += floatval($row['Sub_Profit']);
        $total_sales_subtotal += $row_sales_subtotal;
    }

    // Format totals after accumulating
    $total_profit_formatted = number_format($total_profit, 2, '.', ',');
    $total_sales_subtotal_formatted = number_format($total_sales_subtotal, 2, '.', ',');

    echo json_encode([
        'data' => $data, 
        'total_profit' => $total_profit_formatted, 
        'total_sales_subtotal' => $total_sales_subtotal_formatted
    ]);
} else {
    echo json_encode(['error' => 'Invalid request']);
}
?>