<?php
require_once __DIR__ . "/_commons.php";

$pdo = pdo_m();
$GF  = get_global_filters();

if (!require_filters_guard($GF)) {
  echo '<div class="warn">Aplica los filtros globales para ver el embudo.</div>';
  return;
}

/* ===== Rango/params base ===== */
[$from,$to] = build_range($GF['range'] ?? 'month', $GF['fromUi'] ?? '', $GF['toUi'] ?? '');
$paramsBase = [':site'=>$GF['site'], ':from'=>$from, ':to'=>$to];

/* ===== Filtros globales ===== */
[$campWhere, $campParams] = campaign_source_condition($GF, 'v');
[$advWhere, $advParams, $needAin] = build_advanced_where($GF, 'v', 'ain');
[$noUtmWhere, $noUtmParams] = non_campaign_guard_sql($GF, 'ain', false);

/* ===== Patrones de URL ===== */
$LP1   = '%/vibe-landing-page%';
$LP2   = '%/es/vibe-landing-page%';
$CHK1  = '%/checkout%';
$CHK2  = '%/es/checkout%';

/* ===== Compra por diccionario (cat/act/nam) ===== */
$buyLike = build_purchase_like_sql('cat','act','nam');

/* ===== Compatibilidad: nombre de evento ===== */
function lva_has_col_local($col){
  try{
    $st = pdo_m()->prepare("SHOW COLUMNS FROM matomo_log_link_visit_action LIKE :c");
    $st->execute([':c'=>$col]);
    return (bool)$st->fetch();
  }catch(Throwable $e){ return false; }
}
$hasEventName = lva_has_col_local('idaction_event_name');
$joinEventName = $hasEventName
  ? "LEFT JOIN matomo_log_action nam ON nam.idaction = lva.idaction_event_name"
  : "LEFT JOIN matomo_log_action nam ON nam.idaction = lva.idaction_name";

/* ===== Subconsultas por visita (primer timestamp de cada paso) ===== */
$subLanding = "
  SELECT lva.idvisit, MIN(lva.server_time) AS t_land
  FROM matomo_log_link_visit_action lva
  JOIN matomo_log_action aurl ON aurl.idaction = lva.idaction_url AND aurl.type = 1
  WHERE (aurl.name LIKE :lp1 OR aurl.name LIKE :lp2)
  GROUP BY lva.idvisit
";
$subCheckout = "
  SELECT lva.idvisit, MIN(lva.server_time) AS t_chk
  FROM matomo_log_link_visit_action lva
  JOIN matomo_log_action aurl ON aurl.idaction = lva.idaction_url AND aurl.type = 1
  WHERE (aurl.name LIKE :chk1 OR aurl.name LIKE :chk2)
  GROUP BY lva.idvisit
";
$subPurchase = "
  SELECT lva.idvisit, MIN(lva.server_time) AS t_buy
  FROM matomo_log_link_visit_action lva
  LEFT JOIN matomo_log_action cat ON cat.idaction = lva.idaction_event_category
  LEFT JOIN matomo_log_action act ON act.idaction = lva.idaction_event_action
  $joinEventName
  WHERE $buyLike
  GROUP BY lva.idvisit
";

/* ===== Base: visitas dentro de filtros globales ===== */
$baseWhere = "
  WHERE v.idsite = :site
    AND v.visit_last_action_time BETWEEN :from AND :to
    $campWhere
    $advWhere
";
$joinEntry = "LEFT JOIN matomo_log_action ain ON ain.idaction = v.visit_entry_idaction_url";

