<?php
declare(strict_types=1);
require_once __DIR__ . '/_inc/boot.php';
require_once __DIR__ . '/_inc/db.php';

$pdo = db();
auth_require_any_permission(['use_crons', 'manage_users']);

$cfg = app_cfg();
$cronKey = (string)($cfg['security']['cron_key'] ?? '');
$active = 'bad_tracking';
$title = 'Bad Tracking Leads';

function bad_tracking_missing_sql(string $providerExpr): string {
  return "(
    l.tracking_number IS NULL
    OR TRIM(l.tracking_number)=''
    OR UPPER(TRIM(l.tracking_number)) IN ('PICKPACK','LINK','PDF','LABEL','PENDING','N/A','NA','NONE','NULL','UNKNOWN','0','-')
    OR l.tracking_number LIKE 'http://%'
    OR l.tracking_number LIKE 'https://%'
    OR LOWER(l.tracking_number) LIKE '%.pdf%'
    OR (
      ({$providerExpr})<>'QCARGO'
      AND (
        TRIM(l.tracking_number)=TRIM(l.lead_number)
        OR (l.order_id_remote IS NOT NULL AND l.order_id_remote<>'' AND TRIM(l.tracking_number)=TRIM(l.order_id_remote))
      )
    )
  )";
}

function bad_tracking_base_expr(): string {
  return "COALESCE(l.confirmed_at, l.created_at_remote, l.created_at)";
}

function bad_tracking_provider_expr(): string {
  return "CASE
    WHEN l.lead_number LIKE 'PP-%' THEN 'PICKPACK'
    WHEN l.lead_number LIKE 'AH-%' THEN 'EFC'
    ELSE COALESCE(c.fulfillment_provider,'EFC')
  END";
}

function bad_tracking_terminal_status_sql(): string {
  $statuses = "'no answer 9','canceled','out_of_area','out of area','duplicated','archived'";
  $latestNorm = "(SELECT hterm.status_norm FROM lead_history hterm WHERE hterm.lead_number=l.lead_number ORDER BY COALESCE(hterm.status_at,hterm.created_at) DESC, hterm.id DESC LIMIT 1)";
  $latestRaw = "(SELECT LOWER(TRIM(hraw.status_raw)) FROM lead_history hraw WHERE hraw.lead_number=l.lead_number ORDER BY COALESCE(hraw.status_at,hraw.created_at) DESC, hraw.id DESC LIMIT 1)";
  return "(
    LOWER(TRIM(COALESCE(l.current_status,''))) IN ({$statuses})
    OR COALESCE({$latestNorm}, '') IN ({$statuses})
    OR COALESCE({$latestRaw}, '') IN ({$statuses})
  )";
}

function bad_tracking_scalar($value): string {
  if ($value === null) return '';
  return trim((string)$value);
}

$hours = isset($_GET['hours']) ? max(1, min(2160, (int)$_GET['hours'])) : 48;
$limit = isset($_GET['limit']) ? max(25, min(1000, (int)$_GET['limit'])) : 500;
$country = strtoupper(trim((string)($_GET['country'] ?? '')));
$provider = strtoupper(trim((string)($_GET['provider'] ?? '')));
$q = trim((string)($_GET['q'] ?? ''));
$export = (string)($_GET['export'] ?? '') === 'csv';
$providerExpr = bad_tracking_provider_expr();

$where = [
  'l.final_status=0',
  bad_tracking_missing_sql($providerExpr),
  'NOT ' . bad_tracking_terminal_status_sql(),
  bad_tracking_base_expr() . ' IS NOT NULL',
  'TIMESTAMPDIFF(HOUR, ' . bad_tracking_base_expr() . ', NOW()) >= ?',
];
$params = [$hours];

if ($country !== '') {
  $where[] = 'UPPER(l.country)=?';
  $params[] = $country;
}

if ($provider !== '' && in_array($provider, ['EFC', 'QCARGO', 'PICKPACK'], true)) {
  $where[] = bad_tracking_provider_expr() . '=?';
  $params[] = $provider;
}

if ($q !== '') {
  $where[] = '(l.lead_number LIKE ? OR l.customer_name LIKE ? OR l.email LIKE ? OR l.phone LIKE ?)';
  $like = '%' . $q . '%';
  array_push($params, $like, $like, $like, $like);
}

$latestRawSql = "(SELECT h.status_raw FROM lead_history h WHERE h.lead_number=l.lead_number ORDER BY COALESCE(h.status_at,h.created_at) DESC, h.id DESC LIMIT 1)";
$latestNormSql = "(SELECT h.status_norm FROM lead_history h WHERE h.lead_number=l.lead_number ORDER BY COALESCE(h.status_at,h.created_at) DESC, h.id DESC LIMIT 1)";
$latestAtSql = "(SELECT COALESCE(h.status_at,h.created_at) FROM lead_history h WHERE h.lead_number=l.lead_number ORDER BY COALESCE(h.status_at,h.created_at) DESC, h.id DESC LIMIT 1)";
$baseExpr = bad_tracking_base_expr();

