<?php
/* ============================================================================
   WBMCZ — PURCHASE ENGINE
   ----------------------------------------------------------------------------
   Two kinds of product now live side by side.

     ONLINE   the customer pays and the thing is done on the website, exactly as
              it has always worked.

     VISIT    somebody has to go out to the customer. The customer pays an
              ADVANCE on the site, the seller carries out the visit, and the
              REMAINDER is taken there — in cash, or online with the office
              recording it. The order is not finished until that money is in.

   How the money is split
   ----------------------
   Say a product sells for 2,800, the seller's share is 2,200 and the company's
   is 600. Leave the advance on its default and the site asks for exactly the
   600 up front — the company's own share — and the seller collects the 2,200
   they were going to earn anyway. Nothing has to be moved afterwards; it simply
   lands in the right hands. Set a different advance and the arithmetic still
   holds, it just means a payment passes between the two of you afterwards, and
   the Seller Dues screen keeps the running total.

   Cash the seller takes is company revenue until it is handed over, so it is
   tracked separately from their earnings. Their earnings reach their wallet the
   usual way, through shares_distribute(), the moment the order completes.

   Main entry points
     buy_quote($db, $svc, $user_id)      one product, fully priced for this buyer
     cart_items($db, $user_id)           the basket, each line already priced
     buy_place_order($db, $user_id, ...) takes the money and writes the order
     buy_record_collection(...)          the seller says what they took
     buy_approve_collection(...)         the office agrees, and the order closes
     buy_seller_dues($db, $seller_id)    how much of ours that seller is holding
   ============================================================================ */

require_once __DIR__ . '/wallet.php';
require_once __DIR__ . '/shares.php';

