<?php
namespace Modules\Vendas\Controllers;

use Gestoreasy\Core\Http\Request;
use Gestoreasy\Core\Http\Response;
use Gestoreasy\Core\Tenant\ConnectionManager;
use Gestoreasy\Core\Audit\AuditLogger;

class SalesReportsController {
    public function filters(Request $r): Response {
        $pdo = ConnectionManager::forTenant();
        $out = ['products' => [], 'regions' => [], 'sellers' => []];

        // Produtos (top 100) — tenta extrair nome de product_meta; fallback para código
        try {
            $sql = "SELECT p.id,
                           COALESCE(JSON_UNQUOTE(JSON_EXTRACT(pm.meta_json, '$.nome')), p.codigo) AS name
                    FROM products p
                    LEFT JOIN product_meta pm ON pm.product_id = p.id
                    ORDER BY p.id DESC
                    LIMIT 100";
            $stmt = $pdo->query($sql);
            $out['products'] = $stmt->fetchAll();
        } catch (\Throwable $e) {
            try {
                $stmt = $pdo->query("SELECT id, codigo AS name FROM products ORDER BY id DESC LIMIT 100");
                $out['products'] = $stmt->fetchAll();
            } catch (\Throwable $e2) { $out['products'] = []; }
        }

        // Regiões (best effort: cidades de clients)
        try {
            $stmt = $pdo->query("SELECT DISTINCT cidade AS name FROM clients WHERE cidade IS NOT NULL AND cidade <> '' ORDER BY cidade ASC LIMIT 100");
            $out['regions'] = array_map(function($r){ return ['id' => $r['name'], 'name' => $r['name']]; }, $stmt->fetchAll());
        } catch (\Throwable $e) { $out['regions'] = []; }

        // Vendedores (distintos dos logs de criação)
        try {
            $stmt = $pdo->query("SELECT DISTINCT al.user_id AS id, u.name AS name FROM audit_logs al LEFT JOIN users u ON u.id = al.user_id WHERE al.action = 'create' AND al.user_id IS NOT NULL ORDER BY u.name ASC");
            $rows = $stmt->fetchAll();
            $out['sellers'] = array_values(array_filter($rows, function($r){ return !empty($r['id']); }));
        } catch (\Throwable $e) { $out['sellers'] = []; }

        AuditLogger::logRead('reports', null, ['endpoint' => 'vendas.relatorios.filters']);
        return Response::json($out);
    }

