<?php
final class ReportService {
    public const ORDER_COLUMNS = [
        'pedido' => 'Pedido', 'data' => 'Data', 'status' => 'Status', 'cliente' => 'Cliente',
        'email' => 'E-mail', 'telefone' => 'Telefone', 'cidade_uf' => 'Cidade/UF',
        'produto' => 'Produto', 'entrega' => 'Entrega', 'pagamento' => 'Pagamento',
        'subtotal_itens' => 'Subtotal itens', 'frete' => 'Frete', 'total' => 'Total',
        'tipo_certidao' => 'Tipo certidão', 'categoria' => 'Categoria', 'modalidade' => 'Modalidade', 'gratuidade' => 'Gratuidade',
        'livro' => 'Livro', 'folha' => 'Folha', 'termo' => 'Termo', 'registrado' => 'Registrado',
        'solicitante' => 'Solicitante', 'anexos' => 'Anexos', 'apostila' => 'Apostila', 'pendencias' => 'Pendências'
    ];

    public function dashboard(array $filters=[]): array {
        $pdo = Database::pdo();
        [$where,$params] = $this->where($filters, 'o');
        $kpi = $pdo->prepare("SELECT COUNT(*) orders_count, COALESCE(SUM(o.order_total),0) total, COALESCE(SUM(o.shipping_total),0) shipping, COALESCE(AVG(o.order_total),0) avg_ticket FROM orders o $where");
        $kpi->execute($params);
        return [
            'kpi'=>$kpi->fetch(),
            'by_status'=>$this->group('status',$where,$params),
            'by_product'=>$this->products($where,$params),
            'by_shipping'=>$this->group('shipping_method',$where,$params),
            'daily'=>$this->daily($where,$params)
        ];
    }

    public function orders(array $filters=[]): array {
        $pdo = Database::pdo(); [$where,$params] = $this->where($filters, 'o');
        $limit = min(200, max(10, (int)($filters['limit'] ?? 50)));
        $page = max(1, (int)($filters['page'] ?? 1)); $offset = ($page-1)*$limit;
        $count = $pdo->prepare("SELECT COUNT(*) FROM orders o LEFT JOIN order_intelligence oi ON oi.order_id=o.id $where");
        $count->execute($params);
        $sql = "SELECT o.id, o.woo_order_id, o.order_number_formatted, o.order_date, o.status, o.billing_name, o.billing_email, o.billing_phone, o.billing_city, o.billing_state, o.shipping_method, o.payment_method, o.order_total, o.shipping_total, oi.certificate_category, oi.pending_count FROM orders o LEFT JOIN order_intelligence oi ON oi.order_id=o.id $where ORDER BY o.order_date DESC LIMIT $limit OFFSET $offset";
        $st=$pdo->prepare($sql); $st->execute($params);
        return ['rows'=>$st->fetchAll(), 'page'=>$page, 'limit'=>$limit, 'total'=>(int)$count->fetchColumn()];
    }

