<?php
namespace App\Models;

use App\Core\Database;
use Throwable;

final class WorkshopV51
{
    public static function allActive(): array
    {
        return Database::connection()->query(
            "SELECT
                a.id,
                a.code,
                a.libelle,
                a.type_atelier,
                a.duree_standard_minutes,
                a.capacite_max,
                a.statut,
                a.actif,
                p.libelle AS pole_libelle,
                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
             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,
                a.duree_standard_minutes,a.capacite_max,
                a.statut,a.actif,p.libelle,d.libelle
             ORDER BY
                p.ordre_affichage,
                d.libelle,
                a.libelle"
        )->fetchAll();
    }

    public static function find(int $id): ?array
    {
        $stmt=Database::connection()->prepare(
            "SELECT a.*,p.code AS pole_code,p.libelle AS pole_libelle,
                    d.code AS discipline_code,d.libelle AS discipline_libelle
             FROM ateliers a
             LEFT JOIN poles p ON p.id=a.pole_id
             LEFT JOIN disciplines d ON d.id=a.discipline_id
             WHERE a.id=:id
               AND a.deleted_at IS NULL
             LIMIT 1"
        );
        $stmt->execute(['id'=>$id]);
        return $stmt->fetch() ?: null;
    }

    public static function publicIds(int $id): array
    {
        $stmt=Database::connection()->prepare(
            "SELECT public_cible_id
             FROM atelier_publics
             WHERE atelier_id=:id"
        );
        $stmt->execute(['id'=>$id]);

        return array_map(
            'intval',
            array_column($stmt->fetchAll(),'public_cible_id')
        );
    }

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

    public static function update(
        int $id,
        array $data
    ): void {
        $pdo=Database::connection();
        $pdo->beginTransaction();

        try{
            $stmt=$pdo->prepare(
                "UPDATE ateliers
                 SET
                    libelle=:libelle,
                    type_atelier=:type_atelier,
                    description=:description,
                    duree_standard_minutes=:duree,
                    capacite_max=:capacite,
                    updated_at=CURRENT_TIMESTAMP
                 WHERE id=:id"
            );

            $stmt->execute([
                'libelle'=>$data['libelle'],
                'type_atelier'=>$data['type_atelier'],
                'description'=>$data['description'] ?: null,
                'duree'=>$data['duree_standard_minutes'] ?: null,
                'capacite'=>$data['capacite_max'] ?: null,
                'id'=>$id,
            ]);

            $pdo->prepare(
                "DELETE FROM atelier_publics
                 WHERE atelier_id=:id"
            )->execute(['id'=>$id]);

            $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'=>$id,
                    'public_cible_id'=>(int)$publicId,
                ]);
            }

            $pdo->commit();
        }catch(Throwable $e){
            $pdo->rollBack();
            throw $e;
        }
    }

    public static function archive(
        int $id,
        int $userId
    ): void {
        Database::connection()->prepare(
            "UPDATE ateliers
             SET
                actif=0,
                statut='archive',
                archive_at=CURRENT_TIMESTAMP,
                archive_by=:user_id,
                updated_at=CURRENT_TIMESTAMP
             WHERE id=:id"
        )->execute([
            'user_id'=>$userId,
            'id'=>$id,
        ]);
    }
}
