<?php
// =======================================================
// cron_transfer_order_to_db.php  (FULL COPY/PASTE FILE)
// - Create customer ONCE per order
// - Insert woocommerce_order_info_item per item
// - Bonus Sponsor (%) from system_setting.bonus_sponsor (agent level 1 only)
// - Hibah Unilevel 3 levels (%) from hibah_unilevel_setting with compression
// - Logs ALL errors (PHP fatal + MySQL) into /logs folder
//
// NEW REQUIREMENTS APPLIED:
// 1) Do NOT insert user+wallet if customer already exists by billing_email
// 2) nick_name_new = wording from billing_email before @ (unique)
// 3) After creating new customer, email login info
// 4) Email bonus info to agent for each bonus txn inserted
// 5) agentHasSalesThisMonth checks woocommerce_order_info OR transactions (bonus/hibah) for current month
// =======================================================

ini_set('display_errors', 0);
error_reporting(0);
date_default_timezone_set("Asia/Kuala_Lumpur");

// ------------------------
// LOGGING (for HTTP 500)
// ------------------------
function cron_log($msg, $context = [])
{
    $logDir = __DIR__ . "/logs";
    if (!is_dir($logDir)) {
        @mkdir($logDir, 0755, true);
    }

    $file = $logDir . "/cron_transfer_order_to_db_" . date("Y-m-d") . ".log";
    $line = "[" . date("Y-m-d H:i:s") . "] " . $msg;

    if (!empty($context)) {
        $json = json_encode($context, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        $line .= " | " . $json;
    }

    $line .= PHP_EOL;
    @file_put_contents($file, $line, FILE_APPEND);
}

// Log fatal errors (HTTP 500)
register_shutdown_function(function () {
    $e = error_get_last();
    if ($e && in_array($e['type'], [E_ERROR, E_PARSE, E_CORE_ERROR, E_COMPILE_ERROR])) {
        cron_log("FATAL", [
            "type" => $e['type'],
            "message" => $e['message'],
            "file" => $e['file'],
            "line" => $e['line'],
        ]);
    }
});

// Log warnings/notices
set_error_handler(function ($errno, $errstr, $errfile, $errline) {
    cron_log("PHP_ERROR", [
        "errno" => $errno,
        "message" => $errstr,
        "file" => $errfile,
        "line" => $errline,
    ]);
    return false;
});

// Log uncaught exceptions
set_exception_handler(function ($ex) {
    cron_log("EXCEPTION", [
        "message" => $ex->getMessage(),
        "file" => $ex->getFile(),
        "line" => $ex->getLine(),
        "trace" => $ex->getTraceAsString(),
    ]);
});

// ------------------------
// DB CONNECTION
// ------------------------
$host = "localhost";
$username = "mygoldpl_mygoldplt"; 
$password = "295@pS7(F4"; 
$db = "mygoldpl_mygoldplt"; 

$mysqli = new mysqli($host, $username, $password, $db);
if ($mysqli->connect_errno) {
    cron_log("DB_CONNECT_FAILED", ["errno" => $mysqli->connect_errno, "error" => $mysqli->connect_error]);
    die("DB connect failed");
}
$mysqli->set_charset("utf8mb4");

// Wrapper: query + log mysql errors
function cron_query($sql, $ctx = [])
{
    global $mysqli;
    $res = $mysqli->query($sql);
    if ($res === false) {
        cron_log("MYSQL_ERROR", array_merge($ctx, [
            "mysqli_error" => $mysqli->error,
            "sql" => $sql
        ]));
    }
    return $res;
}

// ------------------------
// HELPERS
// ------------------------
function randomRefferalCode()
{
    $alphabet = "abcdefghijklmnopqrstuwxyzABCDEFGHIJKLMNOPQRSTUWXYZ0123456789";
    $pass = [];
    $alphaLength = strlen($alphabet) - 1;
    for ($i = 0; $i < 8; $i++) {
        $n = rand(0, $alphaLength);
        $pass[] = $alphabet[$n];
    }
    return implode($pass);
}

// (2) nick from email before '@'
function makeNickFromEmail(string $billing_email): string
{
    $billing_email = strtolower(trim($billing_email));
    $local = explode('@', $billing_email)[0] ?? 'user';
    $local = trim($local);
    // keep only safe characters
    $local = preg_replace('/[^a-z0-9._]/', '', $local);
    if ($local === '') $local = 'user';
    return $local;
}

// ensure unique nick_name
function makeUniqueNick(mysqli $mysqli, string $baseNick): string
{
    $nick = $baseNick;
    $i = 0;
    while (true) {
        $nickEsc = $mysqli->real_escape_string($nick);
        $res = $mysqli->query("SELECT 1 FROM user WHERE nick_name='$nickEsc' LIMIT 1");
        if ($res && $res->num_rows == 0) return $nick;

        $i++;
        $nick = $baseNick . $i; // base, base1, base2 ...
        if ($i > 50) return $baseNick . rand(1000, 9999);
    }
}

function getBonusSponsorPercent()
{
    $res = cron_query("SELECT bonus_sponsor FROM system_setting WHERE status='1' ORDER BY id DESC LIMIT 1", ["fn" => "getBonusSponsorPercent"]);
    if ($res && $res->num_rows > 0) {
        $row = $res->fetch_assoc();
        return (float)($row['bonus_sponsor'] ?? 0);
    }
    return 0;
}

function getHibahUnilevelPercentSetting()
{
    $setting = ['level_1' => 0, 'level_2' => 0, 'level_3' => 0];
    $res = cron_query("SELECT level_1, level_2, level_3 FROM hibah_unilevel_setting WHERE status='1' ORDER BY id DESC LIMIT 1", ["fn" => "getHibahUnilevelPercentSetting"]);
    if ($res && $res->num_rows > 0) {
        $row = $res->fetch_assoc();
        $setting['level_1'] = (float)$row['level_1'];
        $setting['level_2'] = (float)$row['level_2'];
        $setting['level_3'] = (float)$row['level_3'];
    }
    return $setting;
}

function getSponsorCodeByRefCode($ref_code)
{
    global $mysqli;
    $ref_code = $mysqli->real_escape_string($ref_code);

    $res = cron_query("SELECT sponsor_code FROM user WHERE ref_code='$ref_code' AND status='1' LIMIT 1", ["fn" => "getSponsorCodeByRefCode", "ref_code" => $ref_code]);
    if ($res && $res->num_rows > 0) {
        $row = $res->fetch_assoc();
        return $row['sponsor_code'] ?? '';
    }
    return '';
}

// (5) Sales check: woocommerce_order_info OR transactions bonus/hibah in current month
function agentHasSalesThisMonth($agent_ref_code)
{
    global $mysqli;
    $agent_ref_code = $mysqli->real_escape_string($agent_ref_code);

    $first = date("Y-m-01 00:00:00");
    $last  = date("Y-m-t 23:59:59");

    // A) original check from woocommerce_order_info
    $sql1 = "SELECT 1
             FROM woocommerce_order_info
             WHERE status='1'
               AND order_comm_ref_code='$agent_ref_code'
               AND created_date BETWEEN '$first' AND '$last'
             LIMIT 1";
    $res1 = cron_query($sql1, ["fn" => "agentHasSalesThisMonth.order", "agent_ref_code" => $agent_ref_code]);
    if ($res1 && $res1->num_rows > 0) return true;

    // B) NEW check from transactions
    $sql2 = "SELECT 1
             FROM transactions
             WHERE status='1'
               AND user_ref_code='$agent_ref_code'
               AND created_date BETWEEN '$first' AND '$last'
               AND (type_of_process='bonus_sponsor' OR type_of_process LIKE 'hibah_unilevel_level_%')
             LIMIT 1";
    $res2 = cron_query($sql2, ["fn" => "agentHasSalesThisMonth.txn", "agent_ref_code" => $agent_ref_code]);
    return ($res2 && $res2->num_rows > 0);
}

function findEligibleAgentCompressed($candidate_ref_code)
{
    $maxHop = 30;
    while ($maxHop-- > 0) {
        if ($candidate_ref_code == '' || $candidate_ref_code == 'MGP10000') return '';
        if (agentHasSalesThisMonth($candidate_ref_code)) return $candidate_ref_code;
        $candidate_ref_code = getSponsorCodeByRefCode($candidate_ref_code);
    }
    return '';
}

// bonus name for email (4)
function bonusNameFromProcess(string $type_of_process): string
{
    if ($type_of_process === 'bonus_sponsor') return 'Bonus Sponsor';
    if ($type_of_process === 'hibah_unilevel_level_1') return 'Hibah Unilevel Level 1';
    if ($type_of_process === 'hibah_unilevel_level_2') return 'Hibah Unilevel Level 2';
    if ($type_of_process === 'hibah_unilevel_level_3') return 'Hibah Unilevel Level 3';
    return $type_of_process;
}

// send login email (3)
function emailLoginInfo($billing_email, $name_new_user, $nick_name_new, $password_new_user)
{
    $to = $billing_email;
    $subject = "Hi $name_new_user mygoldsdnbhd.com Account Verification";

    $message = "
    <html>
    <head>
    <title>Hi $name_new_user, mygoldsdnbhd.com Account Verification</title>
    </head>
    <body>
    <p>
    Hai $name_new_user,<br><br>

    Thank you for your purchase with mygoldsdnbhd.com<br><br>

    This is your account info :<br><br>

    Full Name : ".$name_new_user."<br>
    Email : ".$billing_email."<br>
    Username : ".$nick_name_new."<br>
    Password : ".$password_new_user."<br><br>

    Please Click This Link To Login: https://mygoldsdnbhd.com/log-masuk/<br><br>
    </p>

    </body>
    </html>
    ";

    $headers  = "MIME-Version: 1.0\r\n";
    $headers .= "Content-type:text/html;charset=UTF-8\r\n";
    $headers .= "From: mygoldsdnbhd.com <no-reply@mygoldsdnbhd.com>\r\n";
    $headers .= "Reply-To: no-reply@mygoldsdnbhd.com\r\n";

    @mail($to, $subject, $message, $headers);
}

// send bonus email to agent (4)
function emailBonusInfo($email, $name, $type_of_process, $amt, $order_id, $order_item_name)
{
    $bonus_name = bonusNameFromProcess($type_of_process);
    $amtFmt = number_format((float)$amt, 2);

    $to = $email;
    $subject = "Hi $name mygoldsdnbhd.com Bonus Info";

    $message = "
    <html>
    <head>
    <title>Hi $name, mygoldsdnbhd.com Bonus Info</title>
    </head>
    <body>
    <p>

    Hai $name,<br><br>

    This is your Bonus Info :<br><br>

    Bonus Name : ".$bonus_name."<br>
    Amount : USD ".$amtFmt."<br>
    Order ID:".$order_id." | ".$order_item_name."<br><br>

    Please Login to your UMH dashboard to view more detail.
    </p>

    </body>
    </html>
    ";

    $headers  = "MIME-Version: 1.0\r\n";
    $headers .= "Content-type:text/html;charset=UTF-8\r\n";
    $headers .= "From: mygoldsdnbhd.com <no-reply@mygoldsdnbhd.com>\r\n";
    $headers .= "Reply-To: no-reply@mygoldsdnbhd.com\r\n";

    @mail($to, $subject, $message, $headers);
}

// add commission + email bonus (4)
// NOTE: extended signature to accept order_id + order_item_name
function addWalletCommission($agent_ref_code, $amount, $trans_code, $type_of_process, $description, $order_id, $order_item_name)
{
    global $mysqli;

    $amount = (float)$amount;
    if ($amount <= 0) return true;

    $agent_ref_code_esc = $mysqli->real_escape_string($agent_ref_code);
    $trans_code_esc = $mysqli->real_escape_string($trans_code);
    $type_of_process_esc = $mysqli->real_escape_string($type_of_process);
    $description_esc = $mysqli->real_escape_string($description);

    // wallet must exist
    $resW = cron_query("SELECT cash_wallet FROM wallet WHERE ref_code='$agent_ref_code_esc' LIMIT 1", [
        "fn" => "addWalletCommission.selectWallet",
        "agent_ref_code" => $agent_ref_code_esc
    ]);
    if (!$resW || $resW->num_rows == 0) {
        cron_log("NO_WALLET_ROW", ["agent_ref_code" => $agent_ref_code_esc, "amount" => $amount]);
        return false;
    }

    $rowW = $resW->fetch_assoc();
    $current = (float)$rowW['cash_wallet'];
    $newBal = (float)number_format($current + $amount, 2, '.', '');

    // update wallet
    $upd = cron_query("UPDATE wallet SET cash_wallet='$newBal' WHERE ref_code='$agent_ref_code_esc'", [
        "fn" => "addWalletCommission.updateWallet",
        "agent_ref_code" => $agent_ref_code_esc,
        "amount" => $amount
    ]);
    if (!$upd) return false;

    $created_date = date('Y-m-d H:i:s');

    // insert transaction
    $ins = "INSERT INTO transactions
    (
        user_ref_code, trans_code, type_of_process, type_of_transaction,
        in_transactions, out_transactions, balance, description, status, created_date
    )
    VALUES
    (
        '$agent_ref_code_esc', '$trans_code_esc', '$type_of_process_esc', 'In',
        '$amount', '0', '$newBal', '$description_esc', '1', '$created_date'
    )";
    $ok = cron_query($ins, [
        "fn" => "addWalletCommission.insertTxn",
        "agent_ref_code" => $agent_ref_code_esc
    ]);
    if ($ok === false) return false;

    // (4) Email bonus info to agent
    $resAgent = cron_query("SELECT email, name FROM user WHERE ref_code='$agent_ref_code_esc' AND status='1' LIMIT 1", [
        "fn" => "addWalletCommission.agentEmail",
        "agent_ref_code" => $agent_ref_code_esc
    ]);
    if ($resAgent && $resAgent->num_rows > 0) {
        $rowA = $resAgent->fetch_assoc();
        $email = $rowA['email'] ?? '';
        $name = $rowA['name'] ?? '';
        if ($email != '' && $name != '') {
            emailBonusInfo($email, $name, $type_of_process, $amount, (int)$order_id, $order_item_name);
        }
    }

    return true;
}

function distributeCommissionCompressed($order_comm_ref_code, $item_total_price, $trans_code, $order_id, $order_item_name)
{
    $item_total_price = (float)$item_total_price;
    if ($item_total_price <= 0) return true;

    // A) Bonus Sponsor (%) -> agent level 1 only
    $bonusPct = getBonusSponsorPercent();
    if ($bonusPct > 0) {
        $bonusAmt = (float)number_format($item_total_price * ($bonusPct / 100.0), 2, '.', '');
        $ok = addWalletCommission(
            $order_comm_ref_code,
            $bonusAmt,
            $trans_code,
            'bonus_sponsor',
            "Bonus Sponsor ($bonusPct%) USD $bonusAmt for Order ID:$order_id | $order_item_name",
            $order_id,
            $order_item_name
        );
        if (!$ok) return false;
    }

    // B) Hibah Unilevel 3 levels with compression
    $setting = getHibahUnilevelPercentSetting();
    $percents = [
        1 => (float)$setting['level_1'],
        2 => (float)$setting['level_2'],
        3 => (float)$setting['level_3'],
    ];

    $cursor = $order_comm_ref_code;

    for ($lvl = 1; $lvl <= 3; $lvl++) {

        if ($lvl > 1) {
            $cursor = getSponsorCodeByRefCode($cursor);
        }

        $eligible = findEligibleAgentCompressed($cursor);
        if ($eligible == '') break;

        $pct = $percents[$lvl];
        if ($pct > 0) {
            $amt = (float)number_format($item_total_price * ($pct / 100.0), 2, '.', '');
            $ok = addWalletCommission(
                $eligible,
                $amt,
                $trans_code,
                "hibah_unilevel_level_$lvl",
                "Hibah Unilevel L$lvl ($pct%) USD $amt for Order ID:$order_id | $order_item_name",
                $order_id,
                $order_item_name
            );
            if (!$ok) return false;
        }

        // continue chain from compressed eligible
        $cursor = $eligible;
    }

    return true;
}

// --------------------------------------------------
// MAIN PROCESS
// --------------------------------------------------
cron_log("CRON_START");

$sql_orders = "SELECT
p.ID as order_id,
p.post_date,
MAX(CASE WHEN pm.meta_key = '_billing_email' AND p.ID = pm.post_id THEN pm.meta_value END) as billing_email,
MAX(CASE WHEN pm.meta_key = '_billing_phone' AND p.ID = pm.post_id THEN pm.meta_value END) as billing_phone,
MAX(CASE WHEN pm.meta_key = '_billing_first_name' AND p.ID = pm.post_id THEN pm.meta_value END) as billing_first_name,
MAX(CASE WHEN pm.meta_key = '_billing_last_name' AND p.ID = pm.post_id THEN pm.meta_value END) as billing_last_name,
MAX(CASE WHEN pm.meta_key = '_billing_address_1' AND p.ID = pm.post_id THEN pm.meta_value END) as billing_address_1,
MAX(CASE WHEN pm.meta_key = '_billing_address_2' AND p.ID = pm.post_id THEN pm.meta_value END) as billing_address_2,
MAX(CASE WHEN pm.meta_key = '_billing_city' AND p.ID = pm.post_id THEN pm.meta_value END) as billing_city,
MAX(CASE WHEN pm.meta_key = '_billing_state' AND p.ID = pm.post_id THEN pm.meta_value END) as billing_state,
MAX(CASE WHEN pm.meta_key = '_billing_country' AND p.ID = pm.post_id THEN pm.meta_value END) as billing_country,
MAX(CASE WHEN pm.meta_key = '_billing_postcode' AND p.ID = pm.post_id THEN pm.meta_value END) as billing_postcode,
MAX(CASE WHEN pm.meta_key = '_shipping_first_name' AND p.ID = pm.post_id THEN pm.meta_value END) as shipping_first_name,
MAX(CASE WHEN pm.meta_key = '_shipping_last_name' AND p.ID = pm.post_id THEN pm.meta_value END) as shipping_last_name,
MAX(CASE WHEN pm.meta_key = '_shipping_address_1' AND p.ID = pm.post_id THEN pm.meta_value END) as shipping_address_1,
MAX(CASE WHEN pm.meta_key = '_shipping_address_2' AND p.ID = pm.post_id THEN pm.meta_value END) as shipping_address_2,
MAX(CASE WHEN pm.meta_key = '_shipping_city' AND p.ID = pm.post_id THEN pm.meta_value END) as shipping_city,
MAX(CASE WHEN pm.meta_key = '_shipping_state' AND p.ID = pm.post_id THEN pm.meta_value END) as shipping_state,
MAX(CASE WHEN pm.meta_key = '_shipping_postcode' AND p.ID = pm.post_id THEN pm.meta_value END) as shipping_postcode,
MAX(CASE WHEN pm.meta_key = '_order_total' AND p.ID = pm.post_id THEN pm.meta_value END) as order_total,
MAX(CASE WHEN pm.meta_key = '_order_tax' AND p.ID = pm.post_id THEN pm.meta_value END) as order_tax,
MAX(CASE WHEN pm.meta_key = '_paid_date' AND p.ID = pm.post_id THEN pm.meta_value END) as paid_date,
MAX(CASE WHEN pm.meta_key = '_ref_code' AND p.ID = pm.post_id THEN pm.meta_value END) as ref_code,
(SELECT GROUP_CONCAT(order_item_name SEPARATOR '|') FROM wp1o_woocommerce_order_items WHERE order_id = p.ID) as order_items
FROM wp1o_posts p
JOIN wp1o_postmeta pm ON p.ID = pm.post_id
JOIN wp1o_woocommerce_order_items oi ON p.ID = oi.order_id
WHERE post_type = 'shop_order'
AND post_status IN ('wc-completed','wc-processing')
AND p.to_ping != '1'
GROUP BY p.ID";

$orders = cron_query($sql_orders, ["step" => "select_orders"]);
if (!$orders) { cron_log("CRON_ABORT_SELECT_ORDERS"); exit; }

while ($o = $orders->fetch_assoc()) {

    $order_id = $o['order_id'];
    $post_date = $o['post_date'];
    $billing_email = trim(strtolower($o['billing_email'] ?? ''));
    $billing_phone = $o['billing_phone'];
    $billing_first_name = $o['billing_first_name'];
    $billing_last_name = $o['billing_last_name'];
    $billing_address_1 = $o['billing_address_1'];
    $billing_address_2 = $o['billing_address_2'];
    $billing_city = $o['billing_city'];
    $billing_state = $o['billing_state'];
    $billing_country = $o['billing_country'];
    $billing_postcode = $o['billing_postcode'];
    $shipping_first_name = $o['shipping_first_name'];
    $shipping_last_name = $o['shipping_last_name'];
    $shipping_address_1 = $o['shipping_address_1'];
    $shipping_address_2 = $o['shipping_address_2'];
    $shipping_city = $o['shipping_city'];
    $shipping_state = $o['shipping_state'];
    $shipping_postcode = $o['shipping_postcode'];
    $order_total = (float)$o['order_total'];
    $order_tax = $o['order_tax'];
    $paid_date = $o['paid_date'];
    $order_items = $o['order_items'];
    $order_comm_ref_code_nick_name = $o['ref_code'];

    // sponsor ref_code from sponsor nick_name
    $order_comm_ref_code = '';
    $sql = "SELECT ref_code 
            FROM user
            WHERE nick_name='$order_comm_ref_code_nick_name'
            AND status='1'
            LIMIT 1";
    $result = $mysqli->query($sql);
    if ($result && $result->num_rows > 0) {
        $row = $result->fetch_assoc();
        $order_comm_ref_code = $row['ref_code'];
    }

    if ($order_comm_ref_code == '') {
        cron_log("SPONSOR_NICKNAME_NOT_FOUND", ["order_id" => $order_id, "ref_code_meta" => $order_comm_ref_code_nick_name]);
        // Do not mark as done because sponsor mapping missing
        continue;
    }

    $created_date = date('Y-m-d H:i:s');
    $trans_code = randomRefferalCode();
    $status = 1;

    $mysqli->begin_transaction();

    // skip if already inserted
    $chk = cron_query("SELECT 1 FROM woocommerce_order_info WHERE order_id='$order_id' LIMIT 1", ["order_id" => $order_id, "step" => "check_existing"]);
    if ($chk && $chk->num_rows > 0) {
        $mysqli->rollback();
        continue;
    }

    // currency conversion
    $deposit_rm = 1;
    $cur = cron_query("SELECT deposit_rm FROM currency_rate_setting WHERE status='1' LIMIT 1", ["order_id" => $order_id, "step" => "currency"]);
    if ($cur && $cur->num_rows > 0) {
        $deposit_rm = (float)$cur->fetch_assoc()['deposit_rm'];
        if ($deposit_rm <= 0) $deposit_rm = 1;
    }

    if ($billing_country == "MY") {
        $order_total = $order_total / $deposit_rm;
    }
    $order_total = (float)number_format($order_total, 2, '.', '');

    // Umrah date = 1 year after created_date
    // If your umrah_date column is DATE, use Y-m-d
    $umrah_date = date('Y-m-d', strtotime($created_date . ' +1 year'));

    // Insert woocommerce_order_info
    $sql_info = "INSERT INTO woocommerce_order_info
    (
        order_id, trans_code, post_date, billing_email, billing_phone, billing_first_name, billing_last_name,
        billing_address_1, billing_address_2, billing_city, billing_state, billing_postcode,
        shipping_first_name, shipping_last_name, shipping_address_1, shipping_address_2, shipping_city, shipping_state, shipping_postcode,
        order_total, order_tax, paid_date, order_items, order_comm_ref_code, umrah_date, status, created_date
    )
    VALUES
    (
        '$order_id', '$trans_code', '$post_date', '$billing_email', '$billing_phone', '$billing_first_name', '$billing_last_name',
        '$billing_address_1', '$billing_address_2', '$billing_city', '$billing_state', '$billing_postcode',
        '$shipping_first_name', '$shipping_last_name', '$shipping_address_1', '$shipping_address_2', '$shipping_city', '$shipping_state', '$shipping_postcode',
        '$order_total', '$order_tax', '$paid_date', '$order_items', '$order_comm_ref_code', '$umrah_date', '$status', '$created_date'
    )";

    $res_sale = cron_query($sql_info, ["order_id" => $order_id, "step" => "insert_woocommerce_order_info"]);
    if (!$res_sale) { $mysqli->rollback(); continue; }

    // Find FIRST SKU that exists in package_setting (to get package_price & auto_wallet)
    $package_price = 0.00;
    $auto_wallet = 0.00;
    $first_matched_item_name = "";

    $orderItemRows = cron_query("SELECT order_item_id, order_item_name
                                FROM wp1o_woocommerce_order_items
                                WHERE order_id='$order_id'
                                AND order_item_name != 'Free shipping'
                                ORDER BY order_item_id ASC", ["order_id" => $order_id, "step" => "order_items"]);
    if (!$orderItemRows) { $mysqli->rollback(); continue; }

    while ($it = $orderItemRows->fetch_assoc()) {
        $tmp_order_item_id = $it['order_item_id'];
        $tmp_order_item_name = $it['order_item_name'];

        $pid = cron_query("SELECT product_id FROM wp1o_wc_order_product_lookup WHERE order_item_id='$tmp_order_item_id' LIMIT 1",
                          ["order_id" => $order_id, "step" => "product_id", "order_item_id" => $tmp_order_item_id]);
        if (!$pid || $pid->num_rows == 0) continue;
        $item_product_id = $pid->fetch_assoc()['product_id'];

        $skuRes = cron_query("SELECT meta_value FROM wp1o_postmeta WHERE meta_key='_sku' AND post_id='$item_product_id' LIMIT 1",
                             ["order_id" => $order_id, "step" => "sku", "product_id" => $item_product_id]);
        if (!$skuRes || $skuRes->num_rows == 0) continue;
        $sku = $skuRes->fetch_assoc()['meta_value'];
        if ($sku == "") continue;

        $pkgRes = cron_query("SELECT * FROM package_setting WHERE package_ref_code='$sku' AND status='1' LIMIT 1",
                             ["order_id" => $order_id, "step" => "package_setting", "sku" => $sku]);
        if ($pkgRes && $pkgRes->num_rows > 0) {
            $pkg = $pkgRes->fetch_assoc();
            $package_price = (float)$pkg['package_price'];
            $aw1 = (float)$pkg['package_auto_wallet_1'];
            $aw2 = (float)$pkg['package_auto_wallet_2'];

            if ($billing_country == "MY") {
                $package_price = $package_price / $deposit_rm;
                $aw1 = $aw1 / $deposit_rm;
                $aw2 = $aw2 / $deposit_rm;
            }

            $pilihan1 = (strpos($tmp_order_item_name, "Pilihan 1") !== false);
            $auto_wallet = $pilihan1 ? $aw1 : $aw2;

            $package_price = (float)number_format($package_price, 2, '.', '');
            $auto_wallet = (float)number_format($auto_wallet, 2, '.', '');
            $first_matched_item_name = $tmp_order_item_name;
            break;
        }
    }

    if ($package_price <= 0) {
        cron_log("NO_PACKAGE_MATCH", ["order_id" => $order_id, "order_comm_ref_code" => $order_comm_ref_code]);
        $mysqli->rollback();
        continue;
    }

    // Sponsor must exist
    $sponsorRes = cron_query("SELECT user_id, ref_code FROM user WHERE ref_code='$order_comm_ref_code' AND status='1' LIMIT 1",
                             ["order_id" => $order_id, "step" => "check_sponsor", "order_comm_ref_code" => $order_comm_ref_code]);
    if (!$sponsorRes || $sponsorRes->num_rows == 0) {
        cron_log("SPONSOR_NOT_FOUND", ["order_id" => $order_id, "order_comm_ref_code" => $order_comm_ref_code]);
        $mysqli->rollback();
        continue;
    }
    $sponsorRow = $sponsorRes->fetch_assoc();
    $sponsor_id = $sponsorRow['user_id'];
    $sponsor_code = $sponsorRow['ref_code'];

    // ==============================
    // Create customer ONCE per order
    // ==============================
    // (1) Do NOT create if user already exists by billing_email
    // (2) nick_name from billing_email before '@'
    $createdNewCustomer = false;

    $New_ref_code = '';
    $New_ref_code_count = 0;
    $username_new = '';
    $password_new_user = '';
    $name_new_user = trim($billing_first_name." ".$billing_last_name);

    $nick_base = makeNickFromEmail($billing_email);
    $nick_name_new = makeUniqueNick($mysqli, $nick_base);

    // check if customer exists
    $emailEsc = $mysqli->real_escape_string($billing_email);
    $existUser = cron_query("SELECT ref_code, nick_name, name, password FROM user WHERE LOWER(email)=LOWER('$emailEsc') AND status='1' LIMIT 1",
                            ["order_id" => $order_id, "step" => "check_existing_customer_email"]);

    if ($existUser && $existUser->num_rows > 0) {
        $rowEU = $existUser->fetch_assoc();
        $New_ref_code = $rowEU['ref_code'];             // use existing customer ref_code
        $username_new = $New_ref_code;
        // keep their existing nick_name (do not override)
        $nick_name_new = $rowEU['nick_name'] ?? $nick_name_new;

        // ensure wallet exists
        $wchk = cron_query("SELECT 1 FROM wallet WHERE ref_code='$New_ref_code' LIMIT 1",
                           ["order_id" => $order_id, "step" => "check_wallet_existing", "ref_code" => $New_ref_code]);
        if (!$wchk || $wchk->num_rows == 0) {
            $res_wallet = cron_query("INSERT INTO wallet (ref_code, cash_wallet, status, created_date) VALUES ('$New_ref_code', '0', '1', '$created_date')",
                                     ["order_id" => $order_id, "step" => "insert_wallet_for_existing", "ref_code" => $New_ref_code]);
            if (!$res_wallet) { $mysqli->rollback(); continue; }
        }

        // mark these as OK so commit condition doesn�t fail
        $res_user = true;
        $res_wallet = true;

    } else {
        // create new customer
        $createdNewCustomer = true;

        // next UHMC number
        $New_ref_code_count = 10001;
        $lastUser = cron_query("SELECT ref_code FROM user WHERE status='1' AND ref_code LIKE 'MGPC%' ORDER BY user_id DESC LIMIT 1",
                               ["order_id" => $order_id, "step" => "last_user"]);
        if ($lastUser && $lastUser->num_rows > 0) {
            $Last_ref_code = $lastUser->fetch_assoc()['ref_code'];
            $numPart = preg_replace('/\D+/', '', $Last_ref_code);
            if ($numPart != '') $New_ref_code_count = (int)$numPart + 1;
        }
        $New_ref_code = "UHMC".$New_ref_code_count;
        $username_new = $New_ref_code;

        $level_user = 2;
        $password_new_user = randomRefferalCode();

        $address = trim($billing_address_1." ".$billing_address_2);

        $tmp_phone = $billing_phone;
        if (substr($tmp_phone, 0, 1) == "0") $tmp_phone = substr($tmp_phone, 1);
        $phone_number_new_user = "60".$tmp_phone;

        $sql_user = "INSERT INTO user
        (
            level, ref_code, trans_code, user_id, username, nick_name,
            sponsor_id, sponsor_code, email, password, name, phone_number,
            address, state, area, postcode, country, country_static,
            activation_status, verification_status, status, created_date
        )
        VALUES
        (
            '$level_user', '$New_ref_code', '$trans_code', '$New_ref_code_count', '$username_new', '$nick_name_new',
            '$sponsor_id', '$sponsor_code', '$billing_email', '$password_new_user', '$name_new_user', '$phone_number_new_user',
            '$address', '$billing_state', '$billing_city', '$billing_postcode', '$billing_country', '$billing_country',
            '1', '1', '1', '$created_date'
        )";
        $res_user = cron_query($sql_user, ["order_id" => $order_id, "step" => "insert_user", "new_ref_code" => $New_ref_code]);
        if (!$res_user) { $mysqli->rollback(); continue; }

        $sql_wallet = "INSERT INTO wallet (ref_code, cash_wallet, status, created_date)
                       VALUES ('$New_ref_code', '0', '1', '$created_date')";
        $res_wallet = cron_query($sql_wallet, ["order_id" => $order_id, "step" => "insert_wallet", "new_ref_code" => $New_ref_code]);
        if (!$res_wallet) { $mysqli->rollback(); continue; }

        // (3) Email login info ONLY when new user created
        emailLoginInfo($billing_email, $name_new_user, $nick_name_new, $password_new_user);
    }

    // Payment ONCE per order (amount = order_total)
    $payment_amount = $order_total;
    $balance = (float)number_format(($package_price - $payment_amount), 2, '.', '');

    $sql_payment = "INSERT INTO package_payment
    (
        order_id, trans_code, ref_code, full_amount, amount, balance, payment_method, status, created_date
    )
    VALUES
    (
        '$order_id', '$trans_code', '$New_ref_code', '$package_price', '$payment_amount', '$balance', 'Website', '1', '$created_date'
    )";
    $res_payment = cron_query($sql_payment, ["order_id" => $order_id, "step" => "insert_payment", "new_ref_code" => $New_ref_code]);
    if (!$res_payment) { $mysqli->rollback(); continue; }

    // Per-item loop: insert woocommerce_order_info_item + commissions
    $res_order_info_item = true;

    $items2 = cron_query("SELECT order_item_id, order_item_name
                          FROM wp1o_woocommerce_order_items
                          WHERE order_id='$order_id'
                          AND order_item_name != 'Free shipping'
                          ORDER BY order_item_id ASC", ["order_id" => $order_id, "step" => "items2"]);
    if (!$items2) { $mysqli->rollback(); continue; }

    while ($it2 = $items2->fetch_assoc()) {

        $order_item_id = $it2['order_item_id'];
        $order_item_name = $it2['order_item_name'];

        $pid2 = cron_query("SELECT product_id FROM wp1o_wc_order_product_lookup WHERE order_item_id='$order_item_id' LIMIT 1",
                           ["order_id" => $order_id, "step" => "pid2", "order_item_id" => $order_item_id]);
        if (!$pid2 || $pid2->num_rows == 0) continue;
        $item_product_id = $pid2->fetch_assoc()['product_id'];

        $sku2 = cron_query("SELECT meta_value FROM wp1o_postmeta WHERE meta_key='_sku' AND post_id='$item_product_id' LIMIT 1",
                           ["order_id" => $order_id, "step" => "sku2", "product_id" => $item_product_id]);
        if (!$sku2 || $sku2->num_rows == 0) continue;
        $order_item_sku = $sku2->fetch_assoc()['meta_value'];
        if ($order_item_sku == "") continue;

        $info2 = cron_query("SELECT
                pm.order_item_id,
                MAX(CASE WHEN pm.meta_key = '_line_subtotal' THEN pm.meta_value END) as _line_subtotal,
                MAX(CASE WHEN pm.meta_key = '_qty' THEN pm.meta_value END) as _qty
            FROM wp1o_woocommerce_order_itemmeta pm
            WHERE pm.order_item_id = '$order_item_id'
            LIMIT 1", ["order_id" => $order_id, "step" => "item_info", "order_item_id" => $order_item_id]);

        $item_total_price = 0.00;
        $item_quantity = 1;
        if ($info2 && $info2->num_rows > 0) {
            $r = $info2->fetch_assoc();
            $item_total_price = (float)$r['_line_subtotal'];
            $item_quantity = (int)($r['_qty'] ?? 1);
            if ($item_quantity <= 0) $item_quantity = 1;
            if ($billing_country == "MY") {
                $item_total_price = $item_total_price / $deposit_rm;
            }
        }
        $item_total_price = (float)number_format($item_total_price, 2, '.', '');

        $sql_item = "INSERT INTO woocommerce_order_info_item
        (
            order_id, ref_code, trans_code, item_id, item_name, item_quantity,
            item_total_price, item_nilai_mata, type_of_transaction, status, created_date
        )
        VALUES
        (
            '$order_id', '$order_comm_ref_code', '$trans_code', '$item_product_id', '$order_item_name', '$item_quantity',
            '$item_total_price', '$order_item_sku', 'In', '$status', '$created_date'
        )";
        $res_order_info_item = cron_query($sql_item, ["order_id" => $order_id, "step" => "insert_item", "order_item_id" => $order_item_id]);
        if (!$res_order_info_item) { $mysqli->rollback(); continue 2; }

        // Commission + email bonus info inside addWalletCommission()
        $okCommission = distributeCommissionCompressed($order_comm_ref_code, $item_total_price, $trans_code, $order_id, $order_item_name);
        if (!$okCommission) { $mysqli->rollback(); continue 2; }
    }

    // Post updates + Auto-wallet + mark order as done
    $u1 = cron_query("UPDATE woocommerce_order_info SET new_ref_code='$New_ref_code', order_full_total='$package_price' WHERE trans_code='$trans_code'",
                     ["order_id" => $order_id, "step" => "update_order_info"]);
    $u2 = cron_query("UPDATE wallet SET cash_wallet='$auto_wallet' WHERE ref_code='$New_ref_code'",
                     ["order_id" => $order_id, "step" => "update_new_wallet"]);

    $desc_auto = "Auto E-Wallet Comission USD $auto_wallet For Purchase Of $first_matched_item_name";
    $sql_txn_auto = "INSERT INTO transactions
    (
        user_ref_code, trans_code, type_of_process, type_of_transaction,
        in_transactions, out_transactions, balance, description, status, created_date
    )
    VALUES
    (
        '$New_ref_code', '$trans_code', 'Auto_Wallet', 'CW',
        '$auto_wallet', '0', '$auto_wallet', '$desc_auto', '1', '$created_date'
    )";
    $u3 = cron_query($sql_txn_auto, ["order_id" => $order_id, "step" => "insert_auto_wallet_txn"]);

    $u4 = cron_query("UPDATE wp1o_posts SET to_ping='1' WHERE id='$order_id'",
                     ["order_id" => $order_id, "step" => "mark_done"]);

    // Note: res_user/res_wallet may be TRUE when customer already exists
    if ($res_sale && $res_user && $res_wallet && $res_payment && $res_order_info_item && $u1 && $u2 && $u3 && $u4) {
        $mysqli->commit();
        cron_log("ORDER_COMMIT_OK", ["order_id" => $order_id, "new_ref_code" => $New_ref_code, "sponsor" => $order_comm_ref_code]);
    } else {
        $mysqli->rollback();
        cron_log("ORDER_ROLLBACK", ["order_id" => $order_id, "new_ref_code" => $New_ref_code, "sponsor" => $order_comm_ref_code]);
    }
}

cron_log("CRON_END");
?>