<?php
// This file is part of Moodle - http://moodle.org/
//
// Moodle is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.

/**
 * CAPP (Conseil d'apprentissage) session CRUD, per-student grid with a
 * single appréciation globale, previous-session niveau recovery, and
 * consultative (never authoritative) rattachement of évaluations/stages.
 *
 * @package    local_kid
 */

namespace local_kid\local;

use local_kid\event\csv_imported;
use local_kid\event\result_saved;

defined('MOODLE_INTERNAL') || die();

class capp_repository {

    public static function get_sessions_for_promotion(int $promotionid): array {
        global $DB;
        return $DB->get_records('local_kid_capp_session', ['promotionid' => $promotionid], 'sortorder ASC, id ASC');
    }

    public static function get_active_sessions_for_promotion(int $promotionid): array {
        global $DB;
        return $DB->get_records('local_kid_capp_session', ['promotionid' => $promotionid, 'active' => 1], 'sortorder ASC, id ASC');
    }

    public static function get_session(int $id): ?\stdClass {
        global $DB;
        return $DB->get_record('local_kid_capp_session', ['id' => $id], '*', IGNORE_MISSING) ?: null;
    }

    public static function create_session(int $promotionid, string $code, string $libelle, int $userid): int {
        global $DB;

        self::validate($code, $libelle);
        if ($DB->record_exists('local_kid_capp_session', ['promotionid' => $promotionid, 'code' => $code])) {
            throw new \moodle_exception('error_capp_code_duplicate', 'local_kid');
        }

        $maxsortorder = (int) $DB->get_field_sql(
            'SELECT MAX(sortorder) FROM {local_kid_capp_session} WHERE promotionid = ?',
            [$promotionid]
        );

        $now = time();
        return $DB->insert_record('local_kid_capp_session', (object) [
            'promotionid' => $promotionid,
            'code' => $code,
            'libelle' => $libelle,
            'sortorder' => $maxsortorder + 1,
            'active' => 1,
            'timecreated' => $now,
            'timemodified' => $now,
            'usermodified' => $userid,
        ]);
    }

    public static function update_session(int $id, string $code, string $libelle, bool $active, int $userid): void {
        global $DB;

        self::validate($code, $libelle);
        $session = self::get_session($id);
        if ($DB->record_exists_select(
            'local_kid_capp_session',
            'promotionid = :promotionid AND code = :code AND id <> :id',
            ['promotionid' => $session->promotionid, 'code' => $code, 'id' => $id]
        )) {
            throw new \moodle_exception('error_capp_code_duplicate', 'local_kid');
        }

        $DB->update_record('local_kid_capp_session', (object) [
            'id' => $id,
            'code' => $code,
            'libelle' => $libelle,
            'active' => $active ? 1 : 0,
            'timemodified' => time(),
            'usermodified' => $userid,
        ]);
    }

    private static function validate(string $code, string $libelle): void {
        if (trim($code) === '' || trim($libelle) === '') {
            throw new \moodle_exception('error_capp_fields_required', 'local_kid');
        }
    }

    /**
     * Physically removes the CAPP session and everything scoped to it:
     * every student entry and its results, the consultative rattachements
     * pointing at this session, its ADESI export log rows, and matching
     * publication log rows (orphaned otherwise once their entryid stops
     * existing).
     */
    public static function delete_session(int $id): void {
        global $DB;
        $transaction = $DB->start_delegated_transaction();

        $entryids = $DB->get_fieldset_select('local_kid_capp_entry', 'id', 'sessionid = ?', [$id]);
        if ($entryids) {
            [$insql, $inparams] = $DB->get_in_or_equal($entryids);
            $DB->delete_records_select('local_kid_capp_result', "entryid $insql", $inparams);
            $DB->delete_records_select('local_kid_publication_log', "domain = 'capp' AND entryid $insql", $inparams);
        }
        $DB->delete_records('local_kid_capp_entry', ['sessionid' => $id]);
        $DB->delete_records('local_kid_capp_source', ['sessionid' => $id]);
        $DB->delete_records('local_kid_adesi_export_log', ['sessionid' => $id]);
        $DB->delete_records('local_kid_capp_session', ['id' => $id]);

        $transaction->allow_commit();
    }