    public function reportRows(array $filters=[]): array {
        $pdo = Database::pdo(); [$where,$params] = $this->where($filters, 'o');
        $limit = min(10000, max(10, (int)($filters['limit'] ?? 2000)));
        $page = max(1, (int)($filters['page'] ?? 1)); $offset = ($page-1)*$limit;
        $sql = "SELECT o.id, o.woo_order_id, o.order_number_formatted, o.order_date, o.status, o.billing_name, o.billing_email, o.billing_phone, o.billing_city, o.billing_state, o.shipping_method, o.payment_method, o.shipping_total, o.order_total, oi.certificate_type, oi.certificate_category, oi.modality, oi.free_request, oi.has_apostille, oi.has_attachment, oi.book, oi.page, oi.term, oi.registered_name, oi.requester_name, oi.pending_flags, GROUP_CONCAT(DISTINCT it.name ORDER BY it.name SEPARATOR ' | ') produto, COALESCE(SUM(it.total),0) subtotal_itens FROM orders o LEFT JOIN order_intelligence oi ON oi.order_id=o.id LEFT JOIN order_items it ON it.order_id=o.id $where GROUP BY o.id ORDER BY o.order_date DESC LIMIT $limit OFFSET $offset";
        $st=$pdo->prepare($sql); $st->execute($params); $rows=$st->fetchAll();
        foreach ($rows as &$row) {
            $row['pedido'] = $row['order_number_formatted'] ?: $row['woo_order_id'];
            $row['data'] = $row['order_date'];
            $row['cliente'] = $row['billing_name'];
            $row['email'] = $row['billing_email'];
            $row['telefone'] = $row['billing_phone'];
            $row['cidade_uf'] = trim(($row['billing_city'] ?? '') . '/' . ($row['billing_state'] ?? ''), '/');
            $row['entrega'] = $row['shipping_method'];
            $row['pagamento'] = $row['payment_method'];
            $row['frete'] = $row['shipping_total'];
            $row['total'] = $row['order_total'];
            $row['tipo_certidao'] = $row['certificate_type'] ?? '';
            $row['categoria'] = $row['certificate_category'] ?? '';
            $row['modalidade'] = $row['modality'] ?? '';
            $row['gratuidade'] = !empty($row['free_request']) ? 'Sim' : 'Não';
            $row['livro'] = $row['book'] ?? '';
            $row['folha'] = $row['page'] ?? '';
            $row['termo'] = $row['term'] ?? '';
            $row['registrado'] = $row['registered_name'] ?? '';
            $row['solicitante'] = $row['requester_name'] ?? '';
            $row['anexos'] = !empty($row['has_attachment']) ? 'Sim' : 'Não';
            $row['apostila'] = !empty($row['has_apostille']) ? 'Sim' : 'Não';
            $row['pendencias'] = $row['pending_flags'] ?? '';
        }
        return $rows;
    }

    public function reportTotals(array $filters=[]): array {
        $pdo=Database::pdo(); [$where,$params]=$this->where($filters, 'o');
        $sql="SELECT COUNT(*) pedidos, COALESCE(SUM(o.shipping_total),0) frete, COALESCE(SUM(o.order_total),0) total_geral, COALESCE(SUM(o.order_total - o.shipping_total),0) entrega FROM orders o LEFT JOIN order_intelligence oi ON oi.order_id=o.id $where";
        $st=$pdo->prepare($sql); $st->execute($params); $row=$st->fetch() ?: [];
        return ['pedidos'=>(int)($row['pedidos']??0), 'entrega'=>(float)($row['entrega']??0), 'frete'=>(float)($row['frete']??0), 'total_geral'=>(float)($row['total_geral']??0)];
    }

    public function operationalRows(array $filters=[]): array {
        $filters['smart'] = 'pendencias';
        $filters['limit'] = $filters['limit'] ?? 100;
        return $this->reportRows($filters);
    }

    public function orderDetail(int $id): array {
        $pdo=Database::pdo(); $st=$pdo->prepare('SELECT o.*, oi.certificate_type, oi.certificate_category, oi.modality, oi.has_apostille, oi.has_attachment, oi.delivery_type, oi.pending_flags, oi.processed_at FROM orders o LEFT JOIN order_intelligence oi ON oi.order_id=o.id WHERE o.id=?'); $st->execute([$id]); $order=$st->fetch();
        if (!$order) throw new RuntimeException('Pedido não encontrado.');
        $items=$pdo->prepare('SELECT * FROM order_items WHERE order_id=?'); $items->execute([$id]); $items=$items->fetchAll();
        foreach ($items as &$item) { $m=$pdo->prepare('SELECT meta_key, meta_value FROM order_item_meta WHERE order_item_id=? ORDER BY id'); $m->execute([$item['id']]); $item['meta']=$m->fetchAll(); }
        $notes=$pdo->prepare('SELECT * FROM order_notes WHERE order_id=? ORDER BY note_date'); $notes->execute([$id]);
        $ship=$pdo->prepare('SELECT * FROM shipping_items WHERE order_id=?'); $ship->execute([$id]);
        return ['order'=>$order,'items'=>$items,'notes'=>$notes->fetchAll(),'shipping_items'=>$ship->fetchAll()];
    }