$sql = "SELECT
    l.lead_number,
    l.country,
    {$providerExpr} AS provider,
    l.customer_name,
    l.email,
    l.phone,
    l.current_status,
    l.current_status_at,
    l.created_at_remote,
    l.confirmed_at,
    l.tracking_number,
    l.order_id_remote,
    l.courier_code,
    l.shipping_company,
    l.details_fetched_at,
    l.last_sync_job,
    l.last_sync_at,
    TIMESTAMPDIFF(HOUR, {$baseExpr}, NOW()) AS age_hours,
    TIMESTAMPDIFF(HOUR, COALESCE(l.details_fetched_at, {$baseExpr}), NOW()) AS details_age_hours,
    {$latestRawSql} AS latest_raw,
    {$latestNormSql} AS latest_norm,
    {$latestAtSql} AS latest_at
  FROM leads l
  LEFT JOIN countries c ON c.code=l.country
  WHERE " . implode(' AND ', $where) . "
  ORDER BY age_hours DESC, COALESCE(l.details_fetched_at,'1970-01-01') ASC
  LIMIT {$limit}";

$st = $pdo->prepare($sql);
$st->execute($params);
$rows = $st->fetchAll();

$countries = $pdo->query("SELECT DISTINCT country FROM leads WHERE country IS NOT NULL AND country<>'' ORDER BY country")->fetchAll(PDO::FETCH_COLUMN);
$providers = ['EFC', 'PICKPACK', 'QCARGO'];

$total = count($rows);
$over72 = 0;
$over96 = 0;
$watched = 0;
foreach ($rows as $row) {
  $age = (int)($row['age_hours'] ?? 0);
  if ($age >= 72) $over72++;
  if ($age >= 96) $over96++;
  if ((string)($row['last_sync_job'] ?? '') === 'watch_tracking') $watched++;
}

if ($export) {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="bad_tracking_' . date('Ymd_His') . '.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, [
    'lead_number',
    'country',
    'provider',
    'age_hours',
    'current_status',
    'current_status_at',
    'latest_raw',
    'latest_at',
    'customer_name',
    'email',
    'phone',
    'tracking_number',
    'order_id_remote',
    'courier_code',
    'shipping_company',
    'details_fetched_at',
    'last_sync_job',
    'last_sync_at',
  ]);
  foreach ($rows as $row) {
    fputcsv($out, [
      bad_tracking_scalar($row['lead_number'] ?? ''),
      bad_tracking_scalar($row['country'] ?? ''),
      bad_tracking_scalar($row['provider'] ?? ''),
      (int)($row['age_hours'] ?? 0),
      bad_tracking_scalar($row['current_status'] ?? ''),
      bad_tracking_scalar($row['current_status_at'] ?? ''),
      bad_tracking_scalar($row['latest_raw'] ?? ''),
      bad_tracking_scalar($row['latest_at'] ?? ''),
      bad_tracking_scalar($row['customer_name'] ?? ''),
      bad_tracking_scalar($row['email'] ?? ''),
      bad_tracking_scalar($row['phone'] ?? ''),
      bad_tracking_scalar($row['tracking_number'] ?? ''),
      bad_tracking_scalar($row['order_id_remote'] ?? ''),
      bad_tracking_scalar($row['courier_code'] ?? ''),
      bad_tracking_scalar($row['shipping_company'] ?? ''),
      bad_tracking_scalar($row['details_fetched_at'] ?? ''),
      bad_tracking_scalar($row['last_sync_job'] ?? ''),
      bad_tracking_scalar($row['last_sync_at'] ?? ''),
    ]);
  }
  fclose($out);
  exit;
}

require_once __DIR__ . '/_inc/layout_top.php';
?>
<div data-autorefresh="1" data-kind="bad_tracking"></div>

<section class="card wide" style="margin-bottom:14px">
  <div class="card-h">
    <div>
      <div class="card-title">Bad tracking leads</div>
      <div class="muted">Non-final leads older than <?= (int)$hours ?>h where tracking is still missing or invalid.</div>
    </div>
    <div class="row" style="justify-content:flex-end">
      <a class="btn btn-sm btn-ghost" href="/crons.php">Crons</a>
      <a class="btn btn-sm" href="/bad_tracking.php?<?= h(http_build_query(array_merge($_GET, ['export' => 'csv']))) ?>">Export CSV</a>
    </div>
  </div>

  <div class="kpi-row">
    <div class="kpi neutral"><div class="k">BAD LEADS</div><div class="v"><?= (int)$total ?></div><div class="p">Visible rows</div></div>
    <div class="kpi warn"><div class="k">72H+</div><div class="v"><?= (int)$over72 ?></div><div class="p">Needs attention</div></div>
    <div class="kpi bad"><div class="k">96H+</div><div class="v"><?= (int)$over96 ?></div><div class="p">Critical queue</div></div>
    <div class="kpi neutral"><div class="k">WATCHED</div><div class="v"><?= (int)$watched ?></div><div class="p">Last touched by watch_tracking</div></div>
  </div>
