<?php
$page_title    = 'Dashboard';
$page_subtitle = date('l d \d\e F Y', strtotime('today'));
ob_start();
?>
<a href="prospectos.php?nuevo=1" class="btn btn-primary"><i class="ti ti-plus"></i> Nuevo prospecto</a>
<?php $page_actions = ob_get_clean();
require_once 'layout.php';
$db = getDB();

// ── Métricas base ────────────────────────────────────────────────
$clientes_activos   = $db->query("SELECT COUNT(*) FROM clientes WHERE estado_pago != 'suspendido'")->fetchColumn();
$prospectos_activos = $db->query("SELECT COUNT(*) FROM prospectos WHERE estado NOT IN ('ganado','perdido')")->fetchColumn();
$demos_semana       = $db->query("SELECT COUNT(*) FROM prospectos WHERE estado IN ('demo_agendada','demo_realizada') AND updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)")->fetchColumn();
$mrr                = $db->query("SELECT COALESCE(SUM(precio_mes),0) FROM clientes WHERE estado_pago='al_dia'")->fetchColumn();

// ── Metas desde DB ──────────────────────────────────────────────
$m = getMetas($db);
$metas_rampa = [
    '2026-06'=>['label'=>'Jun','meta'=>$m['rampa_2026_06']],
    '2026-07'=>['label'=>'Jul','meta'=>$m['rampa_2026_07']],
    '2026-08'=>['label'=>'Ago','meta'=>$m['rampa_2026_08']],
    '2026-09'=>['label'=>'Sep','meta'=>$m['rampa_2026_09']],
    '2026-10'=>['label'=>'Oct','meta'=>$m['rampa_2026_10']],
];
$rows_mes = $db->query("SELECT DATE_FORMAT(fecha_inicio,'%Y-%m') as mes, COUNT(*) as total FROM clientes WHERE fecha_inicio IS NOT NULL GROUP BY mes")->fetchAll();
$clientes_por_mes = [];
foreach ($rows_mes as $r) $clientes_por_mes[$r['mes']] = intval($r['total']);

$mes_key      = date('Y-m');
$meta_mes     = $m['rampa_2026_'.date('m')] ?? $m['rampa_2026_06'];
$clientes_mes = intval($clientes_por_mes[$mes_key] ?? 0);

// ── Semáforo comercial (playbook) ────────────────────────────────
$dia_actual = max(1, intval(date('j')));
$dias_mes   = intval(date('t'));
$proyeccion = round(($clientes_mes / $dia_actual) * $dias_mes);
$pct_meta   = $meta_mes > 0 ? min(100, round($clientes_mes / $meta_mes * 100)) : 0;
$pct_proy   = $meta_mes > 0 ? round($proyeccion / $meta_mes * 100) : 0;

if      ($pct_proy >= 100)                  $sema = ['color'=>'verde',   'icon'=>'🟢','label'=>'En meta',      'msg'=>'Ritmo actual cumple el objetivo del mes.'];
elseif  ($pct_proy >= $m['sema_amarillo'])  $sema = ['color'=>'amarillo','icon'=>'🟡','label'=>'Alerta −20%',  'msg'=>'Revisar canal y mensaje de apertura. Doblar esfuerzo.'];
else                                         $sema = ['color'=>'rojo',    'icon'=>'🔴','label'=>'Urgente −40%', 'msg'=>'Reunión de emergencia. Cambio de estrategia requerido.'];

