Decree 1290/2009 Compliance

CREATE TABLE academic_periods (
    id              UUID PRIMARY KEY,
    school_id       UUID NOT NULL,
    school_year_id  UUID NOT NULL,
    number          INT NOT NULL,     -- 1, 2, 3, 4
    name            TEXT NOT NULL,    -- "Primer Período", "Segundo Período"
    starts_on       DATE NOT NULL,
    ends_on         DATE NOT NULL,
    status          TEXT NOT NULL,    -- 'active', 'closed'
    UNIQUE (school_id, school_year_id, number)
);

{
  "grading_scale": {
    "min": 1.0,
    "max": 5.0,
    "passing_threshold": 3.0,
    "performance_levels": {
      "bajo": { "min": 1.0, "max": 2.9, "label": "Desempeño Bajo" },
      "basico": { "min": 3.0, "max": 3.9, "label": "Desempeño Básico" },
      "alto": { "min": 4.0, "max": 4.5, "label": "Desempeño Alto" },
      "superior": { "min": 4.6, "max": 5.0, "label": "Desempeño Superior" }
    },
    "rounding_precision": 1,
    "calculation_method": "weighted_average"  -- or "simple_average"
  }
}

CREATE TABLE evaluation_activities (
    id                   UUID PRIMARY KEY,
    school_id            UUID NOT NULL,
    course_offering_id   UUID NOT NULL,  -- Subject for specific class group
    teacher_assignment_id UUID NOT NULL,  -- Teacher at time of activity
    academic_period_id   UUID NOT NULL,
    title                TEXT NOT NULL,
    activity_type        TEXT NOT NULL,   -- 'quiz', 'exam', 'homework', 'project'
    weight               NUMERIC(5,2),    -- Percentage weight (0-100)
    is_gradable          BOOLEAN DEFAULT true,
    due_at               TIMESTAMPTZ,
    attachments          JSONB
);

CREATE TABLE activity_scores (
    id                     UUID PRIMARY KEY,
    school_id              UUID NOT NULL,
    evaluation_activity_id UUID NOT NULL,
    course_enrollment_id   UUID NOT NULL,  -- Student enrolled in course
    student_id             UUID NOT NULL,
    score                  NUMERIC(4,2),   -- e.g., 4.5 (on 1.0-5.0 scale)
    observations           TEXT,
    recorded_by            UUID NOT NULL,  -- Teacher user_id
    recorded_at            TIMESTAMPTZ DEFAULT now(),
    updated_at             TIMESTAMPTZ DEFAULT now(),
    UNIQUE (school_id, evaluation_activity_id, course_enrollment_id)
);

CREATE TABLE report_cards (
    id                 UUID PRIMARY KEY,
    school_id          UUID NOT NULL,
    enrollment_id      UUID NOT NULL,
    academic_period_id UUID NOT NULL,
    items              JSONB NOT NULL,  -- Subject grades and details
    overall_average    NUMERIC(4,2),
    status             TEXT NOT NULL,   -- 'draft', 'published'
    published_at       TIMESTAMPTZ,
    created_at         TIMESTAMPTZ DEFAULT now(),
    updated_at         TIMESTAMPTZ DEFAULT now(),
    UNIQUE (enrollment_id, academic_period_id)
);

{
  "items": [
    {
      "subject_catalog_id": "math-uuid",
      "subject_name": "Matemáticas",
      "course_offering_id": "course-uuid",
      "teacher_assignment_id": "assignment-uuid",
      "teacher_name": "Laura Martínez",
      "final_score": 4.3,
      "performance_level": "alto",
      "performance_label": "Desempeño Alto",
      "notes": "Excelente participación en clase. Mejorar resolución de problemas complejos.",
      "absences": 2,
      "tardiness": 1
    },
    {
      "subject_catalog_id": "spanish-uuid",
      "subject_name": "Lengua Castellana",
      "final_score": 4.7,
      "performance_level": "superior",
      "performance_label": "Desempeño Superior",
      "notes": "Sobresaliente comprensión lectora y producción textual."
    }
  ],
  "overall_average": 4.5,
  "general_observations": "Estudiante comprometido con desempeño consistente.",
  "promotion_status": "promoted",
  "failed_subjects_count": 0,
  "absences_percentage": 3.2
}

def calculate_period_grade(
    activities: list[Activity],
    scores: dict[activity_id, score]
) -> float:
    """
    Calculate period grade using weighted average.
    
    Args:
        activities: List of evaluation activities with weights
        scores: Student scores for each activity
    
    Returns:
        Final grade (e.g., 4.3 on 1.0-5.0 scale)
    """
    total_weight = sum(a.weight for a in activities if a.is_gradable)
    if total_weight == 0:
        return 0.0
    
    weighted_sum = sum(
        scores[a.id] * a.weight 
        for a in activities 
        if a.is_gradable and a.id in scores
    )
    
    return round(weighted_sum / total_weight, 1)  # Round to 1 decimal

def get_performance_level(score: float, scale_config: dict) -> str:
    """
    Map numeric score to performance level.
    """
    for level, config in scale_config['performance_levels'].items():
        if config['min'] <= score <= config['max']:
            return level
    return 'bajo'  # Default if no match

Informe de Promoción 2026
Grado: 10-A

Estudiantes promovidos: 32 (91.4%)
Estudiantes en recuperación: 2 (5.7%)
Estudiantes reprobados: 1 (2.9%)

Materias con mayor reprobación:
1. Matemáticas: 3 estudiantes
2. Física: 2 estudiantes
3. Química: 1 estudiante

# From permissions.py
ROLE_PERMISSIONS = {
    Role.TEACHER: {"write:grades", "read:own_students"},
    Role.COORDINATOR: {"read:grades", "write:convivencia", "config:academic"},
    Role.STUDENT: {"read:own_grades"},
    Role.GUARDIAN: {"read:own_child"},
}