<?php
declare(strict_types=1);

/**
 * modules/accounts/index.php
 *
 * Accounts Dashboard (Zoho-style):
 *  - KPI cards (This Month): Chauffeur Income, Rental Income, Total Expenses, Net Profit
 *  - Line chart: Chauffeur vs Rental Income (last 6 months)
 *  - Pie chart: Expense breakdown by category (this month)
 *
 * Assumptions:
 *  - transactions(company_id, date, account_code, type['income'|'expense'], amount, booking_id?, rental_id?)
 *  - account_code values include: 'CHAUF_INCOME', 'RENTAL_INCOME', plus expense codes
 *  - chart_of_accounts(company_id, code, name, type='income'|'expense'|..., ...) is optional for labels
 *
 * Split parts:
 *  1) Bootstrap & guards
 *  2) Period window (current month, with month nav)
 *  3) Helpers (sum_tx, safe_query, series builders)
 *  4) KPI queries
 *  5) Chart datasets
 *  6) Render: Header + KPI cards + quick actions
 *  7) Render: Charts (line + pie)
 */

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

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

///////////////////////////////////////////////
// 2) Period window (current month + month nav)
///////////////////////////////////////////////
$today      = new DateTimeImmutable('today');

$monthParam = (string)($_GET['month'] ?? ''); // YYYY-MM
if ($monthParam && preg_match('/^\d{4}-\d{2}$/', $monthParam)) {
  $monthStart = DateTimeImmutable::createFromFormat('Y-m-d', $monthParam.'-01') ?: $today->modify('first day of this month');
} else {
  $monthStart = $today->modify('first day of this month');
}
$monthEnd = $monthStart->modify('last day of this month');

$fromYmd  = $monthStart->format('Y-m-d');
$toYmd    = $monthEnd->format('Y-m-d');

$prevMonth = $monthStart->modify('-1 month')->format('Y-m');
$nextMonth = $monthStart->modify('+1 month')->format('Y-m');

///////////////////////////////////////////////
// 3) Helpers (DB-safe wrappers & dataset builders)
///////////////////////////////////////////////
/** Run a prepared query safely; return fetched rows or [] on failure. */
function safe_query(string $sql, array $args = []): array {
  try {
    $st = db()->prepare($sql);
    $st->execute($args);
    return $st->fetchAll();
  } catch (Throwable $e) {
    return [];
  }
}

/** Sum of transactions for company and period; optionally filter by type and account_code(s). */
function sum_tx(int $cid, string $from, string $to, ?string $type = null, ?array $accountCodes = null): float {
  $w = ["company_id = :cid", "`date` BETWEEN :from AND :to"];
  $a = [':cid'=>$cid, ':from'=>$from, ':to'=>$to];

  if ($type !== null) { $w[] = "type = :type"; $a[':type'] = $type; }
  $accSql = '';
  if ($accountCodes && count($accountCodes) > 0) {
    $ph = [];
    foreach ($accountCodes as $i => $code) {
      $phKey = ":acc{$i}";
      $ph[] = $phKey;
      $a[$phKey] = $code;
    }
    $accSql = " AND account_code IN (".implode(',', $ph).")";
  }
  $sql = "SELECT COALESCE(SUM(amount),0) AS s FROM transactions WHERE ".implode(' AND ', $w) . $accSql . " LIMIT 1";
  try {
    $st = db()->prepare($sql);
    $st->execute($a);
    $val = $st->fetchColumn();
    return $val !== false ? (float)$val : 0.0;
  } catch (Throwable $e) {
    return 0.0;
  }
}

/** Build last N months labels like ['2024-11','2024-12',...,'2025-04'] ending at current month. */
function last_n_month_keys(DateTimeImmutable $endMonthStart, int $n = 6): array {
  $keys = [];
  for ($i = $n - 1; $i >= 0; $i--) {
    $m = $endMonthStart->modify("-{$i} months");
    $keys[] = $m->format('Y-m');
  }
  return $keys;
}

/** Income series for CHAUF vs RENTAL over last N months (grouping in SQL; fill zeros). */
function income_series_last_n(int $cid, DateTimeImmutable $monthStart, int $n = 6): array {
  $start = $monthStart->modify('-'.($n-1).' months')->format('Y-m-01');
  $end   = $monthStart->modify('last day of this month')->format('Y-m-d');

  $sql = "
    SELECT DATE_FORMAT(`date`, '%Y-%m') AS ym, account_code, COALESCE(SUM(amount),0) AS total
      FROM transactions
     WHERE company_id = :cid
       AND type = 'income'
       AND account_code IN ('CHAUF_INCOME','RENTAL_INCOME')
       AND `date` BETWEEN :from AND :to
     GROUP BY ym, account_code
     ORDER BY ym ASC
  ";
  $rows = safe_query($sql, [':cid'=>$cid, ':from'=>$start, ':to'=>$end]);

  $map = [];
  foreach ($rows as $r) {
    $ym = (string)$r['ym']; $acc = (string)$r['account_code']; $tot = (float)$r['total'];
    $map[$ym][$acc] = $tot;
  }
  $keys = last_n_month_keys(DateTimeImmutable::createFromFormat('Y-m-d', $start) ?: new DateTimeImmutable($start), $n);
  $labels  = [];
  $chaufs  = [];
  $rentals = [];
  foreach ($keys as $ym) {
    $labels[]  = $ym;
    $chaufs[]  = isset($map[$ym]['CHAUF_INCOME'])  ? (float)$map[$ym]['CHAUF_INCOME']  : 0.0;
    $rentals[] = isset($map[$ym]['RENTAL_INCOME']) ? (float)$map[$ym]['RENTAL_INCOME'] : 0.0;
  }
  return [$labels, $chaufs, $rentals];
}

