<?php
/* MJ Marketplace v2.9.2 Invoice First + Editable Payment Checkout helpers */
if (!function_exists('mj291_setting_default')) {
function mj291_setting_default(string $key, string $value): void {
    try {
        if (function_exists('setting') && setting($key, '') === '') {
            q('INSERT INTO mj_settings(setting_key,setting_value) VALUES(?,?) ON DUPLICATE KEY UPDATE setting_value=setting_value', [$key, $value]);
        }
    } catch (Throwable $e) {}
}
}

if (!function_exists('mj291_add_col')) {
function mj291_add_col(string $table, string $column, string $definition): void {
    try { if (table_exists($table) && !mj_column_exists($table, $column)) { q("ALTER TABLE `$table` ADD COLUMN `$column` $definition"); } } catch (Throwable $e) {}
}
}

if (!function_exists('mj291_payment_ensure_schema')) {
function mj291_payment_ensure_schema(): void {
    static $done = false;
    if ($done) { return; }
    $done = true;
    if (!function_exists('mj_is_installed') || !mj_is_installed()) { return; }
    // v2.9.3: after migration, skip expensive CREATE/ALTER checks on every page load.
    try {
        if (setting('v293_payment_performance_schema_ready','0') === '1'
            && table_exists('mj_payment_batches')
            && table_exists('mj_payment_batch_items')
            && table_exists('mj_invoices')
            && table_exists('mj_invoice_items')
            && table_exists('mj_receipts')) {
            return;
        }
    } catch (Throwable $e) {}
    try { if (function_exists('mj_upgrade_payment_ensure_schema')) { mj_upgrade_payment_ensure_schema(); } } catch (Throwable $e) {}
    try { if (function_exists('mj_listing_paid_upgrade_ensure_schema')) { mj_listing_paid_upgrade_ensure_schema(); } } catch (Throwable $e) {}
    try { if (function_exists('mj_user_limits_ensure_schema')) { mj_user_limits_ensure_schema(); } } catch (Throwable $e) {}
    try { if (function_exists('mj_v281_ensure_schema')) { mj_v281_ensure_schema(); } } catch (Throwable $e) {}

    try {
        q("CREATE TABLE IF NOT EXISTS mj_payment_batches (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            batch_no VARCHAR(80) NULL UNIQUE,
            invoice_id BIGINT NULL,
            user_id BIGINT NOT NULL,
            country_id BIGINT NULL,
            currency_code VARCHAR(10) NOT NULL DEFAULT 'SGD',
            subtotal_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            payment_method VARCHAR(80) NULL,
            reference_no VARCHAR(190) NULL,
            proof_path VARCHAR(500) NULL,
            original_name VARCHAR(255) NULL,
            mime_type VARCHAR(120) NULL,
            file_size BIGINT NULL,
            note TEXT NULL,
            status VARCHAR(50) NOT NULL DEFAULT 'pending_review',
            admin_note TEXT NULL,
            reviewed_by BIGINT NULL,
            reviewed_at DATETIME NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX(user_id), INDEX(invoice_id), INDEX(status), INDEX(created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    } catch (Throwable $e) {}

    try {
        q("CREATE TABLE IF NOT EXISTS mj_payment_batch_items (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            batch_id BIGINT NOT NULL,
            user_id BIGINT NOT NULL,
            item_type VARCHAR(80) NOT NULL,
            item_id BIGINT NOT NULL DEFAULT 0,
            item_label VARCHAR(190) NULL,
            item_description TEXT NULL,
            listing_id BIGINT NULL,
            company_id BIGINT NULL,
            quantity INT NOT NULL DEFAULT 1,
            unit_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            currency_code VARCHAR(10) NOT NULL DEFAULT 'SGD',
            status VARCHAR(50) NOT NULL DEFAULT 'pending_review',
            activation_status VARCHAR(50) NOT NULL DEFAULT 'not_activated',
            admin_note TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX(batch_id), INDEX(user_id), INDEX(item_type,item_id), INDEX(status), INDEX(activation_status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    } catch (Throwable $e) {}

    try {
        q("CREATE TABLE IF NOT EXISTS mj_invoice_items (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            invoice_id BIGINT NOT NULL,
            user_id BIGINT NOT NULL,
            item_type VARCHAR(80) NULL,
            item_id BIGINT NULL,
            description TEXT NULL,
            quantity INT NOT NULL DEFAULT 1,
            unit_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            currency_code VARCHAR(10) NOT NULL DEFAULT 'SGD',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX(invoice_id), INDEX(user_id), INDEX(item_type,item_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    } catch (Throwable $e) {}

    try {
        q("CREATE TABLE IF NOT EXISTS mj_receipts (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            receipt_no VARCHAR(80) NULL UNIQUE,
            invoice_id BIGINT NULL,
            batch_id BIGINT NULL,
            user_id BIGINT NOT NULL,
            amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            currency_code VARCHAR(10) NOT NULL DEFAULT 'SGD',
            payment_method VARCHAR(80) NULL,
            reference_no VARCHAR(190) NULL,
            status VARCHAR(40) NOT NULL DEFAULT 'paid',
            admin_id BIGINT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX(invoice_id), INDEX(batch_id), INDEX(user_id), INDEX(receipt_no)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    } catch (Throwable $e) {}

    $invoiceCols = [
        'batch_id' => 'BIGINT NULL',
        'proof_path' => 'VARCHAR(500) NULL',
        'payment_method' => 'VARCHAR(80) NULL',
        'reference_no' => 'VARCHAR(190) NULL',
        'admin_note' => 'TEXT NULL',
        'updated_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
    ];
    foreach ($invoiceCols as $c=>$d) { mj291_add_col('mj_invoices', $c, $d); }

    $proofCols = [
        'batch_id' => 'BIGINT NULL',
        'invoice_id' => 'BIGINT NULL',
        'receipt_id' => 'BIGINT NULL'
    ];
    foreach ($proofCols as $c=>$d) { mj291_add_col('mj_upgrade_payment_proofs', $c, $d); }

    $limitCols = [
        'batch_id' => 'BIGINT NULL',
        'invoice_id' => 'BIGINT NULL',
        'approved_slots' => 'INT NULL',
        'unit_amount' => 'DECIMAL(12,2) NULL'
    ];
    foreach ($limitCols as $c=>$d) { mj291_add_col('mj_user_limit_upgrade_requests', $c, $d); }

    foreach (['mj_listing_renewal_requests','mj_listing_upgrade_requests','mj_payment_requests'] as $t) {
        mj291_add_col($t, 'batch_id', 'BIGINT NULL');
        mj291_add_col($t, 'invoice_id', 'BIGINT NULL');
    }

    $defaults = [
        'payment_cart_enabled' => '1',
        'payment_cart_allow_partial_admin_approval' => '1',
        'payment_cart_tax_enabled' => setting('billing_tax_enabled','0'),
        'payment_cart_tax_label' => setting('billing_tax_label','GST/VAT'),
        'payment_cart_default_tax_rate' => setting('billing_default_tax_rate','0'),
        'extra_listing_slot_charge_amount' => '5.00',
        'extra_company_slot_charge_amount' => '5.00',
        'extra_slot_currency' => setting('listing_upgrade_currency','SGD') ?: 'SGD',
        'extra_slot_min_qty' => '1',
        'extra_slot_max_qty' => '100',
        'listing_feature_charge_amount' => '10.00',
        'listing_sponsor_charge_amount' => '10.00',
        'listing_upgrade_days' => '30'
    ];
    foreach ($defaults as $k=>$v) { mj291_setting_default($k, (string)$v); }
    try { q('INSERT INTO mj_settings(setting_key,setting_value) VALUES(?,?) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value)', ['v293_payment_performance_schema_ready','1']); } catch (Throwable $e) {}
}
}

if (!function_exists('mj291_status_class')) {
function mj291_status_class(string $status): string {
    $s = strtolower(trim($status));
    if (in_array($s, ['approved','verified','paid','completed','activated','payment_verified','wallet_paid'], true)) { return 'success'; }
    if (in_array($s, ['rejected','failed','cancelled','payment_rejected'], true)) { return 'danger'; }
    if (in_array($s, ['need_more_info','partial_paid','partial_approved'], true)) { return 'info'; }
    if (in_array($s, ['invoice_created','invoice_generated','draft'], true)) { return 'secondary'; }
    if (in_array($s, ['pending','pending_review','submitted','uploaded','payment_uploaded','overdue','pending_payment','payment_required','unpaid'], true)) { return 'warning'; }
    return 'secondary';
}
}

if (!function_exists('mj291_status_badge')) {
function mj291_status_badge(string $status): string {
    $raw = trim((string)$status);
    if ($raw === '') { $raw = 'pending'; }
    $key = strtolower($raw);
    $labels = [
        'invoice_created' => 'Invoice Created',
        'invoice_generated' => 'Invoice Created',
        'draft' => 'Draft',
        'unpaid' => 'Pending Payment',
        'pending_payment' => 'Pending Payment',
        'payment_required' => 'Pending Payment',
        'pending' => 'Pending',
        'pending_review' => 'Pending Admin Review',
        'payment_uploaded' => 'Pending Admin Review',
        'uploaded' => 'Pending Admin Review',
        'submitted' => 'Submitted',
        'approved' => 'Approved',
        'verified' => 'Verified',
        'payment_verified' => 'Approved',
        'paid' => 'Paid',
        'wallet_paid' => 'Wallet Paid',
        'completed' => 'Completed',
        'activated' => 'Activated',
        'rejected' => 'Rejected',
        'payment_rejected' => 'Rejected',
        'failed' => 'Failed',
        'cancelled' => 'Cancelled',
        'canceled' => 'Cancelled',
        'need_more_info' => 'Need More Info',
        'partial_paid' => 'Partial Paid',
        'partial_approved' => 'Partial Approved',
        'overdue' => 'Overdue'
    ];
    $label = $labels[$key] ?? ucwords(str_replace('_',' ', $raw));
    $cls = mj291_status_class($raw);
    $bs5 = [
        'success' => 'bg-success text-white',
        'danger' => 'bg-danger text-white',
        'warning' => 'bg-warning text-dark',
        'info' => 'bg-info text-dark',
        'secondary' => 'bg-secondary text-white',
        'primary' => 'bg-primary text-white'
    ];
    $bg = $bs5[$cls] ?? 'bg-secondary text-white';
    return '<span class="badge rounded-pill badge-'.$cls.' '.$bg.'">'.e($label).'</span>';
}
}

if (!function_exists('mj291_payable_status')) {
function mj291_payable_status(?string $status): bool {
    $s = strtolower(trim((string)$status));
    return in_array($s, ['', 'payment_required','pending','pending_payment','unpaid','overdue','need_more_info','rejected','payment_rejected'], true);
}
}

if (!function_exists('mj291_payment_methods')) {
function mj291_payment_methods(): array {
    try { return function_exists('mj_upgrade_payment_methods') ? mj_upgrade_payment_methods() : []; } catch (Throwable $e) { return []; }
}
}

if (!function_exists('mj291_upload_payment_proof')) {
function mj291_upload_payment_proof(int $userId, string $field='payment_proof'): array {
    if (empty($_FILES[$field]) || !is_array($_FILES[$field]) || (int)($_FILES[$field]['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) {
        throw new Exception('Please upload payment proof.');
    }
    $f = $_FILES[$field];
    if ((int)$f['error'] !== UPLOAD_ERR_OK) { throw new Exception(upload_error_message((int)$f['error'])); }
    if ((int)$f['size'] > 8*1024*1024) { throw new Exception('Payment proof is too large. Maximum allowed size is 8MB.'); }
    $name = (string)($f['name'] ?? 'proof');
    $ext = strtolower(pathinfo($name, PATHINFO_EXTENSION));
    if (!in_array($ext, ['jpg','jpeg','png','webp','pdf'], true)) { throw new Exception('Allowed proof files: JPG, PNG, WEBP or PDF only.'); }
    [$dir, $rel] = ensure_upload_dir('payment-batches');
    $safe = 'payment-'.$userId.'-'.date('YmdHis').'-'.bin2hex(random_bytes(4)).'.'.$ext;
    $dest = $dir.'/'.$safe;
    if (!move_uploaded_file((string)$f['tmp_name'], $dest)) { throw new Exception('Unable to save payment proof. Please check upload folder permission.'); }
    @chmod($dest, 0644);
    return ['path'=>$rel.'/'.$safe, 'original_name'=>$name, 'mime_type'=>(string)($f['type'] ?? ''), 'file_size'=>(int)($f['size'] ?? 0)];
}
}

if (!function_exists('mj291_next_doc_no')) {
function mj291_next_doc_no(string $prefix, int $id, string $currency='SGD'): string {
    $cc = strtoupper(preg_replace('/[^A-Z0-9]/', '', $currency ?: 'SGD'));
    if (strlen($cc) > 3) { $cc = substr($cc, 0, 3); }
    return $prefix.'-'.$cc.'-'.date('Y').'-'.str_pad((string)$id, 6, '0', STR_PAD_LEFT);
}
}

if (!function_exists('mj291_pending_upgrade_items')) {
function mj291_pending_upgrade_items(int $userId): array {
    mj291_payment_ensure_schema();
    $rows = [];
    try {
        if (table_exists('mj_listing_renewal_requests')) {
            foreach (safe_all_rows_public('SELECT "renewal" item_type, r.id item_id, r.listing_id, 0 company_id, l.title item_title, "Renew Listing" item_label, r.requested_days quantity, r.payment_amount total_amount, r.payment_currency currency_code, r.payment_status, r.status, r.requested_at created_at FROM mj_listing_renewal_requests r LEFT JOIN mj_listings l ON l.id=r.listing_id WHERE r.user_id=? AND IFNULL(r.payment_required,0)=1 ORDER BY r.id DESC LIMIT 100', [$userId]) as $r) {
                if (mj291_payable_status((string)($r['payment_status'] ?? ''))) { $rows[] = $r; }
            }
        }
        if (table_exists('mj_listing_upgrade_requests')) {
            foreach (safe_all_rows_public('SELECT "listing_upgrade" item_type, r.id item_id, r.listing_id, 0 company_id, l.title item_title, r.upgrade_type, r.requested_days quantity, r.charge_amount total_amount, r.currency_code, r.payment_status, r.status, r.requested_at created_at FROM mj_listing_upgrade_requests r LEFT JOIN mj_listings l ON l.id=r.listing_id WHERE r.user_id=? AND IFNULL(r.payment_required,0)=1 ORDER BY r.id DESC LIMIT 100', [$userId]) as $r) {
                $r['item_label'] = function_exists('mj_listing_upgrade_type_label') ? mj_listing_upgrade_type_label((string)($r['upgrade_type'] ?? '')) : ucwords(str_replace('_',' ', (string)($r['upgrade_type'] ?? 'Listing Upgrade')));
                if (mj291_payable_status((string)($r['payment_status'] ?? ''))) { $rows[] = $r; }
            }
        }
        if (table_exists('mj_user_limit_upgrade_requests')) {
            foreach (safe_all_rows_public('SELECT "limit_upgrade" item_type, r.id item_id, 0 listing_id, 0 company_id, CONCAT("Extra ", r.request_type, " slots") item_title, r.request_type upgrade_type, r.requested_slots quantity, r.charge_amount total_amount, r.currency_code, r.payment_status, r.status, r.requested_at created_at FROM mj_user_limit_upgrade_requests r WHERE r.user_id=? AND IFNULL(r.payment_required,0)=1 ORDER BY r.id DESC LIMIT 100', [$userId]) as $r) {
                $r['item_label'] = ((string)($r['upgrade_type'] ?? '') === 'company') ? 'Extra Company Slots' : 'Extra Listing Slots';
                if (mj291_payable_status((string)($r['payment_status'] ?? ''))) { $rows[] = $r; }
            }
        }
        if (table_exists('mj_payment_requests')) {
            foreach (safe_all_rows_public('SELECT "payment_request" item_type, r.id item_id, 0 listing_id, IFNULL(r.company_id,0) company_id, r.title item_title, IFNULL(r.request_type,"Manual Payment") upgrade_type, 1 quantity, IFNULL(r.total_amount,r.amount) total_amount, r.currency_code, r.status payment_status, r.status, r.created_at FROM mj_payment_requests r WHERE r.user_id=? ORDER BY r.id DESC LIMIT 100', [$userId]) as $r) {
                $r['item_label'] = ucwords(str_replace('_',' ', (string)($r['upgrade_type'] ?? 'Payment Request')));
                if (mj291_payable_status((string)($r['payment_status'] ?? ''))) { $rows[] = $r; }
            }
        }
    } catch (Throwable $e) {}
    usort($rows, function($a,$b){ return strcmp((string)($b['created_at'] ?? ''), (string)($a['created_at'] ?? '')); });
    return $rows;
}
}

if (!function_exists('mj291_collect_checkout_items')) {
function mj291_collect_checkout_items(int $userId, array $selected, array $post): array {
    mj291_payment_ensure_schema();
    $items = [];
    $currency = setting('listing_upgrade_currency', 'SGD') ?: 'SGD';
    $pending = [];
    foreach (mj291_pending_upgrade_items($userId) as $r) { $pending[(string)$r['item_type'].':'.(int)$r['item_id']] = $r; }

    foreach ($selected as $raw) {
        $key = trim((string)$raw);
        if ($key === '') { continue; }
        if (isset($pending[$key])) {
            $r = $pending[$key];
            $qty = max(1, (int)($r['quantity'] ?? 1));
            $total = max(0, (float)($r['total_amount'] ?? 0));
            $unit = $qty > 0 ? ($total / $qty) : $total;
            $items[] = [
                'item_type'=>(string)$r['item_type'], 'item_id'=>(int)$r['item_id'], 'listing_id'=>(int)($r['listing_id'] ?? 0), 'company_id'=>(int)($r['company_id'] ?? 0),
                'item_label'=>(string)($r['item_label'] ?? 'Payment Item'), 'item_description'=>(string)($r['item_title'] ?? ''), 'quantity'=>$qty, 'unit_amount'=>$unit, 'total_amount'=>$total,
                'currency_code'=>(string)($r['currency_code'] ?: $currency)
            ];
        }
    }

    $minQty = max(1, (int)setting('extra_slot_min_qty', '1'));
    $maxQty = max($minQty, (int)setting('extra_slot_max_qty', '100'));
    $slotCurrency = setting('extra_slot_currency', $currency) ?: $currency;

    $extraListingQty = (int)($post['extra_listing_qty'] ?? 0);
    if (!empty($post['include_extra_listing']) && $extraListingQty > 0) {
        if (!mj_user_feature_enabled('extra_listing_slots', $userId)) { throw new Exception('Extra listing slots are not enabled for your account.'); }
        $qty = min($maxQty, max($minQty, $extraListingQty));
        $unit = max(0, (float)setting('extra_listing_slot_charge_amount', '5.00'));
        $items[] = ['item_type'=>'extra_listing_slot_new','item_id'=>0,'listing_id'=>0,'company_id'=>0,'item_label'=>'Extra Listing Slots','item_description'=>'Buy '.$qty.' extra listing slot(s)','quantity'=>$qty,'unit_amount'=>$unit,'total_amount'=>$unit*$qty,'currency_code'=>$slotCurrency];
    }

    $extraCompanyQty = (int)($post['extra_company_qty'] ?? 0);
    if (!empty($post['include_extra_company']) && $extraCompanyQty > 0) {
        if (!mj_user_feature_enabled('extra_company_slots', $userId)) { throw new Exception('Extra company slots are not enabled for your account.'); }
        $qty = min($maxQty, max($minQty, $extraCompanyQty));
        $unit = max(0, (float)setting('extra_company_slot_charge_amount', '5.00'));
        $items[] = ['item_type'=>'extra_company_slot_new','item_id'=>0,'listing_id'=>0,'company_id'=>0,'item_label'=>'Extra Company Slots','item_description'=>'Buy '.$qty.' extra company slot(s)','quantity'=>$qty,'unit_amount'=>$unit,'total_amount'=>$unit*$qty,'currency_code'=>$slotCurrency];
    }

    if (!$items) { throw new Exception('Please select at least one pending payment item or extra slot quantity.'); }
    $firstCurrency = (string)$items[0]['currency_code'];
    foreach ($items as $it) {
        if ((string)$it['currency_code'] !== $firstCurrency) { throw new Exception('Please pay items with the same currency together. Create a separate checkout for another currency.'); }
    }
    return $items;
}
}

if (!function_exists('mj291_create_limit_request_for_batch')) {
function mj291_create_limit_request_for_batch(int $userId, string $slotType, int $qty, float $unit, string $currency, int $batchId=0, int $invoiceId=0, string $note='', string $paymentStatus='payment_uploaded'): int {
    mj291_payment_ensure_schema();
    $slotType = $slotType === 'company' ? 'company' : 'listing';
    $limits = function_exists('mj_user_limits_get') ? mj_user_limits_get($userId) : ['max_listings'=>0,'max_companies'=>0];
    $currentLimit = $slotType === 'company' ? (int)($limits['max_companies'] ?? 0) : (int)($limits['max_listings'] ?? 0);
    $requestedLimit = $currentLimit > 0 ? ($currentLimit + $qty) : $qty;
    $amount = $qty * $unit;
    q('INSERT INTO mj_user_limit_upgrade_requests(user_id,request_type,requested_slots,current_limit,requested_limit,charge_amount,currency_code,payment_required,payment_status,status,user_note,requested_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,NOW())', [
        $userId, $slotType, $qty, $currentLimit, $requestedLimit, $amount, $currency, $amount > 0 ? 1 : 0, $paymentStatus, $paymentStatus, $note
    ]);
    $id = (int)db()->lastInsertId();
    try { if ($batchId || $invoiceId) { q('UPDATE mj_user_limit_upgrade_requests SET batch_id=?, invoice_id=?, unit_amount=? WHERE id=?', [$batchId ?: null, $invoiceId ?: null, $unit, $id]); } } catch (Throwable $e) {}
    return $id;
}
}

if (!function_exists('mj291_create_checkout_batch')) {
function mj291_create_checkout_batch(int $userId, array $post): int {
    mj291_payment_ensure_schema();
    if (setting('upgrade_payment_proof_upload_enabled','1') !== '1') { throw new Exception('Payment proof upload is disabled by admin.'); }
    if (!mj_user_feature_enabled('payment_proof_upload', $userId)) { throw new Exception('Payment proof upload is not enabled for your account. Please contact admin.'); }
    $methods = mj291_payment_methods();
    if (!$methods) { throw new Exception('No active payment method. Please contact admin.'); }
    $method = trim((string)($post['payment_method'] ?? ''));
    if ($method === '' || !isset($methods[$method])) { throw new Exception('Please select an active payment method.'); }
    $ref = trim((string)($post['payment_reference'] ?? ''));
    if ($ref === '') { throw new Exception('Please enter payment/reference number.'); }
    $selected = isset($post['pay_items']) && is_array($post['pay_items']) ? $post['pay_items'] : [];
    $items = mj291_collect_checkout_items($userId, $selected, $post);
    $proof = mj291_upload_payment_proof($userId, 'payment_proof');
    $currency = (string)$items[0]['currency_code'];
    $subtotal = 0.0;
    foreach ($items as $it) { $subtotal += (float)$it['total_amount']; }
    $taxEnabled = setting('payment_cart_tax_enabled', setting('billing_tax_enabled','0')) === '1';
    $taxRate = $taxEnabled ? max(0, (float)setting('payment_cart_default_tax_rate', setting('billing_default_tax_rate','0'))) : 0;
    $tax = round($subtotal * $taxRate / 100, 2);
    $total = $subtotal + $tax;
    $note = trim((string)($post['payment_note'] ?? ''));
    $countryId = 0;
    try { $u = one('SELECT country_id FROM mj_users WHERE id=? LIMIT 1', [$userId]); $countryId = (int)($u['country_id'] ?? 0); } catch (Throwable $e) {}

    q('INSERT INTO mj_invoices(user_id,company_id,invoice_type,title,amount,tax_amount,total_amount,currency_code,status,due_date,payment_method,reference_no,proof_path,created_at) VALUES(?,NULL,"proforma","Marketplace payment checkout",?,?,?,?,"pending_review",DATE_ADD(CURDATE(), INTERVAL 7 DAY),?,?,?,NOW())', [
        $userId, $subtotal, $tax, $total, $currency, $method, $ref, $proof['path']
    ]);
    $invoiceId = (int)db()->lastInsertId();
    $invoiceNo = mj291_next_doc_no('INV', $invoiceId, $currency);
    q('UPDATE mj_invoices SET invoice_no=? WHERE id=?', [$invoiceNo, $invoiceId]);

    q('INSERT INTO mj_payment_batches(batch_no,invoice_id,user_id,country_id,currency_code,subtotal_amount,tax_amount,total_amount,payment_method,reference_no,proof_path,original_name,mime_type,file_size,note,status,created_at) VALUES(NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,"pending_review",NOW())', [
        $invoiceId, $userId, $countryId ?: null, $currency, $subtotal, $tax, $total, $method, $ref, $proof['path'], $proof['original_name'], $proof['mime_type'], $proof['file_size'], $note
    ]);
    $batchId = (int)db()->lastInsertId();
    $batchNo = mj291_next_doc_no('PAY', $batchId, $currency);
    q('UPDATE mj_payment_batches SET batch_no=? WHERE id=?', [$batchNo, $batchId]);
    try { q('UPDATE mj_invoices SET batch_id=? WHERE id=?', [$batchId, $invoiceId]); } catch (Throwable $e) {}

    foreach ($items as $it) {
        $itemType = (string)$it['item_type'];
        $itemId = (int)$it['item_id'];
        if ($itemType === 'extra_listing_slot_new') {
            $itemId = mj291_create_limit_request_for_batch($userId, 'listing', (int)$it['quantity'], (float)$it['unit_amount'], $currency, $batchId, $invoiceId, $note);
            $itemType = 'limit_upgrade';
            $it['item_description'] = 'Extra listing slots x '.(int)$it['quantity'];
        } elseif ($itemType === 'extra_company_slot_new') {
            $itemId = mj291_create_limit_request_for_batch($userId, 'company', (int)$it['quantity'], (float)$it['unit_amount'], $currency, $batchId, $invoiceId, $note);
            $itemType = 'limit_upgrade';
            $it['item_description'] = 'Extra company slots x '.(int)$it['quantity'];
        }
        q('INSERT INTO mj_payment_batch_items(batch_id,user_id,item_type,item_id,item_label,item_description,listing_id,company_id,quantity,unit_amount,total_amount,currency_code,status,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,"pending_review",NOW())', [
            $batchId, $userId, $itemType, $itemId, $it['item_label'], $it['item_description'], (int)$it['listing_id'] ?: null, (int)$it['company_id'] ?: null, (int)$it['quantity'], (float)$it['unit_amount'], (float)$it['total_amount'], $currency
        ]);
        q('INSERT INTO mj_invoice_items(invoice_id,user_id,item_type,item_id,description,quantity,unit_amount,total_amount,currency_code,created_at) VALUES(?,?,?,?,?,?,?,?,?,NOW())', [
            $invoiceId, $userId, $itemType, $itemId, trim((string)$it['item_label'].' - '.(string)$it['item_description']), (int)$it['quantity'], (float)$it['unit_amount'], (float)$it['total_amount'], $currency
        ]);
        try {
            if ($itemType === 'renewal') { q('UPDATE mj_listing_renewal_requests SET payment_status="payment_uploaded", status=IF(status="pending_payment","payment_uploaded",status), batch_id=?, invoice_id=? WHERE id=? AND user_id=?', [$batchId,$invoiceId,$itemId,$userId]); }
            elseif ($itemType === 'listing_upgrade') { q('UPDATE mj_listing_upgrade_requests SET payment_status="payment_uploaded", status=IF(status="pending_payment","payment_uploaded",status), batch_id=?, invoice_id=? WHERE id=? AND user_id=?', [$batchId,$invoiceId,$itemId,$userId]); }
            elseif ($itemType === 'limit_upgrade') { q('UPDATE mj_user_limit_upgrade_requests SET payment_status="payment_uploaded", status="payment_uploaded", batch_id=?, invoice_id=? WHERE id=? AND user_id=?', [$batchId,$invoiceId,$itemId,$userId]); }
            elseif ($itemType === 'payment_request') { q('UPDATE mj_payment_requests SET status="payment_uploaded", batch_id=?, invoice_id=?, updated_at=NOW() WHERE id=? AND user_id=?', [$batchId,$invoiceId,$itemId,$userId]); }
        } catch (Throwable $e) {}
    }

    try {
        mj_create_admin_alert([
            'alert_key'=>'payment_batch_'.$batchId.'_'.date('YmdHis'), 'alert_type'=>'payment_batch', 'module_name'=>'payments', 'priority'=>'high',
            'title'=>'Bulk payment proof uploaded', 'message'=>'User uploaded one proof for invoice '.$invoiceNo.' / '.$batchNo.' total '.$currency.' '.number_format($total,2),
            'action_url'=>'payment-center.php?tab=review', 'action_label'=>'Review Payment', 'related_type'=>'payment_batch', 'related_id'=>$batchId
        ]);
    } catch (Throwable $e) {}
    if (function_exists('mj263_notify')) { mj263_notify($userId, null, 'payment_batch_submitted', 'Payment proof submitted', 'Your payment proof for '.$invoiceNo.' was submitted. Admin will review before activation.', 'billing-payments.php?tab=proofs', 'normal', true); }
    return $batchId;
}
}



if (!function_exists('mj292_mark_source_invoice_status')) {
function mj292_mark_source_invoice_status(array $item, int $batchId, int $invoiceId, int $userId, string $status): void {
    $type = (string)($item['item_type'] ?? '');
    $id = (int)($item['item_id'] ?? 0);
    try {
        if ($type === 'renewal' && table_exists('mj_listing_renewal_requests')) {
            q('UPDATE mj_listing_renewal_requests SET payment_status=?, status=IF(status IN ("pending_payment","payment_required","payment_uploaded","invoice_created"),?,status), batch_id=?, invoice_id=? WHERE id=? AND user_id=?', [$status,$status,$batchId,$invoiceId,$id,$userId]);
        } elseif ($type === 'listing_upgrade' && table_exists('mj_listing_upgrade_requests')) {
            q('UPDATE mj_listing_upgrade_requests SET payment_status=?, status=IF(status IN ("pending_payment","payment_required","payment_uploaded","invoice_created"),?,status), batch_id=?, invoice_id=? WHERE id=? AND user_id=?', [$status,$status,$batchId,$invoiceId,$id,$userId]);
        } elseif ($type === 'limit_upgrade' && table_exists('mj_user_limit_upgrade_requests')) {
            q('UPDATE mj_user_limit_upgrade_requests SET payment_status=?, status=?, batch_id=?, invoice_id=? WHERE id=? AND user_id=?', [$status,$status,$batchId,$invoiceId,$id,$userId]);
        } elseif ($type === 'payment_request' && table_exists('mj_payment_requests')) {
            $reqStatus = ($status === 'payment_uploaded') ? 'payment_uploaded' : 'invoice_created';
            q('UPDATE mj_payment_requests SET status=?, batch_id=?, invoice_id=?, updated_at=NOW() WHERE id=? AND user_id=?', [$reqStatus,$batchId,$invoiceId,$id,$userId]);
        }
    } catch (Throwable $e) {}
}
}

if (!function_exists('mj292_create_invoice_draft')) {
function mj292_create_invoice_draft(int $userId, array $post): int {
    mj291_payment_ensure_schema();
    $selected = isset($post['pay_items']) && is_array($post['pay_items']) ? $post['pay_items'] : [];
    $items = mj291_collect_checkout_items($userId, $selected, $post);
    $currency = (string)$items[0]['currency_code'];
    $subtotal = 0.0;
    foreach ($items as $it) { $subtotal += (float)$it['total_amount']; }
    $taxEnabled = setting('payment_cart_tax_enabled', setting('billing_tax_enabled','0')) === '1';
    $taxRate = $taxEnabled ? max(0, (float)setting('payment_cart_default_tax_rate', setting('billing_default_tax_rate','0'))) : 0;
    $tax = round($subtotal * $taxRate / 100, 2);
    $total = $subtotal + $tax;
    $note = trim((string)($post['payment_note'] ?? ''));
    $countryId = 0;
    try { $u = one('SELECT country_id FROM mj_users WHERE id=? LIMIT 1', [$userId]); $countryId = (int)($u['country_id'] ?? 0); } catch (Throwable $e) {}

    q('INSERT INTO mj_invoices(user_id,company_id,invoice_type,title,amount,tax_amount,total_amount,currency_code,status,due_date,created_at) VALUES(?,NULL,"proforma","Marketplace payment checkout",?,?,?,? ,"invoice_created",DATE_ADD(CURDATE(), INTERVAL 7 DAY),NOW())', [
        $userId, $subtotal, $tax, $total, $currency
    ]);
    $invoiceId = (int)db()->lastInsertId();
    $invoiceNo = mj291_next_doc_no('INV', $invoiceId, $currency);
    q('UPDATE mj_invoices SET invoice_no=? WHERE id=?', [$invoiceNo, $invoiceId]);

    q('INSERT INTO mj_payment_batches(batch_no,invoice_id,user_id,country_id,currency_code,subtotal_amount,tax_amount,total_amount,note,status,created_at) VALUES(NULL,?,?,?,?,?,?,?,?,"invoice_created",NOW())', [
        $invoiceId, $userId, $countryId ?: null, $currency, $subtotal, $tax, $total, $note
    ]);
    $batchId = (int)db()->lastInsertId();
    $batchNo = mj291_next_doc_no('PAY', $batchId, $currency);
    q('UPDATE mj_payment_batches SET batch_no=? WHERE id=?', [$batchNo, $batchId]);
    try { q('UPDATE mj_invoices SET batch_id=? WHERE id=?', [$batchId, $invoiceId]); } catch (Throwable $e) {}

    foreach ($items as $it) {
        $itemType = (string)$it['item_type'];
        $itemId = (int)$it['item_id'];
        if ($itemType === 'extra_listing_slot_new') {
            $itemId = mj291_create_limit_request_for_batch($userId, 'listing', (int)$it['quantity'], (float)$it['unit_amount'], $currency, $batchId, $invoiceId, $note, 'invoice_created');
            $itemType = 'limit_upgrade';
            $it['item_description'] = 'Extra listing slots x '.(int)$it['quantity'];
        } elseif ($itemType === 'extra_company_slot_new') {
            $itemId = mj291_create_limit_request_for_batch($userId, 'company', (int)$it['quantity'], (float)$it['unit_amount'], $currency, $batchId, $invoiceId, $note, 'invoice_created');
            $itemType = 'limit_upgrade';
            $it['item_description'] = 'Extra company slots x '.(int)$it['quantity'];
        }
        $rowItem = $it; $rowItem['item_type'] = $itemType; $rowItem['item_id'] = $itemId;
        q('INSERT INTO mj_payment_batch_items(batch_id,user_id,item_type,item_id,item_label,item_description,listing_id,company_id,quantity,unit_amount,total_amount,currency_code,status,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,? ,"invoice_created",NOW())', [
            $batchId, $userId, $itemType, $itemId, $it['item_label'], $it['item_description'], (int)$it['listing_id'] ?: null, (int)$it['company_id'] ?: null, (int)$it['quantity'], (float)$it['unit_amount'], (float)$it['total_amount'], $currency
        ]);
        q('INSERT INTO mj_invoice_items(invoice_id,user_id,item_type,item_id,description,quantity,unit_amount,total_amount,currency_code,created_at) VALUES(?,?,?,?,?,?,?,?,?,NOW())', [
            $invoiceId, $userId, $itemType, $itemId, trim((string)$it['item_label'].' - '.(string)$it['item_description']), (int)$it['quantity'], (float)$it['unit_amount'], (float)$it['total_amount'], $currency
        ]);
        mj292_mark_source_invoice_status($rowItem, $batchId, $invoiceId, $userId, 'invoice_created');
    }

    if (function_exists('mj263_notify')) { mj263_notify($userId, null, 'invoice_created', 'Invoice generated', 'Your invoice '.$invoiceNo.' is ready. Please review it and upload payment proof when paid.', 'billing-payments.php?tab=invoices', 'normal', true); }
    return $batchId;
}
}

if (!function_exists('mj291_create_wallet_topup_invoice')) {
function mj291_create_wallet_topup_invoice(int $userId, float $amount, string $currency='', string $note=''): int {
    mj291_payment_ensure_schema();
    if (function_exists('mj295_wallet_ensure_schema')) { mj295_wallet_ensure_schema(); }
    if (setting('wallet_topup_enabled','1') !== '1') { throw new Exception('Wallet top-up is disabled by admin.'); }
    if (!mj_user_feature_enabled('wallet', $userId)) { throw new Exception('Wallet is not enabled for your account.'); }
    $currency = strtoupper(trim($currency ?: (setting('wallet_default_currency', setting('listing_upgrade_currency','SGD') ?: 'SGD') ?: 'SGD')));
    if ($currency === '') { $currency = 'SGD'; }
    $min = max(0.01, (float)setting('wallet_topup_min_amount','1.00'));
    $max = max($min, (float)setting('wallet_topup_max_amount','9999.00'));
    $amount = round($amount, 2);
    if ($amount < $min) { throw new Exception('Minimum wallet top-up is '.money($min, $currency).'.'); }
    if ($amount > $max) { throw new Exception('Maximum wallet top-up is '.money($max, $currency).'.'); }
    $note = trim($note);
    $countryId = 0;
    try { $u = one('SELECT country_id FROM mj_users WHERE id=? LIMIT 1', [$userId]); $countryId = (int)($u['country_id'] ?? 0); } catch (Throwable $e) {}
    $taxEnabled = setting('payment_cart_tax_enabled', setting('billing_tax_enabled','0')) === '1';
    $taxRate = $taxEnabled ? max(0, (float)setting('payment_cart_default_tax_rate', setting('billing_default_tax_rate','0'))) : 0;
    $tax = round($amount * $taxRate / 100, 2);
    $total = $amount + $tax;
    q('INSERT INTO mj_invoices(user_id,company_id,invoice_type,title,amount,tax_amount,total_amount,currency_code,status,due_date,created_at) VALUES(?,NULL,"proforma","Wallet top-up credit",?,?,?,? ,"invoice_created",DATE_ADD(CURDATE(), INTERVAL 7 DAY),NOW())', [
        $userId, $amount, $tax, $total, $currency
    ]);
    $invoiceId = (int)db()->lastInsertId();
    $invoiceNo = mj291_next_doc_no('INV', $invoiceId, $currency);
    q('UPDATE mj_invoices SET invoice_no=? WHERE id=?', [$invoiceNo, $invoiceId]);
    q('INSERT INTO mj_payment_batches(batch_no,invoice_id,user_id,country_id,currency_code,subtotal_amount,tax_amount,total_amount,note,status,created_at) VALUES(NULL,?,?,?,?,?,?,?,? ,"invoice_created",NOW())', [
        $invoiceId, $userId, $countryId ?: null, $currency, $amount, $tax, $total, $note
    ]);
    $batchId = (int)db()->lastInsertId();
    $batchNo = mj291_next_doc_no('PAY', $batchId, $currency);
    q('UPDATE mj_payment_batches SET batch_no=? WHERE id=?', [$batchNo, $batchId]);
    try { q('UPDATE mj_invoices SET batch_id=? WHERE id=?', [$batchId, $invoiceId]); } catch (Throwable $e) {}
    q('INSERT INTO mj_payment_batch_items(batch_id,user_id,item_type,item_id,item_label,item_description,listing_id,company_id,quantity,unit_amount,total_amount,currency_code,status,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,? ,"invoice_created",NOW())', [
        $batchId, $userId, 'wallet_topup', 0, 'Wallet Top-up Credit', 'Add wallet credit after admin approves payment proof', null, null, 1, $amount, $amount, $currency
    ]);
    q('INSERT INTO mj_invoice_items(invoice_id,user_id,item_type,item_id,description,quantity,unit_amount,total_amount,currency_code,created_at) VALUES(?,?,?,?,?,?,?,?,?,NOW())', [
        $invoiceId, $userId, 'wallet_topup', 0, 'Wallet Top-up Credit', 1, $amount, $amount, $currency
    ]);
    if (function_exists('mj263_notify')) { mj263_notify($userId, null, 'wallet_topup_invoice_created', 'Wallet top-up invoice generated', 'Please pay and upload proof for invoice '.$invoiceNo.'. Admin will approve before credit is added.', 'billing-payments.php?tab=invoices', 'normal', true); }
    return $batchId;
}
}

if (!function_exists('mj292_submit_invoice_payment')) {
function mj292_submit_invoice_payment(int $batchId, int $userId, array $post): int {
    mj291_payment_ensure_schema();
    if (setting('upgrade_payment_proof_upload_enabled','1') !== '1') { throw new Exception('Payment proof upload is disabled by admin.'); }
    if (!mj_user_feature_enabled('payment_proof_upload', $userId)) { throw new Exception('Payment proof upload is not enabled for your account. Please contact admin.'); }
    $batch = one('SELECT * FROM mj_payment_batches WHERE id=? AND user_id=? LIMIT 1', [$batchId,$userId]);
    if (!$batch) { throw new Exception('Invoice not found.'); }
    $status = strtolower((string)($batch['status'] ?? ''));
    if (!in_array($status, ['invoice_created','unpaid','draft','need_more_info','rejected'], true)) { throw new Exception('This invoice cannot accept payment proof now.'); }
    $methods = mj291_payment_methods();
    if (!$methods) { throw new Exception('No active payment method. Please contact admin.'); }
    $method = trim((string)($post['payment_method'] ?? ''));
    if ($method === '' || !isset($methods[$method])) { throw new Exception('Please select an active payment method.'); }
    $ref = trim((string)($post['payment_reference'] ?? ''));
    if ($ref === '') { throw new Exception('Please enter payment/reference number.'); }
    $proof = mj291_upload_payment_proof($userId, 'payment_proof');
    $note = trim((string)($post['payment_note'] ?? ''));
    q('UPDATE mj_payment_batches SET status="pending_review", payment_method=?, reference_no=?, proof_path=?, original_name=?, mime_type=?, file_size=?, note=?, admin_note=NULL, reviewed_by=NULL, reviewed_at=NULL, updated_at=NOW() WHERE id=? AND user_id=?', [$method,$ref,$proof['path'],$proof['original_name'],$proof['mime_type'],$proof['file_size'],$note,$batchId,$userId]);
    q('UPDATE mj_payment_batch_items SET status="pending_review", activation_status="not_activated", admin_note=NULL, updated_at=NOW() WHERE batch_id=? AND user_id=?', [$batchId,$userId]);
    if (!empty($batch['invoice_id'])) { q('UPDATE mj_invoices SET status="pending_review", payment_method=?, reference_no=?, proof_path=?, admin_note=NULL, updated_at=NOW() WHERE id=? AND user_id=?', [$method,$ref,$proof['path'],(int)$batch['invoice_id'],$userId]); }
    foreach (mj291_batch_items($batchId) as $it) { mj292_mark_source_invoice_status($it, $batchId, (int)($batch['invoice_id'] ?? 0), $userId, 'payment_uploaded'); }
    try {
        $invoiceNo = '';
        if (!empty($batch['invoice_id'])) { $inv = one('SELECT invoice_no FROM mj_invoices WHERE id=? LIMIT 1', [(int)$batch['invoice_id']]); $invoiceNo = (string)($inv['invoice_no'] ?? ''); }
        mj_create_admin_alert([
            'alert_key'=>'payment_invoice_proof_'.$batchId.'_'.date('YmdHis'), 'alert_type'=>'payment_batch', 'module_name'=>'payments', 'priority'=>'high',
            'title'=>'Payment proof uploaded', 'message'=>'User uploaded proof for '.($invoiceNo ?: 'invoice #'.(int)($batch['invoice_id'] ?? 0)).' total '.(string)$batch['currency_code'].' '.number_format((float)$batch['total_amount'],2),
            'action_url'=>'payment-center.php?tab=review', 'action_label'=>'Review Payment', 'related_type'=>'payment_batch', 'related_id'=>$batchId
        ]);
    } catch (Throwable $e) {}
    if (function_exists('mj263_notify')) { mj263_notify($userId, null, 'payment_proof_submitted', 'Payment proof submitted', 'Your payment proof was submitted. Admin will review before activation.', 'billing-payments.php?tab=proofs', 'normal', true); }
    return $batchId;
}
}

if (!function_exists('mj292_cancel_invoice_for_edit')) {
function mj292_cancel_invoice_for_edit(int $batchId, int $userId): void {
    mj291_payment_ensure_schema();
    $batch = one('SELECT * FROM mj_payment_batches WHERE id=? AND user_id=? LIMIT 1', [$batchId,$userId]);
    if (!$batch) { throw new Exception('Invoice not found.'); }
    $status = strtolower((string)($batch['status'] ?? ''));
    if (!in_array($status, ['invoice_created','unpaid','draft'], true)) { throw new Exception('You can edit items only before payment proof is submitted.'); }
    if (trim((string)($batch['proof_path'] ?? '')) !== '') { throw new Exception('Proof already uploaded. Ask admin to reject or request more info if you need to change it.'); }
    $invoiceId = (int)($batch['invoice_id'] ?? 0);
    foreach (mj291_batch_items($batchId) as $it) {
        $type = (string)($it['item_type'] ?? '');
        $id = (int)($it['item_id'] ?? 0);
        try {
            if ($type === 'renewal' && table_exists('mj_listing_renewal_requests')) {
                q('UPDATE mj_listing_renewal_requests SET payment_status="payment_required", status="pending_payment", batch_id=NULL, invoice_id=NULL WHERE id=? AND user_id=?', [$id,$userId]);
            } elseif ($type === 'listing_upgrade' && table_exists('mj_listing_upgrade_requests')) {
                q('UPDATE mj_listing_upgrade_requests SET payment_status="payment_required", status="pending_payment", batch_id=NULL, invoice_id=NULL WHERE id=? AND user_id=?', [$id,$userId]);
            } elseif ($type === 'limit_upgrade' && table_exists('mj_user_limit_upgrade_requests')) {
                $label = strtolower((string)($it['item_label'] ?? ''));
                if (str_contains($label, 'extra')) { q('DELETE FROM mj_user_limit_upgrade_requests WHERE id=? AND user_id=? AND batch_id=?', [$id,$userId,$batchId]); }
                else { q('UPDATE mj_user_limit_upgrade_requests SET payment_status="payment_required", status="pending_payment", batch_id=NULL, invoice_id=NULL WHERE id=? AND user_id=?', [$id,$userId]); }
            } elseif ($type === 'payment_request' && table_exists('mj_payment_requests')) {
                q('UPDATE mj_payment_requests SET status="pending", batch_id=NULL, invoice_id=NULL, updated_at=NOW() WHERE id=? AND user_id=?', [$id,$userId]);
            }
        } catch (Throwable $e) {}
    }
    if ($invoiceId > 0) {
        try { q('DELETE FROM mj_invoice_items WHERE invoice_id=?', [$invoiceId]); } catch (Throwable $e) {}
        try { q('DELETE FROM mj_invoices WHERE id=? AND user_id=?', [$invoiceId,$userId]); } catch (Throwable $e) {}
    }
    try { q('DELETE FROM mj_payment_batch_items WHERE batch_id=? AND user_id=?', [$batchId,$userId]); } catch (Throwable $e) {}
    try { q('DELETE FROM mj_payment_batches WHERE id=? AND user_id=?', [$batchId,$userId]); } catch (Throwable $e) {}
}
}

if (!function_exists('mj291_batch_items')) {
function mj291_batch_items(int $batchId): array {
    try { return table_exists('mj_payment_batch_items') ? all_rows('SELECT * FROM mj_payment_batch_items WHERE batch_id=? ORDER BY id ASC', [$batchId]) : []; } catch (Throwable $e) { return []; }
}
}

if (!function_exists('mj291_batch_receipt_exists')) {
function mj291_batch_receipt_exists(int $batchId): bool {
    try { return table_exists('mj_receipts') && (bool)one('SELECT id FROM mj_receipts WHERE batch_id=? AND status IN ("paid","approved","completed") LIMIT 1', [$batchId]); } catch (Throwable $e) { return false; }
}
}

if (!function_exists('mj291_is_final_paid_status')) {
function mj291_is_final_paid_status(string $status): bool {
    return in_array(strtolower(trim($status)), ['approved','paid','completed','payment_verified'], true);
}
}

if (!function_exists('mj295_wallet_credit_from_payment')) {
function mj295_wallet_credit_from_payment(int $userId, float $amount, string $currency, int $adminId=0, string $note='', int $batchId=0, int $invoiceId=0): void {
    if ($amount <= 0) { throw new Exception('Wallet amount must be greater than 0.'); }
    if (!function_exists('mj295_wallet_get') || !function_exists('mj295_wallet_tx')) { throw new Exception('Wallet helper is not loaded.'); }
    $currency = strtoupper(trim($currency ?: (setting('wallet_default_currency','SGD') ?: 'SGD')));
    if ($currency === '') { $currency = 'SGD'; }
    $w = mj295_wallet_get($userId, $currency);
    if ((string)($w['status'] ?? '') === 'locked') { throw new Exception('This user wallet is locked.'); }
    $before = (float)($w['balance'] ?? 0);
    $heldBefore = (float)($w['held_amount'] ?? 0);
    $after = $before + $amount;
    q('UPDATE mj_wallets SET balance=?, updated_at=NOW() WHERE id=?', [$after, (int)$w['id']]);
    mj295_wallet_tx((int)$w['id'], $userId, $currency, 'wallet_topup', 'credit', $amount, $before, $after, $heldBefore, $heldBefore, $note ?: 'Wallet top-up approved from invoice', 'payment_batch', $batchId, $invoiceId, $batchId, $adminId, $note);
}
}

if (!function_exists('mj291_activate_batch_item')) {
function mj291_activate_batch_item(array $item, int $adminId, string $note=''): string {
    mj291_payment_ensure_schema();
    $type = (string)($item['item_type'] ?? '');
    $id = (int)($item['item_id'] ?? 0);
    $userId = (int)($item['user_id'] ?? 0);
    if ($type === 'wallet_topup') {
        if (!function_exists('mj295_wallet_credit_from_payment')) { return 'Wallet helper not loaded.'; }
        $amount = max(0, (float)($item['total_amount'] ?? 0));
        $currency = (string)($item['currency_code'] ?? setting('wallet_default_currency','SGD') ?: 'SGD');
        if ($amount <= 0) { return 'Wallet top-up amount invalid.'; }
        mj295_wallet_credit_from_payment($userId, $amount, $currency, $adminId, $note ?: 'Wallet top-up approved from invoice', (int)($item['batch_id'] ?? 0), (int)($item['invoice_id'] ?? 0));
        if (function_exists('mj263_notify')) { mj263_notify($userId, null, 'wallet_topup_approved', 'Wallet top-up approved', 'Your wallet has been credited with '.money($amount, $currency).'.', 'billing-payments.php?tab=wallet', 'normal', true); }
        return 'Wallet credited '.money($amount, $currency).'.';
    }
    if (function_exists('mj_v300_activate_deal_item') && in_array($type, ['extra_deal_slot','deal_renewal','deal_homepage','deal_bump','deal_urgent'], true)) {
        return mj_v300_activate_deal_item($type, $userId, $id, (int)($item['quantity'] ?? 1), $adminId, $note);
    }
    if ($id <= 0) { return 'Skipped item without target ID.'; }
    if ($type === 'renewal' && table_exists('mj_listing_renewal_requests')) {
        $req = one('SELECT * FROM mj_listing_renewal_requests WHERE id=? LIMIT 1', [$id]);
        if (!$req) { return 'Renewal request not found.'; }
        $listing = table_exists('mj_listings') ? one('SELECT * FROM mj_listings WHERE id=? LIMIT 1', [(int)$req['listing_id']]) : null;
        $days = max(1, (int)($req['requested_days'] ?? $item['quantity'] ?? setting('listing_upgrade_days','30')));
        $base = time();
        if (!empty($listing['expires_at']) && strtotime((string)$listing['expires_at']) > time()) { $base = strtotime((string)$listing['expires_at']); }
        $newExp = date('Y-m-d H:i:s', strtotime('+'.$days.' days', $base));
        q('UPDATE mj_listings SET expires_at=?, expired_at=NULL, status="active", updated_at=NOW() WHERE id=?', [$newExp, (int)$req['listing_id']]);
        q('UPDATE mj_listing_renewal_requests SET payment_status="payment_verified", status="approved", admin_id=?, admin_note=?, reviewed_at=NOW(), requested_expires_at=? WHERE id=?', [$adminId,$note,$newExp,$id]);
        if (function_exists('mj_listing_log')) { mj_listing_log((int)$req['listing_id'], 'payment_batch_renewal_approved', $listing ?: [], ['expires_at'=>$newExp]); }
        if (function_exists('mj263_notify')) { mj263_notify($userId, null, 'listing_renewal_approved', 'Listing renewed', 'Your listing was renewed until '.$newExp, 'my-listings.php', 'normal', true); }
        return 'Renewed listing #'.(int)$req['listing_id'].' until '.$newExp;
    }
    if ($type === 'listing_upgrade' && table_exists('mj_listing_upgrade_requests')) {
        $req = one('SELECT * FROM mj_listing_upgrade_requests WHERE id=? LIMIT 1', [$id]);
        if (!$req) { return 'Listing upgrade request not found.'; }
        $listing = table_exists('mj_listings') ? one('SELECT * FROM mj_listings WHERE id=? LIMIT 1', [(int)$req['listing_id']]) : null;
        $days = max(1, (int)($req['requested_days'] ?? setting('listing_upgrade_days','30')));
        $until = date('Y-m-d H:i:s', strtotime('+'.$days.' days'));
        $upgradeType = (string)($req['upgrade_type'] ?? '');
        if ($upgradeType === 'feature') { q('UPDATE mj_listings SET is_featured=1, featured_until=?, featured_priority=50, updated_at=NOW() WHERE id=?', [$until,(int)$req['listing_id']]); }
        if ($upgradeType === 'sponsor') { q('UPDATE mj_listings SET is_sponsored=1, sponsored_until=?, sponsored_priority=100, updated_at=NOW() WHERE id=?', [$until,(int)$req['listing_id']]); }
        q('UPDATE mj_listing_upgrade_requests SET payment_status="payment_verified", status="approved", reviewed_by=?, admin_note=?, reviewed_at=NOW(), approved_until=? WHERE id=?', [$adminId,$note,$until,$id]);
        if (function_exists('mj_listing_log')) { mj_listing_log((int)$req['listing_id'], 'payment_batch_'.$upgradeType.'_approved', $listing ?: [], ['approved_until'=>$until]); }
        if (function_exists('mj263_notify')) { mj263_notify($userId, null, 'listing_upgrade_approved', (function_exists('mj_listing_upgrade_type_label')?mj_listing_upgrade_type_label($upgradeType):'Listing upgrade').' approved', 'Your listing upgrade is active until '.$until, 'my-listings.php', 'normal', true); }
        return 'Activated '.($upgradeType ?: 'listing upgrade').' until '.$until;
    }
    if ($type === 'limit_upgrade' && table_exists('mj_user_limit_upgrade_requests')) {
        $req = one('SELECT * FROM mj_user_limit_upgrade_requests WHERE id=? LIMIT 1', [$id]);
        if (!$req) { return 'Extra slot request not found.'; }
        $qty = max(1, (int)($req['requested_slots'] ?? $item['quantity'] ?? 1));
        $slotType = (string)($req['request_type'] ?? 'listing') === 'company' ? 'company' : 'listing';
        $limits = function_exists('mj_user_limits_get') ? mj_user_limits_get($userId) : ['max_listings'=>0,'max_companies'=>0];
        $maxListings = (int)($limits['max_listings'] ?? 0);
        $maxCompanies = (int)($limits['max_companies'] ?? 0);
        if ($slotType === 'company') { $maxCompanies = max(0, $maxCompanies) + $qty; } else { $maxListings = max(0, $maxListings) + $qty; }
        q('INSERT INTO mj_user_limits(user_id,max_listings,max_companies,note,updated_by,updated_at) VALUES(?,?,?,?,?,NOW()) ON DUPLICATE KEY UPDATE max_listings=VALUES(max_listings), max_companies=VALUES(max_companies), note=VALUES(note), updated_by=VALUES(updated_by), updated_at=NOW()', [$userId,$maxListings,$maxCompanies,'Extra '.$slotType.' slots approved from payment batch.',$adminId]);
        q('UPDATE mj_user_limit_upgrade_requests SET payment_status="payment_verified", status="approved", approved_slots=?, reviewed_by=?, admin_note=?, reviewed_at=NOW() WHERE id=?', [$qty,$adminId,$note,$id]);
        if (function_exists('mj263_notify')) { mj263_notify($userId, null, 'extra_slots_approved', 'Extra slots approved', 'Admin approved '.$qty.' extra '.$slotType.' slot(s).', 'billing-payments.php?tab=receipts', 'normal', true); }
        return 'Added '.$qty.' extra '.$slotType.' slot(s).';
    }
    if (($type === 'deal_featured' || $type === 'deal_sponsored') && table_exists('mj_deals')) {
        $deal = one('SELECT * FROM mj_deals WHERE id=? AND user_id=? LIMIT 1', [$id,$userId]);
        if (!$deal) { return 'Deal promotion target not found.'; }
        $days = max(1, (int)($item['quantity'] ?? setting('deal_promotion_default_days','7')));
        $until = date('Y-m-d H:i:s', strtotime('+'.$days.' days'));
        if ($type === 'deal_featured') {
            q('UPDATE mj_deals SET is_featured=1, featured_until=?, status=IF(status IN ("draft","pending","rejected"),"active",status), updated_at=NOW() WHERE id=? AND user_id=?', [$until,$id,$userId]);
            if (function_exists('mj263_notify')) { mj263_notify($userId, null, 'deal_featured_approved', 'Featured deal activated', 'Your deal is featured until '.$until.'.', 'my-deals.php', 'normal', true); }
            return 'Featured deal #'.$id.' until '.$until;
        }
        q('UPDATE mj_deals SET is_sponsored=1, sponsored_until=?, status=IF(status IN ("draft","pending","rejected"),"active",status), updated_at=NOW() WHERE id=? AND user_id=?', [$until,$id,$userId]);
        if (function_exists('mj263_notify')) { mj263_notify($userId, null, 'deal_sponsored_approved', 'Sponsored deal activated', 'Your deal is sponsored until '.$until.'.', 'my-deals.php', 'normal', true); }
        return 'Sponsored deal #'.$id.' until '.$until;
    }
    if ($type === 'payment_request' && table_exists('mj_payment_requests')) {
        q('UPDATE mj_payment_requests SET status="paid", updated_at=NOW() WHERE id=? AND user_id=?', [$id,$userId]);
        if (function_exists('mj263_notify')) { mj263_notify($userId, null, 'payment_request_paid', 'Payment marked paid', 'Your payment request was marked paid.', 'billing-payments.php?tab=receipts', 'normal', true); }
        return 'Marked payment request #'.$id.' paid.';
    }
    return 'No activation rule for '.$type.'.';
}
}

if (!function_exists('mj291_review_batch')) {
function mj291_review_batch(int $batchId, string $action, int $adminId, string $note='', array $selectedItemIds=[]): array {
    mj291_payment_ensure_schema();
    $batch = one('SELECT * FROM mj_payment_batches WHERE id=? LIMIT 1', [$batchId]);
    if (!$batch) { throw new Exception('Payment batch not found.'); }
    $items = mj291_batch_items($batchId);
    $logs = [];
    $currentStatus = strtolower((string)($batch['status'] ?? ''));
    $isApproveAction = in_array($action, ['approve_all','approve_selected','verify','mark_paid'], true);
    if (mj291_is_final_paid_status($currentStatus)) {
        if ($isApproveAction) { return ['Payment batch already approved. No duplicate receipt, wallet debit or service activation was created.']; }
        throw new Exception('This payment is already approved/paid and cannot be changed here.');
    }
    if (strtolower((string)($batch['payment_method'] ?? '')) === 'wallet') {
        foreach ($items as $__walletCheckItem) {
            if ((string)($__walletCheckItem['item_type'] ?? '') === 'wallet_topup') {
                throw new Exception('Wallet top-up invoices cannot be approved as wallet payments. Please use a manual payment method proof.');
            }
        }
    }
    if ($action === 'reject') {
        if (function_exists('mj295_release_wallet_hold')) { mj295_release_wallet_hold($batchId, 'Payment rejected by admin'); }
        q('UPDATE mj_payment_batches SET status="rejected", admin_note=?, reviewed_by=?, reviewed_at=NOW() WHERE id=?', [$note,$adminId,$batchId]);
        q('UPDATE mj_payment_batch_items SET status="rejected", activation_status="not_activated", admin_note=?, updated_at=NOW() WHERE batch_id=?', [$note,$batchId]);
        if (!empty($batch['invoice_id'])) { q('UPDATE mj_invoices SET status="rejected", admin_note=?, updated_at=NOW() WHERE id=?', [$note,(int)$batch['invoice_id']]); }
        if (function_exists('mj263_notify')) { mj263_notify((int)$batch['user_id'], null, 'payment_rejected', 'Payment proof rejected', $note !== '' ? 'Reason: '.$note : 'Your payment proof was rejected.', 'billing-payments.php?tab=proofs', 'high', true); }
        return ['Payment batch rejected.'];
    }
    if ($action === 'need_more_info') {
        if (function_exists('mj295_release_wallet_hold')) { mj295_release_wallet_hold($batchId, 'Need more info requested by admin'); }
        q('UPDATE mj_payment_batches SET status="need_more_info", admin_note=?, reviewed_by=?, reviewed_at=NOW() WHERE id=?', [$note,$adminId,$batchId]);
        q('UPDATE mj_payment_batch_items SET status="need_more_info", admin_note=?, updated_at=NOW() WHERE batch_id=?', [$note,$batchId]);
        if (!empty($batch['invoice_id'])) { q('UPDATE mj_invoices SET status="need_more_info", admin_note=?, updated_at=NOW() WHERE id=?', [$note,(int)$batch['invoice_id']]); }
        if (function_exists('mj263_notify')) { mj263_notify((int)$batch['user_id'], null, 'payment_need_more_info', 'Payment needs more information', $note !== '' ? $note : 'Please check your payment proof and upload again.', 'billing-payments.php?tab=proofs', 'high', true); }
        return ['Marked as need more info.'];
    }
    $approveAll = $action === 'approve_all' || !$selectedItemIds;
    $selected = array_flip(array_map('intval', $selectedItemIds));
    $approvedCount = 0;
    foreach ($items as $it) {
        $itemRowId = (int)$it['id'];
        if (!$approveAll && !isset($selected[$itemRowId])) { continue; }
        try {
            if (strtolower((string)($it['status'] ?? '')) === 'approved' && strtolower((string)($it['activation_status'] ?? '')) === 'activated') {
                $logs[] = 'Skipped already activated item #'.$itemRowId;
                continue;
            }
            $msg = mj291_activate_batch_item($it, $adminId, $note);
            q('UPDATE mj_payment_batch_items SET status="approved", activation_status="activated", admin_note=?, updated_at=NOW() WHERE id=?', [$msg, $itemRowId]);
            $logs[] = $msg; $approvedCount++;
        } catch (Throwable $e) {
            q('UPDATE mj_payment_batch_items SET status="approved", activation_status="activation_failed", admin_note=?, updated_at=NOW() WHERE id=?', [$e->getMessage(), $itemRowId]);
            $logs[] = 'Failed item #'.$itemRowId.': '.$e->getMessage();
        }
    }
    if ($approvedCount <= 0) {
        return $logs ?: ['No new item was approved. Already activated items were skipped.'];
    }
    $final = ($approvedCount > 0 && $approvedCount < count($items)) ? 'partial_approved' : 'approved';
    $approvedAmount = 0.0;
    foreach ($items as $it) {
        if ($approveAll || isset($selected[(int)$it['id']])) { $approvedAmount += (float)($it['total_amount'] ?? 0); }
    }
    $receiptAmount = (float)($batch['total_amount'] ?? 0);
    if (strtolower((string)($batch['payment_method'] ?? '')) === 'wallet' && function_exists('mj295_capture_wallet_hold_for_batch')) {
        // For full wallet payments, capture the exact invoice total including tax so wallet, invoice and receipt tally.
        // For partial approval, capture only the approved item subtotal and release the rest.
        $walletCaptureAmount = $approveAll ? $receiptAmount : ($approvedAmount > 0 ? $approvedAmount : $receiptAmount);
        $captured = mj295_capture_wallet_hold_for_batch($batchId, $walletCaptureAmount, $adminId, $note);
        if ($captured > 0) { $receiptAmount = $captured; }
    }
    q('UPDATE mj_payment_batches SET status=?, admin_note=?, reviewed_by=?, reviewed_at=NOW() WHERE id=?', [$final,$note,$adminId,$batchId]);
    if (!empty($batch['invoice_id'])) { q('UPDATE mj_invoices SET status="paid", paid_at=NOW(), admin_note=?, updated_at=NOW() WHERE id=?', [$note,(int)$batch['invoice_id']]); }
    if (!mj291_batch_receipt_exists($batchId)) {
        q('INSERT INTO mj_receipts(receipt_no,invoice_id,batch_id,user_id,amount,currency_code,payment_method,reference_no,status,admin_id,created_at) VALUES(NULL,?,?,?,?,?,?,?,"paid",?,NOW())', [(int)($batch['invoice_id'] ?? 0) ?: null, $batchId, (int)$batch['user_id'], $receiptAmount, (string)$batch['currency_code'], (string)$batch['payment_method'], (string)$batch['reference_no'], $adminId]);
        $receiptId = (int)db()->lastInsertId();
        q('UPDATE mj_receipts SET receipt_no=? WHERE id=?', [mj291_next_doc_no('RCP', $receiptId, (string)$batch['currency_code']), $receiptId]);
    } else {
        $logs[] = 'Receipt already exists for this batch; duplicate receipt skipped.';
    }
    if (function_exists('mj263_notify')) { mj263_notify((int)$batch['user_id'], null, 'payment_approved', 'Payment approved', 'Your payment was approved and selected services were activated.', 'billing-payments.php?tab=receipts', 'normal', true); }
    return $logs ?: ['Payment approved.'];
}
}

if (!function_exists('mj291_batch_timeline')) {
function mj291_batch_timeline(array $batch): array {
    $status = strtolower((string)($batch['status'] ?? 'pending_review'));
    $steps = [
        ['Invoice Created', true],
        ['Proof Uploaded', trim((string)($batch['proof_path'] ?? '')) !== ''],
        ['Waiting Admin Review', in_array($status, ['pending_review','submitted','uploaded','payment_uploaded'], true)],
        ['Need More Info', $status === 'need_more_info'],
        ['Approved / Paid', in_array($status, ['approved','partial_approved','paid'], true)],
        ['Rejected', $status === 'rejected'],
    ];
    return $steps;
}
}

if (!function_exists('mj291_reupload_batch_proof')) {
function mj291_reupload_batch_proof(int $batchId, int $userId, array $post): void {
    mj291_payment_ensure_schema();
    $batch = one('SELECT * FROM mj_payment_batches WHERE id=? AND user_id=? LIMIT 1', [$batchId,$userId]);
    if (!$batch) { throw new Exception('Payment invoice not found.'); }
    $status = strtolower((string)($batch['status'] ?? ''));
    if (!in_array($status, ['rejected','need_more_info'], true)) { throw new Exception('You can re-upload only when admin rejected or requested more info.'); }
    $methods = mj291_payment_methods();
    $method = trim((string)($post['payment_method'] ?? $batch['payment_method'] ?? ''));
    if ($method === '' || !isset($methods[$method])) { throw new Exception('Please select an active payment method.'); }
    $ref = trim((string)($post['payment_reference'] ?? $batch['reference_no'] ?? ''));
    if ($ref === '') { throw new Exception('Please enter payment/reference number.'); }
    $proof = mj291_upload_payment_proof($userId, 'payment_proof');
    $note = trim((string)($post['payment_note'] ?? ''));
    q('UPDATE mj_payment_batches SET status="pending_review", payment_method=?, reference_no=?, proof_path=?, original_name=?, mime_type=?, file_size=?, note=?, admin_note=NULL, reviewed_by=NULL, reviewed_at=NULL, updated_at=NOW() WHERE id=? AND user_id=?', [$method,$ref,$proof['path'],$proof['original_name'],$proof['mime_type'],$proof['file_size'],$note,$batchId,$userId]);
    q('UPDATE mj_payment_batch_items SET status="pending_review", activation_status="not_activated", admin_note=NULL, updated_at=NOW() WHERE batch_id=?', [$batchId]);
    if (!empty($batch['invoice_id'])) { q('UPDATE mj_invoices SET status="pending_review", payment_method=?, reference_no=?, proof_path=?, admin_note=NULL, updated_at=NOW() WHERE id=? AND user_id=?', [$method,$ref,$proof['path'],(int)$batch['invoice_id'],$userId]); }
    try { mj_create_admin_alert(['alert_key'=>'payment_batch_reupload_'.$batchId.'_'.date('YmdHis'),'alert_type'=>'payment_batch','module_name'=>'payments','priority'=>'high','title'=>'Payment proof re-uploaded','message'=>'User re-uploaded proof for payment batch #'.$batchId,'action_url'=>'payment-center.php?tab=review','action_label'=>'Review Payment','related_type'=>'payment_batch','related_id'=>$batchId]); } catch (Throwable $e) {}
}
}

/* ===== v2.9.3 Payment performance + finance tally helpers ===== */
if (!function_exists('mj293_amount_expr')) {
function mj293_amount_expr(string $table, string $alias=''): string {
    $p = $alias !== '' ? preg_replace('/[^a-zA-Z0-9_]/','',$alias).'.' : '';
    if (mj_column_exists($table, 'total_amount')) { return 'COALESCE('.$p.'total_amount,0)'; }
    if (mj_column_exists($table, 'amount')) { return 'COALESCE('.$p.'amount,0)'; }
    if (mj_column_exists($table, 'charge_amount')) { return 'COALESCE('.$p.'charge_amount,0)'; }
    return '0';
}
}

if (!function_exists('mj293_admin_href')) {
function mj293_admin_href(string $path): string {
    $path = trim($path);
    if ($path === '') { return ''; }
    if (preg_match('/^https?:\/\//i', $path)) { return $path; }
    return '../'.ltrim($path, '/');
}
}

if (!function_exists('mj293_is_legacy_unbatched_where')) {
function mj293_is_legacy_unbatched_where(string $table, string $alias=''): string {
    $p = $alias !== '' ? preg_replace('/[^a-zA-Z0-9_]/','',$alias).'.' : '';
    return mj_column_exists($table, 'batch_id') ? ' AND ('.$p.'batch_id IS NULL OR '.$p.'batch_id=0)' : '';
}
}

if (!function_exists('mj293_finance_metrics')) {
function mj293_finance_metrics(): array {
    $m = [
        'invoice_count'=>0,
        'batch_count'=>0,
        'proof_count'=>0,
        'awaiting_user_payment_count'=>0,
        'awaiting_user_payment_amount'=>0.0,
        'pending_review_count'=>0,
        'pending_review_amount'=>0.0,
        'need_more_info_count'=>0,
        'need_more_info_amount'=>0.0,
        'paid_count'=>0,
        'paid_amount'=>0.0,
        'rejected_count'=>0,
        'rejected_amount'=>0.0,
        'open_count'=>0,
        'open_amount'=>0.0,
        'receipt_count'=>0,
        'receipt_amount'=>0.0,
        'invoice_paid_count'=>0,
        'invoice_paid_amount'=>0.0,
        'wallet_payment_debit_count'=>0,
        'wallet_payment_debit_amount'=>0.0,
        'wallet_topup_credit_count'=>0,
        'wallet_topup_credit_amount'=>0.0,
        'wallet_admin_credit_amount'=>0.0,
        'wallet_admin_debit_amount'=>0.0,
        'paid_receipt_delta'=>0.0,
        'invoice_receipt_delta'=>0.0,
        'refunds_pending'=>0,
    ];
    try {
        if (table_exists('mj_payment_batches')) {
            $row = one("SELECT
                COUNT(*) batch_count,
                SUM(CASE WHEN status IN ('invoice_created','unpaid','draft') THEN 1 ELSE 0 END) awaiting_count,
                SUM(CASE WHEN status IN ('invoice_created','unpaid','draft') THEN total_amount ELSE 0 END) awaiting_amount,
                SUM(CASE WHEN status IN ('pending_review','submitted','uploaded','payment_uploaded','proof_uploaded') THEN 1 ELSE 0 END) pending_count,
                SUM(CASE WHEN status IN ('pending_review','submitted','uploaded','payment_uploaded','proof_uploaded') THEN total_amount ELSE 0 END) pending_amount,
                SUM(CASE WHEN status='need_more_info' THEN 1 ELSE 0 END) need_count,
                SUM(CASE WHEN status='need_more_info' THEN total_amount ELSE 0 END) need_amount,
                SUM(CASE WHEN status IN ('approved','partial_approved','paid','completed','payment_verified') THEN 1 ELSE 0 END) paid_count,
                SUM(CASE WHEN status IN ('approved','partial_approved','paid','completed','payment_verified') THEN total_amount ELSE 0 END) paid_amount,
                SUM(CASE WHEN status IN ('rejected','failed','cancelled','payment_rejected') THEN 1 ELSE 0 END) rejected_count,
                SUM(CASE WHEN status IN ('rejected','failed','cancelled','payment_rejected') THEN total_amount ELSE 0 END) rejected_amount,
                SUM(CASE WHEN proof_path IS NOT NULL AND proof_path<>'' THEN 1 ELSE 0 END) proof_count
                FROM mj_payment_batches");
            $m['batch_count'] += (int)($row['batch_count'] ?? 0);
            $m['awaiting_user_payment_count'] += (int)($row['awaiting_count'] ?? 0);
            $m['awaiting_user_payment_amount'] += (float)($row['awaiting_amount'] ?? 0);
            $m['pending_review_count'] += (int)($row['pending_count'] ?? 0);
            $m['pending_review_amount'] += (float)($row['pending_amount'] ?? 0);
            $m['need_more_info_count'] += (int)($row['need_count'] ?? 0);
            $m['need_more_info_amount'] += (float)($row['need_amount'] ?? 0);
            $m['paid_count'] += (int)($row['paid_count'] ?? 0);
            $m['paid_amount'] += (float)($row['paid_amount'] ?? 0);
            $m['rejected_count'] += (int)($row['rejected_count'] ?? 0);
            $m['rejected_amount'] += (float)($row['rejected_amount'] ?? 0);
            $m['proof_count'] += (int)($row['proof_count'] ?? 0);
        }
    } catch (Throwable $e) {}
    try {
        if (table_exists('mj_invoices')) {
            $whereUnbatched = mj_column_exists('mj_invoices','batch_id') ? ' AND (batch_id IS NULL OR batch_id=0)' : '';
            $row = one("SELECT
                COUNT(*) invoice_count,
                SUM(CASE WHEN status IN ('invoice_created','unpaid','draft','pending') {$whereUnbatched} THEN 1 ELSE 0 END) awaiting_count,
                SUM(CASE WHEN status IN ('invoice_created','unpaid','draft','pending') {$whereUnbatched} THEN total_amount ELSE 0 END) awaiting_amount,
                SUM(CASE WHEN status IN ('pending_review','submitted','uploaded','payment_uploaded','proof_uploaded') {$whereUnbatched} THEN 1 ELSE 0 END) pending_count,
                SUM(CASE WHEN status IN ('pending_review','submitted','uploaded','payment_uploaded','proof_uploaded') {$whereUnbatched} THEN total_amount ELSE 0 END) pending_amount,
                SUM(CASE WHEN status IN ('paid','approved','completed') {$whereUnbatched} THEN 1 ELSE 0 END) paid_count,
                SUM(CASE WHEN status IN ('paid','approved','completed') {$whereUnbatched} THEN total_amount ELSE 0 END) paid_amount
                FROM mj_invoices");
            $m['invoice_count'] += (int)($row['invoice_count'] ?? 0);
            $m['awaiting_user_payment_count'] += (int)($row['awaiting_count'] ?? 0);
            $m['awaiting_user_payment_amount'] += (float)($row['awaiting_amount'] ?? 0);
            $m['pending_review_count'] += (int)($row['pending_count'] ?? 0);
            $m['pending_review_amount'] += (float)($row['pending_amount'] ?? 0);
            $m['paid_count'] += (int)($row['paid_count'] ?? 0);
            $m['paid_amount'] += (float)($row['paid_amount'] ?? 0);
        }
    } catch (Throwable $e) {}
    foreach (['mj_upgrade_payment_proofs'=>'amount','mj_payment_proofs'=>'amount'] as $table=>$amountCol) {
        try {
            if (!table_exists($table) || !mj_column_exists($table, $amountCol)) { continue; }
            $amount = mj293_amount_expr($table);
            $unbatched = mj293_is_legacy_unbatched_where($table);
            $row = one("SELECT
                COUNT(*) proof_count,
                SUM(CASE WHEN (status IS NULL OR status='' OR status IN ('submitted','uploaded','pending','pending_review','proof_uploaded','payment_uploaded')) THEN 1 ELSE 0 END) pending_count,
                SUM(CASE WHEN (status IS NULL OR status='' OR status IN ('submitted','uploaded','pending','pending_review','proof_uploaded','payment_uploaded')) THEN {$amount} ELSE 0 END) pending_amount,
                SUM(CASE WHEN status='need_more_info' THEN 1 ELSE 0 END) need_count,
                SUM(CASE WHEN status='need_more_info' THEN {$amount} ELSE 0 END) need_amount,
                SUM(CASE WHEN status IN ('verified','approved','paid','completed','payment_verified') THEN 1 ELSE 0 END) paid_count,
                SUM(CASE WHEN status IN ('verified','approved','paid','completed','payment_verified') THEN {$amount} ELSE 0 END) paid_amount,
                SUM(CASE WHEN status IN ('rejected','cancelled','failed','payment_rejected') THEN 1 ELSE 0 END) rejected_count,
                SUM(CASE WHEN status IN ('rejected','cancelled','failed','payment_rejected') THEN {$amount} ELSE 0 END) rejected_amount
                FROM `{$table}` WHERE 1=1 {$unbatched}");
            $m['proof_count'] += (int)($row['proof_count'] ?? 0);
            $m['pending_review_count'] += (int)($row['pending_count'] ?? 0);
            $m['pending_review_amount'] += (float)($row['pending_amount'] ?? 0);
            $m['need_more_info_count'] += (int)($row['need_count'] ?? 0);
            $m['need_more_info_amount'] += (float)($row['need_amount'] ?? 0);
            $m['paid_count'] += (int)($row['paid_count'] ?? 0);
            $m['paid_amount'] += (float)($row['paid_amount'] ?? 0);
            $m['rejected_count'] += (int)($row['rejected_count'] ?? 0);
            $m['rejected_amount'] += (float)($row['rejected_amount'] ?? 0);
        } catch (Throwable $e) {}
    }
    try {
        if (table_exists('mj_receipts')) {
            $row = one("SELECT COUNT(*) c, COALESCE(SUM(amount),0) amount FROM mj_receipts WHERE status IN ('paid','approved','completed')");
            $m['receipt_count'] = (int)($row['c'] ?? 0);
            $m['receipt_amount'] = (float)($row['amount'] ?? 0);
        }
    } catch (Throwable $e) {}
    try {
        if (table_exists('mj_invoices')) {
            $row = one("SELECT COUNT(*) c, COALESCE(SUM(total_amount),0) amount FROM mj_invoices WHERE status IN ('paid','approved','completed')");
            $m['invoice_paid_count'] = (int)($row['c'] ?? 0);
            $m['invoice_paid_amount'] = (float)($row['amount'] ?? 0);
        }
    } catch (Throwable $e) {}
    try {
        if (table_exists('mj_wallet_transactions')) {
            $row = one("SELECT
                SUM(CASE WHEN transaction_type='wallet_payment' AND direction='debit' THEN 1 ELSE 0 END) wallet_payment_debit_count,
                SUM(CASE WHEN transaction_type='wallet_payment' AND direction='debit' THEN amount ELSE 0 END) wallet_payment_debit_amount,
                SUM(CASE WHEN transaction_type='wallet_topup' AND direction='credit' THEN 1 ELSE 0 END) wallet_topup_credit_count,
                SUM(CASE WHEN transaction_type='wallet_topup' AND direction='credit' THEN amount ELSE 0 END) wallet_topup_credit_amount,
                SUM(CASE WHEN transaction_type='admin_credit' AND direction='credit' THEN amount ELSE 0 END) wallet_admin_credit_amount,
                SUM(CASE WHEN transaction_type='admin_debit' AND direction='debit' THEN amount ELSE 0 END) wallet_admin_debit_amount
                FROM mj_wallet_transactions");
            $m['wallet_payment_debit_count'] = (int)($row['wallet_payment_debit_count'] ?? 0);
            $m['wallet_payment_debit_amount'] = (float)($row['wallet_payment_debit_amount'] ?? 0);
            $m['wallet_topup_credit_count'] = (int)($row['wallet_topup_credit_count'] ?? 0);
            $m['wallet_topup_credit_amount'] = (float)($row['wallet_topup_credit_amount'] ?? 0);
            $m['wallet_admin_credit_amount'] = (float)($row['wallet_admin_credit_amount'] ?? 0);
            $m['wallet_admin_debit_amount'] = (float)($row['wallet_admin_debit_amount'] ?? 0);
        }
    } catch (Throwable $e) {}
    $m['paid_receipt_delta'] = round((float)$m['paid_amount'] - (float)$m['receipt_amount'], 2);
    $m['invoice_receipt_delta'] = round((float)$m['invoice_paid_amount'] - (float)$m['receipt_amount'], 2);
    try { if (table_exists('mj_refund_requests')) { $m['refunds_pending'] = safe_count('SELECT COUNT(*) c FROM mj_refund_requests WHERE status IN ("pending","submitted","open")'); } } catch (Throwable $e) {}
    $m['open_count'] = $m['awaiting_user_payment_count'] + $m['pending_review_count'] + $m['need_more_info_count'];
    $m['open_amount'] = $m['awaiting_user_payment_amount'] + $m['pending_review_amount'] + $m['need_more_info_amount'];
    return $m;
}
}

if (!function_exists('mj293_user_billing_summary')) {
function mj293_user_billing_summary(int $userId): array {
    $out = ['invoice_created'=>0,'invoice_created_amount'=>0.0,'pending_review'=>0,'pending_amount'=>0.0,'uploaded_proofs'=>0,'paid_count'=>0,'open_amount'=>0.0];
    try {
        if (!table_exists('mj_payment_batches')) { return $out; }
        $row = one("SELECT
            SUM(CASE WHEN status IN ('invoice_created','unpaid','draft') THEN 1 ELSE 0 END) invoice_created,
            SUM(CASE WHEN status IN ('invoice_created','unpaid','draft') THEN total_amount ELSE 0 END) invoice_created_amount,
            SUM(CASE WHEN status IN ('pending_review','submitted','uploaded','payment_uploaded','proof_uploaded') THEN 1 ELSE 0 END) pending_review,
            SUM(CASE WHEN status IN ('pending_review','submitted','uploaded','payment_uploaded','proof_uploaded') THEN total_amount ELSE 0 END) pending_amount,
            SUM(CASE WHEN proof_path IS NOT NULL AND proof_path<>'' THEN 1 ELSE 0 END) uploaded_proofs,
            SUM(CASE WHEN status IN ('approved','partial_approved','paid','completed','payment_verified') THEN 1 ELSE 0 END) paid_count
            FROM mj_payment_batches WHERE user_id=?", [$userId]);
        foreach ($out as $k=>$v) { if (isset($row[$k])) { $out[$k] = is_float($v) ? (float)$row[$k] : (int)$row[$k]; } }
        $out['open_amount'] = (float)$out['invoice_created_amount'] + (float)$out['pending_amount'];
    } catch (Throwable $e) {}
    return $out;
}
}



/* ===== v2.9.5 Wallet + Payment Center Pro helpers ===== */
if (!function_exists('mj295_wallet_ensure_schema')) {
function mj295_wallet_ensure_schema(): void {
    static $done=false; if($done){return;} $done=true;
    if (!function_exists('mj_is_installed') || !mj_is_installed()) { return; }
    try {
        q("CREATE TABLE IF NOT EXISTS mj_wallets (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            user_id BIGINT NOT NULL,
            currency_code VARCHAR(10) NOT NULL DEFAULT 'SGD',
            balance DECIMAL(12,2) NOT NULL DEFAULT 0,
            held_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            status VARCHAR(40) NOT NULL DEFAULT 'active',
            admin_note TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_user_currency (user_id,currency_code),
            INDEX(user_id), INDEX(status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    } catch (Throwable $e) {}
    try {
        q("CREATE TABLE IF NOT EXISTS mj_wallet_transactions (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            wallet_id BIGINT NULL,
            user_id BIGINT NOT NULL,
            currency_code VARCHAR(10) NOT NULL DEFAULT 'SGD',
            transaction_type VARCHAR(60) NOT NULL,
            direction VARCHAR(20) NOT NULL DEFAULT 'credit',
            amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            balance_before DECIMAL(12,2) NOT NULL DEFAULT 0,
            balance_after DECIMAL(12,2) NOT NULL DEFAULT 0,
            held_before DECIMAL(12,2) NOT NULL DEFAULT 0,
            held_after DECIMAL(12,2) NOT NULL DEFAULT 0,
            description TEXT NULL,
            source_type VARCHAR(80) NULL,
            source_id BIGINT NULL,
            invoice_id BIGINT NULL,
            batch_id BIGINT NULL,
            admin_id BIGINT NULL,
            admin_note TEXT NULL,
            ip_address VARCHAR(80) NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX(wallet_id), INDEX(user_id), INDEX(transaction_type), INDEX(batch_id), INDEX(invoice_id), INDEX(created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    } catch (Throwable $e) {}
    try {
        q("CREATE TABLE IF NOT EXISTS mj_wallet_holds (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            wallet_id BIGINT NOT NULL,
            user_id BIGINT NOT NULL,
            batch_id BIGINT NULL,
            invoice_id BIGINT NULL,
            amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            currency_code VARCHAR(10) NOT NULL DEFAULT 'SGD',
            status VARCHAR(40) NOT NULL DEFAULT 'held',
            note TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            released_at DATETIME NULL,
            captured_at DATETIME NULL,
            INDEX(wallet_id), INDEX(user_id), INDEX(batch_id), INDEX(invoice_id), INDEX(status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    } catch (Throwable $e) {}
    try { if (function_exists('mj291_add_col')) {
        // v2.9.8: old installs may already have mj_wallets from earlier versions with fewer columns.
        // Add every required column safely so wallet search/list/add never silently fails.
        mj291_add_col('mj_wallets','currency_code',"VARCHAR(10) NOT NULL DEFAULT \'SGD\'");
        mj291_add_col('mj_wallets','balance','DECIMAL(12,2) NOT NULL DEFAULT 0');
        mj291_add_col('mj_wallets','held_amount','DECIMAL(12,2) NOT NULL DEFAULT 0');
        mj291_add_col('mj_wallets','status',"VARCHAR(40) NOT NULL DEFAULT \'active\'");
        mj291_add_col('mj_wallets','admin_note','TEXT NULL');
        mj291_add_col('mj_wallets','created_at','DATETIME DEFAULT CURRENT_TIMESTAMP');
        mj291_add_col('mj_wallets','updated_at','DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP');
        mj291_add_col('mj_wallet_transactions','wallet_id','BIGINT NULL');
        mj291_add_col('mj_wallet_transactions','user_id','BIGINT NOT NULL DEFAULT 0');
        mj291_add_col('mj_wallet_transactions','currency_code',"VARCHAR(10) NOT NULL DEFAULT \'SGD\'");
        mj291_add_col('mj_wallet_transactions','transaction_type',"VARCHAR(60) NOT NULL DEFAULT \'adjustment\'");
        mj291_add_col('mj_wallet_transactions','direction',"VARCHAR(20) NOT NULL DEFAULT \'credit\'");
        mj291_add_col('mj_wallet_transactions','balance_before','DECIMAL(12,2) NOT NULL DEFAULT 0');
        mj291_add_col('mj_wallet_transactions','balance_after','DECIMAL(12,2) NOT NULL DEFAULT 0');
        mj291_add_col('mj_wallet_transactions','held_before','DECIMAL(12,2) NOT NULL DEFAULT 0');
        mj291_add_col('mj_wallet_transactions','held_after','DECIMAL(12,2) NOT NULL DEFAULT 0');
        mj291_add_col('mj_wallet_transactions','description','TEXT NULL');
        mj291_add_col('mj_wallet_transactions','source_type','VARCHAR(80) NULL');
        mj291_add_col('mj_wallet_transactions','source_id','BIGINT NULL');
        mj291_add_col('mj_wallet_transactions','invoice_id','BIGINT NULL');
        mj291_add_col('mj_wallet_transactions','batch_id','BIGINT NULL');
        mj291_add_col('mj_wallet_transactions','admin_id','BIGINT NULL');
        mj291_add_col('mj_wallet_transactions','admin_note','TEXT NULL');
        mj291_add_col('mj_wallet_transactions','ip_address','VARCHAR(80) NULL');
    } } catch (Throwable $e) {}
    try { if (function_exists('mj291_setting_default')) {
        mj291_setting_default('wallet_enabled','1');
        mj291_setting_default('wallet_payment_enabled','1');
        mj291_setting_default('wallet_payment_admin_approval_required','0');
        mj291_setting_default('wallet_low_balance_amount','10.00');
        mj291_setting_default('wallet_topup_enabled','1');
        mj291_setting_default('wallet_topup_min_amount','1.00');
        mj291_setting_default('wallet_topup_max_amount','9999.00');
        mj291_setting_default('wallet_default_currency', setting('listing_upgrade_currency','SGD') ?: 'SGD');
    } } catch (Throwable $e) {}
}
}

if (!function_exists('mj295_wallet_get')) {
function mj295_wallet_get(int $userId, string $currency=''): array {
    mj295_wallet_ensure_schema();
    $currency = strtoupper(trim($currency ?: (setting('wallet_default_currency','SGD') ?: 'SGD')));
    if ($currency === '') { $currency = 'SGD'; }
    $w = null;
    try { $w = one('SELECT * FROM mj_wallets WHERE user_id=? AND currency_code=? LIMIT 1', [$userId,$currency]); } catch (Throwable $e) { $w=null; }
    // v2.9.8: support old wallet table that had UNIQUE(user_id) and only one currency row.
    if (!$w) {
        try { $w = one('SELECT * FROM mj_wallets WHERE user_id=? LIMIT 1', [$userId]); } catch (Throwable $e) { $w=null; }
        if ($w && empty($w['currency_code'])) {
            try { q('UPDATE mj_wallets SET currency_code=? WHERE id=?', [$currency,(int)$w['id']]); $w['currency_code']=$currency; } catch (Throwable $e) {}
        }
    }
    if (!$w) {
        try { q('INSERT INTO mj_wallets(user_id,currency_code,balance,held_amount,status,created_at) VALUES(?,?,0,0,"active",NOW())', [$userId,$currency]); } catch (Throwable $e) {}
        try { $w = one('SELECT * FROM mj_wallets WHERE user_id=? AND currency_code=? LIMIT 1', [$userId,$currency]); } catch (Throwable $e) { $w=null; }
        if (!$w) { try { $w = one('SELECT * FROM mj_wallets WHERE user_id=? LIMIT 1', [$userId]); } catch (Throwable $e) { $w=null; } }
    }
    $w = $w ?: ['id'=>0,'user_id'=>$userId,'currency_code'=>$currency,'balance'=>0,'held_amount'=>0,'status'=>'active'];
    if (!isset($w['held_amount'])) { $w['held_amount'] = 0; }
    if (!isset($w['status']) || $w['status'] === '') { $w['status'] = 'active'; }
    if (!isset($w['currency_code']) || $w['currency_code'] === '') { $w['currency_code'] = $currency; }
    $w['available_balance'] = max(0, (float)($w['balance'] ?? 0) - (float)($w['held_amount'] ?? 0));
    return $w;
}
}

if (!function_exists('mj295_wallet_tx')) {
function mj295_wallet_tx(int $walletId, int $userId, string $currency, string $type, string $direction, float $amount, float $before, float $after, float $heldBefore, float $heldAfter, string $description='', string $sourceType='', int $sourceId=0, int $invoiceId=0, int $batchId=0, int $adminId=0, string $adminNote=''): void {
    try { q('INSERT INTO mj_wallet_transactions(wallet_id,user_id,currency_code,transaction_type,direction,amount,balance_before,balance_after,held_before,held_after,description,source_type,source_id,invoice_id,batch_id,admin_id,admin_note,ip_address,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW())', [
        $walletId ?: null, $userId, $currency, $type, $direction, $amount, $before, $after, $heldBefore, $heldAfter, $description, $sourceType ?: null, $sourceId ?: null, $invoiceId ?: null, $batchId ?: null, $adminId ?: null, $adminNote ?: null, (string)($_SERVER['REMOTE_ADDR'] ?? '')
    ]); } catch (Throwable $e) {}
}
}

if (!function_exists('mj295_wallet_adjust')) {
function mj295_wallet_adjust(int $userId, float $amount, string $direction, int $adminId=0, string $note='', string $currency=''): void {
    if ($amount <= 0) { throw new Exception('Wallet amount must be greater than 0.'); }
    $currency = strtoupper(trim($currency ?: (setting('wallet_default_currency','SGD') ?: 'SGD')));
    $w = mj295_wallet_get($userId, $currency);
    if ((string)($w['status'] ?? '') === 'locked') { throw new Exception('This user wallet is locked.'); }
    $before = (float)($w['balance'] ?? 0); $heldBefore = (float)($w['held_amount'] ?? 0);
    if ($direction === 'debit') {
        $available = max(0, $before - $heldBefore);
        if ($available + 0.00001 < $amount) { throw new Exception('Insufficient available wallet balance.'); }
        $after = $before - $amount; $type='admin_debit'; $desc='Admin deducted wallet credit';
    } else {
        $after = $before + $amount; $type='admin_credit'; $desc='Admin added wallet credit';
    }
    q('UPDATE mj_wallets SET balance=?, updated_at=NOW() WHERE id=?', [$after,(int)$w['id']]);
    mj295_wallet_tx((int)$w['id'],$userId,$currency,$type,$direction,$amount,$before,$after,$heldBefore,$heldBefore,$note ?: $desc,'admin_adjustment',0,0,0,$adminId,$note);
    try { admin_log($type, 'mj_wallets', (int)$w['id'], ['user_id'=>$userId,'amount'=>$amount,'currency'=>$currency,'note'=>$note]); } catch (Throwable $e) {}
}
}

if (!function_exists('mj295_hold_wallet_for_batch')) {
function mj295_hold_wallet_for_batch(int $batchId, int $userId): void {
    mj295_wallet_ensure_schema();
    $batch = one('SELECT * FROM mj_payment_batches WHERE id=? AND user_id=? LIMIT 1', [$batchId,$userId]);
    if (!$batch) { throw new Exception('Invoice not found.'); }
    $amount = (float)($batch['total_amount'] ?? 0); $currency = (string)($batch['currency_code'] ?? 'SGD');
    if ($amount <= 0) { throw new Exception('Invoice amount is invalid.'); }
    $existing = one('SELECT * FROM mj_wallet_holds WHERE batch_id=? AND user_id=? AND status="held" LIMIT 1', [$batchId,$userId]);
    if ($existing) { return; }
    $w = mj295_wallet_get($userId, $currency);
    if ((string)($w['status'] ?? '') === 'locked') { throw new Exception('Your wallet is locked. Please contact admin.'); }
    $before = (float)$w['balance']; $heldBefore = (float)$w['held_amount']; $available = max(0, $before - $heldBefore);
    if ($available + 0.00001 < $amount) { throw new Exception('Insufficient wallet balance. Available: '.money($available, $currency).'. Invoice total: '.money($amount, $currency).'.'); }
    $heldAfter = $heldBefore + $amount;
    q('UPDATE mj_wallets SET held_amount=?, updated_at=NOW() WHERE id=?', [$heldAfter,(int)$w['id']]);
    q('INSERT INTO mj_wallet_holds(wallet_id,user_id,batch_id,invoice_id,amount,currency_code,status,note,created_at) VALUES(?,?,?,?,?,?,"held","Wallet payment reserved for instant approval",NOW())', [(int)$w['id'],$userId,$batchId,(int)($batch['invoice_id']??0) ?: null,$amount,$currency]);
    mj295_wallet_tx((int)$w['id'],$userId,$currency,'wallet_hold','hold',$amount,$before,$before,$heldBefore,$heldAfter,'Hold for invoice '.((string)($batch['batch_no'] ?? '#'.$batchId)),'payment_batch',$batchId,(int)($batch['invoice_id']??0),$batchId,0,'');
}
}

if (!function_exists('mj295_release_wallet_hold')) {
function mj295_release_wallet_hold(int $batchId, string $reason=''): void {
    mj295_wallet_ensure_schema();
    try {
        $holds = all_rows('SELECT * FROM mj_wallet_holds WHERE batch_id=? AND status="held"', [$batchId]);
        foreach ($holds as $h) {
            $w = mj295_wallet_get((int)$h['user_id'], (string)$h['currency_code']);
            $before = (float)$w['balance']; $heldBefore = (float)$w['held_amount']; $amount=(float)$h['amount'];
            $heldAfter = max(0, $heldBefore - $amount);
            q('UPDATE mj_wallets SET held_amount=?, updated_at=NOW() WHERE id=?', [$heldAfter,(int)$w['id']]);
            q('UPDATE mj_wallet_holds SET status="released", note=?, released_at=NOW() WHERE id=?', [$reason ?: 'Released', (int)$h['id']]);
            mj295_wallet_tx((int)$w['id'],(int)$h['user_id'],(string)$h['currency_code'],'wallet_hold_released','release',$amount,$before,$before,$heldBefore,$heldAfter,$reason ?: 'Wallet hold released','payment_batch',$batchId,(int)($h['invoice_id']??0),$batchId,0,'');
        }
    } catch (Throwable $e) {}
}
}

if (!function_exists('mj295_capture_wallet_hold_for_batch')) {
function mj295_capture_wallet_hold_for_batch(int $batchId, float $captureAmount, int $adminId=0, string $note=''): float {
    mj295_wallet_ensure_schema();
    $hold = one('SELECT * FROM mj_wallet_holds WHERE batch_id=? AND status="held" ORDER BY id ASC LIMIT 1', [$batchId]);
    if (!$hold) { return 0.0; }
    $w = mj295_wallet_get((int)$hold['user_id'], (string)$hold['currency_code']);
    $holdAmount = (float)$hold['amount'];
    $capture = max(0, min($captureAmount > 0 ? $captureAmount : $holdAmount, $holdAmount));
    $before = (float)$w['balance']; $heldBefore = (float)$w['held_amount'];
    $after = max(0, $before - $capture); $heldAfter = max(0, $heldBefore - $holdAmount);
    q('UPDATE mj_wallets SET balance=?, held_amount=?, updated_at=NOW() WHERE id=?', [$after,$heldAfter,(int)$w['id']]);
    q('UPDATE mj_wallet_holds SET status="captured", note=?, captured_at=NOW() WHERE id=?', [$note ?: 'Captured for approved payment', (int)$hold['id']]);
    mj295_wallet_tx((int)$w['id'],(int)$hold['user_id'],(string)$hold['currency_code'],'wallet_payment','debit',$capture,$before,$after,$heldBefore,$heldAfter,$note ?: 'Wallet payment captured','payment_batch',$batchId,(int)($hold['invoice_id']??0),$batchId,$adminId,$note);
    if ($holdAmount > $capture) {
        mj295_wallet_tx((int)$w['id'],(int)$hold['user_id'],(string)$hold['currency_code'],'wallet_partial_release','release',$holdAmount-$capture,$after,$after,$heldAfter,$heldAfter,'Unused wallet hold released','payment_batch',$batchId,(int)($hold['invoice_id']??0),$batchId,$adminId,$note);
    }
    return $capture;
}
}

if (!function_exists('mj295_pay_invoice_by_wallet')) {
function mj295_pay_invoice_by_wallet(int $batchId, int $userId, string $note=''): void {
    mj291_payment_ensure_schema(); mj295_wallet_ensure_schema();
    if (setting('wallet_payment_enabled','1') !== '1') { throw new Exception('Wallet payment is disabled by admin.'); }
    if (!mj_user_feature_enabled('wallet', $userId)) { throw new Exception('Wallet is not enabled for your account.'); }
    $batch = one('SELECT * FROM mj_payment_batches WHERE id=? AND user_id=? LIMIT 1', [$batchId,$userId]);
    if (!$batch) { throw new Exception('Invoice not found.'); }
    $status = strtolower((string)($batch['status'] ?? ''));
    if (!in_array($status, ['invoice_created','unpaid','draft','need_more_info','rejected'], true)) { throw new Exception('This invoice cannot be paid by wallet now.'); }
    foreach (mj291_batch_items($batchId) as $it) {
        if ((string)($it['item_type'] ?? '') === 'wallet_topup') { throw new Exception('Wallet top-up invoice cannot be paid using wallet credit. Please use an admin-enabled payment method and upload proof.'); }
    }
    // v2.9.10: full wallet payment is instant. Hold first for audit safety, then immediately approve/capture through the normal activation flow.
    mj295_hold_wallet_for_batch($batchId, $userId);
    $ref = 'WALLET-'.$batchId.'-'.date('YmdHis');
    $note = trim($note);
    q('UPDATE mj_payment_batches SET status="pending_review", payment_method="wallet", reference_no=?, proof_path="WALLET", note=?, updated_at=NOW() WHERE id=? AND user_id=?', [$ref,$note,$batchId,$userId]);
    q('UPDATE mj_payment_batch_items SET status="pending_review", activation_status="not_activated", updated_at=NOW() WHERE batch_id=? AND user_id=?', [$batchId,$userId]);
    if (!empty($batch['invoice_id'])) { q('UPDATE mj_invoices SET status="pending_review", payment_method="wallet", reference_no=?, proof_path="WALLET", updated_at=NOW() WHERE id=? AND user_id=?', [$ref,(int)$batch['invoice_id'],$userId]); }
    foreach (mj291_batch_items($batchId) as $it) { mj292_mark_source_invoice_status($it, $batchId, (int)($batch['invoice_id'] ?? 0), $userId, 'payment_uploaded'); }
    // Direct approval: wallet balance is deducted immediately and receipt is generated. No admin review needed for full wallet credit payment.
    mj291_review_batch($batchId, 'approve_all', 0, $note !== '' ? $note : 'Auto-approved: paid fully by wallet credit');
}
}


if (!function_exists('mj295_wallet_admin_rows')) {
function mj295_wallet_admin_rows(string $qterm=''): array {
    mj295_wallet_ensure_schema();
    $where='1=1'; $params=[];
    if ($qterm !== '') { $where .= ' AND (u.name LIKE ? OR u.email LIKE ?)'; $params[]='%'.$qterm.'%'; $params[]='%'.$qterm.'%'; }
    try { return all_rows("SELECT w.*, u.name user_name, u.email FROM mj_wallets w LEFT JOIN mj_users u ON u.id=w.user_id WHERE {$where} ORDER BY w.updated_at DESC, w.id DESC LIMIT 200", $params); }
    catch (Throwable $e) { return []; }
}
}
