<?php
namespace App\Models;
use App\Core\Database;
use RuntimeException;
final class StudentMusicLevel
{
 public static function currentYear(int $organisationId):?array{$q=Database::connection()->prepare("SELECT id,libelle,date_debut,date_fin FROM annees_scolaires WHERE organisation_id=? AND active=1 ORDER BY date_debut DESC LIMIT 1");$q->execute([$organisationId]);return $q->fetch()?:null;}
 public static function references():array{return Database::connection()->query("SELECT code,libelle,categorie FROM ref_niveaux_musique WHERE actif=1 ORDER BY ordre_affichage,libelle")->fetchAll();}
 public static function instruments(int $organisationId):array{$q=Database::connection()->prepare("SELECT id,libelle FROM disciplines WHERE organisation_id=? AND actif=1 AND (code IN ('PIA','GUI','VIO','VLC','FLU','CLA','SAX','TRP','TRB','BAT') OR LOWER(libelle) REGEXP 'piano|guitare|violon|violoncelle|flûte|flute|clarinette|saxophone|trompette|trombone|batterie|percussion') ORDER BY libelle");$q->execute([$organisationId]);return $q->fetchAll();}
 public static function history(string $source,int $memberId,int $organisationId):array{$q=Database::connection()->prepare("SELECT n.*,a.libelle annee_libelle,r.libelle niveau_libelle,d.libelle instrument_libelle FROM eleve_niveaux_musique n JOIN annees_scolaires a ON a.id=n.annee_scolaire_id JOIN ref_niveaux_musique r ON r.code=n.niveau_code LEFT JOIN disciplines d ON d.id=NULLIF(n.instrument_id,0) WHERE n.organisation_id=? AND n.membre_source=? AND n.membre_id=? ORDER BY a.date_debut DESC,n.type_niveau,n.instrument_id");$q->execute([$organisationId,$source,$memberId]);return $q->fetchAll();}
 public static function save(int $organisationId,string $source,int $memberId,int $userId,array $data):void{$type=in_array($data['type_niveau']??'', ['formation_musicale','instrument'],true)?$data['type_niveau']:'';$instrument=$type==='instrument'?(int)($data['instrument_id']??0):0;$code=trim((string)($data['niveau_code']??''));$year=(int)($data['annee_scolaire_id']??0);if($type===''||$code===''||$year<1)throw new RuntimeException('Le type, le niveau et l’année scolaire sont obligatoires.');if($type==='instrument'&&$instrument<1)throw new RuntimeException('Sélectionnez l’instrument concerné.');$check=Database::connection()->prepare("SELECT COUNT(*) FROM ref_niveaux_musique WHERE code=? AND actif=1");$check->execute([$code]);if(!(int)$check->fetchColumn())throw new RuntimeException('Niveau musical non reconnu.');$q=Database::connection()->prepare("INSERT INTO eleve_niveaux_musique(organisation_id,membre_source,membre_id,annee_scolaire_id,type_niveau,instrument_id,niveau_code,observation,saisi_par) VALUES(?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE niveau_code=VALUES(niveau_code),observation=VALUES(observation),saisi_par=VALUES(saisi_par),updated_at=CURRENT_TIMESTAMP");$q->execute([$organisationId,$source,$memberId,$year,$type,$instrument,$code,trim((string)($data['observation']??''))?:null,$userId?:null]);}
}
