<?php
final class OrderIntelligenceService {
    public function analyzeOrder(int $orderId): void {
        $pdo = Database::pdo();
        $orderSt = $pdo->prepare('SELECT * FROM orders WHERE id=? LIMIT 1');
        $orderSt->execute([$orderId]);
        $order = $orderSt->fetch();
        if (!$order) return;

        $itemsSt = $pdo->prepare('SELECT id, name, total, meta_raw FROM order_items WHERE order_id=? ORDER BY id');
        $itemsSt->execute([$orderId]);
        $items = $itemsSt->fetchAll();

        $metaSt = $pdo->prepare('SELECT im.normalized_key, im.meta_key, im.meta_value FROM order_items oi JOIN order_item_meta im ON im.order_item_id=oi.id WHERE oi.order_id=? ORDER BY im.id');
        $metaSt->execute([$orderId]);
        $metaRows = $metaSt->fetchAll();

        $allText = clean_text(implode(' ', array_filter(array_merge(
            array_column($items, 'name'),
            array_column($metaRows, 'meta_key'),
            array_column($metaRows, 'meta_value'),
            [$order['shipping_method'] ?? '', $order['shipping_method_id'] ?? '']
        ))));
        $allNorm = normalize_key($allText);

        $certificateType = $this->firstMetaValue($metaRows, ['tipo_de_certidao','tipo_certidao','escolha_o_tipo','selecione_o_tipo']) ?: $this->inferTypeFromText($allText);
        $category = $this->category($certificateType . ' ' . $allText);
        $modality = $this->modality($allText, $items);
        $delivery = $this->deliveryType((string)($order['shipping_method'] ?? ''), (string)($order['shipping_method_id'] ?? ''));

        $book = $this->firstExact($metaRows, ['livro']);
        $page = $this->firstExact($metaRows, ['folha']);
        $term = $this->firstExact($metaRows, ['termo']);
        $registered = $this->firstContains($metaRows, ['registrado','nome_do_registrado','dados_do_registrado']);
        $requester = $this->firstContains($metaRows, ['solicitante','requerente','nome_solicitante','nome_do_requerente']);
        $free = $this->isFreeRequest($metaRows, $allNorm);
        $hasAttachment = $this->hasAttachment($metaRows);
        $hasApostille = str_contains($allNorm, 'apostila') || str_contains($allNorm, 'apostilamento') || str_contains($allNorm, 'apostil');

        $flags = [];
        if ((float)($order['order_total'] ?? 0) <= 0) $flags[] = 'valor zerado';
        if (trim((string)($order['billing_email'] ?? '')) === '') $flags[] = 'sem e-mail';
        if (trim((string)($order['billing_phone'] ?? '')) === '') $flags[] = 'sem telefone';
        if (trim((string)($order['shipping_method'] ?? '')) === '' && $delivery !== 'sem_entrega') $flags[] = 'sem entrega';
        if (trim((string)$certificateType) === '') $flags[] = 'sem tipo de certidão';
        $isSearch = str_contains($allNorm, 'busca') || str_contains($allNorm, 'pedido_de_busca') || str_contains($allNorm, 'solicitar_busca');
        if (!$isSearch) {
            if (trim((string)$book) === '') $flags[] = 'sem livro';
            if (trim((string)$page) === '') $flags[] = 'sem folha';
            if (trim((string)$term) === '') $flags[] = 'sem termo';
        }
        $flags = array_values(array_unique($flags));
        $hash = sha1($allText . '|' . (string)$order['updated_at']);

        $sql = 'INSERT INTO order_intelligence
            (order_id, certificate_type, certificate_category, modality, free_request, has_apostille, has_attachment, delivery_type, has_book, has_page, has_term, registered_name, requester_name, book, page, term, pending_flags, pending_count, source_hash, processed_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW())
            ON DUPLICATE KEY UPDATE
              certificate_type=VALUES(certificate_type), certificate_category=VALUES(certificate_category), modality=VALUES(modality), free_request=VALUES(free_request),
              has_apostille=VALUES(has_apostille), has_attachment=VALUES(has_attachment), delivery_type=VALUES(delivery_type), has_book=VALUES(has_book), has_page=VALUES(has_page), has_term=VALUES(has_term),
              registered_name=VALUES(registered_name), requester_name=VALUES(requester_name), book=VALUES(book), page=VALUES(page), term=VALUES(term), pending_flags=VALUES(pending_flags),
              pending_count=VALUES(pending_count), source_hash=VALUES(source_hash), processed_at=NOW()';
        $pdo->prepare($sql)->execute([
            $orderId, $certificateType, $category, $modality, $free ? 1 : 0, $hasApostille ? 1 : 0, $hasAttachment ? 1 : 0, $delivery,
            trim((string)$book) !== '' ? 1 : 0, trim((string)$page) !== '' ? 1 : 0, trim((string)$term) !== '' ? 1 : 0,
            $registered, $requester, $book, $page, $term, implode('; ', $flags), count($flags), $hash
        ]);
    }