</section>

<section class="card wide">
  <form class="row" method="get" action="/bad_tracking.php">
    <input class="input" type="text" name="q" value="<?= h($q) ?>" placeholder="Search lead/customer/email/phone" style="min-width:260px" />
    <select class="input" name="hours" style="max-width:130px">
      <?php foreach([12,24,48,72,96,168,720,2160] as $h0): ?>
        <option value="<?= (int)$h0 ?>" <?= $hours===$h0?'selected':'' ?>><?= (int)$h0 ?>h+</option>
      <?php endforeach; ?>
    </select>
    <select class="input" name="country" style="max-width:180px">
      <option value="">All countries</option>
      <?php foreach($countries as $c): $c = (string)$c; ?>
        <option value="<?= h($c) ?>" <?= $country===strtoupper($c)?'selected':'' ?>><?= h($c) ?></option>
      <?php endforeach; ?>
    </select>
    <select class="input" name="provider" style="max-width:160px">
      <option value="">All providers</option>
      <?php foreach($providers as $p): ?>
        <option value="<?= h($p) ?>" <?= $provider===$p?'selected':'' ?>><?= h($p) ?></option>
      <?php endforeach; ?>
    </select>
    <select class="input" name="limit" style="max-width:130px">
      <?php foreach([100,250,500,1000] as $l0): ?>
        <option value="<?= (int)$l0 ?>" <?= $limit===$l0?'selected':'' ?>>max <?= (int)$l0 ?></option>
      <?php endforeach; ?>
    </select>
    <button class="btn" type="submit">Apply</button>
    <a class="btn btn-ghost" href="/bad_tracking.php">Reset</a>
  </form>

  <div class="table-wrap" style="margin-top:12px">
    <table class="table">
      <thead>
        <tr>
          <th>Lead</th>
          <th>Country</th>
          <th>Provider</th>
          <th>Age</th>
          <th>Customer</th>
          <th>Status</th>
          <th>Latest history</th>
          <th>Tracking</th>
          <th>Last details</th>
          <th>Last watch</th>
          <th class="right">Action</th>
        </tr>
      </thead>
      <tbody>
      <?php if(!$rows): ?>
        <tr><td colspan="11" class="muted">No bad tracking leads for this filter.</td></tr>
      <?php endif; ?>
      <?php foreach($rows as $row):
        $lead = (string)$row['lead_number'];
        $age = (int)($row['age_hours'] ?? 0);
        $ageBadge = $age >= 96 ? 'badge-bad' : ($age >= 72 ? 'badge-warn' : '');
        $watchAt = ((string)($row['last_sync_job'] ?? '') === 'watch_tracking') ? (string)($row['last_sync_at'] ?? '') : '';
      ?>
        <tr>
          <td><a class="lead-link" href="/lead.php?lead=<?= urlencode($lead) ?>"><?= h($lead) ?></a></td>
          <td><?= h($row['country']) ?></td>
          <td><span class="badge"><?= h($row['provider']) ?></span></td>
          <td><span class="badge <?= h($ageBadge) ?>"><?= (int)$age ?>h</span></td>
          <td>
            <div class="t-top"><?= h($row['customer_name']) ?></div>
            <div class="t-sub muted"><?= h($row['email']) ?><?= $row['phone'] ? ' - ' . h($row['phone']) : '' ?></div>
          </td>
          <td>
            <span class="badge"><?= h($row['current_status']) ?></span>
            <div class="t-sub muted"><?= h(dd($row['current_status_at'])) ?></div>
          </td>
          <td>
            <div class="t-top"><?= h($row['latest_raw'] ?: $row['latest_norm']) ?></div>
            <div class="t-sub muted"><?= h(dd($row['latest_at'])) ?></div>
          </td>
          <td>
            <div class="t-top"><?= h($row['tracking_number'] ?: 'Pending') ?></div>
            <?php if(!empty($row['order_id_remote'])): ?><div class="t-sub muted">order <?= h($row['order_id_remote']) ?></div><?php endif; ?>
          </td>
          <td>
            <div class="t-top"><?= h(dd($row['details_fetched_at'])) ?></div>
            <div class="t-sub muted"><?= (int)($row['details_age_hours'] ?? 0) ?>h ago</div>
          </td>
          <td>
            <?php if($watchAt !== ''): ?>
              <div class="t-top"><?= h(dd($watchAt)) ?></div>
              <div class="t-sub muted">watch_tracking</div>
            <?php else: ?>
              <span class="muted">Never</span>
            <?php endif; ?>
          </td>
          <td class="right">
            <a class="btn btn-sm" target="_blank" rel="noopener" href="/cron/run.php?job=watch_tracking&key=<?= urlencode($cronKey) ?>&lead=<?= urlencode($lead) ?>&force=1&limit=1">Run</a>
          </td>
        </tr>
      <?php endforeach; ?>
      </tbody>
    </table>
  </div>
</section>

<?php require_once __DIR__ . '/_inc/layout_bottom.php'; ?>