<?php

// Set timezone
date_default_timezone_set('Asia/Jakarta');
require 'vendor/autoload.php';

use Picqer\Barcode\BarcodeGeneratorPNG;
use Predis\Client;

$errors = array();

/*--------------------------------------------------------------*/
/* Function for Remove escapes special
 /* characters in a string for use in an SQL statement
 /*--------------------------------------------------------------*/
function real_escape($str, $con)
{
    return mysqli_real_escape_string($con, $str);
}
/**
 * Get setting value by key.
 *
 * @param string $key The key of the setting to retrieve.
 * @return string|null The value of the setting or null if not found.
 */
function get_setting_value($key)
{
    global $db; // Make sure $mysqli is your MySQLi connection
    $setting_value = "0";

    // Prepare the query
    $query = "SELECT setting_value FROM settings WHERE setting_key = ?";

    // Initialize the prepared statement
    if ($stmt = $db->prepare($query)) {
        // Bind the parameter
        $stmt->bind_param("s", $key); // 's' stands for string type

        // Execute the statement
        $stmt->execute();

        // Bind the result to a variable
        $stmt->bind_result($setting_value);

        // Fetch the result
        if ($stmt->fetch()) {
            // Return the value if found
            $stmt->close();
            return $setting_value;
        }

        // Close the statement
        $stmt->close();
    }

    // Return null if the key was not found
    return null;
}
/**
 * Update or insert shop data and authenticate with Lazada
 *
 * @param mysqli $db Database connection (MySQLi instance)
 * @param string $code The code parameter for the shop
 * @param int $code The shop
 * @return array Response with a success or error message
 */
function handleAuthLazada($code)
{
    $data = LazadaAuth($code);
    InsertAuthToken($data);
}
/**
 * Update or insert shop data and authenticate with Shopee
 *
 * @param mysqli $db Database connection (MySQLi instance)
 * @param string $code The code parameter for the shop
 * @param int $shop_id The shop ID
 * @param int $platform_id The platform ID
 * @return array Response with a success or error message
 */
function handleAuthShopee($code, $shop_id)
{
    global $db;
    $platform_id = 1;
    // Sanitize inputs
    $code = htmlspecialchars($code);
    $shop_id = htmlspecialchars($shop_id);

    // Prepare the SQL statement to check if shop_id exists
    $checkSql = "SELECT * FROM shop WHERE shop_id = ? AND platform_id = ?";
    $checkStmt = $db->prepare($checkSql);
    if (!$checkStmt) {
        return ["error" => "Prepare failed: " . $db->error];
    }
    $checkStmt->bind_param("ii", $shop_id, $platform_id);
    $checkStmt->execute();
    $result = $checkStmt->get_result();

    if ($result->num_rows > 0) {
        // If shop_id exists, update the record
        $updateSql = "UPDATE shop SET code = ? WHERE shop_id = ? AND platform_id = ?";
        $updateStmt = $db->prepare($updateSql);
        if (!$updateStmt) {
            return ["error" => "Prepare failed: " . $db->error];
        }
        $updateStmt->bind_param("sii", $code, $shop_id, $platform_id);

        if (!$updateStmt->execute()) {
            return ["error" => "Error updating data: " . $updateStmt->error];
        }

        // Close the update statement
        $updateStmt->close();
    } else {
        // If shop_id does not exist, insert a new record
        $insertSql = "INSERT INTO shop (code, shop_id, platform_id) VALUES (?, ?, ?)";
        $insertStmt = $db->prepare($insertSql);
        if (!$insertStmt) {
            return ["error" => "Prepare failed: " . $db->error];
        }
        $insertStmt->bind_param("sii", $code, $shop_id, $platform_id);

        if (!$insertStmt->execute()) {
            return ["error" => "Error inserting data: " . $insertStmt->error];
        }

        // Close the insert statement
        $insertStmt->close();
    }

    // Call the getAuthShopee function
    $authResponse = getAuthShopee($shop_id, $code);
    if ($authResponse[2] === false) {
        return ["message" => "Failed to authenticate with Shopee Shop ID = " . $shop_id, "response" => $authResponse[0]];
    }

    // Close the check statement
    $checkStmt->close();

    return ["message" => "Data Shop Shopee successfully"];
}
/*--------------------------------------------------------------*/
/* Function for Remove html characters
/*--------------------------------------------------------------*/
function remove_junk($str)
{
    $str = nl2br($str);
    $str = htmlspecialchars(strip_tags($str, ENT_QUOTES));
    return $str;
}
/*--------------------------------------------------------------*/
/* Function for Uppercase first character
/*--------------------------------------------------------------*/
function first_character($str)
{
    $val = str_replace('-', " ", $str);
    $val = ucfirst($val);
    return $val;
}
/*--------------------------------------------------------------*/
/* Function for Checking input fields not empty
/*--------------------------------------------------------------*/
function validate_fields($var)
{
    global $errors;
    $error_messages = array();
    foreach ($var as $field) {
        $val = remove_junk($_POST[$field]);
        if (isset($val) && $val == '') {
            $error_messages[] = $field . " can't be blank.";
        }
    }
    return $error_messages;
}
/*--------------------------------------------------------------*/
/* Function for Display Session Message
   Ex echo displayt_msg($message);
/*--------------------------------------------------------------*/
function display_msg($msg = array())
{
    $output = "";
    if (!empty($msg)) {
        foreach ($msg as $key => $value) {
            $output .= "<div class=\"alert alert-{$key}\">";
            $output .= "<a href=\"#\" class=\"close\" data-dismiss=\"alert\">&times;</a>";
            $output .= remove_junk(first_character($value));
            $output .= "</div>";
        }
    }
    return $output;
}

function display_msg_new($msg = [])
{
    $output = "";
    if (!empty($msg)) {
        foreach ($msg as $key => $value) {
            $output .= "<div class=\"alert alert-{$key} alert-dismissible\" role=\"alert\">";
            $output .= "<button type=\"button\" class=\"close\" data-dismiss=\"alert\" aria-label=\"Close\">";
            $output .= "<span aria-hidden=\"true\">&times;</span>";
            $output .= "</button>";
            $output .= remove_junk(first_character($value));
            $output .= "</div>";
        }
    }
    return $output;
}

/*--------------------------------------------------------------*/
/* Function for redirect
/*--------------------------------------------------------------*/
function redirect($url, $permanent = false)
{
    if (headers_sent() === false) {
        header('Location: ' . $url, true, ($permanent === true) ? 301 : 302);
    }

    exit();
}
/*--------------------------------------------------------------*/
/* Function for find out total saleing price, buying price and profit
/*--------------------------------------------------------------*/
function total_price($totals)
{
    $sum = 0;
    $sub = 0;
    foreach ($totals as $total) {
        $sum += $total['total_saleing_price'];
        $sub += $total['total_buying_price'];
    }
    $profit = $sum - $sub;
    return array($sum, $profit);
}
/*--------------------------------------------------------------*/
/* Function for Readable date time
/*--------------------------------------------------------------*/
function read_date($str)
{
    if ($str)
        return date('F j, Y, g:i:s a', strtotime($str));
    else
        return null;
}
/*--------------------------------------------------------------*/
/* Function for  Readable Make date time
/*--------------------------------------------------------------*/
function make_date()
{
    return (new DateTime())->format('Y-m-d H:i:s');
}
/*--------------------------------------------------------------*/
/* Function for  Readable date time
/*--------------------------------------------------------------*/
function count_id()
{
    static $count = 1;
    return $count++;
}
/*--------------------------------------------------------------*/
/* Function for Creting random string
/*--------------------------------------------------------------*/
function randString($length = 5)
{
    $str = '';
    $cha = "0123456789abcdefghijklmnopqrstuvwxyz";

    for ($x = 0; $x < $length; $x++)
        $str .= $cha[mt_rand(0, strlen($cha))];
    return $str;
}

// includes/functions.php

function search_products($query, $search_option)
{
    global $db;
    $query = "%" . $db->escape($query) . "%";
    $sql = "SELECT p.*, p.UoM as uom, c.name AS categorie, m.file_name AS image
        FROM products p 
        LEFT JOIN categories c ON c.id = p.categorie_id 
        LEFT JOIN media m ON m.id = p.media_id 
        WHERE ";

    // Tentukan bidang mana yang akan digunakan untuk pencarian
    switch ($search_option) {
        case 'product':
            $sql .= "p.name LIKE '$query'";
            break;
        case 'part_number':
            $sql .= "p.part_number LIKE '$query'";
            break;
        default:
            // Default ke pencarian berdasarkan nama produk jika opsi tidak valid
            $sql .= "p.name LIKE '$query'";
            break;
    }

    return find_by_sql($sql);
}



function get_product_stock($product_id)
{
    global $db; // Assuming you have a database connection variable called $db

    $sql = "SELECT quantity FROM products WHERE id = " . (int)$product_id;
    $result = $db->query($sql);

    if ($result && $db->num_rows($result) > 0) {
        $row = $db->fetch_assoc($result);
        return (int)$row['quantity'];
    } else {
        return 0; // Return 0 if the product doesn't exist or there's an error
    }
}

function search_products_by_part_number($query)
{
    global $db;

    $query = "%" . $db->escape($query) . "%"; // Menggunakan real_escape_string dari objek $db
    $sql = "SELECT * FROM products WHERE part_number LIKE '$query'";
    return find_by_sql($sql);
}

function search_products_by_name($query)
{
    global $db;
    $query = "%" . $db->escape($query) . "%"; // Gunakan metode escape() yang sesuai
    $sql = "SELECT * FROM products WHERE name LIKE '$query'";
    return find_by_sql($sql);
}

// Function to generate a unique purchase order number
function generate_purchase_order_number()
{
    $prefix = 'PO';
    $random_number = mt_rand(100000, 999999); // Generate a random number
    $timestamp = time();
    return $prefix . $random_number . $timestamp;
}

// Function to find products with quantity below a certain threshold
function find_products_below_quantity($threshold)
{
    global $db;
    $sql = "SELECT id, part_number, name, categorie_id, quantity FROM products WHERE quantity < $threshold";
    return $db->query($sql);
}

function get_all_purchase_requests()
{
    global $db;

    $query = "SELECT * FROM purchase_requests";
    $result = $db->query($query);
    $purchaseRequests = array();

    while ($row = $db->fetch_assoc($result)) {
        $purchaseRequests[] = $row;
    }

    return $purchaseRequests;
}

function get_product_name_by_id($product_id)
{
    global $db;

    $query = "SELECT name FROM products WHERE id = '$product_id'";
    $result = $db->query($query);

    if ($db->num_rows($result) > 0) {
        $row = $db->fetch_assoc($result);
        return $row['name'];
    } else {
        return 'N/A';
    }
}

function get_all_products()
{
    global $db;
    $sql  = "SELECT p.id, p.part_number, p.name, p.quantity, p.uom, p.buy_price, p.loc_id, p.new_code, p.media_id, p.date, c.name AS categorie, m.file_name AS image ";
    $sql .= "FROM products p ";
    $sql .= "LEFT JOIN categories c ON c.id = p.categorie_id ";
    $sql .= "LEFT JOIN media m ON m.id = p.media_id ";
    $sql .= "ORDER BY p.id ASC";
    return find_by_sql($sql);
}



function find_raw_materials_below_quantity($threshold)
{
    global $db;

    $query = "SELECT * FROM products WHERE categorie_id = 2 AND quantity < $threshold";
    $result = $db->query($query);

    if (!$result) {
        die($db->error); // Print any error if occurred
    }

    $raw_materials = [];
    while ($row = $db->fetch_assoc($result)) {
        $raw_materials[] = $row;
    }

    return $raw_materials;
}

function get_user_name_by_id($user_id)
{
    global $db; // Pastikan variabel $db sudah ada dan menginisialisasi koneksi ke database

    $query = "SELECT name FROM users WHERE id = '$user_id'";
    $result = $db->query($query);

    if ($result && $db->num_rows($result) > 0) {
        $user = $db->fetch_assoc($result);
        return $user['name'];
    } else {
        return "Unknown User";
    }
}

function get_part_number_by_product_id($product_id)
{
    global $db; // Pastikan variabel $db sudah ada dan menginisialisasi koneksi ke database

    $query = "SELECT part_number FROM products WHERE id = '$product_id'";
    $result = $db->query($query);

    if ($result && $db->num_rows($result) > 0) {
        $row = $db->fetch_assoc($result);
        return $row['part_number'];
    } else {
        return 'N/A';
    }
}

function find_product_by_id($product_id)
{
    global $db; // Assuming you have a database connection variable called $db

    $sql = "SELECT * FROM products WHERE id = " . (int)$product_id;
    $result = $db->query($sql);

    if ($result && $db->num_rows($result) > 0) {
        $product = $db->fetch_assoc($result);
        return $product;
    } else {
        return false; // Return false if the product doesn't exist or there's an error
    }
}

function find_all_sales()
{
    global $db; // Assuming you have a database connection variable called $db

    $sql = "SELECT * FROM sales";
    $result = $db->query($sql);

    $sales = array();
    if ($result && $db->num_rows($result) > 0) {
        while ($row = $db->fetch_assoc($result)) {
            $sales[] = $row;
        }
    }

    return $sales;
}
// Di dalam file includes/functions.php atau di mana Anda menyimpan fungsi-fungsi Anda
function find_sales_by_date_range($start_date, $end_date, $part_number = null, $product_name = null)
{
    global $db;

    $start_date = $db->escape($start_date);
    $end_date = $db->escape($end_date);

    $sql = "SELECT p.part_number, p.name as product_name, s.qty, s.date
            FROM sales s
            JOIN products p ON s.product_id = p.id
            WHERE s.date BETWEEN '{$start_date}' AND '{$end_date}'";

    // Check if part number filter is applied
    if (!empty($part_number)) {
        $part_number = $db->escape($part_number);
        $sql .= " AND p.part_number = '{$part_number}'";
    }

    // Check if product name filter is applied
    if (!empty($product_name)) {
        $product_name = $db->escape($product_name);
        $sql .= " AND p.name = '{$product_name}'";
    }

    $result = $db->query($sql);
    $sales = array();

    while ($row = $db->fetch_assoc($result)) {
        $sales[] = $row;
    }

    return $sales;
}



function find_product_by_part_number($partNumber)
{
    global $db; // Assuming you have a database connection variable called $db

    $sql = "SELECT * FROM products WHERE part_number = '" . $db->escape($partNumber) . "'";
    $result = $db->query($sql);

    if ($result && $db->num_rows($result) > 0) {
        return $db->fetch_assoc($result);
    } else {
        return false;
    }
}

// includes/functions.php

function find_latest_incoming_goods()
{
    global $db;

    $sql = "SELECT p.part_number, p.name AS product_name, ig.quantity, ig.arrival_date
            FROM incoming_goods ig
            INNER JOIN products p ON p.id = ig.product_id
            ORDER BY ig.arrival_date DESC
            LIMIT 5";

    return find_by_sql($sql);
}

function get_all_data()
{
    global $db;
    $query = "SELECT * FROM data"; // Ganti dengan nama tabel yang sesuai
    $result = $db->query($query);
    $data = array();
    while ($row = $db->fetch_assoc($result)) {
        $data[] = $row;
    }
    return $data;
}

// functions.php

function get_all_data_with_search($search_query, $search_option)
{
    global $db;

    $allowed_options = array('part_number', 'description');

    if (!in_array($search_option, $allowed_options)) {
        return false; // Invalid search option
    }

    $escaped_search_query = $db->escape($search_query);

    $query = "SELECT * FROM data WHERE {$search_option} LIKE '%{$escaped_search_query}%'";

    $result = $db->query($query);

    if ($db->num_rows($result) > 0) {
        $data = array();
        while ($row = $db->fetch_assoc($result)) {
            $data[] = $row;
        }
        return $data;
    } else {
        return array();
    }
}

// includes/functions.php


function add_new_data($part_number, $description, $color, $unit_price, $unit_measurements, $db)
{
    // Sanitize input data
    $part_number = $db->escape($part_number);
    $description = $db->escape($description);
    $color = $db->escape($color);
    $unit_price = $db->escape($unit_price);
    $unit_measurements = $db->escape($unit_measurements);

    // SQL query to insert data
    $query = "INSERT INTO data (part_number, description, color, unit_price, unit_measurements)
              VALUES ('{$part_number}', '{$description}', '{$color}', '{$unit_price}', '{$unit_measurements}')";

    // Execute the query
    $result = $db->query($query);

    if ($result) {
        return true; // Data inserted successfully
    } else {
        return false; // Failed to insert data
    }
}

/**
 * Get data by ID from the database.
 *
 * @param int $data_id The ID of the data to retrieve.
 * @param MySqli_DB $db The database connection.
 * @return array|bool The data record if found, or false if not found.
 */
function get_data_by_id($data_id, $db)
{
    $data_id = (int)$data_id;

    $sql = "SELECT * FROM data WHERE id = {$data_id} LIMIT 1";
    $result = $db->query($sql);

    if ($result && $db->num_rows($result) === 1) {
        return $db->fetch_assoc($result);
    } else {
        return false;
    }
}

function get_by_id($data_id, $tableName)
{
    global $db;
    $data_id = (int)$data_id;

    $sql = "SELECT * FROM {$tableName} WHERE id = {$data_id} LIMIT 1";
    $result = $db->query($sql);

    if ($result && $db->num_rows($result) === 1) {
        return $db->fetch_assoc($result);
    } else {
        return false;
    }
}



// includes/functions.php

function update_data($id, $part_number, $description, $color, $unit_price, $unit_measurements, $db)
{
    $part_number = $db->escape($part_number);
    $description = $db->escape($description);
    $color = $db->escape($color);
    $unit_price = $db->escape($unit_price);
    $unit_measurements = $db->escape($unit_measurements);

    $sql = "UPDATE data SET ";
    $sql .= "part_number='{$part_number}', ";
    $sql .= "description='{$description}', ";
    $sql .= "color='{$color}', ";
    $sql .= "unit_price='{$unit_price}', ";
    $sql .= "unit_measurements='{$unit_measurements}' ";
    $sql .= "WHERE id='{$id}'";

    $result = $db->query($sql);
    return $result ? true : false;
}

// Get all incoming data
function get_all_incoming_data($db)
{
    $query = "SELECT * FROM incoming_data";
    return $db->query($query)->fetch_all(MYSQLI_ASSOC);
}

// Get all outgoing data
function get_all_outgoing_data($db)
{
    $query = "SELECT * FROM outgoing_data";
    return $db->query($query)->fetch_all(MYSQLI_ASSOC);
}

// Get all stock data
function get_all_stock_data($db)
{
    $query = "SELECT * FROM stock_data";
    return $db->query($query)->fetch_all(MYSQLI_ASSOC);
}

function get_items_with_stock($db)
{
    $query = "SELECT sd.id, d.part_number, d.description, d.color, d.unit_price, d.unit_measurements, sd.stock_on_hand
              FROM stock_data sd
              JOIN data d ON sd.id = d.id
              WHERE sd.stock_on_hand > 0";

    return $db->query($query);
}

function get_part_numbers($db)
{
    $sql = "SELECT DISTINCT part_number FROM data";
    $result = $db->query($sql);
    $part_numbers = [];

    while ($row = $db->fetch_assoc($result)) {
        $part_numbers[] = $row['part_number'];
    }

    return $part_numbers;
}

function get_requested_quantity($product_id)
{
    global $db;
    $product_id = (int) $product_id;

    $sql = "SELECT SUM(quantity) AS total_quantity FROM purchase_requests WHERE product_id = $product_id";
    $result = $db->query($sql);
    $row = $db->fetch_assoc($result);

    return ($row['total_quantity']) ? (int) $row['total_quantity'] : 0;
}

// Retrieve all products with stock information
function get_all_products_with_stock()
{
    global $db;

    $sql = "SELECT p.*, 
                   COALESCE(SUM(ig.quantity), 0) AS total_incoming, 
                   COALESCE(SUM(s.quantity), 0) AS total_outgoing
            FROM products p
            LEFT JOIN incoming_goods ig ON p.id = ig.product_id
            LEFT JOIN sales s ON p.id = s.product_id
            GROUP BY p.id, p.part_number, p.name
            ORDER BY p.name";

    $result = $db->query($sql);
    $products = array();

    while ($row = $db->fetch_assoc($result)) {
        $products[] = $row;
    }

    return $products;
}

// ...

// Retrieve total incoming quantity for a given product
function get_total_incoming($product_id)
{
    global $db;
    $safe_product_id = $db->escape($product_id); // Prevent SQL injection
    $sql = "SELECT SUM(quantity) AS total_incoming FROM incoming_goods WHERE product_id = '$safe_product_id'";
    $result = $db->query($sql);
    $data = $db->fetch_assoc($result);
    return (int)$data['total_incoming'];
}

// Retrieve total outgoing quantity for a given product
function get_total_outgoing($product_id)
{
    global $db;
    $safe_product_id = $db->escape($product_id); // Prevent SQL injection
    $sql = "SELECT SUM(qty) AS total_outgoing FROM sales WHERE product_id = '$safe_product_id'";
    $result = $db->query($sql);
    $data = $db->fetch_assoc($result);
    return (int)$data['total_outgoing'];
}

// Retrieve sale data by ID
function find_sale_by_id($sale_id)
{
    global $db;
    $safe_sale_id = $db->escape($sale_id); // Prevent SQL injection
    $sql = "SELECT * FROM sales WHERE id = '$safe_sale_id' LIMIT 1";
    $result = $db->query($sql);

    if ($result && $db->num_rows($result) > 0) {
        return $db->fetch_assoc($result); // Return the sale data
    } else {
        return false; // Sale not found
    }
}

function get_product_name_by_part_number($part_number)
{
    global $db;
    $sql = "SELECT name FROM products WHERE part_number = '$part_number'";
    $result = $db->query($sql);
    if ($db->num_rows($result) === 1) {
        $product = $db->fetch_assoc($result);
        return $product['name'];
    }
    return "N/A";
}

function get_bom_structure($parent_bom, $current_level = 1)
{
    global $db;
    $result = array();

    // Query untuk mengambil data dari tabel bom_level
    $sql = "SELECT * FROM bom_level WHERE parent_bom = '$parent_bom'";
    $query = $db->query($sql);

    while ($row = $db->fetch_assoc($query)) {
        $child_bom = $row['child_bom'];
        $level = $current_level;
        $qty = $row['qty'];
        $buy_price = $row['buy_price']; // Ambil buy_price dari bom_level

        // Indentation untuk visualisasi
        $indentation = str_repeat('&nbsp;&nbsp;&nbsp;&nbsp;', $current_level);

        // Ambil nama dan UoM produk dari tabel products
        $product_result = $db->query("SELECT name, uom FROM products WHERE part_number = '$child_bom'");
        $product_row = $db->fetch_assoc($product_result);
        $child_name = isset($product_row['name']) ? $product_row['name'] : 'N/A';
        $uom = isset($product_row['uom']) ? $product_row['uom'] : 'N/A';

        $result[] = array(
            'indentation' => $indentation,
            'child_bom' => $child_bom,
            'child_name' => $child_name,
            'level' => $level,
            'qty' => $qty,
            'uom' => $uom,
            'buy_price' => $buy_price
        );

        // Dapatkan struktur BOM untuk produk anak secara rekursif
        $child_structure = get_bom_structure($child_bom, $current_level + 1);
        $result = array_merge($result, $child_structure);
    }

    return $result;
}




