<?php
require_once __DIR__ . '/includes/expense_common.php';
expense_require_role(['admin','superadmin']);
expense_ensure_schema($conn);

$username = $_SESSION['username'] ?? 'Admin';
$role = $_SESSION['role'];

$from     = $_GET['from'] ?? date('Y-m-01');
$to       = $_GET['to'] ?? date('Y-m-d');
$status   = $_GET['status'] ?? '';
$category = $_GET['category'] ?? '';
$division = $_GET['division'] ?? '';
$q        = trim($_GET['q'] ?? '');

$where  = "WHERE e.expense_date <= ? AND COALESCE(e.to_date, e.expense_date) >= ?";
$params = [$to, $from];
$types  = "ss";

if (in_array($status, ['pending','approved','rejected'], true)) { $where .= " AND e.status = ?"; $params[] = $status; $types .= "s"; }
if ($category !== '') { $where .= " AND e.category = ?"; $params[] = $category; $types .= "s"; }
if ($division !== '') { $where .= " AND u.division = ?"; $params[] = $division; $types .= "s"; }
if ($q !== '') { $where .= " AND (u.first_name LIKE ? OR u.last_name LIKE ? OR e.employee_name LIKE ?)"; $like = "%$q%"; $params[] = $like; $params[] = $like; $params[] = $like; $types .= "sss"; }

$sql = "SELECT e.*, u.first_name, u.last_name, u.division, u.designation
        FROM expenses e LEFT JOIN users u ON u.id = e.employee_id COLLATE utf8mb4_general_ci
        $where ORDER BY e.expense_date DESC, e.id DESC";
$stmt = $conn->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

/* CSV export */
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="expense_report_' . date('Ymd_His') . '.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['ID','Trip','Employee','Division','Designation','From Date','To Date','Category','Amount','Status','Description','Reviewed By','Reviewed At','Rejection Reason']);
    foreach ($rows as $r) {
        fputcsv($out, [
            $r['id'], $r['claim_group'] ?? '',
            trim(($r['first_name']??'').' '.($r['last_name']??$r['employee_name'])),
            $r['division'] ?? '', $r['designation'] ?? '',
            $r['expense_date'], $r['to_date'] ?? $r['expense_date'], $r['category'], $r['amount'], $r['status'],
            $r['description'], $r['reviewed_by_name'] ?? '', $r['reviewed_at'] ?? '', $r['rejection_reason'] ?? ''
        ]);
    }
    fclose($out);
    exit();
}

/* Excel export — a browser-openable .xls file built as an HTML table.
   (No PhpSpreadsheet/COM dependency needed; Excel opens this natively.) */
if (isset($_GET['export']) && $_GET['export'] === 'excel') {
    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="expense_report_' . date('Ymd_His') . '.xls"');
    echo "\xEF\xBB\xBF"; // UTF-8 BOM so ₹ and names render correctly in Excel
    echo '<table border="1"><tr>';
    foreach (['ID','Trip','Employee','Division','Designation','From Date','To Date','Category','Amount','Status','Description','Reviewed By','Reviewed At','Rejection Reason'] as $h) {
        echo '<th>' . htmlspecialchars($h) . '</th>';
    }
    echo '</tr>';
    foreach ($rows as $r) {
        $cells = [
            $r['id'], $r['claim_group'] ?? '',
            trim(($r['first_name']??'').' '.($r['last_name']??$r['employee_name'])),
            $r['division'] ?? '', $r['designation'] ?? '',
            $r['expense_date'], $r['to_date'] ?? $r['expense_date'], $r['category'], $r['amount'], $r['status'],
            $r['description'], $r['reviewed_by_name'] ?? '', $r['reviewed_at'] ?? '', $r['rejection_reason'] ?? ''
        ];
        echo '<tr>';
        foreach ($cells as $cell) echo '<td>' . htmlspecialchars((string)$cell) . '</td>';
        echo '</tr>';
    }
    echo '</table>';
    exit();
}

/* Print / Save-as-PDF view — a clean printable page; the browser's own
   "Print > Save as PDF" produces the PDF, so no server-side PDF library is required. */
