Mathias STRASSER
b7dc27f2a5
Les enseignants ont besoin de moyennes à jour immédiatement après la publication ou modification des notes, sans attendre un batch nocturne. Le système recalcule via Domain Events synchrones : statistiques d'évaluation (min/max/moyenne/médiane), moyennes matières pondérées (normalisation /20), et moyenne générale par élève. Les résultats sont stockés dans des tables dénormalisées avec cache Redis (TTL 5 min). Trois endpoints API exposent les données avec contrôle d'accès par rôle. Une commande console permet le backfill des données historiques au déploiement.
85 lines
3.0 KiB
PHP
85 lines
3.0 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace DoctrineMigrations;
|
|
|
|
use Doctrine\DBAL\Schema\Schema;
|
|
use Doctrine\Migrations\AbstractMigration;
|
|
|
|
final class Version20260329082334 extends AbstractMigration
|
|
{
|
|
public function getDescription(): string
|
|
{
|
|
return 'Création des tables dénormalisées pour les moyennes élèves et statistiques évaluations';
|
|
}
|
|
|
|
public function up(Schema $schema): void
|
|
{
|
|
$this->addSql(<<<'SQL'
|
|
CREATE TABLE student_averages (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
student_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
subject_id UUID NOT NULL REFERENCES subjects(id) ON DELETE CASCADE,
|
|
period_id UUID NOT NULL REFERENCES academic_periods(id) ON DELETE CASCADE,
|
|
average DECIMAL(4,2),
|
|
grade_count INT NOT NULL DEFAULT 0,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (student_id, subject_id, period_id)
|
|
)
|
|
SQL);
|
|
|
|
$this->addSql(<<<'SQL'
|
|
CREATE INDEX idx_student_averages_tenant ON student_averages(tenant_id)
|
|
SQL);
|
|
|
|
$this->addSql(<<<'SQL'
|
|
CREATE INDEX idx_student_averages_student ON student_averages(student_id)
|
|
SQL);
|
|
|
|
$this->addSql(<<<'SQL'
|
|
CREATE INDEX idx_student_averages_subject ON student_averages(subject_id)
|
|
SQL);
|
|
|
|
$this->addSql(<<<'SQL'
|
|
CREATE TABLE student_general_averages (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
student_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
period_id UUID NOT NULL REFERENCES academic_periods(id) ON DELETE CASCADE,
|
|
average DECIMAL(4,2),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (student_id, period_id)
|
|
)
|
|
SQL);
|
|
|
|
$this->addSql(<<<'SQL'
|
|
CREATE INDEX idx_student_general_averages_tenant ON student_general_averages(tenant_id)
|
|
SQL);
|
|
|
|
$this->addSql(<<<'SQL'
|
|
CREATE INDEX idx_student_general_averages_student ON student_general_averages(student_id)
|
|
SQL);
|
|
|
|
$this->addSql(<<<'SQL'
|
|
CREATE TABLE evaluation_statistics (
|
|
evaluation_id UUID PRIMARY KEY REFERENCES evaluations(id) ON DELETE CASCADE,
|
|
average DECIMAL(5,2),
|
|
min_grade DECIMAL(5,2),
|
|
max_grade DECIMAL(5,2),
|
|
median_grade DECIMAL(5,2),
|
|
graded_count INT NOT NULL DEFAULT 0,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
)
|
|
SQL);
|
|
}
|
|
|
|
public function down(Schema $schema): void
|
|
{
|
|
$this->addSql('DROP TABLE IF EXISTS evaluation_statistics');
|
|
$this->addSql('DROP TABLE IF EXISTS student_general_averages');
|
|
$this->addSql('DROP TABLE IF EXISTS student_averages');
|
|
}
|
|
}
|