<?php
declare(strict_types=1);

/**
 * modules/accounts/pnl_actual.php
 *
 * Actual Profit & Loss (by Segment)
 *
 * Rules:
 *  - Chauffeur Income = from bookings:
 *      total_client_price + client_parking_fee + client_waiting_fee
 *  - Driver Cost (COGS, Chauffeur only) = from bookings:
 *      total_driver_price + driver_parking_fee + driver_waiting_fee
 *  - Rental Income = from transactions (RENTAL_INCOME)
 *  - All other expenses = from transactions (by account codes)
 *  - EXCLUDE driver payouts from transactions (DRIVER_PAYOUT) to avoid double-count
 *
 * Columns: Chauffeur, Rent, Untagged, Total
 * Filters: Month / Year / Date Range
 */

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'));

/////////////////////////////////////////////////////////
// 1) Period filters (month | year | range)
/////////////////////////////////////////////////////////
$today  = new DateTimeImmutable('today');
$period = (string)($_GET['period'] ?? 'month');

$from = $today->modify('first day of this month');
$to   = $today->modify('last day of this month');

if ($period === 'month' && isset($_GET['month']) && preg_match('/^\d{4}-\d{2}$/', $_GET['month'])) {
  $from = DateTimeImmutable::createFromFormat('Y-m-d', $_GET['month'].'-01');
  $to   = $from->modify('last day of this month');
} elseif ($period === 'year' && isset($_GET['year']) && preg_match('/^\d{4}$/', $_GET['year'])) {
  $year = (int)$_GET['year'];
  $from = new DateTimeImmutable("$year-01-01");
  $to   = new DateTimeImmutable("$year-12-31");
} elseif ($period === 'range' && isset($_GET['from'], $_GET['to']) &&
           preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['from']) &&
           preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['to'])) {
  $from = new DateTimeImmutable($_GET['from']);
  $to   = new DateTimeImmutable($_GET['to']);
}

$fromYmd = $from->format('Y-m-d');
$toYmd   = $to->format('Y-m-d');

/////////////////////////////////////////////////////////
// 2) Account code groupings (transactions-side)
/////////////////////////////////////////////////////////
$incomeMap = [
  // Chauffeur income comes from bookings, so we DO NOT read CHAUF_INCOME from transactions.
  'Rental Income' => ['RENTAL_INCOME'],
  'Other Income'  => ['OTHER_INCOME'],
];

$cogsTxMap = [
  // Driver cost is taken from bookings (Chauffeur only) — do NOT include DRIVER_PAYOUT here.
  'Commission Paid'                    => ['PARTNER_PAYOUT','PARTNER_COMM'],
  'Vehicle Repairs & Maintenance'      => ['COGS_VEH_REPAIR_MAINT','REPAIRS_MAINT'],
];

$opexMap = [
  'Advertising & Marketing'        => ['EXP_ADV','ADV_MARKETING'],
  'Bank Fees & Charges'            => ['EXP_BANK','BANK_FEES'],
  'Congestion Charges'             => ['EXP_CONGESTION','CONGESTION'],
  'Consultant Expense'             => ['EXP_CONSULTANT','CONSULTANCY'],
  'Fines & Penalties'              => ['EXP_FINES','FINES'],
  'Fuel Expense'                   => ['EXP_FUEL','FUEL'],
  'General Expenses'               => ['EXP_GENERAL','GENERAL_EXP'],
  'Insurance'                      => ['EXP_INSURANCE','INSURANCE'],
  'Office Supplies'                => ['EXP_OFFICE','OFFICE_SUPPLIES'],
  'Parking Cost'                   => ['EXP_PARKING','PARKING_COST'],
  'Rent Expense'                   => ['EXP_RENT','RENT'],
  'Road Tax'                       => ['EXP_ROADTAX','ROAD_TAX'],
  'Salaries & Employee Wages'      => ['EXP_SALARIES','STAFF_SALARY'],
  'Subscriptions'                  => ['EXP_SUBSCRIPTIONS','SUBSCRIPTIONS'],
  'Telephone Expense'              => ['EXP_TELEPHONE','TELEPHONE'],
  'Miscellaneous Expenses'         => ['EXP_MISC','MISCELLANEOUS'],
  'Other Expenses'                 => ['EXP_OTHER','OTHER EXPENSES'],
];

/////////////////////////////////////////////////////////
// 3) Helpers
/////////////////////////////////////////////////////////

