<?php
declare(strict_types=1);

function kpi_stock(PDO $pdo): int {
  return (int)$pdo->query("SELECT COUNT(*) c FROM vehicles WHERE status IN ('disponivel','em_preparacao','reservado')")->fetch()['c'];
}

function kpi_avg_days_in_stock(PDO $pdo): float {
  $row = $pdo->query("
    SELECT AVG(DATEDIFF(CURDATE(), entry_date)) AS d
    FROM vehicles
    WHERE status IN ('disponivel','em_preparacao','reservado') AND entry_date IS NOT NULL
  ")->fetch();
  return (float)($row['d'] ?? 0);
}

function kpi_margin_month(PDO $pdo): float {
  $row = $pdo->query("
    SELECT COALESCE(SUM(
      s.sale_price
      - v.buy_price
      - COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id = v.id),0)
      + s.finance_commission + s.insurance_commission
    ),0) AS m
    FROM sales s
    JOIN vehicles v ON v.id=s.vehicle_id
    WHERE s.sale_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  ")->fetch();
  return (float)($row['m'] ?? 0);
}

function kpi_roi_month(PDO $pdo): float {
  $row = $pdo->query("
    SELECT
      COALESCE(SUM(
        s.sale_price
        - v.buy_price
        - COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0)
        + s.finance_commission + s.insurance_commission
      ),0) AS margin_sum,
      COALESCE(SUM(
        v.buy_price + COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0)
      ),0) AS invested_sum
    FROM sales s
    JOIN vehicles v ON v.id=s.vehicle_id
    WHERE s.sale_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  ")->fetch();

  $margin = (float)($row['margin_sum'] ?? 0);
  $invested = (float)($row['invested_sum'] ?? 0);
  if ($invested <= 0) return 0.0;
  return ($margin / $invested) * 100.0;
}

function kpi_slow_movers(PDO $pdo, int $limit = 8): array {
  $st = $pdo->prepare("
    SELECT id, internal_code, brand, model, plate, entry_date,
           DATEDIFF(CURDATE(), entry_date) AS days_stock
    FROM vehicles
    WHERE status IN ('disponivel','em_preparacao','reservado')
    ORDER BY days_stock DESC
    LIMIT ?
  ");
  $st->bindValue(1, $limit, PDO::PARAM_INT);
  $st->execute();
  return $st->fetchAll();
}


function kpi_stock_value(PDO $pdo): float {
  $row = $pdo->query("
    SELECT COALESCE(SUM(
      v.buy_price + COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0)
    ),0) AS invested
    FROM vehicles v
    WHERE v.status IN ('disponivel','em_preparacao','reservado')
  ")->fetch();
  return (float)($row['invested'] ?? 0);
}

function kpi_aging_buckets(PDO $pdo): array {
  $row = $pdo->query("
    SELECT
      SUM(CASE WHEN DATEDIFF(CURDATE(), entry_date) BETWEEN 0 AND 15 THEN 1 ELSE 0 END) AS b15,
      SUM(CASE WHEN DATEDIFF(CURDATE(), entry_date) BETWEEN 16 AND 30 THEN 1 ELSE 0 END) AS b30,
      SUM(CASE WHEN DATEDIFF(CURDATE(), entry_date) BETWEEN 31 AND 60 THEN 1 ELSE 0 END) AS b60,
      SUM(CASE WHEN DATEDIFF(CURDATE(), entry_date) >= 61 THEN 1 ELSE 0 END) AS b61
    FROM vehicles
    WHERE status IN ('disponivel','em_preparacao','reservado') AND entry_date IS NOT NULL
  ")->fetch();
  return [
    '0-15' => (int)($row['b15'] ?? 0),
    '16-30' => (int)($row['b30'] ?? 0),
    '31-60' => (int)($row['b60'] ?? 0),
    '61+' => (int)($row['b61'] ?? 0),
  ];
}

function kpi_top_margins(PDO $pdo, int $limit=8): array {
  $st = $pdo->prepare("
    SELECT
      s.sale_date,
      v.internal_code, v.brand, v.model, v.plate,
      s.sale_price,
      (v.buy_price + COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0)) AS invested,
      (
        s.sale_price
        - (v.buy_price + COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0))
        + s.finance_commission + s.insurance_commission
      ) AS margin
    FROM sales s
    JOIN vehicles v ON v.id=s.vehicle_id
    ORDER BY margin DESC
    LIMIT ?
  ");
  $st->bindValue(1, $limit, PDO::PARAM_INT);
  $st->execute();
  return $st->fetchAll();
}


function kpi_margin_12months(PDO $pdo): array {
  // Returns array of ['label' => 'YYYY-MM', 'margin' => float]
  $rows = $pdo->query("
    SELECT DATE_FORMAT(s.sale_date, '%Y-%m') AS ym,
           COALESCE(SUM(
             s.sale_price
             - v.buy_price
             - COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0)
             + s.finance_commission + s.insurance_commission
           ),0) AS margin
    FROM sales s
    JOIN vehicles v ON v.id=s.vehicle_id
    WHERE s.sale_date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 11 MONTH)
    GROUP BY ym
    ORDER BY ym ASC
  ")->fetchAll();

  $map = [];
  foreach ($rows as $r) $map[$r['ym']] = (float)$r['margin'];

  $out = [];
  $d = new DateTime(date('Y-m-01'));
  $d->modify('-11 months');
  for ($i=0; $i<12; $i++) {
    $key = $d->format('Y-m');
    $out[] = ['label' => $key, 'margin' => (float)($map[$key] ?? 0)];
    $d->modify('+1 month');
  }
  return $out;
}

