<?php
declare(strict_types=1);

function log_lead_stage(PDO $pdo, int $leadId, ?string $from, string $to, ?int $userId, ?string $notes=null): void {
  $st = $pdo->prepare("INSERT INTO lead_stage_events (lead_id,from_stage,to_stage,changed_by,notes) VALUES (?,?,?,?,?)");
  $st->execute([$leadId, $from, $to, $userId, $notes]);
}

function kpi_avg_stage_deltas(PDO $pdo, int $days=90): array {
  // Average time (hours) between stages based on stage events in last N days, grouped by channel (lead.source)
  // Only works from now on (events are recorded when stage changes).
  $st = $pdo->prepare("
    SELECT
      l.source AS channel,
      e.from_stage,
      e.to_stage,
      AVG(TIMESTAMPDIFF(HOUR,
        e_prev.changed_at,
        e.changed_at
      )) AS avg_hours,
      COUNT(*) AS n
    FROM lead_stage_events e
    JOIN leads l ON l.id=e.lead_id
    JOIN lead_stage_events e_prev
      ON e_prev.lead_id = e.lead_id
     AND e_prev.changed_at = (
        SELECT MAX(changed_at) FROM lead_stage_events
        WHERE lead_id=e.lead_id AND changed_at < e.changed_at
      )
    WHERE e.changed_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
      AND e_prev.to_stage = e.from_stage
    GROUP BY channel, e.from_stage, e.to_stage
    ORDER BY channel, avg_hours ASC
  ");
  $st->execute([$days]);
  return $st->fetchAll();
}

function kpi_loss_rate_by_channel(PDO $pdo, int $days=90): array {
  $st = $pdo->prepare("
    SELECT
      source AS channel,
      COUNT(*) AS leads,
      SUM(CASE WHEN stage='perdido' THEN 1 ELSE 0 END) AS lost,
      SUM(CASE WHEN stage='fechado' THEN 1 ELSE 0 END) AS closed
    FROM leads
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
    GROUP BY source
    ORDER BY lost DESC
  ");
  $st->execute([$days]);
  $rows = $st->fetchAll();
  foreach ($rows as &$r) {
    $l = (int)$r['leads'];
    $lost = (int)$r['lost'];
    $r['loss_rate'] = $l>0 ? ($lost/$l)*100.0 : 0.0;
    $r['close_rate'] = $l>0 ? (((int)$r['closed'])/$l)*100.0 : 0.0;
  }
  return $rows;
}