/** Sum amounts from transactions by type, account_code list, and segment. */
function seg_sum_tx(int $cid, string $from, string $to, string $type, array $codes, ?string $segment): float {
  $where = ["company_id = :cid", "`date` BETWEEN :f AND :t", "type = :type"];
  $args  = [':cid'=>$cid, ':f'=>$from, ':t'=>$to, ':type'=>$type];

  // account_code IN (...)
  $accSql = '';
  if ($codes) {
    $ph = [];
    foreach ($codes as $i=>$code) {
      $k = ":ac$i"; $ph[] = $k; $args[$k] = $code;
    }
    $accSql = " AND account_code IN(".implode(',', $ph).")";
  }

  // Segment handling
  if ($segment === 'Chauffeur' || $segment === 'Rent') {
    $where[] = "segment = :seg"; $args[':seg'] = $segment;
  } elseif ($segment === 'Untagged') {
    $where[] = "(segment IS NULL OR segment = '')";
  }

  // EXCLUDE driver payouts categorically (only relevant when reading expenses)
  // This keeps us safe even if someone mistakenly put driver payouts under a broad code.
  $excludeDriverPayout = " AND account_code <> 'DRIVER_PAYOUT' ";

  $sql = "SELECT COALESCE(SUM(amount),0)
            FROM transactions
           WHERE ".implode(' AND ', $where)
         . $accSql
         . $excludeDriverPayout;

  try { $st = db()->prepare($sql); $st->execute($args); return (float)$st->fetchColumn(); }
  catch (Throwable $e) { return 0.0; }
}

/** Chauffeur sums from bookings for income & driver cost (COGS). */
function chauffeur_from_bookings(int $cid, string $from, string $to): array {
  // We treat 'Completed' as realised; include Invoiced/Paid if your ops mark later than completion.
  $statuses = ['Completed','Invoiced','Paid'];
  $ph = [];
  $args = [':cid'=>$cid, ':f'=>$from, ':t'=>$to];
  foreach ($statuses as $i=>$st) { $k=":s$i"; $ph[]=$k; $args[$k]=$st; }

  $sql = "
    SELECT
      COALESCE(SUM(total_client_price + client_parking_fee + client_waiting_fee),0)   AS client_total,
      COALESCE(SUM(total_driver_price + driver_parking_fee + driver_waiting_fee),0)   AS driver_total
    FROM bookings
    WHERE company_id = :cid
      AND pickup_date BETWEEN :f AND :t
      AND status IN (".implode(',', $ph).")
  ";

  try {
    $st = db()->prepare($sql);
    $st->execute($args);
    $row = $st->fetch() ?: ['client_total'=>0, 'driver_total'=>0];
    return [
      'income_chauf' => (float)$row['client_total'],
      'cogs_driver'  => (float)$row['driver_total'],
    ];
  } catch (Throwable $e) {
    return ['income_chauf'=>0.0, 'cogs_driver'=>0.0];
  }
}

/** Build grouped rows (transactions-backed groups). */
function group_tx_rows(int $cid, string $from, string $to, array $map, string $type, array $segments): array {
  $rows = [];
  foreach ($map as $label => $codes) {
    $sums = [];
    $total = 0.0;
    foreach ($segments as $seg) {
      $val = seg_sum_tx($cid, $from, $to, $type, $codes, $seg);
      $sums[$seg] = $val; $total += $val;
    }
    $rows[] = ['label'=>$label] + $sums + ['Total'=>$total];
  }
  return $rows;
}

function col_sum(array $rows, string $col): float {
  $s = 0.0; foreach ($rows as $r) $s += (float)($r[$col] ?? 0.0); return $s;
}

/////////////////////////////////////////////////////////
// 4) Compute P&L blocks
/////////////////////////////////////////////////////////
$segments = ['Chauffeur','Rent','Untagged'];

// A) Chauffeur: Bookings-based Income & Driver Cost
$bkAgg = chauffeur_from_bookings($cid, $fromYmd, $toYmd);

// Build two synthetic rows that are NOT transaction-backed
$incomeRows = [];
$incomeRows[] = [
  'label'     => 'Chauffeur Income (from bookings)',
  'Chauffeur' => $bkAgg['income_chauf'],
  'Rent'      => 0.0,
  'Untagged'  => 0.0,
  'Total'     => $bkAgg['income_chauf'],
];