function kpi_stock_over_days(PDO $pdo, int $days): array {
  $st = $pdo->prepare("
    SELECT id, internal_code, brand, model, plate, entry_date,
           DATEDIFF(CURDATE(), entry_date) AS days_stock
    FROM vehicles
    WHERE status IN ('disponivel','em_preparacao','reservado')
      AND entry_date IS NOT NULL
      AND DATEDIFF(CURDATE(), entry_date) >= ?
    ORDER BY days_stock DESC
  ");
  $st->execute([$days]);
  return $st->fetchAll();
}

function kpi_roi_ranking(PDO $pdo, int $limit = 15): array {
  // ROI per vehicle for SOLD vehicles in last 12 months
  $st = $pdo->prepare("
    SELECT
      s.sale_date,
      v.id AS vehicle_id,
      v.internal_code, v.brand, v.model, v.plate,
      s.sale_price,
      (v.buy_price + COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0)) AS invested,
      (
        s.sale_price
        - (v.buy_price + COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0))
        + s.finance_commission + s.insurance_commission
      ) AS margin
    FROM sales s
    JOIN vehicles v ON v.id=s.vehicle_id
    WHERE s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    HAVING invested > 0
    ORDER BY (margin/invested) DESC
    LIMIT ?
  ");
  $st->bindValue(1, $limit, PDO::PARAM_INT);
  $st->execute();
  $rows = $st->fetchAll();
  foreach ($rows as &$r) {
    $inv = (float)$r['invested'];
    $m = (float)$r['margin'];
    $r['roi'] = $inv > 0 ? ($m/$inv)*100.0 : 0.0;
  }
  return $rows;
}

function roi_badge_class(float $roi): string {
  // Semáforo: ajusta aqui se quiseres
  if ($roi >= 15.0) return 'roi-green';
  if ($roi >= 7.0) return 'roi-yellow';
  return 'roi-red';
}


function kpi_roi_12months(PDO $pdo): array {
  // Returns array of ['label'=>'YYYY-MM','roi'=>float] for last 12 months
  $rows = $pdo->query("
    SELECT DATE_FORMAT(s.sale_date, '%Y-%m') AS ym,
      COALESCE(SUM(
        s.sale_price
        - (v.buy_price + COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0))
        + s.finance_commission + s.insurance_commission
      ),0) AS margin_sum,
      COALESCE(SUM(
        (v.buy_price + COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0))
      ),0) AS invested_sum
    FROM sales s
    JOIN vehicles v ON v.id=s.vehicle_id
    WHERE s.sale_date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 11 MONTH)
    GROUP BY ym
    ORDER BY ym ASC
  ")->fetchAll();

  $map = [];
  foreach ($rows as $r) {
    $inv = (float)$r['invested_sum'];
    $m = (float)$r['margin_sum'];
    $map[$r['ym']] = ($inv > 0) ? ($m/$inv)*100.0 : 0.0;
  }

  $out = [];
  $d = new DateTime(date('Y-m-01'));
  $d->modify('-11 months');
  for ($i=0; $i<12; $i++) {
    $key = $d->format('Y-m');
    $out[] = ['label' => $key, 'roi' => (float)($map[$key] ?? 0)];
    $d->modify('+1 month');
  }
  return $out;
}

