<?php
namespace App\Models;

use App\Core\Database;
use Throwable;

final class Workshop
{
    public static function all(): array
    {
        return Database::connection()->query(
            "SELECT
                a.*,
                p.code AS pole_code,
                p.libelle AS pole_libelle,
                d.code AS discipline_code,
                d.libelle AS discipline_libelle,
                GROUP_CONCAT(
                    DISTINCT pc.code
                    ORDER BY pc.ordre_affichage
                    SEPARATOR ','
                ) AS public_codes,
                GROUP_CONCAT(
                    DISTINCT pc.libelle
                    ORDER BY pc.ordre_affichage
                    SEPARATOR ', '
                ) AS public_libelles,
                an.libelle AS annee_libelle,
                (
                    SELECT COUNT(*)
                    FROM groupes_cours g
                    WHERE g.atelier_id = a.id
                      AND g.deleted_at IS NULL
                ) AS nb_groupes,
                (
                    SELECT COUNT(*)
                    FROM groupe_participants gp
                    INNER JOIN groupes_cours gg ON gg.id = gp.groupe_id
                    WHERE gg.atelier_id = a.id
                      AND gg.deleted_at IS NULL
                      AND gp.deleted_at IS NULL
                      AND gp.statut = 'actif'
                ) AS nb_inscrits,
                (
                    SELECT COUNT(*)
                    FROM demandes_inscription di
                    WHERE di.organisation_id = a.organisation_id
                      AND di.deleted_at IS NULL
                      AND di.statut IN ('file_attente','a_etudier','creneau_propose')
                      AND (
                          di.atelier_id = a.id
                          OR (
                              di.atelier_id IS NULL
                              AND di.discipline_id = a.discipline_id
                              AND di.annee_scolaire_id = a.annee_scolaire_id
                          )
                      )
                ) AS nb_demandes_attente,
                (
                    SELECT MIN(CONCAT(pv.jour_semaine, ' ', TIME_FORMAT(pv.heure_debut, '%H:%i')))
                    FROM groupes_cours gs
                    INNER JOIN groupe_planning_versions pv ON pv.groupe_id = gs.id
                    WHERE gs.atelier_id = a.id
                      AND gs.deleted_at IS NULL
                      AND pv.date_fin_effet IS NULL
                ) AS prochain_creneau
             FROM ateliers a
             INNER JOIN annees_scolaires an
                     ON an.id = a.annee_scolaire_id
             LEFT JOIN poles p
                    ON p.id = a.pole_id
             LEFT JOIN disciplines d
                    ON d.id = a.discipline_id
             LEFT JOIN atelier_publics ap
                    ON ap.atelier_id = a.id
             LEFT JOIN publics_cibles pc
                    ON pc.id = ap.public_cible_id
             WHERE a.deleted_at IS NULL
             GROUP BY
                a.id,
                p.code,
                p.libelle,
                d.code,
                d.libelle,
                an.libelle
             ORDER BY
                p.ordre_affichage,
                d.libelle,
                a.libelle"
        )->fetchAll();
    }

    public static function active(): array
    {
        return Database::connection()->query(
            "SELECT
                a.id,
                a.code,
                a.libelle,
                a.type_atelier,
                p.libelle AS pole_libelle,
                d.libelle AS discipline_libelle,
                GROUP_CONCAT(
                    DISTINCT pc.libelle
                    ORDER BY pc.ordre_affichage
                    SEPARATOR ', '
                ) AS publics
             FROM ateliers a
             LEFT JOIN poles p
                    ON p.id = a.pole_id
             LEFT JOIN disciplines d
                    ON d.id = a.discipline_id
             LEFT JOIN atelier_publics ap
                    ON ap.atelier_id = a.id
             LEFT JOIN publics_cibles pc
                    ON pc.id = ap.public_cible_id
             WHERE a.deleted_at IS NULL
               AND a.actif = 1
             GROUP BY
                a.id,
                a.code,
                a.libelle,
                a.type_atelier,
                p.libelle,
                d.libelle
             ORDER BY
                p.libelle,
                d.libelle,
                a.libelle"
        )->fetchAll();
    }

    public static function poles(): array
    {
        return Database::connection()->query(
            "SELECT id, code, libelle
             FROM poles
             WHERE actif = 1
               AND deleted_at IS NULL
             ORDER BY ordre_affichage, libelle"
        )->fetchAll();
    }

    public static function disciplines(): array
    {
        return Database::connection()->query(
            "SELECT
                d.id,
                d.pole_id,
                d.code,
                d.libelle,
                p.code AS pole_code,
                p.libelle AS pole_libelle
             FROM disciplines d
             INNER JOIN poles p
                     ON p.id = d.pole_id
             WHERE d.actif = 1
               AND d.deleted_at IS NULL
               AND p.actif = 1
               AND p.deleted_at IS NULL
             ORDER BY
                p.ordre_affichage,
                d.libelle"
        )->fetchAll();
    }

    public static function publics(): array
    {
        return Database::connection()->query(
            "SELECT id, code, libelle
             FROM publics_cibles
             WHERE actif = 1
               AND deleted_at IS NULL
             ORDER BY ordre_affichage"
        )->fetchAll();
    }