// Add transaction-backed income groups (e.g., Rental, Other)
$incomeRows = array_merge(
  $incomeRows,
  group_tx_rows($cid, $fromYmd, $toYmd, $incomeMap, 'income', $segments)
);

// B) COGS:
//  - Driver Cost from bookings (Chauffeur only)
//  - Other COGS items from transactions (e.g., Partner Commissions, Repairs & Maint)
$cogsRows = [];
$cogsRows[] = [
  'label'     => 'Driver Cost (from bookings)',
  'Chauffeur' => $bkAgg['cogs_driver'],
  'Rent'      => 0.0,
  'Untagged'  => 0.0,
  'Total'     => $bkAgg['cogs_driver'],
];
$cogsRows = array_merge(
  $cogsRows,
  group_tx_rows($cid, $fromYmd, $toYmd, $cogsTxMap, 'expense', $segments)
);

// C) OPEX — all from transactions
$opexRows = group_tx_rows($cid, $fromYmd, $toYmd, $opexMap, 'expense', $segments);

// Totals per column
$totals = [];
foreach (array_merge($segments, ['Total']) as $col) {
  $totals['income'][$col] = col_sum($incomeRows, $col);
  $totals['cogs'][$col]   = col_sum($cogsRows,   $col);
  $totals['opex'][$col]   = col_sum($opexRows,   $col);
  $totals['gross'][$col]  = $totals['income'][$col] - $totals['cogs'][$col];
  $totals['net'][$col]    = $totals['gross'][$col]  - $totals['opex'][$col];
}

/////////////////////////////////////////////////////////
// 5) Render
/////////////////////////////////////////////////////////
include dirname(__DIR__, 2) . '/includes/header.php';

// You can override these with real company details if available in settings
$companyName = "Brownhill Group Limited";
$companyAddr = "Suite 109, 4-6 Wadsworth Road, Perivale, UB6 7JJ";
$companyWeb  = "www.brown-hillchauffeurs.co.uk";
$logoUrl     = url_public('assets/logo.png');
?>
<div class="d-flex justify-content-between align-items-center mb-3">
  <div>
    <h1 class="h4 mb-1">Actual Profit &amp; Loss</h1>
    <div class="text-muted">Period: <?= e($fromYmd) ?> &rarr; <?= e($toYmd) ?></div>
  </div>
  <div class="d-flex flex-wrap gap-2">
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('accounts/index.php')) ?>">← Accounts</a>
    <button class="btn btn-dark" onclick="window.print()">🖨️ Print</button>
  </div>
</div>

<form method="get" class="row g-2 align-items-end mb-3">
  <div class="col-md-2">
    <label class="form-label">Mode</label>
    <select class="form-select" name="period" id="periodSelect">
      <option value="month" <?= $period==='month'?'selected':'' ?>>Month</option>
      <option value="year"  <?= $period==='year'?'selected':'' ?>>Year</option>
      <option value="range" <?= $period==='range'?'selected':'' ?>>Date Range</option>
    </select>
  </div>
  <div class="col-md-3 js-month" style="<?= $period==='month'?'':'display:none' ?>">
    <label class="form-label">Month</label>
    <input type="month" class="form-control" name="month" value="<?= e($from->format('Y-m')) ?>">
  </div>
  <div class="col-md-3 js-year" style="<?= $period==='year'?'':'display:none' ?>">
    <label class="form-label">Year</label>
    <input type="number" class="form-control" name="year" value="<?= e($from->format('Y')) ?>">
  </div>
  <div class="col-md-2 js-range" style="<?= $period==='range'?'':'display:none' ?>">
    <label class="form-label">From</label>
    <input type="date" class="form-control" name="from" value="<?= e($fromYmd) ?>">
  </div>
  <div class="col-md-2 js-range" style="<?= $period==='range'?'':'display:none' ?>">
    <label class="form-label">To</label>
    <input type="date" class="form-control" name="to" value="<?= e($toYmd) ?>">
  </div>
  <div class="col-md-2">
    <label class="form-label">&nbsp;</label>
    <button class="btn btn-dark w-100">Apply</button>
  </div>
</form>