function kpi_margin_by_channel(PDO $pdo): array {
  // Uses locked sales.final_channel when available; fallback to last lead source if final_channel is missing/empty
  $rows = $pdo->query("
    SELECT
      COALESCE(NULLIF(TRIM(s.final_channel),''), COALESCE(l.source,'sem_canal')) AS channel,
      COALESCE(SUM(
        s.sale_price
        - (v.buy_price + COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0))
        + s.finance_commission + s.insurance_commission
      ),0) AS margin
    FROM sales s
    JOIN vehicles v ON v.id=s.vehicle_id
    LEFT JOIN (
      SELECT l1.*
      FROM leads l1
      INNER JOIN (
        SELECT vehicle_id, MAX(created_at) AS max_created
        FROM leads
        WHERE vehicle_id IS NOT NULL
        GROUP BY vehicle_id
      ) x ON x.vehicle_id = l1.vehicle_id AND x.max_created = l1.created_at
    ) l ON l.vehicle_id = v.id
    WHERE s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    GROUP BY channel
    ORDER BY margin DESC
  ")->fetchAll();

  $out = [];
  foreach ($rows as $r) $out[] = ['channel' => $r['channel'], 'margin' => (float)$r['margin']];
  return $out;
}

function prep_cost_types(): array {
  return ['recondicionamento','pecas','pintura','limpeza','outsourcing','garantia'];
}

function kpi_avg_prep_cost_per_vehicle(PDO $pdo): float {
  // Average preparation cost per vehicle (stock + sold) based on selected cost types
  $types = prep_cost_types();
  $in = implode(',', array_fill(0, count($types), '?'));

  $st = $pdo->prepare("
    SELECT AVG(t.total_cost) AS avg_cost FROM (
      SELECT v.id,
        COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id AND vc.cost_type IN ($in)),0) AS total_cost
      FROM vehicles v
    ) t
  ");
  $st->execute($types);
  $row = $st->fetch();
  return (float)($row['avg_cost'] ?? 0);
}

function kpi_prep_cost_by_supplier(PDO $pdo, int $limit=10): array {
  $types = prep_cost_types();
  $in = implode(',', array_fill(0, count($types), '?'));

  $st = $pdo->prepare("
    SELECT
      COALESCE(NULLIF(TRIM(supplier),''),'sem_fornecedor') AS supplier,
      COUNT(*) AS items,
      COALESCE(SUM(amount),0) AS total,
      COALESCE(AVG(amount),0) AS avg_item
    FROM vehicle_costs
    WHERE cost_type IN ($in)
    GROUP BY supplier
    ORDER BY total DESC
    LIMIT $limit
  ");
  $st->execute($types);
  return $st->fetchAll();
}


function kpi_top_bottom_margins(PDO $pdo, int $limit=10): array {
  $base = "
    SELECT
      s.sale_date,
      v.id AS vehicle_id,
      v.internal_code, v.brand, v.model, v.plate,
      s.sale_price,
      (v.buy_price + COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0)) AS invested,
      (
        s.sale_price
        - (v.buy_price + COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0))
        + s.finance_commission + s.insurance_commission
      ) AS margin
    FROM sales s
    JOIN vehicles v ON v.id=s.vehicle_id
    WHERE s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
  ";

  $stTop = $pdo->prepare($base . " ORDER BY margin DESC LIMIT ?");
  $stTop->bindValue(1, $limit, PDO::PARAM_INT);
  $stTop->execute();
  $top = $stTop->fetchAll();

  $stBot = $pdo->prepare($base . " ORDER BY margin ASC LIMIT ?");
  $stBot->bindValue(1, $limit, PDO::PARAM_INT);
  $stBot->execute();
  $bottom = $stBot->fetchAll();

  return ['top'=>$top,'bottom'=>$bottom];
}

