<?php
require_once 'shikanisha.kts.php';

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

// Initialize variables to store fetched data
$openingStock = [];
$receivedStock = [];
$soldStock = [];
$physicalStock = [];
$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,
        CONCAT(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;
}

foreach ($openingStock as $product_id => &$data) {
    // Initialize variables
    $received_quantity_before_start_date = 0;
    $sold_quantity_before_start_date = 0;

    // Determine the category-specific conditions
    if ($data['category'] == 'Ingredient') {
        $productId = str_replace('i_', '', $product_id);
        $sqlReceivedBeforeStartDate = "
            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 '" . $data['initial_date'] . "' AND '$end_date'
            GROUP BY po.product_id,category
        ";
    } elseif ($data['category'] == 'Beverages') {
        $productId = str_replace('b_', '', $product_id);
        $sqlReceivedBeforeStartDate = "
            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 '" . $data['initial_date'] . "' AND '$end_date'
            GROUP BY po.product_id,category
        ";
    } elseif ($data['category'] == 'Tot') {
        $productId = str_replace('t_', '', $product_id);
        $sqlReceivedBeforeStartDate = "
            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 '" . $data['initial_date'] . "' AND '$end_date'
            GROUP BY po.product_id,category
        ";
    }

    // Execute the query if it was set
    if (isset($sqlReceivedBeforeStartDate)) {
        $resultReceivedBeforeStartDate = mysqli_query($conn, $sqlReceivedBeforeStartDate);
        if ($resultReceivedBeforeStartDate) {
            $receivedBeforeStartDate = mysqli_fetch_assoc($resultReceivedBeforeStartDate);
            $received_quantity_before_start_date = isset($receivedBeforeStartDate['received_quantity']) ? $receivedBeforeStartDate['received_quantity'] : 0;
        }
    }

    // Fetch total sold quantity and value from initial_date to start_date
// Fetch total sold quantity and value from initial_date to start_date
if ($data['category'] == 'Ingredient') {
    $sold_quantity_before_start_date = 0;

    // Fetch ingredients used in this food product
    $food_id = str_replace('i_', '', $product_id); // Extract the food_id from product_id

    $sqlGetIngredients = "
        SELECT fi.ingredient_id, fi.quantity_required
        FROM food_ingredients fi
        WHERE fi.food_id = 1
    ";

    $resultIngredients = mysqli_query($conn, $sqlGetIngredients);
    if (!$resultIngredients) {
        die('Error fetching ingredients: ' . mysqli_error($conn));
    }

    while ($ingredient = mysqli_fetch_assoc($resultIngredients)) {
        $ingredient_id = $ingredient['ingredient_id'];
        $quantity_required = $ingredient['quantity_required'];

        // Query to get sold quantity for this ingredient
        $sqlSoldIngredient = "
            SELECT 
                SUM(s.quantity) AS sold_quantity
            FROM sales s
            LEFT JOIN transaction_receipts t ON s.trans_id = t.trans_id
            WHERE s.product_id = 1 
                AND category = 'Food'
                AND t.seller_date BETWEEN '" . $data['initial_date'] . "' AND '$end_date'
        ";

        $resultSoldIngredient = mysqli_query($conn, $sqlSoldIngredient);
        if (!$resultSoldIngredient) {
            die('Error fetching sold quantity: ' . mysqli_error($conn));
        }

        $soldIngredient = mysqli_fetch_assoc($resultSoldIngredient);
        $sold_quantity_before_start_date += isset($soldIngredient['sold_quantity']) ? ($soldIngredient['sold_quantity'] * $quantity_required) : 0;
    }
}

 else {
        // For tots and beverages, fetch sold quantities directly from sales table
        $sqlSoldBeforeStartDate = "
             SELECT 
    CASE 
        WHEN s.product_id LIKE 't_%' THEN 'Tot'
        WHEN s.product_id LIKE 'b_%' THEN 'Beverage'
    END as category,
    s.product_id,
    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
LEFT JOIN beverages b ON CONCAT('b_', s.product_id) = CONCAT('b_', b.beverage_id)
LEFT JOIN tots t ON CONCAT('t_', s.product_id) = CONCAT('t_', t.tot_id)
WHERE tr.seller_date BETWEEN '" . $data['initial_date'] . "' AND '$end_date'
GROUP BY 
    category,
    s.product_id;

        ";

        $resultSoldBeforeStartDate = mysqli_query($conn, $sqlSoldBeforeStartDate);
        $soldBeforeStartDate = mysqli_fetch_assoc($resultSoldBeforeStartDate);

        $sold_quantity_before_start_date = isset($soldBeforeStartDate['sold_quantity']) ? $soldBeforeStartDate['sold_quantity'] : 0;
    }

    // Calculate the adjusted opening stock
    $adjusted_quantity = $data['quantity'] + $received_quantity_before_start_date + $sold_quantity_before_start_date;
    $data['quantity'] = $adjusted_quantity;
    $data['opening_value'] = $adjusted_quantity * $data['price'];
}



// Fetch received stock and value within the date range
$sqlReceivedStock = "
SELECT 
    CASE 
        WHEN t.tot_id IS NOT NULL THEN CONCAT('t_', t.tot_id)
        WHEN b.beverage_id IS NOT NULL THEN CONCAT('b_', b.beverage_id)
        ELSE CONCAT('i_', po.product_id) 
    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 = 'Tot'
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 = 'Beverages' 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 ('Beverages', 'Tot', 'Food')
    GROUP BY 
        s.category, 
        product_id
";

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

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) {
                while ($ingredient = $ingredientResult->fetch_assoc()) {
                    $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'] += $soldValue;
                    } else {
                        $soldStockIngredients[$key] = ['quantity' => $totalIngredientUsed, 'value' => $soldValue];
                    }
                }
            }
        } 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 == 'Beverages') {
            $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 di.product_id, SUM(di.quantity) AS disposed_quantity
    FROM disposed_items di
    WHERE di.dispose_date BETWEEN '$start_date' AND '$end_date'
    GROUP BY di.product_id
";
$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) {
        // Check if the key starts with the product_id (assuming $product_id is numeric)
        if (strpos($key, (string)$product_id) !== false) {
            $sold_quantity = isset($value['quantity']) ? $value['quantity'] : 0; // Retrieve sold quantity
            $sold_value = isset($value['value']) ? $value['value'] : 0; // Retrieve sold value
            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 ;
    $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' => $data['quantity'] - $received_quantity + $sold_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

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