    /**
     * @return \stdClass|null the CAPP session immediately before this one in
     *         the promotion's ordered session list, or null if this is the
     *         first session (CAPP1) — "récupérer niveau précédent" is masked
     *         entirely in that case.
     */
    public static function get_previous_session(int $sessionid): ?\stdClass {
        $session = self::get_session($sessionid);
        $ordered = array_values(self::get_active_sessions_for_promotion($session->promotionid));
        foreach ($ordered as $index => $s) {
            if ($s->id == $sessionid && $index > 0) {
                return $ordered[$index - 1];
            }
        }
        return null;
    }

    /**
     * Short display name for a CAPP session, derived from its actual
     * libelle rather than a hardcoded 1..N sequence — e.g. "Capp 2 - 24-26"
     * → "CAPP 2", "CAPP1 - DIPFD" → "CAPP1": the part before the first
     * " - <promotion suffix>" separator, uppercased. Falls back to the
     * whole libelle uppercased if there is no such separator. Used by the
     * "Niveau CAPP N : …" prior-sessions lines on the CAPP grid
     * (capp_grid_form.php), never by capp_repository::code (free admin
     * text, too inconsistent across real sessions to serve as a display
     * name — e.g. "CAPP 2"/"CAPP1"/"Capp2" all coexist in production).
     */
    public static function short_name(\stdClass $session): string {
        $libelle = format_string($session->libelle);
        [$name] = explode(' - ', $libelle, 2);
        return strtoupper(trim($name));
    }

    /**
     * Central "niveau CAPP final" history for one student, across every
     * active CAPP session of the promotion, ordered by the sequence
     * (sortorder ASC — CAPP1 → CAPP4). Reused by the "Niveau CAPP N"
     * prior-sessions lines on the grid (classes/form/capp_grid_form.php,
     * via pages/capp.php). Previously also fed the dashboard "Évolution"
     * cross-tab, removed 2026-08-13 with the other redundant dashboard
     * sub-tabs (see Comparateur de bilans).
     *
     * @param int[] $competencyids
     * @return array{sessions: \stdClass[], niveaux: array<int, array<int, ?\stdClass>>}
     *         sessions: ordered active sessions of the promotion.
     *         niveaux: competencyid => sessionid => niveau record reached at
     *         that session (local_kid_niveau row), or null if there was no
     *         entry yet or no niveau saisi at that session.
     */
    public static function get_niveau_history(int $promotionid, int $studentid, array $competencyids): array {
        $sessions = array_values(self::get_active_sessions_for_promotion($promotionid));

        $niveauxbyid = [];
        foreach (niveau_repository::get_all() as $niveau) {
            $niveauxbyid[$niveau->id] = $niveau;
        }

        $niveaux = array_fill_keys($competencyids, []);
        foreach ($sessions as $session) {
            $entry = self::get_entry_for_student((int) $session->id, $studentid);
            $results = $entry ? self::get_results((int) $entry->id) : [];
            foreach ($competencyids as $competencyid) {
                $result = $results[$competencyid] ?? null;
                $niveauid = $result->niveauid ?? null;
                $niveaux[$competencyid][$session->id] = ($niveauid && isset($niveauxbyid[$niveauid])) ? $niveauxbyid[$niveauid] : null;
            }
        }

        return ['sessions' => $sessions, 'niveaux' => $niveaux];
    }

    public static function ensure_entries(int $sessionid, int $userid): void {
        global $DB;

        $session = self::get_session($sessionid);
        $students = promotion_repository::get_active_students((int) $session->promotionid);
        $existing = $DB->get_records_menu('local_kid_capp_entry', ['sessionid' => $sessionid], '', 'studentid, id');

        $now = time();
        foreach ($students as $student) {
            if (isset($existing[$student->studentid])) {
                continue;
            }
            $DB->insert_record('local_kid_capp_entry', (object) [
                'sessionid' => $sessionid,
                'studentid' => $student->studentid,
                'workflowstatus' => workflow_service::STATUS_SAISIE,
                'published' => 0,
                'publishedon' => null,
                'publishedby' => null,
                'appreciationglobale' => null,
                'timecreated' => $now,
                'timemodified' => $now,
            ]);
        }
    }

    public static function get_entry(int $id): ?\stdClass {
        global $DB;
        return $DB->get_record('local_kid_capp_entry', ['id' => $id], '*', IGNORE_MISSING) ?: null;
    }

