<?php

include "../root.class.php";
$auth = new authentication();

$db = new db_safeguard();

require '../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'pos.xlsx';

$spreadsheet = IOFactory::load($inputFileName);
$worksheet = $spreadsheet->getActiveSheet();
$data = [];


?>
<!-- 
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <style>
        #loadingOverlay {
            position: fixed;
            width: 100%;
            height: 100%;
            top: 0;
            left: 0;
            background-color: rgba(0, 0, 0, 0.5);
            display: flex;
            justify-content: center;
            align-items: center;
            z-index: 9999;
            color: white;
            font-size: 2em;
            animation: fadeOut 1s forwards 14s;
        }

        @keyframes fadeOut {
            to {
                opacity: 0;
                visibility: hidden;
            }
        }
    </style>
</head>
<body>
    <div id="loadingOverlay">Loading...</div>
    <script>
        setTimeout(() => {
            const loadingOverlay = document.getElementById('loadingOverlay');
            loadingOverlay.style.display = 'none';
        }, 15000);
    </script>
</body>
</html> -->


<?php
function excelDateToDate($serialDate)
{
    // Excel's epoch starts at 1900-01-01
    $excelEpoch = strtotime('1900-01-01');

    // Excel serial numbers are 1-based, so subtract 1
    $timestamp = strtotime("+$serialDate days", $excelEpoch - 1);

    // Format the timestamp as yyyy-mm-dd
    return date('Y-m-d', $timestamp);
}

function supplier_check($supplier_name)
{
    $supplier_name = str_replace("'", "\'", $supplier_name);
    $db = new db_safeguard();
    $res = $db->query("suppliers", "SELECT * FROM suppliers  WHERE `name` = '$supplier_name'");
    if ($res->num_rows > 0) {
        $data = $res->fetch_assoc();
        return $data['record_id'];
    } else {
        // create supplier
        return $db->query("suppliers", "INSERT INTO suppliers (`name`) VALUES ('$supplier_name')");
    }
}

function check_stock($stock_name, $item_code, $supplier_id, $cost_price)
{
    $stock_name = str_replace("'", "", $stock_name);
    $db = new db_safeguard();
    $stock_type = check_stock_type($item_code);

    // check if desctiption exists

    $res = $db->query("stock", "SELECT * FROM stock WHERE description = '$stock_name'");
    if ($res->num_rows > 0) {
        $data = $res->fetch_assoc();
        echo "ITEM EXISTS UPDATED STOCK TYPE";
        $db->query("stock", "UPDATE stock SET stock_type = $stock_type WHERE record_id = {$data['record_id']}");
        return $data['record_id'];
    } else {
        echo "ITEM CREATED";
        // create that stock item
        return $db->query("stock", "INSERT INTO `stock`(`category_id`, `supplier_id`, `description`, `packaging_size`, `cost_price`, `max`, `min`, `unit_type_id`, `barcode`, `stock_type`, `item_code`) VALUES (-1,$supplier_id,'$stock_name','',$cost_price,0,0,0,'',$stock_type,'$item_code')");
    }
}

function check_stock_type($item_code)
{
    $db = new db_safeguard();
    $res = $db->query("item_code_alocation", "SELECT * FROM item_code_alocation WHERE 1");
    while ($row = $res->fetch_assoc()) {
        if (strpos($item_code, (string) $row['code']) !== false) {
            echo "<br>" . $row['code'] . " : " . $row['stock_type'] . " : " . $item_code . "<br>";
            return $row['stock_type'];
        }
    }

    // if (strpos($item_code, "UNIT") !== false || strpos($item_code, "UNT") !== false) {
    //     $stock_type = 2;
    //     echo "<br>UNITS<br>";

    // } else if (strpos($item_code, "FAN") !== false || strpos($item_code, "MT C") !== false || strpos($item_code, "SPARES") !== false || strpos($item_code, "DUCT") !== false || strpos($item_code, "SILPOD") !== false || strpos($item_code, "HIT") !== false || strpos($item_code, "TB") !== false || strpos($item_code, "TD") !== false|| strpos($item_code, "GRA") !== false || strpos($item_code, "HXM") !== false || strpos($item_code, "FEET") !== false || strpos($item_code, "FLAN") !== false || strpos($item_code, "OTHER") !== false || strpos($item_code, "moun") !== false || strpos($item_code, "TECSPA") !== false|| strpos($item_code, "MOUN01") !== false) {
    //     echo "<br>NON STOCK<br>";
    //     $stock_type = 1;
    // } else {
    //     echo "<br>STOCK<br>";

    //     $stock_type = 0;
    // }
    echo "<br>STOCK : " . $item_code . "<br>";
    return 0;
}

foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    $rowIndex = $row->getRowIndex();

    if ($rowIndex >= 1 && $rowIndex <= 6) {
        continue;
    }

    $data[$rowIndex] = [];
    foreach ($cellIterator as $cell) {
        $data[$rowIndex][$cell->getColumn()] = $cell->getValue();
    }

}

