<?php $PAGE_TITLE='Reports'; require __DIR__.'/_layout.php';

$q = function($sql) use ($pdo){ return $pdo->query($sql)->fetch(); };

$total_recharge     = $q("SELECT COALESCE(SUM(amount),0) c FROM recharges WHERE status='approved'")['c'];
$pending_recharge   = $q("SELECT COALESCE(SUM(amount),0) c FROM recharges WHERE status='pending'")['c'];
$rejected_recharge  = $q("SELECT COALESCE(SUM(amount),0) c FROM recharges WHERE status='rejected'")['c'];
$recharge_count     = $q("SELECT COUNT(*) c FROM recharges WHERE status='approved'")['c'];

$total_balance      = $q("SELECT COALESCE(SUM(balance),0) c FROM users WHERE role='user'")['c'];
$users_total        = $q("SELECT COUNT(*) c FROM users WHERE role='user'")['c'];
$users_with_balance = $q("SELECT COUNT(*) c FROM users WHERE role='user' AND balance>0")['c'];

$orders_total       = $q("SELECT COUNT(*) c FROM orders")['c'];
$orders_success     = $q("SELECT COUNT(*) c FROM orders WHERE status IN ('completed','success')")['c'];
$orders_pending     = $q("SELECT COUNT(*) c FROM orders WHERE status='pending'")['c'];
$orders_failed      = $q("SELECT COUNT(*) c FROM orders WHERE status IN ('failed','rejected')")['c'];
$today_orders       = $q("SELECT COUNT(*) c FROM orders WHERE DATE(created_at)=CURDATE()")['c'];
$month_orders       = $q("SELECT COUNT(*) c FROM orders WHERE YEAR(created_at)=YEAR(CURDATE()) AND MONTH(created_at)=MONTH(CURDATE())")['c'];

$revenue_total      = $q("SELECT COALESCE(SUM(price),0) c FROM orders WHERE status IN ('completed','success')")['c'];
$revenue_today      = $q("SELECT COALESCE(SUM(price),0) c FROM orders WHERE status IN ('completed','success') AND DATE(created_at)=CURDATE()")['c'];
$revenue_month      = $q("SELECT COALESCE(SUM(price),0) c FROM orders WHERE status IN ('completed','success') AND YEAR(created_at)=YEAR(CURDATE()) AND MONTH(created_at)=MONTH(CURDATE())")['c'];

