<?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.

namespace local_kid\local;

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

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

class stage_repository {

    public static function get_for_promotion(int $promotionid): array {
        global $DB;
        return $DB->get_records('local_kid_stage', ['promotionid' => $promotionid], 'timecreated DESC');
    }

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

    public static function create(int $promotionid, string $libelle, ?int $datedebut, ?int $datefin, string $scope, array $competencyids, int $userid): int {
        global $DB;
        $now = time();
        $id = $DB->insert_record('local_kid_stage', (object) [
            'promotionid' => $promotionid,
            'libelle' => $libelle,
            'datedebut' => $datedebut,
            'datefin' => $datefin,
            'competencyscope' => $scope,
            'active' => 1,
            'timecreated' => $now,
            'timemodified' => $now,
            'usermodified' => $userid,
        ]);
        if ($scope === 'custom') {
            self::set_scope_competencies($id, $competencyids);
        }
        self::ensure_entries($id, $promotionid, $userid);
        return $id;
    }

    public static function update(int $id, string $libelle, ?int $datedebut, ?int $datefin, string $scope, array $competencyids, int $userid): void {
        global $DB;
        $DB->update_record('local_kid_stage', (object) [
            'id' => $id,
            'libelle' => $libelle,
            'datedebut' => $datedebut,
            'datefin' => $datefin,
            'competencyscope' => $scope,
            'timemodified' => time(),
            'usermodified' => $userid,
        ]);
        if ($scope === 'custom') {
            self::set_scope_competencies($id, $competencyids);
        } else {
            $DB->delete_records('local_kid_stage_competency', ['stageid' => $id]);
        }
    }

    /** Archive, never a physical delete — existing entries/results/docs stay intact. */
    public static function toggle_active(int $id, int $userid): void {
        global $DB;
        $stage = self::get($id);
        if (!$stage) {
            return;
        }
        $DB->update_record('local_kid_stage', (object) [
            'id' => $id,
            'active' => $stage->active ? 0 : 1,
            'timemodified' => time(),
            'usermodified' => $userid,
        ]);
    }

    /**
     * Physically removes the stage and everything scoped to it: uploaded
     * documents (DB index + the actual Moodle files), the custom
     * competency scope, every student entry and its results, any CAPP
     * consultative attachment pointing at it, and matching publication log
     * rows (orphaned otherwise once their entryid stops existing).
     */
    public static function delete(int $id): void {
        global $DB;
        $transaction = $DB->start_delegated_transaction();

        $entryids = $DB->get_fieldset_select('local_kid_stage_entry', 'id', 'stageid = ?', [$id]);
        foreach ($entryids as $entryid) {
            foreach (self::list_docs((int) $entryid) as $doc) {
                self::delete_doc((int) $doc->id);
            }
        }
        if ($entryids) {
            [$insql, $inparams] = $DB->get_in_or_equal($entryids);
            $DB->delete_records_select('local_kid_stage_result', "entryid $insql", $inparams);
            $DB->delete_records_select('local_kid_publication_log', "domain = 'stage' AND entryid $insql", $inparams);
        }
        $DB->delete_records('local_kid_stage_entry', ['stageid' => $id]);
        $DB->delete_records('local_kid_stage_competency', ['stageid' => $id]);
        $DB->delete_records('local_kid_capp_source', ['sourcetype' => 'stage', 'sourceid' => $id]);
        $DB->delete_records('local_kid_stage', ['id' => $id]);

        $transaction->allow_commit();
    }

    private static function set_scope_competencies(int $stageid, array $competencyids): void {
        global $DB;
        $DB->delete_records('local_kid_stage_competency', ['stageid' => $stageid]);
        foreach (array_unique($competencyids) as $competencyid) {
            $DB->insert_record('local_kid_stage_competency', (object) ['stageid' => $stageid, 'competencyid' => (int) $competencyid]);
        }
    }

    public static function get_scope_competencies(\stdClass $stage): array {
        global $DB;
        if ($stage->competencyscope === 'all') {
            return competency_repository::get_active_for_promotion((int) $stage->promotionid);
        }
        $sql = "SELECT c.* FROM {local_kid_competency} c
                  JOIN {local_kid_stage_competency} sc ON sc.competencyid = c.id
                 WHERE sc.stageid = :stageid ORDER BY c.sortorder ASC";
        return $DB->get_records_sql($sql, ['stageid' => $stage->id]);
    }

    public static function ensure_entries(int $stageid, int $promotionid, int $userid): void {
        global $DB;
        $students = promotion_repository::get_active_students($promotionid);
        $existing = $DB->get_records_menu('local_kid_stage_entry', ['stageid' => $stageid], '', 'studentid, id');
        $now = time();
        foreach ($students as $student) {
            if (isset($existing[$student->studentid])) {
                continue;
            }
            $DB->insert_record('local_kid_stage_entry', (object) [
                'stageid' => $stageid,
                'studentid' => $student->studentid,
                'workflowstatus' => workflow_service::STATUS_SAISIE,
                'published' => 0,
                'publishedon' => null,
                'publishedby' => null,
                'commentaireglobal' => null,
                'timecreated' => $now,
                'timemodified' => $now,
            ]);
        }
    }