if (isset($_GET['view']) && $_GET['view'] === 'print') {
    $grand_total_print = array_sum(array_column(array_filter($rows, fn($r) => $r['status'] === 'approved'), 'amount'));
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Expense Report · <?= h($from) ?> to <?= h($to) ?></title>
<style>
  *{box-sizing:border-box;font-family:'DM Sans',Arial,sans-serif}
  body{margin:24px;color:#0f172a}
  h1{font-size:18px;margin-bottom:2px}
  .sub{font-size:12px;color:#64748b;margin-bottom:16px}
  table{width:100%;border-collapse:collapse;font-size:11px}
  th,td{border:1px solid #cbd5e1;padding:6px 8px;text-align:left}
  th{background:#f1f5f9}
  tfoot td{font-weight:700;background:#f8fafc}
  .no-print{margin-bottom:16px}
  @media print { .no-print{display:none} }
</style>
</head>
<body>
  <div class="no-print">
    <button onclick="window.print()" style="padding:8px 16px;font-weight:700;cursor:pointer">Print / Save as PDF</button>
  </div>
  <h1>MPC · Expense Report</h1>
  <div class="sub">
    <?= h($from) ?> to <?= h($to) ?>
    <?php if ($status !== '') echo ' · Status: ' . h(ucfirst($status)); ?>
    <?php if ($category !== '') echo ' · Category: ' . h($category); ?>
    <?php if ($division !== '') echo ' · Division: ' . h($division); ?>
    · Generated <?= date('d M Y, H:i') ?>
  </div>
  <table>
    <thead><tr><th>#</th><th>Employee</th><th>Division</th><th>Date</th><th>Category</th><th>Amount</th><th>Status</th><th>Reviewed By</th></tr></thead>
    <tbody>
      <?php foreach ($rows as $r): ?>
      <tr>
        <td><?= (int)$r['id'] ?></td>
        <td><?= h(trim(($r['first_name']??'').' '.($r['last_name']??$r['employee_name']))) ?></td>
        <td><?= h($r['division'] ?? '—') ?></td>
        <td><?= h($r['expense_date']) ?><?php if (!empty($r['to_date']) && $r['to_date'] !== $r['expense_date']): ?> → <?= h($r['to_date']) ?><?php endif; ?></td>
        <td><?= h($r['category']) ?></td>
        <td>₹<?= number_format((float)$r['amount'],2) ?></td>
        <td><?= h(ucfirst($r['status'])) ?></td>
        <td><?= h($r['reviewed_by_name'] ?? '—') ?></td>
      </tr>
      <?php endforeach; ?>
      <?php if (empty($rows)): ?><tr><td colspan="8">No claims match these filters.</td></tr><?php endif; ?>
    </tbody>
    <tfoot>
      <tr><td colspan="5" style="text-align:right">Total Approved</td><td colspan="3">₹<?= number_format($grand_total_print,2) ?></td></tr>
    </tfoot>
  </table>
  <script>window.onload = () => setTimeout(() => window.print(), 300);</script>
</body>
</html>
<?php
    exit();
}

$categories = $conn->query("SELECT DISTINCT name FROM expense_categories ORDER BY name")->fetch_all(MYSQLI_ASSOC);
$divisions  = $conn->query("SELECT DISTINCT division FROM users WHERE division IS NOT NULL ORDER BY division")->fetch_all(MYSQLI_ASSOC);
$emp_options = $conn->query("
    SELECT DISTINCT TRIM(CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,''))) name
    FROM expenses e LEFT JOIN users u ON u.id = e.employee_id COLLATE utf8mb4_general_ci
    WHERE TRIM(CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,''))) <> ''
    ORDER BY name
")->fetch_all(MYSQLI_ASSOC);

$grand_total = array_sum(array_column(array_filter($rows, fn($r) => $r['status'] === 'approved'), 'amount'));

/* Group on-screen rows by trip (claim_group); exports above stay one-row-per-category for accounting detail */
$row_groups = [];
foreach ($rows as $r) {
    $key = !empty($r['claim_group']) ? $r['claim_group'] : 'single_' . $r['id'];
    if (!isset($row_groups[$key])) $row_groups[$key] = ['items' => [], 'total' => 0];
    $row_groups[$key]['items'][] = $r;
    $row_groups[$key]['total'] += (float)$r['amount'];
}

require_once __DIR__ . '/includes/expense_theme.php';
expense_render_head('Reports');
expense_render_sidebar_and_topbar($role, $username, 'expense_reports.php', 'Reports');
?>
  <div class="page">
    <div class="page-header">
      <div>
        <div class="page-title">Expense Reports</div>
        <div class="page-subtitle">Filter, review, and export company expense data</div>
      </div>
      <div style="display:flex;gap:8px;flex-wrap:wrap">
        <a href="?<?= h(http_build_query(array_merge($_GET, ['export'=>'csv']))) ?>" class="back-btn"><i class="fas fa-file-csv" style="font-size:10px"></i> CSV</a>
        <a href="?<?= h(http_build_query(array_merge($_GET, ['export'=>'excel']))) ?>" class="back-btn"><i class="fas fa-file-excel" style="font-size:10px"></i> Excel</a>
        <a href="?<?= h(http_build_query(array_merge($_GET, ['view'=>'print']))) ?>" target="_blank" class="back-btn"><i class="fas fa-file-pdf" style="font-size:10px"></i> Print / PDF</a>
      </div>
    </div>

    <div class="dash-card">
      <div class="dash-card-body">
        <form method="GET" class="filter-bar" style="margin:0;align-items:flex-end">
          <div>
            <label class="form-label">From</label>
            <input type="date" name="from" value="<?= h($from) ?>">
          </div>
          <div>
            <label class="form-label">To</label>
            <input type="date" name="to" value="<?= h($to) ?>">
          </div>
          <div>
            <label class="form-label">Status</label>
            <select name="status">
              <option value="">All</option>
              <option value="pending" <?= $status==='pending'?'selected':'' ?>>Pending</option>
              <option value="approved" <?= $status==='approved'?'selected':'' ?>>Approved</option>
              <option value="rejected" <?= $status==='rejected'?'selected':'' ?>>Rejected</option>
            </select>
          </div>
          <div>
            <label class="form-label">Category</label>
            <select name="category">
              <option value="">All</option>
              <?php foreach ($categories as $c): ?>
                <option value="<?= h($c['name']) ?>" <?= $category===$c['name']?'selected':'' ?>><?= h($c['name']) ?></option>
              <?php endforeach; ?>
            </select>
          </div>
          <div>
            <label class="form-label">Division</label>
            <select name="division">
              <option value="">All</option>
              <?php foreach ($divisions as $d): ?>
                <option value="<?= h($d['division']) ?>" <?= $division===$d['division']?'selected':'' ?>><?= h($d['division']) ?></option>
              <?php endforeach; ?>
            </select>
          </div>
          <div>
            <label class="form-label">Employee</label>
            <input type="text" name="q" list="empOptions" value="<?= h($q) ?>" placeholder="Search name…">
            <datalist id="empOptions">
              <?php foreach ($emp_options as $eo): ?><option value="<?= h($eo['name']) ?>"><?php endforeach; ?>
            </datalist>
          </div>
          <button type="submit" class="btn-action btn-finish"><i class="fas fa-filter" style="font-size:10px"></i> Apply</button>
        </form>
      </div>
    </div>

    <div class="dash-card">
      <div class="dash-card-header">
        <div class="dash-card-title"><i class="fas fa-table"></i> Results</div>
        <span style="font-size:10px;font-weight:600;background:#d1fae5;color:#065f46;padding:3px 9px;border-radius:20px"><?= count($rows) ?> rows · ₹<?= number_format($grand_total,0) ?> approved</span>
      </div>
      <div class="dash-card-body" style="padding:0">
        <div class="tbl-wrap">
          <table class="dash-table">
            <thead><tr><th>#</th><th>Employee</th><th>Division</th><th>Date</th><th>Category</th><th>Amount</th><th>Status</th><th>Reviewed By</th></tr></thead>
            <tbody>
              <?php foreach ($row_groups as $key => $group): $items = $group['items']; $multi = count($items) > 1; ?>
                <?php if ($multi): $first = $items[0]; ?>
                <tr style="background:#f8fafc">
                  <td colspan="5" style="font-weight:700;color:var(--ink);font-size:12px">
                    <i class="fas fa-suitcase" style="margin-right:5px;color:var(--accent)"></i>
                    <?= h(trim(($first['first_name']??'').' '.($first['last_name']??$first['employee_name']))) ?> — Trip claim
                    <span style="font-weight:400;color:var(--muted)">(<?= count($items) ?> categories)</span>
                  </td>
                  <td colspan="3" style="text-align:right;font-weight:700;color:var(--accent)">Total ₹<?= number_format($group['total'],2) ?></td>
                </tr>
                <?php endif; ?>
                <?php foreach ($items as $r): [$cls,$icon,$label] = expense_status_badge($r['status']); ?>
                <tr>
                  <td style="color:var(--muted);font-size:11px"><?= (int)$r['id'] ?></td>
                  <td style="<?= $multi ? 'padding-left:18px' : '' ?>">
                    <?php if (!$multi): ?>
                      <strong style="font-size:12.5px"><?= h(trim(($r['first_name']??'').' '.($r['last_name']??$r['employee_name']))) ?></strong>
                      <div style="font-size:10.5px;color:var(--muted)"><?= h($r['designation'] ?? '') ?></div>
                    <?php else: ?><span style="color:var(--muted);font-size:11px">—</span><?php endif; ?>
                  </td>
                  <td style="font-size:11px;color:var(--muted)"><?= h($r['division'] ?? '—') ?></td>
                  <td style="white-space:nowrap"><?= h($r['expense_date']) ?><?php if (!empty($r['to_date']) && $r['to_date'] !== $r['expense_date']): ?> → <?= h($r['to_date']) ?><?php endif; ?></td>
                  <td><span class="badge-custom badge-purple"><?= h($r['category']) ?></span></td>
                  <td style="font-weight:700">₹<?= number_format((float)$r['amount'],2) ?></td>
                  <td><span class="badge-custom <?= $cls ?>"><i class="fas fa-<?= $icon ?>" style="font-size:9px"></i><?= $label ?></span></td>
                  <td style="font-size:11px;color:var(--muted)"><?= h($r['reviewed_by_name'] ?? '—') ?></td>
                </tr>
                <?php endforeach; ?>
              <?php endforeach; ?>
              <?php if (empty($rows)): ?>
                <tr><td colspan="8"><div class="empty-state"><i class="fas fa-magnifying-glass"></i><p>No claims match these filters.</p></div></td></tr>
              <?php endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
<?php expense_render_footer_scripts(); ?>