<?php
declare(strict_types=1);

/**
 * modules/reports/transactions_summary.php
 *
 * Transactions Summary (by Account Code & Segment)
 * - Filters: date range, account_code, segment, type (income/expense/all), search (reference/notes)
 * - KPIs: Total Income, Total Expense, Net
 * - Grouped summary: DATE(date), account_code, segment, type → COUNT, SUM(amount)
 * - Recent transactions list (latest 300 matching filters)
 * - CSV export (?export=csv) of the grouped summary
 */

require_once dirname(__DIR__, 2) . '/config/functions.php';
require_role(['MD','Accounts','Admin','Management']);

$user = current_user();
$cid  = (int)($user['company_id'] ?? 0);
if ($cid <= 0) redirect(url_public('login.php'));

$errors = [];
$notice = null;

/* -----------------------------
   Helpers
------------------------------*/
function tbl_exists(string $t): bool {
  try { db()->query("SELECT 1 FROM `{$t}` LIMIT 1"); return true; }
  catch (Throwable) { return false; }
}

/* -----------------------------
   Filters
------------------------------*/
$today      = new DateTimeImmutable('today');
$monthStart = $today->modify('first day of this month')->format('Y-m-01');
$monthEnd   = $today->modify('last day of this month')->format('Y-m-d');

$from    = (string)($_GET['from'] ?? $monthStart);
$to      = (string)($_GET['to']   ?? $monthEnd);
$code    = trim((string)($_GET['account_code'] ?? ''));    // '' = all
$segment = trim((string)($_GET['segment'] ?? ''));         // '' = all
$type    = trim((string)($_GET['type'] ?? 'all'));         // income|expense|all
$q       = trim((string)($_GET['q'] ?? ''));
$export  = (string)($_GET['export'] ?? '');

if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $from)) $from = $monthStart;
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $to))   $to   = $monthEnd;
if (!in_array($type, ['income','expense','all'], true)) $type = 'all';

/* -----------------------------
   Early exit if table missing
------------------------------*/
if (!tbl_exists('transactions')) {
  include dirname(__DIR__, 2) . '/includes/header.php';
  echo '<div class="alert alert-danger">The <code>transactions</code> table is missing.</div>';
  include dirname(__DIR__, 2) . '/includes/footer.php';
  exit;
}

/* -----------------------------
   Dropdown data: account codes, segments
------------------------------*/
$codes = [];
$segs  = [];
try {
  $st = db()->prepare("SELECT DISTINCT account_code FROM transactions WHERE company_id=:cid ORDER BY account_code");
  $st->execute([':cid'=>$cid]);
  $codes = array_column($st->fetchAll(), 'account_code');

  $ss = db()->prepare("SELECT DISTINCT segment FROM transactions WHERE company_id=:cid AND segment IS NOT NULL AND segment<>'' ORDER BY segment");
  $ss->execute([':cid'=>$cid]);
  $segs = array_column($ss->fetchAll(), 'segment');
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to load filter options.';
}

/* -----------------------------
   Build WHERE + args (shared)
------------------------------*/
$where = ["company_id = :cid", "date BETWEEN :from AND :to"];
$args  = [':cid'=>$cid, ':from'=>$from.' 00:00:00', ':to'=>$to.' 23:59:59'];

if ($code !== '') { $where[] = "account_code = :code"; $args[':code'] = $code; }
if ($segment !== '') { $where[] = "segment = :seg"; $args[':seg'] = $segment; }
if ($type !== 'all') { $where[] = "`type` = :type"; $args[':type'] = $type; }
if ($q !== '') { $where[] = "(reference LIKE :q OR notes LIKE :q)"; $args[':q'] = '%'.$q.'%'; }

$whereSql = implode(' AND ', $where);