    public function advanced(Request $r): Response {
        $pdo = ConnectionManager::forTenant();
        $start = (string)($r->query['start'] ?? '');
        $end = (string)($r->query['end'] ?? '');
        $cStart = (string)($r->query['compare_start'] ?? '');
        $cEnd = (string)($r->query['compare_end'] ?? '');
        $product = trim((string)($r->query['product'] ?? ''));
        $region = trim((string)($r->query['region'] ?? ''));
        $seller = trim((string)($r->query['seller'] ?? ''));
        $groupBy = (string)($r->query['group_by'] ?? 'day');

        $fnAgg = function($s,$e) use ($pdo,$product,$region,$seller,$groupBy){
            $items = [];
            // Pedidos
            try {
                $sql = "SELECT DATE(so.created_at) AS dia,
                               SUM(COALESCE(soi.total, so.valor_total)) AS total
                        FROM sales_orders so
                        LEFT JOIN sales_order_items soi ON soi.order_id = so.id
                        LEFT JOIN clients c ON c.id = so.cliente_id
                        LEFT JOIN audit_logs al ON al.resource = 'sales_orders' AND al.action = 'create' AND al.resource_id = so.id
                        WHERE DATE(so.created_at) BETWEEN ? AND ?";
                $params = [$s,$e];
                if ($product !== '') { $sql .= " AND soi.product_id = ?"; $params[] = $product; }
                if ($region !== '') { $sql .= " AND c.cidade = ?"; $params[] = $region; }
                if ($seller !== '') { $sql .= " AND al.user_id = ?"; $params[] = $seller; }
                $sql .= " GROUP BY DATE(so.created_at) ORDER BY DATE(so.created_at) ASC";
                $stmt = $pdo->prepare($sql); $stmt->execute($params);
                foreach ($stmt->fetchAll() as $row){ $items[] = ['date' => (string)$row['dia'], 'amount_cents' => (int)round(((float)$row['total'] ?? 0)*100)]; }
            } catch (\Throwable $e) {}

            // PDV
            try {
                $sql = "SELECT DATE(ps.created_at) AS dia,
                               SUM(COALESCE(psi.total, ps.valor_total)) AS total
                        FROM pos_sales ps
                        LEFT JOIN pos_sale_items psi ON psi.sale_id = ps.id
                        LEFT JOIN clients c ON c.id = ps.cliente_id
                        LEFT JOIN audit_logs al ON al.resource = 'pos_sales' AND al.action = 'create' AND al.resource_id = ps.id
                        WHERE DATE(ps.created_at) BETWEEN ? AND ?";
                $params = [$s,$e];
                if ($product !== '') { $sql .= " AND psi.product_id = ?"; $params[] = $product; }
                if ($region !== '') { $sql .= " AND c.cidade = ?"; $params[] = $region; }
                if ($seller !== '') { $sql .= " AND al.user_id = ?"; $params[] = $seller; }
                $sql .= " GROUP BY DATE(ps.created_at) ORDER BY DATE(ps.created_at) ASC";
                $stmt = $pdo->prepare($sql); $stmt->execute($params);
                foreach ($stmt->fetchAll() as $row){ $items[] = ['date' => (string)$row['dia'], 'amount_cents' => (int)round(((float)$row['total'] ?? 0)*100)]; }
            } catch (\Throwable $e) {}

            // Consolidar por dia
            $map = [];
            foreach ($items as $it){ $map[$it['date']] = ($map[$it['date']] ?? 0) + ($it['amount_cents'] ?? 0); }
            $out = [];
            ksort($map);
            foreach ($map as $d => $v){ $out[] = ['date' => $d, 'amount_cents' => $v]; }
            return $out;
        };

        // Defaults se não vierem datas
        $today = date('Y-m-d');
        if ($start === '' || $end === '') { $end = $today; $start = date('Y-m-d', strtotime('-30 days')); }
        if ($cStart === '' || $cEnd === '') {
            $aStart = new \DateTime($start); $aEnd = new \DateTime($end);
            $delta = max(1, (int)$aEnd->diff($aStart)->format('%a'));
            $bEnd = (clone $aStart)->modify('-1 day');
            $bStart = (clone $bEnd)->modify("-$delta days");
            $cStart = $bStart->format('Y-m-d'); $cEnd = $bEnd->format('Y-m-d');
        }

        $current = $fnAgg($start, $end);
        $previous = $fnAgg($cStart, $cEnd);
        AuditLogger::logRead('reports', null, ['endpoint' => 'vendas.relatorios.advanced', 'start' => $start, 'end' => $end, 'compare_start' => $cStart, 'compare_end' => $cEnd]);
        return Response::json(['current' => ['items' => $current], 'previous' => ['items' => $previous]]);
    }
    public function bySeller(Request $r): Response {
        $pdo = ConnectionManager::forTenant();
        $start = (string)($r->query['start'] ?? '');
        $end = (string)($r->query['end'] ?? '');
        if ($start === '' || $end === '') {
            // padrão: últimos 30 dias
            $end = date('Y-m-d');
            $start = date('Y-m-d', strtotime('-30 days'));
        }

        $by = [];
        // Pedidos (sales_orders)
        try {
            $stmt = $pdo->prepare(
                "SELECT al.user_id AS uid, u.name AS seller_name, COUNT(*) AS orders, COALESCE(SUM(so.valor_total),0) AS revenue
                 FROM sales_orders so
                 LEFT JOIN audit_logs al ON al.resource = 'sales_orders' AND al.action = 'create' AND al.resource_id = so.id
                 LEFT JOIN users u ON u.id = al.user_id
                 WHERE DATE(so.created_at) BETWEEN ? AND ?
                 GROUP BY al.user_id, u.name"
            );
            $stmt->execute([$start, $end]);
            foreach ($stmt->fetchAll() as $row) {
                $uid = (string)($row['uid'] ?? '');
                if (!isset($by[$uid])) $by[$uid] = ['seller_name' => null, 'orders' => 0, 'revenue_cents' => 0];
                $by[$uid]['seller_name'] = $by[$uid]['seller_name'] ?? ($row['seller_name'] ?? null);
                $by[$uid]['orders'] += (int)($row['orders'] ?? 0);
                // receita em centavos
                $rev = (float)($row['revenue'] ?? 0);
                $by[$uid]['revenue_cents'] += (int)round($rev * 100);
            }
        } catch (\Throwable $e) {
            // tolerar ausência de tabela/users
        }

        // Vendas PDV (pos_sales)
        try {
            $stmt = $pdo->prepare(
                "SELECT al.user_id AS uid, u.name AS seller_name, COUNT(*) AS orders, COALESCE(SUM(ps.valor_total),0) AS revenue
                 FROM pos_sales ps
                 LEFT JOIN audit_logs al ON al.resource = 'pos_sales' AND al.action = 'create' AND al.resource_id = ps.id
                 LEFT JOIN users u ON u.id = al.user_id
                 WHERE DATE(ps.created_at) BETWEEN ? AND ?
                 GROUP BY al.user_id, u.name"
            );
            $stmt->execute([$start, $end]);
            foreach ($stmt->fetchAll() as $row) {
                $uid = (string)($row['uid'] ?? '');
                if (!isset($by[$uid])) $by[$uid] = ['seller_name' => null, 'orders' => 0, 'revenue_cents' => 0];
                $by[$uid]['seller_name'] = $by[$uid]['seller_name'] ?? ($row['seller_name'] ?? null);
                $by[$uid]['orders'] += (int)($row['orders'] ?? 0);
                $rev = (float)($row['revenue'] ?? 0);
                $by[$uid]['revenue_cents'] += (int)round($rev * 100);
            }
        } catch (\Throwable $e) {
            // tolerar ausência
        }

        // Montar saída
        $out = [];
        foreach ($by as $uid => $acc) {
            $orders = (int)$acc['orders'];
            $revCents = (int)$acc['revenue_cents'];
            $avg = $orders > 0 ? (int)floor($revCents / $orders) : 0;
            $name = $acc['seller_name'] ?? null;
            if (!$name) {
                $name = ($uid !== '' ? ('Usuário #' . $uid) : '(não informado)');
            }
            $out[] = [
                'seller_name' => $name,
                'orders' => $orders,
                'revenue_cents' => $revCents,
                'avg_ticket_cents' => $avg,
            ];
        }

        // Ordenar por faturamento desc
        usort($out, function($a,$b){ return ($b['revenue_cents'] <=> $a['revenue_cents']); });

        AuditLogger::logRead('reports', null, ['endpoint' => 'vendas.relatorios.by_seller', 'start' => $start, 'end' => $end]);
        return Response::json(['data' => $out]);
    }

