<?php
namespace App\Models;

use App\Core\Database;

final class Pricing
{
    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 activeGrid(): ?array
    {
        return Database::connection()->query(
            "SELECT g.*,a.libelle AS annee_libelle
             FROM grilles_tarifaires g
             INNER JOIN annees_scolaires a ON a.id=g.annee_scolaire_id
             WHERE g.statut IN ('brouillon','active')
             ORDER BY g.statut='active' DESC,g.id DESC
             LIMIT 1"
        )->fetch() ?: null;
    }

    public static function courseCatalog(): array
    {
        return Database::connection()->query(
            "SELECT
                a.id,
                a.code,
                a.libelle,
                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,p.libelle,d.libelle
             ORDER BY p.ordre_affichage,d.libelle,a.libelle"
        )->fetchAll();
    }

    public static function lines(int $gridId): array
    {
        $stmt=Database::connection()->prepare(
            "SELECT
                l.*,
                a.code AS atelier_code,
                a.libelle AS atelier_libelle
             FROM grille_tarifaire_lignes l
             LEFT JOIN ateliers a ON a.id=l.atelier_id
             WHERE l.grille_id=:grid_id
               AND l.actif=1
             ORDER BY a.code,l.priorite,l.id"
        );
        $stmt->execute(['grid_id'=>$gridId]);
        return $stmt->fetchAll();
    }

    public static function createGrid(array $data): int
    {
        $stmt=Database::connection()->prepare(
            "INSERT INTO grilles_tarifaires(
                organisation_id,annee_scolaire_id,
                code,libelle,version_numero,
                date_debut_validite,date_fin_validite,
                statut
             ) VALUES(
                :organisation_id,:annee_scolaire_id,
                :code,:libelle,:version_numero,
                :date_debut_validite,:date_fin_validite,
                :statut
             )"
        );
        $stmt->execute($data);
        return (int)Database::connection()->lastInsertId();
    }

    public static function addLine(array $data): int
    {
        $stmt=Database::connection()->prepare(
            "INSERT INTO grille_tarifaire_lignes(
                grille_id,atelier_id,libelle,montant,
                periodicite,priorite,actif
             ) VALUES(
                :grille_id,:atelier_id,:libelle,:montant,
                :periodicite,:priorite,1
             )"
        );
        $stmt->execute($data);
        return (int)Database::connection()->lastInsertId();
    }
}
