<?php
$page_title = 'Import Scan AWB Jakarta';
require_once('includes/load.php');
page_require_level(7);

// Set timezone
date_default_timezone_set('Asia/Jakarta');

// Function to check if AWB is already scanned
function is_scan_completed($awb_resi)
{
    global $db;
    $result = find_by_resi($awb_resi);
    return !empty($result['scan_status']);
}

// Function to insert into sales_jakarta table
function insert_into_sales_jakarta($product_id, $part_number, $quantity, $categorie_id, $remark, $loc)
{
    global $db;
    $date = date("Y-m-d");

    $sql = "INSERT INTO sales_jakarta (product_id, part_number, qty, date, categorie_id, remark, loc) 
            VALUES ('{$product_id}', '{$part_number}', '{$quantity}', '{$date}', '{$categorie_id}', '{$remark}', '{$loc}')";
    return $db->query($sql);
}

// Function to update quantity in online_products_jakarta table
function update_product_qty_online_products_jakarta($part_number, $quantity)
{
    global $db;
    $sql = "UPDATE online_products_jakarta SET quantity = quantity - '{$quantity}' WHERE part_number = '{$part_number}'";
    return $db->query($sql);
}

// Function to get all part numbers and quantities for a given resi
function get_part_numbers_and_quantities_by_resi($resi)
{
    global $db;
    $resi = $db->escape($resi);
    $sql = "SELECT part_number, quantity FROM awb WHERE resi = '{$resi}'";
    $result = $db->query($sql);
    return ($result) ? $result->fetch_all(MYSQLI_ASSOC) : [];
}

// Function to find product by part number in online_products_jakarta table
function find_product_by_part_number_online_jakarta($part_number)
{
    global $db;
    $part_number = $db->escape($part_number);
    $sql = "SELECT * FROM online_products_jakarta WHERE part_number = '{$part_number}' LIMIT 1";
    $result = $db->query($sql);
    return ($result) ? $result->fetch_assoc() : null;
}

// Handle CSV file upload and processing
if (isset($_POST['import'])) {
    if (isset($_FILES['file']) && $_FILES['file']['error'] == 0) {
        $file = $_FILES['file']['tmp_name'];
        $handle = fopen($file, "r");

        // Skip the first row if it contains the header
        fgetcsv($handle, 1000, ";");

        while (($data = fgetcsv($handle, 1000, ";")) !== false) {
            $awb_resi = $db->escape($data[0]);
            $packer_no = $db->escape($data[1]);
            $awb_records = get_part_numbers_and_quantities_by_resi($awb_resi);

            if ($awb_records) {
                if (is_scan_completed($awb_resi)) {
                    $session->msg('w', "AWB {$awb_resi} DOUBLE SCAN!!");
                } else {
                    $scan_status = date("Y-m-d H:i:s");
                    $sql = "UPDATE awb SET scan_status = '{$scan_status}', packer_no = '{$packer_no}' WHERE resi = '{$awb_resi}'";
                    if ($db->query($sql)) {
                        foreach ($awb_records as $awb) {
                            $part_number = $awb['part_number'];
                            $quantity = $awb['quantity'];
                            $online_product = find_product_by_part_number_online_jakarta($part_number);

                            if ($online_product) {
                                $product_id = $online_product['id'];
                                $categorie_id = $online_product['categorie_id'];
                                $remark = "Scan {$awb_resi}";
                                $loc = "Jakarta";

                                // Insert into sales_jakarta and update stock regardless of stock availability
                                if (insert_into_sales_jakarta($product_id, $part_number, $quantity, $categorie_id, $remark, $loc)) {
                                    if (!update_product_qty_online_products_jakarta($part_number, $quantity)) {
                                        $session->msg('d', "Failed to update product quantity in online_products_jakarta table.");
                                    }
                                } else {
                                    $session->msg('d', "Failed to insert into sales_jakarta table.");
                                }
                            } else {
                                $session->msg('d', "Product with part number {$part_number} not found in online_products_jakarta table.");
                            }
                        }
                        $session->msg('s', "AWB {$awb_resi} BERHASIL SCAN!!");
                    } else {
                        $session->msg('d', "Failed to update scan status for AWB barcode.");
                    }
                }
            } else {
                $session->msg('w', "AWB barcode {$awb_resi} Tidak ada!!");
            }
        }
        fclose($handle);
        redirect('import_scan_awb_jakarta.php', false);
    } else {
        $session->msg('d', "Please upload a valid CSV file.");
    }
}

include_once('layouts/header.php');
?>

<!-- HTML Form for CSV Upload -->
<div class="row">
    <div class="col-md-6">
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title">Import AWB Jakarta</h3>
            </div>
            <div class="panel-body">
                <form method="post" enctype="multipart/form-data" action="import_scan_awb_jakarta.php">
                    <div class="form-group">
                        <label for="file">Select CSV File:</label>
                        <input type="file" name="file" class="form-control" required>
                    </div>
                    <button type="submit" name="import" class="btn btn-primary">Import</button>
                </form>
            </div>
        </div>
    </div>
</div>

<?php include_once('layouts/footer.php'); ?>