    // Top Produtos por faturamento (consolida Pedidos e PDV)
    public function topProducts(Request $r): Response {
        $pdo = ConnectionManager::forTenant();
        $start = (string)($r->query['start'] ?? '');
        $end = (string)($r->query['end'] ?? '');
        $region = trim((string)($r->query['region'] ?? ''));
        $seller = trim((string)($r->query['seller'] ?? ''));

        if ($start === '' || $end === '') {
            $end = date('Y-m-d');
            $start = date('Y-m-d', strtotime('-30 days'));
        }

        $agg = [];
        // Pedidos (por item)
        try {
            $sql = "SELECT soi.product_id AS pid, SUM(COALESCE(soi.total, 0)) AS revenue, COUNT(*) AS items
                    FROM sales_orders so
                    LEFT JOIN sales_order_items soi ON soi.order_id = so.id
                    LEFT JOIN clients c ON c.id = so.cliente_id
                    LEFT JOIN audit_logs al ON al.resource = 'sales_orders' AND al.action = 'create' AND al.resource_id = so.id
                    WHERE DATE(so.created_at) BETWEEN ? AND ?";
            $params = [$start, $end];
            if ($region !== '') { $sql .= " AND c.cidade = ?"; $params[] = $region; }
            if ($seller !== '') { $sql .= " AND al.user_id = ?"; $params[] = $seller; }
            $sql .= " GROUP BY soi.product_id";
            $stmt = $pdo->prepare($sql); $stmt->execute($params);
            foreach ($stmt->fetchAll() as $row) {
                $pid = (string)($row['pid'] ?? ''); if ($pid === '') { continue; }
                if (!isset($agg[$pid])) { $agg[$pid] = ['revenue_cents' => 0, 'items' => 0]; }
                $rev = (float)($row['revenue'] ?? 0);
                $agg[$pid]['revenue_cents'] += (int)round($rev * 100);
                $agg[$pid]['items'] += (int)($row['items'] ?? 0);
            }
        } catch (\Throwable $e) {}

        // PDV (por item)
        try {
            $sql = "SELECT psi.product_id AS pid, SUM(COALESCE(psi.total, 0)) AS revenue, COUNT(*) AS items
                    FROM pos_sales ps
                    LEFT JOIN pos_sale_items psi ON psi.sale_id = ps.id
                    LEFT JOIN clients c ON c.id = ps.cliente_id
                    LEFT JOIN audit_logs al ON al.resource = 'pos_sales' AND al.action = 'create' AND al.resource_id = ps.id
                    WHERE DATE(ps.created_at) BETWEEN ? AND ?";
            $params = [$start, $end];
            if ($region !== '') { $sql .= " AND c.cidade = ?"; $params[] = $region; }
            if ($seller !== '') { $sql .= " AND al.user_id = ?"; $params[] = $seller; }
            $sql .= " GROUP BY psi.product_id";
            $stmt = $pdo->prepare($sql); $stmt->execute($params);
            foreach ($stmt->fetchAll() as $row) {
                $pid = (string)($row['pid'] ?? ''); if ($pid === '') { continue; }
                if (!isset($agg[$pid])) { $agg[$pid] = ['revenue_cents' => 0, 'items' => 0]; }
                $rev = (float)($row['revenue'] ?? 0);
                $agg[$pid]['revenue_cents'] += (int)round($rev * 100);
                $agg[$pid]['items'] += (int)($row['items'] ?? 0);
            }
        } catch (\Throwable $e) {}

        // Buscar nomes dos produtos para os IDs agregados
        $out = [];
        $pids = array_keys($agg);
        $names = [];
        if (count($pids) > 0) {
            try {
                $in = implode(',', array_fill(0, count($pids), '?'));
                $sql = "SELECT p.id, COALESCE(JSON_UNQUOTE(JSON_EXTRACT(pm.meta_json, '$.nome')), p.codigo) AS name
                        FROM products p
                        LEFT JOIN product_meta pm ON pm.product_id = p.id
                        WHERE p.id IN ($in)";
                $stmt = $pdo->prepare($sql); $stmt->execute(array_map('intval', $pids));
                foreach ($stmt->fetchAll() as $row) { $names[(string)$row['id']] = (string)($row['name'] ?? ('Produto #' . $row['id'])); }
            } catch (\Throwable $e) {
                foreach ($pids as $pid) { $names[(string)$pid] = 'Produto #' . $pid; }
            }
        }

        foreach ($agg as $pid => $acc) {
            $out[] = [
                'product_id' => (int)$pid,
                'product_name' => (string)($names[(string)$pid] ?? ('Produto #' . $pid)),
                'revenue_cents' => (int)$acc['revenue_cents'],
                'items_count' => (int)$acc['items'],
            ];
        }
        usort($out, function($a,$b){ return ($b['revenue_cents'] <=> $a['revenue_cents']); });
        // limitar top 20
        $out = array_slice($out, 0, 20);

        AuditLogger::logRead('reports', null, ['endpoint' => 'vendas.relatorios.top_products', 'start' => $start, 'end' => $end]);
        return Response::json(['data' => $out]);
    }

