<?php
require_once 'shikanisha.kts.php'; // Make sure this is the correct path to your database connection file

// Define start and end dates from POST data
$start_date = $_POST['startdateconsumption'];
$end_date = $_POST['enddateconsumption'];

// Initialize arrays to store fetched data
$openingStock = [];
$receivedStock = [];
$soldStock = [];
$disposedStock = [];

// Fetch initial stock and value for tots, beverages, and ingredients
$sqlInitialStock = "
    SELECT 
        CONCAT('t_', t.tot_id) AS product_id,
        'Tots' AS category,
        t.name AS productname,
        t.price AS price,
        t.quantity AS quantity,
        (t.price * t.quantity) AS opening_value,
        t.created_at AS initial_date
    FROM tots t
    UNION ALL
    SELECT 
        CONCAT('b_', b.beverage_id) AS product_id,
        'Beverage' AS category,
        b.beverage_name AS productname,
        b.buying_price AS price,
        b.quantity AS quantity,
        (b.buying_price * b.quantity) AS opening_value,
        b.initial_date AS initial_date
    FROM beverages b
    UNION ALL
    SELECT 
        CONCAT('i_', i.id) AS product_id,
        'Ingredient' AS category,
        i.name AS productname,
        i.buying_price AS price,
        i.quantity AS quantity,
        (i.buying_price * i.quantity) AS opening_value,
        i.initial_date AS initial_date
    FROM ingredients i
";
$resultInitialStock = mysqli_query($conn, $sqlInitialStock);
if (!$resultInitialStock) {
    die('Initial Stock Query Failed: ' . mysqli_error($conn));
}
while ($row = mysqli_fetch_assoc($resultInitialStock)) {
    $openingStock[$row['product_id']] = $row;
}

// Fetch received, disposed, and sold quantities and values
foreach ($openingStock as $product_id => &$data) {
    $productId = substr($product_id, 2); // Remove prefix
    $categoryCondition = $data['category'];

    // Fetch received quantity and value within the date range
    $sqlReceived = "
        SELECT 
            SUM(po.quantity) AS received_quantity,
            SUM(po.subtotal) AS received_value
        FROM purchase_orders po
        WHERE po.product_id = '$productId'
            AND po.purchase_date BETWEEN '$start_date' AND '$end_date'
            AND po.category = '$categoryCondition'
    ";
    $resultReceived = mysqli_query($conn, $sqlReceived);
    if ($resultReceived) {
        $received = mysqli_fetch_assoc($resultReceived);
        $received_quantity = isset($received['received_quantity']) ? $received['received_quantity'] : 0;
        $received_value = isset($received['received_value']) ? $received['received_value'] : 0;
    } else {
        $received_quantity = 0;
        $received_value = 0;
    }

    // Fetch disposed quantity and value within the date range
    $sqlDisposed = "
        SELECT 
            SUM(di.quantity) AS disposed_quantity,
            SUM(di.quantity * p.price) AS disposed_value
        FROM disposed_items di
        JOIN dispose d ON di.dispose_id = d.dispose_id
        LEFT JOIN " . ($categoryCondition == 'Ingredients' ? 'ingredients p' : ($categoryCondition == 'Beverages' ? 'beverages p' : 'tots p')) . " ON di.product_id = p.id
        WHERE di.category = '$categoryCondition'
            AND di.product_id = '$productId'
            AND d.date BETWEEN '$start_date' AND '$end_date'
    ";
    $resultDisposed = mysqli_query($conn, $sqlDisposed);
    if ($resultDisposed) {
        $disposed = mysqli_fetch_assoc($resultDisposed);
        $disposed_quantity = isset($disposed['disposed_quantity']) ? $disposed['disposed_quantity'] : 0;
        $disposed_value = isset($disposed['disposed_value']) ? $disposed['disposed_value'] : 0;
    } else {
        $disposed_quantity = 0;
        $disposed_value = 0;
    }

    // Fetch sold quantity and value within the date range
    $sqlSold = "
        SELECT 
            SUM(s.quantity) AS sold_quantity,
            SUM(s.subtotal) AS sold_value
        FROM sales s
        LEFT JOIN transaction_receipts tr ON s.trans_id = tr.trans_id
        WHERE s.product_id = '$productId'
            AND tr.seller_date BETWEEN '$start_date' AND '$end_date'
            AND s.category = '$categoryCondition'
    ";
    $resultSold = mysqli_query($conn, $sqlSold);
    if ($resultSold) {
        $sold = mysqli_fetch_assoc($resultSold);
        $sold_quantity = isset($sold['sold_quantity']) ? $sold['sold_quantity'] : 0;
        $sold_value = isset($sold['sold_value']) ? $sold['sold_value'] : 0;
    } else {
        $sold_quantity = 0;
        $sold_value = 0;
    }

    // Calculate the adjusted opening stock
    $adjusted_quantity = $data['quantity'] + $received_quantity + $sold_quantity - $disposed_quantity;
    $data['quantity'] = $adjusted_quantity;
    $data['opening_value'] = $adjusted_quantity * $data['price'];
}

