<?php
// =======================================================
// cron_transfer_order_to_db_from_registration.php
// - No wp1o_* tables
// - Source = activated user + successful payment (registration)
// - Package info = payment.reference -> package_setting.package_ref_code
// - Insert woocommerce_order_info + woocommerce_order_info_item
// - Commission:
//    bonus_sponsor from package_setting.bonus_sponsor
//    hibah_unilevel_1/2/3 from package_setting
// - Compression sales check uses TRANSACTIONS (bonus_sponsor / hibah_unilevel_level_%)
// =======================================================

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

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

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

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

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

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", $e);
    }
});

// ------------------------
// 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");

function cron_query($sql, $ctx = [])
{
    global $mysqli;
    $res = $mysqli->query($sql);
    if ($res === false) {
        cron_log("MYSQL_ERROR", array_merge($ctx, ["error" => $mysqli->error, "sql" => $sql]));
    }
    return $res;
}

// ------------------------
// COMMISSION HELPERS
// ------------------------
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"]);
    if ($res && $res->num_rows > 0) return $res->fetch_assoc()['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 '';
}

function addWalletCommission($agent_ref_code, $amount, $trans_code, $type_of_process, $description)
{
    global $mysqli;
    $amount = (float)$amount;
    if ($amount <= 0) return true;

    $agent_ref_code = $mysqli->real_escape_string($agent_ref_code);
    $trans_code = $mysqli->real_escape_string($trans_code);
    $type_of_process = $mysqli->real_escape_string($type_of_process);
    $description = $mysqli->real_escape_string($description);

    $resW = cron_query("SELECT cash_wallet FROM wallet WHERE ref_code='$agent_ref_code' LIMIT 1", ["fn"=>"addWalletCommission.wallet"]);
    if (!$resW || $resW->num_rows == 0) return false;

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

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

    $created_date = date('Y-m-d H:i:s');
    $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', '$trans_code', '$type_of_process', 'In', '$amount', '0', '$newBal', '$description', '1', '$created_date')";
    $ok = cron_query($ins, ["fn"=>"addWalletCommission.insertTxn"]);
    return ($ok !== false);
}

function distributeCommissionCompressed($order_comm_ref_code, $paid_amount_usd, $trans_code, $order_id, $order_item_name, $bonusPct, $h1, $h2, $h3)
{
    $paid_amount_usd = (float)$paid_amount_usd;
    if ($paid_amount_usd <= 0) return true;

    // =====================================================
    // NEW RULES:
    // Level 1 -> Bonus Sponsor only (to direct sponsor)
    // Level 2 -> Hibah uses hibah_unilevel_1
    // Level 3 -> Hibah uses hibah_unilevel_2
    // Level 4 -> Hibah uses hibah_unilevel_3
    // =====================================================

    // A) BONUS SPONSOR (Level 1) -> direct sponsor ONLY
    $bonusPct = (float)$bonusPct;
    if ($bonusPct > 0) {
        $bonusAmt = (float)number_format($paid_amount_usd * ($bonusPct / 100.0), 2, '.', '');
        $ok = addWalletCommission(
            $order_comm_ref_code,
            $bonusAmt,
            $trans_code,
            'bonus_sponsor',
            "Bonus Sponsor ($bonusPct%) USD $bonusAmt from paid amount USD $paid_amount_usd | Order ID:$order_id | $order_item_name"
        );
        if (!$ok) return false;
    }

    // B) HIBAH UNILEVEL starts from sponsor's sponsor (Level 2+)
    $hibahPercents = [
        2 => (float)$h1, // Level 2 gets hibah_unilevel_1 percent
        3 => (float)$h2, // Level 3 gets hibah_unilevel_2 percent
        4 => (float)$h3, // Level 4 gets hibah_unilevel_3 percent
    ];

    // Start at sponsor's sponsor (NOT sponsor)
    $cursor = getSponsorCodeByRefCode($order_comm_ref_code);

    // Pay Level 2, 3, 4
    for ($lvl = 2; $lvl <= 4; $lvl++) {

        // if empty chain stop
        if ($cursor == '' || $cursor == 'MGP10000') break;

        // compression eligibility check (based on sales this month)
        $eligible = findEligibleAgentCompressed($cursor);
        if ($eligible == '') break;

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

        // continue chain from compressed eligible
        $cursor = getSponsorCodeByRefCode($eligible);
    }

    return true;
}

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

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

