<?php
session_start();
$page_title = 'Reports';
$active_nav = 'reports';
require_once __DIR__ . '/includes/header.php';

// Date range filter
$date_from = $_GET['from'] ?? date('Y-m-01'); // default: start of current month
$date_to   = $_GET['to']   ?? date('Y-m-d');

// ─── Summary metrics ────────────────────────────────────────────────────
// Total funded in range
$r1 = $conn->prepare(
    "SELECT COALESCE(SUM(total_amount),0), COUNT(*), COALESCE(SUM(staff_count),0)
     FROM org_funding_batches
     WHERE organization_id=? AND status='completed'
       AND DATE(created_at) BETWEEN ? AND ?"
);
$r1->bind_param("iss", $org_id, $date_from, $date_to);
$r1->execute();
[$period_funded, $period_batches, $period_staff_funded] = $r1->get_result()->fetch_row();

// Current balances
$r2 = $conn->prepare(
    "SELECT COALESCE(SUM(w.balance),0), COUNT(DISTINCT u.id)
     FROM wallets w
     INNER JOIN users u ON w.user_id=u.id
     WHERE u.organization_id=? AND w.wallet_type='meal_ticket' AND u.status=1"
);
$r2->bind_param("i", $org_id);
$r2->execute();
[$current_mt_balance, $active_staff] = $r2->get_result()->fetch_row();

// Staff with zero balance
$r3 = $conn->prepare(
    "SELECT COUNT(DISTINCT u.id) FROM users u
     LEFT JOIN wallets w ON w.user_id=u.id AND w.wallet_type='meal_ticket'
     WHERE u.organization_id=? AND u.role='org_staff' AND u.status=1
       AND (w.balance IS NULL OR w.balance = 0)"
);
$r3->bind_param("i", $org_id);
$r3->execute();
$zero_balance_count = (int)$r3->get_result()->fetch_row()[0];

// ─── Monthly trend (last 6 months) ──────────────────────────────────────
$trend = [];
$tr = $conn->prepare(
    "SELECT DATE_FORMAT(created_at,'%Y-%m') AS mo,
            SUM(total_amount) AS funded,
            COUNT(*) AS batches
     FROM org_funding_batches
     WHERE organization_id=? AND status='completed'
       AND created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
     GROUP BY mo ORDER BY mo ASC"
);
$tr->bind_param("i", $org_id);
$tr->execute();
$trend_rows = $tr->get_result()->fetch_all(MYSQLI_ASSOC);
$trend_map  = array_column($trend_rows, null, 'mo');

// Build 6-month grid
for ($i = 5; $i >= 0; $i--) {
    $key = date('Y-m', strtotime("-{$i} months"));
    $trend[] = [
        'month'   => date('M Y', strtotime("{$key}-01")),
        'funded'  => (float)($trend_map[$key]['funded']  ?? 0),
        'batches' => (int)($trend_map[$key]['batches'] ?? 0),
    ];
}
$max_funded = max(array_column($trend, 'funded')) ?: 1;

// ─── Top funded staff ────────────────────────────────────────────────────
$top_funded = [];
$top = $conn->prepare(
    "SELECT u.name, u.employee_id, u.department,
            COALESCE(w.balance,0) AS current_balance,
            COALESCE(SUM(wt.amount),0) AS total_received
     FROM users u
     LEFT JOIN wallets w ON w.user_id=u.id AND w.wallet_type='meal_ticket'
     LEFT JOIN wallet_transactions wt ON wt.user_id=u.id AND wt.wallet_type='meal_ticket'
              AND wt.type='credit' AND DATE(wt.created_at) BETWEEN ? AND ?
     WHERE u.organization_id=? AND u.role='org_staff' AND u.status=1
     GROUP BY u.id
     ORDER BY total_received DESC
     LIMIT 10"
);
$top->bind_param("ssi", $date_from, $date_to, $org_id);
$top->execute();
$top_funded = $top->get_result()->fetch_all(MYSQLI_ASSOC);

// ─── Funding batches in range ────────────────────────────────────────────
$batch_history = [];
$bh = $conn->prepare(
    "SELECT b.*, u.name AS admin_name
     FROM org_funding_batches b
     INNER JOIN users u ON b.funded_by=u.id
     WHERE b.organization_id=? AND DATE(b.created_at) BETWEEN ? AND ?
     ORDER BY b.created_at DESC"
);
$bh->bind_param("iss", $org_id, $date_from, $date_to);
$bh->execute();
$batch_history = $bh->get_result()->fetch_all(MYSQLI_ASSOC);

// ─── Department breakdown ────────────────────────────────────────────────
$dept_data = [];
$dd = $conn->prepare(
    "SELECT COALESCE(u.department,'No Department') AS dept,
            COUNT(u.id) AS staff_count,
            COALESCE(SUM(w.balance),0) AS total_balance,
            COALESCE(SUM(wt.total),0) AS period_funded
     FROM users u
     LEFT JOIN wallets w ON w.user_id=u.id AND w.wallet_type='meal_ticket'
     LEFT JOIN (
       SELECT user_id, SUM(amount) AS total
       FROM wallet_transactions
       WHERE wallet_type='meal_ticket' AND type='credit'
         AND DATE(created_at) BETWEEN ? AND ?
       GROUP BY user_id
     ) wt ON wt.user_id=u.id
     WHERE u.organization_id=? AND u.role='org_staff' AND u.status=1
     GROUP BY dept
     ORDER BY total_balance DESC"
);
$dd->bind_param("ssi", $date_from, $date_to, $org_id);
$dd->execute();
$dept_data = $dd->get_result()->fetch_all(MYSQLI_ASSOC);
?>