    public static function get_entry_for_student(int $sessionid, int $studentid): ?\stdClass {
        global $DB;
        return $DB->get_record('local_kid_capp_entry', ['sessionid' => $sessionid, 'studentid' => $studentid], '*', IGNORE_MISSING) ?: null;
    }

    /**
     * @return \stdClass[] every entry for this session, keyed by studentid,
     *         joined with the student's identity for display purposes.
     */
    public static function get_entries_for_session(int $sessionid): array {
        global $DB;
        $sql = "SELECT e.*, u.id AS userid, u.firstname, u.lastname, u.email
                  FROM {local_kid_capp_entry} e
                  JOIN {local_kid_student} s ON s.id = e.studentid
                  JOIN {user} u ON u.id = s.userid
                 WHERE e.sessionid = :sessionid
                 ORDER BY u.lastname, u.firstname";
        return $DB->get_records_sql($sql, ['sessionid' => $sessionid]);
    }

    /**
     * @return \stdClass[] keyed by competencyid, for one entry.
     */
    public static function get_results(int $entryid): array {
        global $DB;
        return $DB->get_records('local_kid_capp_result', ['entryid' => $entryid], '', 'competencyid, id, niveauid, commentaire');
    }

    /**
     * Full replace — the grid's "Enregistrer" button. $appreciationglobale
     * is the single CAPP-level appréciation (local_kid_capp_entry.
     * appreciationglobale), entirely separate from the per-competency
     * commentaire written into local_kid_capp_result by write_results()
     * below — never merged, never overwritten by CSV import of the
     * compétences file (upsert_results() never touches it; only
     * upsert_appreciation(), used by the separate appréciations file,
     * touches it).
     *
     * @param array<int, array{niveauid: ?int, commentaire: ?string}> $results keyed by competencyid.
     */
    public static function save_results(int $entryid, array $results, ?string $appreciationglobale, int $userid): void {
        global $DB;

        $diff = self::write_results($entryid, $results, true);
        $DB->update_record('local_kid_capp_entry', (object) [
            'id' => $entryid,
            'appreciationglobale' => $appreciationglobale,
            'timemodified' => time(),
        ]);
        self::log_saved($entryid, $userid, $diff);
    }

    /**
     * Only touches the competencies present in $results, and never touches
     * appreciationglobale — used by the CSV import of the compétences file
     * so a second import (or a follow-up appréciations-only import) never
     * erases a competency or appréciation entered elsewhere. Not the same
     * semantics as save_results() (full replace) — deliberate, same pattern
     * as stage_repository::upsert_results().
     */
    public static function upsert_results(int $entryid, array $results, int $userid): void {
        $diff = self::write_results($entryid, $results, false);
        self::log_saved($entryid, $userid, $diff);
    }

    /**
     * Only touches appreciationglobale — used by the CSV import of the
     * appréciations file so it never touches local_kid_capp_result.
     */
    public static function upsert_appreciation(int $entryid, ?string $appreciationglobale, int $userid): void {
        global $DB;
        $DB->update_record('local_kid_capp_entry', (object) [
            'id' => $entryid,
            'appreciationglobale' => $appreciationglobale,
            'timemodified' => time(),
        ]);
        self::log_saved($entryid, $userid, []);
    }

