<?php
declare(strict_types=1);

/**
 * modules/dashboard/md.php
 * MD (Finance) Dashboard — KPIs + quick finance actions + Sales Target
 */

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;

// Use HTML entity to avoid mojibake for currency
$GBP = '&pound;';

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

/** Ensure sales_targets table exists. One row per company/month. */
function ensure_sales_targets_table(): void {
  if (tbl_exists('sales_targets')) return;
  db()->exec("
    CREATE TABLE IF NOT EXISTS sales_targets (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      company_id INT(10) UNSIGNED NOT NULL,
      year SMALLINT UNSIGNED NOT NULL,
      month TINYINT UNSIGNED NOT NULL,       -- 1..12
      target_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
      created_by INT(10) UNSIGNED DEFAULT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      UNIQUE KEY uq_company_period (company_id, year, month),
      KEY idx_company (company_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  ");
}

/** Get or create the row for (company, year, month). 
 *  If missing, copy most recent prior month’s target_amount (auto-renew).
 */
function get_or_create_month_target(int $cid, int $year, int $month, ?int $userId = null): array {
  $q = db()->prepare("SELECT * FROM sales_targets WHERE company_id=:c AND year=:y AND month=:m LIMIT 1");
  $q->execute([':c'=>$cid, ':y'=>$year, ':m'=>$month]);
  $row = $q->fetch();
  if ($row) return $row;

  // copy prior most-recent target
  $p = db()->prepare("
    SELECT target_amount
      FROM sales_targets
     WHERE company_id=:c
     ORDER BY year DESC, month DESC
     LIMIT 1
  ");
  $p->execute([':c'=>$cid]);
  $prev = $p->fetchColumn();
  $amount = $prev !== false ? (float)$prev : 0.00;

  $ins = db()->prepare("
    INSERT INTO sales_targets (company_id, year, month, target_amount, created_by)
    VALUES (:c,:y,:m,:a,:u)
  ");
  $ins->execute([':c'=>$cid, ':y'=>$year, ':m'=>$month, ':a'=>$amount, ':u'=>$userId ?: null]);

  $q->execute([':c'=>$cid, ':y'=>$year, ':m'=>$month]);
  return $q->fetch();
}

/** Sum monthly achieved sales (Completed bookings only) — client price only */
function sum_month_achieved(int $cid, string $fromYmd, string $toYmd): float {
  // FIX: keep it strictly Completed; do NOT add any cancelled-only predicate
  $q = db()->prepare("
    SELECT COALESCE(SUM(b.total_client_price),0)
      FROM bookings b
     WHERE b.company_id = :cid
       AND b.pickup_date BETWEEN :from AND :to
       AND b.status = 'Completed'
  ");
  $q->execute([':cid'=>$cid, ':from'=>$fromYmd, ':to'=>$toYmd]);
  return (float)$q->fetchColumn();
}

/** Sum achieved sales in arbitrary range (Completed bookings, client price only) */
function sum_achieved_range(int $cid, string $fromYmd, string $toYmd): float {
  $q = db()->prepare("
    SELECT COALESCE(SUM(b.total_client_price),0)
      FROM bookings b
     WHERE b.company_id = :cid
       AND b.pickup_date BETWEEN :from AND :to
       AND b.status = 'Completed'
  ");
  $q->execute([':cid'=>$cid, ':from'=>$fromYmd, ':to'=>$toYmd]);
  return (float)$q->fetchColumn();
}

/** Sum of targets over months [Y1-M1 .. Y2-M2] */
function sum_targets_over_months(int $cid, DateTimeImmutable $start, DateTimeImmutable $end): float {
  // Gather (year, month) list
  $cur = $start->modify('first day of this month');
  $endMonth = $end->modify('first day of this month');
  $pairs = [];
  while ($cur <= $endMonth) {
    $pairs[] = [$cur->format('Y'), $cur->format('n')];
    $cur = $cur->modify('+1 month');
  }
  if (!$pairs) return 0.0;

  // Ensure rows exist (auto-renew) then sum
  $sum = 0.0;
  foreach ($pairs as [$y,$m]) {
    $row = get_or_create_month_target($cid, (int)$y, (int)$m, (int)(current_user()['id'] ?? 0));
    $sum += (float)$row['target_amount'];
  }
  return $sum;
}

/* =========================================================
   Time window
   ========================================================= */
$today      = new DateTimeImmutable('today');
$monthStart = $today->modify('first day of this month');
$monthEnd   = $today->modify('last day of this month');
$fromYmd    = $monthStart->format('Y-m-d');
$toYmd      = $monthEnd->format('Y-m-d');
$year       = (int)$today->format('Y');
$month      = (int)$today->format('n');

/* =========================================================
   Sales Target: table + row + update handler
   ========================================================= */
try { ensure_sales_targets_table(); } catch (Throwable $e) { $errors[] = 'Unable to ensure sales_targets table.'; }

$targetRow = get_or_create_month_target($cid, $year, $month, (int)($user['id'] ?? 0));
$targetAmount = (float)($targetRow['target_amount'] ?? 0.00);

if ($_SERVER['REQUEST_METHOD']==='POST' && ($_POST['action'] ?? '') === 'set_target') {
  try {
    csrf_verify((string)($_POST['csrf'] ?? ''));
    $newTarget = (float)($_POST['target_amount'] ?? 0);
    $up = db()->prepare("UPDATE sales_targets SET target_amount=:a, updated_at=NOW() WHERE id=:id AND company_id=:cid");
    $up->execute([':a'=>$newTarget, ':id'=>(int)$targetRow['id'], ':cid'=>$cid]);
    $targetAmount = $newTarget;
    $notice = 'Monthly sales target updated.';
  } catch (Throwable $e) {
    $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to update target.';
  }
}

/* =========================================================
   Existing KPIs (kept)
   ========================================================= */

// Bookings count (non-cancelled)
$bookingsThisMonth = 0;
try {
  $q = db()->prepare("
    SELECT COUNT(*) FROM bookings
     WHERE company_id=:cid
       AND pickup_date BETWEEN :from AND :to
       AND (status IS NULL OR status <> 'Cancelled')
  ");
  $q->execute([':cid'=>$cid, ':from'=>$fromYmd, ':to'=>$toYmd]);
  $bookingsThisMonth = (int)$q->fetchColumn();
} catch (Throwable $e) {}

// Active rentals (best-effort)
$activeRentals = 0;
try {
  $q = db()->prepare("SELECT COUNT(*) FROM rentals WHERE company_id=:cid AND (status='Active' OR status='active')");
  $q->execute([':cid'=>$cid]);
  $activeRentals = (int)$q->fetchColumn();
} catch (Throwable $e) {}

/** Dynamic partner commission expression */
$commissionExpr = "
  CASE
    WHEN b.partner_id IS NULL THEN 0
    WHEN COALESCE(b.partner_commission_amount,0) > 0
      THEN COALESCE(b.partner_commission_amount,0)
    ELSE
      CASE
        WHEN p.commission_type = 'fixed'
          THEN COALESCE(p.commission_value,0)
        WHEN p.commission_type = 'percent'
          THEN (COALESCE(p.commission_value,0)/100.0) * COALESCE(b.total_client_price,0)
        ELSE 0
      END
  END
";
$nonCancelledWhere = "(b.status IS NULL OR b.status <> 'Cancelled')";

// Commission (this month, non-cancelled)
$commissionThisMonth = 0.0;
try {
  $sql = "
    SELECT COALESCE(SUM($commissionExpr),0)
      FROM bookings b
      LEFT JOIN partners p ON p.id = b.partner_id
     WHERE b.company_id = :cid
       AND b.pickup_date BETWEEN :from AND :to
       AND $nonCancelledWhere
  ";
  $q = db()->prepare($sql);
  $q->execute([':cid'=>$cid, ':from'=>$fromYmd, ':to'=>$toYmd]);
  $commissionThisMonth = (float)$q->fetchColumn();
} catch (Throwable $e) {}

// Profit (this month, non-cancelled)
$totalProfitThisMonth = 0.0;
try {
  $sql = "
    SELECT COALESCE(SUM(
      (COALESCE(b.total_client_price,0) + COALESCE(b.client_parking_fee,0) + COALESCE(b.client_waiting_fee,0))
      - (COALESCE(b.total_driver_price,0) + COALESCE(b.driver_parking_fee,0) + COALESCE(b.driver_waiting_fee,0))
      - ($commissionExpr)
    ),0)
    FROM bookings b
    LEFT JOIN partners p ON p.id = b.partner_id
    WHERE b.company_id = :cid
      AND b.pickup_date BETWEEN :from AND :to
      AND $nonCancelledWhere
  ";
  $q = db()->prepare($sql);
  $q->execute([':cid'=>$cid, ':from'=>$fromYmd, ':to'=>$toYmd]);
  $totalProfitThisMonth = (float)$q->fetchColumn();
} catch (Throwable $e) {}

// Unpaid driver jobs snapshot (your original quick card)
$unpaidDriverJobs = 0;
try {
  $q = db()->prepare("
    SELECT COUNT(*)
      FROM bookings
     WHERE company_id = :cid
       AND pickup_date BETWEEN :from AND :to
       AND status IN ('Completed','Invoiced')
       AND COALESCE(total_driver_price,0) > 0
  ");
  $q->execute([':cid'=>$cid, ':from'=>$fromYmd, ':to'=>$toYmd]);
  $unpaidDriverJobs = (int)$q->fetchColumn();
} catch (Throwable $e) {}

/* =========================================================
   Sales Target math (progress + summaries)
   ========================================================= */
$achievedThisMonth = sum_month_achieved($cid, $fromYmd, $toYmd);
$remaining = max(0.0, round($targetAmount - $achievedThisMonth, 2));
$achPct = ($targetAmount > 0) ? min(100.0, max(0.0, ($achievedThisMonth / $targetAmount) * 100.0)) : 0.0;
$remPct = ($targetAmount > 0) ? max(0.0, 100.0 - $achPct) : 0.0;

// Quarter + Year summaries
$quarterStart = (function(DateTimeImmutable $d){
  $m = (int)$d->format('n');
  $qStartMonth = (int)(floor(($m-1)/3)*3 + 1);
  return new DateTimeImmutable($d->format('Y').'-'.sprintf('%02d',$qStartMonth).'-01');
})($today);
$quarterEnd = (clone $quarterStart)->modify('+2 months')->modify('last day of this month');

$yearStart = new DateTimeImmutable($today->format('Y').'-01-01');
$yearEnd   = new DateTimeImmutable($today->format('Y').'-12-31');

try {
  $targetQuarter  = sum_targets_over_months($cid, $quarterStart, $quarterEnd);
  $achievedQuarter= sum_achieved_range($cid, $quarterStart->format('Y-m-d'), $quarterEnd->format('Y-m-d'));
  $targetYear     = sum_targets_over_months($cid, $yearStart, $yearEnd);
  $achievedYear   = sum_achieved_range($cid, $yearStart->format('Y-m-d'), $yearEnd->format('Y-m-d'));
} catch (Throwable $e) {
  $targetQuarter = $achievedQuarter = $targetYear = $achievedYear = 0.0;
}

/* =========================================================
   Recent jobs (kept)
   ========================================================= */
$recent = [];
try {
  $sql = "
    SELECT b.id, b.booking_ref, b.client_name, b.pickup_date, b.pickup_time,
           COALESCE(b.total_client_price,0) AS c,
           COALESCE(b.total_driver_price,0) AS d,
           COALESCE(b.client_parking_fee,0) AS cp,
           COALESCE(b.client_waiting_fee,0) AS cw,
           COALESCE(b.driver_parking_fee,0) AS dp,
           COALESCE(b.driver_waiting_fee,0) AS dw,
           $commissionExpr AS commission_calc
      FROM bookings b
      LEFT JOIN partners p ON p.id = b.partner_id
     WHERE b.company_id=:cid
       AND b.pickup_date BETWEEN :from AND :to
       AND $nonCancelledWhere
     ORDER BY b.pickup_date DESC, b.pickup_time DESC, b.id DESC
     LIMIT 8
  ";
  $q = db()->prepare($sql);
  $q->execute([':cid'=>$cid, ':from'=>$fromYmd, ':to'=>$toYmd]);
  $recent = $q->fetchAll();
} catch (Throwable $e) {}

/* =========================================================
   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">MD Dashboard</h1>
    <div class="text-muted">Finance overview — <?= e($monthStart->format('F Y')) ?></div>
  </div>

  <!-- Quick Nav: requested buttons -->
  <div class="d-flex flex-wrap gap-2">
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('accounts/index.php')) ?>">Accounts Dashboard</a>

    <div class="btn-group">
      <a class="btn btn-outline-dark" href="<?= e(url_modules('payments/drivers.php')) ?>">Driver Payments</a>
      <a class="btn btn-outline-dark" href="<?= e(url_modules('payments/partners.php')) ?>">Partner Payments</a>
    </div>

    <a class="btn btn-outline-primary" href="<?= e(url_modules('payments/pending_payments.php')) ?>">Pending Payments</a>

    <a class="btn btn-outline-secondary" href="<?= e(url_public('index.php')) ?>">
      System Administrator
    </a>

    <a class="btn btn-primary" href="<?= e(url_modules('reports/index.php')) ?>">Reports</a>
  </div>
</div>

<?php if ($notice): ?><div class="alert alert-success"><?= e($notice) ?></div><?php endif; ?>
<?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; ?>

<!-- Sales Target Bar -->
<div class="card shadow-sm mb-3">
  <div class="card-body">
    <div class="d-flex justify-content-between align-items-center mb-2">
      <div>
        <div class="small text-muted">Monthly Sales Target (client price only, Completed jobs)</div>
        <div class="fw-semibold">
          Target <?= $GBP ?><?= number_format($targetAmount,2) ?> ·
          Achieved <?= $GBP ?><?= number_format($achievedThisMonth,2) ?> ·
          Remaining <?= $GBP ?><?= number_format($remaining,2) ?>
        </div>
      </div>
      <form method="post" class="d-flex align-items-end gap-2">
        <input type="hidden" name="csrf" value="<?= e(csrf_token()) ?>">
        <input type="hidden" name="action" value="set_target">
        <div>
          <label class="form-label small mb-0">Set Target (<?= $GBP ?>)</label>
          <input type="number" step="0.01" min="0" name="target_amount" class="form-control form-control-sm" value="<?= e(number_format($targetAmount,2,'.','')) ?>">
        </div>
        <button class="btn btn-sm btn-dark" style="margin-top:18px">Save</button>
      </form>
    </div>

    <!-- Progress: grey = achieved, red = remaining -->
    <div class="progress" style="height: 16px;">
      <?php if ($targetAmount > 0): ?>
        <div class="progress-bar bg-secondary" role="progressbar"
             style="width: <?= number_format($achPct,2,'.','') ?>%;"
             aria-valuenow="<?= number_format($achPct,2,'.','') ?>" aria-valuemin="0" aria-valuemax="100">
          <?= number_format($achPct,0) ?>%
        </div>
        <div class="progress-bar bg-danger" role="progressbar"
             style="width: <?= number_format($remPct,2,'.','') ?>%;"
             aria-valuenow="<?= number_format($remPct,2,'.','') ?>" aria-valuemin="0" aria-valuemax="100"></div>
      <?php else: ?>
        <div class="progress-bar bg-warning" style="width: 100%;">Set a target to start tracking</div>
      <?php endif; ?>
    </div>

    <div class="small text-muted mt-2">
      Tip: If you don’t change the amount, next month’s target auto-renews with the same figure.
    </div>
  </div>
</div>

<!-- KPI Cards -->
<div class="row g-3">
  <div class="col-6 col-md-3">
    <div class="card shadow-sm h-100"><div class="card-body">
      <div class="small text-muted">Bookings (This Month)</div>
      <div class="display-6"><?= number_format($bookingsThisMonth) ?></div>
    </div></div>
  </div>
  <div class="col-6 col-md-3">
    <div class="card shadow-sm h-100"><div class="card-body">
      <div class="small text-muted">Active Rentals</div>
      <div class="display-6"><?= number_format($activeRentals) ?></div>
    </div></div>
  </div>
  <div class="col-6 col-md-3">
    <div class="card shadow-sm h-100"><div class="card-body">
      <div class="small text-muted">Partner Commission (This Month)</div>
      <div class="display-6"><?= $GBP ?><?= number_format($commissionThisMonth, 2) ?></div>
    </div></div>
  </div>
  <div class="col-6 col-md-3">
    <div class="card shadow-sm h-100"><div class="card-body">
      <div class="small text-muted">Total Profit (This Month)</div>
      <?php $badge = $totalProfitThisMonth >= 0 ? 'success' : 'danger'; ?>
      <div class="display-6"><span class="badge text-bg-<?= e($badge) ?>"><?= $GBP ?><?= number_format($totalProfitThisMonth, 2) ?></span></div>
    </div></div>
  </div>
</div>

<!-- Quarter & Year glance -->
<div class="row g-3 mt-1">
  <div class="col-md-6">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <div class="d-flex justify-content-between align-items-center mb-1">
          <h2 class="h6 mb-0">This Quarter</h2>
          <span class="small text-muted"><?= e($quarterStart->format('M d')) ?> – <?= e($quarterEnd->format('M d, Y')) ?></span>
        </div>
        <div class="row">
          <div class="col-6">
            <div class="small text-muted">Target</div>
            <div class="h5 mb-0"><?= $GBP ?><?= number_format($targetQuarter,2) ?></div>
          </div>
          <div class="col-6 text-end">
            <div class="small text-muted">Achieved</div>
            <div class="h5 mb-0"><?= $GBP ?><?= number_format($achievedQuarter,2) ?></div>
          </div>
        </div>
        <?php
          $qPct = $targetQuarter>0?min(100,max(0,($achievedQuarter/$targetQuarter)*100)):0;
          $qRem = 100 - $qPct;
        ?>
        <div class="progress mt-2" style="height:10px;">
          <div class="progress-bar bg-secondary" style="width: <?= number_format($qPct,2,'.','') ?>%"></div>
          <div class="progress-bar bg-danger" style="width: <?= number_format($qRem,2,'.','') ?>%"></div>
        </div>
      </div>
    </div>
  </div>
  <div class="col-md-6">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <div class="d-flex justify-content-between align-items-center mb-1">
          <h2 class="h6 mb-0">This Year</h2>
          <span class="small text-muted"><?= e($yearStart->format('Y')) ?></span>
        </div>
        <div class="row">
          <div class="col-6">
            <div class="small text-muted">Target</div>
            <div class="h5 mb-0"><?= $GBP ?><?= number_format($targetYear,2) ?></div>
          </div>
          <div class="col-6 text-end">
            <div class="small text-muted">Achieved</div>
            <div class="h5 mb-0"><?= $GBP ?><?= number_format($achievedYear,2) ?></div>
          </div>
        </div>
        <?php
          $yPct = $targetYear>0?min(100,max(0,($achievedYear/$targetYear)*100)):0;
          $yRem = 100 - $yPct;
        ?>
        <div class="progress mt-2" style="height:10px;">
          <div class="progress-bar bg-secondary" style="width: <?= number_format($yPct,2,'.','') ?>%"></div>
          <div class="progress-bar bg-danger" style="width: <?= number_format($yRem,2,'.','') ?>%"></div>
        </div>
      </div>
    </div>
  </div>
</div>

<!-- Quick actions bar -->
<div class="card shadow-sm mt-3">
  <div class="card-body d-flex flex-wrap gap-2">
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('bookings/calendar.php')) ?>">Open Calendar</a>
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('bookings/list.php') . '?view=active') ?>">Active Bookings</a>
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('bookings/list.php') . '?view=cleared&from='.$fromYmd.'&to='.$toYmd) ?>">Cleared (<?= e($monthStart->format('M Y')) ?>)</a>
    <a class="btn btn-dark" href="<?= e(url_modules('payments/drivers.php')) ?>">Drivers Payment (<?= (int)$unpaidDriverJobs ?> pending)</a>
    <a class="btn btn-outline-primary" href="<?= e(url_modules('payments/partners.php')) ?>">Partner Payments</a>
    <a class="btn btn-outline-primary" href="<?= e(url_modules('payments/pending_payments.php')) ?>">Pending Payments</a>
  </div>
</div>

<!-- Recent Jobs (kept) -->
<div class="card shadow-sm mt-3">
  <div class="card-body">
    <div class="d-flex justify-content-between align-items-center mb-2">
      <h2 class="h6 mb-0">This Month — Recent Jobs</h2>
      <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('bookings/list.php') . '?from='.$fromYmd.'&to='.$toYmd) ?>">Open Bookings</a>
    </div>
    <div class="table-responsive">
      <table class="table align-middle mb-0">
        <thead class="table-light">
          <tr>
            <th>Ref</th>
            <th>Client</th>
            <th>Pickup</th>
            <th class="text-end"><?= $GBP ?> Client</th>
            <th class="text-end"><?= $GBP ?> Driver</th>
            <th class="text-end"><?= $GBP ?> Commission</th>
            <th class="text-end"><?= $GBP ?> Profit</th>
            <th class="text-end">Actions</th>
          </tr>
        </thead>
        <tbody>
          <?php if ($recent): foreach ($recent as $r):
            $client = (float)$r['c'] + (float)$r['cp'] + (float)$r['cw'];
            $driver = (float)$r['d'] + (float)$r['dp'] + (float)$r['dw'];
            $comm   = (float)$r['commission_calc'];
            $profit = $client - $driver - $comm;
            $badge  = $profit >= 0 ? 'success' : 'danger';
            $pdt    = trim(($r['pickup_date'] ?? '') . ' ' . ($r['pickup_time'] ?? ''));
          ?>
          <tr>
            <td><strong><?= e($r['booking_ref'] ?? ('#'.$r['id'])) ?></strong></td>
            <td><?= e($r['client_name'] ?? '—') ?></td>
            <td><?= e($pdt) ?></td>
            <td class="text-end"><?= number_format($client, 2) ?></td>
            <td class="text-end"><?= number_format($driver, 2) ?></td>
            <td class="text-end"><?= number_format($comm, 2) ?></td>
            <td class="text-end"><span class="badge text-bg-<?= e($badge) ?>"><?= $GBP ?><?= number_format($profit, 2) ?></span></td>
            <td class="text-end">
              <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('bookings/edit.php')) ?>?id=<?= (int)$r['id'] ?>">View</a>
              <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('bookings/pdf.php')) ?>?booking_id=<?= (int)$r['id'] ?>" target="_blank" rel="noopener">Journey</a>
            </td>
          </tr>
          <?php endforeach; else: ?>
            <tr><td colspan="8" class="text-center text-muted py-4">No data this month.</td></tr>
          <?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>

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