<?php
namespace App\Models;

use App\Core\Database;

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

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

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

    public static function schoolLevels(): array
    {
        return Database::connection()->query(
            "SELECT id,code,libelle,rang_scolaire
             FROM niveaux_scolaires
             WHERE actif=1
             ORDER BY ordre_affichage"
        )->fetchAll();
    }

    public static function eligibilityRules(): array
    {
        return Database::connection()->query(
            "SELECT r.*,
                    p.libelle AS pole_libelle,
                    d.libelle AS discipline_libelle,
                    pc.libelle AS public_libelle,
                    ns.libelle AS niveau_min_libelle
             FROM regles_eligibilite r
             LEFT JOIN poles p ON p.id=r.pole_id
             LEFT JOIN disciplines d ON d.id=r.discipline_id
             LEFT JOIN publics_cibles pc ON pc.id=r.public_cible_id
             LEFT JOIN niveaux_scolaires ns ON ns.id=r.niveau_scolaire_min_id
             WHERE r.actif=1
             ORDER BY p.ordre_affichage,d.libelle"
        )->fetchAll();
    }

    public static function createPole(array $data): int
    {
        $stmt=Database::connection()->prepare(
            "INSERT INTO poles(
                organisation_id,code,libelle,actif,ordre_affichage
             ) VALUES(
                :organisation_id,:code,:libelle,1,:ordre_affichage
             )"
        );
        $stmt->execute($data);
        return (int)Database::connection()->lastInsertId();
    }

    public static function createDiscipline(array $data): int
    {
        $stmt=Database::connection()->prepare(
            "INSERT INTO disciplines(
                organisation_id,pole_id,code,libelle,
                duree_individuelle_minutes,duree_collective_minutes,
                actif,created_at
             ) VALUES(
                :organisation_id,:pole_id,:code,:libelle,
                :duree_individuelle_minutes,:duree_collective_minutes,
                1,CURRENT_TIMESTAMP
             )"
        );
        $stmt->execute($data);
        return (int)Database::connection()->lastInsertId();
    }
}