function calculate_material_requirements($product, $quantity)
{
    global $db;

    // Check if the selected product has a BOM
    if (!has_bom($product)) {
        echo "Selected product doesn't have a Bill of Materials (BOM).";
        return;
    }

    // Initialize the requirements array
    $requirements = array();

    // Calculate the material requirements recursively
    calculate_requirements_recursive($product, $quantity, $requirements);

    // Display the material requirements
    echo "<h3>Material Requirements for Product: " . get_product_name_by_part_number($product) . "</h3>";
    echo "<table class='table table-bordered'>";
    echo "<thead>";
    echo "<tr>";
    echo "<th>Material</th>";
    echo "<th>Material Name</th>";
    echo "<th>Required Quantity</th>";
    echo "</tr>";
    echo "</thead>";
    echo "<tbody>";

    foreach ($requirements as $material => $required_qty) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($material) . "</td>";
        echo "<td>" . htmlspecialchars(get_product_name_by_part_number($material)) . "</td>";
        echo "<td>" . htmlspecialchars($required_qty) . "</td>";
        echo "</tr>";
    }

    echo "</tbody>";
    echo "</table>";
}


function calculate_requirements_recursive($product, $quantity, &$requirements)
{
    global $db;

    $bom = get_bom_structure($product);

    foreach ($bom as $bom_item) {
        $child_bom = $bom_item['child_bom'];
        $child_qty = $bom_item['qty'];

        $required_qty = $child_qty * $quantity;

        if (has_bom($child_bom)) {
            calculate_requirements_recursive($child_bom, $required_qty, $requirements);
        } else {
            if (array_key_exists($child_bom, $requirements)) {
                $requirements[$child_bom] += $required_qty;
            } else {
                $requirements[$child_bom] = $required_qty;
            }
        }
    }
}

function has_bom($product)
{
    $bom_structure = get_bom_structure($product);
    return !empty($bom_structure);
}

// Di dalam file yang relevan, misalnya includes/functions.php atau di tempat yang sesuai

/**
 * Cari produk berdasarkan nomor bagian atau nama.
 * @param string $query Kata kunci pencarian.
 * @return array Daftar produk yang cocok dengan kriteria pencarian.
 */
function find_products_by_part_number_or_name($query)
{
    global $db; // Pastikan variabel $db tersedia

    $escaped_query = $db->escape($query);

    // Sesuaikan query berdasarkan struktur tabel dan kolom Anda
    $sql = "SELECT * FROM products WHERE part_number LIKE '%$escaped_query%' OR name LIKE '%$escaped_query%'";

    $result = $db->query($sql);

    $products = array();
    while ($row = $db->fetch_assoc($result)) {
        $products[] = $row;
    }

    return $products;
}

function get_category_id($category_name)
{
    global $db;

    $sql = "SELECT id FROM categories WHERE name = ?";
    $stmt = $db->prepare($sql);
    $stmt->bind_param("s", $category_name);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        return $row['id'];
    } else {
        // Jika kategori tidak ditemukan, Anda dapat menambahkannya ke database
        $sql = "INSERT INTO categories (name) VALUES (?)";
        $stmt = $db->prepare($sql);
        $stmt->bind_param("s", $category_name);
        if ($stmt->execute()) {
            return $stmt->insert_id;
        } else {
            echo "Error inserting category: " . $stmt->error;
            return null;
        }
    }
}

/**
 * Find a record by part number in the specified table.
 * @param string $table Table name
 * @param string $partNumber Part number
 * @return array|null The record as an associative array, or null if not found
 */
function find_by_part_number($table, $partNumber)
{
    global $db;

    $partNumber = $db->escape($partNumber);
    $sql = "SELECT * FROM {$table} WHERE part_number = '{$partNumber}'";
    $result = $db->query($sql);

    return $db->fetch_assoc($result);
}

function get_product_info($part_number)
{
    global $db;
    $query = "SELECT * FROM products WHERE part_number = '{$part_number}'";
    return find_by_sql($query);
}


function get_bom_info($parent_bom, $child_bom)
{
    global $db;

    $query = "SELECT * FROM bom_level WHERE parent_bom = ? AND child_bom = ?";
    $stmt = $db->prepare($query);
    $stmt->bind_param('ss', $parent_bom, $child_bom);
    $stmt->execute();
    $result = $stmt->get_result();

    return $result->fetch_assoc();
}



// Fungsi untuk mengupdate informasi BOM
function update_bom($parent_bom, $child_bom, $new_qty)
{
    global $db;

    // Pastikan bahwa new_qty adalah tipe data numerik (float atau decimal)
    if (!is_numeric($new_qty)) {
        return false; // Kembalikan false jika new_qty bukan numerik
    }

    $query = "UPDATE bom_level SET qty = ? WHERE parent_bom = ? AND child_bom = ?";
    $stmt = $db->prepare($query);
    $stmt->bind_param('dss', $new_qty, $parent_bom, $child_bom);

    if ($stmt->execute()) {
        return true; // BOM berhasil diupdate
    } else {
        return false; // Gagal mengupdate BOM
    }
}


function redirect_to($location)
{
    header("Location: {$location}");
    exit;
}


function find_bom_by_id($bom_id)
{
    global $db;
    $sql = "SELECT * FROM bom_table WHERE id = '{$db->escape($bom_id)}' LIMIT 1";
    $result_set = $db->query($sql);
    if ($db->num_rows($result_set) > 0) {
        return $db->fetch_assoc($result_set);
    } else {
        return null;
    }
}

// Di dalam file includes/functions.php atau di mana Anda menyimpan fungsi-fungsi Anda
function filter_sales($filter_type)
{
    global $db;

    $current_date = date('Y-m-d');
    $start_date = '';
    $end_date = '';

    if ($filter_type === 'this_month') {
        // Filter Bulan Ini
        $start_date = date('Y-m-01');
        $end_date = date('Y-m-t');
    } elseif ($filter_type === 'last_month') {
        // Filter Bulan Lalu
        $start_date = date('Y-m-01', strtotime('first day of last month'));
        $end_date = date('Y-m-t', strtotime('last day of last month'));
    } elseif ($filter_type === 'custom') {
        // Filter berdasarkan Custom Date (harap sesuaikan dengan input form)
        if (isset($_POST['start_date']) && isset($_POST['end_date'])) {
            $start_date = $_POST['start_date'];
            $end_date = $_POST['end_date'];
        }
    }

    // Query database berdasarkan tanggal yang di-filter
    $start_date = $db->escape($start_date);
    $end_date = $db->escape($end_date);

    $sql = "SELECT p.part_number, p.name as product_name, s.qty, s.date
            FROM sales s
            JOIN products p ON s.product_id = p.id
            WHERE s.date BETWEEN '{$start_date}' AND '{$end_date}'";

    $result = $db->query($sql);
    $sales = array();

    while ($row = $db->fetch_assoc($result)) {
        $sales[] = $row;
    }

    return $sales;
}


// Function to find AWB by resi number
function find_by_resi($resi)
{
    global $db;
    $sql = "SELECT * FROM awb WHERE resi = '{$resi}'";
    $result = $db->query($sql);
    return $db->fetch_assoc($result);
}

function find_by_resi_urgent($resi)
{
    global $db;
    $sql = "SELECT * FROM awb_urgent WHERE resi = '{$resi}'";
    $result = $db->query($sql);
    return $db->fetch_assoc($result);
}

// Function to fetch scanned AWBs from the database
function find_scanned_awbs()
{
    global $db;
    $sql = "SELECT * FROM awb WHERE scan_status IS NOT NULL";
    return find_by_sql($sql);
}

function search_online_products($search_query, $search_option)
{
    global $db;
    $search_query = $db->escape($search_query);
    if ($search_option == "name") {
        $sql = "SELECT * FROM online_products WHERE name LIKE '%$search_query%'";
    } elseif ($search_option == "part_number") {
        $sql = "SELECT * FROM online_products WHERE part_number LIKE '%$search_query%'";
    }
    return find_by_sql($sql);
}

function join_online_product_table($per_page, $page)
{
    global $db;
    $start = ($page - 1) * $per_page;
    $sql = "SELECT * FROM online_products LIMIT {$start}, {$per_page}";
    return find_by_sql($sql);
}

function count_all_online_products()
{
    global $db;
    $sql = "SELECT COUNT(id) AS total FROM online_products";
    $result = $db->query($sql);
    return $db->fetch_assoc($result)['total'];
}

function get_all_online_products()
{
    return find_by_sql("SELECT * FROM online_products");
}



function find_online_product_by_part_number($part_number)
{
    global $db;
    $part_number = $db->escape($part_number);
    $sql = "SELECT * FROM online_products WHERE part_number = '{$part_number}' LIMIT 1";
    $result = find_by_sql($sql);
    return !empty($result) ? $result[0] : false;
}

function update_product_quantity($product_id, $quantity)
{
    global $db;
    $sql = "UPDATE products SET quantity = '{$quantity}' WHERE id = '{$product_id}'";
    return ($db->query($sql)) ? true : false;
}

function update_online_product_quantity($online_product_id, $quantity)
{
    global $db;
    $sql = "UPDATE online_products SET quantity = '{$quantity}' WHERE id = '{$online_product_id}'";
    return ($db->query($sql)) ? true : false;
}

function insert_online_product($product, $quantity)
{
    global $db;
    $sql = "INSERT INTO online_products (name, quantity, UoM, buy_price, categorie_id, media_id, date, part_number, lead_time, loc_id)
            VALUES ('{$db->escape($product['name'])}', '{$quantity}', '{$db->escape($product['UoM'])}', '{$db->escape($product['buy_price'])}', '{$db->escape($product['categorie_id'])}', '{$db->escape($product['media_id'])}', '{$db->escape($product['date'])}', '{$db->escape($product['part_number'])}', '{$db->escape($product['lead_time'])}', '{$db->escape($product['loc_id'])}')";
    return ($db->query($sql)) ? true : false;
}



function get_where_used_list($product_part_number)
{
    global $db;

    $product_part_number = $db->escape($product_part_number);

    // Query untuk mendapatkan daftar di mana produk digunakan
    $sql = "SELECT bl.parent_bom, p.name AS parent_name, bl.qty
            FROM bom_level bl
            LEFT JOIN products p ON bl.parent_bom = p.part_number
            WHERE bl.child_bom = '{$product_part_number}'";

    $result = find_by_sql($sql);

    return $result;
}

function get_all_categories()
{
    global $db;
    return find_by_sql("SELECT * FROM categories");
}

function filter_products_by_category($category_id)
{
    global $db;
    if ($category_id) {
        $sql  = "SELECT p.*, p.UoM as uom, c.name AS categorie ";
        $sql .= "FROM products p ";
        $sql .= "LEFT JOIN categories c ON p.categorie_id = c.id ";
        $sql .= "WHERE p.categorie_id = '{$db->escape($category_id)}'";
        return find_by_sql($sql);
    } else {
        return join_product_table(50, 1); // Replace with your function to get products with default pagination
    }
}

function get_today_scan_summary($loc)
{
    global $db;
    if ($loc == 'surabaya') {
        $query_loc = "AND (loc is NULL OR loc = 'NULL')";
    } elseif ($loc == 'jakarta') {
        $query_loc = "AND loc = 'jakarta'";
    } elseif ($loc == 'bali') {
        $query_loc = "AND loc = 'Bali'";
    } elseif ($loc == 'makasar') {
        $query_loc = "AND (loc = 'makasar' OR loc = 'Makassar')";
    } elseif ($loc == 'bandung') {
        $query_loc = "AND loc = 'bandung'";
    }


    // Query to count unique AWB resi scanned today
    $today_date = date("Y-m-d");
    $sql = "
    SELECT 
        COUNT(DISTINCT CASE WHEN DATE(scan_in) = '{$today_date}' THEN resi END) AS scan_in_count,
        COUNT(DISTINCT CASE WHEN DATE(scan_status) = '{$today_date}' THEN resi END) AS scan_out_count,
        COUNT(DISTINCT CASE WHEN DATE(scan_in) = '{$today_date}' THEN resi END) - 
        COUNT(DISTINCT CASE WHEN DATE(scan_status) = '{$today_date}' THEN resi END) AS difference
    FROM 
        awb 
    WHERE 
        DATE(scan_in) = '{$today_date}' OR DATE(scan_status) = '{$today_date}' $query_loc;
    ";

    $result = $db->query($sql);
    if ($result) {
        $row = $db->fetch_assoc($result);
        return [
            'scan_in_count' => intval($row['scan_in_count']), // Jumlah resi masuk
            'scan_out_count' => intval($row['scan_out_count']), // Jumlah resi keluar
            'difference' => intval($row['difference']) // Selisih antara masuk dan keluar
        ];
    } else {
        return [
            'scan_in_count' => 0,
            'scan_out_count' => 0,
            'difference' => 0
        ]; // Kembalikan 0 jika query gagal atau tidak ada hasil
    }
}
// Function to get today's unique scan count based on AWB resi
function get_today_scan_count()
{
    global $db;

    // Query to count unique AWB resi scanned today
    $today_date = date("Y-m-d");
    $sql = "SELECT COUNT(DISTINCT resi) AS scan_count FROM awb WHERE DATE(scan_status) = '{$today_date}'";

    $result = $db->query($sql);
    if ($result) {
        $row = $db->fetch_assoc($result);
        return intval($row['scan_count']); // Return the count as an integer
    } else {
        return 0; // Return 0 if query fails or no results found
    }
}


// Function to get today's unique scan count based on AWB resi
function get_today_scan_in_count()
{
    global $db;

    // Query to count unique AWB resi scanned today
    $today_date = date("Y-m-d");
    $sql = "SELECT COUNT(DISTINCT resi) AS scan_count FROM awb WHERE DATE(scan_in) = '{$today_date}'";

    $result = $db->query($sql);
    if ($result) {
        $row = $db->fetch_assoc($result);
        return intval($row['scan_count']); // Return the count as an integer
    } else {
        return 0; // Return 0 if query fails or no results found
    }
}


function get_kurir_by_resi($resi_prefix)
{
    global $db;
    $sql = "SELECT kurir FROM kurir WHERE LEFT(kode_kurir, 3) = '$resi_prefix' LIMIT 1";
    $result = find_by_sql($sql);
    return !empty($result) ? $result[0]['kurir'] : 'N/A';
}

function get_bundling_products()
{
    global $db;
    $sql = "SELECT * FROM online_products WHERE categorie_id = 8";
    return find_by_sql($sql);
}

function get_bundling()
{
    global $db;
    $sql = "SELECT bundling_code FROM paket_bundling";
    return find_by_sql($sql);
}

function get_bundling_details($bundling_code)
{
    global $db;
    $sql = "SELECT * FROM paket_bundling WHERE bundling_code = '{$bundling_code}'";
    return find_by_sql($sql);
}

// Function to update quantity of a product in online_products table
function update_online_product_quantity_bundling($part_number, $new_quantity)
{
    global $db;

    // Prepare the SQL statement
    $sql = "UPDATE online_products SET quantity = '{$new_quantity}' WHERE part_number = '{$part_number}'";

    // Execute the SQL statement
    $result = $db->query($sql);

    // Check if the query executed successfully
    if ($result && $db->affected_rows() > 0) {
        return true; // Return true if update successful
    } else {
        return false; // Return false if update failed
    }
}

function update_online_product_quantity_bundling_table($table, $part_number, $new_quantity)
{
    global $db;

    // Prepare the SQL statement
    $sql = "UPDATE online_products SET {$table} = '{$new_quantity}' WHERE part_number = '{$part_number}'";

    // Execute the SQL statement
    $result = $db->query($sql);

    // Check if the query executed successfully
    if ($result && $db->affected_rows() > 0) {
        return true; // Return true if update successful
    } else {
        return false; // Return false if update failed
    }
}

function get_monthly_quantity($part_number)
{
    global $db;
    $current_month = date('Y-m');
    $sql = "SELECT SUM(quantity) AS total_quantity 
            FROM awb 
            WHERE part_number='{$part_number}' 
              AND DATE_FORMAT(scan_status, '%Y-%m') = '{$current_month}'";
    $result = $db->query($sql);
    if ($result && $db->num_rows($result) > 0) {
        $row = $db->fetch_assoc($result);
        return $row['total_quantity'] ? $row['total_quantity'] : 0;
    }
    return 0;
}


function generateShopeeSignature($partner_id, $api_path, $timestamp, $access_token, $shop_id, $partner_key)
{
    // Concatenate the parameters to create the base string
    $base_string = "{$partner_id}{$api_path}{$timestamp}{$access_token}{$shop_id}";

    // Generate HMAC-SHA256 signature
    $signature = hash_hmac('sha256', $base_string, $partner_key);

    return $signature;
}

function formatCurrency($amount)
{
    // Format the amount as currency with thousand separator and currency symbol
    return 'Rp ' . number_format($amount, 0, ',', '.');
}

// shopee API Call
function generateFirstMileTrackingNumber(&$accessToken, &$refreshToken, $shopId)
{
    $baseUrl = SHOPEE_API_URL;
    $partnerId = SHOPEE_API_PARTNER_ID;
    $path = "/api/v2/first_mile/generate_first_mile_tracking_number";
    $partnerKey = SHOPEE_API_PARTNER_KEY;
    $timestamp = time();
    $declareDate = date("Y-m-d");
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId,  $partnerKey);
    $url = $baseUrl . $path;
    $curl = curl_init();

    $postData = json_encode(array(
        "declare_date" => $declareDate
    ));

    curl_setopt_array($curl, array(
        CURLOPT_URL => $url . '?access_token=' . $accessToken . '&partner_id=' . $partnerId . '&shop_id=' . $shopId . '&sign=' . $sign . '&timestamp=' . $timestamp,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'POST',
        CURLOPT_POSTFIELDS => $postData,
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
    ));

    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
    curl_close($curl);

    if ($httpCode === 403) {
        // Minta token baru
        $responseTokens = refreshAccessToken($refreshToken, $shopId);
        if ($responseTokens[2]) {
            $accessToken = $responseTokens[0]; // New access token
            $refreshToken = $responseTokens[1]; // New refresh token
            // Ulangi permintaan dengan token baru
            return generateFirstMileTrackingNumber($responseTokens[0], $responseTokens[1], $shopId);
        } else {
            return null; // Gagal mendapatkan token baru
        }
    }

    return $response;
}




function getOrderList(&$accessToken, &$refreshToken, $pageSize, $partnerId, $shopId, $timeFrom, $timeTo, $cursor = "")
{
    $baseUrl = SHOPEE_API_URL;
    $path = "/api/v2/order/get_order_list";
    $partnerKey = SHOPEE_API_PARTNER_KEY;
    $timestamp = time();
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId,  $partnerKey);
    $curl = curl_init();

    // '&order_status=' . urlencode($orderStatus) .

    // Construct the URL with the provided parameters
    $url = $baseUrl . $path . '?access_token=' . urlencode($accessToken) .
        '&page_size=' . $pageSize .
        '&partner_id=' . $partnerId .
        '&request_order_status_pending=true' .
        '&response_optional_fields=order_status' .
        '&shop_id=' . $shopId .
        '&time_from=' . $timeFrom .
        '&time_range_field=create_time' .
        '&time_to=' . $timeTo .
        '&timestamp=' . $timestamp .
        '&cursor=' . $cursor .
        '&sign=' . $sign;

    curl_setopt_array($curl, array(
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'GET',
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
    ));

    curl_setopt($curl, CURLOPT_TIMEOUT, 10);
    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);

    curl_close($curl);
    if ($httpCode === 403) {
        // Minta token baru
        $responseTokens = refreshAccessToken($refreshToken, $shopId);
        if ($responseTokens[2]) {
            $accessToken = $responseTokens[0]; // New access token
            $refreshToken = $responseTokens[1]; // New refresh token
            // Ulangi permintaan dengan token baru
            return getOrderList($responseTokens[0], $responseTokens[1], $pageSize, $partnerId, $shopId, $timeFrom, $timeTo, $cursor);
        } else {
            return null; // Gagal mendapatkan token baru
        }
    } else if ($httpCode === 200) {
        return $response;
    } else {
        return null;
    }
}

function getAuthShopee($shop_id, $code)
{
    global $db;
    $baseUrl = SHOPEE_API_URL;
    $partner_id = SHOPEE_API_PARTNER_ID;
    $partner_key = SHOPEE_API_PARTNER_KEY;

    $timestamp = time();

    // Define the API path
    $api_path = '/api/v2/auth/token/get';

    // Create the base string for signing
    $base_string = "{$partner_id}{$api_path}{$timestamp}";

    // Generate HMAC-SHA256 signature
    $sign = urlencode(hash_hmac('sha256', $base_string, $partner_key));

    // API URL
    $url = "{$baseUrl}{$api_path}?partner_id={$partner_id}&sign={$sign}&timestamp={$timestamp}";

    // Data to be sent in the request body
    $data = json_encode([
        "shop_id" => (int)$shop_id,
        "code" => $code,
        "partner_id" => (int)$partner_id
    ]);

    // Initialize cURL
    $ch = curl_init($url);

    // Set cURL options
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // Return the response as a string
    curl_setopt($ch, CURLOPT_HTTPHEADER, [
        'Content-Type: application/json',
        'Cookie: SPC_SI=8Q4lZwAAAAAyd3B2VzR6anezDgAAAAAAeFA2R3JnUnQ=' // Set the cookie
    ]);
    curl_setopt($ch, CURLOPT_POST, true); // Use POST method
    curl_setopt($ch, CURLOPT_POSTFIELDS, $data); // Attach the JSON data
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

    // Execute the request
    $response = curl_exec($ch);

    // Close cURL session
    curl_close($ch);

    // Check for errors
    if (curl_errno($ch)) {

        echo 'Error:' . curl_error($ch);
    } else {
        // Print the response
        $data = json_decode($response, true);
        // Check if the response contains the expected fields
        if (isset($data['refresh_token'])) {
            $refresh_token = $data['refresh_token'];
            $access_token = $data['access_token'];
            $expire_in = $data['expire_in'];
            $platform_id = 1;

            $response = getShopDetail($access_token, $refresh_token, $shop_id);
            $shopLogo = $response['response']['shop_logo'] ?? "";
            $description = $response['response']['description'] ?? "";
            $shopName = $response['response']['shop_name'] ?? "";

            // Prepare the SQL statement to insert or update the tokens
            $sql = "UPDATE shop SET refresh_token = ?, access_token = ?, expire_in = ?, shop_logo = ?, description = ?, shop_name = ? WHERE shop_id = ? and platform_id = ?";
            $stmt = $db->prepare($sql);

            // Bind parameters
            $stmt->bind_param("ssissssi", $refresh_token, $access_token, $expire_in, $shopLogo, $description, $shopName, $shop_id, $platform_id);

            if (!$stmt->execute()) {
                return ["", "", false]; // Jika gagal, hentikan proses
            }

            $sql = "UPDATE queue_shipment SET refresh_token = ?, access_token = ? WHERE shop_id = ? and platform_id = ? and flag_delete = 0";
            $stmt = $db->prepare($sql);

            // Bind parameters untuk query kedua
            $stmt->bind_param("sssi", $refresh_token, $access_token, $shop_id, $platform_id);

            // Execute the statement
            if ($stmt->execute()) {
                return [$access_token, $refresh_token, true];
            } else {
                return ["", "", false];
            }
        } else {
            return [$response, "", false];
        }
    }
}

