<?php
/**
 * MJ Marketplace v2.8.9 Admin Command Center helpers.
 * Safe additive helpers for dashboard, admin module center, finance snapshots,
 * approval queues and sidebar badges. All helpers tolerate missing old tables/columns.
 */
if (!defined('MJ_V289_ADMIN_HELPERS_LOADED')) { define('MJ_V289_ADMIN_HELPERS_LOADED', true); }

if (!function_exists('mj_v289_clean_ident')) {
function mj_v289_clean_ident(string $value): string { return preg_replace('/[^a-zA-Z0-9_]/', '', $value) ?: ''; }
}

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

if (!function_exists('mj_v289_sum')) {
function mj_v289_sum(string $table, string $column, string $where = '1=1', array $params = []): float {
    $safe = mj_v289_clean_ident($table); $col = mj_v289_clean_ident($column);
    if ($safe === '' || $col === '' || !table_exists($safe) || !mj_has_col($safe, $col)) { return 0.0; }
    try { $row = one("SELECT COALESCE(SUM(`{$col}`),0) s FROM `{$safe}` WHERE {$where}", $params); return (float)($row['s'] ?? 0); }
    catch (Throwable $e) { return 0.0; }
}
}

if (!function_exists('mj_v289_latest')) {
function mj_v289_latest(string $table, array $columns = ['*'], string $where = '1=1', int $limit = 8): array {
    $safe = mj_v289_clean_ident($table);
    if ($safe === '' || !table_exists($safe)) { return []; }
    $cols = [];
    foreach ($columns as $c) {
        if ($c === '*') { $cols[] = '*'; continue; }
        $clean = mj_v289_clean_ident($c);
        if ($clean !== '' && mj_has_col($safe, $clean)) { $cols[] = "`{$clean}`"; }
    }
    if (!$cols) { $cols = ['*']; }
    $order = mj_has_col($safe, 'created_at') ? 'created_at DESC' : (mj_has_col($safe, 'id') ? 'id DESC' : '1');
    try { return all_rows('SELECT '.implode(',', $cols)." FROM `{$safe}` WHERE {$where} ORDER BY {$order} LIMIT ".max(1,min(50,$limit))); }
    catch (Throwable $e) { return []; }
}
}

if (!function_exists('mj_v289_money_total')) {
function mj_v289_money_total(): float {
    if (function_exists('mj293_finance_metrics')) { $m = mj293_finance_metrics(); return (float)($m['paid_amount'] ?? 0); }
    $total = 0.0;
    $total += mj_v289_sum('mj_invoices','total_amount',"status IN ('paid','approved','completed')");
    $total += mj_v289_sum('mj_payment_batches','total_amount',"status IN ('approved','partial_approved','paid','completed')");
    $total += mj_v289_sum('mj_upgrade_payment_proofs','amount',"status IN ('approved','paid','completed')");
    $total += mj_v289_sum('mj_payment_proofs','amount',"status IN ('approved','paid','completed')");
    return $total;
}
}

if (!function_exists('mj_v289_pending_payment_count')) {
function mj_v289_pending_payment_count(): int {
    if (function_exists('mj293_finance_metrics')) { $m = mj293_finance_metrics(); return (int)(($m['pending_review_count'] ?? 0) + ($m['awaiting_user_payment_count'] ?? 0) + ($m['need_more_info_count'] ?? 0)); }
    $pending = "(status IS NULL OR status='' OR status IN ('pending','submitted','pending_review','review','unpaid','pending_payment'))";
    return mj_v289_count('mj_upgrade_payment_proofs', $pending)
        + mj_v289_count('mj_payment_proofs', $pending)
        + mj_v289_count('mj_payment_requests', $pending)
        + mj_v289_count('mj_payment_batches', $pending)
        + mj_v289_count('mj_invoices', $pending);
}
}