<div class="card shadow-sm">
  <div class="card-body table-responsive">
    <div class="d-flex align-items-center gap-3 mb-2">
      <img src="<?= e($logoUrl) ?>" alt="Logo" style="height:38px" onerror="this.style.display='none'">
      <div class="small text-muted">
        <div class="fw-semibold"><?= e($companyName) ?></div>
        <div><?= e($companyAddr) ?></div>
        <div><?= e($companyWeb) ?></div>
      </div>
    </div>

    <table class="table table-bordered align-middle mb-0">
      <thead class="table-light text-center">
        <tr>
          <th style="width:32%">Account</th>
          <th style="width:17%">Chauffeur</th>
          <th style="width:17%">Rent</th>
          <th style="width:17%">Untagged</th>
          <th style="width:17%">Total</th>
        </tr>
      </thead>
      <tbody>
        <!-- INCOME -->
        <tr><th colspan="5" class="bg-light">Operating Income</th></tr>
        <?php foreach ($incomeRows as $r): ?>
          <tr>
            <td><?= e($r['label']) ?></td>
            <?php foreach (array_merge($segments, ['Total']) as $c): ?>
              <td class="text-end">£<?= number_format((float)$r[$c], 2) ?></td>
            <?php endforeach; ?>
          </tr>
        <?php endforeach; ?>
        <tr class="fw-semibold">
          <td>Total Income</td>
          <?php foreach (array_merge($segments, ['Total']) as $c): ?>
            <td class="text-end">£<?= number_format($totals['income'][$c], 2) ?></td>
          <?php endforeach; ?>
        </tr>

        <!-- COGS -->
        <tr><th colspan="5" class="bg-light">Cost of Goods Sold</th></tr>
        <?php foreach ($cogsRows as $r): ?>
          <tr>
            <td><?= e($r['label']) ?></td>
            <?php foreach (array_merge($segments, ['Total']) as $c): ?>
              <td class="text-end">£<?= number_format((float)$r[$c], 2) ?></td>
            <?php endforeach; ?>
          </tr>
        <?php endforeach; ?>
        <tr class="fw-semibold">
          <td>Total COGS</td>
          <?php foreach (array_merge($segments, ['Total']) as $c): ?>
            <td class="text-end">£<?= number_format($totals['cogs'][$c], 2) ?></td>
          <?php endforeach; ?>
        </tr>

        <tr class="table-group-divider"><td colspan="5"></td></tr>
        <tr class="fw-bold bg-secondary text-white">
          <td>Gross Profit</td>
          <?php foreach (array_merge($segments, ['Total']) as $c): ?>
            <td class="text-end">£<?= number_format($totals['gross'][$c], 2) ?></td>
          <?php endforeach; ?>
        </tr>

        <!-- OPEX -->
        <tr><th colspan="5" class="bg-light">Operating Expenses</th></tr>
        <?php foreach ($opexRows as $r): ?>
          <tr>
            <td><?= e($r['label']) ?></td>
            <?php foreach (array_merge($segments, ['Total']) as $c): ?>
              <td class="text-end">£<?= number_format((float)$r[$c], 2) ?></td>
            <?php endforeach; ?>
          </tr>
        <?php endforeach; ?>
        <tr class="fw-semibold">
          <td>Total OPEX</td>
          <?php foreach (array_merge($segments, ['Total']) as $c): ?>
            <td class="text-end">£<?= number_format($totals['opex'][$c], 2) ?></td>
          <?php endforeach; ?>
        </tr>

        <tr class="table-group-divider"><td colspan="5"></td></tr>
        <tr class="fw-bold bg-dark text-white">
          <td>Net Profit</td>
          <?php foreach (array_merge($segments, ['Total']) as $c): ?>
            <td class="text-end">£<?= number_format($totals['net'][$c], 2) ?></td>
          <?php endforeach; ?>
        </tr>
      </tbody>
    </table>
  </div>
</div>

<style>
/* Tidy spacing for print + screen */
.table th, .table td { vertical-align: middle; }

/* PRINT LAYOUT */
@media print {
  @page { size: A4; margin: 14mm; }
  body { background: #fff !important; -webkit-print-color-adjust: exact; print-color-adjust: exact; }
  .btn, form, .d-flex.justify-content-between { display: none !important; }
  .card { border: none !important; box-shadow: none !important; }
  .table th, .table td { font-size: 11pt; }
}
</style>

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

<script>
(function(){
  const sel = document.getElementById('periodSelect');
  const toggle = (cls, on) => document.querySelectorAll('.' + cls).forEach(el => el.style.display = on ? '' : 'none');
  function updateUI(){
    const v = sel?.value || 'month';
    toggle('js-month', v === 'month');
    toggle('js-year',  v === 'year');
    toggle('js-range', v === 'range');
  }
  sel?.addEventListener('change', updateUI);
  updateUI();
})();
</script>