function getAuthMainShopee($main_account_id, $code)
{
    global $db;
    $baseUrl = SHOPEE_API_URL;
    $partner_id = SHOPEE_API_PARTNER_ID;
    $partner_key = SHOPEE_API_PARTNER_KEY;

    $timestamp = time();

    // Define the API path
    $api_path = '/api/v2/auth/token/get';

    // Create the base string for signing
    $base_string = "{$partner_id}{$api_path}{$timestamp}";

    // Generate HMAC-SHA256 signature
    $sign = urlencode(hash_hmac('sha256', $base_string, $partner_key));

    // API URL
    $url = "{$baseUrl}{$api_path}?partner_id={$partner_id}&sign={$sign}&timestamp={$timestamp}";

    // Data to be sent in the request body
    $data = json_encode([
        "main_account_id" => (int)$main_account_id,
        "code" => $code,
        "partner_id" => (int)$partner_id
    ]);

    // Initialize cURL
    $ch = curl_init($url);

    // Set cURL options
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // Return the response as a string
    curl_setopt($ch, CURLOPT_HTTPHEADER, [
        'Content-Type: application/json',
        'Cookie: SPC_SI=8Q4lZwAAAAAyd3B2VzR6anezDgAAAAAAeFA2R3JnUnQ=' // Set the cookie
    ]);
    curl_setopt($ch, CURLOPT_POST, true); // Use POST method
    curl_setopt($ch, CURLOPT_POSTFIELDS, $data); // Attach the JSON data
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

    // Execute the request
    $response = curl_exec($ch);

    // Close cURL session
    curl_close($ch);

    // Check for errors
    if (curl_errno($ch)) {

        echo 'Error:' . curl_error($ch);
    } else {
        // Print the response
        $data = json_decode($response, true);
        echo "<pre>";
        var_dump($data);
        echo "</pre>";
    }
}

function addOrderlist(&$accessToken, &$refreshToken, $shopId, $dataArray = array(), $next_cursor = "")
{
    global $db;
    $pageSize = 100; // maximum page size
    $partnerId = SHOPEE_API_PARTNER_ID;

    $timeFrom = strtotime('-15 days'); // max 15 days
    $timeTo = time();

    $response = getOrderList($accessToken, $refreshToken, $pageSize, $partnerId, $shopId, $timeFrom, $timeTo, $next_cursor);
    $data = json_decode($response, true);

    $orderList = $data['response']['order_list'];
    $orderListMore = $data['response']['more'];
    $currentDatetime = date('Y-m-d H:i:s');

    foreach ($orderList as $order) {
        $orderSn = $order['order_sn'];
        $orderStatus = $order['order_status'];

        // Check if order_sn already exists
        $stmt = $db->prepare("SELECT order_status FROM orders WHERE order_sn = ?");
        $stmt->bind_param("s", $orderSn);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($result->num_rows > 0) {
            // Order exists, check if order_status is different
            $row = $result->fetch_assoc();
            if ($row['order_status'] !== $orderStatus) {
                // Update order_status if it's different
                $updateStmt = $db->prepare("UPDATE orders SET order_status = ?, last_call = ? WHERE order_sn = ?");
                $updateStmt->bind_param("sss", $orderStatus, $currentDatetime, $orderSn);
                $updateStmt->execute();
                // echo "Updated order status for order_sn: $orderSn\n";
                array_push($dataArray, $orderSn);
            } else {
                array_push($dataArray, $orderSn);
            }
        } else {
            // Order does not exist, insert new record
            $insertStmt = $db->prepare("INSERT INTO orders (order_sn, order_status, shop_id, last_call) VALUES (?, ?, ?, ?)");
            $insertStmt->bind_param("ssis", $orderSn, $orderStatus, $shopId, $currentDatetime);
            $insertStmt->execute();
            array_push($dataArray, $orderSn);
        }
    }

    if ($orderListMore) {
        $next_cursor = $data['response']['next_cursor'];
        // Recursive call to get more order list
        return addOrderList($accessToken, $refreshToken, $shopId, $dataArray, $next_cursor);
    }

    return $dataArray;
}

function getDetailOrder(&$accessToken, &$refreshToken, $shopId, $orderSnList)
{
    // Define your parameters
    $baseUrl = SHOPEE_API_URL;
    $partnerKey = SHOPEE_API_PARTNER_KEY;
    $partnerId = SHOPEE_API_PARTNER_ID;
    $timestamp = time();

    $path = '/api/v2/order/get_order_detail';
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId,  $partnerKey);

    // Build the query parameters
    $params = [
        'access_token' => $accessToken,
        'order_sn_list' => $orderSnList,
        'partner_id' => $partnerId,
        'request_order_status_pending' => 'true',
        'shop_id' => $shopId,
        'sign' => $sign,
        'timestamp' => $timestamp,
        'response_optional_fields' => 'item_list,payment_method,buyer_username,buyer_user_id,recipient_address,total_amount,shipping_carrier,package_list',
    ];

    // Use executeCurl to make the request
    $response = executeCurl(
        $baseUrl,
        $path,
        $params,           // query parameters
        [],                // headers (no additional headers needed in this case)
        'GET',              // HTTP method
        null,
        $orderSnList
    );

    if ($response === null) {
        return null; // Failed to execute cURL request
    }

    if (!empty($response['error'])) {
        // Minta token baru (Request a new token)
        $responseTokens = refreshAccessToken($refreshToken, $shopId);
        if ($responseTokens[2]) {
            $accessToken = $responseTokens[0]; // New access token
            $refreshToken = $responseTokens[1]; // New refresh token
            // Retry the request with the new token
            return getDetailOrder($accessToken, $refreshToken, $shopId, $orderSnList);
        } else {
            return null; // Failed to get a new token
        }
    }

    // Return the decoded response
    return $response;
}

function saveOrderDetails($response)
{
    global $db;
    $orderId = 0;
    $db->begin_transaction();
    try {
        // Extract order details
        $orderList = $response['response']['order_list'];

        foreach ($orderList as $order) {
            $buyer_user_id = $order['buyer_user_id'] ?? 0;
            $buyer_username = $order['buyer_username'] ?? "";
            $order_status = $order['order_status'] ?? "";
            $payment_method = $order['payment_method'] ?? "";
            $total_amount = $order['total_amount'] ?? 0.0;
            $currency = $order['currency'] ?? "";
            $shipping_carrier = $order['shipping_carrier'] ?? "";
            $create_time = 0;
            if (isset($order['create_time'])) {
                $create_time =  date("Y-m-d H:i:s", $order['create_time']);
            }

            $order_sn = $order['order_sn'] ?? "";

            // Prepare the query
            $stmtOrder = $db->prepare("UPDATE orders SET buyer_username = ?, order_status = ?, payment_method = ?, total_amount = ?, buyer_user_id = ?, create_time = ?, currency = ?, shipping_carrier = ? WHERE order_sn = ?");
            if (!$stmtOrder) {
                throw new Exception("Prepare failed for UPDATE orders: " . $db->error);
            }

            // Bind parameters
            $stmtOrder->bind_param(
                "sssdissss",
                $buyer_username,
                $order_status,
                $payment_method,
                $total_amount,
                $buyer_user_id,
                $create_time,
                $currency,
                $shipping_carrier,
                $order_sn
            );

            // Execute the query
            if (!$stmtOrder->execute()) {
                throw new Exception("Execute failed for UPDATE orders: " . $stmtOrder->error);
            }

            // Get order_id
            $stmtGetId = $db->prepare("SELECT id FROM orders WHERE order_sn = ?");
            if (!$stmtGetId) {
                throw new Exception("Prepare failed for SELECT order_id: " . $db->error);
            }
            $stmtGetId->bind_param("s", $order_sn);
            if (!$stmtGetId->execute()) {
                throw new Exception("Execute failed for SELECT order_id: " . $stmtGetId->error);
            }

            $result = $stmtGetId->get_result();
            if ($result->num_rows > 0) {
                $row = $result->fetch_assoc();
                $orderId = $row['id'];
            }

            // Check if order_id already exists in Shopee_Items
            $countItem = 0;
            $stmtCheck = $db->prepare("SELECT COUNT(*) FROM order_detail WHERE order_id = ?");
            if (!$stmtCheck) {
                throw new Exception("Prepare failed for SELECT COUNT: " . $db->error);
            }
            $stmtCheck->bind_param("i", $orderId);
            if (!$stmtCheck->execute()) {
                throw new Exception("Execute failed for SELECT COUNT: " . $stmtCheck->error);
            }
            $stmtCheck->bind_result($countItem);
            $stmtCheck->fetch();
            $stmtCheck->close();

            // Insert if not exists
            if ($countItem == 0) {
                $product_location_id = "";
                foreach ($order['item_list'] as $item) {

                    $stmtItem = $db->prepare("INSERT INTO order_detail (order_item_id, model_sku, item_name, model_id, model_quantity_purchased, model_discounted_price, model_original_price, image_url, order_id, item_sku, order_sn, weight) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                    if (!$stmtItem) {
                        throw new Exception("Prepare failed for INSERT order_detail: " . $db->error);
                    }
                    $stmtItem->bind_param("issiddssiss", $item['item_id'], $item['model_sku'], $item['item_name'], $item['model_id'], $item['model_quantity_purchased'], $item['model_discounted_price'], $item['model_original_price'], $item['image_info']['image_url'], $orderId, $item['item_sku'], $order_sn, $item['weight']);
                    if (!$stmtItem->execute()) {
                        throw new Exception("Execute failed for INSERT order_detail: " . $stmtItem->error);
                    }
                    if (!empty($item['product_location_id'][0])) {
                        $product_location_id = $item['product_location_id'][0];
                    }
                }

                $stmtOrder = $db->prepare("UPDATE orders SET product_location_id = ? WHERE order_sn = ?");
                if (!$stmtOrder) {
                    throw new Exception("Prepare failed for UPDATE product_location_id: " . $db->error);
                }
                $stmtOrder->bind_param("ss", $product_location_id, $order_sn);
                if (!$stmtOrder->execute()) {
                    throw new Exception("Execute failed for UPDATE product_location_id: " . $stmtOrder->error);
                }
            }
        }

        $db->commit();
    } catch (Exception $e) {
        // Rollback transaction on error
        $db->rollback();
        echo "Error: " . $e->getMessage();
        die();
    }
}

function insertOrderDetails($response, $shopId)
{
    global $db;
    $orderId = 0;
    $db->begin_transaction();
    try {
        // Extract order details
        $orderList = $response['response']['order_list'];

        foreach ($orderList as $order) {
            $orderSn = $order['order_sn'];
            $order_sn = $order['order_sn'];
            $orderStatus = $order['order_status'];

            // Check if order_sn already exists
            $stmt = $db->prepare("SELECT order_status FROM orders WHERE order_sn = ?");
            $stmt->bind_param("s", $orderSn);
            $stmt->execute();
            $result = $stmt->get_result();

            if ($result->num_rows > 0) {
                // Order exists, check if order_status is different
                $row = $result->fetch_assoc();
                if ($row['order_status'] !== $orderStatus) {
                    // Update order_status if it's different
                    $updateStmt = $db->prepare("UPDATE orders SET order_status = ? WHERE order_sn = ?");
                    $updateStmt->bind_param("ss", $orderStatus, $orderSn);
                    $updateStmt->execute();
                }
            } else {
                // Order does not exist, insert new record
                $insertStmt = $db->prepare("INSERT INTO orders (order_sn, order_status, shop_id) VALUES (?, ?, ?)");
                $insertStmt->bind_param("sss", $orderSn, $orderStatus, $shopId);
                $insertStmt->execute();
            }



            $buyer_user_id = $order['buyer_user_id'] ?? 0;
            $buyer_username = $order['buyer_username'] ?? "";
            $order_status = $order['order_status'] ?? "";
            $payment_method = $order['payment_method'] ?? "";
            $total_amount = $order['total_amount'] ?? 0.0;
            $currency = $order['currency'] ?? "";
            $shipping_carrier = $order['shipping_carrier'] ?? "";
            $create_time = 0;
            if (isset($order['create_time'])) {
                $create_time =  date("Y-m-d H:i:s", $order['create_time']);
            }


            // Prepare the query
            $stmtOrder = $db->prepare("UPDATE orders SET buyer_username = ?, order_status = ?, payment_method = ?, total_amount = ?, buyer_user_id = ?, create_time = ?, currency = ?, shipping_carrier = ? WHERE order_sn = ?");
            if (!$stmtOrder) {
                throw new Exception("Prepare failed for UPDATE orders: " . $db->error);
            }

            // Bind parameters
            $stmtOrder->bind_param(
                "sssdissss",
                $buyer_username,
                $order_status,
                $payment_method,
                $total_amount,
                $buyer_user_id,
                $create_time,
                $currency,
                $shipping_carrier,
                $order_sn
            );

            // Execute the query
            if (!$stmtOrder->execute()) {
                throw new Exception("Execute failed for UPDATE orders: " . $stmtOrder->error);
            }

            // Get order_id
            $stmtGetId = $db->prepare("SELECT id FROM orders WHERE order_sn = ?");
            if (!$stmtGetId) {
                throw new Exception("Prepare failed for SELECT order_id: " . $db->error);
            }
            $stmtGetId->bind_param("s", $order_sn);
            if (!$stmtGetId->execute()) {
                throw new Exception("Execute failed for SELECT order_id: " . $stmtGetId->error);
            }

            $result = $stmtGetId->get_result();
            if ($result->num_rows > 0) {
                $row = $result->fetch_assoc();
                $orderId = $row['id'];
            }

            // Check if order_id already exists in Shopee_Items
            $countItem = 0;
            $stmtCheck = $db->prepare("SELECT COUNT(*) FROM order_detail WHERE order_id = ?");
            if (!$stmtCheck) {
                throw new Exception("Prepare failed for SELECT COUNT: " . $db->error);
            }
            $stmtCheck->bind_param("i", $orderId);
            if (!$stmtCheck->execute()) {
                throw new Exception("Execute failed for SELECT COUNT: " . $stmtCheck->error);
            }
            $stmtCheck->bind_result($countItem);
            $stmtCheck->fetch();
            $stmtCheck->close();

            // Insert if not exists
            if ($countItem == 0) {
                $product_location_id = "";
                foreach ($order['item_list'] as $item) {

                    $stmtItem = $db->prepare("INSERT INTO order_detail (order_item_id, model_sku, item_name, model_id, model_quantity_purchased, model_discounted_price, model_original_price, image_url, order_id, item_sku, order_sn, weight) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                    if (!$stmtItem) {
                        throw new Exception("Prepare failed for INSERT order_detail: " . $db->error);
                    }
                    $stmtItem->bind_param("issiddssissd", $item['item_id'], $item['model_sku'], $item['item_name'], $item['model_id'], $item['model_quantity_purchased'], $item['model_discounted_price'], $item['model_original_price'], $item['image_info']['image_url'], $orderId, $item['item_sku'], $order_sn, $item['weight']);
                    if (!$stmtItem->execute()) {
                        throw new Exception("Execute failed for INSERT order_detail: " . $stmtItem->error);
                    }
                    if (!empty($item['product_location_id'][0])) {
                        $product_location_id = $item['product_location_id'][0];
                    }
                }

                $stmtOrder = $db->prepare("UPDATE orders SET product_location_id = ? WHERE order_sn = ?");
                if (!$stmtOrder) {
                    throw new Exception("Prepare failed for UPDATE product_location_id: " . $db->error);
                }
                $stmtOrder->bind_param("ss", $product_location_id, $order_sn);
                if (!$stmtOrder->execute()) {
                    throw new Exception("Execute failed for UPDATE product_location_id: " . $stmtOrder->error);
                }
            }
        }

        $db->commit();
    } catch (Exception $e) {
        // Rollback transaction on error
        $db->rollback();
        echo "Error: " . $e->getMessage();
        die();
    }
}


function refreshAccessToken($refreshToken, $shopId)
{
    global $db;

    $baseUrl = SHOPEE_API_URL;
    $partnerId = SHOPEE_API_PARTNER_ID;
    $partnerKey = SHOPEE_API_PARTNER_KEY;
    $path = "/api/v2/auth/access_token/get";
    $timestamp = time();
    // Create the base string for signing
    $base_string = "{$partnerId}{$path}{$timestamp}";

    // Generate HMAC-SHA256 signature
    $sign = urlencode(hash_hmac('sha256', $base_string, $partnerKey));

    $url = $baseUrl . $path . "?partner_id=" . $partnerId . "&sign=" . $sign . "&timestamp=" . $timestamp;
    $data = [
        'partner_id' => (int)$partnerId,
        'refresh_token' => $refreshToken,
        'shop_id' => (int)$shopId
    ];

    // Inisialisasi cURL untuk refresh token
    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_HTTPHEADER, [
        'Content-Type: application/json'
    ]);
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));

    curl_setopt($ch, CURLOPT_TIMEOUT, 30);

    // Eksekusi permintaan
    $response = curl_exec($ch);
    curl_close($ch);
    $data = json_decode($response, true);
    if (isset($data['refresh_token'])) {
        $refresh_token = $data['refresh_token'];
        $access_token = $data['access_token'];
        $expire_in = $data['expire_in'];

        // Prepare the SQL statement to insert or update the tokens
        $sql = "UPDATE shop SET refresh_token = ?, access_token = ?, expire_in = ? WHERE shop_id = ?";
        $stmt = $db->prepare($sql);

        // Bind parameters
        $stmt->bind_param("ssis", $refresh_token, $access_token, $expire_in, $shopId);

        if (!$stmt->execute()) {
            return [$access_token, $refresh_token, true];
        }

        $sql = "UPDATE queue_shipment SET refresh_token = ?, access_token = ? WHERE shop_id = ? and flag_delete = 0";
        $stmt = $db->prepare($sql);

        // Bind parameters untuk query kedua
        $stmt->bind_param("sss", $refresh_token, $access_token, $shopId);

        // Execute the statement
        if ($stmt->execute()) {
            return [$access_token, $refresh_token, true];
        } else {
            return ['', '', false];
        }
    } else if (isset($data['error'])) {
        $sql = "UPDATE shop 
        SET access_token = '' 
        WHERE shop_id = ?";
        $stmt = $db->prepare($sql);

        // Bind parameters
        $stmt->bind_param("s", $shopId);
        $stmt->execute();

        echo "Refresh Token Failed Shop " . $shopId;
        echo "<br/>";
        echo $response;
        echo "<br/>";
        die();
    }
    return ['', '', false];
}

function makeCurlRequest($url, $method = 'GET', $headers = [], $data = null)
{
    $curl = curl_init();

    // Set default options
    curl_setopt_array($curl, [
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => $method,
        CURLOPT_HTTPHEADER => array_merge(['Content-Type: application/json'], $headers),
    ]);

    // If the method is POST or PUT, add the data
    if ($data !== null) {
        curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode($data));
    }

    // Execute the request
    $response = curl_exec($curl);

    // Check for errors
    if (curl_errno($curl)) {
        $error_msg = curl_error($curl);
        curl_close($curl);
        return ['error' => true, 'message' => $error_msg];
    }

    // Close the cURL session
    curl_close($curl);

    // Return the response
    return json_decode($response, true);
}

function getShopDetail(&$accessToken, &$refreshToken, $shopId)
{
    $baseUrl = SHOPEE_API_URL;
    $partnerId = SHOPEE_API_PARTNER_ID;
    $partnerKey = SHOPEE_API_PARTNER_KEY;
    $path = "/api/v2/shop/get_profile";
    $timestamp = time();
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId,  $partnerKey);


    $url =  $baseUrl . $path;
    $params = [
        'access_token' => $accessToken,
        'partner_id' => $partnerId,
        'shop_id' => $shopId,
        'sign' => $sign,
        'timestamp' => $timestamp
    ];

    $response = makeCurlRequest($url . '?' . http_build_query($params));

    return $response;
}

function getShipmentListAll($access_token, $refresh_token, $shop_id, $data = array())
{
    $responeJson = getShipmentList($access_token, $refresh_token, $shop_id);
    if ($responeJson === null) {
        return null;
    }
    $respone = json_decode($responeJson, true);
    $orderList = $respone['response']['order_list'];
    $more = $respone['response']['more'];
    if ($orderList == null) {
        return null;
    }
    foreach ($orderList as $order) {
        array_push($data, $order['order_sn']);
    }

    if ($more) {
        return getShipmentListAll($access_token, $refresh_token, $shop_id, $data);
    }

    return $data;
}
function getShipmentList(&$accessToken, &$refreshToken, $shopId, $cursor = '')
{
    $baseUrl = SHOPEE_API_URL;
    $partnerId = SHOPEE_API_PARTNER_ID;
    $pageSize = 100;
    $partnerKey = SHOPEE_API_PARTNER_KEY;
    $path = "/api/v2/order/get_shipment_list";
    $timestamp = time();
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId,  $partnerKey);


    $url =  $baseUrl . $path;

    $curl = curl_init();

    // Construct the URL with parameters
    $param = sprintf(
        '?access_token=%s&cursor=%s&page_size=%d&partner_id=%s&shop_id=%s&sign=%s&timestamp=%s',
        urlencode($accessToken),
        urlencode($cursor),
        $pageSize,
        urlencode($partnerId),
        urlencode($shopId),
        urlencode($sign),
        urlencode($timestamp)
    );

    $url = $url . $param;

    curl_setopt_array($curl, array(
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'GET',
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
    ));

    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
    curl_close($curl);

    if ($httpCode === 403) {
        // Minta token baru
        $responseTokens = refreshAccessToken($refreshToken, $shopId);
        if ($responseTokens[2]) {
            $accessToken = $responseTokens[0]; // New access token
            $refreshToken = $responseTokens[1]; // New refresh token
            // Ulangi permintaan dengan token baru
            return getShipmentList($responseTokens[0], $responseTokens[1], $shopId, $cursor);
        } else {
            return null; // Gagal mendapatkan token baru
        }
    }

    return $response;
}
function getShippingParameter(&$accessToken, &$refreshToken, $orderSn, $shopId)
{
    $timestamp = time();
    $path = "/api/v2/logistics/get_shipping_parameter";
    $partnerKey = SHOPEE_API_PARTNER_KEY;
    $partnerId = SHOPEE_API_PARTNER_ID;
    $baseUrl = SHOPEE_API_URL;
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId, $partnerKey);
    $curl = curl_init();

    // Construct the URL with parameters
    $param = sprintf(
        '?access_token=%s&order_sn=%s&partner_id=%s&shop_id=%s&sign=%s&timestamp=%s',
        urlencode($accessToken),
        urlencode($orderSn),
        urlencode($partnerId),
        urlencode($shopId),
        urlencode($sign),
        urlencode($timestamp)
    );

    $url = $baseUrl . $path . $param;

    curl_setopt_array($curl, array(
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'GET',
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
    ));

    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
    curl_close($curl);

    if ($httpCode === 403) {
        // Minta token baru
        $responseTokens = refreshAccessToken($refreshToken, $shopId);
        if ($responseTokens[2]) {
            $accessToken = $responseTokens[0]; // New access token
            $refreshToken = $responseTokens[1]; // New refresh token
            // Ulangi permintaan dengan token baru
            return getShippingParameter($responseTokens[0], $responseTokens[1], $orderSn, $shopId);
        } else {
            return null; // Gagal mendapatkan token baru
        }
    }

    return $response;
}

