<?php
declare(strict_types=1);

/**
 * modules/dashboard/sales.php
 *
 * Sales Manager Dashboard — quick actions, KPIs, and sales target progress.
 *
 * - Monthly Sales Target bar.
 * - Target progress = sum(total_client_price) for bookings in current month
 *   where status IN ('Confirmed','Assigned','InProgress','In Progress','Completed').
 * - Counts: Quotations (this month), Approved Quotes (this month),
 *           Bookings (this month), Partners (total), Corporate Accounts (total).
 * - Quick actions (includes Suppliers) + recent activity.
 *
 * Currency rendering uses &pound; to prevent mojibake.
 */

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

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

$GBP = '&pound;';
$errors = [];
$notice = null;

/* -------------------------------------------------------
   Helpers
   ------------------------------------------------------- */
function tbl_exists(string $name): bool {
  try { db()->query("SELECT 1 FROM `{$name}` LIMIT 1"); return true; }
  catch (Throwable $e) { return false; }
}
function col_exists(string $table, string $col): bool {
  try {
    $q = db()->prepare("
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND COLUMN_NAME = :c
      LIMIT 1
    ");
    $q->execute([':t'=>$table, ':c'=>$col]);
    return (bool)$q->fetchColumn();
  } catch (Throwable $e) { return false; }
}

/** sales_targets table (shared with MD dashboard) */
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,
      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
  ");
}

/** Fetch or auto-create sales target row for the given month (auto-renew logic). */
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;

  $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();
}

/** Achieved (open pipeline GR) — Confirmed + Assigned + InProgress + In Progress + Completed */
function sum_gross_revenue_open_pipeline(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 IN ('Confirmed','Assigned','InProgress','In Progress','Completed')
  ");
  $q->execute([':cid'=>$cid, ':from'=>$fromYmd, ':to'=>$toYmd]);
  return (float)$q->fetchColumn();
}

/* -------------------------------------------------------
   Time window (this month)
   ------------------------------------------------------- */
$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 Bar
   ------------------------------------------------------- */
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);