    public static function get_entries_for_stage(int $stageid): array {
        global $DB;
        $sql = "SELECT e.*, u.firstname, u.lastname, u.email, s.userid
                  FROM {local_kid_stage_entry} e
                  JOIN {local_kid_student} s ON s.id = e.studentid
                  JOIN {user} u ON u.id = s.userid
                 WHERE e.stageid = :stageid
                 ORDER BY u.lastname, u.firstname";
        return $DB->get_records_sql($sql, ['stageid' => $stageid]);
    }

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

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

    public static function get_results(int $entryid): array {
        global $DB;
        return $DB->get_records('local_kid_stage_result', ['entryid' => $entryid], '', 'competencyid, id, niveauid, commentaire');
    }

    /**
     * Full replace — the grid's "Enregistrer" button. $commentaireglobal is
     * the single stage-level comment (local_kid_stage_entry.commentaireglobal),
     * entirely separate from the per-competency commentaire written into
     * local_kid_stage_result by write_results() below — never merged,
     * never overwritten by CSV import (upsert_results() never touches it).
     */
    public static function save_results(int $entryid, array $results, ?string $commentaireglobal, int $userid): void {
        global $DB;

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

    /**
     * Only touches the competencies present in $results — used by CSV
     * import so a second import (e.g. a different delimiter, a follow-up
     * file) never erases a competency entered by an earlier import. Not the
     * same semantics as save_results() (full replace) — deliberate.
     */
    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);
    }

    /**
     * @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 = [];
        foreach ($results as $competencyid => $data) {
            $existing = $DB->get_record('local_kid_stage_result', ['entryid' => $entryid, 'competencyid' => $competencyid], '*', IGNORE_MISSING);
            $newniveauid = $data['niveauid'] ? (int) $data['niveauid'] : null;
            $newcommentaire = (string) ($data['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 = $existing && $existing->niveauid ? (int) $existing->niveauid : null;
            $oldcommentaire = $existing ? (string) ($existing->commentaire ?? '') : '';
            if ($oldniveauid !== $newniveauid || $oldcommentaire !== $newcommentaire) {
                $diff[$competencyid] = [
                    'old' => ['niveauid' => $oldniveauid, 'commentaire' => $oldcommentaire],
                    'new' => ['niveauid' => $newniveauid, 'commentaire' => $newcommentaire],
                ];
            }
            $record = (object) [
                'entryid' => $entryid,
                'competencyid' => $competencyid,
                'niveauid' => $newniveauid,
                'commentaire' => $newcommentaire,
                'timemodified' => $now,
            ];
            if ($existing) {
                $record->id = $existing->id;
                $DB->update_record('local_kid_stage_result', $record);
            } else {
                $record->timecreated = $now;
                $DB->insert_record('local_kid_stage_result', $record);
            }
        }
        $DB->update_record('local_kid_stage_entry', (object) ['id' => $entryid, 'timemodified' => $now]);
        return $diff;
    }

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

    /**
     * Builds the downloadable CSV template — one row per (student ×
     * competency) pair of the stage's scope, niveau/commentaire left blank
     * — restricted to a single student when given, matching the CSV import
     * format exactly so the filled-in file can be re-uploaded as-is.
     */
    public static function build_csv_template(\stdClass $stage, ?int $onlystudentid = null): string {
        $students = promotion_repository::get_active_students((int) $stage->promotionid);
        if ($onlystudentid) {
            $students = array_filter($students, fn($s) => (int) $s->studentid === $onlystudentid);
        }
        return csv_import_service::build_template($students, self::get_scope_competencies($stage));
    }

    /**
     * @param string $content raw CSV file content
     * @return array{added:int, updated:int, errors:string[]}
     */
    public static function import_csv(\stdClass $stage, string $content, int $userid, ?int $onlystudentid = null): array {
        global $DB;

        $parsed = csv_import_service::parse($content);
        $competencies = self::get_scope_competencies($stage);
        $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_stage((int) $stage->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('stage_import_error_student', 'local_kid', $line);
                continue;
            }
            if ($onlystudentid !== null && $studentid !== $onlystudentid) {
                $errors[] = get_string('stage_import_error_scope', 'local_kid', $line);
                continue;
            }
            if ($entriesbystudentid[$studentid]->workflowstatus === workflow_service::STATUS_FINALISE) {
                $errors[] = get_string('stage_import_error_locked', 'local_kid', $line);
                continue;
            }
            if (!isset($competenciesbylibelle[$competencelabel])) {
                $errors[] = get_string('stage_import_error_competency', 'local_kid', $line);
                continue;
            }
            if ($niveaulabel !== '' && !isset($niveauxbylabel[$niveaulabel])) {
                $errors[] = get_string('stage_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' => 'stage', 'promotionid' => (int) $stage->promotionid, 'itemid' => (int) $stage->id,
                'added' => count($touchedentries), 'errorcount' => count($errors),
            ],
        ])->trigger();

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

    public static function list_docs(int $entryid): array {
        global $DB;
        return $DB->get_records('local_kid_stage_doc', ['entryid' => $entryid], 'timecreated DESC');
    }

    public static function delete_doc(int $docid): void {
        global $DB;
        $doc = $DB->get_record('local_kid_stage_doc', ['id' => $docid], '*', IGNORE_MISSING);
        if (!$doc) {
            return;
        }
        $fs = get_file_storage();
        $file = $fs->get_file_by_hash($doc->pathnamehash);
        if ($file) {
            $file->delete();
        }
        $DB->delete_records('local_kid_stage_doc', ['id' => $docid]);
    }
}