function shipOrder(&$accessToken, &$refreshToken, $shopId, $orderSn, $addressId)
{

    $timestamp = time();
    $path = "/api/v2/logistics/ship_order";
    $partnerKey = SHOPEE_API_PARTNER_KEY;
    $partnerId = SHOPEE_API_PARTNER_ID;
    $baseUrl = SHOPEE_API_URL;
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId, $partnerKey);
    $curl = curl_init();

    // Construct the URL with parameters
    $param = sprintf(
        '?access_token=%s&partner_id=%s&shop_id=%s&sign=%s&timestamp=%s',
        urlencode($accessToken),
        urlencode($partnerId),
        urlencode($shopId),
        urlencode($sign),
        urlencode($timestamp)
    );

    $url = $baseUrl . $path . $param;

    // Prepare the POST fields as an associative array
    $postFields = array(
        'order_sn' => $orderSn,
        'pickup' => array(
            'address_id' => $addressId,
        )
    );

    // Convert the array to JSON format
    $jsonPostFields = json_encode($postFields);

    curl_setopt_array($curl, array(
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'POST',
        CURLOPT_POSTFIELDS => $jsonPostFields,
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
    ));

    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
    curl_close($curl);

    if ($httpCode === 403) {
        // Minta token baru
        $responseTokens = refreshAccessToken($refreshToken, $shopId);
        if ($responseTokens[2]) {
            $accessToken = $responseTokens[0]; // New access token
            $refreshToken = $responseTokens[1]; // New refresh token
            // Ulangi permintaan dengan token baru
            return shipOrder($responseTokens[0], $responseTokens[1], $shopId, $orderSn, $addressId);
        } else {
            return null; // Gagal mendapatkan token baru
        }
    }

    return $response;
}

function updateShippingOrder(&$accessToken, &$refreshToken, $shopId, $orderSn, $addressId, $pickupTimeId)
{
    $path = "/api/v2/logistics/update_shipping_order";


    $timestamp = time();
    $path = "/api/v2/logistics/ship_order";
    $partnerKey = SHOPEE_API_PARTNER_KEY;
    $partnerId = SHOPEE_API_PARTNER_ID;
    $baseUrl = SHOPEE_API_URL;
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId, $partnerKey);
    $curl = curl_init();

    // Construct the URL with parameters
    $param = sprintf(
        '?access_token=%s&partner_id=%s&shop_id=%s&sign=%s&timestamp=%s',
        urlencode($accessToken),
        urlencode($partnerId),
        urlencode($shopId),
        urlencode($sign),
        urlencode($timestamp)
    );

    $url = $baseUrl . $path . $param;

    // Prepare the POST fields as an associative array
    $postFields = array(
        'order_sn' => $orderSn,
        'pickup' => array(
            'address_id' => $addressId,
            'pickup_time_id' => $pickupTimeId
        )
    );

    // Convert the array to JSON format
    $jsonPostFields = json_encode($postFields);

    curl_setopt_array($curl, array(
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'POST',
        CURLOPT_POSTFIELDS => $jsonPostFields,
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
    ));

    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
    curl_close($curl);

    if ($httpCode === 403) {
        // Minta token baru
        $responseTokens = refreshAccessToken($refreshToken, $shopId);
        if ($responseTokens[2]) {
            $accessToken = $responseTokens[0]; // New access token
            $refreshToken = $responseTokens[1]; // New refresh token
            // Ulangi permintaan dengan token baru
            return updateShippingOrder($responseTokens[0], $responseTokens[1], $shopId, $orderSn, $addressId, $pickupTimeId);
        } else {
            return null; // Gagal mendapatkan token baru
        }
    }

    return $response;
}

function getTrackingNumber(&$accessToken, &$refreshToken, $orderSn, $shopId, $retryCount = 0)
{
    $baseUrl = SHOPEE_API_URL;
    $partnerId = SHOPEE_API_PARTNER_ID;
    $partnerKey = SHOPEE_API_PARTNER_KEY;
    $path = "/api/v2/logistics/get_tracking_number";
    $timestamp = time();
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId, $partnerKey);

    $curl = curl_init();

    // Construct the URL with parameters
    $param = sprintf(
        '?access_token=%s&order_sn=%s&partner_id=%s&response_optional_fields=first_mile_tracking_number&shop_id=%s&sign=%s&timestamp=%s',
        urlencode($accessToken),
        urlencode($orderSn),
        urlencode($partnerId),
        urlencode($shopId),
        urlencode($sign),
        urlencode($timestamp)
    );

    $url = $baseUrl . $path . $param;

    curl_setopt_array($curl, array(
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'GET',
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
    ));

    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);

    curl_close($curl);

    if ($httpCode == 403) {
        // Minta token baru
        $responseTokens = refreshAccessToken($refreshToken, $shopId);
        if ($responseTokens[2]) {
            $accessToken = $responseTokens[0]; // New access token
            $refreshToken = $responseTokens[1]; // New refresh token
            // Ulangi permintaan dengan token baru
            return getTrackingNumber($accessToken, $refreshToken, $orderSn, $shopId, $retryCount);
        } else {
            return null; // Gagal mendapatkan token baru
        }
    } else {
        $decode = json_decode($response, true); // Menggunakan true untuk mendapatkan array asosiatif
        $resi = $decode['response']['tracking_number'] ?? null;

        if (empty($resi)) {
            if ($retryCount <= 5) { // Batasi ke maksimal 3 percobaan
                sleep(2); // Tunggu 5 menit sebelum mencoba lagi
                return getTrackingNumber($accessToken, $refreshToken, $orderSn, $shopId, $retryCount + 1);
            } else {
                return $response; // Return response setelah 3 percobaan
            }
        }
    }

    return $response;
}


// get_shipping_document_parameter
function getShippingDocumentParameter(&$accessToken, &$refreshToken, $shopId, $data = array())
{
    // example data
    //  $data = [
    //     "order_list" => [
    //         [
    //             "order_sn" => "201201E81SYYKE",
    //             "package_number" => "60489687088750"
    //         ],
    //         [
    //             "order_sn" => "201201V81SYYDG"
    //         ]
    //     ]
    // ];

    $baseUrl = SHOPEE_API_URL;
    $partnerId = SHOPEE_API_PARTNER_ID;
    $partnerKey = SHOPEE_API_PARTNER_KEY;
    $path = "/api/v2/logistics/get_shipping_document_parameter";
    $timestamp = time();
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId, $partnerKey);

    $params = [
        'access_token' => $accessToken,
        'partner_id' => (int)$partnerId,
        'shop_id' => (int)$shopId,
        'sign' => $sign,
        'timestamp' => $timestamp
    ];
    $method = 'POST';
    // Build the URL with query parameters
    $url = $baseUrl . $path . '?' . http_build_query($params);

    $curl = curl_init();

    // Set cURL options
    curl_setopt_array($curl, array(
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => $method,
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
    ));

    // If data is provided, set it as POSTFIELDS
    if ($data) {
        curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode($data));
    }

    // Execute the request and close the session
    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
    curl_close($curl);

    if ($httpCode == 403) {
        // Refresh access token and retry
        $responseTokens = refreshAccessToken($refreshToken, $shopId);
        if ($responseTokens[2]) {
            $accessToken = $responseTokens[0]; // New access token
            $refreshToken = $responseTokens[1]; // New refresh token
            return getShippingDocumentParameter($responseTokens[0], $responseTokens[1], $shopId, $data);
        } else {
            return null; // Failed to refresh access token
        }
    }

    return $response;
}


function createShippingDocument(&$accessToken, &$refreshToken, $shopId, $data = array())
{
    // example data
    //  $data = [
    //     "order_list" => [
    //         [
    //             "order_sn" => "201118BCKPJQQ8",
    //             "package_number" => "2485710696837122445",
    //             "shipping_document_type" => "NORMAL_AIR_WAYBILL",
    //             "tracking_number" => "SPXID02742637123B"
    //         ]
    //     ]
    // ];
    $baseUrl = SHOPEE_API_URL; // Define your base URL
    $partnerId = SHOPEE_API_PARTNER_ID; // Define your partner ID
    $partnerKey = SHOPEE_API_PARTNER_KEY; // Define your partner key
    $path = "/api/v2/logistics/create_shipping_document"; // API endpoint
    $timestamp = time(); // Current timestamp
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId, $partnerKey); // Generate signature

    // Prepare query parameters
    $params = [
        'access_token' => $accessToken,
        'partner_id' => (int)$partnerId,
        'shop_id' => (int)$shopId,
        'sign' => $sign,
        'timestamp' => $timestamp
    ];

    $method = 'POST'; // HTTP method
    // Build the URL with query parameters
    $url = $baseUrl . $path . '?' . http_build_query($params);

    $curl = curl_init(); // Initialize cURL

    // Set cURL options
    curl_setopt_array($curl, array(
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => $method,
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
    ));

    // If data is provided, set it as POSTFIELDS
    if ($data) {
        curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode($data));
    }

    // Execute the request and close the session
    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE); // Get HTTP response code
    curl_close($curl);

    if ($httpCode == 403) {
        // Refresh access token and retry if access is forbidden
        $responseTokens = refreshAccessToken($refreshToken, $shopId);
        if ($responseTokens[2]) {
            $accessToken = $responseTokens[0]; // New access token
            $refreshToken = $responseTokens[1]; // New refresh token
            return createShippingDocument($responseTokens[0], $responseTokens[1], $shopId, $data); // Retry with new tokens
        } else {
            return null; // Failed to refresh access token
        }
    }

    return $response; // Return the response
}


function getShippingDocumentResult(&$accessToken, &$refreshToken, $shopId, $data = array())
{
    // example data
    // $data = [
    //     "order_list" => [
    //         [
    //             "order_sn" => "210129H5A7WVSE",
    //             "package_number" => "2537708677189632625"
    //         ],
    //         [
    //             "order_sn" => "210129H5A7WVSE",
    //             "package_number" => "2537708677189633625"
    //         ],
    //         [
    //             "order_sn" => "210129H577K4AM",
    //             "package_number" => "2537712267513857625"
    //         ]
    //     ]
    // ];
    $baseUrl = SHOPEE_API_URL; // Define your base URL
    $partnerId = SHOPEE_API_PARTNER_ID; // Define your partner ID
    $partnerKey = SHOPEE_API_PARTNER_KEY; // Define your partner key
    $path = "/api/v2/logistics/get_shipping_document_result"; // API endpoint
    $timestamp = time(); // Current timestamp
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId, $partnerKey); // Generate signature

    // Prepare query parameters
    $params = [
        'access_token' => $accessToken,
        'partner_id' => (int)$partnerId,
        'shop_id' => (int)$shopId,
        'sign' => $sign,
        'timestamp' => $timestamp
    ];

    $method = 'POST'; // HTTP method
    // Build the URL with query parameters
    $url = $baseUrl . $path . '?' . http_build_query($params);

    $curl = curl_init(); // Initialize cURL

    // Set cURL options
    curl_setopt_array($curl, array(
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => $method,
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
    ));

    // If data is provided, set it as POSTFIELDS
    if ($data) {
        curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode($data));
    }

    // Execute the request and close the session
    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE); // Get HTTP response code
    curl_close($curl);

    if ($httpCode == 403) {
        // Refresh access token and retry if access is forbidden
        $responseTokens = refreshAccessToken($refreshToken, $shopId);
        if ($responseTokens[2]) {
            $accessToken = $responseTokens[0]; // New access token
            $refreshToken = $responseTokens[1]; // New refresh token
            return getShippingDocumentResult($responseTokens[0], $responseTokens[1], $shopId, $data); // Retry with new tokens
        } else {
            return null; // Failed to refresh access token
        }
    }

    return $response; // Return the response
}



function downloadShippingDocument(&$accessToken, &$refreshToken, $shopId, $data = array(), $savePath = 'shipping_document.pdf')
{
    $baseUrl = SHOPEE_API_URL; // Define your base URL
    $partnerId = SHOPEE_API_PARTNER_ID; // Define your partner ID
    $partnerKey = SHOPEE_API_PARTNER_KEY; // Define your partner key
    $path = "/api/v2/logistics/download_shipping_document"; // API endpoint
    $timestamp = time(); // Current timestamp
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId, $partnerKey);

    // Prepare query parameters
    $params = [
        'access_token' => $accessToken,
        'partner_id' => (int)$partnerId,
        'shop_id' => (int)$shopId,
        'sign' => $sign,
        'timestamp' => $timestamp
    ];

    $method = 'POST'; // HTTP method
    // Build the URL with query parameters
    $url = $baseUrl . $path . '?' . http_build_query($params);

    $curl = curl_init(); // Initialize cURL

    // Set cURL options
    curl_setopt_array($curl, array(
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => $method,
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
    ));

    // If data is provided, set it as POSTFIELDS
    if ($data) {
        curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode($data));
    }

    // Execute the request
    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE); // Get HTTP response code
    curl_close($curl);

    if ($httpCode == 403) {
        // Refresh access token and retry if access is forbidden
        $responseTokens = refreshAccessToken($refreshToken, $shopId);
        if ($responseTokens[2]) {
            $accessToken = $responseTokens[0]; // New access token
            $refreshToken = $responseTokens[1]; // New refresh token
            return downloadShippingDocument($responseTokens[0], $responseTokens[1], $shopId, $data, $savePath); // Retry with new tokens
        } else {
            return null; // Failed to refresh access token
        }
    }

    // Save the response to a file
    if ($httpCode == 200) {
        file_put_contents($savePath, $response); // Save the response to the specified path
        return "$savePath"; // Return success message
    } else {
        return "Failed to download document. HTTP Code: $httpCode"; // Return error message
    }
}



function findPickupTimeId($addressId, $addressList)
{
    $currentTime = time();
    $currentHour = date('H', $currentTime);
    $currentDate = date('Y-m-d', $currentTime);
    $tomorrowDate = date('Y-m-d', strtotime('+1 day', $currentTime));


    // Iterate through the address list to find the matching address_id
    foreach ($addressList as $address) {

        if ($address['address_id'] == $addressId) {
            // Iterate through the time slot list to find valid pickup times for the next day

            $pickupTimeId = null;

            foreach ($address['time_slot_list'] as $slot) {
                $slotDate = date('Y-m-d', $slot['date']);
                $slotTimeText = $slot['time_text'];
                list($startTime, $endTime) = explode(' - ', $slotTimeText);
                $startHour = (int)explode(':', $startTime)[0];
                $endHour = (int)explode(':', $endTime)[0];

                // Kondisi berdasarkan waktu saat ini
                if ($currentHour >= 15 && $currentHour < 24) {
                    // Ambil date besok dan time_text terpagi
                    if ($slotDate === $tomorrowDate && $startHour < 16) {
                        $pickupTimeId = $slot['pickup_time_id'];
                        break; // Hentikan pencarian setelah menemukan yang pertama
                    }
                } elseif ($currentHour >= 0 && $currentHour < 13) {
                    // Ambil date hari ini dan time_text terpagi
                    if ($slotDate === $currentDate && $startHour < 16) {
                        $pickupTimeId = $slot['pickup_time_id'];
                        break; // Hentikan pencarian setelah menemukan yang pertama
                    }
                } elseif ($currentHour >= 13 && $currentHour < 15) {
                    // Ambil date hari ini dan time_text end kurang dari jam 17:00
                    if ($slotDate === $currentDate && $endHour < 17) {
                        $pickupTimeId = $slot['pickup_time_id'];
                        break; // Hentikan pencarian setelah menemukan yang pertama
                    }
                }
            }

            // Jika ada time slot yang valid, ambil pickup_time_id terakhir
            $pickupTimeId = end($address['time_slot_list'])['pickup_time_id'];
        }
    }

    return $pickupTimeId;
}


function downloadAwb(&$access_token, &$refresh_token, $orderSN, $shop_id)
{
    $orderListAirBill = ["order_list" => []];
    $orderListDownload = ["order_list" => []];
    $getTrackingNumber = getTrackingNumber($access_token, $refresh_token, $orderSN, $shop_id);

    // Decode respons JSON
    $response = json_decode($getTrackingNumber, true); // Menggunakan true untuk mendapatkan array asosiatif

    // Periksa apakah respons valid dan memiliki tracking_number
    if (isset($response['response']['tracking_number'])) {
        $orderListAirBill["order_list"][] = [
            "order_sn" => $orderSN, // Menggunakan $order langsung
            "tracking_number" => $response['response']['tracking_number'],
        ];
        $orderListDownload["order_list"][] = [
            "order_sn" => $orderSN,
        ];

        createShippingDocument($access_token, $refresh_token, $shop_id, $orderListAirBill);

        getShippingDocumentParameter($access_token, $refresh_token, $shop_id, $orderListAirBill);

        getShippingDocumentResult($access_token, $refresh_token, $shop_id, $orderListAirBill);

        $nameDownload = 'storage/awb/awb-' . $orderSN . '.pdf';
        $url = downloadShippingDocument($access_token, $refresh_token, $shop_id, $orderListDownload, $nameDownload);

        return $url;
    }

    return null;
}

function updateOrderAWB($orderSn, $zipcode, $loc, $awbUrl)
{
    global $db;
    // SQL query to update the order details
    $sql = "
        UPDATE orders 
        SET 
            zipcode = '$zipcode',
            loc = '$loc',
            awb_url = '$awbUrl'
        WHERE 
            order_sn = '$orderSn';
    ";

    // Execute the query
    if ($db->query($sql)) {
        return true;
    } else {
        return false;
    }
}
function repositoryOrdersPackingQueue($sn_order, $zipcode, $awb_url, $stok_failed, $resi, $platform_id)
{
    global $db; // Pastikan $db adalah instance dari `mysqli`
    try {
        // Query insert
        $query = "INSERT INTO orders_packing_queue (order_sn, zipcode, awb_url, stok_failed, resi, platform_id) 
                  VALUES (?, ?, ?, ?, ?, ?)";
        $stmt = $db->prepare($query);

        if ($stmt === false) {
            throw new Exception("Failed to prepare statement: " . $db->error);
        }

        // Bind parameter
        $stmt->bind_param("sssisi", $sn_order, $zipcode, $awb_url, $stok_failed, $resi, $platform_id);

        // Eksekusi query
        if ($stmt->execute()) {
            // echo "Data berhasil disisipkan ke tabel orders_packing_queue.";
            return true;
        } else {
            return false;
        }

        // Tutup statement
        $stmt->close();

        if ($zipcode == '61256') {
            $warehouse = "Sidoarjo";
        } elseif ($zipcode == '15151') {
            $warehouse = "Jakarta";
        } elseif ($zipcode == '61256') {
            $warehouse = "Makasar";
        } elseif ($zipcode == '50248') {
            $warehouse = "semarang";
        }

        updateOrderAWB(
            $sn_order,
            $zipcode,
            $warehouse,
            $awb_url
        );
        return true;
    } catch (Exception $e) {
        // echo "Error: " . $e->getMessage();

        return false;
    }
}
function makeShopeeApiRequest($path, $params, &$accessToken, &$refreshToken, $shopId)
{
    $baseUrl = SHOPEE_API_URL; // Base URL API Shopee
    $partnerId = SHOPEE_API_PARTNER_ID;
    $partnerKey = SHOPEE_API_PARTNER_KEY;
    $timestamp = time();

    // Generate signature
    $sign = generateShopeeSignature($partnerId, $path, $timestamp, $accessToken, $shopId, $partnerKey);

    // Tambahkan parameter wajib ke query
    $params['access_token'] = $accessToken;
    $params['partner_id'] = $partnerId;
    $params['shop_id'] = $shopId;
    $params['timestamp'] = $timestamp;
    $params['sign'] = $sign;

    // Buat URL lengkap
    $url = $baseUrl . $path . '?' . http_build_query($params);

    // Inisialisasi cURL
    $curl = curl_init();
    curl_setopt_array($curl, array(
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 10,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'GET',
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json',
        ),
    ));

    // Eksekusi permintaan
    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
    curl_close($curl);

    // Periksa HTTP Code
    if ($httpCode === 403) {
        // Refresh token jika diperlukan
        $tokens = refreshAccessToken($refreshToken, $shopId);
        if ($tokens[2]) { // Jika refresh token berhasil
            $accessToken = $tokens[0];
            $refreshToken = $tokens[1];
            // Ulangi permintaan dengan token baru
            return makeShopeeApiRequest($path, $params, $accessToken, $refreshToken, $partnerId, $partnerKey, $shopId);
        } else {
            return null; // Gagal mendapatkan token baru
        }
    } elseif ($httpCode === 200) {
        return $response; // Permintaan berhasil
    } else {
        return null; // Terjadi error lain
    }
}
function getWarehouseDetail(&$accessToken, &$refreshToken, $shopId)
{
    $path = "/api/v2/shop/get_warehouse_detail";
    $params = [
        'region' => 'ID',
    ];

    return makeShopeeApiRequest($path, $params, $accessToken, $refreshToken, $shopId);
}

function findWarehouse($response, $criteria, $value)
{
    // Iterasi melalui array respons untuk mencari kecocokan
    foreach ($response as $warehouse) {
        if (isset($warehouse[$criteria]) && $warehouse[$criteria] == $value) {
            return $warehouse; // Kembalikan objek jika ditemukan
        }
    }
    return null; // Kembalikan null jika tidak ditemukan
}

function getDataOrderByOrderSn($orderSn)
{
    global $db;
    // Persiapkan query SQL
    $query = "SELECT * FROM orders WHERE order_sn = ?";
    $stmt = $db->prepare($query);

    // Bind parameter
    $stmt->bind_param("s", $orderSn);

    // Eksekusi query
    $stmt->execute();

    // Ambil hasil
    $result = $stmt->get_result();

    // Cek apakah data ditemukan
    if ($result->num_rows > 0) {
        $orderDetails = $result->fetch_assoc();
        return $orderDetails; // Mengembalikan array dengan semua kolom
    } else {
        return null; // Tidak ada data ditemukan
    }
}

function moveOrdersToAwb($orderSn, $resi, $zipcode, $platformName)
{
    global $db;
    try {
        // Mulai transaksi
        $db->begin_transaction();

        // Ambil data dari tabel `orders` berdasarkan `order_sn`
        $queryOrder = "SELECT orders.*, shop.shop_name FROM orders JOIN shop ON orders.shop_id = shop.shop_id WHERE order_sn = ?";
        $stmtOrder = $db->prepare($queryOrder);
        $stmtOrder->bind_param("s", $orderSn);
        $stmtOrder->execute();
        $order = $stmtOrder->get_result()->fetch_assoc();
        if (!$order) {
            throw new Exception("Order not found for order_sn: $orderSn");
        }

        // Ambil data dari tabel `order_detail` berdasarkan `order_sn`
        $queryDetail = "SELECT * FROM order_detail WHERE order_sn = ? AND deleted_at IS NULL ";
        echo $queryDetail;
        $stmtDetail = $db->prepare($queryDetail);
        $stmtDetail->bind_param("s", $order['order_sn']);
        $stmtDetail->execute();
        $resultDetail = $stmtDetail->get_result();

        while ($detail = $resultDetail->fetch_assoc()) {
            // Persiapkan data untuk dimasukkan ke tabel `awb`
            $tanggalPesenanDibuat = $db->escape($order['create_time']);
            $invoice = $db->escape($order['order_sn']);
            $namaToko = $db->escape($platformName . " - " . $order['shop_name']);
            $partNumber = $db->escape(strlen($detail['item_sku']) < strlen($detail['model_sku']) ? $detail['model_sku'] : $detail['item_sku']);
            if ($platformName == "lazada") {
                $partNumber = $db->escape($detail['model_sku']);
            }
            $productName = $db->escape($detail['item_name']);
            $quantity = (int)$detail['model_quantity_purchased'];
            $unitPrice = (float)$detail['model_discounted_price'];
            $totalPrice = $unitPrice * $quantity;
            $kurir = $db->escape($order['shipping_carrier']);
            $dateScan = date('Y-m-d H:i:s');
            $batasPengiriman = date('Y-m-d') . ' 23:59:00';
            $uniqCode = $db->escape($resi . '-' . $partNumber . '-' . $quantity);
            $loc = getWarehouseByZipcode($zipcode);

            // Masukkan data ke tabel `awb`
            $queryAwb = "
                INSERT INTO awb (
                    tanggal_pesenan_dibuat, invoice, nama_toko, part_number, product_name, 
                    quantity, unit_price, total_price, kurir, resi, date_scan, 
                    batas_pengiriman, uniq_code, waktu_proses, loc
                ) VALUES (
                    '$tanggalPesenanDibuat', '$invoice', '$namaToko', '$partNumber', '$productName', 
                    $quantity, $unitPrice, $totalPrice, '$kurir', '$resi', '$dateScan', 
                    '$batasPengiriman', '$uniqCode', NOW(), '$loc'
                )
            ";
            echo $queryAwb;
            $db->query($queryAwb);
        }

        // Commit transaksi
        $db->commit();
        return 0;
    } catch (Exception $e) {
        // Rollback transaksi jika terjadi kesalahan
        $db->rollback();
        error_log("Error in moveOrdersToAwb: " . $e->getMessage());
        return 1;
    }
}