    /**
     * @return array competencyid => ['old' => ['niveauid' => ?int, 'commentaire' => string], 'new' => [...]],
     *   only for competencies whose stored niveauid/commentaire actually changed — for the Journal diff
     *   (see journal_service::observe()). Never influences what gets written, only what gets reported.
     */
    private static function write_results(int $entryid, array $results, bool $fullreplace): array {
        global $DB;
        $now = time();
        $diff = [];

        // Snapshot pre-existing rows before a $fullreplace delete wipes them —
        // otherwise every competency would look "new" (old=null) in the diff
        // even when its value didn't actually change.
        $existingmap = [];
        foreach ($DB->get_records('local_kid_capp_result', ['entryid' => $entryid]) as $row) {
            $existingmap[(int) $row->competencyid] = $row;
        }

        if ($fullreplace) {
            $DB->delete_records('local_kid_capp_result', ['entryid' => $entryid]);
        }
        foreach ($results as $competencyid => $result) {
            $competencyid = (int) $competencyid;
            $prior = $existingmap[$competencyid] ?? null;
            $existing = $fullreplace ? null
                : $DB->get_record('local_kid_capp_result', ['entryid' => $entryid, 'competencyid' => $competencyid], '*', IGNORE_MISSING);

            $newniveauid = $result['niveauid'] ? (int) $result['niveauid'] : null;
            $newcommentairefordiff = (string) ($result['commentaire'] ?? '');
            // $DB->get_record() returns every column as a string (Moodle DB
            // layer convention, confirmed on pgsql) — cast niveauid back to
            // int before comparing, otherwise "2" !== 2 makes every
            // resubmission of an unchanged value look like a real change.
            $oldniveauid = $prior && $prior->niveauid ? (int) $prior->niveauid : null;
            $oldcommentaire = $prior ? (string) ($prior->commentaire ?? '') : '';
            if ($oldniveauid !== $newniveauid || $oldcommentaire !== $newcommentairefordiff) {
                $diff[$competencyid] = [
                    'old' => ['niveauid' => $oldniveauid, 'commentaire' => $oldcommentaire],
                    'new' => ['niveauid' => $newniveauid, 'commentaire' => $newcommentairefordiff],
                ];
            }

            $record = (object) [
                'entryid' => $entryid,
                'competencyid' => $competencyid,
                'niveauid' => $newniveauid,
                'commentaire' => $result['commentaire'] ?? null,
                'timemodified' => $now,
            ];
            if ($existing) {
                $record->id = $existing->id;
                $DB->update_record('local_kid_capp_result', $record);
            } else {
                $record->timecreated = $now;
                $DB->insert_record('local_kid_capp_result', $record);
            }
        }
        $DB->update_record('local_kid_capp_entry', (object) ['id' => $entryid, 'timemodified' => $now]);
        return $diff;
    }

    private static function log_saved(int $entryid, int $userid, array $diff): void {
        $other = ['domain' => 'capp', 'entryid' => $entryid];
        if ($diff) {
            $other['diff'] = $diff;
        }
        result_saved::create([
            'context' => \context_system::instance(),
            'userid' => $userid,
            'other' => $other,
        ])->trigger();
    }

    /**
     * Copies only the niveauid (never the commentaire) from the previous
     * CAPP session's results for this student into the current session's
     * results — upsert-only, remains fully editable afterwards, never
     * touches the source session. No-op if there is no previous session
     * (CAPP1) or the student has no previous entry yet.
     *
     * @return int number of niveaux copied.
     */
    public static function copy_previous_niveaux(int $sessionid, int $studentid): int {
        global $DB;

        $previous = self::get_previous_session($sessionid);
        if (!$previous) {
            return 0;
        }
        $previousentry = self::get_entry_for_student((int) $previous->id, $studentid);
        if (!$previousentry) {
            return 0;
        }
        $previousresults = self::get_results((int) $previousentry->id);
        if (empty($previousresults)) {
            return 0;
        }

        $currententry = self::get_entry_for_student($sessionid, $studentid);
        if (!$currententry) {
            return 0;
        }

        $now = time();
        $copied = 0;
        foreach ($previousresults as $competencyid => $previousresult) {
            if (empty($previousresult->niveauid)) {
                continue;
            }
            $existing = $DB->get_record('local_kid_capp_result', ['entryid' => $currententry->id, 'competencyid' => $competencyid], '*', IGNORE_MISSING);
            if ($existing) {
                $DB->update_record('local_kid_capp_result', (object) [
                    'id' => $existing->id,
                    'niveauid' => $previousresult->niveauid,
                    'timemodified' => $now,
                ]);
            } else {
                $DB->insert_record('local_kid_capp_result', (object) [
                    'entryid' => $currententry->id,
                    'competencyid' => $competencyid,
                    'niveauid' => $previousresult->niveauid,
                    'commentaire' => null,
                    'timecreated' => $now,
                    'timemodified' => $now,
                ]);
            }
            $copied++;
        }

        return $copied;
    }

    /**
     * @return array{studentcount:int, totalcopied:int} recovers previous
     *         niveaux for every active student of the promotion.
     */
    public static function copy_previous_niveaux_bulk(int $sessionid, int $userid): array {
        $session = self::get_session($sessionid);
        self::ensure_entries($sessionid, $userid);
        $students = promotion_repository::get_active_students((int) $session->promotionid);

        $studentcount = 0;
        $totalcopied = 0;
        foreach ($students as $student) {
            $copied = self::copy_previous_niveaux($sessionid, (int) $student->studentid);
            if ($copied > 0) {
                $studentcount++;
                $totalcopied += $copied;
            }
        }
        return ['studentcount' => $studentcount, 'totalcopied' => $totalcopied];
    }