if (!function_exists('mj_v289_admin_dashboard_metrics')) {
function mj_v289_admin_dashboard_metrics(): array {
    $pendingStatuses = "status IN ('pending','submitted','pending_review','review')";
    $activeStatuses = "(status IS NULL OR status='' OR status IN ('active','approved','published'))";
    return [
        'users_total' => mj_v289_count('mj_users'),
        'users_today' => mj_v289_count('mj_users', mj_has_col('mj_users','created_at') ? 'DATE(created_at)=CURDATE()' : '1=0'),
        'listings_total' => mj_v289_count('mj_listings'),
        'listings_active' => mj_v289_count('mj_listings', $activeStatuses),
        'listings_pending' => mj_v289_count('mj_listings', $pendingStatuses),
        'listings_expired' => mj_v289_count('mj_listings', mj_has_col('mj_listings','expires_at') ? "expires_at IS NOT NULL AND expires_at<NOW()" : "status='expired'"),
        'companies_total' => mj_v289_count('mj_companies'),
        'companies_pending' => mj_v289_count('mj_companies', $pendingStatuses),
        'countries_active' => mj_v289_count('mj_countries', mj_has_col('mj_countries','is_active') ? 'is_active=1' : '1=1'),
        'payments_pending' => mj_v289_pending_payment_count(),
        'invoices_unpaid' => mj_v289_count('mj_invoices', "status IN ('unpaid','pending','draft') OR status IS NULL"),
        'support_open' => mj_v289_count('mj_support_tickets', "status IN ('open','pending','new') OR status IS NULL"),
        'reports_new' => mj_v289_count('mj_reports', "status IN ('new','open','pending') OR status IS NULL") + mj_v289_count('mj_listing_reports', "status IN ('new','open','pending') OR status IS NULL"),
        'verification_pending' => mj_v289_count('mj_verification_requests', $pendingStatuses),
        'bookings_pending' => mj_v289_count('mj_appointments', "status IN ('pending','submitted','pending_review')") + mj_v289_count('mj_bookings', "status IN ('pending','submitted','pending_review')"),
        'reviews_pending' => mj_v289_count('mj_reviews', $pendingStatuses),
        'revenue_total' => mj_v289_money_total(),
        'alerts_unread' => function_exists('mj_admin_unread_alert_count') ? mj_admin_unread_alert_count() : 0,
        'errors_open' => mj_v289_count('mj_error_logs', "status IS NULL OR status NOT IN ('resolved','ignored')"),
    ];
}
}

if (!function_exists('mj_v289_admin_health_score')) {
function mj_v289_admin_health_score(array $metrics = []): int {
    if (!$metrics) { $metrics = mj_v289_admin_dashboard_metrics(); }
    $score = 100;
    if (($metrics['errors_open'] ?? 0) > 0) { $score -= min(25, (int)$metrics['errors_open'] * 2); }
    if (($metrics['payments_pending'] ?? 0) > 0) { $score -= min(15, (int)$metrics['payments_pending']); }
    if (($metrics['support_open'] ?? 0) > 0) { $score -= min(15, (int)$metrics['support_open']); }
    if (($metrics['reports_new'] ?? 0) > 0) { $score -= min(20, (int)$metrics['reports_new'] * 2); }
    if (($metrics['listings_pending'] ?? 0) > 10) { $score -= 10; }
    return max(0, min(100, $score));
}
}

