<?php
session_start();

// Retrieve session data
$user = $_SESSION['user'];
$cart = $_SESSION['cart'];
$employee_id = $_SESSION['user_id']; // Get employee ID from session

// Database connection details
require_once('shikanisha.kts.php');

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Default values
    $phone = '0712345678'; // Example phone number
    $method = 'To Pay'; // Example payment method
    $cash = 0; // Example cash amount

    // Disable autocommit
    mysqli_autocommit($conn, false);

    // Start a transaction
    mysqli_begin_transaction($conn);

    $transactionSuccess = true;
    $errorMessage = '';

    try {
        // 1. Get employee's active shift
        $shiftQuery = "SELECT shift_id FROM shifts 
                      WHERE employee_id = ? AND status = 'active' 
                      LIMIT 1";
        $stmt = $conn->prepare($shiftQuery);
        if (!$stmt) {
            throw new Exception("Error preparing shift query: " . $conn->error);
        }
        $stmt->bind_param("i", $employee_id);
        if (!$stmt->execute()) {
            throw new Exception("Error executing shift query: " . $stmt->error);
        }
        $shift = $stmt->get_result()->fetch_assoc();
        
        if (!$shift) {
            throw new Exception("You don't have an active shift");
        }
        $shift_id = $shift['shift_id'];

        $cartDetails = [];
        $total = 0;

        // Loop through the cart items and calculate subtotals
        foreach ($cart as $item) {
            $itemName = $item['food_name'];
            $productid = $item['food_id'];
            $itemQuantity = intval($item['quantity']);
            $itemPrice = isset($item['food_price']) ? floatval($item['food_price']) : 0;
            $subtotal = $itemPrice * $itemQuantity;
            
            // Handle category - default to 'Food' if not Beverage/Beverages
            $cat = isset($item['cat']) ? $item['cat'] : 'Food';
            $cat = (strtolower($cat) === 'beverage' || (strtolower($cat) === 'beverages')) ? 'Beverage' : 'Food';

            // Add to the cart details
            $cartDetails[] = [
                'name' => $itemName,
                'productid' => $productid,
                'price' => $itemPrice,
                'quantity' => $itemQuantity,
                'subtotal' => $subtotal,
                'cat' => $cat,
            ];

            // Update the total sum
            $total += $subtotal;
        }

        // Insert the sale transaction into the database with shift_id
        $insertSaleTransactionQuery = "INSERT INTO transaction_receipts 
                                    (phone_number, total, payment_method, seller_date, 
                                    seller_name, amount_received, shift_id, order_employee_id) 
                                    VALUES (?, ?, ?, NOW(), ?, ?, ?, ?)";
        $stmt = $conn->prepare($insertSaleTransactionQuery);
        if (!$stmt) {
            throw new Exception("Error preparing sale transaction query: " . $conn->error);
        }
        $stmt->bind_param("sdsssii", $phone, $total, $method, $user, $cash, $shift_id, $employee_id);
        if (!$stmt->execute()) {
            throw new Exception("Error inserting sale transaction: " . $stmt->error);
        }

        // Get the transaction ID for further use
        $stid = $stmt->insert_id;
        $_SESSION['trans_id'] = $stid;

        // Process each cart item
        foreach ($cart as $item) {
            $itemName = $item['food_name'];
            $productid = $item['food_id'];
            $itemQuantity = intval($item['quantity']);
            $itemPrice = isset($item['food_price']) ? floatval($item['food_price']) : 0;
            $subtotal = $itemPrice * $itemQuantity;
            
            // Handle category
            $cat = isset($item['cat']) ? $item['cat'] : 'Food';
            $cat = (strtolower($cat) === 'beverage' || (strtolower($cat) === 'beverages')) ? 'Beverage' : 'Food';
            
            $bp = 0; // Default Buying Price for items

            if ($cat === 'Beverage') {
                // Handle beverages
                $sqlGetBeverage = "SELECT buying_price, quantity FROM beverages WHERE beverage_id = ?";
                $stmt = $conn->prepare($sqlGetBeverage);
                if (!$stmt) {
                    throw new Exception("Error preparing get beverage query: " . $conn->error);
                }
                $stmt->bind_param("i", $productid);
                if (!$stmt->execute()) {
                    throw new Exception("Error executing get beverage query: " . $stmt->error);
                }
                $result = $stmt->get_result();

                if ($result && $result->num_rows > 0) {
                    $beverage = $result->fetch_assoc();
                    $bp = floatval($beverage['buying_price']);

                    if ($beverage['quantity'] < $itemQuantity) {
                        throw new Exception("Insufficient quantity for beverage: " . $itemName);
                    }

                    // Update the beverage quantity
                    $sqlUpdateBeverage = "UPDATE beverages SET quantity = quantity - ? WHERE beverage_id = ?";
                    $stmt = $conn->prepare($sqlUpdateBeverage);
                    if (!$stmt) {
                        throw new Exception("Error preparing update beverage query: " . $conn->error);
                    }
                    $stmt->bind_param("ii", $itemQuantity, $productid);
                    if (!$stmt->execute()) {
                        throw new Exception("Error updating beverage quantity: " . $stmt->error);
                    }
                } else {
                    throw new Exception("Beverage not found.");
                }
            } else {
                // Handle food items
                $sqlGetFood = "SELECT cost_price FROM food WHERE food_id = ?";
                $stmt = $conn->prepare($sqlGetFood);
                if (!$stmt) {
                    throw new Exception("Error preparing get food query: " . $conn->error);
                }
                $stmt->bind_param("i", $productid);
                if (!$stmt->execute()) {
                    throw new Exception("Error executing get food query: " . $stmt->error);
                }
                $result = $stmt->get_result();

                if ($result && $result->num_rows > 0) {
                    $food = $result->fetch_assoc();
                    $bp = floatval($food['cost_price']);
                } else {
                    throw new Exception("Food item not found.");
                }

                // Deduct ingredients
                $sqlGetIngredients = "SELECT ingredient_id, quantity_required FROM food_ingredients WHERE food_id = ?";
                $stmt = $conn->prepare($sqlGetIngredients);
                if (!$stmt) {
                    throw new Exception("Error preparing get ingredients query: " . $conn->error);
                }
                $stmt->bind_param("i", $productid);
                if (!$stmt->execute()) {
                    throw new Exception("Error executing get ingredients query: " . $stmt->error);
                }
                $result = $stmt->get_result();

                if ($result && $result->num_rows > 0) {
                    while ($ingredient = $result->fetch_assoc()) {
                        $ingredientId = $ingredient['ingredient_id'];
                        $ingredientQuantity = $ingredient['quantity_required'] * $itemQuantity;

                        $sqlUpdateIngredient = "UPDATE ingredients SET quantity = quantity - ? WHERE id = ?";
                        $stmt = $conn->prepare($sqlUpdateIngredient);
                        if (!$stmt) {
                            throw new Exception("Error preparing update ingredient query: " . $conn->error);
                        }
                        $stmt->bind_param("ii", $ingredientQuantity, $ingredientId);
                        if (!$stmt->execute()) {
                            throw new Exception("Error updating ingredient quantity: " . $stmt->error);
                        }
                    }
                }
            }

            // Insert sale item with buying price
            $sqlInsertItemSales = "INSERT INTO sales (trans_id, product_name, product_id, quantity, selling_price, subtotal, category, bp) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt = $conn->prepare($sqlInsertItemSales);
            if (!$stmt) {
                throw new Exception("Error preparing insert sale item query: " . $conn->error);
            }
            $stmt->bind_param("isidddss", $stid, $itemName, $productid, $itemQuantity, $itemPrice, $subtotal, $cat, $bp);
            if (!$stmt->execute()) {
                throw new Exception("Error inserting sale item: " . $stmt->error);
            }
        }

        // Update the shift totals
        $updateShiftQuery = "UPDATE shifts SET
                       total_orders = IFNULL(total_orders, 0) + ?,
                       total_debt = IFNULL(total_debt, 0) + ?
                       WHERE shift_id = ?";
    $stmt = $conn->prepare($updateShiftQuery);
    if (!$stmt) {
        throw new Exception("Error preparing update shift query: " . $conn->error);
    }
    
    // Ensure we're using the correct data types in bind_param
    // 'd' for double/decimal, 'i' for integer
    $stmt->bind_param("ddi", $total, $total, $shift_id);
    
    if (!$stmt->execute()) {
        throw new Exception("Error updating shift totals: " . $stmt->error);
    }


        // Commit the transaction if everything is successful
        mysqli_commit($conn);
    } catch (Exception $e) {
        // Rollback the transaction if any error occurs
        mysqli_rollback($conn);
        $transactionSuccess = false;
        $errorMessage = $e->getMessage();
    } finally {
        // Re-enable autocommit
        mysqli_autocommit($conn, true);
        mysqli_close($conn);
    }

    // Return JSON response
    $responseData = [
        'success' => $transactionSuccess,
        'message' => $transactionSuccess ? "Transaction completed successfully." : "Transaction failed: " . $errorMessage,
        'trans_id' => $stid ?? null,
        'shift_id' => $shift_id ?? null
    ];
    header('Content-Type: application/json');
    echo json_encode($responseData);
}