// ── Conversión por canal ─────────────────────────────────────────
$conv_canal = $db->query("
    SELECT canal,
        COUNT(*) as contactados,
        SUM(CASE WHEN estado != 'prospecto' THEN 1 ELSE 0 END) as respondieron,
        SUM(CASE WHEN estado IN ('demo_agendada','demo_realizada','negociacion','propuesta_enviada','ganado') THEN 1 ELSE 0 END) as demos,
        SUM(CASE WHEN estado = 'ganado' THEN 1 ELSE 0 END) as cerrados
    FROM prospectos GROUP BY canal ORDER BY cerrados DESC, contactados DESC
")->fetchAll();

// ── Progreso por sector vs meta ──────────────────────────────────
$metas_sector = ['hoteles'=>$m['sector_hoteles'],'clinicas'=>$m['sector_clinicas'],'inmobiliarias'=>$m['sector_inmobiliarias'],'ecommerce'=>$m['sector_ecommerce'],'servicios'=>$m['sector_servicios']];
$sector_labels = ['hoteles'=>'🏨 Hoteles','clinicas'=>'🏥 Clínicas','inmobiliarias'=>'🏢 Inmob.','ecommerce'=>'🛒 Ecommerce','servicios'=>'⚙️ Servicios'];
$real_rows = $db->query("SELECT sector, COUNT(*) as total FROM clientes GROUP BY sector")->fetchAll();
$real_sector = [];
foreach ($real_rows as $r) $real_sector[$r['sector']] = intval($r['total']);

// ── Velocidad del pipeline (stage_history) ───────────────────────
try {
    $vel_rows = $db->query("
        SELECT sh.estado,
               COUNT(*) as n,
               ROUND(AVG(TIMESTAMPDIFF(HOUR, sh.created_at,
                   COALESCE((SELECT MIN(s2.created_at) FROM stage_history s2
                              WHERE s2.prospecto_id = sh.prospecto_id AND s2.id > sh.id), NOW())
               ) / 24), 1) as avg_dias
        FROM stage_history sh
        GROUP BY sh.estado
        ORDER BY FIELD(sh.estado,'prospecto','contactado','demo_agendada','demo_realizada','negociacion','propuesta_enviada','ganado','perdido')
    ")->fetchAll();
} catch (Exception $e) { $vel_rows = []; }

// ── Follow-ups vencidos ──────────────────────────────────────────
$followups = $db->query("
    SELECT p.id, p.empresa, p.contacto, p.canal, p.estado, p.fecha_followup,
        DATEDIFF(CURDATE(), p.fecha_followup) AS dias_vencido
    FROM prospectos p
    WHERE p.fecha_followup <= CURDATE() AND p.estado NOT IN ('ganado','perdido')
    ORDER BY p.fecha_followup ASC LIMIT 10
")->fetchAll();

$canal_labels = ['whatsapp'=>'WhatsApp','meta_ads'=>'Meta Ads','linkedin'=>'LinkedIn','referidos'=>'Referidos','influencers'=>'Influencers','partnerships'=>'Partnerships','otro'=>'Otro'];
$urgentes_fu  = count(array_filter($followups, fn($f) => $f['dias_vencido'] > 0));
?>

<?php if($urgentes_fu > 0): ?>
<div class="alert alert-danger">
  <i class="ti ti-clock-exclamation"></i>
  <strong><?=$urgentes_fu?> prospectos con follow-up vencido</strong> — revisa el pipeline hoy.
  <a href="pipeline.php" style="margin-left:8px;text-decoration:underline;color:inherit">Ver pipeline →</a>
</div>
<?php endif; ?>

<!-- SEMÁFORO COMERCIAL -->
<div class="sema-com sema-com-<?=$sema['color']?>">
  <span style="font-size:22px;flex-shrink:0"><?=$sema['icon']?></span>
  <div style="flex:1;min-width:0">
    <div style="font-family:'Syne',sans-serif;font-weight:800;font-size:15px"><?=$sema['label']?> — <?=$clientes_mes?> / <?=$meta_mes?> clientes este mes</div>
    <div style="font-size:12px;opacity:.75;margin-top:2px"><?=$sema['msg']?> &nbsp;·&nbsp; Proyección fin de mes: <strong><?=$proyeccion?> clientes</strong></div>
  </div>
  <div style="text-align:right;flex-shrink:0">
    <div style="font-family:'Syne',sans-serif;font-weight:800;font-size:1.5rem;line-height:1"><?=$pct_meta?>%</div>
    <div style="font-size:11px;opacity:.65">meta cumplida</div>
  </div>
</div>

<!-- MÉTRICAS -->
<div class="metrics-grid">
  <div class="metric"><div class="metric-n green"><?=$clientes_activos?></div><div class="metric-l">Clientes activos</div></div>
  <div class="metric"><div class="metric-n"><?=$prospectos_activos?></div><div class="metric-l">Prospectos activos</div></div>
  <div class="metric"><div class="metric-n <?=$demos_semana>=5?'green':($demos_semana>=3?'amber':'red')?>"><?=$demos_semana?></div><div class="metric-l">Demos esta semana</div></div>
  <div class="metric"><div class="metric-n green">$<?=number_format($mrr,0)?></div><div class="metric-l">MRR (USD/mes)</div></div>
</div>

<!-- RAMPA MENSUAL -->
<div class="section-label">Rampa mensual — Meta 100 clientes Jun→Oct 2026</div>
<div class="card" style="margin-bottom:16px">
  <div style="display:grid;grid-template-columns:repeat(5,1fr);gap:8px">
  <?php foreach($metas_rampa as $key=>$info):
    $real  = intval($clientes_por_mes[$key] ?? 0);
    $m     = $info['meta'];
    $pct_r = $m > 0 ? min(100, round($real/$m*100)) : 0;
    $es_hoy= ($key === $mes_key);
    $col   = $pct_r>=100 ? '#0F6E56' : ($pct_r>=80 ? '#854F0B' : ($real>0 ? '#A32D2D' : '#888880'));
    $bg    = $pct_r>=100 ? '#E1F5EE' : ($pct_r>=80 ? '#FAEEDA' : ($real>0 ? '#FCEBEB' : 'var(--off)'));
  ?>
  <div style="background:<?=$bg?>;border-radius:10px;padding:10px 12px;<?=$es_hoy?'border:1.5px solid '.$col.';':''?>">
    <div style="font-size:10px;font-weight:500;color:<?=$col?>;text-transform:uppercase;letter-spacing:.05em"><?=$info['label']?><?=$es_hoy?' ◀':''?></div>
    <div style="font-family:'Syne',sans-serif;font-weight:800;font-size:1.35rem;color:<?=$col?>;line-height:1;margin-top:4px"><?=$real?><span style="font-size:12px;font-weight:500;opacity:.6"> /<?=$m?></span></div>
    <div style="height:4px;background:rgba(0,0,0,.1);border-radius:2px;margin-top:8px;overflow:hidden">
      <div style="height:100%;width:<?=$pct_r?>%;background:<?=$col?>;border-radius:2px"></div>
    </div>
    <div style="font-size:11px;color:<?=$col?>;margin-top:4px;font-weight:500"><?=$pct_r?>%</div>
  </div>
  <?php endforeach; ?>
  </div>
</div>

<!-- CONVERSIÓN CANAL + SECTOR -->
<div style="display:grid;grid-template-columns:1fr 1fr;gap:12px;margin-bottom:16px">

  <div class="card">
    <div class="section-label" style="margin-top:0">Conversión por canal</div>
    <?php if(empty($conv_canal)): ?>
      <p style="font-size:13px;color:var(--muted)">Sin datos — registra prospectos para ver el embudo.</p>
    <?php else: ?>
    <div style="overflow-x:auto">
    <table style="font-size:12px;min-width:280px">
    <thead><tr>
      <th style="width:26%">Canal</th>
      <th style="width:18%;text-align:right">Ctcts.</th>
      <th style="width:18%;text-align:right">Resp%</th>
      <th style="width:18%;text-align:right">Demos</th>
      <th style="width:20%;text-align:right">Cierre%</th>
    </tr></thead>
    <tbody>
    <?php foreach($conv_canal as $r):
      $resp_pct   = $r['contactados']>0 ? round($r['respondieron']/$r['contactados']*100) : 0;
      $cierre_pct = $r['demos']>0       ? round($r['cerrados']/$r['demos']*100)           : 0;
      $rc = $resp_pct  >= $m['kpi_resp_rate'] ? '#0F6E56' : ($resp_pct  >= intval($m['kpi_resp_rate']/2)  ? '#854F0B' : '#A32D2D');
      $cc = $cierre_pct >= $m['kpi_conv_demo'] ? '#0F6E56' : ($cierre_pct >= intval($m['kpi_conv_demo']/2) ? '#854F0B' : ($cierre_pct>0 ? '#A32D2D' : 'var(--muted)'));
    ?>
    <tr>
      <td><span class="badge b-gray" style="font-size:10px"><?=$canal_labels[$r['canal']]??$r['canal']?></span></td>
      <td style="text-align:right;font-weight:500"><?=$r['contactados']?></td>
      <td style="text-align:right;font-weight:600;color:<?=$rc?>"><?=$resp_pct?>%</td>
      <td style="text-align:right"><?=$r['demos']?></td>
      <td style="text-align:right;font-weight:600;color:<?=$cc?>"><?=$cierre_pct>0?$cierre_pct.'%':'—'?></td>
    </tr>
    <?php endforeach; ?>
    </tbody>
    </table>
    </div>
    <div style="margin-top:8px;font-size:11px;color:var(--muted)">Meta: Resp. ≥<?=$m['kpi_resp_rate']?>% · Cierre/demo ≥<?=$m['kpi_conv_demo']?>%</div>
    <?php endif; ?>
  </div>

  <div class="card">
    <div class="section-label" style="margin-top:0">Progreso por sector vs meta</div>
    <?php foreach($metas_sector as $sec=>$meta_sec):
      $real_sec = $real_sector[$sec] ?? 0;
      $pct_sec  = $meta_sec>0 ? min(100, round($real_sec/$meta_sec*100)) : 0;
      $c = $pct_sec>=100 ? '#0F6E56' : ($pct_sec>=60 ? '#854F0B' : '#A32D2D');
    ?>
    <div style="margin-bottom:10px">
      <div style="display:flex;justify-content:space-between;align-items:center;margin-bottom:3px">
        <span style="font-size:12px"><?=$sector_labels[$sec]?></span>
        <span style="font-size:12px;font-weight:600;color:<?=$c?>"><?=$real_sec?>/<?=$meta_sec?></span>
      </div>
      <div style="height:5px;background:var(--off);border-radius:3px;overflow:hidden">
        <div style="height:100%;width:<?=$pct_sec?>%;background:<?=$c?>;border-radius:3px"></div>
      </div>
    </div>
    <?php endforeach; ?>
    <div style="font-size:11px;color:var(--muted);margin-top:6px">Meta total: 100 clientes Jun–Oct 2026</div>
  </div>
</div>

<!-- VELOCIDAD DEL PIPELINE -->
<div class="section-label">Velocidad del pipeline — Tiempo promedio por etapa</div>
<div class="card" style="margin-bottom:16px">
<?php if(empty($vel_rows)): ?>
  <p style="font-size:13px;color:var(--muted);margin:0">Sin datos aún. Los tiempos aparecerán conforme los prospectos avancen de etapa.</p>
<?php else:
  $max_d = max(1, max(array_column($vel_rows,'avg_dias')));
  $v_col = ['prospecto'=>'#888780','contactado'=>'#5B8FBF','demo_agendada'=>'#BA7517','demo_realizada'=>'#185FA5','negociacion'=>'#C77B2A','propuesta_enviada'=>'#534AB7','ganado'=>'#0F6E56','perdido'=>'#A32D2D'];
  $v_lbl = ['prospecto'=>'Prospecto','contactado'=>'Contactado','demo_agendada'=>'Demo agendada','demo_realizada'=>'Demo realizada','negociacion'=>'Negociación','propuesta_enviada'=>'Propuesta enviada','ganado'=>'Ganado','perdido'=>'Perdido'];
?>
  <div style="display:grid;grid-template-columns:1fr 1fr;gap:10px 28px">
  <?php foreach($vel_rows as $vr):
    $col = $v_col[$vr['estado']] ?? '#888';
    $lbl = $v_lbl[$vr['estado']] ?? $vr['estado'];
    $d   = floatval($vr['avg_dias']);
    $pct = round($d / $max_d * 100);
    $dur = $d < 1 ? 'menos de 1 día' : (round($d).' día'.(round($d)!==1?'s':''));
  ?>
  <div>
    <div style="display:flex;justify-content:space-between;align-items:center;margin-bottom:3px">
      <span style="font-size:12px;color:var(--text)"><?=$lbl?></span>
      <span style="font-size:12px;font-weight:700;color:<?=$col?>"><?=$dur?></span>
    </div>
    <div style="height:6px;background:var(--off);border-radius:3px;overflow:hidden">
      <div style="height:100%;width:<?=$pct?>%;background:<?=$col?>;border-radius:3px"></div>
    </div>
    <div style="font-size:10px;color:var(--muted);margin-top:2px"><?=$vr['n']?> muestra<?=$vr['n']>1?'s':''?></div>
  </div>
  <?php endforeach; ?>
  </div>
<?php endif; ?>
</div>

<!-- FOLLOW-UPS VENCIDOS -->
<div class="section-label">Follow-ups vencidos / para hoy</div>
<?php if(empty($followups)): ?>
<div class="alert alert-success"><i class="ti ti-check"></i> Sin follow-ups pendientes. ¡Todo al día!</div>
<?php else: ?>
<div class="table-wrap">
  <table>
    <thead><tr>
      <th style="width:30%">Empresa</th>
      <th style="width:13%">Canal</th>
      <th style="width:18%">Estado</th>
      <th style="width:18%">Follow-up</th>
      <th style="width:21%">Acción</th>
    </tr></thead>
    <tbody>
    <?php foreach($followups as $f):
      $dias = intval($f['dias_vencido']);
      $txt  = $dias===0 ? 'Hoy' : "Hace {$dias} día".($dias>1?'s':'');
      $col  = $dias>=2 ? 'color:#A32D2D' : ($dias===1 ? 'color:#854F0B' : 'color:#0F6E56');
      $eb   = ['prospecto'=>'b-gray','contactado'=>'b-gray','demo_agendada'=>'b-amber','demo_realizada'=>'b-blue','negociacion'=>'b-amber','propuesta_enviada'=>'b-purple'][$f['estado']] ?? 'b-gray';
    ?>
    <tr>
      <td><strong><?=clean($f['empresa'])?></strong><?php if($f['contacto']): ?><br><span style="font-size:11px;color:var(--muted)"><?=clean($f['contacto'])?></span><?php endif; ?></td>
      <td><span class="badge b-green" style="font-size:11px"><?=$canal_labels[$f['canal']]??$f['canal']?></span></td>
      <td><span class="badge <?=$eb?>"><?=str_replace('_',' ',ucfirst($f['estado']))?></span></td>
      <td style="<?=$col?>;font-weight:500"><?=$txt?></td>
      <td><a href="prospectos.php?accion=ver&id=<?=$f['id']?>" class="btn" style="font-size:11px;padding:4px 10px"><i class="ti ti-pencil"></i> Actualizar</a></td>
    </tr>
    <?php endforeach; ?>
    </tbody>
  </table>
</div>
<?php endif; ?>

<style>
.sema-com{display:flex;align-items:center;gap:14px;border-radius:12px;padding:14px 18px;margin-bottom:14px;border:0.5px solid}
.sema-com-verde{background:#E1F5EE;border-color:#1D9E75;color:#085041}
.sema-com-amarillo{background:#FAEEDA;border-color:#EF9F27;color:#633806}
.sema-com-rojo{background:#FCEBEB;border-color:#E24B4A;color:#791F1F}
</style>

<?php require_once 'layout_end.php'; ?>
