<?php
$pageTitle = 'Reports';
require_once __DIR__ . '/includes/header.php';
$db = getDB();

// Filters
$dateFrom = $_GET['date_from'] ?? date('Y-m-01');
$dateTo   = $_GET['date_to']   ?? date('Y-m-d');
$type     = $_GET['type']      ?? 'transactions';

// Data
$transactions = $db->prepare("
    SELECT t.*, m.name, m.card_code
    FROM transactions t
    JOIN members m ON m.id = t.member_id
    WHERE DATE(t.created_at) BETWEEN ? AND ?
    ORDER BY t.created_at DESC
");
$transactions->execute([$dateFrom, $dateTo]);
$txns = $transactions->fetchAll();

$membersReport = $db->query("
    SELECT m.*,
           COUNT(t.id) AS txn_count,
           COALESCE(SUM(CASE WHEN t.type='earn' THEN t.amount ELSE 0 END),0) AS total_spend,
           COALESCE(SUM(CASE WHEN t.type='earn' THEN t.points ELSE -t.points END),0) AS balance
    FROM members m
    LEFT JOIN transactions t ON t.member_id = m.id
    WHERE m.name IS NOT NULL
    GROUP BY m.id
    ORDER BY total_spend DESC
")->fetchAll();

$pointsSummary = $db->query("
    SELECT m.name, m.card_code,
           COALESCE(SUM(CASE WHEN t.type='earn' THEN t.points ELSE 0 END),0) AS earned,
           COALESCE(SUM(CASE WHEN t.type='redeem' THEN t.points ELSE 0 END),0) AS redeemed,
           COALESCE(SUM(CASE WHEN t.type='earn' THEN t.points ELSE -t.points END),0) AS balance,
           COALESCE(SUM(CASE WHEN t.type='earn' THEN t.amount ELSE 0 END),0) AS total_spend
    FROM members m
    LEFT JOIN transactions t ON t.member_id = m.id
    WHERE m.name IS NOT NULL
    GROUP BY m.id
    ORDER BY earned DESC
")->fetchAll();

// Totals
$totalTxnAmt = array_sum(array_column(array_filter($txns, fn($r) => $r['type']==='earn'), 'amount'));
$totalTxnPts = array_sum(array_column(array_filter($txns, fn($r) => $r['type']==='earn'), 'points'));
$totalRedeemed = array_sum(array_column(array_filter($txns, fn($r) => $r['type']==='redeem'), 'points'));
?>

<!-- Filter Bar -->
<div class="card shadow-sm mb-4">
  <div class="card-body py-3">
    <form method="GET" class="row g-3 align-items-end">
      <div class="col-sm-3">
        <label class="form-label fw-semibold small">Report Type</label>
        <select name="type" class="form-select form-select-sm">
          <option value="transactions" <?= $type==='transactions'?'selected':'' ?>>Transactions</option>
          <option value="members"      <?= $type==='members'?'selected':'' ?>>Members</option>
          <option value="points"       <?= $type==='points'?'selected':'' ?>>Points Summary</option>
        </select>
      </div>
      <div class="col-sm-3">
        <label class="form-label fw-semibold small">Date From</label>
        <input type="date" name="date_from" class="form-control form-control-sm" value="<?= e($dateFrom) ?>">
      </div>
      <div class="col-sm-3">
        <label class="form-label fw-semibold small">Date To</label>
        <input type="date" name="date_to" class="form-control form-control-sm" value="<?= e($dateTo) ?>">
      </div>
      <div class="col-sm-3">
        <div class="d-flex gap-2 flex-wrap">
          <button type="submit" class="btn btn-primary btn-sm">
            <i class="bi bi-funnel me-1"></i>Filter
          </button>
          <a href="<?= BASE_URL ?>/ajax/export_csv.php?type=<?= e($type) ?>&date_from=<?= e($dateFrom) ?>&date_to=<?= e($dateTo) ?>"
             class="btn btn-success btn-sm">
            <i class="bi bi-filetype-csv me-1"></i>CSV
          </a>
          <button type="button" class="btn btn-outline-secondary btn-sm" onclick="window.print()">
            <i class="bi bi-printer me-1"></i>Print
          </button>
        </div>
      </div>
    </form>
  </div>
</div>

<!-- Summary Badges -->
<?php if ($type === 'transactions'): ?>
<div class="row g-3 mb-4">
  <div class="col-sm-4">
    <div class="stat-card border-start border-success border-4">
      <div class="stat-label">Total Sales (Period)</div>
      <div class="stat-value">₱<?= number_format($totalTxnAmt, 2) ?></div>
    </div>
  </div>
  <div class="col-sm-4">
    <div class="stat-card border-start border-warning border-4">
      <div class="stat-label">Points Earned (Period)</div>
      <div class="stat-value"><?= number_format($totalTxnPts) ?></div>
    </div>
  </div>
  <div class="col-sm-4">
    <div class="stat-card border-start border-info border-4">
      <div class="stat-label">Points Redeemed (Period)</div>
      <div class="stat-value"><?= number_format($totalRedeemed) ?></div>
    </div>
  </div>
</div>
<?php endif; ?>

<!-- Report Table -->
<div class="card shadow-sm">
  <div class="card-header bg-white d-flex align-items-center justify-content-between">
    <span>
      <i class="bi bi-bar-chart-line me-2 text-secondary"></i>
      <?= $type === 'transactions' ? 'Transaction Report' : ($type === 'members' ? 'Members Report' : 'Points Summary') ?>
      <span class="text-muted small ms-2"><?= e($dateFrom) ?> to <?= e($dateTo) ?></span>
    </span>
  </div>

  <div class="table-responsive">

    <?php if ($type === 'transactions'): ?>
    <table class="table table-hover align-middle mb-0" id="reportTable">
      <thead class="table-light">
        <tr>
          <th>#</th><th>Date</th><th>Member</th><th>Card Code</th>
          <th>Amount</th><th>Points</th><th>Type</th>
        </tr>
      </thead>
      <tbody>
        <?php foreach ($txns as $t): ?>
        <tr>
          <td class="small text-muted"><?= $t['id'] ?></td>
          <td class="small"><?= date('M d, Y H:i', strtotime($t['created_at'])) ?></td>
          <td class="fw-semibold"><?= e($t['name'] ?? '—') ?></td>
          <td><code class="small"><?= e($t['card_code']) ?></code></td>
          <td>₱<?= number_format($t['amount'], 2) ?></td>
          <td><span class="badge bg-<?= $t['type']==='earn'?'success':'info' ?>">
            <?= number_format($t['points']) ?>
          </span></td>
          <td><span class="badge <?= $t['type']==='earn'?'bg-success-subtle text-success':'bg-info-subtle text-info' ?>">
            <?= ucfirst($t['type']) ?>
          </span></td>
        </tr>
        <?php endforeach; ?>
        <?php if (empty($txns)): ?>
        <tr><td colspan="7" class="text-center py-5 text-muted">No transactions found for the selected period.</td></tr>
        <?php endif; ?>
      </tbody>
      <tfoot class="table-light fw-bold">
        <tr>
          <td colspan="4" class="text-end">Totals:</td>
          <td>₱<?= number_format($totalTxnAmt, 2) ?></td>
          <td><?= number_format($totalTxnPts) ?></td>
          <td></td>
        </tr>
      </tfoot>
    </table>

    <?php elseif ($type === 'members'): ?>
    <table class="table table-hover align-middle mb-0" id="reportTable">
      <thead class="table-light">
        <tr><th>#</th><th>Card Code</th><th>Name</th><th>Phone</th><th>Address</th>
            <th>Total Spend</th><th>Transactions</th><th>Balance</th><th>Registered</th></tr>
      </thead>
      <tbody>
        <?php foreach ($membersReport as $m): ?>
        <tr>
          <td class="small text-muted"><?= $m['id'] ?></td>
          <td><code class="small"><?= e($m['card_code']) ?></code></td>
          <td class="fw-semibold"><?= e($m['name']) ?></td>
          <td><?= e($m['phone'] ?? '—') ?></td>
          <td class="small"><?= e(substr($m['address'] ?? '—', 0, 40)) ?></td>
          <td>₱<?= number_format($m['total_spend'], 2) ?></td>
          <td><?= number_format($m['txn_count']) ?></td>
          <td><span class="badge bg-warning text-dark"><?= number_format(max(0,(int)$m['balance'])) ?> pts</span></td>
          <td class="small text-muted"><?= $m['registered_at'] ? date('M d, Y', strtotime($m['registered_at'])) : '—' ?></td>
        </tr>
        <?php endforeach; ?>
        <?php if (empty($membersReport)): ?>
        <tr><td colspan="9" class="text-center py-5 text-muted">No members found.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>

    <?php else: // points summary ?>
    <table class="table table-hover align-middle mb-0" id="reportTable">
      <thead class="table-light">
        <tr><th>#</th><th>Card Code</th><th>Name</th><th>Earned</th>
            <th>Redeemed</th><th>Balance</th><th>Total Spend</th></tr>
      </thead>
      <tbody>
        <?php foreach ($pointsSummary as $i => $p): ?>
        <tr>
          <td class="text-muted small"><?= $i+1 ?></td>
          <td><code class="small"><?= e($p['card_code']) ?></code></td>
          <td class="fw-semibold"><?= e($p['name']) ?></td>
          <td><span class="badge bg-success"><?= number_format($p['earned']) ?></span></td>
          <td><span class="badge bg-info"><?= number_format($p['redeemed']) ?></span></td>
          <td><span class="badge bg-warning text-dark"><?= number_format(max(0,(int)$p['balance'])) ?></span></td>
          <td>₱<?= number_format($p['total_spend'], 2) ?></td>
        </tr>
        <?php endforeach; ?>
        <?php if (empty($pointsSummary)): ?>
        <tr><td colspan="7" class="text-center py-5 text-muted">No data.</td></tr>
        <?php endif; ?>
      </tbody>
      <tfoot class="table-light fw-bold">
        <tr>
          <td colspan="3" class="text-end">Totals:</td>
          <td><?= number_format(array_sum(array_column($pointsSummary,'earned'))) ?></td>
          <td><?= number_format(array_sum(array_column($pointsSummary,'redeemed'))) ?></td>
          <td><?= number_format(max(0, array_sum(array_column($pointsSummary,'balance')))) ?></td>
          <td>₱<?= number_format(array_sum(array_column($pointsSummary,'total_spend')),2) ?></td>
        </tr>
      </tfoot>
    </table>
    <?php endif; ?>

  </div>
</div>

<?php
$extraJS = '<script>$(function(){ $("#reportTable").DataTable({ order:[[0,"desc"]], pageLength:25, dom:"lBfrtip" }); });</script>';
require_once __DIR__ . '/includes/footer.php';