/* -----------------------------
   KPIs
------------------------------*/
$totalIncome = 0.0; $totalExpense = 0.0;
try {
  $k = db()->prepare("
    SELECT `type`, COALESCE(SUM(amount),0) AS total
      FROM transactions
     WHERE $whereSql
     GROUP BY `type`
  ");
  $k->execute($args);
  foreach ($k->fetchAll() as $r) {
    if ((string)$r['type'] === 'income')  $totalIncome  = (float)$r['total'];
    if ((string)$r['type'] === 'expense') $totalExpense = (float)$r['total'];
  }
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? 'KPIs: '.$e->getMessage() : 'Unable to load KPIs.';
}
$netTotal = $totalIncome - $totalExpense;

/* -----------------------------
   Grouped summary
------------------------------*/
$summary = [];
try {
  $g = db()->prepare("
    SELECT
      DATE(`date`) AS d,
      account_code,
      COALESCE(segment,'') AS segment,
      `type`,
      COUNT(*) AS tx_count,
      SUM(amount) AS total_amount
    FROM transactions
    WHERE $whereSql
    GROUP BY d, account_code, segment, `type`
    ORDER BY d ASC, account_code ASC, segment ASC, `type` ASC
    LIMIT 5000
  ");
  $g->execute($args);
  $summary = $g->fetchAll() ?: [];
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? 'Summary: '.$e->getMessage() : 'Unable to load summary.';
}

/* -----------------------------
   Recent transactions (detail list)
------------------------------*/
$detail = [];
try {
  $d = db()->prepare("
    SELECT id, `date`, account_code, COALESCE(segment,'') AS segment, `type`,
           amount, booking_id, rental_id, invoice_id, reference, milestone, notes
    FROM transactions
    WHERE $whereSql
    ORDER BY `date` DESC, id DESC
    LIMIT 300
  ");
  $d->execute($args);
  $detail = $d->fetchAll() ?: [];
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? 'Detail: '.$e->getMessage() : 'Unable to load transactions.';
}

/* -----------------------------
   CSV Export (grouped summary)
------------------------------*/
if ($export === 'csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="transactions_summary_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, ['Date','Account Code','Segment','Type','Count','Total Amount']);
  foreach ($summary as $r) {
    fputcsv($out, [
      (string)$r['d'],
      (string)$r['account_code'],
      (string)$r['segment'],
      (string)$r['type'],
      (int)$r['tx_count'],
      number_format((float)$r['total_amount'], 2, '.', '')
    ]);
  }
  fclose($out);
  exit;
}

/* -----------------------------
   Render
------------------------------*/
include dirname(__DIR__, 2) . '/includes/header.php';
?>
<div class="d-flex justify-content-between align-items-center mb-3">
  <div>
    <h1 class="h4 mb-0">Transactions Summary</h1>
    <div class="text-muted">Grouped by Date, Account Code, Segment, and Type.</div>
  </div>
  <div class="d-flex flex-wrap gap-2">
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('reports/index.php')) ?>">← Reports Home</a>
    <?php
      $qs = $_GET;
      $qs['export'] = 'csv';
      $exportUrl = '?' . http_build_query($qs);
    ?>
    <a class="btn btn-outline-primary" href="<?= e($exportUrl) ?>">⬇️ Export CSV</a>
  </div>
</div>

<?php if ($notice): ?><div class="alert alert-success"><?= e($notice) ?></div><?php endif; ?>
<?php if ($errors): ?><div class="alert alert-warning"><ul class="mb-0"><?php foreach ($errors as $er) echo '<li>'.e($er).'</li>'; ?></ul></div><?php endif; ?>

<!-- Filters -->
<div class="card shadow-sm mb-3">
  <div class="card-body">
    <form class="row g-2 align-items-end" method="get">
      <div class="col-sm-3">
        <label class="form-label">From</label>
        <input type="date" class="form-control" name="from" value="<?= e($from) ?>">
      </div>
      <div class="col-sm-3">
        <label class="form-label">To</label>
        <input type="date" class="form-control" name="to" value="<?= e($to) ?>">
      </div>
      <div class="col-sm-3">
        <label class="form-label">Account Code</label>
        <select class="form-select" name="account_code">
          <option value="">— Any —</option>
          <?php foreach ($codes as $c): ?>
            <option value="<?= e($c) ?>" <?= $code===$c?'selected':'' ?>><?= e($c) ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <div class="col-sm-3">
        <label class="form-label">Segment</label>
        <select class="form-select" name="segment">
          <option value="">— Any —</option>
          <?php foreach ($segs as $s): ?>
            <option value="<?= e($s) ?>" <?= $segment===$s?'selected':'' ?>><?= e($s) ?></option>
          <?php endforeach; ?>
        </select>
      </div>

      <div class="col-sm-3">
        <label class="form-label">Type</label>
        <select class="form-select" name="type">
          <option value="all" <?= $type==='all'?'selected':'' ?>>All</option>
          <option value="income" <?= $type==='income'?'selected':'' ?>>Income</option>
          <option value="expense" <?= $type==='expense'?'selected':'' ?>>Expense</option>
        </select>
      </div>
      <div class="col-sm-6">
        <label class="form-label">Search (Reference / Notes)</label>
        <input class="form-control" name="q" value="<?= e($q) ?>" placeholder="e.g., Salary, payout, invoice #, etc.">
      </div>
      <div class="col-sm-3 d-grid">
        <button class="btn btn-primary">Apply Filters</button>
      </div>
    </form>
  </div>
</div>

<!-- KPI Cards -->
<div class="row g-3 mb-3">
  <div class="col-12 col-lg-4">
    <div class="card shadow-sm h-100 border-success">
      <div class="card-body">
        <div class="small text-muted">Total Income</div>
        <div class="display-6">£<?= number_format($totalIncome, 2) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-lg-4">
    <div class="card shadow-sm h-100 border-danger">
      <div class="card-body">
        <div class="small text-muted">Total Expense</div>
        <div class="display-6">£<?= number_format($totalExpense, 2) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-lg-4">
    <div class="card shadow-sm h-100 border-primary">
      <div class="card-body">
        <div class="small text-muted">Net (Income − Expense)</div>
        <div class="display-6">£<?= number_format($netTotal, 2) ?></div>
      </div>
    </div>
  </div>
</div>

<!-- Grouped Summary -->
<div class="card shadow-sm mb-3">
  <div class="card-body">
    <div class="d-flex justify-content-between align-items-center mb-2">
      <div class="fw-semibold">Grouped Summary</div>
      <div class="small text-muted"><?= e($from) ?> → <?= e($to) ?></div>
    </div>

    <div class="table-responsive">
      <table class="table table-sm align-middle mb-0">
        <thead class="table-light">
          <tr>
            <th>Date</th>
            <th>Account Code</th>
            <th>Segment</th>
            <th>Type</th>
            <th class="text-end">Count</th>
            <th class="text-end">Total (£)</th>
          </tr>
        </thead>
        <tbody>
        <?php if ($summary): foreach ($summary as $r): ?>
          <tr>
            <td><?= e((string)$r['d']) ?></td>
            <td><?= e((string)$r['account_code']) ?></td>
            <td><span class="badge text-bg-light"><?= e((string)$r['segment']) ?: '—' ?></span></td>
            <td><span class="badge text-bg-<?= ((string)$r['type']==='income')?'success':'danger' ?>"><?= e((string)$r['type']) ?></span></td>
            <td class="text-end"><?= number_format((int)$r['tx_count']) ?></td>
            <td class="text-end">£<?= number_format((float)$r['total_amount'], 2) ?></td>
          </tr>
        <?php endforeach; else: ?>
          <tr><td colspan="6" class="text-center text-muted py-4">No results for the selected filters.</td></tr>
        <?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>

<!-- Recent Transactions (Detail) -->
<div class="card shadow-sm">
  <div class="card-body">
    <div class="d-flex justify-content-between align-items-center mb-2">
      <div class="fw-semibold">Recent Transactions (latest 300)</div>
    </div>
    <div class="table-responsive">
      <table class="table table-sm align-middle mb-0">
        <thead class="table-light">
          <tr>
            <th>Date</th>
            <th>Account Code</th>
            <th>Segment</th>
            <th>Type</th>
            <th class="text-end">Amount (£)</th>
            <th>Reference</th>
            <th>Notes</th>
          </tr>
        </thead>
        <tbody>
        <?php if ($detail): foreach ($detail as $r): ?>
          <tr>
            <td><?= e((string)$r['date']) ?></td>
            <td><?= e((string)$r['account_code']) ?></td>
            <td><span class="badge text-bg-light"><?= e((string)$r['segment']) ?: '—' ?></span></td>
            <td><span class="badge text-bg-<?= ((string)$r['type']==='income')?'success':'danger' ?>"><?= e((string)$r['type']) ?></span></td>
            <td class="text-end">£<?= number_format((float)$r['amount'], 2) ?></td>
            <td><?= e((string)($r['reference'] ?? '')) ?></td>
            <td class="text-truncate" style="max-width:360px;"><?= e((string)($r['notes'] ?? '')) ?></td>
          </tr>
        <?php endforeach; else: ?>
          <tr><td colspan="7" class="text-center text-muted py-4">No transactions found.</td></tr>
        <?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>

<?php include dirname(__DIR__, 2) . '/includes/footer.php'; ?>
