<?php
include 'db.php';
function handle_db_error($message, $con) {
    echo json_encode([
        "status" => "error",
        "message" => $message . ": " . mysqli_error($con)
    ]);
    mysqli_close($con);
    exit;
}
function sanitize_input($con, $input) {
    return mysqli_real_escape_string($con, trim($input));
}

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $current_time = date('H:i:s');
    if (!empty($_POST['invoice_date'])) {
        $invoice_date_obj = DateTime::createFromFormat('Y-m-d', $_POST['invoice_date']);
        $invoice_date = $invoice_date_obj ? $invoice_date_obj->format('Y-m-d') . ' ' . $current_time : date('Y-m-d H:i:s');
    } else {
        $invoice_date = date('Y-m-d H:i:s');
    }
    if (!empty($_POST['due_date'])) {
        $due_date_obj = DateTime::createFromFormat('Y-m-d', $_POST['due_date']);
        $due_date = $due_date_obj ? $due_date_obj->format('Y-m-d') . ' ' . $current_time : date('Y-m-d H:i:s', strtotime('+30 days'));
    } else {
        $due_date = date('Y-m-d H:i:s', strtotime('+30 days'));
    }
    $invoice_number = sanitize_input($con, $_POST['invoice_number']);
    $customer = sanitize_input($con, $_POST['customer']);
    $cus_gst = sanitize_input($con, $_POST['cus_gst']);
    $vehical_number = sanitize_input($con, isset($_POST['vehical_number']) ? $_POST['vehical_number'] : '');
    $customer_vehicle = sanitize_input($con, isset($_POST['customer_vehicle']) ? $_POST['customer_vehicle'] : '');

    // Customer details
    $customer_name = sanitize_input($con, $_POST['customer_name']);
    $customer_email = sanitize_input($con, $_POST['customer_email']);
    $customer_phone = sanitize_input($con, $_POST['customer_phone']);
    $customer_billing_address = sanitize_input($con, $_POST['customer_billing_address']);
    $customer_billing_city = sanitize_input($con, $_POST['customer_billing_city']);
    $customer_billing_state = sanitize_input($con, $_POST['customer_billing_state']);
    $customer_billing_pincode = sanitize_input($con, $_POST['customer_billing_pincode']);
    $customer_gst = sanitize_input($con, $_POST['customer_gst']);

    // Shipping details (fallback to customer details if empty)
    $shipping_name = !empty($_POST['shipping_name']) ? sanitize_input($con, $_POST['shipping_name']) : $customer_name;
    $shipping_city = !empty($_POST['shipping_city']) ? sanitize_input($con, $_POST['shipping_city']) : $customer_billing_city;
    $shipping_address = !empty($_POST['shipping_address']) ? sanitize_input($con, $_POST['shipping_address']) : $customer_billing_address;
    $shipping_state = !empty($_POST['shipping_state']) ? sanitize_input($con, $_POST['shipping_state']) : $customer_billing_state;
    $shipping_pincode = !empty($_POST['shipping_pincode']) ? sanitize_input($con, $_POST['shipping_pincode']) : $customer_billing_pincode;

    // Customer address
    $customer_add = isset($_POST['customer_add']) ? sanitize_input($con, $_POST['customer_add']) : '';

    // Check for existing invoice numbers and auto-increment if needed
    $check_query = "SELECT invoice_number FROM invoices ORDER BY invoice_number ASC";
    $result = mysqli_query($con, $check_query);
    if (!$result) {
        handle_db_error("Error checking invoice numbers", $con);
    }
    
    $existing_invoice_numbers = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $existing_invoice_numbers[] = (int) $row['invoice_number'];
    }
    if (in_array((int) $invoice_number, $existing_invoice_numbers)) {
        $invoice_number = empty($existing_invoice_numbers) ? 1 : max($existing_invoice_numbers) + 1;
    }

    // Products
    if (!isset($_POST['kt_docs_repeater_basic']) || !is_array($_POST['kt_docs_repeater_basic'])) {
        echo json_encode([
            "status" => "error",
            "message" => "No products selected for invoice"
        ]);
        exit;
    }
    
    $products = $_POST['kt_docs_repeater_basic'];

    // Start transaction
    mysqli_begin_transaction($con);

    try {
        // Insert invoice details
        $query = "INSERT INTO invoices (invoice_date, invoice_number, due_date, customer_id, customer_add, gst_type, vehical_number, customer_vehicle) 
                VALUES ('$invoice_date', '$invoice_number', '$due_date', '$customer', '$customer_add', '$cus_gst', '$vehical_number', '$customer_vehicle')";

        if (!mysqli_query($con, $query)) {
            throw new Exception("Error inserting invoice: " . mysqli_error($con));
        }

        $invoice_id = mysqli_insert_id($con);

        // Insert customer and shipping details
        $customer_query = "INSERT INTO invoice_customerdetails (invoice_cid, customer_name, customer_email, customer_phone, 
                        customer_billing_address, customer_billing_city, customer_billing_state, customer_billing_pincode, 
                        customer_gst, shipping_name, shipping_city, shipping_address, shipping_state, shipping_pincode) 
                        VALUES ('$invoice_id', '$customer_name', '$customer_email', '$customer_phone', 
                        '$customer_billing_address', '$customer_billing_city', '$customer_billing_state', 
                        '$customer_billing_pincode', '$customer_gst', '$shipping_name', '$shipping_city', 
                        '$shipping_address', '$shipping_state', '$shipping_pincode')";

        if (!mysqli_query($con, $customer_query)) {
            throw new Exception("Error inserting customer details: " . mysqli_error($con));
        }

        // Insert products related to the invoice
        foreach ($products as $product) {
            $pid = sanitize_input($con, $product['pid']);
            $packing = sanitize_input($con, $product['packing']);
            $iqty = (int)$product['iqty'];
            $newprice = (float)$product['newprice'];

            // Get the name from the 'package' table based on the 'packing' value
            $package_check_query = "SELECT name FROM package WHERE paid = '$packing'"; // Assuming 'id' is the key for packing
            $package_check_result = mysqli_query($con, $package_check_query);

            if (!$package_check_result) {
                throw new Exception("Error checking package name for packing ID $packing: " . mysqli_error($con));
            }

            if (mysqli_num_rows($package_check_result) == 0) {
                throw new Exception("Package with packing ID $packing not found in the package table.");
            }

            $package_row = mysqli_fetch_assoc($package_check_result);
            $packing_name = $package_row['name']; // Get the name of the package

            // Now, check stock in 'variation_multi' with the retrieved 'packing_name'
            $stock_query = "SELECT qty, value1 FROM variation_multi WHERE pid = '$pid' and value1='$packing_name'";
            $stock_result = mysqli_query($con, $stock_query);

            if (!$stock_result) {
                throw new Exception("Error checking stock for product ID $pid: " . mysqli_error($con));
            }

            if (mysqli_num_rows($stock_result) > 0) {
                $stock_row = mysqli_fetch_assoc($stock_result);
                $available_qty = (int)$stock_row['qty'];
                $stock_packing = $stock_row['value1'];

                // Check if the package name matches
                if ($packing_name !== $stock_packing) {
                    throw new Exception("Package type mismatch for product ID $pid. Requested: $packing_name, Available: $stock_packing");
                }

                // Check quantity
                if ($iqty > $available_qty) {
                    throw new Exception("Ordered quantity ($iqty) exceeds available stock ($available_qty) for product ID $pid.");
                }

                // Insert product into invoice_products table
                $product_query = "INSERT INTO invoice_products (invoice_id, pid, package, quan, price) 
                VALUES ('$invoice_id', '$pid', '$packing', '$iqty', '$newprice')";

                if (!mysqli_query($con, $product_query)) {
                    throw new Exception("Error inserting product: " . mysqli_error($con));
                }

                // Update stock in variation_multi
                $update_stock = "UPDATE variation_multi SET qty = qty - $iqty WHERE pid = '$pid' AND value1 = '$packing_name'";
                if (!mysqli_query($con, $update_stock)) {
                    throw new Exception("Error updating stock for product ID $pid and package $packing_name: " . mysqli_error($con));
                }

                $updateStock = "UPDATE stock SET quan = quan + $iqty WHERE pid = '$pid' AND pack = '$packing_name'";
                if (!mysqli_query($con, $updateStock)) {
                    throw new Exception("Error updating MAIN STOCK for product ID $pid and package $packing_name: " . mysqli_error($con));
                }

            } else {
                throw new Exception("Product with ID $pid not found in stock.");
            }
        }
        mysqli_commit($con);
        echo json_encode([
            "status" => "success",
            "message" => "Invoice created successfully! Invoice Number: $invoice_number"
        ]);
        
    } catch (Exception $e) {
        mysqli_rollback($con);
        
        echo json_encode([
            "status" => "error",
            "message" => $e->getMessage()
        ]);
    }

    mysqli_close($con);
}
?>