/** Expense breakdown (this month): try COA join for names, else fallback to account_code. */
function expense_breakdown_this_month(int $cid, string $from, string $to): array {
  // Try with chart_of_accounts for prettier labels
  $sqlJoin = "
    SELECT COALESCE(coa.name, t.account_code) AS label, COALESCE(SUM(t.amount),0) AS total
      FROM transactions t
 LEFT JOIN chart_of_accounts coa
        ON coa.company_id = t.company_id
       AND coa.code       = t.account_code
     WHERE t.company_id = :cid
       AND t.type = 'expense'
       AND t.`date` BETWEEN :from AND :to
     GROUP BY label
     HAVING total > 0
     ORDER BY total DESC
     LIMIT 12
  ";
  try {
    $st = db()->prepare($sqlJoin);
    $st->execute([':cid'=>$cid, ':from'=>$from, ':to'=>$to]);
    $rows = $st->fetchAll();
  } catch (Throwable $e) {
    // Fallback without COA
    $rows = safe_query("
      SELECT t.account_code AS label, COALESCE(SUM(t.amount),0) AS total
        FROM transactions t
       WHERE t.company_id = :cid
         AND t.type = 'expense'
         AND t.`date` BETWEEN :from AND :to
       GROUP BY label
       HAVING total > 0
       ORDER BY total DESC
       LIMIT 12
    ", [':cid'=>$cid, ':from'=>$from, ':to'=>$to]);
  }

  $labels = [];
  $values = [];
  foreach ($rows as $r) {
    $labels[] = (string)$r['label'];
    $values[] = (float)$r['total'];
  }
  return [$labels, $values];
}

///////////////////////////////////////////////
// 4) KPI queries
///////////////////////////////////////////////
$chauffeurIncome = sum_tx($cid, $fromYmd, $toYmd, 'income', ['CHAUF_INCOME']);
$rentalIncome    = sum_tx($cid, $fromYmd, $toYmd, 'income', ['RENTAL_INCOME']);
$totalIncome     = $chauffeurIncome + $rentalIncome;
$totalExpenses   = sum_tx($cid, $fromYmd, $toYmd, 'expense', null); // all expenses (COGS + OPEX)
$netProfit       = $totalIncome - $totalExpenses;

///////////////////////////////////////////////
// 5) Chart datasets
///////////////////////////////////////////////
[$lineLabels, $lineChauf, $lineRental] = income_series_last_n($cid, $monthStart, 6);
[$pieLabels,  $pieValues]               = expense_breakdown_this_month($cid, $fromYmd, $toYmd);

// Pretty month label for header & for print report
$monthNice = $monthStart->format('F Y');
$periodLabel = 'Month — ' . $monthNice;

///////////////////////////////////////////////
// 6) Render: Header + KPI cards + quick actions
///////////////////////////////////////////////
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">Accounts Dashboard</h1>
    <div class="text-muted">Financial overview — <?= e($monthNice) ?></div>
  </div>
  <div class="d-flex flex-wrap gap-2">
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('accounts/index.php') . '?month=' . $prevMonth) ?>">&laquo; <?= e((new DateTimeImmutable($fromYmd))->modify('-1 month')->format('M Y')) ?></a>
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('accounts/index.php') . '?month=' . $nextMonth) ?>"><?= e((new DateTimeImmutable($fromYmd))->modify('+1 month')->format('M Y')) ?> &raquo;</a>

    <a class="btn btn-outline-secondary" href="<?= e(url_modules('payments/drivers.php')) ?>">🧾 Driver Payments</a>
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('accounts/ledger.php')) ?>">📑 Ledger</a>
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('accounts/expenses/list.php')) ?>">🧮 Expenses</a>
    <a class="btn btn-dark" href="<?= e(url_modules('accounts/pnl.php') . '?period=month&month=' . $monthStart->format('Y-m')) ?>">📊 P&L</a>

    <!-- NEW: Multi-Segment P&L buttons -->
    <a class="btn btn-outline-primary" target="_blank"
       href="<?= e(url_modules('accounts/pnl_multisegment.php') . '?' . http_build_query(['from'=>$fromYmd,'to'=>$toYmd,'period'=>$periodLabel])) ?>">
      📘 Multi-Segment P&L
    </a>
    <a class="btn btn-primary" target="_blank"
       href="<?= e(url_modules('accounts/pnl_multisegment_print.php') . '?' . http_build_query(['from'=>$fromYmd,'to'=>$toYmd,'period'=>$periodLabel])) ?>">
      🖨 Print Multi-Segment
    </a>
  </div>