    // ─── Rattachement (consultative only) ──────────────────────────────

    /**
     * Attaches an évaluation/stage to a CAPP session for consultative
     * display only — never copies its niveaux into local_kid_capp_result.
     * A source can only be attached to one session at a time: attaching it
     * elsewhere first detaches the previous attachment.
     */
    public static function attach_source(int $sessionid, string $sourcetype, int $sourceid, int $userid): void {
        global $DB;

        $session = self::get_session($sessionid);
        $sql = "sourcetype = :sourcetype AND sourceid = :sourceid
                AND sessionid IN (SELECT id FROM {local_kid_capp_session} WHERE promotionid = :promotionid)";
        $DB->delete_records_select('local_kid_capp_source', $sql, [
            'sourcetype' => $sourcetype, 'sourceid' => $sourceid, 'promotionid' => $session->promotionid,
        ]);

        $DB->insert_record('local_kid_capp_source', (object) [
            'sessionid' => $sessionid,
            'sourcetype' => $sourcetype,
            'sourceid' => $sourceid,
            'timecreated' => time(),
            'usermodified' => $userid,
        ]);
    }

    public static function detach_source(string $sourcetype, int $sourceid, int $promotionid): void {
        global $DB;
        $sql = "sourcetype = :sourcetype AND sourceid = :sourceid
                AND sessionid IN (SELECT id FROM {local_kid_capp_session} WHERE promotionid = :promotionid)";
        $DB->delete_records_select('local_kid_capp_source', $sql, [
            'sourcetype' => $sourcetype, 'sourceid' => $sourceid, 'promotionid' => $promotionid,
        ]);
    }

    /**
     * @return int|null the session id a source is currently attached to
     *         within this promotion, or null if unattached.
     */
    public static function get_source_assignment(string $sourcetype, int $sourceid, int $promotionid): ?int {
        global $DB;
        $sql = "SELECT cs.sessionid
                  FROM {local_kid_capp_source} cs
                  JOIN {local_kid_capp_session} s ON s.id = cs.sessionid
                 WHERE cs.sourcetype = :sourcetype AND cs.sourceid = :sourceid AND s.promotionid = :promotionid";
        $sessionid = $DB->get_field_sql($sql, ['sourcetype' => $sourcetype, 'sourceid' => $sourceid, 'promotionid' => $promotionid]);
        return $sessionid ? (int) $sessionid : null;
    }

    /**
     * @return bool true if any rattachement already exists for this
     *        promotion's sessions — used to decide whether the default rule
     *        needs an overwrite confirmation.
     */
    public static function has_any_sources(int $promotionid): bool {
        global $DB;
        $sessionids = array_keys(self::get_sessions_for_promotion($promotionid));
        if (empty($sessionids)) {
            return false;
        }
        [$insql, $params] = $DB->get_in_or_equal($sessionids);
        return $DB->record_exists_select('local_kid_capp_source', "sessionid $insql", $params);
    }

    // ─── Consultative data for the CAPP grid ───────────────────────────