$achievedThisMonth = sum_gross_revenue_open_pipeline($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;

/* -------------------------------------------------------
   KPI Cards (quotes schema tolerant)
   ------------------------------------------------------- */
$quotesThisMonth = 0;
$approvedQuotesThisMonth = 0;

$quotes_has_created = null;
$quotes_has_status  = null;

if (tbl_exists('quotes')) {
  foreach (['created_at','created_on','created','date_created','quote_date'] as $c) {
    if (col_exists('quotes',$c)) { $quotes_has_created = $c; break; }
  }
  foreach (['status','state'] as $c) {
    if (col_exists('quotes',$c)) { $quotes_has_status = $c; break; }
  }

  try {
    if ($quotes_has_created) {
      $q = db()->prepare("SELECT COUNT(*) FROM quotes WHERE company_id=:cid AND DATE(`$quotes_has_created`) BETWEEN :from AND :to");
      $q->execute([':cid'=>$cid, ':from'=>$fromYmd, ':to'=>$toYmd]);
    } else {
      $q = db()->prepare("SELECT COUNT(*) FROM quotes WHERE company_id=:cid");
      $q->execute([':cid'=>$cid]);
    }
    $quotesThisMonth = (int)$q->fetchColumn();
  } catch (Throwable $e) {}

  try {
    if ($quotes_has_status) {
      $whereDate = $quotes_has_created ? "AND DATE(`$quotes_has_created`) BETWEEN :from AND :to" : "";
      $qa = db()->prepare("
        SELECT COUNT(*)
          FROM quotes
         WHERE company_id=:cid
           $whereDate
           AND (`$quotes_has_status` IN ('Approved','approved','Accepted','accepted','Converted','converted'))
      ");
      $args = [':cid'=>$cid];
      if ($quotes_has_created) { $args[':from']=$fromYmd; $args[':to']=$toYmd; }
      $qa->execute($args);
      $approvedQuotesThisMonth = (int)$qa->fetchColumn();
    }
  } catch (Throwable $e) {}
}

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

// Partners (total)
$partnersCount = 0;
if (tbl_exists('partners')) {
  try {
    $qp = db()->prepare("SELECT COUNT(*) FROM partners WHERE company_id=:cid");
    $qp->execute([':cid'=>$cid]);
    $partnersCount = (int)$qp->fetchColumn();
  } catch (Throwable $e) {}
}

// Corporate Accounts (total)
$corporatesCount = 0;
if (tbl_exists('corporates')) {
  try {
    $qc = db()->prepare("SELECT COUNT(*) FROM corporates WHERE company_id=:cid");
    $qc->execute([':cid'=>$cid]);
    $corporatesCount = (int)$qc->fetchColumn();
  } catch (Throwable $e) {}
}

/* -------------------------------------------------------
   Recent Quotes (schema tolerant)
   ------------------------------------------------------- */
$recentQuotes = [];
if (tbl_exists('quotes')) {
  $exprRef = col_exists('quotes','quote_ref') ? "q.`quote_ref`"
           : (col_exists('quotes','ref') ? "q.`ref`"
           : (col_exists('quotes','reference') ? "q.`reference`" : "CONCAT('Q-', q.id)"));

  $exprClientName = col_exists('quotes','client_name') ? "q.`client_name`"
                 : (col_exists('quotes','customer_name') ? "q.`customer_name`"
                 : (col_exists('quotes','contact_name')  ? "q.`contact_name`" : "NULL"));
  $exprStatus = col_exists('quotes','status') ? "q.`status`"
            : (col_exists('quotes','state')  ? "q.`state`"  : "NULL");

  $colCreatedExpr = "NULL";
  foreach (['created_at','created_on','created','date_created','quote_date'] as $c) {
    if (col_exists('quotes',$c)) { $colCreatedExpr = "q.`$c`"; break; }
  }

  $totalExpr = null;
  foreach (['total_amount','grand_total','total','amount'] as $c) {
    if (col_exists('quotes',$c)) { $totalExpr = "q.`$c`"; break; }
  }
  $useLines = false;
  if (!$totalExpr && tbl_exists('quote_lines') && col_exists('quote_lines','quote_id')) {
    $hasLT   = col_exists('quote_lines','line_total');
    $hasQty  = col_exists('quote_lines','quantity');
    $hasUnit = col_exists('quote_lines','unit_price');
    if ($hasLT || ($hasQty && $hasUnit)) $useLines = true;
  }
  $totalExprFinal = $totalExpr ?: ($useLines ? "COALESCE(ql.total_calced,0)" : "0.00");

  $fromSql = " FROM quotes q ";
  if ($useLines) {
    $sumExpr = col_exists('quote_lines','line_total')
             ? "SUM(COALESCE(l.line_total,0))"
             : "SUM(COALESCE(l.quantity,0) * COALESCE(l.unit_price,0))";
    $fromSql .= "
      LEFT JOIN (
        SELECT l.quote_id, {$sumExpr} AS total_calced
        FROM quote_lines l
        GROUP BY l.quote_id
      ) ql ON ql.quote_id = q.id
    ";
  }

  $select = "
    SELECT
      q.id,
      {$exprRef}        AS quote_ref,
      {$exprClientName} AS client_name,
      {$exprStatus}     AS status,
      {$totalExprFinal} AS total_amount,
      {$colCreatedExpr} AS created_col
  ";

  $where = " WHERE q.company_id = :cid ";
  $order = ($colCreatedExpr !== "NULL") ? " ORDER BY created_col DESC, q.id DESC " : " ORDER BY q.id DESC ";
  $sql = $select . $fromSql . $where . $order . " LIMIT 8";

  try {
    $rq = db()->prepare($sql);
    $rq->execute([':cid'=>$cid]);
    $recentQuotes = $rq->fetchAll();
  } catch (Throwable $e) {}
}

/* -------------------------------------------------------
   Recent Bookings (fallback)
   ------------------------------------------------------- */
$recentBookings = [];
try {
  $rb = db()->prepare("
    SELECT id, booking_ref, client_name, pickup_date, pickup_time, total_client_price, status
      FROM bookings
     WHERE company_id=:cid
     ORDER BY pickup_date DESC, pickup_time DESC, id DESC
     LIMIT 8
  ");
  $rb->execute([':cid'=>$cid]);
  $recentBookings = $rb->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">Sales Dashboard</h1>
    <div class="text-muted">Sales overview — <?= e($monthStart->format('F Y')) ?></div>
  </div>

  <!-- Quick navigation (Sales) -->
  <div class="d-flex flex-wrap gap-2">
    <!-- Quotes -->
    <a class="btn btn-dark" href="<?= e(url_modules('quotes/add.php')) ?>">+ New Quote</a>
    <a class="btn btn-outline-dark" href="<?= e(url_modules('quotes/list.php')) ?>">Quotes</a>

    <!-- Suppliers -->
    <a class="btn btn-outline-warning" href="<?= e(url_modules('suppliers/add.php')) ?>">+ New Supplier</a>
    <a class="btn btn-outline-warning" href="<?= e(url_modules('suppliers/list.php')) ?>">Suppliers</a>

    <!-- Partners -->
    <a class="btn btn-outline-primary" href="<?= e(url_modules('partners/add.php')) ?>">+ New Partner</a>
    <a class="btn btn-outline-primary" href="<?= e(url_modules('partners/list.php')) ?>">Partners</a>

    <!-- Corporate Accounts -->
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('corporates/add.php')) ?>">+ New Corporate</a>
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('corporates/list.php')) ?>">Corporate Accounts</a>

    <!-- Bookings -->
    <a class="btn btn-outline-success" href="<?= e(url_modules('bookings/list.php')) ?>">Bookings</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; ?>

<!-- Monthly Sales Target -->
<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, statuses:
          <strong>Confirmed</strong> + <strong>Assigned</strong> + <strong>InProgress</strong> + <strong>In&nbsp;Progress</strong> + <strong>Completed</strong>)
        </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>
      <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('dashboard/md.php')) ?>" title="Change target in MD Dashboard">View in MD</a>
    </div>
    <div class="progress" style="height:16px;">
      <?php if ($targetAmount > 0): ?>
        <div class="progress-bar bg-secondary" style="width: <?= number_format($achPct,2,'.','') ?>%">
          <?= number_format($achPct,0) ?>%
        </div>
        <div class="progress-bar bg-danger" style="width: <?= number_format($remPct,2,'.','') ?>%"></div>
      <?php else: ?>
        <div class="progress-bar bg-warning" style="width:100%;">No target set by MD</div>
      <?php endif; ?>
    </div>
  </div>