    public static function currentYear(): ?array
    {
        return Database::connection()->query(
            "SELECT id, libelle
             FROM annees_scolaires
             WHERE active = 1
             ORDER BY id DESC
             LIMIT 1"
        )->fetch() ?: null;
    }

    public static function create(array $data): int
    {
        $pdo = Database::connection();
        $pdo->beginTransaction();

        try {
            $publicCodes = self::publicCodes(
                $data['public_ids']
            );

            $audienceCode = self::audienceCode(
                $publicCodes
            );

            $code = self::generateUniqueCode(
                (int)$data['organisation_id'],
                (int)$data['annee_scolaire_id'],
                $data['pole_code'],
                $data['discipline_code'],
                $audienceCode
            );

            $stmt = $pdo->prepare(
                "INSERT INTO ateliers(
                    organisation_id,
                    annee_scolaire_id,
                    pole_id,
                    discipline_id,
                    public_cible_id,
                    code,
                    libelle,
                    type_atelier,
                    description,
                    duree_standard_minutes,
                    capacite_max,
                    statut,
                    actif
                 ) VALUES(
                    :organisation_id,
                    :annee_scolaire_id,
                    :pole_id,
                    :discipline_id,
                    :public_cible_id,
                    :code,
                    :libelle,
                    :type_atelier,
                    :description,
                    :duree_standard_minutes,
                    :capacite_max,
                    :statut,
                    1
                 )"
            );

            $stmt->execute([
                'organisation_id' =>
                    $data['organisation_id'],
                'annee_scolaire_id' =>
                    $data['annee_scolaire_id'],
                'pole_id' =>
                    $data['pole_id'],
                'discipline_id' =>
                    $data['discipline_id'],
                // Compatibilité avec les anciennes requêtes :
                // le premier public reste recopié dans l'ancien champ.
                'public_cible_id' =>
                    $data['public_ids'][0] ?? null,
                'code' => $code,
                'libelle' => $data['libelle'],
                'type_atelier' => $data['type_atelier'],
                'description' =>
                    $data['description'] ?: null,
                'duree_standard_minutes' =>
                    $data['duree_standard_minutes'] ?: null,
                'capacite_max' =>
                    $data['capacite_max'] ?: null,
                'statut' => $data['statut'],
            ]);

            $workshopId = (int)$pdo->lastInsertId();

            $link = $pdo->prepare(
                "INSERT INTO atelier_publics(
                    atelier_id,
                    public_cible_id
                 ) VALUES(
                    :atelier_id,
                    :public_cible_id
                 )"
            );

            foreach ($data['public_ids'] as $publicId) {
                $link->execute([
                    'atelier_id' => $workshopId,
                    'public_cible_id' => (int)$publicId,
                ]);
            }

            $pdo->commit();
            return $workshopId;
        } catch (Throwable $error) {
            $pdo->rollBack();
            throw $error;
        }
    }

    private static function publicCodes(array $publicIds): array
    {
        if (!$publicIds) {
            return [];
        }

        $placeholders = implode(
            ',',
            array_fill(0, count($publicIds), '?')
        );

        $stmt = Database::connection()->prepare(
            "SELECT code
             FROM publics_cibles
             WHERE id IN ({$placeholders})
               AND actif = 1"
        );

        $stmt->execute(
            array_map('intval', $publicIds)
        );

        return array_column(
            $stmt->fetchAll(),
            'code'
        );
    }

    private static function audienceCode(
        array $publicCodes
    ): string {
        if (count(array_unique($publicCodes)) > 1) {
            return 'MIX';
        }
        return strtoupper((string)($publicCodes[0] ?? 'PUB'));
    }

    private static function generateUniqueCode(
        int $organisationId,
        int $schoolYearId,
        string $poleCode,
        string $disciplineCode,
        string $audienceCode
    ): string {
        $normalize = static function(
            string $value
        ): string {
            $ascii = iconv(
                'UTF-8',
                'ASCII//TRANSLIT//IGNORE',
                $value
            ) ?: $value;

            return preg_replace(
                '/[^A-Z0-9]/',
                '',
                strtoupper($ascii)
            ) ?: '';
        };

        $base =
            substr($normalize($poleCode), 0, 3)
            . '-'
            . substr($normalize($disciplineCode), 0, 6)
            . '-'
            . substr($normalize($audienceCode), 0, 3);

        $candidate = $base;
        $counter = 1;

        $stmt = Database::connection()->prepare(
            "SELECT 1
             FROM ateliers
             WHERE organisation_id = :organisation_id
               AND annee_scolaire_id = :annee_scolaire_id
               AND code = :code
             LIMIT 1"
        );

        while (true) {
            $stmt->execute([
                'organisation_id' => $organisationId,
                'annee_scolaire_id' => $schoolYearId,
                'code' => $candidate,
            ]);

            if (!$stmt->fetchColumn()) {
                return $candidate;
            }

            $counter++;
            $candidate = $base . '-' . $counter;
        }
    }
}