/* ===== Contadores ===== */
$landing = $checkout = $purchases = 0;
try{
  // 1) Landing (visitas con landing pageview)
  $sqlL = "
    SELECT COUNT(DISTINCT v.idvisit) AS c
    FROM matomo_log_visit v
    $joinEntry
    JOIN ( $subLanding ) L ON L.idvisit = v.idvisit
    $baseWhere
    $noUtmWhere
  ";
  $stL = $pdo->prepare($sqlL);
  $stL->execute($paramsBase + [
    ':lp1'=>$LP1, ':lp2'=>$LP2
  ] + $campParams + $advParams + $noUtmParams);
  $landing = (int)($stL->fetch()['c'] ?? 0);

  // 2) Checkout DESPUÉS de Landing (en la misma visita)
  $sqlC = "
    SELECT COUNT(DISTINCT v.idvisit) AS c
    FROM matomo_log_visit v
    $joinEntry
    JOIN ( $subLanding )  L ON L.idvisit = v.idvisit
    JOIN ( $subCheckout ) C ON C.idvisit = v.idvisit AND C.t_chk >= L.t_land
    $baseWhere
    $noUtmWhere
  ";
  $stC = $pdo->prepare($sqlC);
  $stC->execute($paramsBase + [
    ':lp1'=>$LP1, ':lp2'=>$LP2, ':chk1'=>$CHK1, ':chk2'=>$CHK2
  ] + $campParams + $advParams + $noUtmParams);
  $checkout = (int)($stC->fetch()['c'] ?? 0);

  // 3) Compra DESPUÉS de Checkout (y por ende después de Landing)
  $sqlB = "
    SELECT COUNT(DISTINCT v.idvisit) AS c
    FROM matomo_log_visit v
    $joinEntry
    JOIN ( $subLanding )  L ON L.idvisit = v.idvisit
    JOIN ( $subCheckout ) C ON C.idvisit = v.idvisit AND C.t_chk >= L.t_land
    JOIN ( $subPurchase ) P ON P.idvisit = v.idvisit AND P.t_buy >= C.t_chk
    $baseWhere
    $noUtmWhere
  ";
  $stB = $pdo->prepare($sqlB);
  $stB->execute($paramsBase + [
    ':lp1'=>$LP1, ':lp2'=>$LP2, ':chk1'=>$CHK1, ':chk2'=>$CHK2
  ] + $campParams + $advParams + $noUtmParams);
  $purchases = (int)($stB->fetch()['c'] ?? 0);
}catch(Throwable $e){
  echo '<div class="warn">No se pudo calcular el embudo.</div>';
}

/* ===== Métricas derivadas ===== */
$rateLC = $landing  > 0 ? round(100*$checkout/$landing,1)  : 0.0;
$rateCP = $checkout > 0 ? round(100*$purchases/$checkout,1): 0.0;

/* ===== UI ===== */
?>
<style>
.fcards{display:grid;grid-template-columns:repeat(3,minmax(160px,1fr));gap:12px;margin:8px 0 14px}
.fcard{border:1px solid var(--bd);border-radius:8px;padding:12px;background:#fff}
.fnum{font:700 28px/1 Arial,Helvetica,sans-serif}
.fmut{color:#6b7280;font-size:12px;margin-top:4px}
.fbar{height:10px;background:#eef2ff;border-radius:999px;overflow:hidden}
.fbar > i{display:block;height:100%;background:#3b82f6}
.frow{display:grid;grid-template-columns:120px 1fr 60px;align-items:center;gap:8px}
</style>

<div class="fcards">
  <div class="fcard">
    <div class="fmut">Landing</div>
    <div class="fnum"><?= number_format($landing) ?></div>
    <div class="fmut">Visitas con landing</div>
  </div>
  <div class="fcard">
    <div class="fmut">Checkout</div>
    <div class="fnum"><?= number_format($checkout) ?></div>
    <div class="fmut">Desde landing · <?= number_format($rateLC,1) ?>%</div>
  </div>
  <div class="fcard">
    <div class="fmut">Compra</div>
    <div class="fnum"><?= number_format($purchases) ?></div>
    <div class="fmut">Desde checkout · <?= number_format($rateCP,1) ?>%</div>
  </div>
</div>

<div class="frow" style="margin-top:6px">
  <div class="fmut">Landing</div>
  <div class="fbar"><i style="width:100%"></i></div>
  <div class="fmut"><?= number_format($landing) ?></div>
</div>
<div class="frow">
  <div class="fmut">Checkout</div>
  <div class="fbar"><i style="width:<?= $landing>0 ? min(100, max(0, 100*$checkout/max(1,$landing))) : 0 ?>%"></i></div>
  <div class="fmut"><?= number_format($checkout) ?></div>
</div>
<div class="frow">
  <div class="fmut">Compra</div>
  <div class="fbar"><i style="width:<?= $checkout>0 ? min(100, max(0, 100*$purchases/max(1,$checkout))) : 0 ?>%"></i></div>
  <div class="fmut"><?= number_format($purchases) ?></div>
</div>

<div class="fmut" style="margin-top:10px">
  Notas: 1) “Landing” busca pageviews que contengan <code>/vibe-landing-page</code> (incluye <code>/es/</code>).<br>
  2) “Checkout” solo cuenta si ocurre <b>después</b> de la landing en la misma visita.<br>
  3) “Compra” usa tu diccionario (categoría/acción/nombre) y solo cuenta si ocurre <b>después</b> del checkout. <br>
  4) Todas las métricas honran el filtro global, la búsqueda avanzada y el guard “Directo sin UTM”.
</div>
