<?php
include "../db/headerApi.php";
include "../db/connection_main.php";

$json = file_get_contents('php://input');
$data = json_decode($json);

if (!is_object($data)) {
    echo json_encode(["status" => false, "message" => "Invalid JSON received"]);
    exit;
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    echo json_encode(["status" => false, "message" => "Invalid request method"]);
    exit;
}

if (
    !property_exists($data, "document_no") || trim((string)$data->document_no) === '' ||
    !property_exists($data, "details") || !is_array($data->details) || count($data->details) === 0
) {
    echo json_encode(["status" => false, "message" => "document_no and details are required"]);
    exit;
}

if (!property_exists($data, "user_id") || trim((string)$data->user_id) === '') {
    echo json_encode(["status" => false, "message" => "Please login"]);
    exit;
}

$documentNo = trim((string)$data->document_no);
$user = trim((string)$data->user_id);
$conn = $dbcon;

try {

    /** -----------------------------------------------------------
     * CHECK DOCUMENT EXISTS
     * ---------------------------------------------------------- */
    $sqlCheck = "SELECT COUNT(*) AS CNT
                 FROM GTH_MEDISAFE_INVOICE_HEADER
                 WHERE DOCUMENT_NO = :document_no";

    $stmtCheck = oci_parse($conn, $sqlCheck);
    oci_bind_by_name($stmtCheck, ":document_no", $documentNo);

    if (!oci_execute($stmtCheck, OCI_NO_AUTO_COMMIT)) {
        $e = oci_error($stmtCheck);
        throw new Exception("Error checking existing document: " . $e['message']);
    }

    $rowCheck = oci_fetch_assoc($stmtCheck);
    if ($rowCheck && (int)$rowCheck['CNT'] > 0) {
        throw new Exception("Document number '$documentNo' already exists.");
    }


    /** -----------------------------------------------------------
     * INSERT HEADER
     * ---------------------------------------------------------- */
    $sqlHeader = "INSERT INTO GTH_MEDISAFE_INVOICE_HEADER
                    (DOCUMENT_NO, GROSS_WEIGHT, NET_WEIGHT, CONTAINER_NO, SEAL_NO,
                     BILL_LOADING_NO, MEASUREMENT, REMARKS, STATUS,
                     CREATED_AT, CREATED_BY, UPDATED_AT, UPDATED_BY)
                  VALUES
                    (:document_no, :gross_weight, :net_weight, :container_no, :seal_no,
                     :bill_loading_no, :measurement, :remarks, 1,
                     SYSDATE, :created_by, SYSDATE, :updated_by)
                  RETURNING ID INTO :header_id";

    $stmtHeader = oci_parse($conn, $sqlHeader);

    // Bind safely with null-coalescing
    oci_bind_by_name($stmtHeader, ":document_no", $documentNo);

    $gross_weight   = $data->gross_weight ?? null;
    $net_weight     = $data->net_weight ?? null;
    $container_no   = $data->container_no ?? null;
    $seal_no        = $data->seal_no ?? null;
    $bill_loading_no = $data->bill_loading_no ?? null;
    $measurement    = $data->measurement ?? null;
    $remarks        = $data->remarks ?? null;

    oci_bind_by_name($stmtHeader, ":gross_weight", $gross_weight);
    oci_bind_by_name($stmtHeader, ":net_weight", $net_weight);
    oci_bind_by_name($stmtHeader, ":container_no", $container_no);
    oci_bind_by_name($stmtHeader, ":seal_no", $seal_no);
    oci_bind_by_name($stmtHeader, ":bill_loading_no", $bill_loading_no);
    oci_bind_by_name($stmtHeader, ":measurement", $measurement);
    oci_bind_by_name($stmtHeader, ":remarks", $remarks);

    oci_bind_by_name($stmtHeader, ":created_by", $user);
    oci_bind_by_name($stmtHeader, ":updated_by", $user);

    $newHeaderId = 0;
    oci_bind_by_name($stmtHeader, ":header_id", $newHeaderId, -1, SQLT_INT);


    if (!oci_execute($stmtHeader, OCI_NO_AUTO_COMMIT)) {
        $e = oci_error($stmtHeader);
        throw new Exception("Failed to insert header: " . $e['message']);
    }


    /** -----------------------------------------------------------
     * INSERT INVOICES
     * ---------------------------------------------------------- */
    foreach ($data->details as $invoice) {

        if (
            !property_exists($invoice, "brand_id") || trim((string)$invoice->brand_id) === '' ||
            !property_exists($invoice, "brand_name") || trim((string)$invoice->brand_name) === '' ||
            !property_exists($invoice, "po_number") || trim((string)$invoice->po_number) === '' ||
            !property_exists($invoice, "item_details") || !is_array($invoice->item_details) ||
            count($invoice->item_details) === 0
        ) {
            throw new Exception("Each invoice must contain brand_id,brand_name, po_number, and item_details.");
        }

        $brandId = trim((string)$invoice->brand_id);
        $brandName = trim((string)$invoice->brand_name);
        $poNumber = trim((string)$invoice->po_number);
        $packingDetails = isset($invoice->packing_details) ? trim((string)$invoice->packing_details) : '';
        $itemDetails = $invoice->item_details;

        // Validate active brand
        $post_brand_id = $brandId;
        $post_brand_name = $brandName;

        $sqlBrand = "SELECT ID, BRAND_ID, BRAND_NAME FROM YTYCUSTOM.GTH_CUSTOMER_BRAND_MASTER WHERE CUSTOMER_ID = '445212' AND LOWER(brand_id) = LOWER(:brand_id)";

        $stmtBrand = oci_parse($conn, $sqlBrand);
        oci_bind_by_name($stmtBrand, ":brand_id", $brandId);

        if (!oci_execute($stmtBrand, OCI_NO_AUTO_COMMIT)) {
            $e = oci_error($stmtBrand);
            throw new Exception("Error validating brand: " . $e['message']);
        }

        $brandRecord = oci_fetch_assoc($stmtBrand);
        if (!$brandRecord) {
            $sqlInsertBrand = "INSERT INTO YTYCUSTOM.GTH_CUSTOMER_BRAND_MASTER
                    (CUSTOMER_ID, BRAND_ID, BRAND_NAME)
                VALUES
                    ('445212', :brand_id, :brand_name) RETURNING ID INTO :new_brand_id";
            $stmtInsertBrand = oci_parse($conn, $sqlInsertBrand);

            oci_bind_by_name($stmtInsertBrand, ":brand_id", $post_brand_id);
            oci_bind_by_name($stmtInsertBrand, ":brand_name", $post_brand_name);

            $newBrandId = null;
            oci_bind_by_name($stmtInsertBrand, ":new_brand_id", $newBrandId, -1, SQLT_INT);

            if (!oci_execute($stmtInsertBrand, OCI_NO_AUTO_COMMIT)) {
                $e = oci_error($stmtInsertBrand);
                throw new Exception("Failed to insert brand: " . $e['message']);
            }
            $brandId = $post_brand_id;
            $brandName = $post_brand_name;
        } else {
            $brandId = $brandRecord["BRAND_ID"];
            $brandName = $brandRecord['BRAND_NAME'];
        }

        /** INSERT INTO GTH_MEDISAFE_INVOICE */
        $sqlInvoice = "INSERT INTO GTH_MEDISAFE_INVOICE
                        (GTH_MEDISAFE_INVOICE_HEADER_ID, BRAND_ID, PO_NUMBER, PACKING_DETAILS, STATUS,
                         CREATED_AT, CREATED_BY, UPDATED_AT, UPDATED_BY, BRAND_NAME)
                       VALUES
                        (:header_id, :brand_id, :po_number, :packing_details, 1,
                         SYSDATE, :created_by, SYSDATE, :updated_by, :BRAND_NAME)
                       RETURNING ID INTO :invoice_id";

        $stmtInvoice = oci_parse($conn, $sqlInvoice);

        oci_bind_by_name($stmtInvoice, ":header_id", $newHeaderId);
        oci_bind_by_name($stmtInvoice, ":brand_id", $brandId);
        oci_bind_by_name($stmtInvoice, ":po_number", $poNumber);
        oci_bind_by_name($stmtInvoice, ":packing_details", $packingDetails);
        oci_bind_by_name($stmtInvoice, ":created_by", $user);
        oci_bind_by_name($stmtInvoice, ":updated_by", $user);
        oci_bind_by_name($stmtInvoice, ":BRAND_NAME", $brandName);
        oci_bind_by_name($stmtInvoice, ":invoice_id", $newInvoiceId, -1, SQLT_INT);

        if (!oci_execute($stmtInvoice, OCI_NO_AUTO_COMMIT)) {
            $e = oci_error($stmtInvoice);
            throw new Exception("Failed to insert invoice: " . $e['message']);
        }

        /** -----------------------------------------------------------
         * INSERT DETAILS
         * ---------------------------------------------------------- */
        foreach ($itemDetails as $item) {

            if (
                !property_exists($item, "glove_size") || trim((string)$item->glove_size) === '' ||
                !property_exists($item, "lot_no") || trim((string)$item->lot_no) === '' ||
                !property_exists($item, "quantity_pieces") ||
                !property_exists($item, "quantity_cases") ||
                !property_exists($item, "gross_weight") ||
                !property_exists($item, "net_weight")
            ) {
                throw new Exception(
                    "Each detail must contain non-empty glove_size, lot_no, quantity_pieces, " .
                    "quantity_cases, gross_weight, and net_weight."
                );
            }

            $gloveSize   = $item->glove_size ?? null;
            $lotNo       = $item->lot_no ?? null;
            $qtyPieces   = $item->quantity_pieces?? null;
            $qtyCases    = $item->quantity_cases?? null;
            $grossWeight = $item->gross_weight?? null;
            $netWeight   = $item->net_weight?? null;
            $inventory_item_id = $item->inventory_item_id ?? null;
            $invoice_line_id = $item->invoice_line_id ?? ($item->Invoice_line_id ?? null);
            $order_code = $item->order_code ?? '';
            $invoice_header_id = $item->invoice_header_id ?? ($item->Invoice_header_id ?? null);

            // Validate size
            $sqlSize = "SELECT SIZE_CODE FROM YTYCUSTOM.GTH_SIZE_MASTER
            WHERE LOWER(SIZE_CODE) = LOWER(:size_code) AND ACTIVE_FLAG = 'Y'";

            $stmtSize = oci_parse($conn, $sqlSize);
            oci_bind_by_name($stmtSize, ":size_code", $gloveSize);

            if (!oci_execute($stmtSize, OCI_NO_AUTO_COMMIT)) {
            $e = oci_error($stmtSize);
            throw new Exception("Error validating SIZE_CODE: " . $e['message']);
            }

            if (!oci_fetch_assoc($stmtSize)) {
            throw new Exception("Invalid size_code '$gloveSize'");
            }

            // Insert final detail

            $sqlDetail = "INSERT INTO GTH_MEDISAFE_INVOICE_DETAILS
                (GTH_MEDISAFE_INVOICE_ID, GLOVE_SIZE, LOT_NO, QUANTITY_PIECES,
                QUANTITY_CASES, GROSS_WEIGHT, NET_WEIGHT, STATUS,
                CREATED_AT, CREATED_BY, UPDATED_AT, UPDATED_BY, INVENTORY_ITEM_ID, INVOICE_LINE_ID, ORDER_CODE,  INVOICE_HEADER_ID)
            VALUES
                (:invoice_id, :glove_size, :lot_no, :quantity_pieces,
                :quantity_cases, :gross_weight, :net_weight, 1,
                SYSDATE, :created_by, SYSDATE, :updated_by, :inventory_item_id, :invoice_line_id,  :ORDER_CODE, :invoice_header_id)";

            $stmtDetail = oci_parse($conn, $sqlDetail);

            oci_bind_by_name($stmtDetail, ":invoice_id", $newInvoiceId);
            oci_bind_by_name($stmtDetail, ":glove_size", $gloveSize);
            oci_bind_by_name($stmtDetail, ":lot_no", $lotNo);
            oci_bind_by_name($stmtDetail, ":quantity_pieces", $qtyPieces);
            oci_bind_by_name($stmtDetail, ":quantity_cases", $qtyCases);
            oci_bind_by_name($stmtDetail, ":gross_weight", $grossWeight);
            oci_bind_by_name($stmtDetail, ":net_weight", $netWeight);
            oci_bind_by_name($stmtDetail, ":created_by", $user);
            oci_bind_by_name($stmtDetail, ":updated_by", $user);
            oci_bind_by_name($stmtDetail, ":inventory_item_id", $inventory_item_id);
            oci_bind_by_name($stmtDetail, ":invoice_line_id", $invoice_line_id);
            oci_bind_by_name($stmtDetail, ":ORDER_CODE", $order_code);
            oci_bind_by_name($stmtDetail, ":invoice_header_id", $invoice_header_id);

            if (!oci_execute($stmtDetail, OCI_NO_AUTO_COMMIT)) {
            $e = oci_error($stmtDetail);
            throw new Exception("Failed to insert detail: " . $e['message']);
            }
        }
    }

    oci_commit($conn);

    echo json_encode([
        "status" => true,
        "message" => "Invoice inserted successfully",
        "header_id" => $newHeaderId
    ]);

} catch (Exception $ex) {
    oci_rollback($conn);
    echo json_encode(["status" => false, "message" => $ex->getMessage()]);
}
?>
