<?php
// ib_withdrawals.php
declare(strict_types=1);
session_start();
require_once __DIR__ . '/auth_guard.php';    // ← session/auth guard
require_once __DIR__ . '/config.php';        // must define $conn as PDO

if (!isset($conn) || !($conn instanceof PDO)) {
  http_response_code(500);
  echo "DB not connected. Ensure config.php sets \$conn as a PDO instance.";
  exit;
}
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/* ---------------------------
   Helpers
---------------------------- */
function h(?string $s): string { return htmlspecialchars($s ?? '', ENT_QUOTES, 'UTF-8'); }
function parse_date(?string $s): ?string {
  if (!$s) return null;
  $t = strtotime($s);
  return $t ? date('Y-m-d', $t) : null;
}

/* ---------------------------
   Filters & Pagination
---------------------------- */
$q       = trim($_GET['q'] ?? '');           // search email or trade_id
$from    = parse_date($_GET['from'] ?? '');  // YYYY-MM-DD
$to      = parse_date($_GET['to'] ?? '');    // YYYY-MM-DD
$page    = max(1, (int)($_GET['page'] ?? 1));
$perPage = 50;
$offset  = ($page - 1) * $perPage;

/* ---------------------------
   WHERE clause (trading_withdrawal > 0)
   Note: column is VARCHAR; cast to DECIMAL for numeric compare.
---------------------------- */
$where = [];
$params = [];
$where[] = "trading_withdrawal IS NOT NULL AND trading_withdrawal <> '' AND CAST(REPLACE(trading_withdrawal, ',', '') AS DECIMAL(18,2)) > 0";

if ($q !== '') {
  $where[] = "(email LIKE :q OR trade_id LIKE :q)";
  $params[':q'] = "%$q%";
}
if ($from) { $where[] = "DATE(reg_date) >= :from"; $params[':from'] = $from; }
if ($to)   { $where[] = "DATE(reg_date) <= :to";   $params[':to']   = $to;   }
$whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

/* ---------------------------
   Count & Sum (for current filter)
---------------------------- */
$sqlCount = "SELECT COUNT(*) AS cnt,
                    COALESCE(SUM(CAST(REPLACE(trading_withdrawal, ',', '') AS DECIMAL(18,2))),0) AS total_sum
             FROM total_balance
             $whereSql";
$st = $conn->prepare($sqlCount);
$st->execute($params);
$agg = $st->fetch(PDO::FETCH_ASSOC) ?: ['cnt'=>0, 'total_sum'=>0];
$totalRows = (int)$agg['cnt'];
$totalSum  = (float)$agg['total_sum'];

/* ---------------------------
   CSV export (respects filters)
---------------------------- */
if (isset($_GET['export']) && $_GET['export'] === '1') {
  $sqlCsv = "SELECT email, trade_id, trading_withdrawal, reg_date
             FROM total_balance
             $whereSql
             ORDER BY reg_date DESC, id DESC";
  $stc = $conn->prepare($sqlCsv);
  $stc->execute($params);

  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename=ib_withdrawals.csv');
  $out = fopen('php://output', 'w');
  fputcsv($out, ['Email','Trade ID','IB Withdrawal','Reg Date']);
  while ($r = $stc->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($out, [$r['email'], $r['trade_id'], $r['trading_withdrawal'], $r['reg_date']]);
  }
  fclose($out);
  exit;
}

/* ---------------------------
   Page data (paginated)
---------------------------- */
$sqlList = "SELECT email, trade_id, trading_withdrawal, reg_date
            FROM total_balance
            $whereSql
            ORDER BY reg_date DESC, id DESC
            LIMIT :limit OFFSET :offset";
$st = $conn->prepare($sqlList);
foreach ($params as $k=>$v) { $st->bindValue($k, $v); }
$st->bindValue(':limit',  $perPage, PDO::PARAM_INT);
$st->bindValue(':offset', $offset,  PDO::PARAM_INT);
$st->execute();
$rows = $st->fetchAll(PDO::FETCH_ASSOC);

