<?php
/* MJ Marketplace v2.9.12 Admin Portal UX + Safe Reset + Reports + Automation helpers */
if (!function_exists('mj292_clean_ident')) {
function mj292_clean_ident(string $name): string { return preg_replace('/[^a-zA-Z0-9_]/', '', $name); }
}

if (!function_exists('mj292_has_table')) {
function mj292_has_table(string $table): bool { return function_exists('table_exists') && table_exists($table); }
}

if (!function_exists('mj292_has_col')) {
function mj292_has_col(string $table, string $col): bool { return function_exists('mj_has_col') ? mj_has_col($table, $col) : (function_exists('mj_column_exists') && mj_column_exists($table, $col)); }
}

if (!function_exists('mj292_add_col')) {
function mj292_add_col(string $table, string $column, string $definition): void {
    $table = mj292_clean_ident($table); $column = mj292_clean_ident($column);
    if ($table === '' || $column === '') { return; }
    try { if (mj292_has_table($table) && !mj292_has_col($table, $column)) { q("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}"); } } catch (Throwable $e) {}
}
}

if (!function_exists('mj292_ensure_schema')) {
function mj292_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_admin_reset_logs (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            admin_id BIGINT NULL,
            admin_name VARCHAR(190) NULL,
            admin_email VARCHAR(190) NULL,
            reset_type VARCHAR(120) NOT NULL,
            selected_options TEXT NULL,
            affected_tables TEXT NULL,
            records_deleted BIGINT NOT NULL DEFAULT 0,
            files_deleted BIGINT NOT NULL DEFAULT 0,
            ip_address VARCHAR(80) NULL,
            user_agent VARCHAR(255) NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX(admin_id), INDEX(reset_type), INDEX(created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        q("CREATE TABLE IF NOT EXISTS mj_admin_notes (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            admin_id BIGINT NULL,
            related_type VARCHAR(80) NOT NULL,
            related_id BIGINT NOT NULL DEFAULT 0,
            note TEXT NOT NULL,
            reminder_at DATETIME NULL,
            status VARCHAR(40) NOT NULL DEFAULT 'open',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX(admin_id), INDEX(related_type,related_id), INDEX(status), INDEX(reminder_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        q("CREATE TABLE IF NOT EXISTS mj_support_tickets (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            user_id BIGINT NULL,
            subject VARCHAR(220) NOT NULL,
            ticket_type VARCHAR(80) NULL,
            priority VARCHAR(40) NOT NULL DEFAULT 'normal',
            status VARCHAR(40) NOT NULL DEFAULT 'open',
            message TEXT NULL,
            admin_id BIGINT NULL,
            admin_reply TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX(user_id), INDEX(status), INDEX(ticket_type), INDEX(created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        q("CREATE TABLE IF NOT EXISTS mj_admin_exception_logs (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            exception_key VARCHAR(190) NOT NULL UNIQUE,
            exception_type VARCHAR(80) NOT NULL,
            severity VARCHAR(40) NOT NULL DEFAULT 'normal',
            title VARCHAR(220) NOT NULL,
            message TEXT NULL,
            action_url VARCHAR(255) NULL,
            related_type VARCHAR(80) NULL,
            related_id BIGINT NULL,
            status VARCHAR(40) NOT NULL DEFAULT 'open',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            resolved_at DATETIME NULL,
            INDEX(exception_type), INDEX(severity), INDEX(status), INDEX(created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        q("CREATE TABLE IF NOT EXISTS mj_lead_events (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            user_id BIGINT NULL,
            listing_id BIGINT NULL,
            company_id BIGINT NULL,
            event_type VARCHAR(80) NOT NULL,
            event_source VARCHAR(80) NULL,
            ip_address VARCHAR(80) NULL,
            user_agent VARCHAR(255) NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX(user_id), INDEX(listing_id), INDEX(company_id), INDEX(event_type), INDEX(created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        q("CREATE TABLE IF NOT EXISTS mj_admin_role_permissions (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            role_key VARCHAR(80) NOT NULL,
            module_key VARCHAR(120) NOT NULL,
            can_view TINYINT(1) NOT NULL DEFAULT 1,
            can_create TINYINT(1) NOT NULL DEFAULT 0,
            can_edit TINYINT(1) NOT NULL DEFAULT 0,
            can_delete TINYINT(1) NOT NULL DEFAULT 0,
            can_approve TINYINT(1) NOT NULL DEFAULT 0,
            UNIQUE KEY uniq_role_module(role_key,module_key)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        $settings = [
            'v292_admin_portal_ready'=>'1',
            'auto_approve_listings'=>'0',
            'auto_approve_companies'=>'0',
            'auto_hide_reported_listing_count'=>'3',
            'auto_listing_quality_check'=>'1',
            'auto_duplicate_listing_check'=>'1',
            'auto_create_renewal_invoice'=>'1',
            'auto_renewal_invoice_days'=>'7',
            'abandoned_invoice_followup_days'=>'1,3,7',
            'abandoned_invoice_cancel_days'=>'30',
            'manual_payment_approval_required'=>'1',
            'wallet_topup_admin_approval_required'=>'1',
            'high_amount_approval_threshold'=>'1000.00'
        ];
        foreach ($settings as $k=>$v) {
            q('INSERT INTO mj_settings(setting_key,setting_value) VALUES(?,?) ON DUPLICATE KEY UPDATE setting_value=setting_value', [$k,$v]);
        }
    } catch (Throwable $e) {}
}
}

if (!function_exists('mj292_money0')) {
function mj292_money0($amount, string $currency='SGD'): string { return function_exists('money') ? money((float)$amount, $currency) : e($currency.' '.number_format((float)$amount, 2)); }
}

if (!function_exists('mj292_status_badge')) {
function mj292_status_badge(string $status): string {
    if (function_exists('mj291_status_badge')) { return mj291_status_badge($status); }
    $s = strtolower(trim($status));
    $cls = 'secondary';
    if (in_array($s, ['active','approved','paid','verified','completed','open','success'], true)) { $cls = 'success'; }
    elseif (in_array($s, ['pending','pending_review','submitted','uploaded','draft','unpaid','warning'], true)) { $cls = 'warning'; }
    elseif (in_array($s, ['rejected','failed','expired','cancelled','suspended','closed','danger'], true)) { $cls = 'danger'; }
    elseif (in_array($s, ['info','need_more_info'], true)) { $cls = 'info'; }
    return '<span class="badge badge-'.$cls.'">'.e(ucwords(str_replace('_',' ', $status ?: 'Unknown'))).'</span>';
}
}

if (!function_exists('mj292_count')) {
function mj292_count(string $table, string $where='1=1', array $params=[]): int {
    $table = mj292_clean_ident($table); if ($table === '' || !mj292_has_table($table)) { return 0; }
    try { $r = one("SELECT COUNT(*) c FROM `{$table}` WHERE {$where}", $params); return (int)($r['c'] ?? 0); } catch (Throwable $e) { return 0; }
}
}

if (!function_exists('mj292_sum')) {
function mj292_sum(string $table, string $column, string $where='1=1', array $params=[]): float {
    $table = mj292_clean_ident($table); $column = mj292_clean_ident($column);
    if ($table === '' || $column === '' || !mj292_has_table($table) || !mj292_has_col($table,$column)) { return 0.0; }
    try { $r = one("SELECT COALESCE(SUM(`{$column}`),0) s FROM `{$table}` WHERE {$where}", $params); return (float)($r['s'] ?? 0); } catch (Throwable $e) { return 0.0; }
}
}

if (!function_exists('mj292_date_col')) {
function mj292_date_col(string $table): string {
    foreach (['created_at','updated_at','entry_date','addedon','date_added','paid_at','reviewed_at'] as $c) { if (mj292_has_col($table,$c)) { return $c; } }
    return 'id';
}
}

if (!function_exists('mj292_admin_stats')) {
function mj292_admin_stats(): array {
    $paidWhere = "status IN ('approved','paid','completed','payment_verified')";
    return [
        'users_total'=>mj292_count('mj_users'),
        'users_today'=>mj292_count('mj_users', mj292_has_col('mj_users','created_at') ? 'DATE(created_at)=CURDATE()' : '1=0'),
        'listings_total'=>mj292_count('mj_listings'),
        'listings_active'=>mj292_count('mj_listings', mj292_has_col('mj_listings','status') ? "status='active'" : '1=1'),
        'listings_pending'=>mj292_count('mj_listings', mj292_has_col('mj_listings','status') ? "status IN ('pending','draft')" : '1=0'),
        'listings_expired'=>mj292_count('mj_listings', mj292_has_col('mj_listings','status') ? "status='expired'" : (mj292_has_col('mj_listings','expires_at') ? 'expires_at<NOW()' : '1=0')),
        'listings_sponsored'=>mj292_count('mj_listings', mj292_has_col('mj_listings','is_sponsored') ? 'is_sponsored=1' : '1=0'),
        'listings_featured'=>mj292_count('mj_listings', mj292_has_col('mj_listings','is_featured') ? 'is_featured=1' : '1=0'),
        'companies_total'=>mj292_count('mj_companies'),
        'companies_pending'=>mj292_count('mj_companies', mj292_has_col('mj_companies','status') ? "status IN ('pending','draft')" : '1=0'),
        'payment_pending'=>mj292_count('mj_payment_batches', mj292_has_col('mj_payment_batches','status') ? "status IN ('pending_review','submitted','uploaded','payment_uploaded','proof_uploaded')" : '1=0'),
        'invoice_open'=>mj292_count('mj_invoices', mj292_has_col('mj_invoices','status') ? "status NOT IN ('paid','approved','cancelled','rejected')" : '1=0'),
        'receipts_total'=>mj292_count('mj_receipts'),
        'support_open'=>mj292_count('mj_support_tickets', "status IN ('open','pending')"),
        'reports_open'=>mj292_count('mj_reports', mj292_has_col('mj_reports','status') ? "status IN ('new','open','pending')" : '1=0'),
        'wallet_balance'=>mj292_sum('mj_wallets','balance'),
        'receipt_amount'=>mj292_sum('mj_receipts','amount', $paidWhere),
        'invoice_paid_amount'=>mj292_sum('mj_invoices','total_amount', "status IN ('paid','approved')"),
        'wallet_debit'=>mj292_sum('mj_wallet_transactions','amount', "direction='debit'"),
        'wallet_credit'=>mj292_sum('mj_wallet_transactions','amount', "direction='credit'"),
    ];
}
}

if (!function_exists('mj292_action_queue')) {
function mj292_action_queue(): array {
    return [
        ['Payment Proof Approval','Manual payment proofs waiting admin review','payment-center.php?tab=review','fas fa-receipt','warning',mj292_count('mj_payment_batches', "status IN ('pending_review','submitted','uploaded','payment_uploaded','proof_uploaded')")],
        ['Wallet Top-up Approval','Top-up proof needs approval before credit is added','payment-center.php?tab=review&type=wallet_topup','fas fa-wallet','success',mj292_count('mj_payment_batch_items', "item_type='wallet_topup' AND status IN ('pending_review','submitted','uploaded','pending')")],
        ['Pending Listing Updates','User edits waiting old-vs-new admin review','listing-revisions.php','fas fa-code-branch','warning',function_exists('mj_review_pending_count') ? mj_review_pending_count('listing') : mj292_count('mj_listing_revisions', "status='pending'")],
        ['Pending Company Updates','Company profile edits waiting admin review','company-revisions.php','fas fa-code-branch','info',function_exists('mj_review_pending_count') ? mj_review_pending_count('company') : mj292_count('mj_company_revisions', "status='pending'")],
        ['Pending Deal Updates','Deal edits waiting admin review','deal-revisions.php','fas fa-code-branch','danger',function_exists('mj_review_pending_count') ? mj_review_pending_count('deal') : mj292_count('mj_deal_revisions', "status='pending'")],
        ['Pending Listings','Listings waiting review when auto-approval is off','listings.php?status=pending','fas fa-tags','primary',mj292_count('mj_listings', "status IN ('pending','draft')")],
        ['Pending Companies','Company profiles waiting review','companies.php?status=pending','fas fa-building','info',mj292_count('mj_companies', "status IN ('pending','draft')")],
        ['Reported Listings','Abuse/scam reports waiting review','reported-listings.php','fas fa-flag','danger',mj292_count('mj_reports', "status IN ('new','open','pending')")],
        ['Finance Mismatch','Invoices/receipts/wallet exceptions','finance-reconciliation.php','fas fa-balance-scale','danger',count(mj292_finance_checks(true))],
        ['Activation Check','Paid services not activated','activation-checker.php','fas fa-bolt','warning',count(mj292_activation_checks(true))],
        ['Support Tickets','User support requests waiting reply','support.php','fas fa-headset','secondary',mj292_count('mj_support_tickets', "status IN ('open','pending')")],
    ];
}
}

if (!function_exists('mj292_finance_checks')) {
function mj292_finance_checks(bool $onlyOpen=false): array {
    $checks = [];
    try {
        if (mj292_has_table('mj_invoices') && mj292_has_table('mj_receipts')) {
            $rows = all_rows("SELECT i.id,i.invoice_no,i.user_id,i.total_amount,i.currency_code,i.status FROM mj_invoices i LEFT JOIN mj_receipts r ON r.invoice_id=i.id WHERE i.status IN ('paid','approved') AND r.id IS NULL ORDER BY i.id DESC LIMIT 50");
            foreach ($rows as $r) { $checks[] = ['severity'=>'high','title'=>'Paid invoice missing receipt','message'=>'Invoice '.($r['invoice_no'] ?: '#'.$r['id']).' is paid but no receipt was found.','url'=>'../invoice-print.php?invoice_id='.(int)$r['id'],'type'=>'receipt_missing']; }
        }
    } catch (Throwable $e) {}
    try {
        if (mj292_has_table('mj_receipts') && mj292_has_table('mj_invoices')) {
            $rows = all_rows("SELECT r.id,r.receipt_no,r.invoice_id,r.amount receipt_amount,i.total_amount invoice_amount,i.currency_code FROM mj_receipts r INNER JOIN mj_invoices i ON i.id=r.invoice_id WHERE ABS(COALESCE(r.amount,0)-COALESCE(i.total_amount,0))>0.01 ORDER BY r.id DESC LIMIT 50");
            foreach ($rows as $r) { $checks[] = ['severity'=>'normal','title'=>'Receipt amount mismatch','message'=>'Receipt '.($r['receipt_no'] ?: '#'.$r['id']).' amount does not match invoice total.','url'=>'../receipt-print.php?receipt_id='.(int)$r['id'],'type'=>'receipt_mismatch']; }
        }
    } catch (Throwable $e) {}
    try {
        if (mj292_has_table('mj_wallet_transactions') && mj292_has_table('mj_invoices')) {
            $rows = all_rows("SELECT wt.id,wt.invoice_id,wt.amount,i.status FROM mj_wallet_transactions wt LEFT JOIN mj_invoices i ON i.id=wt.invoice_id WHERE wt.direction='debit' AND wt.invoice_id IS NOT NULL AND (i.id IS NULL OR i.status NOT IN ('paid','approved')) ORDER BY wt.id DESC LIMIT 50");
            foreach ($rows as $r) { $checks[] = ['severity'=>'high','title'=>'Wallet debit without paid invoice','message'=>'Wallet debit #'.$r['id'].' is linked to an invoice that is not paid.','url'=>'report-wallets.php?q='.(int)$r['id'],'type'=>'wallet_invoice_mismatch']; }
        }
    } catch (Throwable $e) {}
    try {
        if (mj292_has_table('mj_payment_batches') && mj292_has_table('mj_invoices')) {
            $rows = all_rows("SELECT b.id,b.batch_no,b.invoice_id,b.status,i.status invoice_status FROM mj_payment_batches b LEFT JOIN mj_invoices i ON i.id=b.invoice_id WHERE b.status IN ('approved','paid','payment_verified') AND (i.id IS NULL OR i.status NOT IN ('paid','approved')) ORDER BY b.id DESC LIMIT 50");
            foreach ($rows as $r) { $checks[] = ['severity'=>'high','title'=>'Approved payment but invoice not paid','message'=>'Batch '.($r['batch_no'] ?: '#'.$r['id']).' is approved but invoice status is not paid.','url'=>'payment-center.php?tab=history','type'=>'batch_invoice_mismatch']; }
        }
    } catch (Throwable $e) {}
    return $checks;
}
}

if (!function_exists('mj292_activation_checks')) {
function mj292_activation_checks(bool $onlyOpen=false): array {
    $checks = [];
    try {
        if (mj292_has_table('mj_payment_batch_items')) {
            $rows = all_rows("SELECT id,batch_id,invoice_id,item_type,item_label,activation_status,status,listing_id,company_id FROM mj_payment_batch_items WHERE status IN ('approved','paid','payment_verified') AND COALESCE(activation_status,'') NOT IN ('activated','completed','not_required') ORDER BY id DESC LIMIT 80");
            foreach ($rows as $r) { $checks[] = ['severity'=>'warning','title'=>'Paid item not activated','message'=>ucwords(str_replace('_',' ',(string)$r['item_type'])).' item #'.$r['id'].' is paid but activation is '.($r['activation_status'] ?: 'pending').'.','url'=>'payment-center.php?tab=history','type'=>'item_not_activated']; }
        }
    } catch (Throwable $e) {}
    try {
        if (mj292_has_table('mj_listings') && mj292_has_col('mj_listings','is_sponsored') && mj292_has_col('mj_listings','sponsored_until')) {
            $rows = all_rows("SELECT id,title,sponsored_until FROM mj_listings WHERE is_sponsored=1 AND sponsored_until IS NOT NULL AND sponsored_until<NOW() ORDER BY sponsored_until ASC LIMIT 50");
            foreach ($rows as $r) { $checks[] = ['severity'=>'normal','title'=>'Expired sponsored listing still marked sponsored','message'=>'Listing #'.$r['id'].' sponsor expired on '.$r['sponsored_until'].'.','url'=>'listing-edit.php?id='.(int)$r['id'],'type'=>'sponsor_expired']; }
        }
    } catch (Throwable $e) {}
    return $checks;
}
}

if (!function_exists('mj292_report_configs')) {
function mj292_report_configs(): array {
    return [
        'users'=>['title'=>'User Report','icon'=>'fas fa-users','table'=>'mj_users','url'=>'users.php','search'=>['name','email','mobile'],'cols'=>['id'=>'ID','name'=>'Name','email'=>'Email','mobile'=>'Mobile','preferred_currency'=>'Currency','is_active'=>'Active','last_login_at'=>'Last Login','created_at'=>'Created']],
        'listings'=>['title'=>'Listing Report','icon'=>'fas fa-tags','table'=>'mj_listings','url'=>'listing-edit.php?id=','search'=>['title','description'],'cols'=>['id'=>'ID','title'=>'Title','price'=>'Price','currency_code'=>'Currency','status'=>'Status','is_sponsored'=>'Sponsored','is_featured'=>'Featured','views'=>'Views','created_at'=>'Created']],
        'companies'=>['title'=>'Company Report','icon'=>'fas fa-building','table'=>'mj_companies','url'=>'company-edit.php?id=','search'=>['name','email','phone','website'],'cols'=>['id'=>'ID','name'=>'Company','email'=>'Email','phone'=>'Phone','status'=>'Status','is_verified'=>'Verified','views'=>'Views','created_at'=>'Created']],
        'payments'=>['title'=>'Payment Report','icon'=>'fas fa-credit-card','table'=>'mj_payment_batches','url'=>'payment-center.php?tab=history','search'=>['batch_no','reference_no','payment_method'],'cols'=>['id'=>'ID','batch_no'=>'Batch','currency_code'=>'Currency','total_amount'=>'Amount','payment_method'=>'Method','reference_no'=>'Reference','status'=>'Status','reviewed_at'=>'Reviewed','created_at'=>'Created']],
        'invoices'=>['title'=>'Invoice Report','icon'=>'fas fa-file-invoice','table'=>'mj_invoices','url'=>'../invoice-print.php?invoice_id=','search'=>['invoice_no','title'],'cols'=>['id'=>'ID','invoice_no'=>'Invoice','title'=>'Title','currency_code'=>'Currency','total_amount'=>'Total','status'=>'Status','paid_at'=>'Paid','created_at'=>'Created']],
        'wallets'=>['title'=>'Wallet Report','icon'=>'fas fa-wallet','table'=>'mj_wallet_transactions','url'=>'payment-center.php?tab=wallet','search'=>['transaction_type','description','source_type'],'cols'=>['id'=>'ID','user_id'=>'User','currency_code'=>'Currency','transaction_type'=>'Type','direction'=>'Direction','amount'=>'Amount','balance_after'=>'Balance After','created_at'=>'Created']],
        'receipts'=>['title'=>'Receipt Report','icon'=>'fas fa-receipt','table'=>'mj_receipts','url'=>'../receipt-print.php?receipt_id=','search'=>['receipt_no','payment_method','reference_no'],'cols'=>['id'=>'ID','receipt_no'=>'Receipt','invoice_id'=>'Invoice','currency_code'=>'Currency','amount'=>'Amount','payment_method'=>'Method','status'=>'Status','created_at'=>'Created']],
        'expiry'=>['title'=>'Expiry Report','icon'=>'fas fa-hourglass-end','table'=>'mj_listings','url'=>'listing-edit.php?id=','search'=>['title'],'where'=>mj292_has_col('mj_listings','expires_at') ? 'expires_at IS NOT NULL' : '1=0','cols'=>['id'=>'ID','title'=>'Listing','status'=>'Status','expires_at'=>'Expiry','sponsored_until'=>'Sponsor Until','featured_until'=>'Featured Until','created_at'=>'Created']],
        'admin-activity'=>['title'=>'Admin Activity Report','icon'=>'fas fa-history','table'=>'mj_admin_logs','url'=>'audit-logs.php','search'=>['action','table_name'],'cols'=>['id'=>'ID','admin_id'=>'Admin','action'=>'Action','table_name'=>'Table','row_id'=>'Row','ip_address'=>'IP','created_at'=>'Created']],
        'reset-logs'=>['title'=>'Reset Logs','icon'=>'fas fa-undo','table'=>'mj_admin_reset_logs','url'=>'report-reset-logs.php','search'=>['reset_type','admin_email','selected_options'],'cols'=>['id'=>'ID','admin_name'=>'Admin','reset_type'=>'Reset Type','records_deleted'=>'Records','files_deleted'=>'Files','ip_address'=>'IP','created_at'=>'Created']],
        'leads'=>['title'=>'Lead Tracking Report','icon'=>'fas fa-mouse-pointer','table'=>'mj_lead_events','url'=>'report-leads.php','search'=>['event_type','event_source'],'cols'=>['id'=>'ID','event_type'=>'Event','listing_id'=>'Listing','company_id'=>'Company','user_id'=>'User','ip_address'=>'IP','created_at'=>'Created']],
        'support'=>['title'=>'Support Ticket Report','icon'=>'fas fa-headset','table'=>'mj_support_tickets','url'=>'support.php','search'=>['subject','ticket_type','message'],'cols'=>['id'=>'ID','subject'=>'Subject','ticket_type'=>'Type','priority'=>'Priority','status'=>'Status','user_id'=>'User','created_at'=>'Created']],
    ];
}
}

if (!function_exists('mj292_report_rows')) {
function mj292_report_rows(array $cfg, int $limit=100): array {
    $table = mj292_clean_ident((string)($cfg['table'] ?? '')); if ($table === '' || !mj292_has_table($table)) { return []; }
    $where = (string)($cfg['where'] ?? '1=1'); $params = [];
    $status = trim((string)($_GET['status'] ?? ''));
    if ($status !== '' && mj292_has_col($table,'status')) { $where .= ' AND status=?'; $params[] = $status; }
    $qterm = trim((string)($_GET['q'] ?? ''));
    if ($qterm !== '') {
        $ors = [];
        foreach ((array)($cfg['search'] ?? []) as $col) { $col = mj292_clean_ident($col); if ($col !== '' && mj292_has_col($table,$col)) { $ors[] = "LOWER(CAST(`{$col}` AS CHAR)) LIKE ?"; $params[] = '%'.mb_strtolower($qterm, 'UTF-8').'%'; } }
        if (ctype_digit($qterm) && mj292_has_col($table,'id')) { $ors[] = '`id`=?'; $params[] = (int)$qterm; }
        if ($ors) { $where .= ' AND ('.implode(' OR ', $ors).')'; }
    }
    $dateCol = mj292_date_col($table);
    $from = trim((string)($_GET['from'] ?? '')); $to = trim((string)($_GET['to'] ?? ''));
    if ($dateCol !== 'id' && $from !== '') { $where .= " AND DATE(`{$dateCol}`)>=?"; $params[] = $from; }
    if ($dateCol !== 'id' && $to !== '') { $where .= " AND DATE(`{$dateCol}`)<=?"; $params[] = $to; }
    $orderCol = mj292_has_col($table,'created_at') ? 'created_at' : (mj292_has_col($table,'id') ? 'id' : $dateCol);
    try { return all_rows("SELECT * FROM `{$table}` WHERE {$where} ORDER BY `{$orderCol}` DESC LIMIT ".max(10,min(500,$limit)), $params); } catch (Throwable $e) { return []; }
}
}

if (!function_exists('mj292_render_report_page')) {
function mj292_render_report_page(string $key): void {
    $configs = mj292_report_configs(); $cfg = $configs[$key] ?? null;
    if (!$cfg) { echo '<div class="alert alert-warning">Report not found.</div>'; return; }
    $rows = mj292_report_rows($cfg, 150);
    $table = (string)$cfg['table'];
    $cols = [];
    foreach ((array)$cfg['cols'] as $col=>$label) { if (mj292_has_col($table, (string)$col) || in_array($col, ['id'], true)) { $cols[$col] = $label; } }
    echo '<div class="card card-primary card-outline mb-3"><div class="card-body"><form method="get" class="row align-items-end">';
    echo '<div class="col-md-3 mb-2"><label>Search</label><input class="form-control" name="q" value="'.e($_GET['q'] ?? '').'" placeholder="Keyword / ID"></div>';
    echo '<div class="col-md-2 mb-2"><label>Status</label><input class="form-control" name="status" value="'.e($_GET['status'] ?? '').'" placeholder="Any"></div>';
    echo '<div class="col-md-2 mb-2"><label>From</label><input type="date" class="form-control" name="from" value="'.e($_GET['from'] ?? '').'"></div>';
    echo '<div class="col-md-2 mb-2"><label>To</label><input type="date" class="form-control" name="to" value="'.e($_GET['to'] ?? '').'"></div>';
    echo '<div class="col-md-3 mb-2"><button class="btn btn-primary"><i class="fas fa-search mr-1"></i>Filter</button> <a class="btn btn-outline-secondary" href="'.e(basename($_SERVER['SCRIPT_NAME'] ?? '')).'">Reset</a> <button type="button" class="btn btn-outline-info" onclick="window.print()"><i class="fas fa-print mr-1"></i>Print</button></div>';
    echo '</form></div></div>';
    echo '<div class="card"><div class="card-header d-flex align-items-center"><h3 class="card-title"><i class="'.e($cfg['icon']).' mr-2"></i>'.e($cfg['title']).'</h3><span class="badge badge-light ml-auto">'.e(count($rows)).' rows</span></div><div class="card-body p-0 table-responsive"><table class="table table-hover table-sm mb-0"><thead><tr>';
    foreach ($cols as $label) { echo '<th>'.e($label).'</th>'; }
    echo '<th>Action</th></tr></thead><tbody>';
    foreach ($rows as $r) {
        echo '<tr>';
        foreach ($cols as $col=>$label) {
            $val = $r[$col] ?? '';
            if ($col === 'status') { echo '<td>'.mj292_status_badge((string)$val).'</td>'; }
            elseif (in_array($col, ['amount','total_amount','tax_amount','balance_after','price'], true)) { echo '<td>'.mj292_money0((float)$val, (string)($r['currency_code'] ?? 'SGD')).'</td>'; }
            elseif (is_numeric($val) && in_array($col, ['is_active','is_featured','is_sponsored','is_verified'], true)) { echo '<td>'.((int)$val ? '<span class="badge badge-success">Yes</span>' : '<span class="badge badge-secondary">No</span>').'</td>'; }
            else { echo '<td>'.e(mb_strimwidth((string)$val,0,80,'...')).'</td>'; }
        }
        $url = (string)($cfg['url'] ?? '#');
        if (str_ends_with($url, '=')) { $url .= (int)($r['id'] ?? 0); }
        echo '<td><a class="btn btn-xs btn-outline-primary" href="'.e($url).'">View</a></td></tr>';
    }
    if (!$rows) { echo '<tr><td colspan="'.(count($cols)+1).'" class="text-center text-muted p-4">No records found for this filter.</td></tr>'; }
    echo '</tbody></table></div></div>';
}
}

if (!function_exists('mj292_invoice_data')) {
function mj292_invoice_data(int $invoiceId=0, int $batchId=0, int $receiptId=0): array {
    $invoice = null; $batch = null; $receipt = null; $items = [];
    try {
        if ($receiptId > 0 && mj292_has_table('mj_receipts')) { $receipt = one('SELECT * FROM mj_receipts WHERE id=? LIMIT 1', [$receiptId]); if ($receipt) { $invoiceId = (int)($receipt['invoice_id'] ?? $invoiceId); $batchId = (int)($receipt['batch_id'] ?? $batchId); } }
        if ($invoiceId > 0 && mj292_has_table('mj_invoices')) { $invoice = one('SELECT * FROM mj_invoices WHERE id=? LIMIT 1', [$invoiceId]); if ($invoice) { $batchId = (int)($invoice['batch_id'] ?? $batchId); } }
        if (!$invoice && $batchId > 0 && mj292_has_table('mj_invoices')) { $invoice = one('SELECT * FROM mj_invoices WHERE batch_id=? LIMIT 1', [$batchId]); if ($invoice) { $invoiceId = (int)$invoice['id']; } }
        if ($batchId > 0 && mj292_has_table('mj_payment_batches')) { $batch = one('SELECT * FROM mj_payment_batches WHERE id=? LIMIT 1', [$batchId]); if ($batch && !$invoice && !empty($batch['invoice_id'])) { $invoice = one('SELECT * FROM mj_invoices WHERE id=? LIMIT 1', [(int)$batch['invoice_id']]); $invoiceId=(int)($batch['invoice_id']); } }
        if (!$batch && $invoice && !empty($invoice['batch_id']) && mj292_has_table('mj_payment_batches')) { $batch = one('SELECT * FROM mj_payment_batches WHERE id=? LIMIT 1', [(int)$invoice['batch_id']]); }
        if (!$receipt && $invoiceId > 0 && mj292_has_table('mj_receipts')) { $receipt = one('SELECT * FROM mj_receipts WHERE invoice_id=? ORDER BY id DESC LIMIT 1', [$invoiceId]); }
        if ($invoiceId > 0 && mj292_has_table('mj_invoice_items')) { $items = all_rows('SELECT * FROM mj_invoice_items WHERE invoice_id=? ORDER BY id ASC', [$invoiceId]); }
        if (!$items && $batchId > 0 && mj292_has_table('mj_payment_batch_items')) { $items = all_rows('SELECT * FROM mj_payment_batch_items WHERE batch_id=? ORDER BY id ASC', [$batchId]); }
    } catch (Throwable $e) {}
    $user = null;
    $uid = (int)($invoice['user_id'] ?? $batch['user_id'] ?? $receipt['user_id'] ?? 0);
    if ($uid > 0 && mj292_has_table('mj_users')) { try { $user = one('SELECT * FROM mj_users WHERE id=? LIMIT 1', [$uid]); } catch (Throwable $e) {} }
    return ['invoice'=>$invoice,'batch'=>$batch,'receipt'=>$receipt,'items'=>$items,'user'=>$user,'user_id'=>$uid];
}
}

if (!function_exists('mj292_reset_preview')) {
function mj292_reset_map(): array {
    return [
        'users'=>['label'=>'Users & user data','tables'=>['mj_saved_items','mj_notifications','mj_wallet_transactions','mj_wallet_holds','mj_wallets','mj_support_tickets','mj_users'],'files'=>['assets/uploads/users']],
        'listings'=>['label'=>'Listings & listing files','tables'=>['mj_lead_events','mj_listing_logs','mj_listing_upgrade_requests','mj_listing_renewal_requests','mj_reports','mj_listings'],'files'=>['assets/uploads/listings']],
        'companies'=>['label'=>'Companies & company files','tables'=>['mj_company_staff','mj_company_branches','mj_company_claims','mj_companies'],'files'=>['assets/uploads/companies']],
        'payments'=>['label'=>'Payments, invoices, receipts','tables'=>['mj_payment_batch_items','mj_payment_batches','mj_invoice_items','mj_receipts','mj_payment_requests','mj_upgrade_payment_proofs','mj_payment_proofs','mj_order_payment_proofs','mj_invoices'],'files'=>['assets/uploads/payment-proof','assets/uploads/payment-batches']],
        'notifications'=>['label'=>'Notifications & alerts','tables'=>['mj_notifications','mj_admin_alerts'],'files'=>[]],
    ];
}
}

if (!function_exists('mj292_delete_folder_files')) {
function mj292_delete_folder_files(string $relative): int {
    $base = realpath(__DIR__.'/..'); $target = realpath(__DIR__.'/../'.trim($relative,'/'));
    if (!$base || !$target || strpos($target, $base) !== 0 || !is_dir($target)) { return 0; }
    $deleted = 0;
    $it = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($target, FilesystemIterator::SKIP_DOTS), RecursiveIteratorIterator::CHILD_FIRST);
    foreach ($it as $file) {
        $path = $file->getPathname();
        if ($file->isFile() && @unlink($path)) { $deleted++; }
        elseif ($file->isDir()) { @rmdir($path); }
    }
    return $deleted;
}
}

if (!function_exists('mj292_run_reset')) {
function mj292_run_reset(array $options, array $admin): array {
    $map = mj292_reset_map(); $tables = []; $files = [];
    if (in_array('all', $options, true)) { $options = array_keys($map); }
    foreach ($options as $opt) { if (isset($map[$opt])) { $tables = array_merge($tables, $map[$opt]['tables']); $files = array_merge($files, $map[$opt]['files']); } }
    $tables = array_values(array_unique($tables)); $files = array_values(array_unique($files));
    $deleted = 0; $fileDeleted = 0; $affected = [];
    try { db()->beginTransaction(); } catch (Throwable $e) {}
    foreach ($tables as $table) {
        if (!mj292_has_table($table)) { continue; }
        try {
            if ($table === 'mj_users') {
                $c = mj292_count($table); q('DELETE FROM mj_users');
            } else {
                $c = mj292_count($table); q('DELETE FROM `'.mj292_clean_ident($table).'`');
            }
            $deleted += $c; $affected[] = $table.'='.$c;
        } catch (Throwable $e) { $affected[] = $table.'=error'; }
    }
    try { db()->commit(); } catch (Throwable $e) { try { db()->rollBack(); } catch (Throwable $e2) {} }
    foreach ($files as $folder) { $fileDeleted += mj292_delete_folder_files($folder); }
    try {
        q('INSERT INTO mj_admin_reset_logs(admin_id,admin_name,admin_email,reset_type,selected_options,affected_tables,records_deleted,files_deleted,ip_address,user_agent,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,NOW())', [
            (int)($admin['id'] ?? 0), (string)($admin['name'] ?? ''), (string)($admin['email'] ?? ''), in_array('all',$options,true)?'all':'selected', implode(',', $options), implode("\n", $affected), $deleted, $fileDeleted, $_SERVER['REMOTE_ADDR'] ?? '', substr((string)($_SERVER['HTTP_USER_AGENT'] ?? ''),0,250)
        ]);
    } catch (Throwable $e) {}
    return ['records'=>$deleted,'files'=>$fileDeleted,'tables'=>$affected];
}
}

if (!function_exists('mj292_quick_cards')) {
function mj292_quick_cards(array $items): void {
    echo '<div class="row">';
    foreach ($items as $it) {
        echo '<div class="col-xl-3 col-md-6 mb-3"><a href="'.e($it[2]).'" class="small-box bg-'.e($it[4] ?? 'primary').'"><div class="inner"><h3>'.e((string)$it[3]).'</h3><p>'.e($it[0]).'</p><small>'.e($it[1]).'</small></div><div class="icon"><i class="'.e($it[5] ?? 'fas fa-link').'"></i></div><span class="small-box-footer">Open <i class="fas fa-arrow-circle-right"></i></span></a></div>';
    }
    echo '</div>';
}
}