    public function reprocess(int $limit = 300, int $offset = 0): array {
        $limit = max(1, min(1000, $limit));
        $offset = max(0, $offset);
        $pdo = Database::pdo();
        $jobId = $this->startJob();
        $sql = 'SELECT o.id FROM orders o LEFT JOIN order_intelligence oi ON oi.order_id=o.id WHERE oi.order_id IS NULL OR oi.processed_at IS NULL ORDER BY o.id ASC LIMIT '.(int)$limit.' OFFSET '.(int)$offset;
        $rows = $pdo->query($sql)->fetchAll();
        $processed = 0;
        foreach ($rows as $row) {
            $this->analyzeOrder((int)$row['id']);
            $processed++;
        }
        $remaining = (int)$pdo->query('SELECT COUNT(*) FROM orders o LEFT JOIN order_intelligence oi ON oi.order_id=o.id WHERE oi.order_id IS NULL OR oi.processed_at IS NULL')->fetchColumn();
        $this->clearCache();
        $pdo->prepare('UPDATE intelligence_jobs SET status=?, processed=?, message=?, finished_at=NOW() WHERE id=?')->execute(['done', $processed, 'Restantes: '.$remaining, $jobId]);
        return ['processed'=>$processed, 'remaining'=>$remaining, 'job_id'=>$jobId];
    }

    public function status(): array {
        $pdo = Database::pdo();
        $total = (int)$pdo->query('SELECT COUNT(*) FROM orders')->fetchColumn();
        $ready = (int)$pdo->query('SELECT COUNT(*) FROM order_intelligence WHERE processed_at IS NOT NULL')->fetchColumn();
        $last = $pdo->query('SELECT * FROM intelligence_jobs ORDER BY id DESC LIMIT 1')->fetch() ?: null;
        return ['total'=>$total, 'ready'=>$ready, 'pending'=>max(0, $total-$ready), 'last_job'=>$last];
    }

    public function cacheGet(string $key): ?array {
        $st = Database::pdo()->prepare('SELECT payload FROM report_cache WHERE cache_key=? AND (expires_at IS NULL OR expires_at>NOW()) LIMIT 1');
        $st->execute([$key]);
        $payload = $st->fetchColumn();
        return $payload ? (json_decode($payload, true) ?: null) : null;
    }

    public function cacheSet(string $key, array $payload, int $ttlSeconds = 300): void {
        $expires = date('Y-m-d H:i:s', time() + $ttlSeconds);
        Database::pdo()->prepare('INSERT INTO report_cache (cache_key, payload, expires_at) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE payload=VALUES(payload), expires_at=VALUES(expires_at), updated_at=NOW()')
            ->execute([$key, json_encode($payload, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES), $expires]);
    }

    public function clearCache(): void { Database::pdo()->exec('DELETE FROM report_cache'); }

    private function startJob(): int {
        $pdo = Database::pdo();
        $pdo->prepare('INSERT INTO intelligence_jobs (status, started_at) VALUES (?, NOW())')->execute(['running']);
        return (int)$pdo->lastInsertId();
    }

    private function firstMetaValue(array $rows, array $contains): string {
        foreach ($rows as $r) {
            $key = (string)$r['normalized_key'];
            foreach ($contains as $needle) if (str_contains($key, $needle)) return clean_text((string)$r['meta_value']);
        }
        return '';
    }
    private function firstContains(array $rows, array $contains): string { return $this->firstMetaValue($rows, $contains); }
    private function firstExact(array $rows, array $keys): string {
        foreach ($rows as $r) if (in_array((string)$r['normalized_key'], $keys, true)) return clean_text((string)$r['meta_value']);
        return '';
    }
    private function inferTypeFromText(string $text): string {
        $n = normalize_key($text);
        if (str_contains($n,'nascimento')) return 'Nascimento';
        if (str_contains($n,'casamento') || str_contains($n,'habilitacao')) return 'Casamento';
        if (str_contains($n,'obito')) return 'Óbito';
        return '';
    }
    private function category(string $text): string {
        $n = normalize_key($text);
        if (str_contains($n,'nascimento')) return 'nascimento';
        if (str_contains($n,'casamento') || str_contains($n,'habilitacao')) return 'casamento';
        if (str_contains($n,'obito')) return 'obito';
        if (str_contains($n,'busca')) return 'busca';
        return 'outros';
    }
    private function modality(string $text, array $items): string {
        $n = normalize_key($text);
        if (str_contains($n,'copia_reprografica')) return 'Cópia reprográfica';
        if (str_contains($n,'inteiro_teor')) return 'Inteiro teor';
        if (str_contains($n,'breve_relato')) return 'Breve relato';
        foreach ($items as $item) {
            $name = normalize_key((string)($item['name'] ?? ''));
            if (str_contains($name,'apostila')) return 'Apostila';
        }
        return '';
    }
    private function deliveryType(string $method, string $methodId): string {
        $n = normalize_key($method.' '.$methodId);
        if ($n === '') return 'sem_entrega';
        if (str_contains($n,'sedex') || str_contains($n,'correios') || str_contains($n,'cws')) return 'sedex';
        if (str_contains($n,'retir') || str_contains($n,'pickup') || str_contains($n,'local_pickup')) return 'retirada';
        return 'outros';
    }
    private function isFreeRequest(array $rows, string $allNorm): bool {
        if (str_contains($allNorm, 'gratuidade_sim')) return true;
        foreach ($rows as $r) {
            if (str_contains((string)$r['normalized_key'], 'gratuidade') && preg_match('/\bsim\b/i', (string)$r['meta_value'])) return true;
        }
        return false;
    }
    private function hasAttachment(array $rows): bool {
        foreach ($rows as $r) {
            $key = (string)$r['normalized_key']; $value = trim((string)$r['meta_value']);
            if ($value !== '' && (str_contains($key, 'anexar') || str_contains($key, 'arquivo') || str_contains($value, '.pdf') || str_contains($value, 'uploads/'))) return true;
        }
        return false;
    }
}
