<?php
declare(strict_types=1);

$config = require __DIR__ . '/app/config.php';
require __DIR__ . '/app/db.php';
require __DIR__ . '/app/auth.php';
require __DIR__ . '/app/helpers.php';
require __DIR__ . '/app/acl.php';
require __DIR__ . '/app/layout.php';
require __DIR__ . '/app/kpi.php';

require_login($config);
require_role($config, ['admin','manager','finance','sales','ops','viewer']);

$stock = kpi_stock($pdo);
$avgDays = kpi_avg_days_in_stock($pdo);
$marginMonth = kpi_margin_month($pdo);
$roiMonth = kpi_roi_month($pdo);
$slow = kpi_slow_movers($pdo, 8);

$stockValue = kpi_stock_value($pdo);
$aging = kpi_aging_buckets($pdo);
$topMargins = kpi_top_margins($pdo, 8);

$m12 = kpi_margin_12months($pdo);
$alertDays = (int)($_GET['days'] ?? 60);
if ($alertDays < 1) $alertDays = 60;
$alerts = kpi_stock_over_days($pdo, $alertDays);
$roiRank = kpi_roi_ranking($pdo, 15);

$roi12 = kpi_roi_12months($pdo);
$marginByChannel = kpi_margin_by_channel($pdo);
$avgPrep = kpi_avg_prep_cost_per_vehicle($pdo);
$prepBySupplier = kpi_prep_cost_by_supplier($pdo, 12);

$tbMargins = kpi_top_bottom_margins($pdo, 8);
$conv = kpi_conversion_by_channel($pdo);
$avgPrepDays = kpi_avg_prep_days($pdo);
$prepBacklog = kpi_prep_backlog($pdo, 14);

$dtsChannel = kpi_days_to_sell_by_channel($pdo);
$dtsVehicles = kpi_days_to_sell_by_vehicle($pdo, 25);
$funnel = kpi_funnel_counts($pdo, 90);
try {
    $lossByChannel = kpi_loss_rate_by_channel($pdo, 90);
    $stageDeltas   = kpi_avg_stage_deltas($pdo, 90);
} catch (Throwable $e) {
    $lossByChannel = [];
    $stageDeltas = [];
}