<div class="anim-up">

<!-- Page header + date filter -->
<div style="display:flex;align-items:center;justify-content:space-between;margin-bottom:24px;flex-wrap:wrap;gap:12px">
  <div>
    <h2 style="font-size:1.4rem;font-weight:800;letter-spacing:-.03em">Reports & Analytics</h2>
    <p style="font-size:.84rem;color:var(--text-muted);margin-top:3px"><?php echo fmt_date($date_from); ?> — <?php echo fmt_date($date_to); ?></p>
  </div>
  <form method="GET" style="display:flex;align-items:center;gap:10px;flex-wrap:wrap">
    <div style="display:flex;align-items:center;gap:8px">
      <label style="font-size:.78rem;font-weight:700;color:var(--text-muted)">From</label>
      <input type="date" name="from" value="<?php echo h($date_from); ?>" class="form-control" style="width:148px">
    </div>
    <div style="display:flex;align-items:center;gap:8px">
      <label style="font-size:.78rem;font-weight:700;color:var(--text-muted)">To</label>
      <input type="date" name="to" value="<?php echo h($date_to); ?>" class="form-control" style="width:148px">
    </div>
    <button type="submit" class="btn btn-secondary btn-md">Apply</button>
    <!-- Preset ranges -->
    <div style="display:flex;gap:6px">
      <a href="?from=<?php echo date('Y-m-01'); ?>&to=<?php echo date('Y-m-d'); ?>" class="btn btn-secondary btn-sm">This Month</a>
      <a href="?from=<?php echo date('Y-m-01', strtotime('-1 month')); ?>&to=<?php echo date('Y-m-t', strtotime('-1 month')); ?>" class="btn btn-secondary btn-sm">Last Month</a>
      <a href="?from=<?php echo date('Y-01-01'); ?>&to=<?php echo date('Y-m-d'); ?>" class="btn btn-secondary btn-sm">This Year</a>
    </div>
  </form>
</div>

<!-- Summary stats -->
<div class="stat-grid" style="grid-template-columns:repeat(auto-fit,minmax(180px,1fr));margin-bottom:28px">
  <div class="stat-card" style="border-top:3px solid var(--org-accent)">
    <div class="stat-label">Period Funded</div>
    <div class="stat-value" style="font-size:1.3rem;color:var(--org-accent)"><?php echo fmt_money($period_funded); ?></div>
    <div class="stat-sub"><?php echo $period_batches; ?> batch<?php echo $period_batches!=1?'es':''; ?></div>
  </div>
  <div class="stat-card" style="border-top:3px solid var(--success)">
    <div class="stat-label">Total MT Balance</div>
    <div class="stat-value" style="font-size:1.3rem;color:var(--success)"><?php echo fmt_money($current_mt_balance); ?></div>
    <div class="stat-sub">Across <?php echo $active_staff; ?> active staff</div>
  </div>
  <div class="stat-card" style="border-top:3px solid var(--primary)">
    <div class="stat-label">Staff Funded</div>
    <div class="stat-value" style="font-size:1.3rem;color:var(--primary)"><?php echo number_format($period_staff_funded); ?></div>
    <div class="stat-sub">Credits in period</div>
  </div>
  <div class="stat-card" style="border-top:3px solid <?php echo $zero_balance_count > 0 ? 'var(--warning)' : 'var(--success)'; ?>">
    <div class="stat-label">Zero Balance</div>
    <div class="stat-value" style="font-size:1.3rem;color:<?php echo $zero_balance_count > 0 ? 'var(--warning)' : 'var(--success)'; ?>"><?php echo $zero_balance_count; ?></div>
    <div class="stat-sub"><?php echo $zero_balance_count > 0 ? 'Staff need funding' : 'All staff funded'; ?></div>
  </div>
</div>