$top_users = $pdo->query("SELECT u.name,u.email,u.balance,u.total_recharge
                          FROM users u WHERE u.role='user'
                          ORDER BY u.total_recharge DESC LIMIT 5")->fetchAll();

$trend = $pdo->query("SELECT DATE(created_at) d, COALESCE(SUM(amount),0) s
                     FROM recharges WHERE status='approved'
                       AND created_at >= (CURDATE() - INTERVAL 6 DAY)
                     GROUP BY DATE(created_at) ORDER BY d ASC")->fetchAll();

// Helper: wrap a stat card as a clickable link
function stat_link($href, $label, $value, $sub=''){
  $h = htmlspecialchars($href, ENT_QUOTES);
  $l = htmlspecialchars($label, ENT_QUOTES);
  $v = $value;
  $s = $sub ? '<div class="sub">'.htmlspecialchars($sub, ENT_QUOTES).'</div>' : '';
  return '<a class="stat stat-link" href="'.$h.'" style="text-decoration:none;color:inherit;display:block"><div class="label">'.$l.'</div><div class="value">'.$v.'</div>'.$s.'</a>';
}
?>
<style>
.stat-link{transition:transform .15s ease, box-shadow .15s ease;cursor:pointer}
.stat-link:hover{transform:translateY(-2px);box-shadow:0 14px 28px rgba(15,23,42,.12)}
.stat-link:after{content:'বিস্তারিত →';position:absolute;bottom:8px;right:14px;font-size:11px;color:#6366f1;font-weight:600;opacity:.8}
</style>
<h2 style="margin-bottom:14px">📈 রিপোর্ট</h2>

<h3 style="margin:6px 0 10px;color:#334155">💰 রিচার্জ সংক্রান্ত</h3>
<div class="grid g2">
  <?=stat_link('reports_detail.php?type=recharges&filter=approved', 'মোট রিচার্জ (Approved)', money($total_recharge), $recharge_count.' টি রিকোয়েস্ট')?>
  <?=stat_link('reports_detail.php?type=recharges&filter=pending',  'Pending রিচার্জ',        money($pending_recharge))?>
  <?=stat_link('reports_detail.php?type=recharges&filter=rejected', 'Rejected রিচার্জ',       money($rejected_recharge))?>
  <?=stat_link('reports_detail.php?type=user_balance',              'মোট ইউজার ব্যালেন্স',   money($total_balance), $users_with_balance.'/'.$users_total.' জনের ব্যালেন্স আছে')?>
</div>

<h3 style="margin:20px 0 10px;color:#334155">📦 অর্ডার সংক্রান্ত</h3>
<div class="grid g2">
  <?=stat_link('reports_detail.php?type=orders&filter=all',        'মোট অর্ডার',      $orders_total)?>
  <?=stat_link('reports_detail.php?type=orders&filter=successful', 'Success অর্ডার',  $orders_success)?>
  <?=stat_link('reports_detail.php?type=orders&filter=pending',    'Pending অর্ডার',  $orders_pending)?>
  <?=stat_link('reports_detail.php?type=orders&filter=failed',     'Failed / Rejected', $orders_failed)?>
  <?=stat_link('reports_detail.php?type=orders&filter=today',      'আজকের অর্ডার',    $today_orders)?>
  <?=stat_link('reports_detail.php?type=orders&filter=month',      'এই মাসের অর্ডার', $month_orders)?>
</div>

<h3 style="margin:20px 0 10px;color:#334155">💵 আয় সংক্রান্ত</h3>
<div class="grid g2">
  <?=stat_link('reports_detail.php?type=orders&filter=successful', 'মোট Revenue',        money($revenue_total))?>
  <?=stat_link('reports_detail.php?type=orders&filter=month_success', 'এই মাসের Revenue', money($revenue_month))?>
  <?=stat_link('reports_detail.php?type=orders&filter=today_success', 'আজকের Revenue',    money($revenue_today))?>
  <?=stat_link('reports_detail.php?type=users', 'মোট ইউজার', $users_total)?>
</div>

<h3 style="margin:24px 0 10px;color:#334155">🏆 টপ ৫ কাস্টমার (রিচার্জ অনুযায়ী)</h3>
<div class="card" style="padding:0;overflow:hidden">
<table>
<thead><tr><th>#</th><th>নাম</th><th>ইমেইল</th><th>মোট রিচার্জ</th><th>বর্তমান ব্যালেন্স</th></tr></thead>
<tbody>
<?php $i=1; foreach($top_users as $u): ?>
<tr>
  <td><?=$i++?></td>
  <td><b><?=e($u['name'])?></b></td>
  <td><?=e($u['email'])?></td>
  <td><b style="color:#10b981"><?=money($u['total_recharge'])?></b></td>
  <td><?=money($u['balance'])?></td>
</tr>
<?php endforeach; if(!$top_users): ?>
<tr><td colspan="5" style="text-align:center;color:#64748b;padding:20px">কোনো ডেটা নেই</td></tr>
<?php endif; ?>
</tbody>
</table>
</div>

<h3 style="margin:24px 0 10px;color:#334155">📅 গত ৭ দিনের রিচার্জ ট্রেন্ড</h3>
<div class="card" style="padding:0;overflow:hidden">
<table>
<thead><tr><th>তারিখ</th><th>Approved রিচার্জ</th></tr></thead>
<tbody>
<?php if($trend): foreach($trend as $t): ?>
<tr><td><?=e($t['d'])?></td><td><b><?=money($t['s'])?></b></td></tr>
<?php endforeach; else: ?>
<tr><td colspan="2" style="text-align:center;color:#64748b;padding:20px">গত ৭ দিনে কোনো Approved রিচার্জ নেই</td></tr>
<?php endif; ?>
</tbody>
</table>
</div>

<?php require __DIR__.'/_footer.php';
