<?php
header('Content-Type: application/json');
error_reporting(E_ALL);
ini_set('display_errors', 1);

include 'includes/config.php'; 

function getDateRange($filter) {
    switch ($filter) {
        case 'today': return [date('Y-m-d'), date('Y-m-d')];
        case 'this_week': return [date('Y-m-d', strtotime('monday this week')), date('Y-m-d', strtotime('sunday this week'))];
        case 'this_month': return [date('Y-m-01'), date('Y-m-t')];
        case 'financial_year':
            $year = date('Y'); $month = date('m');
            return ($month >= 4) ? ["$year-04-01", ($year + 1) . "-03-31"] : [($year - 1) . "-04-01", "$year-03-31"];
        default: return [null, null];
    }
}

if (!isset($_GET['filter'])) {
    echo json_encode(["error" => "No filter specified"]);
    exit;
}

$filter = $_GET['filter'];
list($startDate, $endDate) = getDateRange($filter);

if (!$startDate || !$endDate) {
    echo json_encode(["error" => "Invalid filter"]);
    exit;
}

try {
    // Fetch total sales, expenses, and returns
    $queries = [
        "totalSales" => "SELECT COALESCE(SUM(total_amount - discount), 0) FROM stock_sale WHERE date_sale BETWEEN :start AND :end",
        "totalPurchases" => "SELECT COALESCE(SUM(quantity * (purchase_price - discount_amount)), 0) FROM purchase_lines WHERE purchase_date BETWEEN :start AND :end",
        "totalExpenses" => "SELECT COALESCE(SUM(amount), 0) FROM expense WHERE date BETWEEN :start AND :end",
        "totalReturns" => "SELECT COALESCE(SUM(total_amount), 0) FROM return_sale WHERE date_sale BETWEEN :start AND :end"
    ];

    $results = [];
    foreach ($queries as $key => $sql) {
        $stmt = $conn->prepare($sql);
        $stmt->execute(['start' => $startDate, 'end' => $endDate]);
        $results[$key] = $stmt->fetchColumn();
    }

    // Corrected Profit Calculation
    $profitQuery = "
    WITH SalesData AS (
        SELECT 
            product_id, 
            SUM(sale_qty) AS total_sold_qty,
            SUM(total_amount - discount) AS total_sales
        FROM stock_sale 
        WHERE DATE(date_sale) BETWEEN :start AND :end 
        GROUP BY product_id
    ),
    ReturnData AS (
        SELECT 
            product_id, 
            SUM(sale_qty) AS total_return_qty,
            SUM(total_amount) AS total_returns
        FROM return_sale 
        WHERE DATE(date_sale) BETWEEN :start AND :end 
        GROUP BY product_id
    ),
    PurchaseCost AS (
        SELECT 
            product_id, 
            SUM(quantity) AS total_purchased_qty,
            SUM(quantity * (purchase_price - discount_amount)) AS total_purchase_cost,
            CASE 
                WHEN SUM(quantity) > 0 THEN SUM(quantity * (purchase_price - discount_amount)) / SUM(quantity) 
                ELSE 0 
            END AS avg_cost_per_unit
        FROM purchase_lines 
        WHERE DATE(purchase_date) <= :end  -- ✅ Include past purchases
        GROUP BY product_id
    )
    SELECT 
        COALESCE(SUM(
            (s.total_sales - COALESCE(r.total_returns, 0)) - 
            ((s.total_sold_qty - COALESCE(r.total_return_qty, 0)) * COALESCE(p.avg_cost_per_unit, 0))
        ), 0) AS total_gross_profit
    FROM SalesData s
    LEFT JOIN ReturnData r ON s.product_id = r.product_id
    LEFT JOIN PurchaseCost p ON s.product_id = p.product_id;
    ";
    

    $stmt = $conn->prepare($profitQuery);
    $stmt->execute(['start' => $startDate, 'end' => $endDate]);
    $results["totalGrossProfit"] = number_format($stmt->fetchColumn(), 2, '.', ''); // Ensures correct decimal format

    echo json_encode($results);
} catch (Exception $e) {
    echo json_encode(["error" => $e->getMessage()]);
}
?>
