<?php
namespace App\Models;

use App\Core\Database;

final class PricingV51
{
    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,
                CASE
                    WHEN gtl.id IS NULL THEN 0
                    ELSE 1
                END AS has_tariff
             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
             LEFT JOIN grilles_tarifaires gt
                    ON gt.annee_scolaire_id=a.annee_scolaire_id
                   AND gt.statut IN ('brouillon','active')
             LEFT JOIN grille_tarifaire_lignes gtl
                    ON gtl.grille_id=gt.id
                   AND gtl.atelier_id=a.id
                   AND gtl.actif=1
             WHERE a.deleted_at IS NULL
               AND a.actif=1
             GROUP BY
                a.id,a.code,a.libelle,
                p.libelle,d.libelle,gtl.id
             ORDER BY
                has_tariff,
                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.id"
        );
        $stmt->execute(['grid_id'=>$gridId]);
        return $stmt->fetchAll();
    }

    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,
                10,
                1
             )"
        );
        $stmt->execute($data);
        return (int)Database::connection()->lastInsertId();
    }
}