    // Top Regiões por faturamento (consolida Pedidos e PDV)
    public function topRegions(Request $r): Response {
        $pdo = ConnectionManager::forTenant();
        $start = (string)($r->query['start'] ?? '');
        $end = (string)($r->query['end'] ?? '');
        $product = trim((string)($r->query['product'] ?? ''));
        $seller = trim((string)($r->query['seller'] ?? ''));

        if ($start === '' || $end === '') {
            $end = date('Y-m-d');
            $start = date('Y-m-d', strtotime('-30 days'));
        }

        $agg = [];
        $inc = function($regionKey, $revenue){
            $k = ($regionKey !== null && $regionKey !== '') ? (string)$regionKey : '(não informado)';
            if (!isset($agg[$k])) { $agg[$k] = ['revenue_cents' => 0, 'orders' => 0]; }
            $agg[$k]['revenue_cents'] += (int)round(((float)$revenue) * 100);
            $agg[$k]['orders'] += 1;
        };

        // Pedidos (valor total por região)
        try {
            $sql = "SELECT c.cidade AS region, so.id AS oid, COALESCE(so.valor_total, 0) AS revenue
                    FROM sales_orders so
                    LEFT JOIN clients c ON c.id = so.cliente_id
                    LEFT JOIN audit_logs al ON al.resource = 'sales_orders' AND al.action = 'create' AND al.resource_id = so.id
                    LEFT JOIN sales_order_items soi ON soi.order_id = so.id
                    WHERE DATE(so.created_at) BETWEEN ? AND ?";
            $params = [$start, $end];
            if ($product !== '') { $sql .= " AND soi.product_id = ?"; $params[] = $product; }
            if ($seller !== '') { $sql .= " AND al.user_id = ?"; $params[] = $seller; }
            $sql .= " GROUP BY so.id";
            $stmt = $pdo->prepare($sql); $stmt->execute($params);
            foreach ($stmt->fetchAll() as $row) { $inc(($row['region'] ?? null), ($row['revenue'] ?? 0)); }
        } catch (\Throwable $e) {}

        // PDV (valor total por região)
        try {
            $sql = "SELECT c.cidade AS region, ps.id AS sid, COALESCE(ps.valor_total, 0) AS revenue
                    FROM pos_sales ps
                    LEFT JOIN clients c ON c.id = ps.cliente_id
                    LEFT JOIN audit_logs al ON al.resource = 'pos_sales' AND al.action = 'create' AND al.resource_id = ps.id
                    LEFT JOIN pos_sale_items psi ON psi.sale_id = ps.id
                    WHERE DATE(ps.created_at) BETWEEN ? AND ?";
            $params = [$start, $end];
            if ($product !== '') { $sql .= " AND psi.product_id = ?"; $params[] = $product; }
            if ($seller !== '') { $sql .= " AND al.user_id = ?"; $params[] = $seller; }
            $sql .= " GROUP BY ps.id";
            $stmt = $pdo->prepare($sql); $stmt->execute($params);
            foreach ($stmt->fetchAll() as $row) { $inc(($row['region'] ?? null), ($row['revenue'] ?? 0)); }
        } catch (\Throwable $e) {}

        $out = [];
        foreach ($agg as $region => $acc) {
            $out[] = [
                'region' => (string)$region,
                'orders_count' => (int)$acc['orders'],
                'revenue_cents' => (int)$acc['revenue_cents'],
            ];
        }
        usort($out, function($a,$b){ return ($b['revenue_cents'] <=> $a['revenue_cents']); });
        $out = array_slice($out, 0, 20);

        AuditLogger::logRead('reports', null, ['endpoint' => 'vendas.relatorios.top_regions', 'start' => $start, 'end' => $end]);
        return Response::json(['data' => $out]);
    }

