<?php
include_once 'shikanisha.kts.php';
session_start();

$response = array(); // Response array to hold success/failure messages

if (!empty($_SESSION['cart'])) {
    $invoice = $_POST['invoice'];

    // Set the timezone to Africa/Nairobi
    $timezone = new DateTimeZone('Africa/Nairobi');

    // Create a DateTime object with the current date and time in the specified timezone
    $date = new DateTime('now', $timezone);

    // Format the date as a string
    $formattedDate = $date->format('Y-m-d H:i:s');

    $totalCost = 0; // Initialize total cost

    // Start a transaction
    $conn->begin_transaction();

    try {
        // Insert into the issued table
        $issue_sql = "INSERT INTO `issued`(`issued_by`, `date_time`) VALUES (?, ?)";
        $issue_stmt = $conn->prepare($issue_sql);
        $issue_stmt->bind_param("ss", $invoice, $formattedDate);
        $issue_result = $issue_stmt->execute();

        if (!$issue_result) {
            throw new Exception("Invoice insertion failed");
        }

        // Get the last inserted invoice ID
        $invoice_id = $conn->insert_id;

        // Update the products and insert into the issued_products table
       // Update the products and insert into the issued_products table
foreach ($_SESSION['cart'] as $item) {
    $product_id = $item['product_id'];
    $buying_price = $item['buying_price'];
    $requested_quantity = $item['quantity'];

    // Check available quantity
    $available_quantity_sql = "SELECT shop_quantity FROM products WHERE product_id = ?";
    $available_quantity_stmt = $conn->prepare($available_quantity_sql);
    $available_quantity_stmt->bind_param("i", $product_id);
    $available_quantity_stmt->execute();
    $available_quantity_result = $available_quantity_stmt->get_result();
    
    if ($available_quantity_result->num_rows === 0) {
        throw new Exception("Product not found");
    }

    $row = $available_quantity_result->fetch_assoc();
    $available_quantity = $row['shop_quantity'];

    // Determine the quantity to issue (minimum of requested and available)
    $quantity_to_issue = min($requested_quantity, $available_quantity);

    // Calculate subtotal
    $subtotal = $quantity_to_issue * $buying_price;

    // Update the product
    $update_sql = "UPDATE products SET shop_quantity = shop_quantity - ? WHERE product_id = ?";
    $update_stmt = $conn->prepare($update_sql);
    $update_stmt->bind_param("ii", $quantity_to_issue, $product_id);
    $update_result = $update_stmt->execute();

    if (!$update_result) {
        throw new Exception("Product update failed");
    }

    // Add subtotal to total cost
    $totalCost += $subtotal;

    // Insert into the issued_products table
    $issued_products_sql = "INSERT INTO `issued_products`(`product_name`, `quantity`, `product_id`, `issue_id`) VALUES (?, ?, ?, ?)";
    $issued_products_stmt = $conn->prepare($issued_products_sql);
    $issued_products_stmt->bind_param("sidi", $item['product_name'], $quantity_to_issue, $product_id, $invoice_id);
    $issued_products_result = $issued_products_stmt->execute();

    if (!$issued_products_result) {
        throw new Exception("Record not added to issued_products successfully");
    }

    // If the requested quantity exceeds the available quantity, provide feedback
    if ($requested_quantity > $available_quantity) {
        $response['message'] = "Some products were issued successfully, but the requested quantity for '{$item['product_name']}' exceeds the available quantity. Only {$available_quantity} available.";
    }
}



        // Commit the transaction
        $conn->commit();

        // Provide success response
        $response['success'] = true;
        $response['message'] = "Products issued Successfully";
    } catch (Exception $e) {
        // If any query fails, roll back the transaction
        $conn->rollback();
        $response['success'] = false;
        $response['message'] = $e->getMessage();
    }

    $issue_stmt->close();
    $update_stmt->close();
    $issued_products_stmt->close();
} else {
    $response['success'] = false;
    $response['message'] = "Cart is empty.";
}

// Provide JSON response
header('Content-Type: application/json');
echo json_encode($response);

$conn->close();
?>