if (!function_exists('mj_v289_approval_cards')) {
function mj_v289_approval_cards(): array {
    $pendingStatuses = "status IN ('pending','submitted','pending_review','review')";
    return [
        ['label'=>'Listings','count'=>mj_v289_count('mj_listings',$pendingStatuses),'url'=>'listings.php?status=pending','icon'=>'fas fa-tags','tone'=>'primary'],
        ['label'=>'Companies','count'=>mj_v289_count('mj_companies',$pendingStatuses),'url'=>'companies.php?status=pending','icon'=>'fas fa-building','tone'=>'info'],
        ['label'=>'Payment Proofs','count'=>mj_v289_pending_payment_count(),'url'=>'payment-center.php?tab=review','icon'=>'fas fa-receipt','tone'=>'success'],
        ['label'=>'Verifications','count'=>mj_v289_count('mj_verification_requests',$pendingStatuses),'url'=>'approval-center.php','icon'=>'fas fa-shield-alt','tone'=>'warning'],
        ['label'=>'Bookings','count'=>mj_v289_count('mj_appointments',"status='pending'") + mj_v289_count('mj_bookings',"status='pending'"),'url'=>'bookings.php?status=pending','icon'=>'fas fa-calendar-check','tone'=>'purple'],
        ['label'=>'Support','count'=>mj_v289_count('mj_support_tickets',"status IN ('open','pending','new') OR status IS NULL"),'url'=>'support.php','icon'=>'fas fa-headset','tone'=>'danger'],
    ];
}
}

if (!function_exists('mj_v289_admin_module_groups')) {
function mj_v289_admin_module_groups(): array {
    return [
        'marketplace'=>['title'=>'Marketplace Center','icon'=>'fas fa-store','color'=>'#2563eb','desc'=>'Listings, renewals, sponsored upgrades, reports and listing rules.','links'=>[
            ['Listings','listings.php','fas fa-tags'],['Listing Edits','listing-revisions.php','fas fa-code-branch'],['Renewals','listing-renewals.php','fas fa-redo'],['Upgrades','listing-upgrade-requests.php','fas fa-star'],['Expired','expired-listings.php','fas fa-hourglass-end'],['Reports','reported-listings.php','fas fa-flag']
        ]],
        'company'=>['title'=>'Company Center','icon'=>'fas fa-building','color'=>'#0ea5e9','desc'=>'Companies, page builder, claims, branches, staff, seed/import and analytics.','links'=>[
            ['Companies','companies.php','fas fa-building'],['Claims','company-claims.php','fas fa-handshake'],['Branches','company-branches.php','fas fa-map-marker-alt'],['Staff','company-staff.php','fas fa-user-tie'],['Business Seed','company-seed.php','fas fa-database'],['Company Analytics','company-analytics.php','fas fa-chart-pie']
        ]],
        'users'=>['title'=>'Users & CRM Center','icon'=>'fas fa-users','color'=>'#7c3aed','desc'=>'Users, customers, access controls, CRM and activity timeline.','links'=>[
            ['Users','users.php','fas fa-users'],['Customers / CRM','customers.php','fas fa-address-book'],['User Limits','user-limits.php','fas fa-user-lock'],['Activity','user-activity.php','fas fa-history'],['Broadcast','broadcast.php','fas fa-bullhorn'],['Support','support.php','fas fa-headset']
        ]],
        'billing'=>['title'=>'Billing Center','icon'=>'fas fa-money-check-alt','color'=>'#059669','desc'=>'Payments, proofs, invoices, wallet, finance reports and payment settings.','links'=>[
            ['Payment Center','payment-center.php','fas fa-credit-card'],['Review Payments','payment-center.php?tab=review','fas fa-receipt'],['Wallet / Credits','payment-center.php?tab=wallet','fas fa-wallet'],['Finance Summary','finance-summary.php','fas fa-chart-line'],['Invoices','payment-center.php?tab=invoices','fas fa-file-invoice'],['Payment Methods','upgrade-payment-settings.php','fas fa-money-check-alt']
        ]],
        'trust'=>['title'=>'Trust & Safety Center','icon'=>'fas fa-shield-alt','color'=>'#dc2626','desc'=>'Verification, reviews, reports, scam checks, fraud and errors.','links'=>[
            ['Reviews','reviews.php','fas fa-star'],['Reports / Scam','reports.php','fas fa-shield-alt'],['Fraud Dashboard','fraud-dashboard.php','fas fa-user-secret'],['Verification','approval-center.php','fas fa-user-check'],['Error Centre','error-center.php','fas fa-bug'],['Audit Logs','audit-logs.php','fas fa-history']
        ]],
        'deals'=>['title'=>'Deals / Services Center','icon'=>'fas fa-fire','color'=>'#f97316','desc'=>'Deals, promotions, coupons, services, RFQ and bookings.','links'=>[
            ['Deals','deals.php','fas fa-fire'],['Promotions','promotions.php','fas fa-percent'],['Coupons','coupons.php','fas fa-ticket-alt'],['Services','services.php','fas fa-concierge-bell'],['RFQ / Quotes','quote-requests.php','fas fa-file-invoice-dollar'],['Bookings','bookings.php','fas fa-calendar-check']
        ]],
        'seo'=>['title'=>'Content / SEO Center','icon'=>'fas fa-search','color'=>'#0f766e','desc'=>'CMS, SEO, sitemap, redirects, social login and frontend health.','links'=>[
            ['CMS Pages','content.php','fas fa-file-alt'],['SEO Center','seo.php','fas fa-search'],['SEO Pages','seo-pages.php','fas fa-heading'],['Sitemap','sitemap-manager.php','fas fa-sitemap'],['Redirects','redirects.php','fas fa-route'],['Social Login','social-login.php','fas fa-sign-in-alt']
        ]],
        'automation'=>['title'=>'Automation / System Center','icon'=>'fas fa-robot','color'=>'#334155','desc'=>'Modules, notifications, cron, health, link checker and system setup.','links'=>[
            ['Modules','modules.php','fas fa-puzzle-piece'],['Notifications','notifications.php','fas fa-bell'],['Automation','automation-center.php','fas fa-robot'],['Link Health','link-health.php','fas fa-link'],['System Health','system-health.php','fas fa-heartbeat'],['Version','version.php','fas fa-code-branch']
        ]],
    ];
}
}