</div>

<div class="row g-3">
  <div class="col-6 col-lg-3">
    <div class="card shadow-sm h-100">
      <div class="card-body d-flex justify-content-between align-items-center">
        <div>
          <div class="small text-muted">Chauffeur Income</div>
          <div class="display-6">£<?= number_format($chauffeurIncome, 2) ?></div>
        </div>
        <span class="badge text-bg-dark">CHAUF</span>
      </div>
    </div>
  </div>
  <div class="col-6 col-lg-3">
    <div class="card shadow-sm h-100">
      <div class="card-body d-flex justify-content-between align-items-center">
        <div>
          <div class="small text-muted">Rental Income</div>
          <div class="display-6">£<?= number_format($rentalIncome, 2) ?></div>
        </div>
        <span class="badge text-bg-dark">RENTAL</span>
      </div>
    </div>
  </div>
  <div class="col-6 col-lg-3">
    <div class="card shadow-sm h-100">
      <div class="card-body d-flex justify-content-between align-items-center">
        <div>
          <div class="small text-muted">Total Expenses</div>
          <div class="display-6">£<?= number_format($totalExpenses, 2) ?></div>
        </div>
        <span class="badge text-bg-secondary">EXP</span>
      </div>
    </div>
  </div>
  <div class="col-6 col-lg-3">
    <div class="card shadow-sm h-100">
      <div class="card-body d-flex justify-content-between align-items-center">
        <div>
          <div class="small text-muted">Net Profit</div>
          <?php $npBadge = $netProfit >= 0 ? 'success' : 'danger'; ?>
          <div class="display-6"><span class="badge text-bg-<?= e($npBadge) ?>">£<?= number_format($netProfit, 2) ?></span></div>
        </div>
        <span class="badge text-bg-light">NET</span>
      </div>
    </div>
  </div>
</div>

<!-- Quick shortcuts -->
<div class="card shadow-sm mt-3">
  <div class="card-body d-flex flex-wrap gap-2">
    <a class="btn btn-outline-primary" href="<?= e(url_modules('invoices/list.php')) ?>">Open Invoices</a>
    <a class="btn btn-outline-primary" href="<?= e(url_modules('accounts/income/add.php')) ?>">Add Manual Income</a>
    <a class="btn btn-outline-primary" href="<?= e(url_modules('accounts/expenses/add.php')) ?>">Add Expense</a>
    <a class="btn btn-outline-primary" href="<?= e(url_modules('accounts/pnl_actual.php')) ?>">P & L from Bookings</a>
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('payments/drivers.php') . '?status=unpaid&from=' . $fromYmd . '&to=' . $toYmd) ?>">Unpaid Driver Jobs (This Month)</a>
  </div>
</div>

<?php
///////////////////////////////////////////////
// 7) Charts (line + pie)
///////////////////////////////////////////////
?>
<div class="row g-3 mt-1">
  <div class="col-12 col-xl-7">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <h2 class="h6 mb-2">Income Trend — Chauffeur vs Rental (last 6 months)</h2>
        <canvas id="lineChart" height="120"></canvas>
        <div class="text-muted small mt-2">Shows the sum of income posted to <em>CHAUF_INCOME</em> and <em>RENTAL_INCOME</em>.</div>
      </div>
    </div>
  </div>
  <div class="col-12 col-xl-5">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <h2 class="h6 mb-2">Expense Breakdown — <?= e($monthNice) ?></h2>
        <canvas id="pieChart" height="120"></canvas>
        <div class="text-muted small mt-2">Top categories by total this month.</div>
      </div>
    </div>
  </div>
</div>

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

<!-- Chart.js (lightweight CDN) -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
(function(){
  // Data from PHP
  const lineLabels  = <?= json_encode($lineLabels) ?>;
  const lineChauf   = <?= json_encode(array_map(fn($v)=>round((float)$v,2), $lineChauf)) ?>;
  const lineRental  = <?= json_encode(array_map(fn($v)=>round((float)$v,2), $lineRental)) ?>;
  const pieLabels   = <?= json_encode($pieLabels) ?>;
  const pieValues   = <?= json_encode(array_map(fn($v)=>round((float)$v,2), $pieValues)) ?>;

  // Line chart
  const lc = document.getElementById('lineChart');
  if (lc) {
    new Chart(lc, {
      type: 'line',
      data: {
        labels: lineLabels,
        datasets: [
          { label: 'Chauffeur', data: lineChauf, tension: .3 },
          { label: 'Rental',    data: lineRental, tension: .3 }
        ]
      },
      options: {
        responsive: true,
        plugins: { legend: { position: 'bottom' } },
        scales: { y: { beginAtZero: true, ticks: { callback: v => '£' + v } } }
      }
    });
  }

  // Pie chart
  const pc = document.getElementById('pieChart');
  if (pc) {
    new Chart(pc, {
      type: 'pie',
      data: { labels: pieLabels, datasets: [{ data: pieValues }] },
      options: { responsive: true, plugins: { legend: { position: 'bottom' } } }
    });
  }
})();
</script>