    public function options(): array {
        $pdo=Database::pdo();
        return [
            'statuses'=>$pdo->query("SELECT DISTINCT status value FROM orders WHERE status IS NOT NULL AND status<>'' ORDER BY status")->fetchAll(),
            'products'=>$pdo->query("SELECT DISTINCT name value FROM order_items WHERE name IS NOT NULL AND name<>'' ORDER BY name LIMIT 250")->fetchAll(),
            'shipping'=>$pdo->query("SELECT DISTINCT shipping_method value FROM orders WHERE shipping_method IS NOT NULL AND shipping_method<>'' ORDER BY shipping_method LIMIT 250")->fetchAll(),
            'states'=>$pdo->query("SELECT DISTINCT billing_state value FROM orders WHERE billing_state IS NOT NULL AND billing_state<>'' ORDER BY billing_state LIMIT 100")->fetchAll(),
            'cities'=>$pdo->query("SELECT DISTINCT billing_city value FROM orders WHERE billing_city IS NOT NULL AND billing_city<>'' ORDER BY billing_city LIMIT 250")->fetchAll(),
            'cert_types'=>$pdo->query("SELECT DISTINCT certificate_type value FROM order_intelligence WHERE certificate_type IS NOT NULL AND certificate_type<>'' ORDER BY certificate_type LIMIT 120")->fetchAll(),
            'modes'=>$pdo->query("SELECT DISTINCT modality value FROM order_intelligence WHERE modality IS NOT NULL AND modality<>'' ORDER BY modality LIMIT 120")->fetchAll(),
            'meta_keys'=>$pdo->query("SELECT DISTINCT meta_key value FROM order_item_meta WHERE meta_key IS NOT NULL AND meta_key<>'' ORDER BY meta_key LIMIT 300")->fetchAll(),
            'columns'=>self::ORDER_COLUMNS,
        ];
    }

    public function smartCards(array $filters=[]): array {
        $svc = new OrderIntelligenceService();
        $cacheKey = 'smart_cards_'.sha1(json_encode($this->cacheableFilters($filters), JSON_UNESCAPED_UNICODE));
        if ($cached = $svc->cacheGet($cacheKey)) return $cached;
        $cards = [
            'nascimento' => ['label'=>'Nascimento','hint'=>'Certidões e buscas de nascimento'],
            'casamento' => ['label'=>'Casamento','hint'=>'Certidões e habilitações de casamento'],
            'obito' => ['label'=>'Óbito','hint'=>'Certidões de óbito'],
            'apostila' => ['label'=>'Apostila','hint'=>'Pedidos com apostilamento/Apostila de Haia'],
            'retirada' => ['label'=>'Retirada','hint'=>'Retirar no cartório'],
            'sedex' => ['label'=>'Sedex','hint'=>'Envio por Correios/Sedex'],
            'com_anexos' => ['label'=>'Com anexos','hint'=>'Pedidos com documentos anexados'],
            'pendencias' => ['label'=>'Pendências','hint'=>'Pedidos para conferência operacional'],
        ];
        $out=[];
        foreach ($cards as $key=>$info) {
            $f=$filters; $f['smart']=$key; [$where,$params]=$this->where($f, 'o');
            $st=Database::pdo()->prepare("SELECT COUNT(*) qty, COALESCE(SUM(o.order_total),0) total FROM orders o LEFT JOIN order_intelligence oi ON oi.order_id=o.id $where");
            $st->execute($params); $row=$st->fetch() ?: ['qty'=>0,'total'=>0];
            $out[]=['key'=>$key,'label'=>$info['label'],'hint'=>$info['hint'],'qty'=>(int)$row['qty'],'total'=>(float)$row['total']];
        }
        $svc->cacheSet($cacheKey, $out, 300);
        return $out;
    }