if (!function_exists('mj_v289_recent_activity')) {
function mj_v289_recent_activity(int $limit = 12): array {
    $items = [];
    $add = function(string $type, string $title, string $status, string $date, string $url = '', string $icon = 'fas fa-circle') use (&$items) {
        if ($date === '') { $date = date('Y-m-d H:i:s'); }
        $items[] = compact('type','title','status','date','url','icon');
    };
    try { if (table_exists('mj_listings')) foreach (all_rows("SELECT id,title,status,created_at FROM mj_listings ORDER BY id DESC LIMIT 8") as $r) { $add('listing','Listing: '.($r['title'] ?? 'Untitled'),$r['status'] ?? '',$r['created_at'] ?? '', 'listing-edit.php?id='.(int)$r['id'], 'fas fa-tags'); } } catch(Throwable $e) {}
    try { if (table_exists('mj_companies')) foreach (all_rows("SELECT id,name,status,created_at FROM mj_companies ORDER BY id DESC LIMIT 8") as $r) { $add('company','Company: '.($r['name'] ?? 'Company'),$r['status'] ?? '',$r['created_at'] ?? '', 'company-edit.php?id='.(int)$r['id'], 'fas fa-building'); } } catch(Throwable $e) {}
    try { if (table_exists('mj_upgrade_payment_proofs')) foreach (all_rows("SELECT id,user_id,status,amount,currency_code,created_at FROM mj_upgrade_payment_proofs ORDER BY id DESC LIMIT 8") as $r) { $add('payment','Payment proof #'.(int)$r['id'].' '.money($r['amount'] ?? 0, $r['currency_code'] ?? 'SGD'),$r['status'] ?? '',$r['created_at'] ?? '', 'payment-center.php?tab=review', 'fas fa-receipt'); } } catch(Throwable $e) {}
    try { if (table_exists('mj_support_tickets')) foreach (all_rows("SELECT id,subject,status,created_at FROM mj_support_tickets ORDER BY id DESC LIMIT 8") as $r) { $add('support','Ticket: '.($r['subject'] ?? ('#'.(int)$r['id'])),$r['status'] ?? '',$r['created_at'] ?? '', 'support.php', 'fas fa-headset'); } } catch(Throwable $e) {}
    try { if (table_exists('mj_admin_logs')) foreach (all_rows("SELECT id,action,table_name,row_id,created_at FROM mj_admin_logs ORDER BY id DESC LIMIT 8") as $r) { $add('admin','Admin: '.($r['action'] ?? 'action').' '.($r['table_name'] ?? ''),'',$r['created_at'] ?? '', 'audit-logs.php', 'fas fa-user-shield'); } } catch(Throwable $e) {}
    usort($items, fn($a,$b) => strcmp((string)$b['date'], (string)$a['date']));
    return array_slice($items, 0, max(1, min(50, $limit)));
}
}

