<?php
namespace App\Models;

use App\Core\Database;

final class Capacity
{
    public static function summary(): array
    {
        $row=Database::connection()->query(
            "SELECT
                COUNT(*) AS nb_groupes,
                SUM(CASE WHEN statut_capacite='complet' THEN 1 ELSE 0 END)
                    AS nb_complets,
                SUM(CASE WHEN statut_capacite='presque_complet' THEN 1 ELSE 0 END)
                    AS nb_presque_complets,
                SUM(COALESCE(places_restantes,0)) AS places_restantes
             FROM v_groupes_capacites"
        )->fetch();

        return $row ?: [
            'nb_groupes'=>0,
            'nb_complets'=>0,
            'nb_presque_complets'=>0,
            'places_restantes'=>0
        ];
    }

    public static function alerts(int $limit=8): array
    {
        $stmt=Database::connection()->prepare(
            "SELECT *
             FROM v_groupes_capacites
             WHERE statut_capacite IN ('complet','presque_complet')
             ORDER BY
                FIELD(statut_capacite,'complet','presque_complet'),
                taux_remplissage DESC,
                atelier_libelle,
                groupe_libelle
             LIMIT :limit"
        );
        $stmt->bindValue('limit',$limit,\PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function availableGroups(int $limit=12): array
    {
        $stmt=Database::connection()->prepare(
            "SELECT *
             FROM v_groupes_capacites
             WHERE statut_capacite='disponible'
               AND places_restantes IS NOT NULL
             ORDER BY places_restantes,taux_remplissage DESC
             LIMIT :limit"
        );
        $stmt->bindValue('limit',$limit,\PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }
}