</div>

<!-- KPI Cards -->
<div class="row g-3">
  <div class="col-6 col-lg-3">
    <div class="card shadow-sm h-100"><div class="card-body">
      <div class="small text-muted"><?= $quotes_has_created ? 'Quotations (This Month)' : 'Quotations (All)' ?></div>
      <div class="display-6"><?= number_format($quotesThisMonth) ?></div>
    </div></div>
  </div>
  <div class="col-6 col-lg-3">
    <div class="card shadow-sm h-100"><div class="card-body">
      <div class="small text-muted">Approved / Accepted / Converted <?= $quotes_has_created ? '(This Month)' : '' ?></div>
      <div class="display-6"><?= number_format($approvedQuotesThisMonth) ?></div>
    </div></div>
  </div>
  <div class="col-6 col-lg-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-lg-3">
    <div class="card shadow-sm h-100"><div class="card-body">
      <div class="small text-muted">Partners (Total)</div>
      <div class="display-6"><?= number_format($partnersCount) ?></div>
    </div></div>
  </div>
  <div class="col-6 col-lg-3">
    <div class="card shadow-sm h-100"><div class="card-body">
      <div class="small text-muted">Corporate Accounts (Total)</div>
      <div class="display-6"><?= number_format($corporatesCount) ?></div>
    </div></div>
  </div>
</div>

<!-- Shortcuts -->
<div class="card shadow-sm mt-3">
  <div class="card-body d-flex flex-wrap gap-2">
    <a class="btn btn-outline-dark" href="<?= e(url_modules('quotes/add.php')) ?>">Create Quote</a>
    <a class="btn btn-outline-dark" href="<?= e(url_modules('quotes/list.php')) ?>">View Quotes</a>

    <a class="btn btn-outline-warning" href="<?= e(url_modules('suppliers/add.php')) ?>">Add Supplier</a>
    <a class="btn btn-outline-warning" href <?= '"'.e(url_modules('suppliers/list.php')).'"' ?>>View Suppliers</a>

    <a class="btn btn-outline-primary" href="<?= e(url_modules('partners/add.php')) ?>">Add Partner</a>
    <a class="btn btn-outline-primary" href="<?= e(url_modules('partners/list.php')) ?>">View Partners</a>

    <a class="btn btn-outline-secondary" href="<?= e(url_modules('corporates/add.php')) ?>">Add Corporate</a>
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('corporates/list.php')) ?>">View Corporate Accounts</a>

    <a class="btn btn-outline-success" href="<?= e(url_modules('bookings/list.php')) ?>">Open Bookings</a>
  </div>
