<?php
include 'db_connection.php';

// Fetch customers
$customers = $con->query("SELECT id, customer_name FROM customer");

// Fetch products
$products = $con->query("SELECT Product_id, name, price FROM product");

// Fetch product categories
$products_cat = $con->query("SELECT * FROM product_cat");

// Handle Sale Creation
if (isset($_POST['createSale'])) {
    $customer_name = $_POST['customer_name'] ?? '';
    $customer_address = $_POST['customer_address'] ?? '';
    $payment_method = $_POST['payment_method'] ?? '';
    $deposit = $_POST['deposit'] ?? 0;
    $discount = $_POST['discount'] ?? 0;
    $grand_total = $_POST['grand_total'] ?? 0;
    $created_at = date("Y-m-d H:i:s");

    $stmt = $con->prepare("INSERT INTO sales (customer_name, customer_address, deposit, discount, grand_total, created_at) VALUES (?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("ssddds", $customer_name, $customer_address, $deposit, $discount, $grand_total, $created_at);
    $stmt->execute();
    $sale_id = $stmt->insert_id;

    if (isset($_POST['product_id']) && is_array($_POST['product_id'])) {
        foreach ($_POST['product_id'] as $index => $product_id) {
            $product_description = $_POST['product_description'][$index];
            $quantity = $_POST['quantity'][$index];
            $price = $_POST['price'][$index];
            $purchase_price = $_POST['purchase_price'][$index];
            $total = $price * $quantity;

            $check_stock = $con->query("SELECT SUM(Quantity) as available_quantity FROM product WHERE Product_id = '$product_id'");
            $row = $check_stock->fetch_assoc();
            $available_quantity = $row['available_quantity'];

            if ($quantity > $available_quantity) {
                echo "<script>alert('Insufficient stock for product ID $product_id. Only $available_quantity available.');</script>";
                continue;
            }

            $item_stmt = $con->prepare("INSERT INTO sales_items (sales_id, product_id, product_description, quantity, price, purchase_price, total) VALUES (?, ?, ?, ?, ?, ?, ?)");
            $item_stmt->bind_param("iisiddd", $sale_id, $product_id, $product_description, $quantity, $price, $purchase_price, $total);
            $item_stmt->execute();

            $con->query("UPDATE product SET Quantity = Quantity - $quantity WHERE Product_id = '$product_id' AND Quantity >= $quantity");
        }
    }
    header("Location: create_sale_form.php");
    exit;
}
?>