// Select activated users that have successful payment but not yet transferred into woocommerce_order_info
// Link payment via payment.trans_code = user.trans_code (your current flow)
$sql = "SELECT
            u.ref_code AS new_ref_code,
            u.trans_code AS user_trans_code,
            u.sponsor_code,
            u.email,
            u.phone_number,
            u.name,
            p.payment_code,
            p.amount AS paid_amount_usd,
            p.amount_myr,
            p.reference AS package_ref_code
        FROM user u
        JOIN payment p
          ON p.trans_code = u.trans_code
         AND p.status='1'
         AND p.payment_status='1'
         AND p.transfer_status = '0'
        WHERE u.status='1'
          AND u.activation_status='1'
          AND u.verification_status='1'
          AND NOT EXISTS (
              SELECT 1
              FROM woocommerce_order_info w
              WHERE w.trans_code = u.trans_code
                AND w.new_ref_code = u.ref_code
                AND w.status='1'
          )
        ORDER BY u.created_date ASC";

$rows = cron_query($sql, ["step"=>"select_registration_sales"]);
if (!$rows) { cron_log("CRON_ABORT_SELECT"); exit; }

while ($r = $rows->fetch_assoc()) {

    $new_ref_code = $r['new_ref_code'];          // user.ref_code (UHM...)
    $user_trans_code = $r['user_trans_code'];    // user.trans_code (old UHMC...)
    $sponsor_ref_code = $r['sponsor_code'];      // sponsor ref_code already
    $billing_email = $r['email'];
    $billing_phone = $r['phone_number'];
    $billing_first_name = $r['name'];            // using name as first_name (ok)
    $billing_last_name = "";                     // unknown -> empty
    $paid_amount_usd = (float)$r['paid_amount_usd'];
    $package_ref_code = $r['package_ref_code'];  // payment.reference
    $payment_code = $r['payment_code'];          // payment.payment_code (BillCode)

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

    // Load package_setting by package_ref_code
    $pkgRefEsc = $mysqli->real_escape_string($package_ref_code);
    $pkgRes = cron_query("SELECT * FROM package_setting WHERE package_ref_code='$pkgRefEsc' AND status='1' LIMIT 1", ["step"=>"package_setting", "package_ref_code"=>$package_ref_code]);
    if (!$pkgRes || $pkgRes->num_rows == 0) {
        cron_log("PACKAGE_NOT_FOUND", ["new_ref_code"=>$new_ref_code, "package_ref_code"=>$package_ref_code]);
        continue;
    }
    $pkg = $pkgRes->fetch_assoc();

    $package_id = (int)($pkg['id'] ?? 0);
    $package_name = $pkg['package_name'] ?? '';
    $package_price_myr = (float)($pkg['package_price'] ?? 0);

    // NEW: bonus/hibah percent from package_setting
    $bonusPct = (float)($pkg['bonus_sponsor'] ?? 0);
    $hibah1 = (float)($pkg['hibah_unilevel_1'] ?? 0);
    $hibah2 = (float)($pkg['hibah_unilevel_2'] ?? 0);
    $hibah3 = (float)($pkg['hibah_unilevel_3'] ?? 0);

    // order_full_total = package_price / deposit_rm (as required)
    $order_full_total = (float)number_format($package_price_myr / $deposit_rm, 2, '.', '');

    // order_total = paid_amount_usd (registration paid)
    $order_total = (float)number_format($paid_amount_usd, 2, '.', '');
    $order_tax = 0;

    $order_items = $package_name . "|" . $package_price_myr;

    // Build a new numeric order_id (max + 1)
    $mx = cron_query("SELECT IFNULL(MAX(order_id),0)+1 AS next_id FROM woocommerce_order_info", ["step"=>"next_order_id"]);
    $next_order_id = 1;
    if ($mx && $mx->num_rows > 0) $next_order_id = (int)$mx->fetch_assoc()['next_id'];

    // 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 (shipping fields empty)
    $mysqli->begin_transaction();

    $sql_info = "INSERT INTO woocommerce_order_info
        (order_id, trans_code, new_ref_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_full_total, order_tax, paid_date, order_items, order_comm_ref_code,
         umrah_date, status, created_date)
        VALUES
        ('$next_order_id', '$user_trans_code', '$new_ref_code', '$post_date',
         '$billing_email', '$billing_phone', '$billing_first_name', '$billing_last_name',
         '', '', '', '', '',
         '', '', '', '', '', '', '',
         '$order_total', '$order_full_total', '0', '$paid_date', '$order_items', '$sponsor_ref_code',
         '$umrah_date', '1', '$created_date')";

    $res_info = cron_query($sql_info, ["step"=>"insert_order_info", "order_id"=>$next_order_id, "new_ref_code"=>$new_ref_code]);
    if (!$res_info) { $mysqli->rollback(); continue; }

    // Insert woocommerce_order_info_item
    // Specs:
    // - order_id = (same as above)
    // - ref_code = user.ref_code
    // - trans_code = payment.payment_code
    // - item_id = package_setting.id
    // - item_name = package_setting.package_name
    // - item_quantity = 1
    // - item_total_price = payment.amount
    // - item_nilai_mata = package_setting.package_ref_code
    // - type_of_transaction = 'In'

    // Insert package_payment (so "payment history" works in system)
    $full_amount_usd = (float)$order_full_total;
    $pay_amount_usd  = (float)$paid_amount_usd;
    $balance_usd     = (float)number_format(($full_amount_usd - $pay_amount_usd), 2, '.', '');

    $sql_pkgpay = "INSERT INTO package_payment
    (
        order_id, trans_code, ref_code, full_amount, amount, balance, payment_method, status, created_date
    )
    VALUES
    (
        '$next_order_id', '$user_trans_code', '$new_ref_code', '$full_amount_usd', '$pay_amount_usd', '$balance_usd', 'Toyyibpay', '1', '$created_date'
    )";
    $res_pkgpay = cron_query($sql_pkgpay, ["step" => "insert_package_payment", "order_id" => $next_order_id, "new_ref_code" => $new_ref_code]);
    if (!$res_pkgpay) { $mysqli->rollback(); continue; }


    $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
        ('$next_order_id', '$new_ref_code', '$payment_code', '$package_id', '$package_name', '1', '$paid_amount_usd', '$package_ref_code', 'In', '1', '$created_date')";
    $res_item = cron_query($sql_item, ["step"=>"insert_order_item", "order_id"=>$next_order_id]);
    if (!$res_item) { $mysqli->rollback(); continue; }

    // Run commission using package_setting percents
    $okComm = distributeCommissionCompressed(
        $sponsor_ref_code,
        $paid_amount_usd,
        $user_trans_code,
        $next_order_id,
        "Registration | $package_name",
        $bonusPct, $hibah1, $hibah2, $hibah3
    );

    if (!$okComm) {
        $mysqli->rollback();
        cron_log("COMMISSION_FAIL", ["order_id"=>$next_order_id, "new_ref_code"=>$new_ref_code]);
        continue;
    }

    $mysqli->commit();

    $payCodeEsc = $mysqli->real_escape_string($payment_code);
    cron_query("UPDATE payment SET transfer_status='1' WHERE payment_code='$payCodeEsc'", [
        "step" => "mark_payment_transferred",
        "payment_code" => $payment_code
    ]);

    cron_log("ORDER_COMMIT_OK", ["order_id"=>$next_order_id, "new_ref_code"=>$new_ref_code, "trans_code"=>$user_trans_code]);
}

cron_log("CRON_END");
?>