<?php
declare(strict_types=1);

/**
 * modules/payroll/history.php
 * Salaries & Advances history with filters (works with staff_salaries / staff_advances).
 */

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

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

$errors = [];
$tab    = (string)($_GET['tab'] ?? 'salaries'); // 'salaries' | 'advances'
$staffId= (int)($_GET['staff_id'] ?? 0);

// Default range = current month
$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);
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $from)) $from = $monthStart;
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $to))   $to   = $monthEnd;

/** Small helpers for anchored periods */
function safe_anchor_dt(int $year, int $month, int $day): DateTimeImmutable {
  // Normalize month wrap and clamp day to month's last day
  $dt   = new DateTimeImmutable(sprintf('%04d-%02d-01', $year, $month));
  $last = (int)$dt->format('t');
  $d    = max(1, min($day, $last));
  return new DateTimeImmutable($dt->format('Y-m-') . sprintf('%02d', $d));
}
function prev_anchor(DateTimeImmutable $anchor, int $day): DateTimeImmutable {
  $pm   = $anchor->modify('-1 month');
  $last = (int)$pm->format('t');
  $d    = max(1, min($day, $last));
  return new DateTimeImmutable($pm->format('Y-m-') . sprintf('%02d', $d));
}
function next_anchor_after(DateTimeImmutable $date, int $day): DateTimeImmutable {
  // First anchor strictly after $date
  $y = (int)$date->format('Y'); $m = (int)$date->format('m');
  $cur = safe_anchor_dt($y, $m, $day);
  if ($date < $cur) return $cur;
  // if date >= cur, next month’s anchor
  $nm = (int)$date->modify('+1 month')->format('m');
  $ny = (int)$date->modify('+1 month')->format('Y');
  return safe_anchor_dt($ny, $nm, $day);
}
function segment_from_department(?string $dep): string {
  return (strtolower((string)$dep) === 'rent') ? 'Rent' : 'Chauffeur';
}