// Fetch received stock and value within the date range
$sqlReceivedStock = "
SELECT 
    CASE 
        WHEN po.category = 'Tots' AND t.tot_id IS NOT NULL THEN CONCAT('t_', t.tot_id)
        WHEN po.category = 'Beverages' AND b.beverage_id IS NOT NULL THEN CONCAT('b_', b.beverage_id)
        WHEN po.category = 'Ingredients' AND i.id IS NOT NULL THEN CONCAT('i_', i.id)
        ELSE CONCAT('unknown_', po.product_id) -- Handle unexpected cases
    END AS product_id, 
    po.category, 
    SUM(po.quantity) AS received_quantity, 
    SUM(po.subtotal) AS received_value
FROM 
    purchase_orders po
LEFT JOIN 
    tots t ON po.product_id = t.tot_id AND po.category = 'Tots'
LEFT JOIN 
    beverages b ON po.product_id = b.beverage_id AND po.category = 'Beverages'
LEFT JOIN 
    ingredients i ON po.product_id = i.id AND po.category = 'Ingredients'
WHERE 
    po.purchase_date BETWEEN '$start_date' AND '$end_date'
GROUP BY 
    product_id, 
    po.category;


";
$resultReceivedStock = mysqli_query($conn, $sqlReceivedStock);
if (!$resultReceivedStock) {
    die('Received Stock Query Failed: ' . mysqli_error($conn));
}
while ($row = mysqli_fetch_assoc($resultReceivedStock)) {
    $receivedStock[$row['product_id']] = $row;
}


// Initialize arrays to store fetched data
$soldStockIngredients = [];
$soldStockBeverages = [];
$soldStockTots = [];

$sqlSoldStock = "
    SELECT 
        CASE 
            WHEN s.category = 'Beverage' THEN CONCAT('b_', s.product_id)
            WHEN s.category = 'Tot' THEN CONCAT('t_', s.product_id)
            WHEN s.category = 'Food' THEN CONCAT('f_', s.product_id)
        END as product_id,
        s.category,
        SUM(s.quantity) AS sold_quantity, 
        SUM(s.subtotal) AS sold_value
    FROM sales s
    LEFT JOIN transaction_receipts tr ON s.trans_id = tr.trans_id
    WHERE tr.seller_date BETWEEN '$start_date' AND '$end_date'
        AND s.category IN ('Beverage', 'Tot', 'Food')
    GROUP BY 
        s.category, 
        product_id
";

$result = $conn->query($sqlSoldStock);

$soldStockIngredients = [];
$soldStockBeverages = [];
$soldStockTots = [];

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $productId = $row['product_id'];
        $soldQty = $row['sold_quantity'];
        $category = $row['category'];
        $soldValue = $row['sold_value'];

        if ($category == 'Food') {
            // Find the ingredients used for this food item
            $foodId = str_replace('f_', '', $productId);
            $sqlGetIngredients = "
                SELECT ingredient_id, quantity_required
                FROM food_ingredients 
                WHERE food_id = '$foodId'
            ";
            $ingredientResult = $conn->query($sqlGetIngredients);

            if ($ingredientResult->num_rows > 0) {
                $ingredients = [];
                while ($ingredient = $ingredientResult->fetch_assoc()) {
                    $ingredients[] = $ingredient;
                }

                $numIngredients = count($ingredients);
                $valuePerIngredient = $soldValue / $numIngredients;

                foreach ($ingredients as $ingredient) {
                    $ingredientId = $ingredient['ingredient_id'];
                    $quantityRequired = $ingredient['quantity_required'];
                    $totalIngredientUsed = $quantityRequired * $soldQty;

                    // Store the total ingredient used with a consistent key format
                    $key = "i_" . $ingredientId;
                    if (isset($soldStockIngredients[$key])) {
                        $soldStockIngredients[$key]['quantity'] += $totalIngredientUsed;
                        $soldStockIngredients[$key]['value'] += $valuePerIngredient;
                    } else {
                        $soldStockIngredients[$key] = ['quantity' => $totalIngredientUsed, 'value' => $valuePerIngredient];
                    }
                }
            }
        } elseif ($category == 'Tot') {
            $key = $productId;
            if (isset($soldStockTots[$key])) {
                $soldStockTots[$key]['quantity'] += $soldQty;
                $soldStockTots[$key]['value'] += $soldValue;
            } else {
                $soldStockTots[$key] = ['quantity' => $soldQty, 'value' => $soldValue];
            }
        } elseif ($category == 'Beverage') {
            $key = $productId;
            if (isset($soldStockBeverages[$key])) {
                $soldStockBeverages[$key]['quantity'] += $soldQty;
                $soldStockBeverages[$key]['value'] += $soldValue;
            } else {
                $soldStockBeverages[$key] = ['quantity' => $soldQty, 'value' => $soldValue];
            }
        }
    }
}