    private function where(array $f, string $alias='o'): array {
        $prefix = $alias ? $alias.'.' : ''; $w=[];$p=[];
        if (!empty($f['from'])) {$w[]=$prefix.'order_date >= ?';$p[]=$f['from'].' 00:00:00';}
        if (!empty($f['to'])) {$w[]=$prefix.'order_date <= ?';$p[]=$f['to'].' 23:59:59';}
        if (!empty($f['status'])) {$w[]=$prefix.'status = ?';$p[]=$f['status'];}
        if (!empty($f['shipping'])) {$w[]=$prefix.'shipping_method LIKE ?';$p[]='%'.$f['shipping'].'%';}
        if (!empty($f['state'])) {$w[]=$prefix.'billing_state = ?';$p[]=$f['state'];}
        if (!empty($f['city'])) {$w[]=$prefix.'billing_city LIKE ?';$p[]='%'.$f['city'].'%';}
        if (!empty($f['q'])) {$w[]='('.$prefix.'billing_name LIKE ? OR '.$prefix.'billing_email LIKE ? OR '.$prefix.'billing_phone LIKE ? OR '.$prefix.'order_number_formatted LIKE ? OR '.$prefix.'woo_order_id LIKE ?)'; for($i=0;$i<5;$i++)$p[]='%'.$f['q'].'%';}
        if (!empty($f['product'])) {$w[]='EXISTS (SELECT 1 FROM order_items oi2 WHERE oi2.order_id='.$prefix.'id AND oi2.name LIKE ?)';$p[]='%'.$f['product'].'%';}
        if (!empty($f['cert_type'])) {$w[]='oi.certificate_type LIKE ?';$p[]='%'.$f['cert_type'].'%';}
        if (!empty($f['mode'])) {$w[]='oi.modality LIKE ?';$p[]='%'.$f['mode'].'%';}
        if (isset($f['has_attachment']) && $f['has_attachment'] !== '') {$w[]='oi.has_attachment = ?'; $p[]=(int)$f['has_attachment'];}
        if (!empty($f['smart'])) { [$sw,$sp] = $this->smartWhere((string)$f['smart']); if ($sw) {$w[]=$sw; $p=array_merge($p,$sp);} }
        if (!empty($f['meta_key']) && !empty($f['meta_value'])) {$w[]='EXISTS (SELECT 1 FROM order_items oi6 JOIN order_item_meta im6 ON im6.order_item_id=oi6.id WHERE oi6.order_id='.$prefix.'id AND im6.normalized_key=? AND im6.meta_value LIKE ?)';$p[]=normalize_key($f['meta_key']);$p[]='%'.$f['meta_value'].'%';}
        return [$w ? 'WHERE '.implode(' AND ', $w) : '', $p];
    }

    private function smartWhere(string $key): array {
        return match($key) {
            'nascimento' => ['oi.certificate_category = ?', ['nascimento']],
            'casamento' => ['oi.certificate_category = ?', ['casamento']],
            'obito' => ['oi.certificate_category = ?', ['obito']],
            'apostila' => ['oi.has_apostille = 1', []],
            'retirada' => ['oi.delivery_type = ?', ['retirada']],
            'sedex' => ['oi.delivery_type = ?', ['sedex']],
            'com_anexos' => ['oi.has_attachment = 1', []],
            'sem_anexos' => ['oi.has_attachment = 0', []],
            'pendencias' => ['oi.pending_count > 0', []],
            default => ['', []]
        };
    }

    private function group(string $field, string $where, array $params): array {
        $allowed=['status','shipping_method','payment_method','billing_state']; if(!in_array($field,$allowed,true)) return [];
        $sql="SELECT COALESCE(NULLIF(o.$field,''),'Não informado') label, COUNT(*) qty, SUM(o.order_total) total FROM orders o LEFT JOIN order_intelligence oi ON oi.order_id=o.id $where GROUP BY label ORDER BY qty DESC LIMIT 20";
        $st=Database::pdo()->prepare($sql); $st->execute($params); return $st->fetchAll();
    }

    private function products(string $where, array $params): array {
        $sql="SELECT it.name label, SUM(it.quantity) qty, SUM(it.total) total FROM order_items it JOIN orders o ON o.id=it.order_id LEFT JOIN order_intelligence oi ON oi.order_id=o.id $where GROUP BY it.name ORDER BY qty DESC LIMIT 20";
        $st=Database::pdo()->prepare($sql); $st->execute($params); return $st->fetchAll();
    }

    private function daily(string $where, array $params): array {
        $sql="SELECT DATE(o.order_date) label, COUNT(*) qty, SUM(o.order_total) total FROM orders o LEFT JOIN order_intelligence oi ON oi.order_id=o.id $where GROUP BY DATE(o.order_date) ORDER BY label DESC LIMIT 60";
        $st=Database::pdo()->prepare($sql); $st->execute($params); return array_reverse($st->fetchAll());
    }

    private function cacheableFilters(array $filters): array {
        return array_intersect_key($filters, array_flip(['from','to','status','product','q','shipping','state','city','cert_type','mode','has_attachment']));
    }
}