/* ---------------------------
   Pagination helpers
---------------------------- */
$totalPages = max(1, (int)ceil($totalRows / $perPage));
function qp(array $add = []): string {
  $base = $_GET;
  foreach ($add as $k=>$v) {
    if ($v === null) unset($base[$k]); else $base[$k] = $v;
  }
  return '?' . http_build_query($base);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8"/>
  <meta name="viewport" content="width=device-width, initial-scale=1, viewport-fit=cover"/>
  <title>IB Withdrawals</title>
  <link href="https://cdn.jsdelivr.net/npm/tailwindcss@2.2.19/dist/tailwind.min.css" rel="stylesheet">
  <style>
    .btn-orange { background:#ff7a00; }
    .btn-orange:hover { background:#e46f00; }
    .chip { background:rgba(255,122,0,0.08); color:#b45500; }
    .ring-orange:focus { box-shadow:0 0 0 3px rgba(255,122,0,0.35); outline:none; }
    @media (min-width: 768px) { .table-sticky thead th { position: sticky; top: 0; background:#fff; z-index:1; } }
  </style>
</head>
<body class="bg-gray-50">
  <?php include 'header.php'; ?>

  <div class="flex min-h-screen">
    <?php include 'side_bar.php'; ?>

    <main class="flex-1 p-3 sm:p-4 md:p-8 md:ml-64">
      <!-- Header -->
      <div class="bg-white rounded-2xl shadow-sm border border-gray-100 overflow-hidden">
        <div class="px-4 sm:px-6 md:px-8 py-4 flex flex-col md:flex-row md:items-center md:justify-between gap-3">
          <div>
            <h1 class="text-xl sm:text-2xl md:text-3xl font-bold text-gray-900">IB Withdrawals</h1>
            <p class="text-sm sm:text-base text-gray-600">
              <!--From <code>total_balance</code> where <span class="font-semibold text-orange-600">trading_withdrawal &gt; 0</span>.-->
            </p>
          </div>
          <span class="chip px-3 py-1 rounded-full text-xs sm:text-sm border border-orange-200">Orange • White</span>
        </div>

        <!-- Filters -->
        <form method="get" class="px-4 sm:px-6 md:px-8 pb-4 grid grid-cols-1 md:grid-cols-4 gap-3 items-end">
          <div>
            <label class="block text-xs text-gray-600 mb-1">From (Date)</label>
            <input type="date" name="from" value="<?= h($_GET['from'] ?? '') ?>" class="w-full px-3 py-2 border border-gray-300 rounded-xl ring-orange text-sm">
          </div>
          <div>
            <label class="block text-xs text-gray-600 mb-1">To (Date)</label>
            <input type="date" name="to" value="<?= h($_GET['to'] ?? '') ?>" class="w-full px-3 py-2 border border-gray-300 rounded-xl ring-orange text-sm">
          </div>
          <div>
            <label class="block text-xs text-gray-600 mb-1">Search (Email / Trade ID)</label>
            <input type="text" name="q" value="<?= h($q) ?>" placeholder="e.g., user@mail.com or 12345"
                   class="w-full px-3 py-2 border border-gray-300 rounded-xl ring-orange text-sm">
          </div>
          <div class="flex gap-2">
            <button class="btn-orange text-white font-semibold px-4 py-2 rounded-xl shadow hover:shadow-md transition w-full">Apply</button>
            <a href="ib_withdrawals.php" class="px-4 py-2 rounded-xl border border-gray-200 hover:border-gray-300 bg-white w-full text-center">Reset</a>
          </div>
        </form>

        <!-- Stats / Actions -->
        <div class="px-4 sm:px-6 md:px-8 py-3 border-t border-gray-100 flex flex-col sm:flex-row sm:items-center sm:justify-between gap-2">
          <div class="text-sm text-gray-700">
            <span class="font-semibold"><?= number_format($totalRows) ?></span> record(s)
            <?php if ($totalRows > 0): ?>
              • Total IB Withdrawal: <span class="font-semibold text-gray-900"><?= number_format($totalSum, 2) ?></span>
            <?php endif; ?>
          </div>
          <div class="flex items-center gap-2">
            <a href="<?= h(qp(['export'=>1,'page'=>null])) ?>" class="btn-orange text-white font-semibold px-4 py-2 rounded-xl shadow hover:shadow-md transition text-sm">Export CSV</a>
          </div>
        </div>
      </div>

      <!-- Table -->
      <div class="mt-4 bg-white rounded-2xl shadow-lg border border-gray-100 overflow-hidden">
        <div class="overflow-x-auto">
          <table class="min-w-full table-auto table-sticky text-xs sm:text-sm">
            <thead>
              <tr class="text-left text-gray-700 border-b border-gray-100">
                <th class="px-3 sm:px-4 py-2 sm:py-3 font-semibold">#</th>
                <th class="px-3 sm:px-4 py-2 sm:py-3 font-semibold">Email</th>
                <th class="px-3 sm:px-4 py-2 sm:py-3 font-semibold">Trade ID</th>
                <th class="px-3 sm:px-4 py-2 sm:py-3 font-semibold">IB Withdrawal</th>
                <th class="px-3 sm:px-4 py-2 sm:py-3 font-semibold">Reg Date</th>
              </tr>
            </thead>
            <tbody>
              <?php
              if ($rows):
                $i = $offset + 1;
                foreach ($rows as $r):
                  $raw = str_replace(',', '', (string)$r['trading_withdrawal']);
                  $amt = is_numeric($raw) ? number_format((float)$raw, 2) : (string)$r['trading_withdrawal'];
              ?>
                <tr class="border-b border-gray-50 hover:bg-orange-50">
                  <td class="px-3 sm:px-4 py-2 text-gray-500"><?= $i++ ?></td>
                  <td class="px-3 sm:px-4 py-2 text-gray-900 break-words"><?= h($r['email']) ?></td>
                  <td class="px-3 sm:px-4 py-2 text-gray-800 break-words"><?= h($r['trade_id']) ?></td>
                  <td class="px-3 sm:px-4 py-2 font-semibold text-gray-900"><?= h($amt) ?></td>
                  <td class="px-3 sm:px-4 py-2 text-gray-700"><?= h($r['reg_date']) ?></td>
                </tr>
              <?php
                endforeach;
              else:
              ?>
                <tr>
                  <td class="px-4 py-8 text-center text-gray-500" colspan="5">No records found for the current filters.</td>
                </tr>
              <?php endif; ?>
            </tbody>
          </table>
        </div>

        <!-- Pagination -->
        <div class="px-4 sm:px-6 md:px-8 py-3 border-t border-gray-100 flex flex-col sm:flex-row sm:items-center sm:justify-between gap-2 text-sm">
          <div>Page <span class="font-semibold"><?= $page ?></span> of <span class="font-semibold"><?= $totalPages ?></span></div>
          <div class="flex flex-wrap gap-2">
            <a class="px-3 py-2 rounded-xl border <?= $page<=1 ? 'opacity-50 pointer-events-none' : 'hover:border-gray-300' ?>" href="<?= h(qp(['page'=>1])) ?>">First</a>
            <a class="px-3 py-2 rounded-xl border <?= $page<=1 ? 'opacity-50 pointer-events-none' : 'hover:border-gray-300' ?>" href="<?= h(qp(['page'=>max(1,$page-1)])) ?>">Prev</a>
            <a class="px-3 py-2 rounded-xl border <?= $page>=$totalPages ? 'opacity-50 pointer-events-none' : 'hover:border-gray-300' ?>" href="<?= h(qp(['page'=>min($totalPages,$page+1)])) ?>">Next</a>
            <a class="px-3 py-2 rounded-xl border <?= $page>=$totalPages ? 'opacity-50 pointer-events-none' : 'hover:border-gray-300' ?>" href="<?= h(qp(['page'=>$totalPages])) ?>">Last</a>
          </div>
        </div>
      </div>

      <div class="h-20"></div>
    </main>

    <?php include 'footer.php'; ?>
  </div>
</body>
</html>