// if ($zipcode == '61256') {
//     //sidoarjo
//     $updateStok = updateStokSidoarjo($partNumber, $quantity, $resi, $order['order_sn']);
// } else if ($zipcode == '15151') {
//     //jakarta (tangerang)
//     $updateStok = updateStokJakarta($partNumber, $quantity, $resi, $order['order_sn']);
// } else if ($zipcode == '90223') {
//     //makasar
//     $updateStok = updateStokMakasar($partNumber, $quantity, $resi, $order['order_sn']);
// }


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

    $sql = "INSERT INTO sales (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 table
function update_product_qty_online_products($part_number, $quantity)
{
    global $db;
    $sql = "UPDATE online_products SET quantity = quantity - '{$quantity}' WHERE part_number = '{$part_number}'";
    return $db->query($sql);
}


// Function to find product by part number in online_products table
function find_product_by_part_number_online($part_number)
{
    global $db;
    $part_number = $db->escape($part_number); // Escape input to prevent SQL injection
    $sql = "SELECT * FROM online_products WHERE part_number = '{$part_number}' LIMIT 1";
    $result = $db->query($sql);

    if ($result) {
        return $result->fetch_assoc(); // Fetch single result as associative array
    } else {
        return null; // Return null if no product found
    }
}
function updateStokSidoarjo($part_number, $quantity, $awb_resi, $order_sn)
{
    $online_product = find_product_by_part_number_online($part_number);

    if ($online_product) {
        $product_id = $online_product['id'];
        $categorie_id = $online_product['categorie_id'];
        $remark = "Scan {$awb_resi}";  // Update remark with resi number and "Scan"
        $loc = "Etalase Online";  // Adjust location as necessary

        // Insert into sales table
        if (insert_into_sales($product_id, $part_number, $quantity, $categorie_id, $remark, $loc)) {
            // Update online_products table
            if (!update_product_qty_online_products($part_number, $quantity)) {
                // error_log("Failed to update product quantity for part number: {$part_number} - {$order_sn}");
                return false; // This indicates the quantity update failed
            } else {
                return true; // Successful update
            }
        } else {
            // error_log("Failed to insert into sales for product ID: {$product_id} - {$order_sn}");
            return false; // This indicates the sales insertion failed
        }
    } else {
        // error_log("Product not found for part number: {$part_number} - {$order_sn}");
        return false; // This indicates the product was not found
    }
}

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

    $sql = "INSERT INTO sales_makassar (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_makassar table
function update_product_qty_online_products_makassar($part_number, $quantity)
{
    global $db;
    $sql = "UPDATE online_products_makassar SET quantity_boked = quantity_boked - '{$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_makassar table
function find_product_by_part_number_online_makassar($part_number)
{
    global $db;
    $part_number = $db->escape($part_number);
    $sql = "SELECT * FROM online_products_makassar WHERE part_number = '{$part_number}' LIMIT 1";
    $result = $db->query($sql);
    return ($result) ? $result->fetch_assoc() : null;
}


function updateStokMakasar($part_number, $quantity, $awb_resi, $order_sn)
{
    $online_product = find_product_by_part_number_online_makassar($part_number);
    if ($online_product) {
        $product_id = $online_product['id'];
        $categorie_id = $online_product['categorie_id'];
        $remark = "Scan {$awb_resi}";
        $loc = "Makassar";

        // Insert into sales_makassar and update stock regardless of stock availability
        if (insert_into_sales_makassar($product_id, $part_number, $quantity, $categorie_id, $remark, $loc)) {
            if (!update_product_qty_online_products_makassar($part_number, $quantity)) {
                return false;
            } else {
                return true;
            }
        } else {
            return false;
        }
    } else {
        return false;
    }
}

// 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 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;
}
function updateStokJakarta($part_number, $quantity, $awb_resi, $order_sn)
{
    $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)) {
                return false;
            } else {
                return true;
            }
        } else {
            return false;
        }
    } else {
        return false;
    }
}

function updateOrderQueueProcessedByID($ids = array())
{
    global $db;
    $user = current_user();
    redisDelete('data_packing_queue:data_count_cache:' . $user['user_level']);
    $cacheQueueKey = 'data_packing_queue:' . $user['user_level'];
    redisDelete($cacheQueueKey);
    $cacheQueueKey = 'data_packing_queue:1';
    redisDelete($cacheQueueKey);
    $cacheQueueKey = 'data_packing_queue:2';
    redisDelete($cacheQueueKey);
    // Pastikan parameter $ids adalah array dan tidak kosong
    if (!is_array($ids) || empty($ids)) {
        return false;
    }

    // Gabungkan ID menjadi string untuk digunakan dalam klausa IN
    $idsString = "'" . implode("','", $ids) . "'";

    // Buat query SQL untuk memperbarui kolom processed
    $sql = "
        UPDATE orders_packing_queue 
        SET processed = 1 
        WHERE id IN ($idsString)
        AND processed = 0;";

    // Eksekusi query
    if ($db->query($sql)) {
        return true;
    } else {
        return false;
    }
}


function updateOrderQueueProcessedByOrderSN($orderSN = array())
{
    global $db;

    // Pastikan parameter $ids adalah array dan tidak kosong
    if (!is_array($orderSN) || empty($orderSN)) {
        return false;
    }

    // Gabungkan ID menjadi string untuk digunakan dalam klausa IN
    $idsString = "'" . implode("','", $orderSN) . "'";

    // Buat query SQL untuk memperbarui kolom processed
    $sql = "
        UPDATE orders_packing_queue 
        SET processed = 1 
        WHERE order_sn IN ($idsString)
        AND processed = 0;";

    // Eksekusi query
    if ($db->query($sql)) {
        return true;
    } else {
        return false;
    }
}



function updateOrderQueueProcessedLazada($zipcode)
{
    global $db;

    // Prepare the SQL query to update the processed field to 1
    $sql = "
        UPDATE orders_packing_queue 
        SET processed = 1 
        WHERE processed = 0 
        AND zipcode = '$zipcode'
        AND NOT resi = ''
        AND platform_id = 4 
        ORDER BY id ASC 
        LIMIT 100;
    ";

    // Execute the query
    if ($db->query($sql)) {
        return true;
    } else {
        return false;
    }
}

function updateOrderQueueProcessed($zipcode)
{
    global $db;

    // Prepare the SQL query to update the processed field to 1
    $sql = "
        UPDATE orders_packing_queue 
        SET processed = 1 
        WHERE processed = 0 
        AND zipcode = '$zipcode'
        AND NOT resi = ''
        AND NOT platform_id = 4 
        ORDER BY id ASC 
        LIMIT 100;
    ";

    // Execute the query
    if ($db->query($sql)) {
        return true;
    } else {
        return false;
    }
}

function insertPackingListAndUpdateOrders(array $packingListData, array $orderSNList)
{
    global $db;
    $user = current_user();
    $packingListId = 0;
    // Mulai transaksi
    $db->begin_transaction();

    try {
        // Insert ke tabel packing_list
        $sqlInsertPackingList = "INSERT INTO packing_list (code, zipcode, file_url, created_by) VALUES (?, ?, ?, ?)";
        $stmtInsert = $db->prepare($sqlInsertPackingList);
        $stmtInsert->bind_param("ssss", $packingListData['code'], $packingListData['zipcode'], $packingListData['file_url'], $user['name']);
        $stmtInsert->execute();

        // Ambil id dari packing_list yang baru saja disisipkan
        $packingListId = $stmtInsert->insert_id;

        // Update tabel orders dengan id_packing_list
        $placeholders = implode(',', array_fill(0, count($orderSNList), '?'));
        $sqlUpdateOrders = "UPDATE orders SET id_packing_list = ? WHERE id IN ($placeholders)";
        $stmtUpdate = $db->prepare($sqlUpdateOrders);

        // Bind id_packing_list dan order_sn
        $params = array_merge([$packingListId], $orderSNList);
        $stmtUpdate->bind_param(str_repeat('s', count($params)), ...$params);
        $stmtUpdate->execute();

        // Commit transaksi
        $db->commit();

        return $packingListId;
    } catch (Exception $e) {
        // Rollback transaksi jika terjadi kesalahan
        $db->rollback();
        return 0;
    }
}


function insertPackingListAndUpdateOrdersLazada(array $packingListData, array $orderSNList)
{
    global $db;
    $user = current_user();
    $packingListId = 0;
    // Mulai transaksi
    $db->begin_transaction();

    try {
        // Insert ke tabel packing_list
        $sqlInsertPackingList = "INSERT INTO packing_list (code, zipcode, file_url, created_by)
VALUES (?, ?, ?, ?)";
        $stmtInsert = $db->prepare($sqlInsertPackingList);
        $stmtInsert->bind_param("ssss", $packingListData['code'], $packingListData['zipcode'], $packingListData['file_url'], remove_junk(ucfirst($user['name'])));
        $stmtInsert->execute();

        // Ambil id dari packing_list yang baru saja disisipkan
        $packingListId = $stmtInsert->insert_id;

        // Update tabel orders dengan id_packing_list
        $placeholders = implode(',', array_fill(0, count($orderSNList), '?'));
        $sqlUpdateOrders = "UPDATE orders SET id_packing_list = ? WHERE id IN ($placeholders)";
        $stmtUpdate = $db->prepare($sqlUpdateOrders);

        // Bind id_packing_list dan order_sn
        $params = array_merge([$packingListId], $orderSNList);
        $stmtUpdate->bind_param(str_repeat('s', count($params)), ...$params);
        $stmtUpdate->execute();

        // Commit transaksi
        $db->commit();

        return $packingListId;
    } catch (Exception $e) {
        // Rollback transaksi jika terjadi kesalahan
        $db->rollback();
        return 0;
    }
}

function generateCode($prefix = 'PACK', $length = 8)
{
    // Format tanggal untuk resi
    $date = date('Ymd'); // TahunBulanTanggal

    // Generate angka acak
    $randomNumber = substr(str_shuffle('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 0, $length);

    // Gabungkan semuanya
    return $prefix . $date . $randomNumber;
}

function zipFilePacking($id_packing_list, $zipFilename)
{
    global $db;
    $rootPath = $_SERVER['DOCUMENT_ROOT'];
    $query = "SELECT file_url FROM packing_list WHERE id = " . (int)$id_packing_list;
    $result = $db->query($query);
    $validFiles = [];
    while ($row = $result->fetch_assoc()) {
        if (file_exists($rootPath . "/" . $row['file_url'])) {
            $validFiles[] = $rootPath . "/" . $row['file_url'];
        }
    }

    $result = $db->query("SELECT opq.awb_url FROM orders o join orders_packing_queue opq on o.order_sn = opq.order_sn  where id_packing_list = " . (int)$id_packing_list);
    while ($row = $result->fetch_assoc()) {
        if (file_exists($rootPath . "/" . $row['awb_url'])) {
            $validFiles[] = $rootPath . "/" . $row['awb_url'];
        }
    }



    // Jika tidak ada file valid
    if (empty($validFiles)) {
        http_response_code(404);
        echo json_encode(['error' => 'Tidak ada file valid untuk diunduh']);
        exit;
    }


    // Buat file ZIP
    $zip = new ZipArchive();
    if ($zip->open($zipFilename, ZipArchive::CREATE) !== TRUE) {
        http_response_code(500);
        echo json_encode(['error' => 'Gagal membuat file ZIP']);
        exit;
    }

    // Tambahkan file ke ZIP
    foreach ($validFiles as $file) {
        $zip->addFile($file, basename($file));
    }

    // Tutup file ZIP
    $zip->close();

    // Kirim file ZIP ke klien
    header('Content-Type: application/zip');
    header('Content-Disposition: attachment; filename="' . $zipFilename . '"');
    header('Content-Length: ' . filesize($zipFilename));

    // Baca dan kirim file ZIP
    readfile($zipFilename);

    // Hapus file ZIP dari server setelah dikirim
    unlink($zipFilename);
}

function executeCurl($baseUrl, $endpoint, $queryParams = [], $headers = [], $method = 'GET', $body = null, $orderSn = "")
{
    // Build the full URL
    $url = rtrim($baseUrl, '/') . $endpoint;  // This handles potential double slashes

    // Append query parameters to the URL if they exist
    if (!empty($queryParams)) {
        $url .= '?' . http_build_query($queryParams);
    }

    // Initialize cURL
    $ch = curl_init();

    // Set common cURL options
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

    // Disable SSL verification (for development only)
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);

    // Set HTTP method
    switch (strtoupper($method)) {
        case 'POST':
            curl_setopt($ch, CURLOPT_POST, true);
            if ($body !== null) {
                curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($body));
            }
            break;
        case 'PUT':
            curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'PUT');
            if ($body !== null) {
                curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($body));
            }
            break;
        case 'DELETE':
            curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'DELETE');
            if ($body !== null) {
                curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($body));
            }
            break;
        case 'GET':
        default:
            curl_setopt($ch, CURLOPT_HTTPGET, true);
            break;
    }

    // Set headers if provided
    if (!empty($headers)) {
        curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
    }

    // Start the timer to measure response time
    $startTime = microtime(true);

    // Execute the cURL request
    $response = curl_exec($ch);

    // Stop the timer
    $endTime = microtime(true);
    $responseTime = $endTime - $startTime;

    // Check for cURL errors
    if (curl_errno($ch)) {
        echo 'cURL Error: ' . curl_error($ch);
        curl_close($ch);
        return null;
    }

    // Get the response status code
    $responseStatus = curl_getinfo($ch, CURLINFO_HTTP_CODE);

    // Close cURL
    curl_close($ch);

    // Decode the response body
    $decodedResponse = json_decode($response, true);

    // Log the request, response, and timing into the database
    logApiRequest($url, $method, $headers, $body, $responseStatus, $response, $responseTime, $orderSn);

    return $decodedResponse;
}


function mergePDFFile($validFiles = [])
{
    global $db;
    $rootPath = $_SERVER['DOCUMENT_ROOT'];
    $Filename = 'merge_file.pdf';

    $mergedPdfPath = $rootPath . "/" . $Filename;
    $cmd = "gs -q -dNOPAUSE -dBATCH -sDEVICE=pdfwrite -sOutputFile=" . $mergedPdfPath . " " . implode(" ", $validFiles);
    shell_exec($cmd);
    if (ob_get_level() == 0) {
        ob_start(); // Start buffering if not already started
    }

    // Send the merged PDF file to the client
    header('Content-Type: application/pdf');
    header('Content-Disposition: attachment; filename="' . basename($mergedPdfPath) . '"');
    header('Content-Length: ' . filesize($mergedPdfPath));

    // Read and send the merged PDF file
    readfile($mergedPdfPath);

    // Clean up: delete the merged PDF file from the server
    unlink($mergedPdfPath);
}


function mergePDFFilePacking($id_packing_list, $Filename)
{
    global $db;
    $rootPath = $_SERVER['DOCUMENT_ROOT'];
    $query = "SELECT file_url FROM packing_list WHERE id = " . (int)$id_packing_list;
    $result = $db->query($query);
    $validFiles = [];

    // Collect valid PDF files
    while ($row = $result->fetch_assoc()) {
        if (file_exists($rootPath . "/" . $row['file_url'])) {
            $validFiles[] = $rootPath . "/" . $row['file_url'];
        }
    }

    $result = $db->query("SELECT opq.awb_url FROM orders_packing_queue opq join orders o ON o.order_sn = opq.order_sn WHERE id_packing_list = " . (int)$id_packing_list);
    while ($row = $result->fetch_assoc()) {
        if (isPdfValid($rootPath . "/" . $row['awb_url'])) {
            $validFiles[] = $rootPath . "/" . $row['awb_url'];
        }
    }


    // If no valid files
    if (empty($validFiles)) {
        http_response_code(404);
        echo json_encode(['error' => 'Tidak ada file valid untuk diunduh']);
        exit;
    }

    $mergedPdfPath = $rootPath . "/" . $Filename;
    $cmd = "gs -q -dNOPAUSE -dBATCH -sDEVICE=pdfwrite -sOutputFile=" . $mergedPdfPath . " " . implode(" ", $validFiles);
    shell_exec($cmd);
    if (ob_get_level() == 0) {
        ob_start(); // Start buffering if not already started
    }

    // Send the merged PDF file to the client
    header('Content-Type: application/pdf');
    header('Content-Disposition: attachment; filename="' . basename($mergedPdfPath) . '"');
    header('Content-Length: ' . filesize($mergedPdfPath));

    // Read and send the merged PDF file
    readfile($mergedPdfPath);

    // Clean up: delete the merged PDF file from the server
    unlink($mergedPdfPath);
}

function isPdfValid($filePath)
{
    if (!file_exists($filePath)) {
        return false;
    }
    $pdfFile = file($filePath);
    $end = trim(end($pdfFile));
    if (substr($pdfFile[0], 0, 4) == '%PDF' && substr($end, -5) == '%%EOF') {
        return true;
    } else {
        return false;
    }
}


function checkPackingDeadline($zipcode, $orderDate)
{
    // Mengatur zona waktu berdasarkan kode pos
    if ($zipcode == '90223') {
        $time = '14:00';
    } else {
        $time = '15:00';
    }

    // Mendapatkan tanggal hari ini
    $today = date('Y-m-d');
    $formattedOrderDate = date('Y-m-d', strtotime($orderDate));
    $formattedOrderTime = date('H:i', strtotime($orderDate));

    // Memeriksa apakah tanggal order adalah hari ini dan waktu saat ini sudah lewat jam 15.00
    if ($formattedOrderDate === $today) {
        if ($formattedOrderTime <= $time) {
            return '<span class="btn btn-sm btn-danger">hari ini</span>';
        } else {
            return '<span class="btn btn-sm btn-warning">bisa besok</span>';
        }
    } else {
        return '<span class="btn btn-sm btn-danger">hari ini</span>';
    }
}

function getWarehouseInfo($zipcode)
{
    global $db;
    $warehouses = [];
    $query = "SELECT * FROM warehouse";
    $result = $db->query($query);
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $warehouses[$row['zipcode']] = [
                0 => $row['btn_class'],
                1 => $row['warehouse_name']
            ];
        }
    }

    return $warehouses[$zipcode] ?? ['btn-default', $zipcode];
}

function getWarehouseByZipcode($zipcode)
{
    global $db;
    $query = "SELECT * FROM warehouse WHERE zipcode = ?";
    $stmt = $db->prepare($query);
    $stmt->bind_param("s", $zipcode);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        return $row['warehouse_name']; // or return any other field you need
    } else {
        return $zipcode; // If no warehouse is found, return the zipcode
    }
}

function getWarehouseByID($id)
{
    global $db;
    $query = "SELECT * FROM warehouse WHERE warehouse_id = ?";
    $stmt = $db->prepare($query);
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        return $row['warehouse_name']; // or return any other field you need
    } else {
        return $id; // If no warehouse is found, return the zipcode
    }
}

function getWarehouseByTable($table)
{
    global $db;
    $query = "SELECT warehouse_id, warehouse_name FROM warehouse WHERE tables = ?";
    $stmt = $db->prepare($query);
    $stmt->bind_param("s", $table);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        return $row; // or return any other field you need
    } else {
        return $table; // If no warehouse is found, return the zipcode
    }
}

function getWarehouseByTableWH($table)
{
    global $db;
    $query = "SELECT warehouse_id, warehouse_name FROM warehouse WHERE tables_wh = ?";
    $stmt = $db->prepare($query);
    $stmt->bind_param("s", $table);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        return $row; // or return any other field you need
    } else {
        return $table; // If no warehouse is found, return the zipcode
    }
}


function downloadFailedAwb($orderSN)
{
    global $db;
    $result = $db->query("SELECT opq.awb_url, s.access_token, s.shop_id, s.refresh_token, o.order_sn FROM orders o JOIN orders_packing_queue opq ON o.order_sn = opq.order_sn JOIN shop s ON o.shop_id = s.shop_id WHERE order_sn = " . $orderSN);
    $row = $result->fetch_assoc();
    $shop_id = $row['shop_id'];
    $access_token = $row['access_token'];
    $refresh_token = $row['refresh_token'];
    $orderSN = $row['order_sn'];
    downloadAwb($access_token, $refresh_token, $orderSN, $shop_id);
}

function generateBarcode($code)
{
    // Inisialisasi generator barcode
    $generator = new BarcodeGeneratorPNG();

    try {
        // Membuat barcode dalam format PNG
        $barcode = $generator->getBarcode($code, $generator::TYPE_CODE_128);

        // Menyimpan file barcode
        $filename = 'storage/barcode_' . $code . '.png';
        file_put_contents($filename, $barcode);

        return [
            'status' => 'success',
            'message' => 'Barcode generated successfully',
            'filename' => $filename,
            'base64' => base64_encode($barcode)
        ];
    } catch (Exception $e) {
        return [
            'status' => 'error',
            'message' => $e->getMessage()
        ];
    }
}

function deleteBarcode($filename)
{
    // Check if the file exists before attempting to delete
    if (file_exists($filename)) {
        // Try to delete the file and return true if successful
        return unlink($filename);
    }

    // Return false if the file doesn't exist
    return false;
}