    // Curva ABC de Vendas (consolida Pedidos e PDV)
    public function curvaAbc(Request $r): Response {
        $pdo = ConnectionManager::forTenant();
        $start = (string)($r->query['start'] ?? '');
        $end = (string)($r->query['end'] ?? '');
        $product = trim((string)($r->query['product'] ?? ''));
        $region = trim((string)($r->query['region'] ?? ''));
        $seller = trim((string)($r->query['seller'] ?? ''));
        $metric = strtolower((string)($r->query['metric'] ?? 'valor'));
        $limit = (int)($r->query['limit'] ?? 50);
        $aPct = (float)($r->query['a_pct'] ?? 0.8);
        $bPct = (float)($r->query['b_pct'] ?? 0.15);

        if ($start === '' || $end === '') {
            $end = date('Y-m-d');
            $start = date('Y-m-d', strtotime('-30 days'));
        }

        $agg = [];
        $inc = function($pid, $qty, $revenueCents) use (&$agg) {
            $k = (string)($pid ?? ''); if ($k === '') return;
            if (!isset($agg[$k])) { $agg[$k] = ['qty' => 0.0, 'revenue_cents' => 0]; }
            $agg[$k]['qty'] += (float)$qty;
            $agg[$k]['revenue_cents'] += (int)round(((float)$revenueCents));
        };

        // Pedidos de venda (por item)
        try {
            $sql = "SELECT soi.product_id AS pid,
                           SUM(COALESCE(soi.quantidade, 0)) AS qty,
                           SUM(COALESCE(soi.total, 0)) AS revenue
                    FROM sales_orders so
                    LEFT JOIN sales_order_items soi ON soi.order_id = so.id
                    LEFT JOIN clients c ON c.id = so.cliente_id
                    LEFT JOIN audit_logs al ON al.resource = 'sales_orders' AND al.action = 'create' AND al.resource_id = so.id
                    WHERE DATE(so.created_at) BETWEEN ? AND ?";
            $params = [$start, $end];
            if ($product !== '') { $sql .= " AND soi.product_id = ?"; $params[] = $product; }
            if ($region !== '') { $sql .= " AND c.cidade = ?"; $params[] = $region; }
            if ($seller !== '') { $sql .= " AND al.user_id = ?"; $params[] = $seller; }
            $sql .= " GROUP BY soi.product_id";
            $stmt = $pdo->prepare($sql); $stmt->execute($params);
            foreach (($stmt->fetchAll() ?: []) as $row) {
                $pid = $row['pid'] ?? null; $qty = (float)($row['qty'] ?? 0); $rev = (float)($row['revenue'] ?? 0);
                $inc($pid, $qty, $rev * 100);
            }
        } catch (\Throwable $e) {}

        // PDV (por item)
        try {
            $sql = "SELECT psi.product_id AS pid,
                           SUM(COALESCE(psi.quantidade, 0)) AS qty,
                           SUM(COALESCE(psi.total, 0)) AS revenue
                    FROM pos_sales ps
                    LEFT JOIN pos_sale_items psi ON psi.sale_id = ps.id
                    LEFT JOIN clients c ON c.id = ps.cliente_id
                    LEFT JOIN audit_logs al ON al.resource = 'pos_sales' AND al.action = 'create' AND al.resource_id = ps.id
                    WHERE DATE(ps.created_at) BETWEEN ? AND ?";
            $params = [$start, $end];
            if ($product !== '') { $sql .= " AND psi.product_id = ?"; $params[] = $product; }
            if ($region !== '') { $sql .= " AND c.cidade = ?"; $params[] = $region; }
            if ($seller !== '') { $sql .= " AND al.user_id = ?"; $params[] = $seller; }
            $sql .= " GROUP BY psi.product_id";
            $stmt = $pdo->prepare($sql); $stmt->execute($params);
            foreach (($stmt->fetchAll() ?: []) as $row) {
                $pid = $row['pid'] ?? null; $qty = (float)($row['qty'] ?? 0); $rev = (float)($row['revenue'] ?? 0);
                $inc($pid, $qty, $rev * 100);
            }
        } catch (\Throwable $e) {}

        // Construir lista e ordenar por métrica
        $rows = [];
        foreach ($agg as $pid => $acc) {
            $rows[] = [
                'product_id' => (int)$pid,
                'quantidade_total' => (float)($acc['qty'] ?? 0),
                'valor_total' => (float)((int)($acc['revenue_cents'] ?? 0) / 100.0),
            ];
        }
        usort($rows, function($a,$b) use ($metric){
            if ($metric === 'quantidade') { return ($b['quantidade_total'] <=> $a['quantidade_total']); }
            return ($b['valor_total'] <=> $a['valor_total']);
        });
        if ($limit > 0) { $rows = array_slice($rows, 0, $limit); }

        // Totais e percentuais
        $totalMetric = 0.0;
        if ($metric === 'quantidade') {
            foreach ($rows as $r) { $totalMetric += (float)($r['quantidade_total'] ?? 0); }
        } else {
            foreach ($rows as $r) { $totalMetric += (float)($r['valor_total'] ?? 0); }
        }
        $totalMetric = max(0.000001, $totalMetric);

        // Buscar nome/código de produtos para os IDs presentes
        $idList = array_values(array_unique(array_map(function($r){ return (int)($r['product_id'] ?? 0); }, $rows)));
        $info = [];
        if (count($idList) > 0) {
            try {
                $placeholders = implode(',', array_fill(0, count($idList), '?'));
                $sql = "SELECT p.id,
                               p.codigo AS code,
                               COALESCE(JSON_UNQUOTE(JSON_EXTRACT(pm.meta_json, '$.nome')), p.codigo) AS name
                        FROM products p
                        LEFT JOIN product_meta pm ON pm.product_id = p.id
                        WHERE p.id IN ($placeholders)";
                $stmt = $pdo->prepare($sql); $stmt->execute($idList);
                foreach ($stmt->fetchAll() as $row) {
                    $info[(int)$row['id']] = ['code' => ($row['code'] ?? null), 'name' => ($row['name'] ?? null)];
                }
            } catch (\Throwable $e) {
                // Fallback mínimo
                try {
                    $placeholders = implode(',', array_fill(0, count($idList), '?'));
                    $stmt = $pdo->prepare("SELECT id, codigo AS code FROM products WHERE id IN ($placeholders)");
                    $stmt->execute($idList);
                    foreach ($stmt->fetchAll() as $row) { $info[(int)$row['id']] = ['code' => ($row['code'] ?? null), 'name' => ($row['code'] ?? null)]; }
                } catch (\Throwable $e2) {}
            }
        }

        $out = [];
        $cumulative = 0.0;
        foreach ($rows as $r) {
            $share = ($metric === 'quantidade')
                ? ( ((float)($r['quantidade_total'] ?? 0)) / $totalMetric )
                : ( ((float)($r['valor_total'] ?? 0)) / $totalMetric );
            $cumulative += $share;
            $cat = ($cumulative <= $aPct) ? 'A' : (($cumulative <= ($aPct + $bPct)) ? 'B' : 'C');
            $pid = (int)($r['product_id'] ?? 0);
            $meta = $info[$pid] ?? ['code' => null, 'name' => null];
            $out[] = [
                'product_id' => $pid,
                'product_code' => $meta['code'] ?? null,
                'product_name' => $meta['name'] ?? null,
                'quantidade_total' => (float)($r['quantidade_total'] ?? 0),
                'valor_total' => (float)($r['valor_total'] ?? 0),
                'share_pct' => round($share * 100, 4),
                'cumulative_pct' => round($cumulative * 100, 4),
                'categoria' => $cat
            ];
        }

        AuditLogger::logRead('reports', null, ['endpoint' => 'vendas.relatorios.abc', 'start' => $start, 'end' => $end, 'metric' => $metric]);
        return Response::json(['period' => ['start' => $start, 'end' => $end], 'items' => $out]);
    }
}