<?php

declare(strict_types=1);

namespace App\Services;

use App\Core\Database;

/**
 * GlobalSearchService
 *
 * Backs the topbar search box that's been sitting inert since Phase 2.
 * Every CRM in this product's competitive set (HubSpot, Salesforce,
 * Zoho, Monday, GoHighLevel) has an omnisearch — this is A&B Hub's:
 * one query fan-out across leads/customers/companies, capped and
 * ranked by simple relevance (prefix match first), returned as a flat
 * list the topbar renders as grouped results.
 *
 * Every LIKE placeholder gets its OWN bound parameter name even when
 * the value is identical — this project's PDO connection disables
 * emulated prepares (real server-side prepared statements), which do
 * NOT support reusing one named placeholder more than once per query.
 */
final class GlobalSearchService
{
    public function search(string $query, int $limitPerType = 5): array
    {
        $query = trim($query);
        if (mb_strlen($query) < 2) {
            return ['leads' => [], 'customers' => [], 'companies' => []];
        }

        return [
            'leads' => $this->searchLeads($query, $limitPerType),
            'customers' => $this->searchCustomers($query, $limitPerType),
            'companies' => $this->searchCompanies($query, $limitPerType),
        ];
    }

    private function searchLeads(string $query, int $limit): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT id, CONCAT(first_name, ' ', COALESCE(last_name, '')) AS label, email, phone
             FROM leads
             WHERE deleted_at IS NULL AND merged_into_lead_id IS NULL
               AND (first_name LIKE :q1 OR last_name LIKE :q2 OR email LIKE :q3 OR phone LIKE :q4)
             ORDER BY (first_name LIKE :exact) DESC, created_at DESC
             LIMIT :limit"
        );
        $like = '%' . $query . '%';
        $stmt->bindValue('q1', $like);
        $stmt->bindValue('q2', $like);
        $stmt->bindValue('q3', $like);
        $stmt->bindValue('q4', $like);
        $stmt->bindValue('exact', $query . '%');
        $stmt->bindValue('limit', $limit, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    private function searchCustomers(string $query, int $limit): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT id, CONCAT(first_name, ' ', COALESCE(last_name, '')) AS label, email, phone
             FROM customers
             WHERE deleted_at IS NULL
               AND (first_name LIKE :q1 OR last_name LIKE :q2 OR email LIKE :q3 OR phone LIKE :q4)
             ORDER BY (first_name LIKE :exact) DESC, created_at DESC
             LIMIT :limit"
        );
        $like = '%' . $query . '%';
        $stmt->bindValue('q1', $like);
        $stmt->bindValue('q2', $like);
        $stmt->bindValue('q3', $like);
        $stmt->bindValue('q4', $like);
        $stmt->bindValue('exact', $query . '%');
        $stmt->bindValue('limit', $limit, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    private function searchCompanies(string $query, int $limit): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT id, name AS label, industry, phone
             FROM companies
             WHERE deleted_at IS NULL AND name LIKE :q1
             ORDER BY (name LIKE :exact) DESC, created_at DESC
             LIMIT :limit"
        );
        $stmt->bindValue('q1', '%' . $query . '%');
        $stmt->bindValue('exact', $query . '%');
        $stmt->bindValue('limit', $limit, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }
}