function generatePackingListPDFByOrderSN($orderSn, $code, $fileName = 'packing_list.pdf')
{
    global $db;

    // Step 3: Convert the order string into an array
    $orderArray = explode(",", $orderSn);

    // Step 4: Ensure the IDs are properly quoted strings for SQL
    $orderArray = array_map(function ($item) {
        return "'" . $item . "'";  // Wrap each item in single quotes
    }, $orderArray);

    // Create the IN clause with properly quoted values
    $inClause = implode(",", $orderArray);

    $outputDir = $_SERVER['DOCUMENT_ROOT'] . '/storage/packing';
    $outputSave = 'storage/packing/' . $fileName;
    $sql = "SELECT CASE WHEN i.model_sku IS NOT NULL AND i.model_sku != '' THEN i.model_sku ELSE i.item_sku END AS sku, SUM(i.model_quantity_purchased) AS quantity, GROUP_CONCAT(opq.resi SEPARATOR ', ') AS resi, GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) AS order_ids FROM order_detail i JOIN orders o ON i.order_id = o.id JOIN(SELECT * FROM orders_packing_queue WHERE order_sn IN ($inClause)) opq ON opq.order_sn = o.order_sn WHERE i.deleted_at is NULL GROUP BY sku;";
    $data = $db->query($sql);

    // Pastikan folder output ada
    if (!is_dir($outputDir)) {
        mkdir($outputDir, 0755, true);
    }

    // Lokasi file PDF yang akan disimpan
    $filePath = $outputDir . '/' . $fileName;
    $barcode = generateBarcode($code);
    // Inisialisasi TCPDF
    $pdf = new TCPDF('P', 'mm', 'A6', true, 'UTF-8', false);

    // Set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('Your Name');
    $pdf->SetTitle('Packing List');
    $pdf->SetSubject('Packing List');
    $pdf->SetKeywords('Packing, List, TCPDF');

    // Remove default header and footer
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);

    // Set margins
    $pdf->SetMargins(0, 0, 0);
    $pdf->SetAutoPageBreak(TRUE, 0);

    // Add a page
    $pdf->AddPage();

    // Template HTML for the table
    $html = '<style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th {
            font-size: 12px;
            text-align: center;
        }
        td {
            font-size: 14px;
            text-align: center;
            padding: 5px;
        }
        .barcode {
            text-align: center;
        }
    </style>';

    // Add header information
    $html .= '<table>
        <tr>
            <td style="font-size:9px; text-align:left; width:20%;">' . date("d-m-Y") . '</td>
            <td style="font-size:14px; text-align:center; width:60%;">' . $code . '<br>
                <img src="data:image/png;base64,' . $barcode['base64'] . '">
            </td>
            <td style="font-size:9px; text-align:right; width:20%;">' . date('H:i:s') .
        '</td>
        </tr>
    </table><br>';

    // Add table header
    $html .= '<table border="1">
        <thead>
            <tr>
                <th style="width:50%;">SKU</th>
                <th style="width:10%;">Q</th>
                <th style="width:40%;">Resi</th>
            </tr>
        </thead>
        <tbody>';

    $orderIds = [];

    // Add table rows dynamically
    foreach ($data as $row) {
        $sku = htmlspecialchars($row["sku"]);
        $quantity = $row["quantity"];
        $resiList = explode(",", $row["resi"]);
        $resiFormatted = implode('<br>', $resiList);
        $orderList = explode(",", $row["order_ids"]);

        $html .= '<tr>';
        $html .= '<td style="width:50%; text-align:left;">' . $sku . '</td>';
        $html .= '<td style="width:10%;">' . $quantity . '</td>';
        $html .= '<td style="width:40%; text-align:left; word-wrap:break-word; font-size:11;">' . $resiFormatted . '</td>';
        $html .= '</tr>';

        $orderIds = array_merge($orderIds, $orderList);
    }

    $html .= '</tbody></table>';

    // Write HTML to PDF
    $pdf->writeHTML($html, true, false, true, false, '');

    // Simpan file ke lokasi yang ditentukan
    if (!$pdf->Output($filePath, 'F')) {
        return ['file_url' => $outputSave, 'order_ids' => array_unique($orderIds)];
    }
}

/**
 * Generate AWB Custom import excel
 * 
 * @param array $data
 * @return array
 */
function generateAwbCustomImport($data)
{
    global $db;

    $pathDir = $_SERVER['DOCUMENT_ROOT'] . '/storage/awb-urgent';
    // Pastikan folder output ada
    if (!is_dir($pathDir)) {
        mkdir($pathDir, 0755, true);
    }

    $files = scandir($pathDir) ?: [];

    // Store file exists
    $fileExists = !empty($files) ? array_filter($files, function ($file) use ($pathDir) {
        if ($file === '.' || $file === '..') {
            return false;
        }
        $filePath = $pathDir . DIRECTORY_SEPARATOR . $file;

        // Check if it is a file (not a directory)
        if (!is_file($filePath)) {
            return false;
        }

        // Check if the file has no extension
        if (pathinfo($file, PATHINFO_EXTENSION) !== '') {
            return false;
        }

        return true;
    }) : [];

    $generated = [];
    foreach ($data as $orderId => $d) {


        // if exist and not update ignore
        if (in_array($orderId, $fileExists) && !empty($d['update'])) {
            continue;
        }
        $sql = "UPDATE awb SET urgent = 1, loc = '" . $d['city'] . "' WHERE resi = '" . $d['resi'] . "'";

        $db->query($sql);


        // Lokasi file PDF yang akan disimpan
        $filePath = $pathDir . "/{$d['resi']}.pdf";
        $barcode = generateBarcode($d['resi']);
        // Inisialisasi TCPDF
        $pdf = new TCPDF('P', 'mm', 'A6', true, 'UTF-8', false);

        // Set document information
        $pdf->SetCreator(PDF_CREATOR);
        $pdf->SetAuthor('Wiselie System');
        $pdf->SetTitle('AWB Custom');
        $pdf->SetSubject('AWB');
        $pdf->SetKeywords('AWB, TCPDF');

        // Remove default header and footer
        $pdf->setPrintHeader(false);
        $pdf->setPrintFooter(false);

        // Set margins
        $pdf->SetMargins(0, 0, 0);
        $pdf->SetAutoPageBreak(TRUE, 0);

        // Add a page
        $pdf->AddPage();

        // Template HTML for the table
        $html = '<style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th {
            font-size: 12px;
            text-align: center;
        }
        td {
            font-size: 14px;
            text-align: center;
            padding: 5px;
        }
        .barcode {
            text-align: center;
        }
    </style>';

        // Add header information
        $html .= '<table>
        <tr>
            <td style="font-size:9px; text-align:left; width:20%;">' . date("d-m-Y") . '</td>
            <td style="font-size:14px; text-align:center; width:60%;">' . $d['resi'] . '<br>
                <img src="data:image/png;base64,' . $barcode['base64'] . '">
            </td>
            <td style="font-size:9px; text-align:right; width:20%;">' . date('H:i:s') .
            '</td>
        </tr>
        <tr>
            <td style="font-size:9px; text-align:center; width:100%;">' . $d['city'] . '</td>
        </tr>
    </table><br>';

        // Add table header
        $html .= '<table border="1">
        <thead>
            <tr>
                <th style="width:80%;">SKU</th>
                <th style="width:20%;">Q</th>
            </tr>
        </thead>
        <tbody>';

        // Add table rows dynamically
        foreach ($d['sku'] as $sku) {
            $sku_name = htmlspecialchars($sku["sku_name"]);
            $quantity = $sku["qty"];

            $html .= '<tr>';
            $html .= '<td style="width:80%; text-align:left;">' . $sku_name . '</td>';
            $html .= '<td style="width:20%;">' . $quantity . '</td>';
            $html .= '</tr>';
        }

        $html .= '</tbody></table>';

        // Write HTML to PDF
        $pdf->writeHTML($html, true, false, true, false, '');

        // save file
        $pdf->Output($filePath, 'F');
        $generated[] = $filePath;
    }

    return $generated;
}

function generatePackingListPDFLazada($zipcode, $code, $fileName = 'packing_list.pdf')
{
    global $db;

    $outputDir = $_SERVER['DOCUMENT_ROOT'] . '/storage/packing';
    $outputSave = 'storage/packing/' . $fileName;
    $sql = "SELECT CASE WHEN i.model_sku IS NOT NULL AND i.model_sku != '' THEN i.model_sku ELSE i.item_sku END AS sku, SUM(i.model_quantity_purchased) AS quantity, GROUP_CONCAT(opq.resi SEPARATOR ', ') AS resi, GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) AS order_ids FROM order_detail i JOIN orders o ON i.order_id = o.id JOIN(SELECT * FROM orders_packing_queue WHERE processed = 0 AND is_changes_sku = 0 AND zipcode = '$zipcode' AND NOT resi = '' AND platform_id = 4 ORDER BY id ASC LIMIT 150) opq ON opq.order_sn = o.order_sn GROUP BY sku;";

    $data = $db->query($sql);

    // Pastikan folder output ada
    if (!is_dir($outputDir)) {
        mkdir($outputDir, 0755, true);
    }

    // Lokasi file PDF yang akan disimpan
    $filePath = $outputDir . '/' . $fileName;
    $barcode = generateBarcode($code);
    // Inisialisasi TCPDF
    $pdf = new TCPDF('P', 'mm', 'A6', true, 'UTF-8', false);

    // Set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('Your Name');
    $pdf->SetTitle('Packing List');
    $pdf->SetSubject('Packing List');
    $pdf->SetKeywords('Packing, List, TCPDF');

    // Remove default header and footer
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);

    // Set margins
    $pdf->SetMargins(0, 0, 0);
    $pdf->SetAutoPageBreak(TRUE, 0);

    // Add a page
    $pdf->AddPage();

    // Template HTML for the table
    $html = '<style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th {
            font-size: 12px;
            text-align: center;
        }
        td {
            font-size: 14px;
            text-align: center;
            padding: 5px;
        }
        .barcode {
            text-align: center;
        }
    </style>';

    // Add header information
    $html .= '<table>
        <tr>
            <td style="font-size:9px; text-align:left; width:20%;">' . date("d-m-Y") . '</td>
            <td style="font-size:14px; text-align:center; width:60%;">' . $code . '<br>
                <img src="data:image/png;base64,' . $barcode['base64'] . '">
            </td>
            <td style="font-size:9px; text-align:right; width:20%;">' . date('H:i:s') .
        '</td>
        </tr>
    </table><br>';

    // Add table header
    $html .= '<table border="1">
        <thead>
            <tr>
                <th style="width:50%;">SKU</th>
                <th style="width:10%;">Q</th>
                <th style="width:40%;">Resi</th>
            </tr>
        </thead>
        <tbody>';

    $orderIds = [];

    // Add table rows dynamically
    foreach ($data as $row) {
        $sku = htmlspecialchars($row["sku"]);
        $quantity = $row["quantity"];
        $resiList = explode(",", $row["resi"]);
        $resiFormatted = implode('<br>', $resiList);
        $orderList = explode(",", $row["order_ids"]);

        $html .= '<tr>';
        $html .= '<td style="width:50%; text-align:left;">' . $sku . '</td>';
        $html .= '<td style="width:10%;">' . $quantity . '</td>';
        $html .= '<td style="width:40%; text-align:left; word-wrap:break-word; font-size:11;">' . $resiFormatted . '</td>';
        $html .= '</tr>';

        $orderIds = array_merge($orderIds, $orderList);
    }

    $html .= '</tbody></table>';

    // Write HTML to PDF
    $pdf->writeHTML($html, true, false, true, false, '');

    // Simpan file ke lokasi yang ditentukan
    if (!$pdf->Output($filePath, 'F')) {
        return ['file_url' => $outputSave, 'order_ids' => array_unique($orderIds)];
    }
}


function generatePackingListPDF($zipcode, $code, $fileName = 'packing_list.pdf')
{
    global $db;

    $outputDir = $_SERVER['DOCUMENT_ROOT'] . '/storage/packing';
    $outputSave = 'storage/packing/' . $fileName;
    $sql = "
    SELECT 
        CASE 
        WHEN i.model_sku IS NOT NULL AND i.model_sku != '' THEN i.model_sku
        ELSE i.item_sku
        END AS sku,
        SUM(i.model_quantity_purchased) AS quantity,
        GROUP_CONCAT(opq.resi SEPARATOR ', ') AS resi,
        GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) AS order_ids
    FROM
        order_detail i
    JOIN 
        orders o ON i.order_id = o.id
    JOIN 
        (SELECT * FROM orders_packing_queue 
            WHERE 
            processed = 0 
            AND is_changes_sku = 0
            AND zipcode = '$zipcode' 
            AND NOT resi = '' 
            AND NOT platform_id = 4 
            ORDER BY id ASC 
            LIMIT 100) opq 
        ON opq.order_sn = o.order_sn
    GROUP BY 
        sku;
    ";


    $data = $db->query($sql);

    // Pastikan folder output ada
    if (!is_dir($outputDir)) {
        mkdir($outputDir, 0755, true);
    }

    // Lokasi file PDF yang akan disimpan
    $filePath = $outputDir . '/' . $fileName;
    $barcode = generateBarcode($code);
    // Inisialisasi TCPDF
    $pdf = new TCPDF('P', 'mm', 'A6', true, 'UTF-8', false);

    // Set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('Your Name');
    $pdf->SetTitle('Packing List');
    $pdf->SetSubject('Packing List');
    $pdf->SetKeywords('Packing, List, TCPDF');

    // Remove default header and footer
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);

    // Set margins
    $pdf->SetMargins(0, 0, 0);
    $pdf->SetAutoPageBreak(TRUE, 0);

    // Add a page
    $pdf->AddPage();

    // Template HTML for the table
    $html = '<style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th {
            font-size: 12px;
            text-align: center;
        }
        td {
            font-size: 14px;
            text-align: center;
            padding: 5px;
        }
        .barcode {
            text-align: center;
        }
    </style>';

    // Add header information
    $html .= '<table>
        <tr>
            <td style="font-size:9px; text-align:left; width:20%;">' . date("d-m-Y") . '</td>
            <td style="font-size:14px; text-align:center; width:60%;">' . $code . '<br>
                <img src="data:image/png;base64,' . $barcode['base64'] . '">
            </td>
            <td style="font-size:9px; text-align:right; width:20%;">' . date('H:i:s') .
        '</td>
        </tr>
    </table><br>';

    // Add table header
    $html .= '<table border="1">
        <thead>
            <tr>
                <th style="width:50%;">SKU</th>
                <th style="width:10%;">Q</th>
                <th style="width:40%;">Resi</th>
            </tr>
        </thead>
        <tbody>';

    $orderIds = [];

    // Add table rows dynamically
    foreach ($data as $row) {
        $sku = htmlspecialchars($row["sku"]);
        $quantity = $row["quantity"];
        $resiList = explode(",", $row["resi"]);
        $resiFormatted = implode('<br>', $resiList);
        $orderList = explode(",", $row["order_ids"]);

        $html .= '<tr>';
        $html .= '<td style="width:50%; text-align:left;">' . $sku . '</td>';
        $html .= '<td style="width:10%;">' . $quantity . '</td>';
        $html .= '<td style="width:40%; text-align:left; word-wrap:break-word; font-size:11;">' . $resiFormatted . '</td>';
        $html .= '</tr>';

        $orderIds = array_merge($orderIds, $orderList);
    }

    $html .= '</tbody></table>';

    // Write HTML to PDF
    $pdf->writeHTML($html, true, false, true, false, '');

    // Simpan file ke lokasi yang ditentukan
    if (!$pdf->Output($filePath, 'F')) {
        return ['file_url' => $outputSave, 'order_ids' => array_unique($orderIds)];
    }
}

function generatePackingListByShopIDInstantPDF($zipcode, $code, $shopIds = [], $fileName = 'packing_list.pdf', $limit = 100)
{
    global $db;

    $outputDir = $_SERVER['DOCUMENT_ROOT'] . '/storage/packing';
    $outputSave = 'storage/packing/' . $fileName;

    $shopIdFilter = '';
    if (!empty($shopIds)) {
        $shopIdFilter = "AND orders.shop_id IN ('" . implode("','", $shopIds) . "')";
    }

    $sql = "
    SELECT 
        CASE 
            WHEN i.model_sku IS NOT NULL AND i.model_sku != '' THEN i.model_sku
            ELSE i.item_sku
        END AS sku,
        SUM(i.model_quantity_purchased) AS quantity,
        GROUP_CONCAT(opq.resi SEPARATOR ', ') AS resi,
        GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) AS order_ids,
        GROUP_CONCAT(DISTINCT opq.id ORDER BY opq.id) AS packing_queue_id
    FROM
        order_detail i
    JOIN 
        orders o ON i.order_id = o.id
    JOIN 
        (SELECT opq.*
         FROM orders_packing_queue opq
         JOIN orders ON opq.order_sn = orders.order_sn
         WHERE 
            opq.processed = 0 
            AND opq.is_changes_sku = 0
            AND opq.zipcode = '$zipcode' 
            AND NOT opq.resi = '' 
            AND opq.is_download_failed = 0
            AND opq.stok_failed = 0
            AND opq.is_oos is null
            AND (opq.is_fake = 0 OR opq.is_fake is null)
            AND orders.shipping_carrier IN ('SPX Instant','GoSend Instant','GrabExpress Sameday','Fail to get channel name','SPX Sameday','Grab Instant','Paxel','Grab','Gojek','GrabExpress Instant','GoSend Same Day')
            $shopIdFilter GROUP BY opq.id
         ORDER BY opq.id ASC 
         LIMIT $limit) opq 
        ON opq.order_sn = o.order_sn
    WHERE 1=1 
    GROUP BY 
        sku;
    ";


    $data = $db->query($sql);

    // Pastikan folder output ada
    if (!is_dir($outputDir)) {
        mkdir($outputDir, 0755, true);
    }

    // Lokasi file PDF yang akan disimpan
    $filePath = $outputDir . '/' . $fileName;
    $barcode = generateBarcode($code);
    // Inisialisasi TCPDF
    $pdf = new TCPDF('P', 'mm', 'A6', true, 'UTF-8', false);

    // Set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('Your Name');
    $pdf->SetTitle('Packing List');
    $pdf->SetSubject('Packing List');
    $pdf->SetKeywords('Packing, List, TCPDF');

    // Remove default header and footer
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);

    // Set margins
    $pdf->SetMargins(0, 0, 0);
    $pdf->SetAutoPageBreak(TRUE, 0);

    // Add a page
    $pdf->AddPage();

    // Template HTML for the table
    $html = '<style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th {
            font-size: 12px;
            text-align: center;
        }
        td {
            font-size: 14px;
            text-align: center;
            padding: 5px;
        }
        .barcode {
            text-align: center;
        }
    </style>';

    // Add header information
    $html .= '<table>
        <tr>
            <td style="font-size:9px; text-align:left; width:20%;">' . date("d-m-Y") . '</td>
            <td style="font-size:14px; text-align:center; width:60%;">' . $code . '<br>
                <img src="data:image/png;base64,' . $barcode['base64'] . '">
            </td>
            <td style="font-size:9px; text-align:right; width:20%;">' . date('H:i:s') .
        '</td>
        </tr>
    </table><br>';

    // Add table header
    $html .= '<table border="1">
        <thead>
            <tr>
                <th style="width:50%;">SKU</th>
                <th style="width:10%;">Q</th>
                <th style="width:40%;">Resi</th>
            </tr>
        </thead>
        <tbody>';

    $orderIds = [];
    $orderPackingQueue = [];

    // Add table rows dynamically
    foreach ($data as $row) {
        $sku = htmlspecialchars($row["sku"]);
        $quantity = $row["quantity"];
        $resiList = explode(",", $row["resi"]);
        $resiFormatted = implode('<br>', $resiList);
        $orderList = explode(",", $row["order_ids"]);
        $orderPackingList = explode(",", $row["packing_queue_id"]);

        $html .= '<tr>';
        $html .= '<td style="width:50%; text-align:left;">' . $sku . '</td>';
        $html .= '<td style="width:10%;">' . $quantity . '</td>';
        $html .= '<td style="width:40%; text-align:left; word-wrap:break-word; font-size:11;">' . $resiFormatted . '</td>';
        $html .= '</tr>';

        $orderIds = array_merge($orderIds, $orderList);
        $orderPackingQueue = array_merge($orderPackingQueue, $orderPackingList);
    }

    $html .= '</tbody></table>';

    // Write HTML to PDF
    $pdf->writeHTML($html, true, false, true, false, '');

    // Simpan file ke lokasi yang ditentukan
    if (!$pdf->Output($filePath, 'F')) {
        return ['file_url' => $outputSave, 'order_ids' => array_unique($orderIds), 'packing_queue_ids' => array_unique($orderPackingQueue)];
    }
}

function generatePackingListByShopIDNonInstantPDF($zipcode, $code, $shopIds = [], $fileName = 'packing_list.pdf', $limit = 100)
{
    global $db;

    $outputDir = $_SERVER['DOCUMENT_ROOT'] . '/storage/packing';
    $outputSave = 'storage/packing/' . $fileName;

    $shopIdFilter = '';
    if (!empty($shopIds)) {
        $shopIdFilter = "AND orders.shop_id IN ('" . implode("','", $shopIds) . "')";
    }

    $sql = "
    SELECT 
        CASE 
            WHEN i.model_sku IS NOT NULL AND i.model_sku != '' THEN i.model_sku
            ELSE i.item_sku
        END AS sku,
        SUM(i.model_quantity_purchased) AS quantity,
        GROUP_CONCAT(opq.resi SEPARATOR ', ') AS resi,
        GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) AS order_ids,
        GROUP_CONCAT(DISTINCT opq.id ORDER BY opq.id) AS packing_queue_id
    FROM
        order_detail i
    JOIN 
        orders o ON i.order_id = o.id
    JOIN 
        (SELECT opq.*
         FROM orders_packing_queue opq
         JOIN orders ON opq.order_sn = orders.order_sn
         WHERE 
            opq.processed = 0 
            AND opq.is_changes_sku = 0
            AND opq.stok_failed = 0
            AND opq.zipcode = '$zipcode' 
            AND NOT opq.resi = '' 
            AND opq.is_download_failed = 0
            AND (opq.is_oos != 1 OR opq.is_oos is NULL)
            AND (opq.is_oos_sku != 1 OR opq.is_oos_sku is NULL)
            AND (opq.is_fake = 0 OR opq.is_fake is null)
            AND orders.shipping_carrier NOT IN ('SPX Instant', 'GoSend Instant', 'GrabExpress Sameday','Fail to get channel name','SPX Sameday','Grab Instant')
            $shopIdFilter GROUP BY opq.id
         ORDER BY opq.id ASC 
         LIMIT $limit) opq 
        ON opq.order_sn = o.order_sn
    WHERE 1=1 
    GROUP BY 
        sku;
    ";

    $data = $db->query($sql);

    // Pastikan folder output ada
    if (!is_dir($outputDir)) {
        mkdir($outputDir, 0755, true);
    }

    // Lokasi file PDF yang akan disimpan
    $filePath = $outputDir . '/' . $fileName;
    $barcode = generateBarcode($code);
    // Inisialisasi TCPDF
    $pdf = new TCPDF('P', 'mm', 'A6', true, 'UTF-8', false);

    // Set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('Your Name');
    $pdf->SetTitle('Packing List');
    $pdf->SetSubject('Packing List');
    $pdf->SetKeywords('Packing, List, TCPDF');

    // Remove default header and footer
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);

    // Set margins
    $pdf->SetMargins(0, 0, 0);
    $pdf->SetAutoPageBreak(TRUE, 0);

    // Add a page
    $pdf->AddPage();

    // Template HTML for the table
    $html = '<style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th {
            font-size: 12px;
            text-align: center;
        }
        td {
            font-size: 14px;
            text-align: center;
            padding: 5px;
        }
        .barcode {
            text-align: center;
        }
    </style>';

    // Add header information
    $html .= '<table>
        <tr>
            <td style="font-size:9px; text-align:left; width:20%;">' . date("d-m-Y") . '</td>
            <td style="font-size:14px; text-align:center; width:60%;">' . $code . '<br>
                <img src="data:image/png;base64,' . $barcode['base64'] . '">
            </td>
            <td style="font-size:9px; text-align:right; width:20%;">' . date('H:i:s') .
        '</td>
        </tr>
    </table><br>';

    // Add table header
    $html .= '<table border="1">
        <thead>
            <tr>
                <th style="width:50%;">SKU</th>
                <th style="width:10%;">Q</th>
                <th style="width:40%;">Resi</th>
            </tr>
        </thead>
        <tbody>';

    $orderIds = [];
    $orderPackingQueue = [];

    // Add table rows dynamically
    foreach ($data as $row) {
        $sku = htmlspecialchars($row["sku"]);
        $quantity = $row["quantity"];
        $resiList = explode(",", $row["resi"]);
        $resiFormatted = implode('<br>', $resiList);
        $orderList = explode(",", $row["order_ids"]);
        $orderPackingList = explode(",", $row["packing_queue_id"]);

        $html .= '<tr>';
        $html .= '<td style="width:50%; text-align:left;">' . $sku . '</td>';
        $html .= '<td style="width:10%;">' . $quantity . '</td>';
        $html .= '<td style="width:40%; text-align:left; word-wrap:break-word; font-size:11;">' . $resiFormatted . '</td>';
        $html .= '</tr>';

        $orderIds = array_merge($orderIds, $orderList);
        $orderPackingQueue = array_merge($orderPackingQueue, $orderPackingList);
    }

    $html .= '</tbody></table>';

    // Write HTML to PDF
    $pdf->writeHTML($html, true, false, true, false, '');

    // Simpan file ke lokasi yang ditentukan
    if (!$pdf->Output($filePath, 'F')) {
        return ['file_url' => $outputSave, 'order_ids' => array_unique($orderIds), 'packing_queue_ids' => array_unique($orderPackingQueue)];
    }
}