<!-- Chart + Department breakdown -->
<div style="display:grid;grid-template-columns:1fr 320px;gap:20px;margin-bottom:20px">

  <!-- Monthly trend chart -->
  <div class="card">
    <div class="card-header"><h3>Monthly Funding Trend (Last 6 Months)</h3></div>
    <div class="card-body">
      <div style="display:flex;align-items:flex-end;gap:10px;height:140px;padding-bottom:8px">
        <?php foreach ($trend as $t): ?>
        <?php $pct = $max_funded > 0 ? max(4, round(($t['funded'] / $max_funded) * 100)) : 4; ?>
        <div style="flex:1;display:flex;flex-direction:column;align-items:center;gap:4px">
          <div style="font-size:.68rem;color:var(--text-muted);font-weight:700"><?php echo $t['funded'] > 0 ? '₦'.number_format($t['funded']/1000,0).'k' : '—'; ?></div>
          <div style="width:100%;background:<?php echo $t['funded'] > 0 ? 'var(--org-accent)' : 'var(--border)'; ?>;border-radius:6px 6px 0 0;height:<?php echo $pct; ?>%;min-height:4px;transition:all .3s"></div>
          <div style="font-size:.68rem;color:var(--text-muted);text-align:center;white-space:nowrap"><?php echo date('M', strtotime($t['month'])); ?></div>
        </div>
        <?php endforeach; ?>
      </div>
    </div>
  </div>

  <!-- Department breakdown -->
  <div class="card">
    <div class="card-header"><h3>By Department</h3></div>
    <?php if (empty($dept_data)): ?>
    <div class="card-body" style="font-size:.82rem;color:var(--text-muted)">No department data.</div>
    <?php else: ?>
    <div style="overflow-y:auto;max-height:220px">
      <table class="data-table">
        <thead><tr><th>Department</th><th>Staff</th><th>MT Balance</th></tr></thead>
        <tbody>
          <?php foreach ($dept_data as $d): ?>
          <tr>
            <td style="font-size:.8rem;font-weight:600"><?php echo h($d['dept']); ?></td>
            <td style="font-size:.8rem"><?php echo $d['staff_count']; ?></td>
            <td style="font-size:.8rem;font-weight:700;color:var(--success)"><?php echo fmt_money($d['total_balance']); ?></td>
          </tr>
          <?php endforeach; ?>
        </tbody>
      </table>
    </div>
    <?php endif; ?>
  </div>

</div>

<!-- Top funded staff -->
<div class="card" style="margin-bottom:20px">
  <div class="card-header"><h3>Top Funded Staff — <?php echo fmt_date($date_from); ?> to <?php echo fmt_date($date_to); ?></h3></div>
  <?php if (empty($top_funded) || max(array_column($top_funded, 'total_received')) == 0): ?>
  <div class="card-body" style="font-size:.84rem;color:var(--text-muted);text-align:center">No funding data in this period.</div>
  <?php else: ?>
  <div style="overflow-x:auto">
    <table class="data-table">
      <thead><tr><th>#</th><th>Staff</th><th>Department</th><th>Received (Period)</th><th>Current MT Balance</th></tr></thead>
      <tbody>
        <?php foreach ($top_funded as $i => $s): ?>
        <tr>
          <td style="font-size:.8rem;color:var(--text-muted)"><?php echo $i+1; ?></td>
          <td>
            <div style="font-weight:700;font-size:.84rem"><?php echo h($s['name']); ?></div>
            <?php if ($s['employee_id']): ?><div style="font-size:.72rem;color:var(--text-muted);font-family:monospace"><?php echo h($s['employee_id']); ?></div><?php endif; ?>
          </td>
          <td style="font-size:.8rem;color:var(--text-muted)"><?php echo h($s['department'] ?: '—'); ?></td>
          <td style="font-weight:700;color:var(--org-accent)"><?php echo fmt_money($s['total_received']); ?></td>
          <td style="font-weight:700;color:var(--success)"><?php echo fmt_money($s['current_balance']); ?></td>
        </tr>
        <?php endforeach; ?>
      </tbody>
    </table>
  </div>
  <?php endif; ?>
</div>

<!-- Batch history -->
<div class="card">
  <div class="card-header">
    <h3>Funding Batch History</h3>
    <a href="transactions.php" class="btn btn-secondary btn-sm">All Transactions</a>
  </div>
  <?php if (empty($batch_history)): ?>
  <div class="card-body" style="font-size:.84rem;color:var(--text-muted);text-align:center">No batches in this period.</div>
  <?php else: ?>
  <div style="overflow-x:auto">
    <table class="data-table">
      <thead><tr><th>Date</th><th>Type</th><th>Staff</th><th>Per Staff</th><th>Total</th><th>Status</th><th>By</th></tr></thead>
      <tbody>
        <?php foreach ($batch_history as $b): ?>
        <tr>
          <td style="font-size:.78rem;color:var(--text-muted)"><?php echo fmt_date($b['created_at']); ?></td>
          <td><span class="badge badge-org"><?php echo ucfirst(str_replace('_',' ',$b['batch_type'])); ?></span></td>
          <td><?php echo $b['staff_count']; ?></td>
          <td><?php echo fmt_money($b['amount_per_staff']); ?></td>
          <td style="font-weight:700"><?php echo fmt_money($b['total_amount']); ?></td>
          <td>
            <?php
            $cls = match($b['status']) { 'completed'=>'badge-success','failed'=>'badge-error',default=>'badge-warning' };
            echo '<span class="badge '.$cls.'">'.ucfirst($b['status']).'</span>';
            ?>
          </td>
          <td style="font-size:.78rem;color:var(--text-muted)"><?php echo h($b['admin_name']); ?></td>
        </tr>
        <?php endforeach; ?>
      </tbody>
    </table>
  </div>
  <?php endif; ?>
</div>

</div>

<?php require_once __DIR__ . '/includes/footer.php'; ?>