</div>

<!-- Recent activity -->
<?php if ($recentQuotes): ?>
  <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">Recent Quotes</h2>
        <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('quotes/list.php')) ?>">All Quotes</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>Status</th>
              <th class="text-end"><?= $GBP ?> Total</th>
              <th class="text-end">Created</th>
              <th class="text-end">Actions</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($recentQuotes as $q): ?>
              <tr>
                <td><strong><?= e($q['quote_ref'] ?? ('#'.$q['id'])) ?></strong></td>
                <td><?= e($q['client_name'] ?? '—') ?></td>
                <td>
                  <?php
                    $st = (string)($q['status'] ?? '');
                    $cls = in_array($st, ['Approved','Accepted','Converted','approved','accepted','converted'])
                      ? 'success' : (in_array($st,['Rejected','Cancelled']) ? 'danger' : 'secondary');
                  ?>
                  <span class="badge text-bg-<?= $cls ?>"><?= e($st ?: '—') ?></span>
                </td>
                <td class="text-end"><?= $GBP ?><?= number_format((float)($q['total_amount'] ?? 0), 2) ?></td>
                <td class="text-end">
                  <?php $dt = $q['created_col'] ?? null; echo $dt ? e(date('Y-m-d H:i', strtotime((string)$dt))) : '—'; ?>
                </td>
                <td class="text-end">
                  <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('quotes/view.php')) ?>?id=<?= (int)$q['id'] ?>">View</a>
                  <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('quotes/edit.php')) ?>?id=<?= (int)$q['id'] ?>">Edit</a>
                  <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('quotes/pdf.php')) ?>?id=<?= (int)$q['id'] ?>" target="_blank" rel="noopener">PDF</a>
                  <a class="btn btn-sm btn-primary" href="<?= e(url_modules('quotes/supplier_quote.php')) ?>?id=<?= (int)$q['id'] ?>" target="_blank" rel="noopener">Supplier Quote</a>
                  <?php if (file_exists(dirname(__DIR__).'/quotes/convert_to_booking.php')): ?>
                    <a class="btn btn-sm btn-success" href="<?= e(url_modules('quotes/convert_to_booking.php')) ?>?id=<?= (int)$q['id'] ?>">Convert</a>
                  <?php endif; ?>
                </td>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
<?php else: ?>
  <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">Recent Bookings</h2>
        <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('bookings/list.php')) ?>">All 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>Status</th>
              <th class="text-end"><?= $GBP ?> Client</th>
              <th class="text-end">Actions</th>
            </tr>
          </thead>
          <tbody>
            <?php if ($recentBookings): foreach ($recentBookings as $b):
              $pdt = trim(($b['pickup_date'] ?? '') . ' ' . ($b['pickup_time'] ?? ''));
            ?>
              <tr>
                <td><strong><?= e($b['booking_ref'] ?? ('#'.$b['id'])) ?></strong></td>
                <td><?= e($b['client_name'] ?? '—') ?></td>
                <td><?= e($pdt) ?></td>
                <td><span class="badge text-bg-<?= ($b['status']??'')==='Completed'?'success':'secondary' ?>"><?= e($b['status'] ?? '—') ?></span></td>
                <td class="text-end"><?= $GBP ?><?= number_format((float)($b['total_client_price'] ?? 0), 2) ?></td>
                <td class="text-end">
                  <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('bookings/edit.php')) ?>?id=<?= (int)$b['id'] ?>">View</a>
                  <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('bookings/pdf.php')) ?>?booking_id=<?= (int)$b['id'] ?>" target="_blank" rel="noopener">Journey</a>
                </td>
              </tr>
            <?php endforeach; else: ?>
              <tr><td colspan="6" class="text-center text-muted py-4">No recent data.</td></tr>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
<?php endif; ?>

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