render_header($config, 'Dashboard');
?>
  <div class="grid">
    <div class="card"><h3>Stock ativo</h3><p class="big"><?= (int)$stock ?></p></div>
    <div class="card"><h3>Dias médios em stock</h3><p class="big"><?= (int)round($avgDays) ?></p></div>
    <div class="card"><h3>Margem (mês)</h3><p class="big"><?= e(money($marginMonth)) ?></p></div>
    <div class="card"><h3>ROI (mês)</h3><p class="big"><?= number_format($roiMonth, 1, ',', '.') ?>%</p></div>
  </div>

  <div class="card" style="margin-top:14px">
    <h3>Viaturas mais paradas</h3>
    <table>
      <thead><tr><th>Código</th><th>Viatura</th><th>Matrícula</th><th>Entrada</th><th>Dias</th><th>Abrir</th></tr></thead>
      <tbody>
        <?php foreach ($slow as $v): ?>
          <tr>
            <td><?= e($v['internal_code']) ?></td>
            <td><?= e(trim($v['brand'].' '.$v['model'])) ?></td>
            <td><?= e((string)$v['plate']) ?></td>
            <td><?= e((string)$v['entry_date']) ?></td>
            <td><span class="badge"><?= e((string)$v['days_stock']) ?></span></td>
            <td><a href="vehicle_view.php?id=<?= (int)$v['id'] ?>">Ver</a></td>
          </tr>
        <?php endforeach; ?>
      </tbody>
    </table>
  </div>

  <div class="grid" style="margin-top:14px">
    <div class="card">
      <h3>Capital imobilizado (stock)</h3>
      <p class="big"><?= e(money($stockValue)) ?></p>
    </div>
    <div class="card">
      <h3>Stock Aging</h3>
      <p class="badge">0-15: <?= (int)$aging['0-15'] ?></p>
      <p class="badge">16-30: <?= (int)$aging['16-30'] ?></p>
      <p class="badge">31-60: <?= (int)$aging['31-60'] ?></p>
      <p class="badge">61+: <?= (int)$aging['61+'] ?></p>
    </div>
  </div>

  <div class="card" style="margin-top:14px">
    <h3>Top margens históricas</h3>
    <table>
      <thead><tr><th>Data</th><th>Viatura</th><th>Matrícula</th><th>Investido</th><th>Venda</th><th>Margem</th></tr></thead>
      <tbody>
        <?php foreach ($topMargins as $r): ?>
          <tr>
            <td><?= e($r['sale_date']) ?></td>
            <td><?= e(trim($r['internal_code'].' '.$r['brand'].' '.$r['model'])) ?></td>
            <td><?= e((string)$r['plate']) ?></td>
            <td><?= e(money($r['invested'])) ?></td>
            <td><?= e(money($r['sale_price'])) ?></td>
            <td><span class="badge"><?= e(money($r['margin'])) ?></span></td>
          </tr>
        <?php endforeach; ?>
      </tbody>
    </table>
  </div>


  <div class="card chart-card" style="margin-top:14px">
    <h3>Margem por mês (últimos 12 meses)</h3>
    <div class="chart-wrap">
      <canvas id="marginChart"></canvas>
    </div>
  </div>

  <div class="card" style="margin-top:14px">
    <div style="display:flex;justify-content:space-between;align-items:center;gap:12px;flex-wrap:wrap">
      <h3 style="margin:0">Alertas: viaturas &ge; <?= (int)$alertDays ?> dias em stock</h3>
      <form method="get" style="display:flex;gap:8px;align-items:center">
        <label style="font-size:12px;color:#6b7280">Dias</label>
        <input name="days" value="<?= (int)$alertDays ?>" style="width:90px">
        <button style="width:auto;padding:10px 12px">Aplicar</button>
      </form>
    </div>
    <table>
      <thead><tr><th>Código</th><th>Viatura</th><th>Matrícula</th><th>Entrada</th><th>Dias</th><th>Abrir</th></tr></thead>
      <tbody>
        <?php foreach ($alerts as $v): ?>
          <tr>
            <td><?= e($v['internal_code']) ?></td>
            <td><?= e(trim($v['brand'].' '.$v['model'])) ?></td>
            <td><?= e((string)$v['plate']) ?></td>
            <td><?= e((string)$v['entry_date']) ?></td>
            <td><span class="badge"><?= e((string)$v['days_stock']) ?></span></td>
            <td><a href="vehicle_view.php?id=<?= (int)$v['id'] ?>">Ver</a></td>
          </tr>
        <?php endforeach; ?>
        <?php if (count($alerts) === 0): ?>
          <tr><td colspan="6" style="color:#6b7280">Sem alertas para este limiar.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>

  <div class="card" style="margin-top:14px">
    <h3>Ranking ROI por viatura (últimos 12 meses)</h3>
    <table>
      <thead><tr><th>Data</th><th>Viatura</th><th>Matrícula</th><th>Investido</th><th>Margem</th><th>ROI</th></tr></thead>
      <tbody>
        <?php foreach ($roiRank as $r): ?>
          <?php $roi = (float)$r['roi']; $cls = roi_badge_class($roi); ?>
          <tr>
            <td><?= e((string)$r['sale_date']) ?></td>
            <td><?= e(trim($r['internal_code'].' '.$r['brand'].' '.$r['model'])) ?></td>
            <td><?= e((string)$r['plate']) ?></td>
            <td><?= e(money($r['invested'])) ?></td>
            <td><?= e(money($r['margin'])) ?></td>
            <td><span class="badge <?= e($cls) ?>"><?= number_format($roi, 1, ',', '.') ?>%</span></td>
          </tr>
        <?php endforeach; ?>
        <?php if (count($roiRank) === 0): ?>
          <tr><td colspan="6" style="color:#6b7280">Sem vendas suficientes para calcular ROI.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>

  
  <div class="card chart-card" style="margin-top:14px">
    <h3>ROI mensal (últimos 12 meses)</h3>
    <div class="chart-wrap"><canvas id="roiChart"></canvas></div>
  </div>

  <div class="card chart-card" style="margin-top:14px">
    <h3>Margem por canal (últimos 12 meses)</h3>
    <div class="chart-wrap"><canvas id="channelChart"></canvas></div>
    <p style="color:#6b7280;font-size:12px;margin-top:8px">Canal inferido pela lead mais recente associada à viatura. Sem lead = “sem_canal”.</p>
  </div>

  <div class="grid" style="margin-top:14px">
    <div class="card">
      <h3>Custo médio de preparação por viatura</h3>
      <p class="big"><?= e(money($avgPrep)) ?></p>
      <p style="color:#6b7280;font-size:12px;margin:6px 0 0">Inclui: recondicionamento, peças, pintura, limpeza, outsourcing, garantia.</p>
    </div>
    <div class="card">
      <h3>Top fornecedores (preparação)</h3>
      <table>
        <thead><tr><th>Fornecedor</th><th>Total</th><th>Média item</th><th>Linhas</th></tr></thead>
        <tbody>
          <?php foreach ($prepBySupplier as $r): ?>
            <tr>
              <td><?= e((string)$r['supplier']) ?></td>
              <td><?= e(money($r['total'])) ?></td>
              <td><?= e(money($r['avg_item'])) ?></td>
              <td><?= (int)$r['items'] ?></td>
            </tr>
          <?php endforeach; ?>
          <?php if (count($prepBySupplier) === 0): ?>
            <tr><td colspan="4" style="color:#6b7280">Sem dados.</td></tr>
          <?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>


  
  <div class="grid" style="margin-top:14px">
    <div class="card">
      <h3>Tempo médio de preparação</h3>
      <p class="big"><?= (int)round($avgPrepDays) ?> dias</p>
      <p style="color:#6b7280;font-size:12px;margin:6px 0 0">Calculado por viaturas com <b>Data disponível</b> preenchida.</p>
    </div>
    <div class="card">
      <h3>Backlog preparação (≥ 14 dias)</h3>
      <p class="big"><?= (int)$prepBacklog ?></p>
      <p style="color:#6b7280;font-size:12px;margin:6px 0 0">Viaturas em <b>em_preparacao</b> há 14+ dias.</p>
    </div>
  </div>

  <div class="card" style="margin-top:14px">
    <h3>Conversão por canal (últimos 12 meses)</h3>
    <div class="chart-wrap" style="margin:8px 0 12px"><canvas id="convChart"></canvas></div>
    <table>
      <thead><tr><th>Canal</th><th>Leads</th><th>Vendas</th><th>Conversão</th></tr></thead>
      <tbody>
        <?php foreach ($conv as $r): ?>
          <tr>
            <td><?= e($r['channel']) ?></td>
            <td><?= (int)$r['leads'] ?></td>
            <td><?= (int)$r['sales'] ?></td>
            <td><span class="badge"><?= number_format((float)$r['conversion'], 1, ',', '.') ?>%</span></td>
          </tr>
        <?php endforeach; ?>
        <?php if (count($conv) === 0): ?>
          <tr><td colspan="4" style="color:#6b7280">Sem dados.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>

  <div class="grid" style="margin-top:14px">
    <div class="card">
      <h3>Top margens (12 meses)</h3>
      <table>
        <thead><tr><th>Viatura</th><th>Margem</th><th>Abrir</th></tr></thead>
        <tbody>
          <?php foreach ($tbMargins['top'] as $r): ?>
            <tr>
              <td><?= e(trim($r['internal_code'].' '.$r['brand'].' '.$r['model'])) ?></td>
              <td><span class="badge"><?= e(money($r['margin'])) ?></span></td>
              <td><a href="vehicle_view.php?id=<?= (int)$r['vehicle_id'] ?>">Ver</a></td>
            </tr>
          <?php endforeach; ?>
        </tbody>
      </table>
    </div>
    <div class="card">
      <h3>Bottom margens (12 meses)</h3>
      <table>
        <thead><tr><th>Viatura</th><th>Margem</th><th>Abrir</th></tr></thead>
        <tbody>
          <?php foreach ($tbMargins['bottom'] as $r): ?>
            <tr>
              <td><?= e(trim($r['internal_code'].' '.$r['brand'].' '.$r['model'])) ?></td>
              <td><span class="badge roi-red"><?= e(money($r['margin'])) ?></span></td>
              <td><a href="vehicle_view.php?id=<?= (int)$r['vehicle_id'] ?>">Ver</a></td>
            </tr>
          <?php endforeach; ?>
        </tbody>
      </table>
    </div>
  </div>


  
  <div class="card" style="margin-top:14px">
    <h3>Funil (últimos <?= (int)$funnel['window_days'] ?> dias)</h3>
    <?php $c = $funnel['counts']; $r = $funnel['rates']; ?>
    <div class="grid" style="grid-template-columns:repeat(4,1fr)">
      <div class="card"><h3>Novo</h3><p class="big"><?= (int)$c['novo'] ?></p></div>
      <div class="card"><h3>Contactado</h3><p class="big"><?= (int)$c['contactado'] ?></p><p class="badge"><?= number_format((float)$r['novo->contactado'],1,',','.') ?>%</p></div>
      <div class="card"><h3>Proposta</h3><p class="big"><?= (int)$c['proposta'] ?></p><p class="badge"><?= number_format((float)$r['contactado->test_drive'],1,',','.') ?>%</p></div>
      <div class="card"><h3>Financiamento</h3><p class="big"><?= (int)$c['financiamento'] ?></p><p class="badge"><?= number_format((float)$r['proposta->financiamento'],1,',','.') ?>%</p></div>
    </div>
    <div style="margin-top:10px;color:#6b7280;font-size:12px">
      Fechados: <b><?= (int)$c['fechado'] ?></b> · Perdidos: <b><?= (int)$c['perdido'] ?></b> · Close rate (novo→fechado): <b><?= number_format((float)$r['novo->fechado'],1,',','.') ?>%</b>
    </div>
  </div>

  <div class="card chart-card" style="margin-top:14px">
    <h3>Tempo médio até vender (Days-to-sell) por canal</h3>
    <div class="chart-wrap"><canvas id="dtsChart"></canvas></div>
  </div>


  <div class="card" style="margin-top:14px">
    <h3>Taxa de perda por canal (últimos 90 dias)</h3>
    <table>
      <thead><tr><th>Canal</th><th>Leads</th><th>Perdidos</th><th>Fechados</th><th>Loss rate</th><th>Close rate</th></tr></thead>
      <tbody>
        <?php foreach ($lossByChannel as $r): ?>
          <tr>
            <td><?= e($r['channel']) ?></td>
            <td><?= (int)$r['leads'] ?></td>
            <td><?= (int)$r['lost'] ?></td>
            <td><?= (int)$r['closed'] ?></td>
            <td><span class="badge roi-red"><?= number_format((float)$r['loss_rate'],1,',','.') ?>%</span></td>
            <td><span class="badge roi-green"><?= number_format((float)$r['close_rate'],1,',','.') ?>%</span></td>
          </tr>
        <?php endforeach; ?>
        <?php if (count($lossByChannel)===0): ?>
          <tr><td colspan="6" style="color:#6b7280">Sem dados.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>

  <div class="card" style="margin-top:14px">
    <h3>Tempo médio entre etapas (baseado em eventos, últimos 90 dias)</h3>
    <p style="color:#6b7280;font-size:12px;margin-top:-6px">Este KPI começa a ser fiável a partir do momento em que começares a atualizar stages via “Lead → Guardar”.</p>
    <table>
      <thead><tr><th>Canal</th><th>De</th><th>Para</th><th>Média (horas)</th><th>Eventos</th></tr></thead>
      <tbody>
        <?php foreach ($stageDeltas as $r): ?>
          <tr>
            <td><?= e($r['channel']) ?></td>
            <td><?= e($r['from_stage']) ?></td>
            <td><span class="badge"><?= e($r['to_stage']) ?></span></td>
            <td><?= number_format((float)$r['avg_hours'],1,',','.') ?></td>
            <td><?= (int)$r['n'] ?></td>
          </tr>
        <?php endforeach; ?>
        <?php if (count($stageDeltas)===0): ?>
          <tr><td colspan="5" style="color:#6b7280">Sem eventos ainda.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>


  <div class="card" style="margin-top:14px">
    <h3>Viaturas com maior tempo até vender (12 meses)</h3>
    <table>
      <thead><tr><th>Viatura</th><th>Canal</th><th>Days-to-sell</th><th>Data venda</th><th>Abrir</th></tr></thead>
      <tbody>
        <?php foreach ($dtsVehicles as $r): ?>
          <tr>
            <td><?= e(trim($r['internal_code'].' '.$r['brand'].' '.$r['model'])) ?></td>
            <td><span class="badge"><?= e((string)$r['channel']) ?></span></td>
            <td><span class="badge"><?= (int)$r['days_to_sell'] ?></span></td>
            <td><?= e((string)$r['sale_date']) ?></td>
            <td><a href="vehicle_view.php?id=<?= (int)$r['vehicle_id'] ?>">Ver</a></td>
          </tr>
        <?php endforeach; ?>
        <?php if (count($dtsVehicles) === 0): ?>
          <tr><td colspan="5" style="color:#6b7280">Sem dados (precisas de registar vendas via “Fechar venda”).</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>


  <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js"></script>
  <script>
    const data12 = <?php echo json_encode($m12, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE); ?>;
    const labels = data12.map(x => x.label);
    const values = data12.map(x => x.margin);

    const ctx = document.getElementById('marginChart');
    new Chart(ctx, {
      type: 'line',
      data: {
        labels,
        datasets: [{
          label: 'Margem (€)',
          data: values,
          tension: 0.25,
          fill: false
        }]
      },
      options: {
        responsive: true,
        maintainAspectRatio: false,
        plugins: {
          legend: { display: true }
        },
        scales: {
          y: {
            ticks: {
              callback: (v) => (new Intl.NumberFormat('pt-PT')).format(v) + ' €'
            }
          }
        }
      }
    });


    // ROI chart
    const roi12 = <?php echo json_encode($roi12, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE); ?>;
    const roiLabels = roi12.map(x => x.label);
    const roiValues = roi12.map(x => x.roi);
    const roiCtx = document.getElementById('roiChart');
    new Chart(roiCtx, {
      type: 'line',
      data: { labels: roiLabels, datasets: [{ label: 'ROI (%)', data: roiValues, tension: 0.25, fill: false }] },
      options: {
        responsive:true, maintainAspectRatio:false,
        scales:{ y:{ ticks:{ callback:(v)=> (new Intl.NumberFormat('pt-PT')).format(v)+' %' } } }
      }
    });

    // Margin by channel chart
    const mbc = <?php echo json_encode($marginByChannel, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE); ?>;
    const chLabels = mbc.map(x => x.channel);
    const chValues = mbc.map(x => x.margin);
    const chCtx = document.getElementById('channelChart');
    new Chart(chCtx, {
      type: 'bar',
      data: { labels: chLabels, datasets: [{ label: 'Margem (€)', data: chValues }] },
      options: {
        responsive:true, maintainAspectRatio:false,
        plugins:{ legend:{ display:true } },
        scales:{ y:{ ticks:{ callback:(v)=> (new Intl.NumberFormat('pt-PT')).format(v)+' €' } } }
      }
    });

  </script>

<?php render_footer(); ?>