/** Staff list for filter */
$staffList = [];
try {
  $q = db()->prepare("
    SELECT id, full_name
      FROM staff_members
     WHERE company_id = :cid
     ORDER BY full_name ASC
  ");
  $q->execute([':cid'=>$cid]);
  $staffList = $q->fetchAll() ?: [];
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to load staff list.';
}

$total = 0.0;
$rows  = [];

try {
  if ($tab === 'salaries') {
    // Load salaries in date range, join staff for department / salary_day / salary_amount
    $where = ["s.company_id = :cid", "DATE(s.paid_at) BETWEEN :f AND :t"];
    $args  = [':cid'=>$cid, ':f'=>$from, ':t'=>$to];
    if ($staffId > 0) { $where[] = "s.staff_id = :sid"; $args[':sid'] = $staffId; }

    $sql = "
      SELECT
        s.*,
        st.full_name, st.job_title, st.department, st.salary_day, st.salary_amount
      FROM staff_salaries s
      JOIN staff_members st ON st.id = s.staff_id AND st.company_id = s.company_id
      WHERE ".implode(' AND ', $where)."
      ORDER BY s.paid_at DESC, s.id DESC
      LIMIT 1000
    ";
    $q = db()->prepare($sql);
    $q->execute($args);
    $baseRows = $q->fetchAll() ?: [];

    // Pre-prepare advance sum stmt to reuse per row
    $advStmt = db()->prepare("
      SELECT COALESCE(SUM(amount),0)
        FROM staff_advances
       WHERE company_id=:cid AND staff_id=:sid
         AND DATE(paid_at) BETWEEN :from AND :to
    ");

    foreach ($baseRows as $r) {
      $paidAt = new DateTimeImmutable($r['paid_at']);
      $salaryDay = (int)($r['salary_day'] ?? 25);
      if ($salaryDay <= 0 || $salaryDay > 31) $salaryDay = 25;

      // Salary period (non-strict anchor for salaries)
      $y = (int)$paidAt->format('Y'); $m = (int)$paidAt->format('m');
      $anchorCur  = safe_anchor_dt($y, $m, $salaryDay);
      $anchorPrev = prev_anchor($anchorCur, $salaryDay);
      $periodFrom = $anchorPrev->format('Y-m-d');
      $periodTo   = $anchorCur->modify('-1 day')->format('Y-m-d');
      $payMonth   = $anchorCur->format('Y-m');

      // Advances in that period
      $advStmt->execute([
        ':cid'=>$cid, ':sid'=>$r['staff_id'],
        ':from'=>$periodFrom, ':to'=>$periodTo
      ]);
      $advInPeriod = (float)$advStmt->fetchColumn();

      $segment = segment_from_department($r['department'] ?? null);

      $rows[] = [
        'staff_id'     => (int)$r['staff_id'],
        'full_name'    => (string)$r['full_name'],
        'job_title'    => (string)($r['job_title'] ?? ''),
        'department'   => (string)($r['department'] ?? ''),
        'segment'      => $segment,
        'paid_at'      => (string)$r['paid_at'],
        'period_from'  => $periodFrom,
        'period_to'    => $periodTo,
        'pay_month'    => $payMonth,
        'base_salary'  => (float)($r['salary_amount'] ?? 0),
        'advances'     => $advInPeriod,
        'net_paid'     => (float)$r['amount'],
        'method'       => (string)($r['method'] ?? ''),
        'reference'    => (string)($r['reference'] ?? ''),
      ];
      $total += (float)$r['amount'];
    }

  } else { // advances
    $where = ["a.company_id = :cid", "DATE(a.paid_at) BETWEEN :f AND :t"];
    $args  = [':cid'=>$cid, ':f'=>$from, ':t'=>$to];
    if ($staffId > 0) { $where[] = "a.staff_id = :sid"; $args[':sid'] = $staffId; }

    $sql = "
      SELECT
        a.*,
        st.full_name, st.job_title, st.department, st.salary_day
      FROM staff_advances a
      JOIN staff_members st ON st.id = a.staff_id AND st.company_id = a.company_id
      WHERE ".implode(' AND ', $where)."
      ORDER BY a.paid_at DESC, a.id DESC
      LIMIT 1000
    ";
    $q = db()->prepare($sql);
    $q->execute($args);
    $baseRows = $q->fetchAll() ?: [];

    foreach ($baseRows as $r) {
      $paidAt = new DateTimeImmutable($r['paid_at']);
      $salaryDay = (int)($r['salary_day'] ?? 25);
      if ($salaryDay <= 0 || $salaryDay > 31) $salaryDay = 25;

      // Advance belongs to the period that ends at the first anchor STRICTLY AFTER the advance date
      $anchorNext = next_anchor_after($paidAt, $salaryDay);     // first anchor > paidAt
      $anchorPrev = prev_anchor($anchorNext, $salaryDay);
      $periodFrom = $anchorPrev->format('Y-m-d');
      $periodTo   = $anchorNext->modify('-1 day')->format('Y-m-d');
      $payMonth   = $anchorNext->format('Y-m');                 // label like salaries

      $segment = segment_from_department($r['department'] ?? null);

      $rows[] = [
        'staff_id'     => (int)$r['staff_id'],
        'full_name'    => (string)$r['full_name'],
        'job_title'    => (string)($r['job_title'] ?? ''),
        'department'   => (string)($r['department'] ?? ''),
        'segment'      => $segment,
        'paid_at'      => (string)$r['paid_at'],
        'period_from'  => $periodFrom,
        'period_to'    => $periodTo,
        'pay_month'    => $payMonth,
        'amount'       => (float)$r['amount'],
        'method'       => (string)($r['method'] ?? ''),
        'reference'    => (string)($r['reference'] ?? ''),
      ];
      $total += (float)$r['amount'];
    }
  }
} catch (Throwable $e) {
  $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to load history.';
}

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">Payroll History</h1>
    <div class="text-muted">Salaries and advances (anchored periods by staff salary day).</div>
  </div>
  <div>
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('payroll/staff/list.php')) ?>">← Staff</a>
  </div>
</div>

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

<ul class="nav nav-pills mb-3">
  <li class="nav-item">
    <a class="nav-link <?= $tab==='salaries'?'active':'' ?>" href="?tab=salaries&from=<?= e($from) ?>&to=<?= e($to) ?>&staff_id=<?= (int)$staffId ?>">Salaries</a>
  </li>
  <li class="nav-item">
    <a class="nav-link <?= $tab==='advances'?'active':'' ?>" href="?tab=advances&from=<?= e($from) ?>&to=<?= e($to) ?>&staff_id=<?= (int)$staffId ?>">Advances</a>
  </li>
</ul>

<div class="card shadow-sm mb-3">
  <div class="card-body">
    <form class="row g-2 align-items-end" method="get">
      <input type="hidden" name="tab" value="<?= e($tab) ?>">
      <div class="col-md-3">
        <label class="form-label">From</label>
        <input type="date" class="form-control" name="from" value="<?= e($from) ?>">
      </div>
      <div class="col-md-3">
        <label class="form-label">To</label>
        <input type="date" class="form-control" name="to" value="<?= e($to) ?>">
      </div>
      <div class="col-md-4">
        <label class="form-label">Staff</label>
        <select class="form-select" name="staff_id">
          <option value="0">— All —</option>
          <?php foreach ($staffList as $s): ?>
            <option value="<?= (int)$s['id'] ?>" <?= $staffId==(int)$s['id']?'selected':'' ?>>
              <?= e($s['full_name']) ?>
            </option>
          <?php endforeach; ?>
        </select>
      </div>
      <div class="col-md-2 d-grid">
        <button class="btn btn-dark">Filter</button>
      </div>
    </form>
  </div>
</div>

<div class="card shadow-sm">
  <div class="card-body">

    <div class="d-flex justify-content-between align-items-center mb-2">
      <div class="small text-muted">
        Total <?= $tab==='salaries' ? 'Net Salaries' : 'Advances' ?> (Filtered)
      </div>
      <div class="fw-bold">£<?= number_format($total, 2) ?></div>
    </div>

    <div class="table-responsive">
      <table class="table align-middle mb-0">
        <thead class="table-light">
        <?php if ($tab === 'salaries'): ?>
          <tr>
            <th>Staff</th>
            <th>Department</th>
            <th>Period</th>
            <th>Pay Month</th>
            <th>Paid At</th>
            <th class="text-end">£ Base</th>
            <th class="text-end">£ Advances</th>
            <th class="text-end">£ Net Paid</th>
            <th>Method</th>
            <th>Reference</th>
          </tr>
        <?php else: ?>
          <tr>
            <th>Staff</th>
            <th>Department</th>
            <th>Period</th>
            <th>Pay Month</th>
            <th>Paid At</th>
            <th class="text-end">£ Amount</th>
            <th>Method</th>
            <th>Reference</th>
          </tr>
        <?php endif; ?>
        </thead>
        <tbody>
        <?php if ($rows): foreach ($rows as $r): ?>
          <?php if ($tab === 'salaries'): ?>
            <tr>
              <td>
                <div class="fw-semibold"><?= e($r['full_name']) ?></div>
                <div class="small text-muted"><?= e($r['job_title'] ?: '—') ?></div>
              </td>
              <td><?= e($r['department'] ?: '—') ?></td>
              <td><?= e(date('d M Y', strtotime($r['period_from']))) ?> → <?= e(date('d M Y', strtotime($r['period_to']))) ?></td>
              <td><?= e($r['pay_month']) ?></td>
              <td><?= e(date('Y-m-d H:i', strtotime($r['paid_at']))) ?></td>
              <td class="text-end">£<?= number_format((float)$r['base_salary'], 2) ?></td>
              <td class="text-end">£<?= number_format((float)$r['advances'], 2) ?></td>
              <td class="text-end">£<?= number_format((float)$r['net_paid'], 2) ?></td>
              <td><?= e($r['method'] ?: '—') ?></td>
              <td><?= e($r['reference'] ?: '—') ?></td>
            </tr>
          <?php else: ?>
            <tr>
              <td>
                <div class="fw-semibold"><?= e($r['full_name']) ?></div>
                <div class="small text-muted"><?= e($r['job_title'] ?: '—') ?></div>
              </td>
              <td><?= e($r['department'] ?: '—') ?></td>
              <td><?= e(date('d M Y', strtotime($r['period_from']))) ?> → <?= e(date('d M Y', strtotime($r['period_to']))) ?></td>
              <td><?= e($r['pay_month']) ?></td>
              <td><?= e(date('Y-m-d H:i', strtotime($r['paid_at']))) ?></td>
              <td class="text-end">£<?= number_format((float)$r['amount'], 2) ?></td>
              <td><?= e($r['method'] ?: '—') ?></td>
              <td><?= e($r['reference'] ?: '—') ?></td>
            </tr>
          <?php endif; ?>
        <?php endforeach; else: ?>
          <tr><td colspan="10" class="text-center text-muted py-4">No records found.</td></tr>
        <?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>

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