if (!function_exists('buy_ensure_schema')) {

/* ---------------------------------------------------------------------------
   Schema, built on demand so a page can never white-screen before the
   migration has been run.
   --------------------------------------------------------------------------- */
function buy_ensure_schema($db) {
    static $done = false;
    if ($done) return true;
    $conn = $db->getConnection();

    $col = function ($t, $c, $def) use ($conn) {
        try {
            $r = $conn->query("SHOW COLUMNS FROM `$t` LIKE '$c'");
            if ($r && $r->num_rows === 0) { $conn->query("ALTER TABLE `$t` ADD COLUMN `$c` $def"); }
        } catch (\Throwable $e) {}
    };
    foreach (['seller_services', 'agency_services'] as $t) {
        $col($t, 'fulfilment',      "VARCHAR(20) NOT NULL DEFAULT 'online'");
        $col($t, 'advance_mode',    "VARCHAR(20) NOT NULL DEFAULT 'company'");
        $col($t, 'advance_amount',  "DECIMAL(10,2) NOT NULL DEFAULT 0.00");
        $col($t, 'advance_percent', "DECIMAL(5,2) NOT NULL DEFAULT 0.00");
        $col($t, 'allow_cart',      "TINYINT(1) NOT NULL DEFAULT 1");
        $col($t, 'allow_code',      "TINYINT(1) NOT NULL DEFAULT 1");
        $col($t, 'visit_note',      "VARCHAR(255) NULL");
    }
    $col('service_categories', 'fulfilment',      "VARCHAR(20) NOT NULL DEFAULT 'online'");
    $col('service_categories', 'advance_mode',    "VARCHAR(20) NOT NULL DEFAULT 'company'");
    $col('service_categories', 'advance_percent', "DECIMAL(5,2) NOT NULL DEFAULT 0.00");
    $col('service_categories', 'advance_amount',  "DECIMAL(10,2) NOT NULL DEFAULT 0.00");

    foreach ([
        'order_code' => "VARCHAR(40) NULL", 'fulfilment' => "VARCHAR(20) NOT NULL DEFAULT 'online'",
        'customer_type' => "VARCHAR(20) NOT NULL DEFAULT 'regular'", 'payment_mode' => "VARCHAR(20) NOT NULL DEFAULT 'full'",
        'mrp' => "DECIMAL(10,2) NOT NULL DEFAULT 0.00", 'base_amount' => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
        'discount_amount' => "DECIMAL(10,2) NOT NULL DEFAULT 0.00", 'paid_amount' => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
        'wallet_used' => "DECIMAL(10,2) NOT NULL DEFAULT 0.00", 'gateway_used' => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
        'due_amount' => "DECIMAL(10,2) NOT NULL DEFAULT 0.00", 'collected_amount' => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
        'collected_mode' => "VARCHAR(20) NULL", 'collected_at' => "DATETIME NULL", 'collected_by' => "INT NULL",
        'seller_payout' => "DECIMAL(10,2) NOT NULL DEFAULT 0.00", 'company_profit' => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
        'payment_code' => "VARCHAR(30) NULL", 'payment_code_status' => "VARCHAR(20) NOT NULL DEFAULT 'none'",
        'visit_status' => "VARCHAR(20) NOT NULL DEFAULT 'none'", 'visit_at' => "DATETIME NULL",
        'stage' => "VARCHAR(30) NOT NULL DEFAULT 'placed'", 'customer_name' => "VARCHAR(150) NULL",
        'customer_mobile' => "VARCHAR(25) NULL", 'address' => "VARCHAR(500) NULL", 'note' => "VARCHAR(500) NULL",
        'item_count' => "INT NOT NULL DEFAULT 1", 'updated_at' => "TIMESTAMP NULL DEFAULT NULL",
    ] as $c => $d) { $col('orders', $c, $d); }

    foreach ([
        "CREATE TABLE IF NOT EXISTS carts (
            id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, service_id INT NOT NULL,
            service_type VARCHAR(20) NOT NULL DEFAULT 'seller', qty INT NOT NULL DEFAULT 1,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_cart (user_id, service_id, service_type), KEY idx_cart_user (user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        "CREATE TABLE IF NOT EXISTS order_items (
            id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, service_id INT NOT NULL,
            service_type VARCHAR(20) NOT NULL DEFAULT 'seller', title VARCHAR(255) NOT NULL,
            category_id INT NULL, seller_id INT NULL, seller_name VARCHAR(150) NULL, seller_mobile VARCHAR(25) NULL,
            qty INT NOT NULL DEFAULT 1, mrp DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            base_price DECIMAL(10,2) NOT NULL DEFAULT 0.00, discount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, advance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            due DECIMAL(10,2) NOT NULL DEFAULT 0.00, seller_payout DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            company_profit DECIMAL(10,2) NOT NULL DEFAULT 0.00, fulfilment VARCHAR(20) NOT NULL DEFAULT 'online',
            status VARCHAR(30) NOT NULL DEFAULT 'pending', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_oi_order (order_id), KEY idx_oi_seller (seller_id, status), KEY idx_oi_service (service_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        "CREATE TABLE IF NOT EXISTS order_timeline (
            id BIGINT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, order_code VARCHAR(40) NULL,
            stage VARCHAR(30) NOT NULL, title VARCHAR(160) NOT NULL, note VARCHAR(500) NULL,
            amount DECIMAL(10,2) NULL, actor_id INT NULL, actor_name VARCHAR(150) NULL,
            actor_role VARCHAR(20) NULL, ip_address VARCHAR(45) NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_ot_order (order_id, id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        "CREATE TABLE IF NOT EXISTS seller_collections (
            id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, order_code VARCHAR(40) NULL,
            item_id INT NULL, seller_id INT NOT NULL, customer_id INT NULL, customer_name VARCHAR(150) NULL,
            amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, mode VARCHAR(20) NOT NULL DEFAULT 'cash',
            reference VARCHAR(120) NULL, note VARCHAR(500) NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending',
            recorded_by INT NULL, recorded_by_name VARCHAR(150) NULL, approved_by INT NULL,
            approved_by_name VARCHAR(150) NULL, approved_at DATETIME NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_sc_order (order_id), KEY idx_sc_seller (seller_id, status), KEY idx_sc_status (status, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        "CREATE TABLE IF NOT EXISTS seller_settlements (
            id INT AUTO_INCREMENT PRIMARY KEY, seller_id INT NOT NULL, direction VARCHAR(10) NOT NULL DEFAULT 'in',
            amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, mode VARCHAR(20) NOT NULL DEFAULT 'cash',
            reference VARCHAR(120) NULL, note VARCHAR(500) NULL, order_id INT NULL, created_by INT NULL,
            created_by_name VARCHAR(150) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_st_seller (seller_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    ] as $q) { try { $conn->query($q); } catch (\Throwable $e) {} }

    $done = true;
    return true;
}

/* ---------------------------------------------------------------------------
   The two ways a product reaches the customer
   --------------------------------------------------------------------------- */
function buy_fulfilments() {
    return [
        'online' => ['Online only', 'fa-bolt',
                     'Paid in full on the site and delivered straight away — nobody has to go anywhere.'],
        'visit'  => ['Seller visit', 'fa-person-walking-luggage',
                     'The customer pays an advance here; a seller visits and takes the rest.'],
    ];
}

/** What a product falls back to when it has not been told: its category's setting. */
function buy_category_default($db, $catId) {
    static $cache = [];
    $catId = (int)$catId;
    if (isset($cache[$catId])) return $cache[$catId];
    $out = ['fulfilment' => 'online', 'advance_mode' => 'company', 'advance_percent' => 0.0, 'advance_amount' => 0.0];
    if ($catId > 0) {
        try {
            $r = $db->select("SELECT fulfilment, advance_mode, advance_percent, advance_amount FROM service_categories WHERE id = ?", [$catId]);
            if (!empty($r)) {
                $out = ['fulfilment' => $r[0]['fulfilment'] ?: 'online',
                        'advance_mode' => $r[0]['advance_mode'] ?: 'company',
                        'advance_percent' => (float)$r[0]['advance_percent'],
                        'advance_amount' => (float)$r[0]['advance_amount']];
            }
        } catch (\Throwable $e) {}
    }
    return $cache[$catId] = $out;
}

/** 'online' or 'visit' for one product — its own answer, or its category's. */
function buy_fulfilment($db, $svc) {
    $own = strtolower(trim((string)($svc['fulfilment'] ?? '')));
    if ($own === 'visit' || $own === 'online') return $own;
    $cat = buy_category_default($db, $svc['category_id'] ?? 0);
    return $cat['fulfilment'] === 'visit' ? 'visit' : 'online';
}

/**
 * How much is asked for up front.
 *
 *   company  the company's own share — the natural default. The customer pays
 *            what the company earns, and the seller collects what they earn, so
 *            no money has to change hands afterwards.
 *   fixed    a flat figure typed in against the product
 *   percent  a share of the price
 *
 * Never more than the price, and never below zero.
 */
function buy_advance_for($db, $svc, $payable, $companyShare) {
    $mode = strtolower(trim((string)($svc['advance_mode'] ?? '')));
    $amt  = (float)($svc['advance_amount'] ?? 0);
    $pct  = (float)($svc['advance_percent'] ?? 0);

    if ($mode === '' || (!in_array($mode, ['company', 'fixed', 'percent'], true))) {
        $cat  = buy_category_default($db, $svc['category_id'] ?? 0);
        $mode = $cat['advance_mode'];
        if ($amt <= 0) $amt = (float)$cat['advance_amount'];
        if ($pct <= 0) $pct = (float)$cat['advance_percent'];
    }

    if     ($mode === 'fixed')   { $adv = $amt; }
    elseif ($mode === 'percent') { $adv = $payable * $pct / 100; }
    else                         { $adv = $companyShare; }

    $adv = round(max(0, min($payable, $adv)), 2);
    return $adv;
}

/* ---------------------------------------------------------------------------
   ONE PRODUCT, FULLY PRICED FOR THIS BUYER
   Everything the product page and the checkout need, worked out once.
   --------------------------------------------------------------------------- */
function buy_quote($db, $svc, $user_id, $is_sub = null) {
    buy_ensure_schema($db);

    $mrp     = round((float)($svc['price'] ?? 0), 2);
    $regular = round((float)($svc['discount_price'] > 0 ? $svc['discount_price'] : $svc['price']), 2);

    $sub = ($is_sub === null) ? shares_is_subscribed($db, $user_id) : (bool)$is_sub;

    /* what a member saves, worked out by the same rules the money engine uses */
    $memberOff  = shares_amount_for($svc, 'subscription', $regular);
    $memberPrice = round(max(0, $regular - $memberOff), 2);

    $payable  = $sub ? $memberPrice : $regular;
    $discount = $sub ? round($regular - $memberPrice, 2) : 0.00;

    /* the split, exactly as shares_distribute() will do it on the amount paid */
    $bdRegular = shares_breakdown($svc, $regular);
    $bdMember  = shares_breakdown($svc, $memberPrice);
    $bd        = $sub ? $bdMember : $bdRegular;

    $sellerPayout  = round($bd['seller']['amount'], 2);
    $companyProfit = shares_company_earned($svc, $payable);

    $fulfilment = buy_fulfilment($db, $svc);
    $advance    = $fulfilment === 'visit' ? buy_advance_for($db, $svc, $payable, $companyProfit) : $payable;
    $due        = round(max(0, $payable - $advance), 2);

    return [
        'service_id'     => (int)$svc['id'],
        'service_type'   => $svc['__type'] ?? 'seller',
        'title'          => $svc['title'] ?? '',
        'category_id'    => (int)($svc['category_id'] ?? 0),
        'seller_id'      => (int)($svc['seller_id'] ?? 0),
        'mrp'            => $mrp,
        'regular'        => $regular,
        'member'         => $memberPrice,
        'member_off'     => round($memberOff, 2),
        'payable'        => $payable,
        'discount'       => $discount,
        'is_subscribed'  => $sub,
        'customer_type'  => $sub ? 'subscriber' : 'regular',
        'fulfilment'     => $fulfilment,
        'advance'        => $advance,
        'due'            => $due,
        'seller_payout'  => $sellerPayout,
        'company_profit' => $companyProfit,
        'profit_regular' => shares_company_earned($svc, $regular),
        'profit_member'  => shares_company_earned($svc, $memberPrice),
        'payout_regular' => round($bdRegular['seller']['amount'], 2),
        'payout_member'  => round($bdMember['seller']['amount'], 2),
        'breakdown'      => $bd,
        'allow_cart'     => (int)($svc['allow_cart'] ?? 1) === 1,
        'allow_code'     => (int)($svc['allow_code'] ?? 1) === 1,
        'visit_note'     => (string)($svc['visit_note'] ?? ''),
    ];
}

/* ---------------------------------------------------------------------------
   THE BASKET
   --------------------------------------------------------------------------- */
function cart_add($db, $user_id, $service_id, $type = 'seller', $qty = 1) {
    buy_ensure_schema($db);
    $qty = max(1, min(20, (int)$qty));
    try {
        $db->insert(
            "INSERT INTO carts (user_id, service_id, service_type, qty) VALUES (?,?,?,?)
             ON DUPLICATE KEY UPDATE qty = LEAST(20, qty + VALUES(qty))",
            [(int)$user_id, (int)$service_id, $type, $qty]);
        return true;
    } catch (\Throwable $e) { return false; }
}
function cart_set_qty($db, $user_id, $cart_id, $qty) {
    $qty = (int)$qty;
    try {
        if ($qty <= 0) { $db->insert("DELETE FROM carts WHERE id = ? AND user_id = ?", [(int)$cart_id, (int)$user_id]); }
        else { $db->insert("UPDATE carts SET qty = ? WHERE id = ? AND user_id = ?", [min(20, $qty), (int)$cart_id, (int)$user_id]); }
    } catch (\Throwable $e) {}
}
function cart_remove($db, $user_id, $cart_id) {
    try { $db->insert("DELETE FROM carts WHERE id = ? AND user_id = ?", [(int)$cart_id, (int)$user_id]); }
    catch (\Throwable $e) {}
}
function cart_clear($db, $user_id) {
    try { $db->insert("DELETE FROM carts WHERE user_id = ?", [(int)$user_id]); } catch (\Throwable $e) {}
}
function cart_count($db, $user_id) {
    if (!$user_id) return 0;
    try {
        $r = $db->select("SELECT COALESCE(SUM(qty),0) c FROM carts WHERE user_id = ?", [(int)$user_id]);
        return (int)($r[0]['c'] ?? 0);
    } catch (\Throwable $e) { return 0; }
}

/** Every line in the basket, already priced for this buyer. */
function cart_items($db, $user_id) {
    buy_ensure_schema($db);
    $out = [];
    try { $rows = $db->select("SELECT * FROM carts WHERE user_id = ? ORDER BY id ASC", [(int)$user_id]); }
    catch (\Throwable $e) { return $out; }
    if (empty($rows)) return $out;

    $sub = shares_is_subscribed($db, $user_id);
    foreach ($rows as $r) {
        $svc = shares_get_service($db, (int)$r['service_id']);
        if (!$svc) { cart_remove($db, $user_id, $r['id']); continue; }   // it went away
        $q = buy_quote($db, $svc, $user_id, $sub);
        $q['cart_id'] = (int)$r['id'];
        $q['qty']     = max(1, (int)$r['qty']);
        $q['svc']     = $svc;
        $out[] = $q;
    }
    return $out;
}

/** Add up a set of priced lines. */
function cart_totals($lines) {
    $t = ['mrp' => 0, 'regular' => 0, 'payable' => 0, 'discount' => 0, 'advance' => 0,
          'due' => 0, 'seller_payout' => 0, 'company_profit' => 0, 'qty' => 0,
          'items' => count($lines), 'has_visit' => false];
    foreach ($lines as $l) {
        $q = max(1, (int)($l['qty'] ?? 1));
        $t['qty']            += $q;
        $t['mrp']            += $l['mrp'] * $q;
        $t['regular']        += $l['regular'] * $q;
        $t['payable']        += $l['payable'] * $q;
        $t['discount']       += $l['discount'] * $q;
        $t['advance']        += $l['advance'] * $q;
        $t['due']            += $l['due'] * $q;
        $t['seller_payout']  += $l['seller_payout'] * $q;
        $t['company_profit'] += $l['company_profit'] * $q;
        if ($l['fulfilment'] === 'visit') { $t['has_visit'] = true; }
    }
    foreach ($t as $k => $v) { if (is_float($v)) { $t[$k] = round($v, 2); } }
    return $t;
}

/* ---------------------------------------------------------------------------
   WALLET FIRST, THEN THE REST
   Wallet 200 against a 500 order means 200 comes off the wallet and 300 is
   asked for. Wallet 500 against a 500 order means nothing more to pay.
   --------------------------------------------------------------------------- */
function buy_wallet_plan($db, $user_id, $payable, $use_wallet = true) {
    $payable   = round(max(0, (float)$payable), 2);
    $spendable = 0.00;
    try { $spendable = round((float)wallet_spendable($db, $user_id), 2); } catch (\Throwable $e) {}

    $wallet  = $use_wallet ? round(min($spendable, $payable), 2) : 0.00;
    $gateway = round(max(0, $payable - $wallet), 2);

    return [
        'payable'   => $payable,
        'spendable' => $spendable,
        'wallet'    => $wallet,
        'gateway'   => $gateway,
        'covered'   => $gateway <= 0.009,          // the wallet paid for all of it
    ];
}

/* ---------------------------------------------------------------------------
   Codes
   --------------------------------------------------------------------------- */
function buy_order_code($db) {
    $stem = 'WBMCZ-' . date('ymd') . '-';
    try {
        $r = $db->select("SELECT order_code FROM orders WHERE order_code LIKE ? ORDER BY id DESC LIMIT 1", [$stem . '%']);
        $n = !empty($r) ? (int)substr((string)$r[0]['order_code'], strlen($stem)) : 0;
    } catch (\Throwable $e) { $n = 0; }
    return $stem . str_pad($n + 1, 6, '0', STR_PAD_LEFT);
}
function buy_payment_code($db) {
    for ($i = 0; $i < 12; $i++) {
        $code = 'PC' . strtoupper(bin2hex(random_bytes(3)));
        try {
            $r = $db->select("SELECT id FROM orders WHERE payment_code = ?", [$code]);
            if (empty($r)) return $code;
        } catch (\Throwable $e) { return $code; }
    }
    return 'PC' . strtoupper(bin2hex(random_bytes(4)));
}

/* ---------------------------------------------------------------------------
   THE SEVEN STAGES
   --------------------------------------------------------------------------- */
function buy_stages() {
    return [
        'placed'    => ['Order Placed',          'fa-cart-shopping',   'The customer placed the order.'],
        'paid'      => ['Payment Received',      'fa-indian-rupee-sign','Advance or full payment received.'],
        'assigned'  => ['Seller Assigned',       'fa-user-check',      'The order went to a seller.'],
        'visit'     => ['Physical Verification', 'fa-person-walking',  'The seller visits the customer.'],
        'collected' => ['Cash Collected',        'fa-sack-dollar',     'The remaining amount was taken.'],
        'activated' => ['Service Activated',     'fa-circle-check',    'The service was set up and handed over.'],
        'completed' => ['Completed',             'fa-flag-checkered',  'Everything is done.'],
    ];
}
function buy_stage_index($stage) {
    $k = array_keys(buy_stages());
    $i = array_search($stage, $k, true);
    return $i === false ? 0 : (int)$i;
}
function buy_stage_meta($stage) {
    $s = buy_stages();
    if ($stage === 'cancelled') return ['Cancelled', 'fa-ban', 'The order was cancelled.'];
    return $s[$stage] ?? ['Order Placed', 'fa-cart-shopping', ''];
}

function buy_ip() {
    foreach (['HTTP_CF_CONNECTING_IP', 'HTTP_X_FORWARDED_FOR', 'HTTP_CLIENT_IP', 'REMOTE_ADDR'] as $k) {
        if (!empty($_SERVER[$k])) {
            $c = trim(explode(',', $_SERVER[$k])[0]);
            if (filter_var($c, FILTER_VALIDATE_IP)) return $c;
        }
    }
    return '0.0.0.0';
}

/** One line on an order's timeline. Never throws. */
function buy_log($db, $order, $stage, $title, $note = '', $amount = null, $actor = null) {
    buy_ensure_schema($db);
    $oid  = is_array($order) ? (int)$order['id'] : (int)$order;
    $code = is_array($order) ? (string)($order['order_code'] ?? '') : '';
    if ($actor === null) {
        $actor = ['id' => (int)($_SESSION['user_id'] ?? 0),
                  'name' => (string)($_SESSION['user_name'] ?? ''),
                  'role' => (string)($_SESSION['user_type'] ?? '')];
    }
    if ($actor['name'] === '' && $actor['id']) {
        try { $r = $db->select("SELECT name FROM users WHERE id = ?", [$actor['id']]); $actor['name'] = $r[0]['name'] ?? ''; }
        catch (\Throwable $e) {}
    }
    try {
        $db->insert(
            "INSERT INTO order_timeline (order_id, order_code, stage, title, note, amount, actor_id, actor_name, actor_role, ip_address)
             VALUES (?,?,?,?,?,?,?,?,?,?)",
            [$oid, $code ?: null, substr($stage, 0, 30), substr($title, 0, 160),
             $note !== '' ? substr($note, 0, 500) : null,
             $amount !== null ? round((float)$amount, 2) : null,
             $actor['id'] ?: null, substr((string)$actor['name'], 0, 150), substr((string)$actor['role'], 0, 20), buy_ip()]
        );
    } catch (\Throwable $e) {}
}

function buy_timeline($db, $order_id) {
    try { return $db->select("SELECT * FROM order_timeline WHERE order_id = ? ORDER BY id ASC", [(int)$order_id]); }
    catch (\Throwable $e) { return []; }
}

/* ---------------------------------------------------------------------------
   PLACE THE ORDER
   ----------------------------------------------------------------------------
   $lines  priced lines from cart_items(), or a single one from buy_quote()
   $opts   payment_mode  full | advance | code
           use_wallet    spend the wallet first
           gateway_ref   the payment id, when money came through the gateway
           address, note

   Returns ['ok','order_id','order_code','payment_code','pay_now','wallet',
            'gateway','due','error']
   --------------------------------------------------------------------------- */
function buy_place_order($db, $user_id, $lines, $opts = []) {
    buy_ensure_schema($db);
    $out = ['ok' => false, 'order_id' => 0, 'order_code' => '', 'payment_code' => '',
            'pay_now' => 0, 'wallet' => 0, 'gateway' => 0, 'due' => 0, 'error' => ''];

    if (empty($lines)) { $out['error'] = 'There is nothing to buy.'; return $out; }

    $mode = in_array(($opts['payment_mode'] ?? 'full'), ['full', 'advance', 'code'], true)
            ? $opts['payment_mode'] : 'full';

    /* the same gates the old checkout enforced — KYC, and where the buyer lives */
    foreach ($lines as $l) {
        $svc = $l['svc'] ?? shares_get_service($db, $l['service_id']);
        if (!$svc) { $out['error'] = 'One of the products is no longer available.'; return $out; }
        $gate = product_purchase_gate($db, $svc, $user_id);
        if (!$gate['ok']) {
            $out['error'] = product_gate_message(product_gate_payload($db, $svc, $user_id, $gate));
            return $out;
        }
    }

    $t = cart_totals($lines);
    if ($t['payable'] <= 0 && $mode !== 'full') { $mode = 'full'; }
    if ($mode === 'advance' && !$t['has_visit']) { $mode = 'full'; }   // nothing to visit for

    $payNow = $mode === 'full' ? $t['payable'] : ($mode === 'advance' ? $t['advance'] : 0.00);
    $payNow = round(max(0, min($t['payable'], $payNow)), 2);
    $due    = round($t['payable'] - $payNow, 2);

    $plan = buy_wallet_plan($db, $user_id, $payNow, !empty($opts['use_wallet']));
    $ref  = trim((string)($opts['gateway_ref'] ?? ''));

    if ($mode !== 'code' && $plan['gateway'] > 0 && $ref === '') {
        $out['error'] = 'Payment of ₹' . number_format($plan['gateway'], 2) . ' has not been completed yet.';
        $out['pay_now'] = $payNow; $out['wallet'] = $plan['wallet']; $out['gateway'] = $plan['gateway'];
        return $out;
    }

    /* who the buyer is, for the seller's visit sheet. The account is the
       starting point, but the checkout form may name somebody else to meet. */
    $buyer = ['name' => '', 'mobile' => ''];
    try {
        $b = $db->select("SELECT name, mobile FROM users WHERE id = ?", [(int)$user_id]);
        if (!empty($b)) { $buyer = ['name' => $b[0]['name'], 'mobile' => $b[0]['mobile']]; }
    } catch (\Throwable $e) {}
    if (trim((string)($opts['name'] ?? '')) !== '')   { $buyer['name']   = substr(trim($opts['name']), 0, 150); }
    if (trim((string)($opts['mobile'] ?? '')) !== '') { $buyer['mobile'] = substr(trim($opts['mobile']), 0, 25); }

    $first      = $lines[0];
    $code       = buy_order_code($db);
    $payCode    = $mode === 'code' ? buy_payment_code($db) : null;
    $fulfilment = $t['has_visit'] ? 'visit' : 'online';
    $stage      = $payNow > 0 ? 'paid' : 'placed';
    $status     = 'Pending';

    try {
        $order_id = (int)$db->insert(
            "INSERT INTO orders
                (user_id, seller_id, service_name, category_id, amount, status,
                 order_code, fulfilment, customer_type, payment_mode,
                 mrp, base_amount, discount_amount, paid_amount, wallet_used, gateway_used,
                 due_amount, seller_payout, company_profit, payment_code, payment_code_status,
                 visit_status, stage, customer_name, customer_mobile, address, note, item_count)
             VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
            [
                (int)$user_id, (int)$first['seller_id'],
                count($lines) === 1 ? $first['title'] : ($first['title'] . ' + ' . (count($lines) - 1) . ' more'),
                (int)$first['category_id'], $t['payable'], $status,
                $code, $fulfilment, $first['customer_type'], $mode,
                $t['mrp'], $t['regular'], $t['discount'],
                $mode === 'code' ? 0.00 : $payNow,
                $mode === 'code' ? 0.00 : $plan['wallet'],
                $mode === 'code' ? 0.00 : $plan['gateway'],
                $due, $t['seller_payout'], $t['company_profit'],
                $payCode, $mode === 'code' ? 'pending' : 'none',
                $t['has_visit'] ? 'pending' : 'none',
                $stage, $buyer['name'], $buyer['mobile'],
                substr((string)($opts['address'] ?? ''), 0, 500),
                substr((string)($opts['note'] ?? ''), 0, 500),
                $t['qty'],
            ]
        );
    } catch (\Throwable $e) { $order_id = 0; }

    if (!$order_id) { $out['error'] = 'The order could not be saved. Please try again.'; return $out; }

    /* one row per product, each with its own seller and its own amount due */
    foreach ($lines as $l) {
        $q  = max(1, (int)($l['qty'] ?? 1));
        $sn = ''; $sm = '';
        if ($l['seller_id'] > 0) {
            try {
                $s = $db->select("SELECT name, mobile FROM users WHERE id = ?", [(int)$l['seller_id']]);
                if (!empty($s)) { $sn = $s[0]['name']; $sm = $s[0]['mobile']; }
            } catch (\Throwable $e) {}
        }
        try {
            $db->insert(
                "INSERT INTO order_items
                    (order_id, service_id, service_type, title, category_id, seller_id, seller_name, seller_mobile,
                     qty, mrp, base_price, discount, amount, advance, due, seller_payout, company_profit, fulfilment, status)
                 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                [$order_id, $l['service_id'], $l['service_type'], $l['title'], $l['category_id'],
                 $l['seller_id'] ?: null, $sn, $sm, $q,
                 $l['mrp'] * $q, $l['regular'] * $q, $l['discount'] * $q, $l['payable'] * $q,
                 ($mode === 'full' ? $l['payable'] : $l['advance']) * $q,
                 ($mode === 'full' ? 0 : $l['due']) * $q,
                 $l['seller_payout'] * $q, $l['company_profit'] * $q,
                 $l['fulfilment'], 'pending']
            );
        } catch (\Throwable $e) {}
    }

    buy_log($db, ['id' => $order_id, 'order_code' => $code], 'placed', 'Order Placed',
            $t['items'] . ' item(s) · ' . ucfirst($mode) . ' payment', $t['payable']);

    /* ---- take the money ---- */
    if ($mode !== 'code' && $payNow > 0) {
        if ($plan['wallet'] > 0) {
            $pay = wallet_spend($db, $user_id, $plan['wallet'], [
                'category' => 'purchase',
                'description' => 'Order ' . $code . ($mode === 'advance' ? ' (advance)' : ''),
                'reference_type' => 'order', 'reference_id' => $order_id,
            ]);
            if (!$pay['ok']) {
                /* the balance moved between the quote and the click — undo cleanly */
                try {
                    $db->insert("DELETE FROM order_items WHERE order_id = ?", [$order_id]);
                    $db->insert("DELETE FROM order_timeline WHERE order_id = ?", [$order_id]);
                    $db->insert("DELETE FROM orders WHERE id = ?", [$order_id]);
                } catch (\Throwable $e) {}
                $out['error'] = 'Your wallet balance changed — you are ₹' . number_format($pay['short'], 2) . ' short. Please try again.';
                return $out;
            }
        }
        if ($plan['gateway'] > 0) {
            try {
                $db->insert("INSERT INTO transactions (user_id, type, amount, description, status) VALUES (?, 'debit', ?, ?, 'Success')",
                    [(int)$user_id, $plan['gateway'], "Paid online (ID: $ref) for order $code"]);
                wallet__ledger($db, $user_id, WALLET_MAIN, 'debit', $plan['gateway'], wallet_balances($db, $user_id)['main'], [
                    'category' => 'purchase', 'description' => "Paid online (ID: $ref) — order $code",
                    'reference_type' => 'order', 'reference_id' => $order_id,
                ]);
            } catch (\Throwable $e) {}
        }

        $note = [];
        if ($plan['wallet']  > 0) { $note[] = '₹' . number_format($plan['wallet'], 2) . ' from wallet'; }
        if ($plan['gateway'] > 0) { $note[] = '₹' . number_format($plan['gateway'], 2) . ' online'; }
        buy_log($db, ['id' => $order_id, 'order_code' => $code], 'paid',
                $mode === 'advance' ? 'Advance Payment Received' : 'Payment Received',
                implode(' + ', $note), $payNow);
    } elseif ($mode === 'code') {
        buy_log($db, ['id' => $order_id, 'order_code' => $code], 'placed', 'Payment Code Generated',
                'Code ' . $payCode . ' — pay by any method and give this code.', $t['payable']);
    }

    /* ---- a seller is already known, so the order is theirs ---- */
    if ($t['has_visit'] && (int)$first['seller_id'] > 0) {
        try { $db->insert("UPDATE orders SET stage = 'assigned' WHERE id = ?", [$order_id]); } catch (\Throwable $e) {}
        buy_log($db, ['id' => $order_id, 'order_code' => $code], 'assigned', 'Seller Assigned',
                'The order went to the seller who listed the product.');
        buy_log($db, ['id' => $order_id, 'order_code' => $code], 'visit', 'Physical Verification Pending',
                'Waiting for the seller to visit and collect ₹' . number_format($due, 2) . '.', $due);
    }

    /* ---- nothing left owing means the shares can be worked out now ---- */
    if ($due <= 0.009 && $mode !== 'code') { buy_settle_shares($db, $order_id); }

    /* ---- take these lines out of the basket ---- */
    foreach ($lines as $l) { if (!empty($l['cart_id'])) { cart_remove($db, $user_id, $l['cart_id']); } }

    $out['ok'] = true;
    $out['order_id'] = $order_id; $out['order_code'] = $code; $out['payment_code'] = (string)$payCode;
    $out['pay_now'] = $payNow; $out['wallet'] = $plan['wallet']; $out['gateway'] = $plan['gateway']; $out['due'] = $due;
    return $out;
}

/**
 * Work out the seller / referral / staff / company shares for an order.
 * Runs at most once — a second call finds the ledger already written and stops.
 */
function buy_settle_shares($db, $order_id) {
    $order_id = (int)$order_id;
    try {
        $has = $db->select("SELECT id FROM product_share_ledger WHERE order_id = ? LIMIT 1", [$order_id]);
        if (!empty($has)) return false;                       // already done
    } catch (\Throwable $e) {}

    try { $o = $db->select("SELECT * FROM orders WHERE id = ?", [$order_id]); } catch (\Throwable $e) { $o = []; }
    if (empty($o)) return false;
    $order = $o[0];

    try { $items = $db->select("SELECT * FROM order_items WHERE order_id = ?", [$order_id]); }
    catch (\Throwable $e) { $items = []; }

    foreach ($items as $it) {
        if ((int)$it['service_id'] <= 0) continue;
        $svc = shares_get_service($db, (int)$it['service_id']);
        if (!$svc) continue;
        $paid = (float)$it['amount'];
        if ($paid <= 0) continue;
        $disc = (float)$it['discount'] > 0
            ? ['discount' => (float)$it['discount'], 'discount_pct' => shares_pct_for($svc, 'subscription', (float)$it['base_price'])]
            : null;
        shares_distribute($db, $svc, (int)$order['user_id'], $order_id, $paid, $disc);
    }
    return true;
}

/* ---------------------------------------------------------------------------
   READING ORDERS BACK
   --------------------------------------------------------------------------- */

/** One order with its items and timeline already attached. */
function buy_order($db, $order_id, $user_id = 0) {
    buy_ensure_schema($db);
    $order_id = (int)$order_id;
    if ($order_id <= 0) return null;
    $sql = "SELECT * FROM orders WHERE id = ?";
    $arg = [$order_id];
    if ($user_id > 0) { $sql .= " AND user_id = ?"; $arg[] = (int)$user_id; }
    try { $r = $db->select($sql, $arg); } catch (\Throwable $e) { return null; }
    if (empty($r)) return null;
    $o = $r[0];
    $o['items']    = buy_order_items($db, $order_id);
    $o['timeline'] = buy_timeline($db, $order_id);
    return $o;
}

function buy_order_by_code($db, $code, $user_id = 0) {
    buy_ensure_schema($db);
    $code = trim((string)$code);
    if ($code === '') return null;
    try { $r = $db->select("SELECT id FROM orders WHERE order_code = ? LIMIT 1", [$code]); }
    catch (\Throwable $e) { return null; }
    if (empty($r)) return null;
    return buy_order($db, (int)$r[0]['id'], $user_id);
}

function buy_order_items($db, $order_id) {
    try { return $db->select("SELECT * FROM order_items WHERE order_id = ? ORDER BY id ASC", [(int)$order_id]); }
    catch (\Throwable $e) { return []; }
}

/**
 * A list of orders. $f accepts user_id, seller_id, stage, fulfilment, status,
 * due (1 = only orders with money still outstanding), q, limit, offset.
 */
function buy_orders($db, $f = []) {
    buy_ensure_schema($db);
    $w = ['1=1']; $a = [];
    if (!empty($f['user_id']))    { $w[] = "o.user_id = ?";     $a[] = (int)$f['user_id']; }
    if (!empty($f['seller_id']))  { $w[] = "o.seller_id = ?";   $a[] = (int)$f['seller_id']; }
    if (!empty($f['stage']))      { $w[] = "o.stage = ?";       $a[] = (string)$f['stage']; }
    if (!empty($f['fulfilment'])) { $w[] = "o.fulfilment = ?";  $a[] = (string)$f['fulfilment']; }
    if (!empty($f['status']))     { $w[] = "o.status = ?";      $a[] = (string)$f['status']; }
    if (!empty($f['due']))        { $w[] = "o.due_amount > 0"; }
    if (!empty($f['q'])) {
        $q = '%' . trim($f['q']) . '%';
        $w[] = "(o.order_code LIKE ? OR o.customer_name LIKE ? OR o.customer_mobile LIKE ? OR o.service_name LIKE ?)";
        array_push($a, $q, $q, $q, $q);
    }
    $lim = max(1, min(500, (int)($f['limit'] ?? 50)));
    $off = max(0, (int)($f['offset'] ?? 0));
    $sql = "SELECT o.* FROM orders o WHERE " . implode(' AND ', $w) .
           " ORDER BY o.id DESC LIMIT $lim OFFSET $off";
    try { return $db->select($sql, $a); } catch (\Throwable $e) { return []; }
}

function buy_orders_count($db, $f = []) {
    $f2 = $f; unset($f2['limit'], $f2['offset']);
    $w = ['1=1']; $a = [];
    if (!empty($f2['user_id']))    { $w[] = "user_id = ?";    $a[] = (int)$f2['user_id']; }
    if (!empty($f2['seller_id']))  { $w[] = "seller_id = ?";  $a[] = (int)$f2['seller_id']; }
    if (!empty($f2['stage']))      { $w[] = "stage = ?";      $a[] = (string)$f2['stage']; }
    if (!empty($f2['fulfilment'])) { $w[] = "fulfilment = ?"; $a[] = (string)$f2['fulfilment']; }
    if (!empty($f2['status']))     { $w[] = "status = ?";     $a[] = (string)$f2['status']; }
    if (!empty($f2['due']))        { $w[] = "due_amount > 0"; }
    try {
        $r = $db->select("SELECT COUNT(*) c FROM orders WHERE " . implode(' AND ', $w), $a);
        return (int)($r[0]['c'] ?? 0);
    } catch (\Throwable $e) { return 0; }
}

/* ---------------------------------------------------------------------------
   MOVING AN ORDER ALONG
   --------------------------------------------------------------------------- */

/** Write a new stage on the order, but never walk backwards by accident. */
function buy_set_stage($db, $order_id, $stage, $force = false) {
    buy_ensure_schema($db);
    $order_id = (int)$order_id;
    try { $r = $db->select("SELECT stage FROM orders WHERE id = ?", [$order_id]); } catch (\Throwable $e) { return false; }
    if (empty($r)) return false;
    $now = (string)$r[0]['stage'];
    if (!$force && $stage !== 'cancelled' && $now !== 'cancelled'
        && buy_stage_index($stage) <= buy_stage_index($now)) return false;
    try { $db->insert("UPDATE orders SET stage = ?, updated_at = NOW() WHERE id = ?", [$stage, $order_id]); }
    catch (\Throwable $e) { return false; }
    return true;
}

/** Hand the order to a seller. */
function buy_assign_seller($db, $order_id, $seller_id, $note = '') {
    buy_ensure_schema($db);
    $order_id = (int)$order_id; $seller_id = (int)$seller_id;
    $o = buy_order($db, $order_id);
    if (!$o) return ['ok' => false, 'error' => 'Order not found.'];

    $name = '';
    try { $s = $db->select("SELECT name FROM users WHERE id = ?", [$seller_id]); $name = $s[0]['name'] ?? ''; }
    catch (\Throwable $e) {}

    try { $db->insert("UPDATE orders SET seller_id = ?, updated_at = NOW() WHERE id = ?", [$seller_id ?: null, $order_id]); }
    catch (\Throwable $e) { return ['ok' => false, 'error' => 'Could not save the seller.']; }

    try { $db->insert("UPDATE order_items SET seller_id = ? WHERE order_id = ?", [$seller_id ?: null, $order_id]); }
    catch (\Throwable $e) {}

    buy_set_stage($db, $order_id, 'assigned');
    buy_log($db, $o, 'assigned', 'Seller assigned', trim($name . ($note ? ' — ' . $note : '')));
    return ['ok' => true];
}

/** The seller is on the way, or has been. */
function buy_mark_visit($db, $order_id, $status = 'scheduled', $when = '', $note = '') {
    buy_ensure_schema($db);
    $order_id = (int)$order_id;
    $o = buy_order($db, $order_id);
    if (!$o) return ['ok' => false, 'error' => 'Order not found.'];

    $when = trim((string)$when);
    $dt   = $when !== '' ? date('Y-m-d H:i:s', strtotime($when)) : date('Y-m-d H:i:s');
    $status = in_array($status, ['scheduled', 'done', 'failed'], true) ? $status : 'scheduled';

    try {
        $db->insert("UPDATE orders SET visit_status = ?, visit_at = ?, updated_at = NOW() WHERE id = ?",
                    [$status, $dt, $order_id]);
    } catch (\Throwable $e) { return ['ok' => false, 'error' => 'Could not save the visit.']; }

    if ($status !== 'failed') buy_set_stage($db, $order_id, 'visit');
    $title = $status === 'done' ? 'Visit completed' : ($status === 'failed' ? 'Visit could not happen' : 'Visit scheduled');
    buy_log($db, $o, 'visit', $title, $note);
    return ['ok' => true];
}

/* ---------------------------------------------------------------------------
   THE MONEY TAKEN AT THE DOOR
   ----------------------------------------------------------------------------
   Three ways the rest of the money can arrive, and they are not the same
   thing at all:

     cash     the seller has notes in their pocket. Ours, until handed over.
     online   the customer paid the seller directly — their UPI, their bank.
              Still the seller's to hand over; only the form differs.
     company  it reached the company: the gateway, the office account. The
              seller is holding nothing, so it never touches their dues.

   'holds' below is what decides whether a collection lands on the seller's
   Dues screen.
   --------------------------------------------------------------------------- */
function buy_collection_modes() {
    return [
        'cash'    => ['Cash',            'fa-money-bill-wave', true,  'Taken in hand by the seller'],
        'online'  => ['Online to seller', 'fa-mobile-screen',   true,  'UPI or transfer into the seller\'s own account'],
        'company' => ['Paid to company',  'fa-building-columns', false, 'Reached the company account or the gateway'],
    ];
}
function buy_mode_label($mode) { $m = buy_collection_modes(); return $m[$mode][0] ?? ucfirst((string)$mode); }
function buy_mode_holds($mode) { $m = buy_collection_modes(); return (bool)($m[$mode][2] ?? false); }

/**
 * The seller says what they took. Nothing on the order changes yet — this is
 * a claim, and the office has to agree with it first.
 *
 * $d: amount, mode, reference, note, seller_id (defaults to the order's),
 *     auto (true to approve it in the same breath — the office doing it itself)
 */
function buy_record_collection($db, $order_id, $d = []) {
    buy_ensure_schema($db);
    $order_id = (int)$order_id;
    $o = buy_order($db, $order_id);
    if (!$o) return ['ok' => false, 'error' => 'Order not found.'];
    if ((string)$o['stage'] === 'cancelled') return ['ok' => false, 'error' => 'This order was cancelled.'];

    $amount = round((float)($d['amount'] ?? 0), 2);
    if ($amount <= 0) return ['ok' => false, 'error' => 'Enter the amount that was collected.'];

    $due = round((float)$o['due_amount'], 2);
    if ($due <= 0) return ['ok' => false, 'error' => 'Nothing is outstanding on this order.'];

    /* anything already claimed and waiting on the office counts against the due */
    $waiting = 0.0;
    try {
        $r = $db->select("SELECT COALESCE(SUM(amount),0) s FROM seller_collections
                          WHERE order_id = ? AND status = 'pending'", [$order_id]);
        $waiting = (float)($r[0]['s'] ?? 0);
    } catch (\Throwable $e) {}

    $room = round($due - $waiting, 2);
    if ($amount > $room + 0.009) {
        return ['ok' => false, 'error' => 'Only ₹' . number_format($room, 2) . ' is still outstanding on this order.'];
    }

    $mode = (string)($d['mode'] ?? 'cash');
    if (!isset(buy_collection_modes()[$mode])) $mode = 'cash';

    $seller_id = (int)($d['seller_id'] ?? $o['seller_id'] ?? 0);
    $actor     = ['id'   => (int)($_SESSION['user_id'] ?? 0),
                  'name' => (string)($_SESSION['user_name'] ?? ''),
                  'role' => (string)($_SESSION['user_type'] ?? '')];
    if ($seller_id <= 0) $seller_id = $actor['id'];

    $auto   = !empty($d['auto']);
    $status = $auto ? 'approved' : 'pending';

    $ok = false;
    try {
        $ok = $db->insert(
            "INSERT INTO seller_collections
               (order_id, order_code, item_id, seller_id, customer_id, customer_name, amount, mode,
                reference, note, status, recorded_by, recorded_by_name, approved_by, approved_by_name, approved_at)
             VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
            [$order_id, $o['order_code'] ?? null, !empty($d['item_id']) ? (int)$d['item_id'] : null,
             $seller_id, (int)$o['user_id'], (string)($o['customer_name'] ?? ''), $amount, $mode,
             trim((string)($d['reference'] ?? '')) ?: null,
             trim((string)($d['note'] ?? '')) ?: null,
             $status, $actor['id'] ?: null, $actor['name'] ?: null,
             $auto ? ($actor['id'] ?: null) : null,
             $auto ? ($actor['name'] ?: null) : null,
             $auto ? date('Y-m-d H:i:s') : null]
        );
    } catch (\Throwable $e) { return ['ok' => false, 'error' => 'Could not save it: ' . $e->getMessage()]; }
    if ($ok === false) return ['ok' => false, 'error' => 'Could not save the collection.'];

    $cid = (int)$db->getConnection()->insert_id;

    buy_log($db, $o, 'collected',
        $auto ? 'Payment recorded' : 'Collection reported',
        buy_mode_label($mode) . ($auto ? '' : ' — waiting for the office to verify'),
        $amount);

    if ($auto) buy_apply_collection($db, $cid);

    return ['ok' => true, 'id' => $cid, 'status' => $status, 'amount' => $amount];
}

/**
 * Add up everything the office has verified and write it back onto the order.
 *
 * Deliberately built to be safe to run twice: it recomputes from the approved
 * collections rather than adding to a running figure, so a double click, a
 * refresh, or an approval that gets undone all end up at the same answer.
 */
function buy_recalc_order($db, $order_id) {
    buy_ensure_schema($db);
    $order_id = (int)$order_id;
    try { $r = $db->select("SELECT * FROM orders WHERE id = ?", [$order_id]); } catch (\Throwable $e) { return false; }
    if (empty($r)) return false;
    $o = $r[0];

    $sum = 0.0; $last = null;
    try {
        $c = $db->select("SELECT COALESCE(SUM(amount),0) s FROM seller_collections
                          WHERE order_id = ? AND status = 'approved'", [$order_id]);
        $sum = round((float)($c[0]['s'] ?? 0), 2);
        $l = $db->select("SELECT mode, seller_id, approved_at FROM seller_collections
                          WHERE order_id = ? AND status = 'approved' ORDER BY id DESC LIMIT 1", [$order_id]);
        $last = $l[0] ?? null;
    } catch (\Throwable $e) {}

    $total  = round((float)$o['amount'], 2);
    $online = round((float)$o['wallet_used'] + (float)$o['gateway_used'], 2);
    $paid   = round($online + $sum, 2);
    $due    = round(max(0, $total - $paid), 2);

    try {
        $db->insert(
            "UPDATE orders
                SET collected_amount = ?, collected_mode = ?, collected_at = ?, collected_by = ?,
                    paid_amount = ?, due_amount = ?, updated_at = NOW()
              WHERE id = ?",
            [$sum,
             $last['mode'] ?? null,
             $sum > 0 ? ($last['approved_at'] ?? date('Y-m-d H:i:s')) : null,
             $sum > 0 ? ((int)($last['seller_id'] ?? 0) ?: null) : null,
             $paid, $due, $order_id]
        );
    } catch (\Throwable $e) { return false; }

    try { $db->insert("UPDATE order_items SET due = 0, status = 'paid' WHERE order_id = ?", [$order_id]); }
    catch (\Throwable $e) {}

    if ($due <= 0.009 && (string)$o['stage'] !== 'cancelled') {
        if ($sum > 0) buy_set_stage($db, $order_id, 'collected');
        /* everything is in, so the shares can finally be worked out */
        buy_settle_shares($db, $order_id);
    }
    return true;
}

/** Nudge the order forward once a verified collection lands on it. */
function buy_apply_collection($db, $collection_id) {
    try { $r = $db->select("SELECT order_id FROM seller_collections WHERE id = ?", [(int)$collection_id]); }
    catch (\Throwable $e) { return false; }
    if (empty($r)) return false;
    return buy_recalc_order($db, (int)$r[0]['order_id']);
}

/** The office agrees with what the seller reported. */
function buy_approve_collection($db, $collection_id, $note = '') {
    buy_ensure_schema($db);
    $collection_id = (int)$collection_id;
    try { $r = $db->select("SELECT * FROM seller_collections WHERE id = ?", [$collection_id]); }
    catch (\Throwable $e) { return ['ok' => false, 'error' => 'Could not read that entry.']; }
    if (empty($r)) return ['ok' => false, 'error' => 'That entry no longer exists.'];
    $c = $r[0];
    if ((string)$c['status'] === 'approved') return ['ok' => true, 'already' => true];

    $actor = ['id' => (int)($_SESSION['user_id'] ?? 0), 'name' => (string)($_SESSION['user_name'] ?? '')];
    try {
        $db->insert("UPDATE seller_collections
                        SET status = 'approved', approved_by = ?, approved_by_name = ?, approved_at = NOW(),
                            note = CASE WHEN ? = '' THEN note ELSE CONCAT(COALESCE(note,''), ' | ', ?) END
                      WHERE id = ?",
                    [$actor['id'] ?: null, $actor['name'] ?: null, $note, $note, $collection_id]);
    } catch (\Throwable $e) { return ['ok' => false, 'error' => 'Could not verify it.']; }

    buy_apply_collection($db, $collection_id);
    buy_log($db, (int)$c['order_id'], 'collected', 'Collection verified',
            buy_mode_label((string)$c['mode']) . ' — verified by the office', (float)$c['amount']);
    return ['ok' => true];
}

/** The office does not agree. Nothing on the order ever moved, so nothing unwinds. */
function buy_reject_collection($db, $collection_id, $reason = '') {
    buy_ensure_schema($db);
    $collection_id = (int)$collection_id;
    try { $r = $db->select("SELECT * FROM seller_collections WHERE id = ?", [$collection_id]); }
    catch (\Throwable $e) { return ['ok' => false, 'error' => 'Could not read that entry.']; }
    if (empty($r)) return ['ok' => false, 'error' => 'That entry no longer exists.'];
    $c = $r[0];

    $actor = ['id' => (int)($_SESSION['user_id'] ?? 0), 'name' => (string)($_SESSION['user_name'] ?? '')];
    try {
        $db->insert("UPDATE seller_collections
                        SET status = 'rejected', approved_by = ?, approved_by_name = ?, approved_at = NOW(),
                            note = CONCAT(COALESCE(note,''), ' | Rejected: ', ?)
                      WHERE id = ?",
                    [$actor['id'] ?: null, $actor['name'] ?: null, substr(trim($reason) ?: 'no reason given', 0, 200), $collection_id]);
    } catch (\Throwable $e) { return ['ok' => false, 'error' => 'Could not save it.']; }

    /* it may have been approved before, so put the order back where it belongs */
    buy_recalc_order($db, (int)$c['order_id']);
    buy_log($db, (int)$c['order_id'], 'collected', 'Collection rejected', trim($reason), (float)$c['amount']);
    return ['ok' => true];
}

/** Collections, filtered. seller_id, status, order_id, q, from, to, limit, offset. */
function buy_collections($db, $f = []) {
    buy_ensure_schema($db);
    $w = ['1=1']; $a = [];
    if (!empty($f['seller_id'])) { $w[] = "sc.seller_id = ?"; $a[] = (int)$f['seller_id']; }
    if (!empty($f['status']))    { $w[] = "sc.status = ?";    $a[] = (string)$f['status']; }
    if (!empty($f['order_id']))  { $w[] = "sc.order_id = ?";  $a[] = (int)$f['order_id']; }
    if (!empty($f['mode']))      { $w[] = "sc.mode = ?";      $a[] = (string)$f['mode']; }
    if (!empty($f['from']))      { $w[] = "DATE(sc.created_at) >= ?"; $a[] = (string)$f['from']; }
    if (!empty($f['to']))        { $w[] = "DATE(sc.created_at) <= ?"; $a[] = (string)$f['to']; }
    if (!empty($f['q'])) {
        $q = '%' . trim($f['q']) . '%';
        $w[] = "(sc.order_code LIKE ? OR sc.customer_name LIKE ? OR sc.reference LIKE ?)";
        array_push($a, $q, $q, $q);
    }
    $lim = max(1, min(500, (int)($f['limit'] ?? 50)));
    $off = max(0, (int)($f['offset'] ?? 0));
    try {
        return $db->select(
            "SELECT sc.*, u.name seller_name, u.mobile seller_mobile, o.service_name, o.amount order_amount
               FROM seller_collections sc
               LEFT JOIN users  u ON u.id = sc.seller_id
               LEFT JOIN orders o ON o.id = sc.order_id
              WHERE " . implode(' AND ', $w) . "
              ORDER BY sc.id DESC LIMIT $lim OFFSET $off", $a);
    } catch (\Throwable $e) { return []; }
}

/* ---------------------------------------------------------------------------
   WHAT A SELLER IS HOLDING
   ----------------------------------------------------------------------------
   holding = verified cash and seller-side online collections
             − what they have handed in
             + what the company has paid out to them

   'pending' is the part they have reported but the office has not looked at
   yet, so it is shown beside the figure rather than inside it.
   --------------------------------------------------------------------------- */
function buy_seller_dues($db, $seller_id) {
    buy_ensure_schema($db);
    $seller_id = (int)$seller_id;
    $out = ['seller_id' => $seller_id, 'collected' => 0.0, 'pending' => 0.0, 'company' => 0.0,
            'handed_in' => 0.0, 'paid_out' => 0.0, 'holding' => 0.0, 'orders' => 0];

    $holds = [];
    foreach (buy_collection_modes() as $k => $m) { if ($m[2]) $holds[] = $k; }
    $in = implode(',', array_fill(0, count($holds), '?'));

    try {
        $r = $db->select(
            "SELECT COALESCE(SUM(CASE WHEN status='approved' AND mode IN ($in) THEN amount ELSE 0 END),0) held,
                    COALESCE(SUM(CASE WHEN status='approved' AND mode NOT IN ($in) THEN amount ELSE 0 END),0) comp,
                    COALESCE(SUM(CASE WHEN status='pending'  THEN amount ELSE 0 END),0) pend,
                    COUNT(DISTINCT order_id) n
               FROM seller_collections WHERE seller_id = ?",
            array_merge($holds, $holds, [$seller_id]));
        if (!empty($r)) {
            $out['collected'] = round((float)$r[0]['held'], 2);
            $out['company']   = round((float)$r[0]['comp'], 2);
            $out['pending']   = round((float)$r[0]['pend'], 2);
            $out['orders']    = (int)$r[0]['n'];
        }
    } catch (\Throwable $e) {}

    try {
        $s = $db->select(
            "SELECT COALESCE(SUM(CASE WHEN direction='in'  THEN amount ELSE 0 END),0) si,
                    COALESCE(SUM(CASE WHEN direction='out' THEN amount ELSE 0 END),0) so
               FROM seller_settlements WHERE seller_id = ?", [$seller_id]);
        if (!empty($s)) {
            $out['handed_in'] = round((float)$s[0]['si'], 2);
            $out['paid_out']  = round((float)$s[0]['so'], 2);
        }
    } catch (\Throwable $e) {}

    $out['holding'] = round($out['collected'] - $out['handed_in'] + $out['paid_out'], 2);
    return $out;
}

/** The same figure for everybody, for the office's Seller Dues screen. */
function buy_seller_dues_list($db, $f = []) {
    buy_ensure_schema($db);
    $rows = [];
    try {
        $rows = $db->select(
            "SELECT DISTINCT seller_id FROM seller_collections WHERE seller_id > 0
             UNION SELECT DISTINCT seller_id FROM seller_settlements WHERE seller_id > 0");
    } catch (\Throwable $e) { return []; }

    $out = [];
    foreach ($rows as $r) {
        $d = buy_seller_dues($db, (int)$r['seller_id']);
        try {
            $u = $db->select("SELECT name, mobile, email FROM users WHERE id = ?", [(int)$r['seller_id']]);
            $d['name']      = $u[0]['name'] ?? ('Seller #' . $r['seller_id']);
            $d['mobile']    = $u[0]['mobile'] ?? '';
            $d['email']     = $u[0]['email'] ?? '';
            $d['user_code'] = 'WBMCZ' . str_pad((int)$r['seller_id'], 4, '0', STR_PAD_LEFT);
        } catch (\Throwable $e) { $d['name'] = 'Seller #' . $r['seller_id']; }

        if (!empty($f['only_due']) && $d['holding'] <= 0 && $d['pending'] <= 0) continue;
        if (!empty($f['q'])) {
            $q = mb_strtolower(trim($f['q']));
            $hay = mb_strtolower(($d['name'] ?? '') . ' ' . ($d['mobile'] ?? '') . ' ' . ($d['user_code'] ?? ''));
            if (mb_strpos($hay, $q) === false) continue;
        }
        $out[] = $d;
    }
    usort($out, function ($a, $b) { return ($b['holding'] <=> $a['holding']); });
    return $out;
}

/**
 * Money changing hands between a seller and the company.
 *   in   the seller hands over what they collected
 *   out  the company pays the seller (their advance was larger than their share)
 */
function buy_settle($db, $seller_id, $amount, $direction = 'in', $d = []) {
    buy_ensure_schema($db);
    $seller_id = (int)$seller_id;
    $amount    = round((float)$amount, 2);
    $direction = $direction === 'out' ? 'out' : 'in';
    if ($seller_id <= 0)  return ['ok' => false, 'error' => 'Choose a seller.'];
    if ($amount    <= 0)  return ['ok' => false, 'error' => 'Enter an amount.'];

    if ($direction === 'in') {
        $due = buy_seller_dues($db, $seller_id);
        if ($amount > $due['holding'] + 0.009) {
            return ['ok' => false, 'error' => 'That seller is only holding ₹' . number_format($due['holding'], 2) . '.'];
        }
    }

    $actor = ['id' => (int)($_SESSION['user_id'] ?? 0), 'name' => (string)($_SESSION['user_name'] ?? '')];
    try {
        $db->insert(
            "INSERT INTO seller_settlements (seller_id, direction, amount, mode, reference, note, order_id, created_by, created_by_name)
             VALUES (?,?,?,?,?,?,?,?,?)",
            [$seller_id, $direction, $amount,
             (string)($d['mode'] ?? 'cash'),
             trim((string)($d['reference'] ?? '')) ?: null,
             trim((string)($d['note'] ?? '')) ?: null,
             !empty($d['order_id']) ? (int)$d['order_id'] : null,
             $actor['id'] ?: null, $actor['name'] ?: null]);
    } catch (\Throwable $e) { return ['ok' => false, 'error' => 'Could not save it: ' . $e->getMessage()]; }

    return ['ok' => true, 'holding' => buy_seller_dues($db, $seller_id)['holding']];
}

function buy_settlements($db, $f = []) {
    buy_ensure_schema($db);
    $w = ['1=1']; $a = [];
    if (!empty($f['seller_id'])) { $w[] = "s.seller_id = ?"; $a[] = (int)$f['seller_id']; }
    if (!empty($f['direction'])) { $w[] = "s.direction = ?"; $a[] = (string)$f['direction']; }
    if (!empty($f['from']))      { $w[] = "DATE(s.created_at) >= ?"; $a[] = (string)$f['from']; }
    if (!empty($f['to']))        { $w[] = "DATE(s.created_at) <= ?"; $a[] = (string)$f['to']; }
    $lim = max(1, min(500, (int)($f['limit'] ?? 50)));
    $off = max(0, (int)($f['offset'] ?? 0));
    try {
        return $db->select("SELECT s.*, u.name seller_name, u.mobile seller_mobile
                              FROM seller_settlements s
                              LEFT JOIN users u ON u.id = s.seller_id
                             WHERE " . implode(' AND ', $w) . "
                             ORDER BY s.id DESC LIMIT $lim OFFSET $off", $a);
    } catch (\Throwable $e) { return []; }
}

/* ---------------------------------------------------------------------------
   PAYMENT CODE
   ----------------------------------------------------------------------------
   The customer chose to pay at the counter. The order exists, nothing has been
   taken, and the code is what ties the two together when the money arrives.
   --------------------------------------------------------------------------- */
function buy_find_payment_code($db, $code) {
    buy_ensure_schema($db);
    $code = strtoupper(trim((string)$code));
    if ($code === '') return null;
    try { $r = $db->select("SELECT id FROM orders WHERE payment_code = ? LIMIT 1", [$code]); }
    catch (\Throwable $e) { return null; }
    if (empty($r)) return null;
    return buy_order($db, (int)$r[0]['id']);
}

/** The money came in against a payment code. */
function buy_redeem_code($db, $code, $d = []) {
    $o = buy_find_payment_code($db, $code);
    if (!$o) return ['ok' => false, 'error' => 'No order carries that code.'];
    if ((string)$o['payment_code_status'] === 'paid') return ['ok' => false, 'error' => 'That code has already been used.'];
    if ((string)$o['stage'] === 'cancelled') return ['ok' => false, 'error' => 'That order was cancelled.'];

    $amount = round((float)($d['amount'] ?? $o['due_amount']), 2);
    $mode   = (string)($d['mode'] ?? 'company');

    $res = buy_record_collection($db, (int)$o['id'], [
        'amount' => $amount, 'mode' => $mode,
        'reference' => (string)($d['reference'] ?? $o['payment_code']),
        'note' => 'Paid against code ' . $o['payment_code'],
        'seller_id' => (int)($d['seller_id'] ?? $o['seller_id']),
        'auto' => true,
    ]);
    if (empty($res['ok'])) return $res;

    try {
        $db->insert("UPDATE orders SET payment_code_status = 'paid', updated_at = NOW() WHERE id = ?", [(int)$o['id']]);
    } catch (\Throwable $e) {}
    buy_set_stage($db, (int)$o['id'], 'paid');
    return ['ok' => true, 'order_id' => (int)$o['id'], 'amount' => $amount];
}

/* ---------------------------------------------------------------------------
   FINISHING, AND UNDOING
   --------------------------------------------------------------------------- */

/** The service is set up and handed over. */
function buy_activate_order($db, $order_id, $note = '') {
    $o = buy_order($db, (int)$order_id);
    if (!$o) return ['ok' => false, 'error' => 'Order not found.'];
    if ((float)$o['due_amount'] > 0.009) {
        return ['ok' => false, 'error' => '₹' . number_format((float)$o['due_amount'], 2) . ' is still outstanding on this order.'];
    }
    buy_set_stage($db, (int)$order_id, 'activated');
    buy_log($db, $o, 'activated', 'Service Activated', $note);
    return ['ok' => true];
}

/**
 * Done. The shares are worked out if that has not happened yet, and the
 * seller's earnings are released into their wallet.
 */
function buy_complete_order($db, $order_id, $note = '') {
    $order_id = (int)$order_id;
    $o = buy_order($db, $order_id);
    if (!$o) return ['ok' => false, 'error' => 'Order not found.'];
    if ((string)$o['stage'] === 'completed') return ['ok' => true, 'already' => true];
    if ((float)$o['due_amount'] > 0.009) {
        return ['ok' => false, 'error' => '₹' . number_format((float)$o['due_amount'], 2) . ' is still to be collected.'];
    }

    buy_settle_shares($db, $order_id);

    try { $db->insert("UPDATE orders SET status = 'Completed', stage = 'completed', updated_at = NOW() WHERE id = ?", [$order_id]); }
    catch (\Throwable $e) { return ['ok' => false, 'error' => 'Could not save it.']; }
    try { $db->insert("UPDATE order_items SET status = 'completed' WHERE order_id = ?", [$order_id]); }
    catch (\Throwable $e) {}

    $released = shares_release_order($db, $order_id, 'Completed');
    buy_log($db, $o, 'completed', 'Order Completed',
            trim($note . ($released ? ' · ' . $released . ' earning(s) released' : '')));

    try {
        $db->insert("INSERT INTO notifications (user_id, title, message, type, link, is_read) VALUES (?,?,?,'order',?,0)", [
            (int)$o['user_id'], 'Order completed',
            'Your order ' . $o['order_code'] . ' has been completed.',
            'order.php?id=' . $order_id,
        ]);
    } catch (\Throwable $e) {}

    return ['ok' => true, 'released' => $released];
}

/**
 * Cancel, and put back whatever the customer had already paid online. Cash the
 * seller took is not touched here — that is a conversation, not a button.
 */
function buy_cancel_order($db, $order_id, $reason = '', $refund = true) {
    $order_id = (int)$order_id;
    $o = buy_order($db, $order_id);
    if (!$o) return ['ok' => false, 'error' => 'Order not found.'];
    if ((string)$o['stage'] === 'completed') return ['ok' => false, 'error' => 'A completed order cannot be cancelled.'];
    if ((string)$o['stage'] === 'cancelled') return ['ok' => true, 'already' => true];

    try { $db->insert("UPDATE orders SET status = 'Cancelled', stage = 'cancelled', updated_at = NOW() WHERE id = ?", [$order_id]); }
    catch (\Throwable $e) { return ['ok' => false, 'error' => 'Could not save it.']; }
    try { $db->insert("UPDATE order_items SET status = 'cancelled' WHERE order_id = ?", [$order_id]); }
    catch (\Throwable $e) {}

    $back = round((float)$o['wallet_used'] + (float)$o['gateway_used'], 2);
    if ($refund && $back > 0 && function_exists('wallet_credit')) {
        wallet_credit($db, (int)$o['user_id'], WALLET_MAIN, $back, [
            'category' => 'refund',
            'description' => 'Refund for cancelled order ' . $o['order_code'],
            'reference_type' => 'order', 'reference_id' => $order_id,
        ]);
        buy_log($db, $o, 'cancelled', 'Refunded to wallet', '', $back);
    }

    buy_log($db, $o, 'cancelled', 'Order Cancelled', trim($reason));
    return ['ok' => true, 'refunded' => $refund ? $back : 0.0];
}

/* ---------------------------------------------------------------------------
   HEADLINE FIGURES for the dashboards
   --------------------------------------------------------------------------- */
function buy_stats($db, $f = []) {
    buy_ensure_schema($db);
    $w = ['1=1']; $a = [];
    if (!empty($f['seller_id'])) { $w[] = "seller_id = ?"; $a[] = (int)$f['seller_id']; }
    if (!empty($f['user_id']))   { $w[] = "user_id = ?";   $a[] = (int)$f['user_id']; }
    $out = ['orders' => 0, 'visit' => 0, 'due' => 0.0, 'collected' => 0.0,
            'paid' => 0.0, 'pending_visits' => 0, 'completed' => 0];
    try {
        $r = $db->select(
            "SELECT COUNT(*) n,
                    COALESCE(SUM(fulfilment = 'visit'),0) v,
                    COALESCE(SUM(due_amount),0) d,
                    COALESCE(SUM(collected_amount),0) c,
                    COALESCE(SUM(paid_amount),0) p,
                    COALESCE(SUM(stage IN ('assigned','visit') AND due_amount > 0),0) pv,
                    COALESCE(SUM(stage = 'completed'),0) done
               FROM orders WHERE " . implode(' AND ', $w), $a);
        if (!empty($r)) {
            $out['orders']         = (int)$r[0]['n'];
            $out['visit']          = (int)$r[0]['v'];
            $out['due']            = round((float)$r[0]['d'], 2);
            $out['collected']      = round((float)$r[0]['c'], 2);
            $out['paid']           = round((float)$r[0]['p'], 2);
            $out['pending_visits'] = (int)$r[0]['pv'];
            $out['completed']      = (int)$r[0]['done'];
        }
    } catch (\Throwable $e) {}
    return $out;
}

/* ---------------------------------------------------------------------------
   THE LITTLE PICTURE ON A PRODUCT
   ----------------------------------------------------------------------------
   A product either carries an uploaded image or a Font Awesome class, and
   which one is decided by is_img. icon_class already holds a path that works
   from the site root, exactly as the category page uses it — so it is passed
   straight through rather than guessed at.

   Returns ['img' => 'uploads/…', 'icon' => 'fa-…'] with one of the two filled.
   --------------------------------------------------------------------------- */
function buy_thumb($svc, $fallback = 'fa-file-lines') {
    $cls = trim((string)($svc['icon_class'] ?? ''));
    if ($cls === '') { return ['img' => '', 'icon' => $fallback]; }

    /* is_img is the intended answer, but some rows carry a file path with the
       flag never set. Anything that looks like a path or an image file is
       treated as a picture regardless, so a stale flag cannot blank the logo. */
    $looks_like_file = (strpos($cls, '/') !== false)
                    || preg_match('~\.(png|jpe?g|gif|webp|svg|bmp|avif)$~i', $cls)
                    || preg_match('~^(https?:)?//~i', $cls);

    /* …and the reverse guard: a Font Awesome class is an icon even if the flag
       says otherwise, so a wrong flag never asks the browser for "fa-bolt.png". */
    $looks_like_icon = preg_match('~^(fa[srlbd]?[ -])~i', $cls) && !$looks_like_file;

    if ($looks_like_icon) { return ['img' => '', 'icon' => $cls]; }
    if ((int)($svc['is_img'] ?? 0) === 1 || $looks_like_file) {
        return ['img' => $cls, 'icon' => ''];
    }
    return ['img' => '', 'icon' => $cls];
}

} /* end of the function_exists guard */
