Backend: Editing existing BTC certificiranje

GitHub Issue: #251

Backend task for updating a BTC certificiranje (POST /btc-certifications/{certificationId}) – check the status rule, candidate and komisija matching, soft-deleted members and the "candidate has grades" rule against the API spec and DB structure, implement, and verify against the spec.

Docs: Editing existing BTC certificiranje Spec: POST /btc-certifications/{certificationId} (updateBtcCertification), BtcCertificationNewRequest, BtcCertificationConflict in api-docs/openapi.yaml DB: BtcCertifications, BtcCertificationCandidates, BtcCertificationWrittenExamScores, BtcCertificationBoardMembers, BtcCertificationGrades in api/database/create_schema.sql

The form is prefilled from GET /btc-certifications/{certificationId} (Backend: BTC certificiranje detail).

1. Double check API spec and DB structure

• Same fields and validation as create. Reuse the validation from Backend: Adding new BTC certificiranje.
• Prefill: the detail response has the candidates (with partnerId, hasGrades) and the active komisija (userId, fullName). The dropdown label (name and e-mail) comes from the users lookup. Check a komisija member whose user was deleted can still be shown (fall back to fullName).
• Rule: only active / upcoming can be edited → 409 certification_past.
• Changing dates into the past (decided: not allowed): endDate before today (Europe/Zagreb) → 400 validation_error. Check the spec description of updateBtcCertification says so. startDate may be in the past (an active certificiranje).
• Candidate with grades: “any grades” = an oral exam grade from any member, including removed members, or a written exam score → 409 candidate_has_grades. Confirm the written exam score counts.
• Komisija: members left out are soft-deleted (DeletedAt); their grades stay and still count in the results (Backend: Rezultati certificiranja). Re-adding a removed member restores the same row (same Id, DeletedAt cleared), so their grades come back. Confirm, and decide whether FullName is refreshed on restore.
• Candidates are matched by id. An id that isn’t a candidate of this certificiranje → 404. A new candidate gets Modul 5 – Usmeni ispit automatically (fixed in code, no rows).
• CTA label (“Uredi certificiranje” vs “SPREMI CERTIFICIRANJE”) is frontend only.
• Roles and permissions: btc-certifications.edit, Trainer only.
• DB: UQ_BtcCertificationBoardMembers_User (CertificationId, UserId) keeps one row per user. It’s deliberately not filtered on DeletedAt: a re-added member must restore the soft-deleted row, and a filtered index would allow a second one.
• DB: “candidate has grades” is enforced by the FKs from BtcCertificationGrades and BtcCertificationWrittenExamScores: deleting such a candidate fails with 547. The maximums are capped by Slot (soft-deleted members have Slot NULL).

2. Implementation

• Roles and permissions: require btc-certifications.edit → 403.
• DB: in one transaction (several writes), in this order:
  • check status (past → 409)
  • delete removed candidates with one DELETE … WHERE CertificationId = @id AND Id NOT IN (…); no check first: FK error 547 → roll back, 409 candidate_has_grades
  • update BtcCertifications; update existing candidates; insert new ones with the lowest free Slot
  • soft-delete removed members (DeletedAt, Slot NULL) first, then restore re-added ones and insert new ones (copy FullName from the users source), each with the lowest free Slot
  • commit only when every statement succeeds
  • unique violation (2601 / 2627) on Slot or UserId (another edit at the same time) → roll back and run the whole edit again once, in a new transaction; same failure again → 500
• Validation → 400 (including endDate before today), conflicts → 409 with the error code, unknown / deleted id → 404. Nothing is saved when any check fails.
• Response 200 with the recalculated BtcCertification.

3. Verify implementation against spec

• Route, request and response match updateBtcCertification; 409 codes match BtcCertificationConflict.
• endDate yesterday → 400; today → saved and still active.
• Past certificiranje → 409 certification_past. Removing a candidate graded by a removed member → 409 candidate_has_grades.
• Remove a member who graded, then re-add them → same row, grades visible again in their form.
• Adding a candidate → listed on the detail page with Usmeni ispit Nije zaključeno. BTC evaluator → 403.

Done when

A trainer can edit an active or upcoming certificiranje, and no grades are lost when candidates or komisija members change.