// Prepare the merged sold stock array
$soldStock = array_merge($soldStockIngredients, $soldStockBeverages, $soldStockTots);

// Output the final soldStock array
// Fetch physical closing stock
//$sqlPhysicalStock = "
  //  SELECT pu.product_id, pu.quantity AS physical_quantity,
  //         CASE WHEN b.beverage_name IS NOT NULL THEN 'Beverage' ELSE 'Food' END AS category
  //  FROM product_update pu
 //   LEFT JOIN beverages b ON pu.product_id = b.beverage_id
 //   WHERE pu.update_date = '$end_date'
//";
//$resultPhysicalStock = mysqli_query($conn, $sqlPhysicalStock);
//if (!$resultPhysicalStock) {
//    die('Physical Stock Query Failed: ' . mysqli_error($conn));
//}
///while ($row = mysqli_fetch_assoc($resultPhysicalStock)) {
   // $physicalStock[$row['product_id']] = $row;
//}

// Fetch disposed stock
$sqlDisposedStock = "
SELECT 
    CASE 
        WHEN di.category = 'Beverage' THEN CONCAT('b_', di.product_id)
        WHEN di.category = 'Tots' THEN CONCAT('t_', di.product_id)
        WHEN di.category = 'Ingredient' THEN CONCAT('i_', di.product_id)
    END AS product_id,
    SUM(di.quantity) AS disposed_quantity,di.category
FROM disposed_items di
JOIN dispose d ON di.dispose_id = d.dispose_id
WHERE d.date BETWEEN '$start_date' AND '$end_date'
GROUP BY product_id, di.category

";
$resultDisposedStock = mysqli_query($conn, $sqlDisposedStock);
if (!$resultDisposedStock) {
    die('Disposed Stock Query Failed: ' . mysqli_error($conn));
}
while ($row = mysqli_fetch_assoc($resultDisposedStock)) {
    $disposedStock[$row['product_id']] = $row;
}

// Generate the report
$report = [];
foreach ($openingStock as $product_id => $data) {
    // Initialize sold quantity and value
    $sold_quantity = 0;
    $sold_value = 0;

    // Fetch sold quantity and value based on $product_id
    foreach ($soldStock as $key => $value) {
        if ($key == $product_id) {
            $sold_quantity = isset($value['quantity']) ? $value['quantity'] : 0;
            $sold_value = isset($value['value']) ? $value['value'] : 0;
            break;
        }
    }

    // Calculate other quantities
    $received_quantity = isset($receivedStock[$product_id]) ? (float)$receivedStock[$product_id]['received_quantity'] : 0;
    $received_value = isset($receivedStock[$product_id]) ? (float)$receivedStock[$product_id]['received_value'] : 0;
    $disposed_quantity = isset($disposedStock[$product_id]) ? (float)$disposedStock[$product_id]['disposed_quantity'] : 0;
    $physical_quantity = isset($physicalStock[$product_id]) ? (float)$physicalStock[$product_id]['physical_quantity'] : (float)$data['quantity'];

    // Calculate closing quantity and value
    $initial_quantity = (float)$data['quantity'];
    $closing_quantity = $initial_quantity + $received_quantity - $sold_quantity - $disposed_quantity;
    $closing_value = $closing_quantity * (float)$data['price'];

    // Calculate variance
    $variance_qty = $physical_quantity - $closing_quantity;
    $variance_value = $variance_qty * (float)$data['price'];

    // Build report array
    $report[] = [
        'product_id' => $product_id,
        'productname' => $data['productname'],
        'category' => $data['category'],
        'opening_quantity' => $initial_quantity,
        'opening_value' => $data['opening_value'],
        'received_quantity' => $received_quantity,
        'received_value' => $received_value,
        'sold_quantity' => $sold_quantity,
        'sold_value' => $sold_value,
        'disposed_quantity' => $disposed_quantity,
        'closing_quantity' => $closing_quantity,
        'closing_value' => $closing_value,
        'physical_quantity' => $physical_quantity,
        'physical_value' => $physical_quantity * (float)$data['price'],
        'variance_qty' => $variance_qty,
        'variance_value' => $variance_value
    ];
}

// Output the report
echo json_encode($report);
?>