<?php
declare(strict_types=1);

ini_set('display_errors', '0'); // em produção deixa 0
ini_set('display_startup_errors', '0');
error_reporting(E_ALL);

$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/kpi.php';
require __DIR__ . '/app/leads_stage.php';

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

try {
    $month = (string)($_GET['month'] ?? date('Y-m'));
    if (!preg_match('/^\d{4}-\d{2}$/', $month)) $month = date('Y-m');

    $from = $month . "-01";
    $to   = (new DateTime($from))->modify('+1 month')->format('Y-m-d');

    // TCPDF
    $tcpdfPath = __DIR__ . '/vendor/tcpdf/tcpdf.php';
    if (!file_exists($tcpdfPath)) {
        http_response_code(500);
        exit("TCPDF não encontrado em /vendor/tcpdf/. Confirma que existe: /vendor/tcpdf/tcpdf.php\n");
    }
    require_once $tcpdfPath;

    // ===== KPIs (mês) =====
    $st = $pdo->prepare("
      SELECT
        COUNT(*) AS sales_cnt,
        COALESCE(SUM(s.sale_price),0) AS revenue,
        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,
        COALESCE(SUM(
          (v.buy_price + COALESCE((SELECT SUM(amount) FROM vehicle_costs vc WHERE vc.vehicle_id=v.id),0))
        ),0) AS invested
      FROM sales s
      JOIN vehicles v ON v.id=s.vehicle_id
      WHERE s.sale_date >= ? AND s.sale_date < ?
    ");
    $st->execute([$from, $to]);
    $k = $st->fetch() ?: ['sales_cnt'=>0,'revenue'=>0,'margin'=>0,'invested'=>0];

    $roi = ((float)$k['invested'] > 0) ? ((float)$k['margin']/(float)$k['invested'])*100.0 : 0.0;

    // ===== Funil (snapshot 90 dias) =====
    $funnel = kpi_funnel_counts($pdo, 90);

    // ===== Days-to-sell por canal (mês) =====
    $dts = $pdo->prepare("
      SELECT COALESCE(NULLIF(TRIM(final_channel),''),'sem_canal') AS channel,
             AVG(days_to_sell) AS avg_days,
             COUNT(*) AS n
      FROM sales
      WHERE sale_date >= ? AND sale_date < ?
        AND days_to_sell IS NOT NULL AND days_to_sell >= 0
      GROUP BY channel
      ORDER BY avg_days ASC
    ");
    $dts->execute([$from, $to]);
    $dtsRows = $dts->fetchAll() ?: [];

    // ===== Margem por canal (mês) =====
    $mbc = $pdo->prepare("
      SELECT COALESCE(NULLIF(TRIM(s.final_channel),''),'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
      WHERE s.sale_date >= ? AND s.sale_date < ?
      GROUP BY channel
      ORDER BY margin DESC
    ");
    $mbc->execute([$from, $to]);
    $mbcRows = $mbc->fetchAll() ?: [];

    // ===== Preparação: Top fornecedores (mês) =====
    // FIX: usar created_at (TIMESTAMP/DATETIME) em vez de cost_date
    $prep = $pdo->prepare("
      SELECT COALESCE(NULLIF(TRIM(supplier),''),'sem_fornecedor') AS supplier,
             COALESCE(SUM(amount),0) AS total
      FROM vehicle_costs
      WHERE created_at >= ? AND created_at < ?
      GROUP BY supplier
      ORDER BY total DESC
      LIMIT 12
    ");
    $prep->execute([$from, $to]);
    $prepRows = $prep->fetchAll() ?: [];

    // ===== PDF =====
    $pdf = new TCPDF('P','mm','A4',true,'UTF-8',false);
    $pdf->SetCreator('BlockSpark Automotive DMS');
    $pdf->SetTitle('Relatório Mensal ' . $month);
    $pdf->SetMargins(12,12,12);
    $pdf->SetAutoPageBreak(true, 12);
    $pdf->AddPage();

    $h  = '<h2>Relatório Mensal · '.$month.'</h2>';
    $h .= '<p><b>Vendas:</b> '.(int)$k['sales_cnt'].
          ' | <b>Receita:</b> '.number_format((float)$k['revenue'],2,',','.').' €'.
          ' | <b>Margem:</b> '.number_format((float)$k['margin'],2,',','.').' €'.
          ' | <b>ROI:</b> '.number_format((float)$roi,1,',','.').'%</p>';
    $pdf->writeHTML($h, true, false, true, false, '');

    // Margem por canal
    $pdf->writeHTML('<h3>Margem por canal (mês)</h3>', true, false, true, false, '');
    $html = '<table border="1" cellpadding="6"><tr><th>Canal</th><th>Margem</th></tr>';
    foreach ($mbcRows as $r) {
        $html .= '<tr><td>'.htmlspecialchars((string)$r['channel']).'</td><td>'.
                 number_format((float)$r['margin'],2,',','.').' €</td></tr>';
    }
    if (count($mbcRows) === 0) {
        $html .= '<tr><td colspan="2">Sem dados</td></tr>';
    }
    $html .= '</table>';
    $pdf->writeHTML($html, true, false, true, false, '');

    // Days-to-sell por canal
    $pdf->writeHTML('<h3>Days-to-sell por canal (mês)</h3>', true, false, true, false, '');
    $html = '<table border="1" cellpadding="6"><tr><th>Canal</th><th>Dias médios</th><th>Vendas</th></tr>';
    foreach ($dtsRows as $r) {
        $html .= '<tr><td>'.htmlspecialchars((string)$r['channel']).'</td><td>'.
                 number_format((float)$r['avg_days'],1,',','.').'</td><td>'.
                 (int)$r['n'].'</td></tr>';
    }
    if (count($dtsRows) === 0) {
        $html .= '<tr><td colspan="3">Sem dados</td></tr>';
    }
    $html .= '</table>';
    $pdf->writeHTML($html, true, false, true, false, '');

    // Preparação: fornecedores
    $pdf->writeHTML('<h3>Preparação: Top fornecedores (mês)</h3>', true, false, true, false, '');
    $html = '<table border="1" cellpadding="6"><tr><th>Fornecedor</th><th>Total</th></tr>';
    foreach ($prepRows as $r) {
        $html .= '<tr><td>'.htmlspecialchars((string)$r['supplier']).'</td><td>'.
                 number_format((float)$r['total'],2,',','.').' €</td></tr>';
    }
    if (count($prepRows) === 0) {
        $html .= '<tr><td colspan="2">Sem dados</td></tr>';
    }
    $html .= '</table>';
    $pdf->writeHTML($html, true, false, true, false, '');

    // Funil
    $c = $funnel['counts'] ?? [];
    $rates = $funnel['rates'] ?? [];
    $window = (int)($funnel['window_days'] ?? 90);

    $pdf->writeHTML('<h3>Funil (últimos '.$window.' dias)</h3>', true, false, true, false, '');

    $fhtml = '<table border="1" cellpadding="6"><tr><th>Etapa</th><th>Qtd</th></tr>';
    foreach ($c as $stage => $qty) {
        $fhtml .= '<tr><td>'.htmlspecialchars((string)$stage).'</td><td>'.(int)$qty.'</td></tr>';
    }
    if (count($c) === 0) {
        $fhtml .= '<tr><td colspan="2">Sem dados</td></tr>';
    }
    $fhtml .= '</table>';

    $closeRate = (float)($rates['novo->fechado'] ?? 0);
    $fhtml .= '<p><b>Close rate novo→fechado:</b> '.number_format($closeRate,1,',','.').'%</p>';
    $pdf->writeHTML($fhtml, true, false, true, false, '');

    // output
    $filename = 'relatorio_'.$month.'.pdf';
    $pdf->Output($filename, 'D');

} catch (Throwable $e) {
    http_response_code(500);
    // mensagem curta e útil (não expõe dados sensíveis)
    echo "Erro ao gerar PDF: " . htmlspecialchars($e->getMessage()) . "\n";
}
