<?php
namespace App\Models;

use App\Core\Auth;
use App\Core\Database;
use PDO;
use Throwable;

final class GlobalSearch
{
    private static function table(string $table): bool
    {
        $stmt = Database::connection()->prepare(
            "SELECT COUNT(*)
             FROM information_schema.TABLES
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = :table_name"
        );
        $stmt->execute(['table_name' => $table]);
        return (int)$stmt->fetchColumn() > 0;
    }

    private static function column(string $table, string $column): bool
    {
        $stmt = Database::connection()->prepare(
            "SELECT COUNT(*)
             FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = :table_name
               AND COLUMN_NAME = :column_name"
        );
        $stmt->execute([
            'table_name' => $table,
            'column_name' => $column,
        ]);
        return (int)$stmt->fetchColumn() > 0;
    }

    public static function search(string $query): array
    {
        $query = trim($query);
        if (mb_strlen($query) < 2) {
            return [];
        }

        $like = '%' . $query . '%';
        $results = [];

        self::appendPeople($results, $like);
        self::appendProfessors($results, $like);
        // Les ateliers et groupes restent volontairement exclus tant que leurs
        // écrans de destination n'acceptent pas encore d'identifiant opaque.

        return array_slice($results, 0, 30);
    }

    private static function appendPeople(array &$results, string $like): void
    {
        $organisationId=(int)(Auth::user()['organisation_id']??0);
        $stmt=Database::connection()->prepare(
            "SELECT c.public_token,c.code_client,c.nom,c.prenom,'Adhérent' result_type
             FROM clients c
             WHERE c.organisation_id=? AND c.deleted_at IS NULL
               AND (c.nom LIKE ? OR c.prenom LIKE ? OR c.code_client LIKE ?)
             UNION ALL
             SELECT c.public_token,c.code_client,m.nom,m.prenom,'Membre' result_type
             FROM membres_famille m
             INNER JOIN familles f ON f.id=m.famille_id AND f.deleted_at IS NULL
             INNER JOIN clients c ON c.id=f.client_id AND c.deleted_at IS NULL
             WHERE f.organisation_id=? AND m.deleted_at IS NULL
               AND (m.nom LIKE ? OR m.prenom LIKE ?)
             ORDER BY nom,prenom LIMIT 16"
        );
        $stmt->execute([$organisationId,$like,$like,$like,$organisationId,$like,$like]);
        foreach($stmt->fetchAll(PDO::FETCH_ASSOC)?:[] as $row){
            $results[]=[
                'type'=>(string)$row['result_type'],
                'title'=>trim((string)$row['prenom'].' '.(string)$row['nom']),
                'subtitle'=>(string)$row['code_client'],
                'url'=>'/public/adherents/dossier?dossier='.rawurlencode((string)$row['public_token']),
                'icon'=>$row['result_type']==='Membre'?'👥':'👤',
            ];
        }
    }

    private static function appendProfessors(array &$results, string $like): void
    {
        if (!self::table('professeurs')) {
            return;
        }

        $hasCode = self::column('professeurs', 'code_professeur');
        $sql = "SELECT public_token, nom, prenom"
            . ($hasCode ? ", code_professeur" : "")
            . " FROM professeurs
               WHERE (
                    nom LIKE :prof_nom
                    OR prenom LIKE :prof_prenom"
            . ($hasCode ? " OR code_professeur LIKE :prof_code" : "")
            . ")"
            . (self::column('professeurs', 'deleted_at') ? " AND deleted_at IS NULL" : "")
            . " AND organisation_id=:organisation_id"
            . " ORDER BY nom, prenom
               LIMIT 8";

        $params = [
            'prof_nom' => $like,
            'prof_prenom' => $like,
            'organisation_id'=>(int)(Auth::user()['organisation_id']??0),
        ];
        if ($hasCode) {
            $params['prof_code'] = $like;
        }

        try {
            $stmt = Database::connection()->prepare($sql);
            $stmt->execute($params);

            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                $results[] = [
                    'type' => 'Professeur',
                    'title' => trim((string)$row['prenom'] . ' ' . (string)$row['nom']),
                    'subtitle' => (string)($row['code_professeur'] ?? ''),
                    'url' => '/public/professeurs-pro/voir?professeur=' . rawurlencode((string)$row['public_token']),
                    'icon' => '👨‍🏫',
                ];
            }
        } catch (Throwable) {
        }
    }

    private static function appendWorkshops(array &$results, string $like): void
    {
        if (!self::table('ateliers')) {
            return;
        }

        $hasCode = self::column('ateliers', 'code');
        $conditions = ['libelle LIKE :atelier_libelle'];
        $params = ['atelier_libelle' => $like];

        if ($hasCode) {
            $conditions[] = 'code LIKE :atelier_code';
            $params['atelier_code'] = $like;
        }

        $sql = "SELECT id, libelle"
            . ($hasCode ? ", code" : "")
            . " FROM ateliers
               WHERE (" . implode(' OR ', $conditions) . ")"
            . (self::column('ateliers', 'deleted_at') ? " AND deleted_at IS NULL" : "")
            . " ORDER BY libelle
               LIMIT 8";

        try {
            $stmt = Database::connection()->prepare($sql);
            $stmt->execute($params);

            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                $results[] = [
                    'type' => 'Atelier',
                    'title' => (string)$row['libelle'],
                    'subtitle' => (string)($row['code'] ?? ''),
                    'url' => '/public/ateliers/voir?id=' . (int)$row['id'],
                    'icon' => '🎨',
                ];
            }
        } catch (Throwable) {
        }
    }

    private static function appendGroups(array &$results, string $like): void
    {
        if (!self::table('groupes_cours')) {
            return;
        }

        $hasCode = self::column('groupes_cours', 'code');
        $conditions = ['libelle LIKE :groupe_libelle'];
        $params = ['groupe_libelle' => $like];

        if ($hasCode) {
            $conditions[] = 'code LIKE :groupe_code';
            $params['groupe_code'] = $like;
        }

        $sql = "SELECT id, libelle"
            . ($hasCode ? ", code" : "")
            . " FROM groupes_cours
               WHERE (" . implode(' OR ', $conditions) . ")"
            . (self::column('groupes_cours', 'deleted_at') ? " AND deleted_at IS NULL" : "")
            . " ORDER BY libelle
               LIMIT 8";

        try {
            $stmt = Database::connection()->prepare($sql);
            $stmt->execute($params);

            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                $results[] = [
                    'type' => 'Groupe',
                    'title' => (string)$row['libelle'],
                    'subtitle' => (string)($row['code'] ?? ''),
                    'url' => '/public/groupes/participants?groupe_id=' . (int)$row['id'],
                    'icon' => '🗓️',
                ];
            }
        } catch (Throwable) {
        }
    }
}