function generatePackingListByZipcodeInstantPDF($zipcode, $code, $fileName = 'packing_list.pdf')
{
    global $db;

    $outputDir = $_SERVER['DOCUMENT_ROOT'] . '/storage/packing';
    $outputSave = 'storage/packing/' . $fileName;



    $sql = "
    SELECT 
        CASE 
        WHEN i.model_sku IS NOT NULL AND i.model_sku != '' THEN i.model_sku
        ELSE i.item_sku
        END AS sku,
        SUM(i.model_quantity_purchased) AS quantity,
        GROUP_CONCAT(opq.resi SEPARATOR ', ') AS resi,
        GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) AS order_ids,
        GROUP_CONCAT(DISTINCT opq.id ORDER BY opq.id) AS packing_queue_id
    FROM
        order_detail i
    JOIN 
        orders o ON i.order_id = o.id
    JOIN 
        (SELECT 
            opq.* 
        FROM 
            orders_packing_queue opq
        JOIN 
            orders 
            ON opq.order_sn = orders.order_sn 
        WHERE 
            opq.processed = 0 
            AND opq.is_changes_sku = 0 
            AND opq.zipcode = '$zipcode' 
            AND opq.resi != '' 
            AND opq.is_download_failed = 0
            AND opq.is_oos is null
            AND (opq.is_fake = 0 OR opq.is_fake is null)
            AND orders.shipping_carrier IN ('SPX Instant','GoSend Instant','GrabExpress Sameday','Fail to get channel name','SPX Sameday','Grab Instant','Paxel','Grab','Gojek','GrabExpress Instant','GoSend Same Day')
        ORDER BY 
            opq.id ASC 
        LIMIT 100) opq 
        ON opq.order_sn = o.order_sn 
    GROUP BY 
        sku;
    ";

    $data = $db->query($sql);

    // Pastikan folder output ada
    if (!is_dir($outputDir)) {
        mkdir($outputDir, 0755, true);
    }

    // Lokasi file PDF yang akan disimpan
    $filePath = $outputDir . '/' . $fileName;
    $barcode = generateBarcode($code);
    // Inisialisasi TCPDF
    $pdf = new TCPDF('P', 'mm', 'A6', true, 'UTF-8', false);

    // Set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('Your Name');
    $pdf->SetTitle('Packing List');
    $pdf->SetSubject('Packing List');
    $pdf->SetKeywords('Packing, List, TCPDF');

    // Remove default header and footer
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);

    // Set margins
    $pdf->SetMargins(0, 0, 0);
    $pdf->SetAutoPageBreak(TRUE, 0);

    // Add a page
    $pdf->AddPage();

    // Template HTML for the table
    $html = '<style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th {
            font-size: 12px;
            text-align: center;
        }
        td {
            font-size: 14px;
            text-align: center;
            padding: 5px;
        }
        .barcode {
            text-align: center;
        }
    </style>';

    // Add header information
    $html .= '<table>
        <tr>
            <td style="font-size:9px; text-align:left; width:20%;">' . date("d-m-Y") . '</td>
            <td style="font-size:14px; text-align:center; width:60%;">' . $code . '<br>
                <img src="data:image/png;base64,' . $barcode['base64'] . '">
            </td>
            <td style="font-size:9px; text-align:right; width:20%;">' . date('H:i:s') .
        '</td>
        </tr>
    </table><br>';

    // Add table header
    $html .= '<table border="1">
        <thead>
            <tr>
                <th style="width:50%;">SKU</th>
                <th style="width:10%;">Q</th>
                <th style="width:40%;">Resi</th>
            </tr>
        </thead>
        <tbody>';

    $orderIds = [];
    $orderPackingQueue = [];

    // Add table rows dynamically
    foreach ($data as $row) {
        $sku = htmlspecialchars($row["sku"]);
        $quantity = $row["quantity"];
        $resiList = explode(",", $row["resi"]);
        $resiFormatted = implode('<br>', $resiList);
        $orderList = explode(",", $row["order_ids"]);
        $orderPackingList = explode(",", $row["packing_queue_id"]);

        $html .= '<tr>';
        $html .= '<td style="width:50%; text-align:left;">' . $sku . '</td>';
        $html .= '<td style="width:10%;">' . $quantity . '</td>';
        $html .= '<td style="width:40%; text-align:left; word-wrap:break-word; font-size:11;">' . $resiFormatted . '</td>';
        $html .= '</tr>';

        $orderIds = array_merge($orderIds, $orderList);
        $orderPackingQueue = array_merge($orderPackingQueue, $orderPackingList);
    }

    $html .= '</tbody></table>';

    // Write HTML to PDF
    $pdf->writeHTML($html, true, false, true, false, '');

    // Simpan file ke lokasi yang ditentukan
    if (!$pdf->Output($filePath, 'F')) {
        return ['file_url' => $outputSave, 'order_ids' => array_unique($orderIds), 'packing_queue_ids' => array_unique($orderPackingQueue)];
    }
}

function generatePackingListByZipcodeOOSPDF($zipcode, $code, $fileName = 'packing_list.pdf')
{
    global $db;

    $outputDir = $_SERVER['DOCUMENT_ROOT'] . '/storage/packing';
    $outputSave = 'storage/packing/' . $fileName;



    $sql = "
    SELECT 
        CASE 
        WHEN i.model_sku IS NOT NULL AND i.model_sku != '' THEN i.model_sku
        ELSE i.item_sku
        END AS sku,
        SUM(i.model_quantity_purchased) AS quantity,
        GROUP_CONCAT(opq.resi SEPARATOR ', ') AS resi,
        GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) AS order_ids,
        GROUP_CONCAT(DISTINCT opq.id ORDER BY opq.id) AS packing_queue_id
    FROM
        order_detail i
    JOIN 
        orders o ON i.order_id = o.id
    JOIN 
        (SELECT 
            opq.* 
        FROM 
            orders_packing_queue opq
        JOIN 
            orders 
            ON opq.order_sn = orders.order_sn 
        WHERE 
            opq.zipcode = '$zipcode' 
            AND opq.processed = 0 
            AND opq.is_download_failed = 0 
            AND opq.is_oos = 1 
            AND opq.stok_failed = 0 
            AND opq.approve_required = 0 
            AND opq.is_changes_sku = 0
        ORDER BY 
            opq.id ASC 
        LIMIT 100) opq 
        ON opq.order_sn = o.order_sn 
    GROUP BY 
        sku;
    ";


    $data = $db->query($sql);

    // Pastikan folder output ada
    if (!is_dir($outputDir)) {
        mkdir($outputDir, 0755, true);
    }

    // Lokasi file PDF yang akan disimpan
    $filePath = $outputDir . '/' . $fileName;
    $barcode = generateBarcode($code);
    // Inisialisasi TCPDF
    $pdf = new TCPDF('P', 'mm', 'A6', true, 'UTF-8', false);

    // Set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('Your Name');
    $pdf->SetTitle('Packing List');
    $pdf->SetSubject('Packing List');
    $pdf->SetKeywords('Packing, List, TCPDF');

    // Remove default header and footer
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);

    // Set margins
    $pdf->SetMargins(0, 0, 0);
    $pdf->SetAutoPageBreak(TRUE, 0);

    // Add a page
    $pdf->AddPage();

    // Template HTML for the table
    $html = '<style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th {
            font-size: 12px;
            text-align: center;
        }
        td {
            font-size: 14px;
            text-align: center;
            padding: 5px;
        }
        .barcode {
            text-align: center;
        }
    </style>';

    // Add header information
    $html .= '<table>
        <tr>
            <td style="font-size:9px; text-align:left; width:20%;">' . date("d-m-Y") . '</td>
            <td style="font-size:14px; text-align:center; width:60%;">' . $code . '<br>
                <img src="data:image/png;base64,' . $barcode['base64'] . '">
            </td>
            <td style="font-size:9px; text-align:right; width:20%;">' . date('H:i:s') .
        '</td>
        </tr>
    </table><br>';

    // Add table header
    $html .= '<table border="1">
        <thead>
            <tr>
                <th style="width:50%;">SKU</th>
                <th style="width:10%;">Q</th>
                <th style="width:40%;">Resi</th>
            </tr>
        </thead>
        <tbody>';

    $orderIds = [];
    $orderPackingQueue = [];

    // Add table rows dynamically
    foreach ($data as $row) {
        $sku = htmlspecialchars($row["sku"]);
        $quantity = $row["quantity"];
        $resiList = explode(",", $row["resi"]);
        $resiFormatted = implode('<br>', $resiList);
        $orderList = explode(",", $row["order_ids"]);
        $orderPackingList = explode(",", $row["packing_queue_id"]);

        $html .= '<tr>';
        $html .= '<td style="width:50%; text-align:left;">' . $sku . '</td>';
        $html .= '<td style="width:10%;">' . $quantity . '</td>';
        $html .= '<td style="width:40%; text-align:left; word-wrap:break-word; font-size:11;">' . $resiFormatted . '</td>';
        $html .= '</tr>';

        $orderIds = array_merge($orderIds, $orderList);
        $orderPackingQueue = array_merge($orderPackingQueue, $orderPackingList);
    }

    $html .= '</tbody></table>';

    // Write HTML to PDF
    $pdf->writeHTML($html, true, false, true, false, '');

    // Simpan file ke lokasi yang ditentukan
    if (!$pdf->Output($filePath, 'F')) {
        return ['file_url' => $outputSave, 'order_ids' => array_unique($orderIds), 'packing_queue_ids' => array_unique($orderPackingQueue)];
    }
}

function generatePackingListByZipcodeKurirPDF($zipcode, $code, $kurir, $fileName = 'packing_list.pdf')
{
    global $db;

    $outputDir = $_SERVER['DOCUMENT_ROOT'] . '/storage/packing';
    $outputSave = 'storage/packing/' . $fileName;

    $kurirIdFilter = '';
    if (!empty($kurir)) {
        $kurirIdFilter = "AND orders.shipping_carrier IN ('" . implode("','", $kurir) . "')";
    }

    $sql = "
    SELECT 
        CASE 
        WHEN i.model_sku IS NOT NULL AND i.model_sku != '' THEN i.model_sku
        ELSE i.item_sku
        END AS sku,
        SUM(i.model_quantity_purchased) AS quantity,
        GROUP_CONCAT(opq.resi SEPARATOR ', ') AS resi,
        GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) AS order_ids,
        GROUP_CONCAT(DISTINCT opq.id ORDER BY opq.id) AS packing_queue_id
    FROM
        order_detail i
    JOIN 
        orders o ON i.order_id = o.id
    JOIN 
        (SELECT 
            opq.* 
        FROM 
            orders_packing_queue opq
        JOIN 
            orders 
            ON opq.order_sn = orders.order_sn 
        WHERE 
            opq.processed = 0 
            AND opq.is_changes_sku = 0 
            AND opq.zipcode = '$zipcode' 
            AND opq.resi != '' 
            AND (opq.is_oos != 1 OR opq.is_oos is NULL)
            AND (opq.is_oos_sku != 1 OR opq.is_oos_sku is NULL)
            AND opq.is_download_failed = 0
            AND (opq.is_fake = 0 OR opq.is_fake is null)
            $kurirIdFilter
        ORDER BY 
            opq.id ASC 
        LIMIT 100) opq 
        ON opq.order_sn = o.order_sn 
    GROUP BY 
        sku;
    ";

    $data = $db->query($sql);

    // Pastikan folder output ada
    if (!is_dir($outputDir)) {
        mkdir($outputDir, 0755, true);
    }

    // Lokasi file PDF yang akan disimpan
    $filePath = $outputDir . '/' . $fileName;
    $barcode = generateBarcode($code);
    // Inisialisasi TCPDF
    $pdf = new TCPDF('P', 'mm', 'A6', true, 'UTF-8', false);

    // Set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('Your Name');
    $pdf->SetTitle('Packing List');
    $pdf->SetSubject('Packing List');
    $pdf->SetKeywords('Packing, List, TCPDF');

    // Remove default header and footer
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);

    // Set margins
    $pdf->SetMargins(0, 0, 0);
    $pdf->SetAutoPageBreak(TRUE, 0);

    // Add a page
    $pdf->AddPage();

    // Template HTML for the table
    $html = '<style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th {
            font-size: 12px;
            text-align: center;
        }
        td {
            font-size: 14px;
            text-align: center;
            padding: 5px;
        }
        .barcode {
            text-align: center;
        }
    </style>';

    // Add header information
    $html .= '<table>
        <tr>
            <td style="font-size:9px; text-align:left; width:20%;">' . date("d-m-Y") . '</td>
            <td style="font-size:14px; text-align:center; width:60%;">' . $code . '<br>
                <img src="data:image/png;base64,' . $barcode['base64'] . '">
            </td>
            <td style="font-size:9px; text-align:right; width:20%;">' . date('H:i:s') .
        '</td>
        </tr>
    </table><br>';

    // Add table header
    $html .= '<table border="1">
        <thead>
            <tr>
                <th style="width:50%;">SKU</th>
                <th style="width:10%;">Q</th>
                <th style="width:40%;">Resi</th>
            </tr>
        </thead>
        <tbody>';

    $orderIds = [];
    $orderPackingQueue = [];

    // Add table rows dynamically
    foreach ($data as $row) {
        $sku = htmlspecialchars($row["sku"]);
        $quantity = $row["quantity"];
        $resiList = explode(",", $row["resi"]);
        $resiFormatted = implode('<br>', $resiList);
        $orderList = explode(",", $row["order_ids"]);
        $orderPackingList = explode(",", $row["packing_queue_id"]);

        $html .= '<tr>';
        $html .= '<td style="width:50%; text-align:left;">' . $sku . '</td>';
        $html .= '<td style="width:10%;">' . $quantity . '</td>';
        $html .= '<td style="width:40%; text-align:left; word-wrap:break-word; font-size:11;">' . $resiFormatted . '</td>';
        $html .= '</tr>';

        $orderIds = array_merge($orderIds, $orderList);
        $orderPackingQueue = array_merge($orderPackingQueue, $orderPackingList);
    }

    $html .= '</tbody></table>';

    // Write HTML to PDF
    $pdf->writeHTML($html, true, false, true, false, '');

    // Simpan file ke lokasi yang ditentukan
    if (!$pdf->Output($filePath, 'F')) {
        return ['file_url' => $outputSave, 'order_ids' => array_unique($orderIds), 'packing_queue_ids' => array_unique($orderPackingQueue)];
    }
}
function generatePackingListByZipcodeNonInstantPDF($zipcode, $code, $fileName = 'packing_list.pdf')
{
    global $db;

    $outputDir = $_SERVER['DOCUMENT_ROOT'] . '/storage/packing';
    $outputSave = 'storage/packing/' . $fileName;



    $sql = "
    SELECT 
        CASE 
        WHEN i.model_sku IS NOT NULL AND i.model_sku != '' THEN i.model_sku
        ELSE i.item_sku
        END AS sku,
        SUM(i.model_quantity_purchased) AS quantity,
        GROUP_CONCAT(opq.resi SEPARATOR ', ') AS resi,
        GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) AS order_ids,
        GROUP_CONCAT(DISTINCT opq.id ORDER BY opq.id) AS packing_queue_id
    FROM
        order_detail i
    JOIN 
        orders o ON i.order_id = o.id
    JOIN 
        (SELECT 
            opq.* 
        FROM 
            orders_packing_queue opq
        JOIN 
            orders 
            ON opq.order_sn = orders.order_sn 
        WHERE 
            opq.processed = 0 
            AND opq.is_changes_sku = 0 
            AND opq.zipcode = '$zipcode' 
            AND opq.resi != '' 
            AND opq.is_download_failed = 0
            AND (opq.is_fake = 0 OR opq.is_fake is null)
            AND orders.shipping_carrier NOT IN ('SPX Instant', 'GoSend Instant', 'GrabExpress Sameday','Fail to get channel name','SPX Sameday','Grab Instant')
        ORDER BY 
            opq.id ASC 
        LIMIT 100) opq 
        ON opq.order_sn = o.order_sn 
    GROUP BY 
        sku;
    ";

    $data = $db->query($sql);

    // Pastikan folder output ada
    if (!is_dir($outputDir)) {
        mkdir($outputDir, 0755, true);
    }

    // Lokasi file PDF yang akan disimpan
    $filePath = $outputDir . '/' . $fileName;
    $barcode = generateBarcode($code);
    // Inisialisasi TCPDF
    $pdf = new TCPDF('P', 'mm', 'A6', true, 'UTF-8', false);

    // Set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('Your Name');
    $pdf->SetTitle('Packing List');
    $pdf->SetSubject('Packing List');
    $pdf->SetKeywords('Packing, List, TCPDF');

    // Remove default header and footer
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);

    // Set margins
    $pdf->SetMargins(0, 0, 0);
    $pdf->SetAutoPageBreak(TRUE, 0);

    // Add a page
    $pdf->AddPage();

    // Template HTML for the table
    $html = '<style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th {
            font-size: 12px;
            text-align: center;
        }
        td {
            font-size: 14px;
            text-align: center;
            padding: 5px;
        }
        .barcode {
            text-align: center;
        }
    </style>';

    // Add header information
    $html .= '<table>
        <tr>
            <td style="font-size:9px; text-align:left; width:20%;">' . date("d-m-Y") . '</td>
            <td style="font-size:14px; text-align:center; width:60%;">' . $code . '<br>
                <img src="data:image/png;base64,' . $barcode['base64'] . '">
            </td>
            <td style="font-size:9px; text-align:right; width:20%;">' . date('H:i:s') .
        '</td>
        </tr>
    </table><br>';

    // Add table header
    $html .= '<table border="1">
        <thead>
            <tr>
                <th style="width:50%;">SKU</th>
                <th style="width:10%;">Q</th>
                <th style="width:40%;">Resi</th>
            </tr>
        </thead>
        <tbody>';

    $orderIds = [];
    $orderPackingQueue = [];

    // Add table rows dynamically
    foreach ($data as $row) {
        $sku = htmlspecialchars($row["sku"]);
        $quantity = $row["quantity"];
        $resiList = explode(",", $row["resi"]);
        $resiFormatted = implode('<br>', $resiList);
        $orderList = explode(",", $row["order_ids"]);
        $orderPackingList = explode(",", $row["packing_queue_id"]);

        $html .= '<tr>';
        $html .= '<td style="width:50%; text-align:left;">' . $sku . '</td>';
        $html .= '<td style="width:10%;">' . $quantity . '</td>';
        $html .= '<td style="width:40%; text-align:left; word-wrap:break-word; font-size:11;">' . $resiFormatted . '</td>';
        $html .= '</tr>';

        $orderIds = array_merge($orderIds, $orderList);
        $orderPackingQueue = array_merge($orderPackingQueue, $orderPackingList);
    }

    $html .= '</tbody></table>';

    // Write HTML to PDF
    $pdf->writeHTML($html, true, false, true, false, '');

    // Simpan file ke lokasi yang ditentukan
    if (!$pdf->Output($filePath, 'F')) {
        return ['file_url' => $outputSave, 'order_ids' => array_unique($orderIds), 'packing_queue_ids' => array_unique($orderPackingQueue)];
    }
}

