<?php
declare(strict_types=1);
/**
 * modules/quotes/list.php
 *
 * Quotes grid (schema-tolerant) with Supplier Quote button.
 * - Detects columns on quotes table (quote_ref/ref/reference, client_name/email, status, created_at variants, total variants).
 * - If no total column, optionally computes from quote_lines (sum of line_total or qty*unit).
 */

require_once dirname(__DIR__, 2) . '/config/functions.php';
require_role(['Sales','SalesManager','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 $table): bool {
  try { db()->query("SELECT 1 FROM `{$table}` 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; }
}

/* ---------------- Detect columns on quotes ---------------- */
$hasQuoteRef   = col_exists('quotes','quote_ref');
$hasRef        = !$hasQuoteRef && col_exists('quotes','ref');
$hasReference  = !$hasQuoteRef && !$hasRef && col_exists('quotes','reference');

$hasClientName = col_exists('quotes','client_name') || col_exists('quotes','customer_name') || col_exists('quotes','contact_name');
$hasClientEmail= col_exists('quotes','client_email') || col_exists('quotes','email');

$hasStatus     = col_exists('quotes','status') || col_exists('quotes','state');

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

$colTotal = null;
foreach (['total_amount','grand_total','total','amount'] as $c) {
  if (col_exists('quotes', $c)) { $colTotal = "q.`$c`"; break; }
}

/* Expressions/aliases (use safe fallbacks) */
$exprRef = $hasQuoteRef   ? "q.`quote_ref`"
        : ($hasRef        ? "q.`ref`"
        : ($hasReference  ? "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"));

$exprClientEmail= col_exists('quotes','client_email') ? "q.`client_email`"
                 : (col_exists('quotes','email')      ? "q.`email`" : "NULL");

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

$exprCreated    = $colCreated ?: "NULL";

/* Totals:
   - Prefer real column.
   - Else, if quote_lines exists, compute sum(line_total or qty*unit).
   - Else, 0.00.
*/
$useLines = false;
if (!$colTotal && tbl_exists('quote_lines') && col_exists('quote_lines','quote_id')) {
  // Need at least quantity/unit_price or line_total to compute
  $hasQty  = col_exists('quote_lines','quantity');
  $hasUnit = col_exists('quote_lines','unit_price');
  $hasLT   = col_exists('quote_lines','line_total');
  if ($hasLT || ($hasQty && $hasUnit)) $useLines = true;
}

$totalExpr = $colTotal ?: ($useLines ? "COALESCE(ql.total_calced,0)" : "0.00");

/* ---------------- Filters (GET) ---------------- */
$status = isset($_GET['status']) && $_GET['status'] !== '' ? (string)$_GET['status'] : null;
$from   = isset($_GET['from'])   && $_GET['from']   !== '' ? (string)$_GET['from']   : null;
$to     = isset($_GET['to'])     && $_GET['to']     !== '' ? (string)$_GET['to']     : null;
$qstr   = isset($_GET['q'])      && $_GET['q']      !== '' ? trim((string)$_GET['q']) : null;

/* ---------------- Build SQL ---------------- */
$select = "
  SELECT
    q.id,
    {$exprRef}         AS quote_ref,
    {$exprClientName}  AS client_name,
    {$exprClientEmail} AS client_email,
    {$exprStatus}      AS status,
    {$totalExpr}       AS total_amount,
    {$exprCreated}     AS created_col
";
$fromSql = " FROM quotes q ";
if ($useLines) {
  // Compute totals from quote_lines
  $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
  ";
}

$where = ["q.company_id = :cid"];
$args  = [':cid' => $cid];

/* Apply filters using only available columns */
if ($status && $exprStatus !== "NULL") {
  $where[] = "{$exprStatus} = :st";
  $args[':st'] = $status;
}
if ($from && $to && $exprCreated !== "NULL") {
  $where[] = "(DATE({$exprCreated}) BETWEEN :from AND :to)";
  $args[':from'] = $from;
  $args[':to']   = $to;
} elseif ($from && $exprCreated !== "NULL") {
  $where[] = "DATE({$exprCreated}) >= :from";
  $args[':from'] = $from;
} elseif ($to && $exprCreated !== "NULL") {
  $where[] = "DATE({$exprCreated}) <= :to";
  $args[':to']   = $to;
}
if ($qstr) {
  $like = '%'.$qstr.'%';
  $parts = ["({$exprRef} LIKE :q)"];
  if ($exprClientName  !== "NULL")  $parts[] = "{$exprClientName} LIKE :q";
  if ($exprClientEmail !== "NULL")  $parts[] = "{$exprClientEmail} LIKE :q";
  $where[] = '('.implode(' OR ', $parts).')';
  $args[':q'] = $like;
}

$order = " ORDER BY created_col DESC, q.id DESC ";
$sql = $select . $fromSql . " WHERE " . implode(' AND ', $where) . $order . " LIMIT 400";

$stmt = db()->prepare($sql);
$stmt->execute($args);
$rows = $stmt->fetchAll();

/* ---------------- UI ---------------- */
include dirname(__DIR__, 2) . '/includes/header.php';
?>
<div class="d-flex justify-content-between align-items-center mb-3">
  <h1 class="h4 mb-0">Quotes</h1>
  <div class="d-flex gap-2">
    <a class="btn btn-dark" href="<?= e(url_modules('quotes/add.php')) ?>">+ New Quote</a>
  </div>
</div>

<div class="card shadow-sm mb-3">
  <div class="card-body">
    <form method="get" class="row g-2 align-items-end">
      <div class="col-md-2">
        <label class="form-label">Status</label>
        <select class="form-select" name="status">
          <option value="">— All —</option>
          <?php foreach (['New','Sent','Approved','Accepted','Rejected','Converted','Cancelled'] as $st): ?>
            <option value="<?= e($st) ?>" <?= ($status===$st)?'selected':'' ?>><?= e($st) ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <div class="col-md-2">
        <label class="form-label">From</label>
        <input type="date" class="form-control" name="from" value="<?= e($from ?? '') ?>" <?= ($exprCreated==="NULL")?'disabled':'' ?>>
      </div>
      <div class="col-md-2">
        <label class="form-label">To</label>
        <input type="date" class="form-control" name="to" value="<?= e($to ?? '') ?>" <?= ($exprCreated==="NULL")?'disabled':'' ?>>
      </div>
      <div class="col-md-4">
        <label class="form-label">Search</label>
        <input class="form-control" name="q" placeholder="Ref / Client / Email" value="<?= e($qstr ?? '') ?>">
      </div>
      <div class="col-md-1 d-grid">
        <button class="btn btn-primary">Filter</button>
      </div>
      <div class="col-md-1 d-grid">
        <a class="btn btn-outline-secondary" href="list.php">Reset</a>
      </div>
    </form>
    <?php if ($exprCreated==="NULL"): ?>
      <div class="text-muted small mt-2">Note: Date filters disabled — your quotes table has no created date column.</div>
    <?php endif; ?>
  </div>
</div>

<div class="card shadow-sm">
  <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" style="min-width:320px;">Actions</th>
        </tr>
      </thead>
      <tbody>
        <?php if ($rows): foreach ($rows as $r): ?>
          <tr>
            <td><strong><?= e($r['quote_ref'] ?? ('#'.$r['id'])) ?></strong></td>
            <td>
              <?php
                $cn = (string)($r['client_name'] ?? '');
                $em = (string)($r['client_email'] ?? '');
              ?>
              <?php if ($cn !== ''): ?><div class="fw-semibold"><?= e($cn) ?></div><?php endif; ?>
              <?php if ($em !== ''): ?><div class="text-muted small"><?= e($em) ?></div><?php endif; ?>
              <?php if ($cn === '' && $em === ''): ?><span class="text-muted">—</span><?php endif; ?>
            </td>
            <td>
              <?php
                $st = (string)($r['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)($r['total_amount'] ?? 0), 2) ?></td>
            <td class="text-end">
              <?php
                $dt = $r['created_col'] ?? null;
                echo $dt ? e(date('Y-m-d H:i', strtotime((string)$dt))) : '—';
              ?>
            </td>
            <td class="text-end">
              <div class="d-inline-flex flex-wrap gap-1 justify-content-end">
                <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('quotes/view.php')) ?>?id=<?= (int)$r['id'] ?>">View</a>
                <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('quotes/edit.php')) ?>?id=<?= (int)$r['id'] ?>">Edit</a>
                <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('quotes/pdf.php')) ?>?id=<?= (int)$r['id'] ?>" target="_blank" rel="noopener">PDF</a>
                <!-- Supplier Quote action -->
                <a class="btn btn-sm btn-primary" href="<?= e(url_modules('quotes/supplier_quote.php')) ?>?id=<?= (int)$r['id'] ?>">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)$r['id'] ?>">Convert</a>
                <?php endif; ?>
              </div>
            </td>
          </tr>
        <?php endforeach; else: ?>
          <tr><td colspan="6" class="text-center text-muted py-4">No quotes found.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>
</div>

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