<?php
require_once __DIR__.'/../includes/functions.php';
$admin=require_admin();
$adminTitle='Frontend Data Check';
$country=(int)($_GET['country_id']??0);

function mj_dbg_rows($sql,$params=[]){ try { return all_rows($sql,$params); } catch(Throwable $e){ return [['ERROR'=>$e->getMessage()]]; } }
function mj_dbg_count($table){ try { $r=one("SELECT COUNT(*) c FROM `".preg_replace('/[^a-zA-Z0-9_]/','',$table)."`"); return (int)($r['c']??0); } catch(Throwable $e){ return 'ERR: '.$e->getMessage(); } }
function mj_dbg_db(){ try { $r=one('SELECT DATABASE() db'); return $r['db'] ?? ''; } catch(Throwable $e){ return 'ERR: '.$e->getMessage(); } }

if($_SERVER['REQUEST_METHOD']==='POST'){
    verify_csrf();
    if(($_POST['action']??'')==='activate_all'){
        try { q("UPDATE mj_listings SET status='active' WHERE status IS NULL OR status='' OR status IN ('draft','pending','published','approved','expired')"); } catch(Throwable $e) { flash('error','Listing activate error: '.$e->getMessage()); }
        try { q("UPDATE mj_companies SET status='active' WHERE status IS NULL OR status='' OR status IN ('draft','pending','published','approved')"); } catch(Throwable $e) { flash('error','Company activate error: '.$e->getMessage()); }
        flash('success','Listings and companies were set to active where possible.');
        redirect('frontend-data-check.php'.($country?'?country_id='.$country:''));
    }
}

$countries=mj_dbg_rows('SELECT id,name,flag_emoji FROM mj_countries ORDER BY name LIMIT 300');
$whereL=$country?'WHERE country_id='.(int)$country:'';
$whereC=$country?'WHERE country_id='.(int)$country:'';
$listings=mj_dbg_rows('SELECT * FROM mj_listings '.$whereL.' ORDER BY id DESC LIMIT 50');
$companies=mj_dbg_rows('SELECT * FROM mj_companies '.$whereC.' ORDER BY id DESC LIMIT 50');
$listingStats=mj_dbg_rows('SELECT COALESCE(status,"EMPTY") status, COUNT(*) c FROM mj_listings '.$whereL.' GROUP BY status ORDER BY c DESC');
$companyStats=mj_dbg_rows('SELECT COALESCE(status,"EMPTY") status, COUNT(*) c FROM mj_companies '.$whereC.' GROUP BY status ORDER BY c DESC');
$publicListings=mj_public_fetch_listings(['country_id'=>$country], 50);
$publicCompanies=mj_public_fetch_companies(['country_id'=>$country], 50);

include __DIR__.'/_header.php';
?>
<div class="card card-primary card-outline"><div class="card-header"><h3 class="card-title"><i class="fas fa-eye mr-2"></i>Frontend Data Check v2.5</h3></div><div class="card-body">
<?php show_flash(); ?>
<div class="alert alert-info mb-3"><b>Current DB:</b> <?=e(mj_dbg_db())?> | <b>mj_listings:</b> <?=e(mj_dbg_count('mj_listings'))?> | <b>mj_companies:</b> <?=e(mj_dbg_count('mj_companies'))?> | <b>Selected country:</b> <?=e($country?:'All')?></div>
<div class="row mb-3"><div class="col-md-6"><form class="form-row"><div class="col-md-8"><select name="country_id" class="form-control"><option value="0">All countries</option><?php foreach($countries as $c): if(isset($c['ERROR'])) continue; ?><option value="<?=e($c['id'])?>" <?=$country===(int)$c['id']?'selected':''?>><?=e(($c['flag_emoji']??'').' '.$c['name'].' #'.$c['id'])?></option><?php endforeach; ?></select></div><div class="col-md-4"><button class="btn btn-info btn-block">Check</button></div></form></div><div class="col-md-6 text-right"><form method="post" style="display:inline"><?=csrf_input()?><input type="hidden" name="action" value="activate_all"><button class="btn btn-success" onclick="return confirm('Set all visible records to active?')">Make pending/draft active</button></form></div></div>
<div class="row"><div class="col-md-6"><h5>Listing status</h5><table class="table table-sm table-bordered"><tr><th>Status</th><th>Count</th></tr><?php foreach($listingStats as $s): ?><tr><td><?=e($s['status'] ?? ($s['ERROR'] ?? ''))?></td><td><?=e($s['c'] ?? '')?></td></tr><?php endforeach; ?></table></div><div class="col-md-6"><h5>Company status</h5><table class="table table-sm table-bordered"><tr><th>Status</th><th>Count</th></tr><?php foreach($companyStats as $s): ?><tr><td><?=e($s['status'] ?? ($s['ERROR'] ?? ''))?></td><td><?=e($s['c'] ?? '')?></td></tr><?php endforeach; ?></table></div></div>
<h5 class="mt-4">Latest listings direct SQL</h5><div class="table-responsive"><table class="table table-sm table-striped"><tr><th>ID</th><th>Title</th><th>Status</th><th>Country</th><th>State</th><th>City</th><th>Category</th></tr><?php foreach($listings as $r): ?><tr><td><?=e($r['id']??'')?></td><td><?=e($r['title']??($r['ERROR']??''))?></td><td><?=e($r['status']??'')?></td><td><?=e($r['country_id']??'')?></td><td><?=e($r['state_id']??'')?></td><td><?=e($r['city_id']??'')?></td><td><?=e($r['category_id']??'')?></td></tr><?php endforeach; ?></table></div>
<h5 class="mt-4">Latest companies direct SQL</h5><div class="table-responsive"><table class="table table-sm table-striped"><tr><th>ID</th><th>Name</th><th>Status</th><th>Verified</th><th>Country</th><th>State</th><th>City</th><th>Category</th></tr><?php foreach($companies as $r): ?><tr><td><?=e($r['id']??'')?></td><td><?=e($r['name']??($r['ERROR']??''))?></td><td><?=e($r['status']??'')?></td><td><?=e($r['is_verified']??'')?></td><td><?=e($r['country_id']??'')?></td><td><?=e($r['state_id']??'')?></td><td><?=e($r['city_id']??'')?></td><td><?=e($r['category_id']??'')?></td></tr><?php endforeach; ?></table></div>
<div class="row mt-4"><div class="col-md-6"><div class="alert alert-secondary"><b>Public fetch listings:</b> <?=count($publicListings)?></div></div><div class="col-md-6"><div class="alert alert-secondary"><b>Public fetch companies:</b> <?=count($publicCompanies)?></div></div></div>
<div class="alert alert-info">Public test links: <a target="_blank" href="../browse.php<?= $country?'?country_id='.$country:'' ?>">Browse</a> | <a target="_blank" href="../companies.php<?= $country?'?country_id='.$country:'' ?>">Companies</a> | <a target="_blank" href="../singapore/">Singapore</a></div>
</div></div>
<?php include __DIR__.'/_footer.php'; ?>