function generatePackingListByZipcodePDF($zipcode, $code, $fileName = 'packing_list.pdf')
{
    global $db;

    $outputDir = $_SERVER['DOCUMENT_ROOT'] . '/storage/packing';
    $outputSave = 'storage/packing/' . $fileName;



    $sql = "
    SELECT 
        CASE 
        WHEN i.model_sku IS NOT NULL AND i.model_sku != '' THEN i.model_sku
        ELSE i.item_sku
        END AS sku,
        SUM(i.model_quantity_purchased) AS quantity,
        GROUP_CONCAT(opq.resi SEPARATOR ', ') AS resi,
        GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) AS order_ids,
        GROUP_CONCAT(DISTINCT opq.id ORDER BY opq.id) AS packing_queue_id
    FROM
        order_detail i
    JOIN 
        orders o ON i.order_id = o.id
    JOIN 
        (SELECT * FROM orders_packing_queue 
            WHERE 
            processed = 0 
            AND is_changes_sku = 0
            AND zipcode = '$zipcode' 
            AND NOT resi = '' 
            ORDER BY id ASC 
            LIMIT 100) opq 
        ON opq.order_sn = o.order_sn 
    GROUP BY 
        sku;
    ";


    $data = $db->query($sql);

    // Pastikan folder output ada
    if (!is_dir($outputDir)) {
        mkdir($outputDir, 0755, true);
    }

    // Lokasi file PDF yang akan disimpan
    $filePath = $outputDir . '/' . $fileName;
    $barcode = generateBarcode($code);
    // Inisialisasi TCPDF
    $pdf = new TCPDF('P', 'mm', 'A6', true, 'UTF-8', false);

    // Set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('Your Name');
    $pdf->SetTitle('Packing List');
    $pdf->SetSubject('Packing List');
    $pdf->SetKeywords('Packing, List, TCPDF');

    // Remove default header and footer
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);

    // Set margins
    $pdf->SetMargins(0, 0, 0);
    $pdf->SetAutoPageBreak(TRUE, 0);

    // Add a page
    $pdf->AddPage();

    // Template HTML for the table
    $html = '<style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th {
            font-size: 12px;
            text-align: center;
        }
        td {
            font-size: 14px;
            text-align: center;
            padding: 5px;
        }
        .barcode {
            text-align: center;
        }
    </style>';

    // Add header information
    $html .= '<table>
        <tr>
            <td style="font-size:9px; text-align:left; width:20%;">' . date("d-m-Y") . '</td>
            <td style="font-size:14px; text-align:center; width:60%;">' . $code . '<br>
                <img src="data:image/png;base64,' . $barcode['base64'] . '">
            </td>
            <td style="font-size:9px; text-align:right; width:20%;">' . date('H:i:s') .
        '</td>
        </tr>
    </table><br>';

    // Add table header
    $html .= '<table border="1">
        <thead>
            <tr>
                <th style="width:50%;">SKU</th>
                <th style="width:10%;">Q</th>
                <th style="width:40%;">Resi</th>
            </tr>
        </thead>
        <tbody>';

    $orderIds = [];
    $orderPackingQueue = [];

    // Add table rows dynamically
    foreach ($data as $row) {
        $sku = htmlspecialchars($row["sku"]);
        $quantity = $row["quantity"];
        $resiList = explode(",", $row["resi"]);
        $resiFormatted = implode('<br>', $resiList);
        $orderList = explode(",", $row["order_ids"]);
        $orderPackingList = explode(",", $row["packing_queue_id"]);

        $html .= '<tr>';
        $html .= '<td style="width:50%; text-align:left;">' . $sku . '</td>';
        $html .= '<td style="width:10%;">' . $quantity . '</td>';
        $html .= '<td style="width:40%; text-align:left; word-wrap:break-word; font-size:11;">' . $resiFormatted . '</td>';
        $html .= '</tr>';

        $orderIds = array_merge($orderIds, $orderList);
        $orderPackingQueue = array_merge($orderPackingQueue, $orderPackingList);
    }

    $html .= '</tbody></table>';

    // Write HTML to PDF
    $pdf->writeHTML($html, true, false, true, false, '');

    // Simpan file ke lokasi yang ditentukan
    if (!$pdf->Output($filePath, 'F')) {
        return ['file_url' => $outputSave, 'order_ids' => array_unique($orderIds), 'packing_queue_ids' => array_unique($orderPackingQueue)];
    }
}
function downloadFileFromUrl($docUrl, $nameDownload)
{
    // Step 1: Initialize cURL session
    $ch = curl_init($docUrl);

    // Step 2: Set cURL options to return the response and avoid outputting directly
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);  // Follow any redirects (if necessary)

    // Step 3: Execute cURL session to fetch the file content
    $fileContent = curl_exec($ch);

    // Step 4: Check for errors in the cURL session
    if (curl_errno($ch)) {
        // Handle error (e.g., logging, returning an error message)
        echo 'Error:' . curl_error($ch);
        curl_close($ch);
        return false;
    }

    // Step 5: Get HTTP response code to ensure the file is valid
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    if ($httpCode == 404) {
        // echo "Error: Failed to download file, HTTP status code: " . $httpCode;
        curl_close($ch);
        sleep(10);
        return downloadFileFromUrl($docUrl, $nameDownload);
    } elseif ($httpCode != 200) {
        // echo "Error: Failed to download file, HTTP status code: " . $httpCode;
        curl_close($ch);
        return false;
    }

    // Step 6: Save the file to the specified location
    $saveResult = file_put_contents($nameDownload, $fileContent);

    // Step 7: Check if the file was successfully saved
    if ($saveResult === false) {
        // echo "Error: Failed to save file to $nameDownload";
        curl_close($ch);
        return false;
    }

    // Step 8: Close the cURL session
    curl_close($ch);

    // Return true if the file was successfully downloaded and saved
    return true;
}

function logApiRequest($url, $method, $headers, $body, $responseStatus, $responseBody, $responseTime, $orderSn = "")
{
    global $db; // Assuming $db is your MySQL database connection object

    // Prepare the SQL query to insert the log entry
    $sql = "INSERT INTO api_requests_log (request_url, request_method, request_headers, request_body, response_status, response_body, response_time, order_sn)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

    // Prepare the statement
    if ($stmt = $db->prepare($sql)) {
        // Encode headers, body, and responseBody to JSON
        $jsonHeaders = json_encode($headers);
        $jsonBody = json_encode($body);
        $jsonResponseBody = json_encode($responseBody);

        // Bind the parameters
        $stmt->bind_param(
            'ssssssss',
            $url,
            $method,
            $jsonHeaders,
            $jsonBody,
            $responseStatus,
            $jsonResponseBody,
            $responseTime,
            $orderSn
        );

        // Execute the statement
        $stmt->execute();

        // Close the statement
        $stmt->close();
    } else {
        echo 'Error preparing SQL statement: ' . $db->error;
    }
}

function insert_order_detail($order_sn, $order_id, $model_sku, $item_name, $model_quantity_purchased, $price)
{
    global $db;

    // Escape the inputs to prevent SQL injection
    $order_sn = $order_sn;
    $order_id = (int)$order_id; // Ensure order_id is an integer
    $model_sku = $model_sku;
    $item_sku = $model_sku;
    $item_name = $item_name;
    $price = $price;
    $model_quantity_purchased = (int)$model_quantity_purchased; // Ensure quantity is an integer

    // Prepare the SQL insert statement
    $sql = "INSERT INTO order_detail (order_sn, order_id, model_sku, item_sku, model_quantity_purchased, item_name, model_original_price) 
            VALUES ('{$order_sn}', {$order_id}, '{$model_sku}', '{$item_sku}', {$model_quantity_purchased}, '{$item_name}', {$price})";

    // Execute the query
    if ($db->query($sql) === TRUE) {
        return true;
    } else {
        return false;
    }
}


// Fungsi untuk koneksi ke Redis menggunakan Predis
function redisConnect()
{
    // Cek apakah Redis diaktifkan
    if (empty(REDIS_HOST) || empty(REDIS_PORT)) {
        return null; // Skip koneksi kalau tidak di-set
    }

    try {
        $redis = new Client([
            'scheme' => 'tcp',
            'host' => REDIS_HOST,
            'port' => REDIS_PORT,
            'database' => 0,
            'password' => REDIS_PASS,
            'read_write_timeout' => 0
        ]);
        return $redis;
    } catch (Exception $e) {
        // Jangan hentikan aplikasi, cukup return null
        return null;
    }
}

// Fungsi untuk menyimpan data ke Redis
function redisSet($key, $value, $ttl = 300)
{
    if (ENVIRONMENT == 'Develop') {
        $key = 'DEV:' . $key;
    }
    $redis = redisConnect();
    if ($redis) {
        $redis->set($key, json_encode($value));
        $redis->expire($key, $ttl);
    }
}

// Fungsi untuk mengambil data dari Redis
function redisGet($key)
{
    if (ENVIRONMENT == 'Develop') {
        $key = 'DEV:' . $key;
    }
    $redis = redisConnect();
    if ($redis) {
        $value = $redis->get($key);
        return $value ? json_decode($value, true) : null;
    }
    return null;
}

// Fungsi untuk menghapus data dari Redis
function redisDelete($key)
{
    if (ENVIRONMENT == 'Develop') {
        $key = 'DEV:' . $key;
    }
    $redis = redisConnect();
    if ($redis) {
        $redis->del([$key]);
    }
}

function write_log($message, $file)
{
    $user = current_user();
    $username = $user['name'] ?? "System";
    $log_entry = date("Y-m-d H:i:s") . " - User: " . $username . " - " . $_SERVER['REMOTE_ADDR'] . " - " . $message . "\n";
    file_put_contents($file, $log_entry, FILE_APPEND);
}



function createAWBPDF($order_id, $order_sn, $customer_name, $address, $phone, $kurir, $products)
{
    // Create instance of TCPDF
    $pdf = new TCPDF();

    // Set document information
    $pdf->SetCreator('PT. BALI CAHAYA ASIA');
    $pdf->SetAuthor('PT. BALI CAHAYA ASIA');
    $pdf->SetTitle('AWB - ' . $order_sn);
    $pdf->SetSubject('AWB for Order ' . $order_sn);

    // Set default header data
    $pdf->SetHeaderData('', 0, 'AWB: ' . $order_sn, 'Customer: ' . $customer_name);
    $pdf->setHeaderFont(array('helvetica', '', 12));
    $pdf->setFooterFont(array('helvetica', '', 8));
    $pdf->SetMargins(10, 25, 10);
    $pdf->SetFooterMargin(10);
    $pdf->SetAutoPageBreak(TRUE, 10);

    // Add a page
    $pdf->AddPage();

    // Order details
    $pdf->SetFont('helvetica', 'B', 14);
    $pdf->Cell(0, 10, 'AWB Details', 0, 1, 'C');

    $pdf->SetFont('helvetica', '', 12);
    $pdf->Ln(5);
    $pdf->Cell(40, 10, 'Order ID:', 0, 0);
    $pdf->Cell(0, 10, $order_id, 0, 1);
    $pdf->Cell(40, 10, 'Order SN:', 0, 0);
    $pdf->Cell(0, 10, $order_sn, 0, 1);
    $pdf->Cell(40, 10, 'Customer Name:', 0, 0);
    $pdf->Cell(0, 10, $customer_name, 0, 1);
    $pdf->Cell(40, 10, 'Address:', 0, 0);
    $pdf->MultiCell(0, 10, $address);
    $pdf->Cell(40, 10, 'Phone:', 0, 0);
    $pdf->Cell(0, 10, $phone, 0, 1);
    $pdf->Cell(40, 10, 'Courier:', 0, 0);
    $pdf->Cell(0, 10, $kurir, 0, 1);

    // Add product details table
    $pdf->Ln(5);
    $pdf->SetFont('helvetica', 'B', 12);
    $pdf->Cell(60, 10, 'Product Name', 1, 0, 'C');
    $pdf->Cell(40, 10, 'Type Box', 1, 0, 'C');
    $pdf->Cell(40, 10, 'Quantity', 1, 0, 'C');
    $pdf->Cell(40, 10, 'Price', 1, 0, 'C');
    $pdf->Cell(40, 10, 'Subtotal', 1, 1, 'C');

    $pdf->SetFont('helvetica', '', 12);

    foreach ($products as $product) {
        $pdf->Cell(60, 10, $product['name'], 1, 0, 'C');
        $pdf->Cell(40, 10, $product['type_box'], 1, 0, 'C');
        $pdf->Cell(40, 10, $product['quantity'], 1, 0, 'C');
        $pdf->Cell(40, 10, number_format($product['price'], 2), 1, 0, 'C');
        $pdf->Cell(40, 10, number_format($product['subtotal'], 2), 1, 1, 'C');
    }

    // Output the PDF (you can save it to a file or output to browser)
    $file_path = 'storage/awb/awb_' . $order_sn . '.pdf';
    $pdf->Output($file_path, 'F'); // Save to file

    // Optionally, output to browser
    // $pdf->Output('awb_' . $order_sn . '.pdf', 'I'); // For viewing in the browser

    return $file_path; // Return file path if needed
}


function isInstant($courierName)
{
    global $db;
    // Prepare the SQL query
    $query = "SELECT name FROM couriers WHERE name = ? AND type = 'INSTANT'";

    // Use prepared statements to prevent SQL injection
    if ($stmt = $db->prepare($query)) {
        // Bind the parameter
        $stmt->bind_param("s", $courierName);

        // Execute the query
        $stmt->execute();

        // Store the result
        $stmt->store_result();

        // Check if any rows were returned
        if ($stmt->num_rows > 0) {
            // Close the statement
            $stmt->close();
            return true; // The courier is of type 'INSTANT'
        } else {
            // Close the statement
            $stmt->close();
            return false; // The courier is not of type 'INSTANT'
        }
    } else {
        // Handle query preparation error
        die("Query preparation failed: " . $db->error);
    }
}


function sendWhatsAppMessage($contactNumber, $message)
{
    $curl = curl_init();
    curl_setopt_array($curl, array(
        CURLOPT_URL => 'https://graph.facebook.com/v22.0/' . WA_BUSSINESS_ID . '/messages',
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 0,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'POST',
        CURLOPT_POSTFIELDS => json_encode([
            "messaging_product" => "whatsapp",
            "recipient_type" => "individual",
            "to" => $contactNumber,
            "type" => "text",
            "text" => [
                "preview_url" => true,
                "body" => $message
            ]
        ]),
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json',
            'Authorization: Bearer EAANMQ8g8QzsBOZBEDdu7Ua1L3A2WSZCJWlwKnYhJtn2OYMlhv1FiDWl7I74uhZBmXKvpaZCK6pEzYpAoFOE4wNWmspCbzswZCkOga7O9yTyERjqbH6fZBW6vB9gXTkBKRD7TQ0vug0jZBHZBUPik0RRXdcKkrvPB9QSyl9icPt36f8JqiVwuBIKLKUYlzioHBpkmsbpCI4Ds8s6zr19JZChRnaVmhpKn6OrQZD' // Replace with your actual token
        ),
    ));

    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE); // Get HTTP response code

    curl_close($curl);

    // Return true if the request was successful (HTTP status code 200)
    return $httpCode === 200;
}

function sendWhatsAppMediaMessage($contactNumber, $mediaId, $mediaType)
{
    $curl = curl_init();

    curl_setopt_array($curl, array(
        CURLOPT_URL => 'https://graph.facebook.com/v22.0/' . WA_BUSSINESS_ID . '/messages',
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 0,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'POST',
        CURLOPT_POSTFIELDS => json_encode([
            "messaging_product" => "whatsapp",
            "to" => $contactNumber,
            "type" => $mediaType, // e.g., "video", "image", etc.
            $mediaType => [
                "id" => $mediaId
            ]
        ]),
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json',
            'Authorization: Bearer YOUR_ACCESS_TOKEN' // Replace with your actual token
        ),
    ));

    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE); // Get HTTP response code

    curl_close($curl);

    return $httpCode === 200; // Return true if the request was successful
}

function sendWhatsAppMedia($filePath, $fileType)
{
    $curl = curl_init();

    curl_setopt_array($curl, array(
        CURLOPT_URL => 'https://graph.facebook.com/v22.0/' . WA_BUSSINESS_ID . '/media',
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 0,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'POST',
        CURLOPT_POSTFIELDS => [
            'messaging_product' => 'whatsapp',
            'file' => new CURLFile($filePath, $fileType) // Attach the file
        ],
        CURLOPT_HTTPHEADER => array(
            'Authorization: Bearer YOUR_ACCESS_TOKEN' // Replace with your actual token
        ),
    ));

    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE); // Get HTTP response code

    curl_close($curl);

    if ($httpCode === 200) {
        $responseData = json_decode($response, true);
        return $responseData['id'] ?? null; // Return the media ID if successful
    }

    return false; // Return false if the request failed
}

function sendWhatsAppTemplateMessage($contactNumber, $nameTemplate)
{
    $curl = curl_init();
    curl_setopt_array($curl, array(
        CURLOPT_URL => 'https://graph.facebook.com/v22.0/' . WA_BUSSINESS_ID . '/messages',
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 0,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'POST',
        CURLOPT_POSTFIELDS => json_encode([
            "messaging_product" => "whatsapp",
            "to" => $contactNumber,
            "type" => "template",
            "template" => [
                "name" => $nameTemplate, // Replace with your actual template name
                "language" => [
                    "code" => "en_US"
                ]
            ]
        ]),
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json',
            'Authorization: Bearer EAANMQ8g8QzsBOZBEDdu7Ua1L3A2WSZCJWlwKnYhJtn2OYMlhv1FiDWl7I74uhZBmXKvpaZCK6pEzYpAoFOE4wNWmspCbzswZCkOga7O9yTyERjqbH6fZBW6vB9gXTkBKRD7TQ0vug0jZBHZBUPik0RRXdcKkrvPB9QSyl9icPt36f8JqiVwuBIKLKUYlzioHBpkmsbpCI4Ds8s6zr19JZChRnaVmhpKn6OrQZD'
        ),
    ));

    $response = curl_exec($curl);
    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE); // Get HTTP response code

    curl_close($curl);

    // Return true if the request was successful (HTTP status code 200)
    return $httpCode === 200;
}

function find_part_number_new($part_number)
{
    global $db;

    // Buat kunci Redis berdasarkan part_number
    $redisKey = "part_number_new:{$part_number}";

    // Coba ambil data dari Redis
    $cachedData = redisGet($redisKey);
    if ($cachedData !== null) {
        return $cachedData; // Kembalikan data dari Redis jika ditemukan
    }

    // Jika tidak ditemukan di Redis, ambil dari database
    $part_number = $db->escape($part_number); // Escape input untuk mencegah SQL injection
    $sql = "SELECT part_number_new FROM group_part_number WHERE part_number_child = '{$part_number}' LIMIT 1";
    $result = $db->query($sql);

    if ($result && $result->num_rows > 0) {
        $row = $result->fetch_assoc(); // Ambil hasil sebagai array asosiatif
        $part_number_new = $row['part_number_new'];

        // Simpan hasil ke Redis dengan TTL (Time To Live) 300 detik
        redisSet($redisKey, $part_number_new, 14400);

        return $part_number_new; // Kembalikan nilai part_number_new
    } else {
        // Jika tidak ditemukan, simpan string kosong di Redis
        redisSet($redisKey, "", 14400);
        return ""; // Kembalikan string kosong
    }
}

function updateOrderOOS($resi)
{
    global $db;
    $sql = "UPDATE orders_packing_queue SET stok_failed = '1', processed = '0', is_oos = '1' WHERE resi = '$resi'";
    $log_file = "scan_oos_log.txt";
    write_log($sql, $log_file);
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $sql2 = "UPDATE orders SET id_packing_list=NULL WHERE order_sn IN (SELECT order_sn FROM orders_packing_queue WHERE resi = '$resi')";
    write_log($sql2, $log_file);
    $stmt2 = $db->prepare($sql2);
    return $stmt2->execute();
}


function updateOrderOOSSKU($resi)
{
    global $db;
    $sql = "UPDATE orders_packing_queue SET stok_failed = '1', processed = '0', is_oos = '1', is_oos_sku = '1' WHERE resi = '$resi'";
    $log_file = "scan_oos_log.txt";
    write_log($sql, $log_file);
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $sql2 = "UPDATE orders SET id_packing_list=NULL WHERE order_sn IN (SELECT order_sn FROM orders_packing_queue WHERE resi = '$resi')";
    write_log($sql2, $log_file);
    $stmt2 = $db->prepare($sql2);
    return $stmt2->execute();
}

function find_by_field_custome($table, $field, $value)
{
    global $db;
    $sql = "SELECT * FROM {$table} WHERE {$field} = '{$value}' LIMIT 1";
    $result = $db->query($sql);
    return $result->fetch_assoc();
}

function StockHistoryProductQuantity($part_number, $location, $new_quantity, $tableProduct = 'products', $reason = 'Manual Update', $category = 'OTHER', $warehouse_id = 0)
{
    global $db;
    $user = current_user();
    $updated_by = $user['name'] ?? "System";

    // Find the product by part number
    $product = find_by_field_custome($tableProduct, 'part_number', $part_number);

    if (!$product) {
        return false; // Product not found
    }

    $prev_quantity = $product['quantity'];
    $qty_change = $new_quantity - $prev_quantity;
    // Log the change in stock history
    $historyQuery = "INSERT INTO stock_history (
                            part_number, 
                            warehouse, 
                            from_warehouse_id, 
                            status, 
                            qty_change, 
                            prev_qty, 
                            new_qty, 
                            reason, 
                            category, 
                            created_by
                        ) VALUES (
                            '{$part_number}', 
                            '{$location}', 
                            '{$warehouse_id}', 
                            '" . ($qty_change > 0 ? 'IN' : 'OUT') . "', 
                            '" . abs($qty_change) . "', 
                            '{$prev_quantity}', 
                            '{$new_quantity}', 
                            '{$reason}', 
                            '{$category}', 
                            '{$updated_by}'
                        )";
    return $db->query($historyQuery);
}

function insertStockHistory($part_number, $location, $status, $qty_change, $reason, $from_warehouse_id, $tableProduct = 'products', $category = 'OTHER', $transfer_id = 0, $to_warehouse_id = 0)
{
    global $db;
    $user = current_user();
    $created_by = $user['name'] ?? "System";
    $product = find_by_field_custome($tableProduct, 'part_number', $part_number);
    $prev_qty = $product['quantity'] ?? 0;
    $new_qty = 0;
    $qty_change = abs($qty_change);
    if ($status === 'OUT') {
        $new_qty = $prev_qty - abs($qty_change);
    } else {
        $new_qty = $prev_qty + abs($qty_change);
    }
    // Query untuk mencatat riwayat perpindahan stok
    $query = "INSERT INTO stock_history (
                  part_number, 
                  warehouse, 
                  status, 
                  qty_change, 
                  prev_qty, 
                  new_qty, 
                  transfer_id, 
                  from_warehouse_id, 
                  to_warehouse_id, 
                  reason, 
                  category, 
                  created_by
              ) VALUES (
                  '{$part_number}', 
                  '{$location}', 
                  '{$status}', 
                  '{$qty_change}', 
                  '{$prev_qty}', 
                  '{$new_qty}', 
                  '{$transfer_id}', 
                  '{$from_warehouse_id}', 
                  '{$to_warehouse_id}', 
                  '{$reason}', 
                  '{$category}', 
                  '{$created_by}'
              )";
    return $db->query($query); // Mengembalikan hasil eksekusi query
}

function find_all_by_date($table, $date) {
    global $db;
    $query = "SELECT a.*, e.first_name, e.last_name FROM {$db->escape($table)} AS a JOIN employees AS e ON a.employee_id = e.id WHERE a.date = '{$db->escape($date)}'";
    $result = $db->query($query);
    
    // Perbaikan dari masalah sebelumnya
    $attendance = []; 
    while ($row = $db->fetch_assoc($result)) {
        $attendance[] = $row;
    }
    return $attendance; 
}


function update_hari_kerja_tabel($bulan, $tahun) {
    global $db;

    // Hitung total hari dalam sebulan
    $jumlah_total_hari = cal_days_in_month(CAL_GREGORIAN, $bulan, $tahun);

    // Hitung jumlah hari akhir pekan (Sabtu & Minggu)
    $jumlah_akhir_pekan = 0;
    for ($i = 1; $i <= $jumlah_total_hari; $i++) {
        $hari_ke_minggu = date('w', strtotime("{$tahun}-{$bulan}-{$i}"));
        if ($hari_ke_minggu == 0 || $hari_ke_minggu == 6) {
            $jumlah_akhir_pekan++;
        }
    }

    // Hitung jumlah hari libur dari tabel kalender_kerja
    $sql_libur = "SELECT COUNT(*) as total_libur FROM kalender_kerja WHERE MONTH(tanggal) = '{$bulan}' AND YEAR(tanggal) = '{$tahun}' AND status = 'Libur'";
    $libur_result = find_by_sql($sql_libur);
    $jumlah_libur_khusus = !empty($libur_result) ? (int)$libur_result[0]['total_libur'] : 0;

    // Hitung hari kerja efektif
    $jumlah_hari_kerja_efektif = $jumlah_total_hari - $jumlah_akhir_pekan - $jumlah_libur_khusus;

    // Simpan atau perbarui data ke tabel hari_kerja
    $sql_cek = "SELECT id FROM hari_kerja WHERE bulan = '{$bulan}' AND tahun = '{$tahun}' LIMIT 1";
    $data_existing = find_by_sql($sql_cek);

    if ($data_existing) {
        $sql_update = "UPDATE hari_kerja SET jumlah_hari_kerja = '{$jumlah_hari_kerja_efektif}', updated_at = NOW() WHERE id = '{$data_existing[0]['id']}'";
        $db->query($sql_update);
    } else {
        $sql_insert = "INSERT INTO hari_kerja (bulan, tahun, jumlah_hari_kerja, created_at, updated_at) VALUES ('{$bulan}', '{$tahun}', '{$jumlah_hari_kerja_efektif}', NOW(), NOW())";
        $db->query($sql_insert);
    }
}







?>