if (!function_exists('mj_v289_country_breakdown')) {
function mj_v289_country_breakdown(int $limit = 10): array {
    if (!table_exists('mj_countries')) { return []; }
    try {
        $listCol = table_exists('mj_listings') && mj_has_col('mj_listings','country_id') ? '(SELECT COUNT(*) FROM mj_listings l WHERE l.country_id=c.id)' : '0';
        $compCol = table_exists('mj_companies') && mj_has_col('mj_companies','country_id') ? '(SELECT COUNT(*) FROM mj_companies co WHERE co.country_id=c.id)' : '0';
        return all_rows("SELECT c.id,c.name,c.flag_emoji,c.currency_code, {$listCol} AS listings_count, {$compCol} AS companies_count FROM mj_countries c WHERE ".(mj_has_col('mj_countries','is_active')?'c.is_active=1':'1=1')." ORDER BY listings_count DESC, companies_count DESC, c.name ASC LIMIT ".max(1,min(50,$limit)));
    } catch(Throwable $e) { return []; }
}
}

if (!function_exists('mj_v289_sidebar_badge')) {
function mj_v289_sidebar_badge(string $key): string {
    static $metrics = null;
    if ($metrics === null) { $metrics = mj_v289_admin_dashboard_metrics(); }
    $map = [
        'approval' => (int)(($metrics['listings_pending'] ?? 0)+($metrics['companies_pending'] ?? 0)+($metrics['payments_pending'] ?? 0)+($metrics['verification_pending'] ?? 0)+($metrics['support_open'] ?? 0)),
        'payments' => (int)($metrics['payments_pending'] ?? 0),
        'support' => (int)($metrics['support_open'] ?? 0),
        'reports' => (int)($metrics['reports_new'] ?? 0),
        'errors' => (int)($metrics['errors_open'] ?? 0),
        'bookings' => (int)($metrics['bookings_pending'] ?? 0),
        'alerts' => (int)($metrics['alerts_unread'] ?? 0),
    ];
    $n = $map[$key] ?? 0;
    return $n > 0 ? ($n > 99 ? '99+' : (string)$n) : '';
}
}

if (!function_exists('mj_v289_render_status_badge')) {
function mj_v289_render_status_badge(?string $status): string {
    if (function_exists('mj_v288_status_badge')) { return mj_v288_status_badge($status); }
    return '<span class="badge badge-secondary">'.e((string)$status).'</span>';
}
}

