Backend: Rezultati certificiranja

GitHub Issue: #253

Backend task for the results page (GET /btc-certifications/{certificationId}/results, POST /written-exam-scores) – check the Usmeni ispit average, the pass rule and the komisija overview against the API spec and DB structure, permissions, implement, and verify with a hand-calculated example.

Docs: Rezultati certificiranja Spec: GET /btc-certifications/{certificationId}/results (getBtcCertificationResults), POST /btc-certifications/{certificationId}/written-exam-scores (setBtcCertificationWrittenExamScores), BtcCertificationResults, BtcWrittenExamScore in api-docs/openapi.yaml DB: BtcCertificationGrades, BtcCertificationCandidates, BtcCertificationWrittenExamScores, BtcCertificationBoardMembers in api/database/create_schema.sql

1. Double check API spec and DB structure

• Each results table row maps to a field: Modul 5 – Pismeni ispit → writtenExam, Modul 5 – Usmeni ispit → oralExam, Prošao / Nije prošao → passed. null renders as “—”.
• Usmeni ispit average: the docs say “the average across all komisija members who have submitted their grades”. The spec averages the finalGrade of every member (including removed members) who has a final grade, even if the form isn’t Zaključeno. Decide whether only Zaključeno forms count, and whether removed members count.
• Pass rule: passed is null until both values exist; true when writtenExam ≥ 75 and oralExam rounded to one decimal (half away from zero) ≥ 3. The docs comment leaves the rounding open; the spec follows the docs recommendation (use the value shown). Confirm.
• Pismeni ispit format: the docs comment recommends 0–100 with whole numbers or one decimal, shown with “%”. Spec (multipleOf: 0.1) and DB (BtcCertificationWrittenExamScores.Score DECIMAL(4,1), CHECK 0–100) follow that. Confirm. One row per candidate that has a score (no row = no score); a separate table so its FK blocks removing a candidate who has a score.
• Pregled unosa članova komisije: one column per active member with isCompleted = Zaključeno for every candidate. Confirm removed members are left out.
• Header subtitle (brand · date · number of candidates · name) → certification in the response (candidatesCount included).
• CSV export is done in the frontend from this response. Confirm it has everything the CSV needs.
• Status rule: the spec allows entering Pismeni ispit in any status (the written exam results often arrive after the end date). Confirm.
• Roles and permissions: GET → btc-certifications.access (Trainer and BTC evaluator, all certificiranja). POST → btc-certifications.written-exam-edit (Trainer and BTC evaluator; see Roles). The permission is added in Backend: Adding new BTC certificiranje.

2. Implementation

• Roles and permissions: GET requires btc-certifications.access; POST requires btc-certifications.written-exam-edit → 403.
• DB: one query for all grades of the certificiranje (all members, removed included), then calculate in code with the shared helper from Backend: Form – Modul 5 – Usmeni ispit: per member and candidate finalGrade → average per candidate → passed.
• Komisija overview: active members only, isCompleted from the same helper for every candidate.
• POST: update only the listed candidates (null clears the score); each candidateId must belong to the certificiranje (404); the same candidate twice → 400; score 0–100 with at most one decimal (400). Return the recalculated results. One MERGE on BtcCertificationWrittenExamScores, no transaction: a value inserts or updates the row, null deletes it. FK error 547 (candidate removed in the meantime) → 404; unique violation (saved twice at once) → retry once.
• Unknown / deleted certificiranje → 404. Values are returned unrounded.

3. Verify implementation against spec

• Shapes and status codes match both operations.
• Results checked against a hand-calculated example: 3 members (one removed after grading), 3 candidates, one member with a partial form, written exam 74.9 / 75 / none. Include an oralExam of 2.96 (shown 3,0 → passes with ≥ 75 %) and 2.94 (fails).
• A candidate with no grades → oralExam and passed are null.
• BTC evaluator → GET 200, POST 200. A user without btc-certifications.written-exam-edit → POST 403. Score 100.5 or 75.25 → 400.

Done when

The results page and CSV can be built from the API, the trainer can enter Pismeni ispit, and the numbers and Prošao / Nije prošao match the hand-calculated example.