function kpi_conversion_by_channel(PDO $pdo): array {
  // Leads counts by source (last 12 months)
  $leads = $pdo->query("
    SELECT source AS channel, COUNT(*) AS leads
    FROM leads
    WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    GROUP BY source
  ")->fetchAll();

  $leadMap = [];
  foreach ($leads as $r) $leadMap[$r['channel']] = (int)$r['leads'];

  // Sales by locked channel
  $sales = $pdo->query("
    SELECT COALESCE(NULLIF(TRIM(final_channel),''),'sem_canal') AS channel, COUNT(*) AS sales
    FROM sales
    WHERE sale_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    GROUP BY channel
  ")->fetchAll();

  $saleMap = [];
  foreach ($sales as $r) $saleMap[$r['channel']] = (int)$r['sales'];

  $channels = array_unique(array_merge(array_keys($leadMap), array_keys($saleMap)));
  sort($channels);

  $out = [];
  foreach ($channels as $ch) {
    $l = (int)($leadMap[$ch] ?? 0);
    $s = (int)($saleMap[$ch] ?? 0);
    $conv = ($l > 0) ? ($s / $l) * 100.0 : 0.0;
    $out[] = ['channel'=>$ch,'leads'=>$l,'sales'=>$s,'conversion'=>$conv];
  }
  usort($out, fn($a,$b)=> ($b['sales'] <=> $a['sales']));
  return $out;
}

function kpi_avg_prep_days(PDO $pdo): float {
  // Average days between entry_date and ready_date when ready_date filled
  $row = $pdo->query("
    SELECT AVG(DATEDIFF(ready_date, entry_date)) AS d
    FROM vehicles
    WHERE entry_date IS NOT NULL AND ready_date IS NOT NULL
      AND DATEDIFF(ready_date, entry_date) >= 0
  ")->fetch();
  return (float)($row['d'] ?? 0);
}

function kpi_prep_backlog(PDO $pdo, int $days = 10): int {
  // Vehicles still in preparation for >= N days
  $st = $pdo->prepare("
    SELECT COUNT(*) AS c
    FROM vehicles
    WHERE status='em_preparacao'
      AND entry_date IS NOT NULL
      AND DATEDIFF(CURDATE(), entry_date) >= ?
  ");
  $st->execute([$days]);
  return (int)($st->fetch()['c'] ?? 0);
}


function kpi_days_to_sell_by_channel(PDO $pdo): array {
  $rows = $pdo->query("
    SELECT
      COALESCE(NULLIF(TRIM(final_channel),''),'sem_canal') AS channel,
      AVG(days_to_sell) AS avg_days,
      COUNT(*) AS sales
    FROM sales
    WHERE sale_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
      AND days_to_sell IS NOT NULL AND days_to_sell >= 0
    GROUP BY channel
    ORDER BY avg_days ASC
  ")->fetchAll();
  $out = [];
  foreach ($rows as $r) {
    $out[] = ['channel'=>$r['channel'], 'avg_days'=>(float)$r['avg_days'], 'sales'=>(int)$r['sales']];
  }
  return $out;
}

function kpi_days_to_sell_by_vehicle(PDO $pdo, int $limit=30): array {
  $st = $pdo->prepare("
    SELECT
      s.sale_date,
      v.id AS vehicle_id,
      v.internal_code, v.brand, v.model, v.plate,
      s.days_to_sell,
      COALESCE(NULLIF(TRIM(s.final_channel),''),'sem_canal') AS channel
    FROM sales s
    JOIN vehicles v ON v.id=s.vehicle_id
    WHERE s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
      AND s.days_to_sell IS NOT NULL
    ORDER BY s.days_to_sell DESC
    LIMIT ?
  ");
  $st->bindValue(1, $limit, PDO::PARAM_INT);
  $st->execute();
  return $st->fetchAll();
}

function kpi_funnel_counts(PDO $pdo, int $days=90): array {
  $st = $pdo->prepare("
    SELECT stage, COUNT(*) AS c
    FROM leads
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
    GROUP BY stage
  ");
  $st->execute([$days]);
  $rows = $st->fetchAll();
  $map = [
    'novo'=>0,'contactado'=>0,'test_drive'=>0,'proposta'=>0,'financiamento'=>0,'fechado'=>0,'perdido'=>0
  ];
  foreach ($rows as $r) {
    $k = (string)$r['stage'];
    if (isset($map[$k])) $map[$k] = (int)$r['c'];
  }
  // rates between stages (simple step conversion)
  $rates = [];
  $steps = ['novo','contactado','test_drive','proposta','financiamento','fechado'];
  for ($i=0; $i<count($steps)-1; $i++){
    $a = $steps[$i]; $b = $steps[$i+1];
    $den = max(1, $map[$a]);
    $rates["$a->$b"] = ($map[$b]/$den)*100.0;
  }
  // close rate from novo to fechado
  $rates["novo->fechado"] = ($map['fechado']/max(1,$map['novo']))*100.0;

  return ['counts'=>$map,'rates'=>$rates,'window_days'=>$days];
}
