<?php
declare(strict_types=1);

/**
 * modules/reports/cash_outflow.php
 *
 * Cash Outflow Timeline & Breakdown
 *
 * Source: transactions (expenses only)
 *   - Filters: date range (transactions.date), account_code(s), segment(s)
 *   - Group by: Month (default) | Day | Account Code | Segment
 *   - Breakdown (chart only when grouping by Month/Day): None | By Account Code | By Segment
 *
 * Outputs:
 *   - KPI cards (Total Outflow, #Transactions)
 *   - Grouped table with totals
 *   - Trend chart (line for Month/Day, bar for Account/Segment)
 *   - CSV export
 */

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;

/* -----------------------------
   Filters & defaults
------------------------------*/
$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);
$group  = (string)($_GET['group'] ?? 'month');      // month|day|account|segment
$break  = (string)($_GET['breakdown'] ?? 'none');   // none|account|segment (chart only when group is time)
$codes  = (array)($_GET['codes'] ?? []);            // multi-select
$segs   = (array)($_GET['segments'] ?? []);         // multi-select
$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($group, ['month','day','account','segment'], true)) $group = 'month';
if (!in_array($break, ['none','account','segment'], true)) $break = 'none';

/* -----------------------------
   Options: distinct codes & segments for filters
------------------------------*/
$codeOptions = [];
$segOptions  = [];
try {
  $q = db()->prepare("
    SELECT DISTINCT account_code
      FROM transactions
     WHERE company_id=:cid AND type='expense' AND `date` BETWEEN :f AND :t
     ORDER BY account_code ASC
     LIMIT 500
  ");
  $q->execute([':cid'=>$cid, ':f'=>$from, ':t'=>$to]);
  $codeOptions = array_values(array_filter(array_map(fn($r)=>$r['account_code'] ?? null, $q->fetchAll() ?: []), fn($v)=>$v!==null));
} catch (Throwable) {}

try {
  $q = db()->prepare("
    SELECT DISTINCT COALESCE(segment,'—') AS seg
      FROM transactions
     WHERE company_id=:cid AND type='expense' AND `date` BETWEEN :f AND :t
     ORDER BY seg ASC
     LIMIT 500
  ");
  $q->execute([':cid'=>$cid, ':f'=>$from, ':t'=>$to]);
  $segOptions = array_values(array_filter(array_map(fn($r)=>$r['seg'] ?? '—', $q->fetchAll() ?: [])));
} catch (Throwable) {}

/* Normalize filters to existing options only */
$codes = array_values(array_intersect($codes, $codeOptions));
$segs  = array_values(array_intersect($segs,  $segOptions));

/* -----------------------------
   WHERE clause
------------------------------*/
$where = ["t.company_id=:cid", "t.type='expense'", "t.`date` BETWEEN :f AND :t"];
$args  = [':cid'=>$cid, ':f'=>$from, ':t'=>$to];

if ($codes) {
  $in = implode(',', array_fill(0, count($codes), '?'));
  $where[] = "t.account_code IN ($in)";
}
if ($segs) {
  $in2 = implode(',', array_fill(0, count($segs), '?'));
  // Normalize NULLs as '—' on display; here we filter non-null segments only.
  $where[] = "COALESCE(t.segment,'—') IN ($in2)";
}

$whereSql = implode(' AND ', $where);
$argList  = array_merge(array_values($args), $codes, $segs);

/* -----------------------------
   Group expressions for table
------------------------------*/
switch ($group) {
  case 'day':
    $grpLabel = "DATE_FORMAT(t.`date`, '%Y-%m-%d')";
    $grpSort  = "DATE_FORMAT(t.`date`, '%Y-%m-%d')";
    $grpTitle = 'Day';
    $chartType= 'line';
    break;
  case 'account':
    $grpLabel = "t.account_code";
    $grpSort  = "t.account_code";
    $grpTitle = 'Account Code';
    $chartType= 'bar';
    break;
  case 'segment':
    $grpLabel = "COALESCE(t.segment,'—')";
    $grpSort  = "COALESCE(t.segment,'—')";
    $grpTitle = 'Segment';
    $chartType= 'bar';
    break;
  case 'month':
  default:
    $grpLabel = "DATE_FORMAT(t.`date`, '%Y-%m')";
    $grpSort  = "DATE_FORMAT(t.`date`, '%Y-%m')";
    $grpTitle = 'Month';
    $chartType= 'line';
    $group    = 'month';
    break;
}

/* -----------------------------
   Query grouped rows for table/KPIs
------------------------------*/
$rows = [];
try {
  $sql = "
    SELECT tt.grp_label, MIN(tt.grp_sort) AS grp_sort,
           COUNT(*) AS txns, SUM(tt.amount) AS outflow
    FROM (
      SELECT
        $grpLabel AS grp_label,
        $grpSort  AS grp_sort,
        t.amount  AS amount
      FROM transactions t
      WHERE $whereSql
    ) tt
    GROUP BY tt.grp_label
    ORDER BY " . (($group==='month' || $group==='day') ? "grp_sort ASC" : "outflow DESC") . "
    LIMIT 2000
  ";
  $st = db()->prepare($sql);
  $st->execute($argList);
  $rows = $st->fetchAll() ?: [];
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to load cash outflow.';
}

/* KPIs */
$totalOut = 0.0; $totalTx = 0;
foreach ($rows as $r) {
  $totalOut += (float)($r['outflow'] ?? 0);
  $totalTx  += (int)($r['txns'] ?? 0);
}

/* -----------------------------
   Build chart datasets
------------------------------*/
$labels = [];
$datasets = [];

/* Helper to generate full label range for time-grouped views */
function build_time_labels(string $mode, string $from, string $to): array {
  $out = [];
  $start = new DateTimeImmutable($from);
  $end   = new DateTimeImmutable($to);
  if ($mode === 'day') {
    for ($d=$start; $d <= $end; $d = $d->modify('+1 day')) {
      $out[] = $d->format('Y-m-d');
    }
  } else { // month
    $d = new DateTimeImmutable($start->format('Y-m-01'));
    $last = new DateTimeImmutable($end->format('Y-m-01'));
    while ($d <= $last) {
      $out[] = $d->format('Y-m');
      $d = $d->modify('+1 month');
    }
  }
  return $out;
}

if ($chartType === 'line') {
  // Prepare base labels (full range)
  $labels = build_time_labels($group, $from, $to);

  // Breakdown handling (none/account/segment)
  if ($break === 'account' || $break === 'segment') {
    $catCol = ($break === 'account') ? "t.account_code" : "COALESCE(t.segment,'—')";
    try {
      // 1) Pull totals by (label, category)
      $fmt = ($group==='day') ? "%Y-%m-%d" : "%Y-%m";
      $sql = "
        SELECT
          DATE_FORMAT(t.`date`, '$fmt') AS lbl,
          $catCol AS cat,
          SUM(t.amount) AS amt
        FROM transactions t
        WHERE $whereSql
        GROUP BY DATE_FORMAT(t.`date`, '$fmt'), $catCol
      ";
      $st = db()->prepare($sql);
      $st->execute($argList);
      $raw = $st->fetchAll() ?: [];

      // 2) Aggregate totals per category to pick top 5
      $totByCat = [];
      foreach ($raw as $r) {
        $cat = (string)($r['cat'] ?? '—');
        $totByCat[$cat] = ($totByCat[$cat] ?? 0) + (float)$r['amt'];
      }
      arsort($totByCat);
      $topCats = array_slice(array_keys($totByCat), 0, 5);

      // 3) Build series for top categories; aggregate others
      $series = [];
      $others = array_fill_keys($labels, 0.0);
      foreach ($topCats as $c) $series[$c] = array_fill_keys($labels, 0.0);

      foreach ($raw as $r) {
        $l = (string)$r['lbl'];
        $c = (string)($r['cat'] ?? '—');
        $v = (float)$r['amt'];
        if (!in_array($l, $labels, true)) continue;
        if (isset($series[$c])) $series[$c][$l] += $v;
        else $others[$l] += $v;
      }

      // 4) Convert to datasets
      foreach ($series as $cat => $map) {
        $datasets[] = [
          'label' => $cat,
          'data'  => array_map(fn($lb)=> (float)($map[$lb] ?? 0), $labels),
        ];
      }
      // Only add "Others" if non-zero
      if (array_sum($others) > 0.0001) {
        $datasets[] = [
          'label' => 'Others',
          'data'  => array_map(fn($lb)=> (float)($others[$lb] ?? 0), $labels),
        ];
      }
    } catch (Throwable $e) {
      $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to build breakdown chart.';
      // Fallback to "none"
      $break = 'none';
      $datasets = [];
    }
  }

  if ($break === 'none') {
    // Simple single-series sum per label from $rows or a direct query
    $sumByLabel = array_fill_keys($labels, 0.0);
    try {
      $fmt = ($group==='day') ? "%Y-%m-%d" : "%Y-%m";
      $sql = "
        SELECT DATE_FORMAT(t.`date`, '$fmt') AS lbl, SUM(t.amount) AS amt
          FROM transactions t
         WHERE $whereSql
         GROUP BY DATE_FORMAT(t.`date`, '$fmt')
      ";
      $st = db()->prepare($sql);
      $st->execute($argList);
      foreach ($st->fetchAll() ?: [] as $r) {
        $l = (string)$r['lbl']; $v = (float)$r['amt'];
        if (isset($sumByLabel[$l])) $sumByLabel[$l] += $v;
      }
    } catch (Throwable $e) {}
    $datasets[] = [
      'label' => 'Total Outflow',
      'data'  => array_map(fn($lb)=> (float)($sumByLabel[$lb] ?? 0), $labels),
    ];
  }
} else {
  // Bar chart for non-time grouping (Account/Segment)
  $labels = array_map(fn($r)=> (string)$r['grp_label'], $rows);
  $datasets[] = [
    'label' => 'Outflow',
    'data'  => array_map(fn($r)=> (float)($r['outflow'] ?? 0), $rows),
  ];
}

/* -----------------------------
   CSV export
------------------------------*/
if ($export === 'csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="cash_outflow_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, [$grpTitle, '# Transactions', 'Outflow']);
  foreach ($rows as $r) {
    fputcsv($out, [
      (string)$r['grp_label'],
      (int)($r['txns'] ?? 0),
      number_format((float)($r['outflow'] ?? 0), 2, '.', '')
    ]);
  }
  // Totals
  fputcsv($out, ['TOTAL', (int)$totalTx, number_format($totalOut, 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">Cash Outflow</h1>
    <div class="text-muted">Expenses from <code>transactions</code> (type = expense).</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-md-2">
        <label class="form-label">From</label>
        <input type="date" class="form-control" name="from" value="<?= e($from) ?>">
      </div>
      <div class="col-md-2">
        <label class="form-label">To</label>
        <input type="date" class="form-control" name="to" value="<?= e($to) ?>">
      </div>
      <div class="col-md-2">
        <label class="form-label">Group By</label>
        <select class="form-select" name="group">
          <option value="month"   <?= $group==='month'?'selected':'' ?>>Month</option>
          <option value="day"     <?= $group==='day'?'selected':'' ?>>Day</option>
          <option value="account" <?= $group==='account'?'selected':'' ?>>Account Code</option>
          <option value="segment" <?= $group==='segment'?'selected':'' ?>>Segment</option>
        </select>
      </div>
      <div class="col-md-3">
        <label class="form-label">Breakdown (chart)</label>
        <select class="form-select" name="breakdown">
          <option value="none"    <?= $break==='none'?'selected':'' ?>>None</option>
          <option value="account" <?= $break==='account'?'selected':'' ?>>By Account Code</option>
          <option value="segment" <?= $break==='segment'?'selected':'' ?>>By Segment</option>
        </select>
        <div class="form-text">Applies when grouping by Month/Day.</div>
      </div>
      <div class="col-md-3 d-grid">
        <button class="btn btn-primary">Apply Filters</button>
      </div>

      <div class="col-12"></div>

      <div class="col-md-6">
        <label class="form-label">Account Codes</label>
        <select class="form-select" name="codes[]" multiple size="4">
          <?php foreach ($codeOptions as $c): ?>
            <option value="<?= e($c) ?>" <?= in_array($c, $codes, true) ? 'selected':'' ?>><?= e($c) ?></option>
          <?php endforeach; ?>
        </select>
        <div class="form-text">Leave empty to include all.</div>
      </div>

      <div class="col-md-6">
        <label class="form-label">Segments</label>
        <select class="form-select" name="segments[]" multiple size="4">
          <?php foreach ($segOptions as $s): ?>
            <option value="<?= e($s) ?>" <?= in_array($s, $segs, true) ? 'selected':'' ?>><?= e($s) ?></option>
          <?php endforeach; ?>
        </select>
        <div class="form-text">Leave empty to include all.</div>
      </div>
    </form>
  </div>
</div>

<!-- KPI Cards -->
<div class="row g-3 mb-3">
  <div class="col-12 col-md-6">
    <div class="card shadow-sm h-100 border-danger">
      <div class="card-body">
        <div class="small text-muted">Total Outflow</div>
        <div class="display-6">£<?= number_format($totalOut, 2) ?></div>
      </div>
    </div>
  </div>
  <div class="col-12 col-md-6">
    <div class="card shadow-sm h-100 border-secondary">
      <div class="card-body">
        <div class="small text-muted"># Transactions</div>
        <div class="display-6"><?= number_format($totalTx) ?></div>
      </div>
    </div>
  </div>
</div>

<!-- Grouped Table -->
<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 by <?= e($grpTitle) ?></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><?= e($grpTitle) ?></th>
            <th class="text-end"># Txns</th>
            <th class="text-end">£ Outflow</th>
          </tr>
        </thead>
        <tbody>
          <?php if ($rows): foreach ($rows as $r): ?>
            <tr>
              <td><?= e((string)$r['grp_label']) ?></td>
              <td class="text-end"><?= number_format((int)$r['txns']) ?></td>
              <td class="text-end fw-semibold">£<?= number_format((float)$r['outflow'], 2) ?></td>
            </tr>
          <?php endforeach; else: ?>
            <tr><td colspan="3" class="text-center text-muted py-4">No expense transactions for the selected filters.</td></tr>
          <?php endif; ?>
        </tbody>
        <?php if ($rows): ?>
        <tfoot class="table-light">
          <tr>
            <th>Total</th>
            <th class="text-end"><?= number_format($totalTx) ?></th>
            <th class="text-end">£<?= number_format($totalOut, 2) ?></th>
          </tr>
        </tfoot>
        <?php endif; ?>
      </table>
    </div>
  </div>
</div>

<!-- Chart -->
<div class="card shadow-sm">
  <div class="card-body">
    <div class="fw-semibold mb-2">Trend<?= ($chartType==='line' && $break!=='none') ? ' (Breakdown: '.e(ucfirst($break)).')' : '' ?></div>
    <canvas id="outChart" height="110"></canvas>
  </div>
</div>

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

<!-- Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
(function(){
  const el = document.getElementById('outChart');
  if (!el || typeof Chart === 'undefined') return;

  const labels  = <?= json_encode(array_values($labels)) ?>;
  const datasets= <?= json_encode($datasets) ?>;

  const isLine = <?= json_encode($chartType==='line') ?>;
  const chart = new Chart(el, {
    type: isLine ? 'line' : 'bar',
    data: {
      labels,
      datasets: datasets.map(ds => ({
        label: ds.label,
        data: ds.data.map(Number)
      }))
    },
    options: {
      responsive: true,
      plugins: { legend: { position: 'top' } },
      scales: {
        y: { beginAtZero: true }
      }
    }
  });
})();
</script>