if (!function_exists('mj_v289_ensure_schema')) {
function mj_v289_ensure_schema(): void {
    if (!mj_is_installed()) { return; }
    if (function_exists('mj_v288_ensure_schema')) { try { mj_v288_ensure_schema(); } catch(Throwable $e) {} }
    if (table_exists('mj_site_modules')) {
        foreach ([
            'module_group' => "VARCHAR(80) NULL",
            'sort_order' => "INT NOT NULL DEFAULT 0",
            'admin_icon' => "VARCHAR(80) NULL",
            'is_core' => "TINYINT(1) NOT NULL DEFAULT 0"
        ] as $col=>$def) { try { if (!mj_has_col('mj_site_modules',$col)) { q('ALTER TABLE mj_site_modules ADD COLUMN `'.$col.'` '.$def); } } catch(Throwable $e) {} }
        try {
            $defs = [
                ['listings','Marketplace','Listings','fas fa-tags',10],['companies','Company','Companies','fas fa-building',20],['crm','Users & CRM','CRM / Leads','fas fa-address-book',30],['billing','Billing','Billing & Payments','fas fa-money-check-alt',40],['wallet','Billing','Wallet / Credits','fas fa-wallet',50],['verification','Trust & Safety','Trust & Verification','fas fa-shield-alt',60],['support','Users & CRM','Help & Support','fas fa-headset',70],['bookings','Deals / Services','Appointments / Bookings','fas fa-calendar-check',80],['deals','Deals / Services','Deals','fas fa-fire',90],['services','Deals / Services','Services','fas fa-concierge-bell',100],['seo','Content / SEO','SEO','fas fa-search',110],['notifications','Automation','Notifications','fas fa-bell',120],['ai_helper','Automation','AI Helper','fas fa-magic',130]
            ];
            foreach ($defs as $d) {
                q('INSERT INTO mj_site_modules(module_code,module_group,module_name,admin_icon,sort_order,is_enabled,is_core,description) VALUES(?,?,?,?,?,1,0,?) ON DUPLICATE KEY UPDATE module_group=COALESCE(NULLIF(module_group,\'\'),VALUES(module_group)), admin_icon=COALESCE(NULLIF(admin_icon,\'\'),VALUES(admin_icon)), sort_order=IF(sort_order=0,VALUES(sort_order),sort_order)', [$d[0],$d[1],$d[2],$d[3],$d[4],$d[2].' module']);
            }
        } catch(Throwable $e) {}
    }
    try { q("CREATE TABLE IF NOT EXISTS mj_admin_saved_filters (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        admin_id BIGINT NULL,
        filter_name VARCHAR(190) NOT NULL,
        page_key VARCHAR(120) NOT NULL,
        filter_json TEXT NULL,
        is_default TINYINT(1) NOT NULL DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX(admin_id), INDEX(page_key)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"); } catch(Throwable $e) {}
    try { q("CREATE TABLE IF NOT EXISTS mj_admin_dashboard_widgets (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        widget_key VARCHAR(120) NOT NULL UNIQUE,
        widget_title VARCHAR(190) NOT NULL,
        widget_group VARCHAR(80) NULL,
        is_enabled TINYINT(1) NOT NULL DEFAULT 1,
        sort_order INT NOT NULL DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"); } catch(Throwable $e) {}
    try { q("CREATE TABLE IF NOT EXISTS mj_admin_task_notes (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        admin_id BIGINT NULL,
        task_title VARCHAR(190) NOT NULL,
        task_status VARCHAR(50) NOT NULL DEFAULT 'open',
        due_at DATETIME NULL,
        note TEXT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX(admin_id), INDEX(task_status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"); } catch(Throwable $e) {}
}
}

if (!function_exists('mj_v289_module_rows_grouped')) {
function mj_v289_module_rows_grouped(): array {
    mj_v289_ensure_schema();
    $groups = [];
    if (!table_exists('mj_site_modules')) { return $groups; }
    try {
        $select = '*';
        $order = (mj_has_col('mj_site_modules','module_group') ? 'module_group,' : '').(mj_has_col('mj_site_modules','sort_order') ? 'sort_order,' : '').'module_name';
        $rows = all_rows("SELECT {$select} FROM mj_site_modules ORDER BY {$order}");
        foreach ($rows as $r) {
            $g = (string)($r['module_group'] ?? 'Other');
            if ($g === '') { $g = 'Other'; }
            $groups[$g][] = $r;
        }
    } catch(Throwable $e) {}
    return $groups;
}
}