function remove_special_char($string)
{
    $string = preg_replace('/\s+/u', ' ', trim($string));
    $string = str_replace('Ø', 'O', trim($string));
    $string = str_replace(array("\r", "\n"), '', $string);
    $string = str_replace("'", "", trim($string));
    return $string;
}
function replace_negative($string)
{
    return str_replace('-', '', $string);
}

$index = 0;
foreach ($data as $index => $row) {
    if ($row["A"] === null) {
        echo "IN LOOP BY PASS<br>";
        continue;
    }

    echo "OUT OF BY PASS<br>";

    echo "OUT OF BY PASS";
    if (!is_int($data[$index]["A"]) && strpos($data[$index]["A"], 'Total') === false && $data[$index]["A"] != "Invoice Date") {
        $po_number = $data[$index]["A"];
        // this is where the index goes out to get all po data
        $item_index_amount = $index + 1;
        $array_index = 0;
        while ($data[$item_index_amount]["A"] > 1) {
            if (strpos($data[$item_index_amount]["A"], "Total")) {
                echo "TOTAL FOUND BREAKING<br>";
                break;
            }
            if ($data[$item_index_amount]["F"] != "" || $data[$item_index_amount]["F"] != Null) {
                $date = excelDateToDate($data[$item_index_amount]["A"]);
                echo $supplier_name = $data[$item_index_amount]["B"];
                $reference = $data[$item_index_amount]["D"];
                $items_array[$array_index]["item_code"] = $data[$item_index_amount]["E"];
                $items_array[$array_index]["description"] = remove_special_char($data[$item_index_amount]["F"]);
                $items_array[$array_index]["quantity"] = replace_negative($data[$item_index_amount]["G"]);

                if ($items_array[$array_index]["description"] == "PROMO DISCOUNT") {
                    $items_array[$array_index]["price"] = $data[$item_index_amount]["H"];
                } else {
                    $items_array[$array_index]["price"] = replace_negative($data[$item_index_amount]["H"]) * 1;

                }
            }
            $array_index++;
            $item_index_amount++;
        }
        var_dump($items_array);

        echo $po_number . "<br>";
        echo " supplier: " . $supplier_name . "<br>";
        echo " date: " . $date . "<br>";
        echo " reference: " . (isset($reference) && $reference != "" ? $reference : 0) . "<br>";
        $supplier_id = (isset($supplier_name) && $supplier_name != "") ? supplier_check($supplier_name) : 0;

        // check if PO exists 
        $po_res = $db->query("orders", "SELECT * FROM orders WHERE po_number = '$po_number'");
        if ($po_res->num_rows > 0) {
            $po_data = $po_res->fetch_assoc();
            $order_id = $po_data['record_id'];
            echo "ORDER EXISTS<br>";
        } else {

            $order_sql = "INSERT INTO `orders`(`po_number`, `jc_number`, `date_time_created`, `date_time_recieved`, `supplier_id`, `status`, `user_id`, `job_card_account_id`, `warrenty`, `user_created`, `reference`) VALUES ('$po_number','','$date',0,'$supplier_id',0,'{$_SESSION['user_id']}',0,0,'{$_SESSION['user_id']}','$reference')";
            echo $order_sql . "<br>";
            $order_id = $db->query("orders", $order_sql);
        }

        // delete all order_items from order_table where this order_exists
        // echo "DELETE FROM order_items WHERE order_id = $order_id";
        $db->query("order_items", "DELETE FROM order_items WHERE order_id = $order_id");
        echo "<br>ITEMS<br>";
        foreach ($items_array as $item) {

            if (strlen($item['item_code']) > 2) {
                $stock_id = check_stock($item['description'], $item['item_code'], $supplier_id, 0);

                $stock_type = check_stock_type($item['item_code']);

                echo "<br>" . $item['item_code'] . "<br>";
                // get item multilication adjustment
                $item_res_stock = $db->query("stock", "SELECT * FROM stock WHERE record_id = $stock_id");
                $item_data_stock = $item_res_stock->fetch_assoc();
                $operator = $item_data_stock['adjustment_type'];
                $amount_adjsuted = $item_data_stock['amount'];
                if (strtoupper($operator) == "X") {
                    $quantity = round($item['quantity'] * $amount_adjsuted, 3);
                    $price = round($item['price'] / $amount_adjsuted, 3);
                }
                if (strtoupper($operator) == "/") {
                    $quantity = round($item['quantity'] / $amount_adjsuted, 3);
                    $price = round($item['price'] * $amount_adjsuted, 3);
                }



                $order_item_sql = "INSERT INTO `order_items`(`name`, `stock_id`, `quantity`, `purchase_price_ex`, `order_id`, `user_id_created`, `user_id_received`, `stock_type`) VALUES ('{$item['description']}',$stock_id,$quantity,$price,$order_id,{$_SESSION['user_id']},0,$stock_type)";

                echo $order_item_sql . "<br>";
                $db->query("order_items", $order_item_sql);
            } else {
                echo "<br> SKIPPED NO ITEM CODE : ".$item['description'].' CODE : '. $item['item_code'];
            }
            echo "<br>";
        }
        $items_array = [];
        echo "<br><br>";
    }
    $index++;
}

// echo "<script>window.location.href = '../app/orders/search_order.php';</script>";