    /**
     * @return array{count:int, items: array<array{label:string, commentaire:?string}>}
     *         évaluation niveaux/commentaires for one student/competency,
     *         across every évaluation attached to this session — read-only
     *         reference material, never copied automatically into
     *         local_kid_capp_result. Also surfaces the Moodle assignment
     *         feedback (single read-only value per (évaluation, élève),
     *         local_kid_eval_entry.feedbacktext) as its own item whenever a
     *         moodle_devoir évaluation attached to this session is linked to
     *         this competency (local_kid_eval_competency) — independently of
     *         whether a manual niveau/commentaire was ever entered for it,
     *         and never merged into the manual commentaire item above.
     */
    public static function get_consultative_eval_data(int $sessionid, int $studentid, int $competencyid): array {
        global $DB;
        $sql = "SELECT er.commentaire, n.libelle AS niveaulibelle, e.titre
                  FROM {local_kid_capp_source} cs
                  JOIN {local_kid_eval} e ON e.id = cs.sourceid AND cs.sourcetype = 'eval'
                  JOIN {local_kid_eval_entry} ee ON ee.evalid = e.id AND ee.studentid = :studentid
                  JOIN {local_kid_eval_result} er ON er.entryid = ee.id AND er.competencyid = :competencyid
             LEFT JOIN {local_kid_niveau} n ON n.id = er.niveauid
                 WHERE cs.sessionid = :sessionid";
        $rows = $DB->get_records_sql($sql, ['sessionid' => $sessionid, 'studentid' => $studentid, 'competencyid' => $competencyid]);
        $items = [];
        foreach ($rows as $row) {
            $items[] = [
                'label' => ($row->niveaulibelle ?: get_string('niveau_none', 'local_kid')) . ' (' . format_string($row->titre) . ')',
                'commentaire' => $row->commentaire,
            ];
        }

        $feedbacksql = "SELECT e.id, e.titre, ee.feedbacktext
                           FROM {local_kid_capp_source} cs
                           JOIN {local_kid_eval} e ON e.id = cs.sourceid AND cs.sourcetype = 'eval' AND e.sourcetype = 'moodle_devoir'
                           JOIN {local_kid_eval_competency} ec ON ec.evalid = e.id AND ec.competencyid = :competencyid
                           JOIN {local_kid_eval_entry} ee ON ee.evalid = e.id AND ee.studentid = :studentid
                          WHERE cs.sessionid = :sessionid AND ee.feedbacktext IS NOT NULL";
        $feedbackrows = $DB->get_records_sql($feedbacksql, ['sessionid' => $sessionid, 'studentid' => $studentid, 'competencyid' => $competencyid]);
        foreach ($feedbackrows as $row) {
            if ($row->feedbacktext === '') {
                continue;
            }
            $items[] = [
                'label' => get_string('eval_feedback_heading', 'local_kid') . ' (' . format_string($row->titre) . ')',
                'commentaire' => $row->feedbacktext,
            ];
        }

        return ['count' => count($items), 'items' => $items];
    }

    /**
     * Same as get_consultative_eval_data() but for stages attached to this
     * session.
     */
    public static function get_consultative_stage_data(int $sessionid, int $studentid, int $competencyid): array {
        global $DB;
        $sql = "SELECT sr.commentaire, n.libelle AS niveaulibelle, st.libelle AS stagelibelle
                  FROM {local_kid_capp_source} cs
                  JOIN {local_kid_stage} st ON st.id = cs.sourceid AND cs.sourcetype = 'stage'
                  JOIN {local_kid_stage_entry} se ON se.stageid = st.id AND se.studentid = :studentid
                  JOIN {local_kid_stage_result} sr ON sr.entryid = se.id AND sr.competencyid = :competencyid
             LEFT JOIN {local_kid_niveau} n ON n.id = sr.niveauid
                 WHERE cs.sessionid = :sessionid";
        $rows = $DB->get_records_sql($sql, ['sessionid' => $sessionid, 'studentid' => $studentid, 'competencyid' => $competencyid]);
        $items = [];
        foreach ($rows as $row) {
            $items[] = [
                'label' => ($row->niveaulibelle ?: get_string('niveau_none', 'local_kid')) . ' (' . format_string($row->stagelibelle) . ')',
                'commentaire' => $row->commentaire,
            ];
        }
        return ['count' => count($items), 'items' => $items];
    }

