<?php
include 'config.php';

mysqli_begin_transaction($conn);

try {

    $customer_id = $_POST['customer_id'];
    $sale_date = date("Y-m-d");
    $invoice_no = "INV" . time();

    // 1️⃣ Insert into sales table
    $insert_sale = mysqli_query($conn, "
        INSERT INTO sales (invoice_no, customer_id, sale_date) 
        VALUES ('$invoice_no', '$customer_id', '$sale_date')
    ");

    if(!$insert_sale){
        throw new Exception("Sale insert failed");
    }

    $sale_id = mysqli_insert_id($conn);
    $total = 0;

    // 2️⃣ Loop through products
    foreach($_POST['inventory_id'] as $key => $inventory_id){

        $qty = $_POST['quantity'][$key];

        // Get product details
        $product = mysqli_fetch_assoc(
            mysqli_query($conn, "SELECT * FROM inventory WHERE id = $inventory_id")
        );

        if($product['quantity'] < $qty){
            throw new Exception("Insufficient stock for ".$product['name']);
        }

        $price = $product['unit_price'];
        $subtotal = $qty * $price;
        $total += $subtotal;

        // Insert into sale_items
        $insert_items = mysqli_query($conn, "
            INSERT INTO sale_items 
            (sale_id, inventory_id, quantity, unit_price, subtotal)
            VALUES ('$sale_id', '$inventory_id', '$qty', '$price', '$subtotal')
        ");

        if(!$insert_items){
            throw new Exception("Sale item insert failed");
        }

        // Update stock
        $update_stock = mysqli_query($conn, "
            UPDATE inventory 
            SET quantity = quantity - $qty 
            WHERE id = $inventory_id
        ");

        if(!$update_stock){
            throw new Exception("Stock update failed");
        }
    }

    // 3️⃣ Update total amount
    mysqli_query($conn, "
        UPDATE sales 
        SET total_amount = '$total', grand_total = '$total'
        WHERE id = '$sale_id'
    ");

    mysqli_commit($conn);

    echo "Sale Saved Successfully! <br>";
    echo "<a href='add_sale.php'>Add Another Sale</a>";

} catch (Exception $e) {

    mysqli_rollback($conn);
    echo "Error: " . $e->getMessage();
}
?>