    /**
     * Pré-remplissage ponctuel du commentaire CAPP d'une compétence à partir
     * de ses commentaires sources (mêmes données que
     * get_consultative_eval_data()/get_consultative_stage_data(), Enseignements
     * + Stages confondus) : si le commentaire CAPP est encore vide et qu'il
     * existe exactement un commentaire source non vide, il est copié tel
     * quel (sans mention d'origine) dans local_kid_capp_result.commentaire.
     * No-op si le commentaire CAPP est déjà renseigné (jamais d'écrasement,
     * y compris lors d'une resynchronisation ultérieure de la source), ou
     * s'il y a zéro ou plusieurs commentaires sources. Ne touche jamais le
     * niveauid ni aucune table source — même principe que
     * copy_previous_niveaux() (upsert-only, reste librement éditable après
     * coup).
     */
    public static function seed_commentaire_from_sources(int $sessionid, int $studentid, int $competencyid, int $entryid): void {
        global $DB;

        $existing = $DB->get_record('local_kid_capp_result', ['entryid' => $entryid, 'competencyid' => $competencyid], '*', IGNORE_MISSING);
        if ($existing && trim((string) $existing->commentaire) !== '') {
            return;
        }

        $evaldata = self::get_consultative_eval_data($sessionid, $studentid, $competencyid);
        $stagedata = self::get_consultative_stage_data($sessionid, $studentid, $competencyid);
        $sourcecomments = [];
        foreach (array_merge($evaldata['items'], $stagedata['items']) as $item) {
            if (trim((string) ($item['commentaire'] ?? '')) !== '') {
                $sourcecomments[] = $item['commentaire'];
            }
        }

        if (count($sourcecomments) !== 1) {
            return;
        }

        $now = time();
        if ($existing) {
            $DB->update_record('local_kid_capp_result', (object) [
                'id' => $existing->id,
                'commentaire' => $sourcecomments[0],
                'timemodified' => $now,
            ]);
        } else {
            $DB->insert_record('local_kid_capp_result', (object) [
                'entryid' => $entryid,
                'competencyid' => $competencyid,
                'niveauid' => null,
                'commentaire' => $sourcecomments[0],
                'timecreated' => $now,
                'timemodified' => $now,
            ]);
        }
    }

    /**
     * Applique seed_commentaire_from_sources() à chaque compétence active
     * fournie — appelée à chaque affichage de la grille CAPP d'un élève
     * (pages/capp.php et son équivalent AJAX), no-op pour toute compétence
     * déjà seedée ou déjà commentée manuellement.
     *
     * @param int[] $competencyids
     */
    public static function seed_commentaires_from_sources(int $sessionid, int $studentid, int $entryid, array $competencyids): void {
        foreach ($competencyids as $competencyid) {
            self::seed_commentaire_from_sources($sessionid, $studentid, (int) $competencyid, $entryid);
        }
    }

    // ─── Import/export CSV (deux fichiers : compétences, appréciations) ──

    /**
     * Builds the "compétences" round-trip template — one row per (student ×
     * competency) pair of the session's promotion, niveau/commentaire left
     * blank — the exact same gabarit/format as Évaluations/Stages
     * (csv_import_service::build_template()), scoped to the promotion's
     * active competencies (competency_repository::get_active_for_promotion(),
     * same scope the CAPP grid itself uses).
     */
    public static function build_csv_template_competences(\stdClass $session): string {
        $students = promotion_repository::get_active_students((int) $session->promotionid);
        $competencies = competency_repository::get_active_for_promotion((int) $session->promotionid);
        return csv_import_service::build_template($students, $competencies);
    }

    /**
     * Builds the "appréciations d'ensemble" round-trip template — one row
     * per student, appréciation left blank.
     */
    public static function build_csv_template_appreciations(\stdClass $session): string {
        $students = promotion_repository::get_active_students((int) $session->promotionid);
        return csv_import_service::build_template_appreciation($students);
    }

    /**
     * Imports the "compétences" file: same parsing/matching logic as
     * eval_repository::import_csv()/stage_repository::import_csv() (shared
     * csv_import_service, same CSV_COLUMNS/errors), targeting this CAPP
     * session's niveau CAPP final (local_kid_capp_result) — never the
     * sources' own niveaux. Never touches appreciationglobale
     * (upsert_results() only). A finalisé entry is left untouched and
     * reported as an error, so the import feeds "saisie" without
     * short-circuiting the workflow.
     *
     * @return array{added:int, errors:string[]}
     */
    public static function import_csv_competences(\stdClass $session, string $content, int $userid): array {
        $parsed = csv_import_service::parse($content);
        $competencies = competency_repository::get_active_for_promotion((int) $session->promotionid);
        $competenciesbylibelle = [];
        foreach ($competencies as $competency) {
            $competenciesbylibelle[strtolower(trim(format_string($competency->libelle)))] = $competency;
        }
        $niveaux = niveau_repository::get_all();
        $niveauxbylabel = [];
        foreach ($niveaux as $niveau) {
            $niveauxbylabel[strtolower($niveau->libelle)] = $niveau;
            $niveauxbylabel[strtolower($niveau->code)] = $niveau;
        }

        $entriesbystudentid = [];
        foreach (self::get_entries_for_session((int) $session->id) as $entry) {
            $entriesbystudentid[(int) $entry->studentid] = $entry;
        }

        $errors = [];
        $touchedentries = []; // entryid => [competencyid => [niveauid, commentaire]]

        foreach ($parsed['rows'] as $row) {
            $line = $row['_line'];
            $studentid = (int) ($row['code_eleve'] ?? 0);
            $competencelabel = strtolower(trim($row['competence'] ?? ''));
            $niveaulabel = strtolower(trim($row['niveau'] ?? ''));
            $commentaire = trim($row['commentaire'] ?? '');

            if (!$studentid || !isset($entriesbystudentid[$studentid])) {
                $errors[] = get_string('capp_import_error_student', 'local_kid', $line);
                continue;
            }
            if ($entriesbystudentid[$studentid]->workflowstatus === workflow_service::STATUS_FINALISE) {
                $errors[] = get_string('capp_import_error_locked', 'local_kid', $line);
                continue;
            }
            if (!isset($competenciesbylibelle[$competencelabel])) {
                $errors[] = get_string('capp_import_error_competency', 'local_kid', $line);
                continue;
            }
            if ($niveaulabel !== '' && !isset($niveauxbylabel[$niveaulabel])) {
                $errors[] = get_string('capp_import_error_niveau', 'local_kid', $line);
                continue;
            }

            $entry = $entriesbystudentid[$studentid];
            $competency = $competenciesbylibelle[$competencelabel];
            $niveau = $niveauxbylabel[$niveaulabel] ?? null;

            $touchedentries[$entry->id][$competency->id] = [
                'niveauid' => $niveau ? $niveau->id : null,
                'commentaire' => $commentaire,
            ];
        }

        foreach ($touchedentries as $entryid => $results) {
            self::upsert_results((int) $entryid, $results, $userid);
        }

        csv_imported::create([
            'context' => \context_system::instance(),
            'userid' => $userid,
            'other' => [
                'domain' => 'capp', 'mode' => 'competences', 'promotionid' => (int) $session->promotionid, 'itemid' => (int) $session->id,
                'added' => count($touchedentries), 'errorcount' => count($errors),
            ],
        ])->trigger();

        return [
            'added' => count($touchedentries),
            'errors' => $errors,
        ];
    }

    /**
     * Imports the "appréciations d'ensemble" file — one row per student,
     * writes only local_kid_capp_entry.appreciationglobale
     * (upsert_appreciation()), never touches local_kid_capp_result: the two
     * files apply fully independently of each other. A finalisé entry is
     * left untouched and reported as an error, same rule as the compétences
     * file.
     *
     * @return array{added:int, errors:string[]}
     */
    public static function import_csv_appreciations(\stdClass $session, string $content, int $userid): array {
        $parsed = csv_import_service::parse($content);

        $entriesbystudentid = [];
        foreach (self::get_entries_for_session((int) $session->id) as $entry) {
            $entriesbystudentid[(int) $entry->studentid] = $entry;
        }

        $errors = [];
        $touchedentries = []; // entryid => appreciation text

        foreach ($parsed['rows'] as $row) {
            $line = $row['_line'];
            $studentid = (int) ($row['code_eleve'] ?? 0);
            $appreciation = trim($row['appreciation'] ?? '');

            if (!$studentid || !isset($entriesbystudentid[$studentid])) {
                $errors[] = get_string('capp_import_appreciation_error_student', 'local_kid', $line);
                continue;
            }
            if ($entriesbystudentid[$studentid]->workflowstatus === workflow_service::STATUS_FINALISE) {
                $errors[] = get_string('capp_import_appreciation_error_locked', 'local_kid', $line);
                continue;
            }

            $touchedentries[$entriesbystudentid[$studentid]->id] = $appreciation;
        }

        foreach ($touchedentries as $entryid => $appreciation) {
            self::upsert_appreciation((int) $entryid, $appreciation, $userid);
        }

        csv_imported::create([
            'context' => \context_system::instance(),
            'userid' => $userid,
            'other' => [
                'domain' => 'capp', 'mode' => 'appreciation', 'promotionid' => (int) $session->promotionid, 'itemid' => (int) $session->id,
                'added' => count($touchedentries), 'errorcount' => count($errors),
            ],
        ])->trigger();

        return [
            'added' => count($touchedentries),
            'errors' => $errors,
        ];
    }
}
