<?php
/**
 * NIS2 Agile - Competenze (A4 Fase 4.2)
 * ----------------------------------------------------------------------------
 * Catalogo competenze (skills), requisiti per ruolo (role_skills), competenze
 * possedute dagli utenti (user_skills), mappatura competenza<->corso
 * (skill_course_map), calcolo del GAP competenze e apertura di un'azione
 * correttiva dal gap riusando il workflow NCR/CAPA esistente.
 *
 * Multi-tenancy ancorata a getCurrentOrgId(); scritture riservate a
 * org_admin/compliance_manager (super_admin bypassa). Anti-IDOR su (id +
 * organization_id). Le competenze globali (skills.organization_id NULL) sono in
 * sola lettura per gli utenti dell'org (solo super_admin le gestisce).
 *
 * Ancoraggio normativo (docs/DESIGN_A4_RELATIONAL.md sez.3/4): PR.AT-01 (tutti),
 * PR.AT-02 (solo soggetti essenziali), GV.RR-04 (cyber nelle pratiche HR).
 *
 * NB: la tabella delle azioni e 'capa_actions' (figlia di 'non_conformities'),
 * NON 'corrective_actions' (che non esiste). openAction() crea una NCR ancorata
 * al gap (source_entity_type='competence_gap') + una CAPA collegata.
 */

require_once __DIR__ . '/BaseController.php';

class CompetenceController extends BaseController
{
    private const MANAGE_ROLES = ['org_admin', 'compliance_manager'];

    // ═══════════════════════════════════════════════════════════════════════
    // CATALOGO COMPETENZE (skills)
    // ═══════════════════════════════════════════════════════════════════════

    /** GET /api/competences/catalog — skill visibili (org + globali) + corsi mappati. */
    public function catalog(): void
    {
        $this->requireOrgAccess();
        $orgId = $this->getCurrentOrgId();

        $skills = Database::fetchAll(
            'SELECT s.id, s.organization_id, s.name, s.area, s.description, s.created_at, s.updated_at
             FROM skills s
             WHERE s.organization_id = ? OR s.organization_id IS NULL
             ORDER BY (s.organization_id IS NULL) DESC, s.area, s.name',
            [$orgId]
        );

        // Corsi mappati per ciascuna skill (corsi visibili: org o globali).
        $maps = Database::fetchAll(
            'SELECT scm.id AS map_id, scm.skill_id, scm.training_course_id, tc.title
             FROM skill_course_map scm
             JOIN training_courses tc ON tc.id = scm.training_course_id
             WHERE tc.organization_id = ? OR tc.organization_id IS NULL',
            [$orgId]
        );
        $coursesBySkill = [];
        foreach ($maps as $m) {
            $coursesBySkill[(int) $m['skill_id']][] = [
                'map_id'    => (int) $m['map_id'],
                'course_id' => (int) $m['training_course_id'],
                'title'     => $m['title'],
            ];
        }

        $isSuper = ($this->currentUser['role'] ?? '') === 'super_admin';
        $out = array_map(function ($s) use ($coursesBySkill, $isSuper, $orgId) {
            $s = $this->normalizeSkill($s);
            $s['courses']   = $coursesBySkill[$s['id']] ?? [];
            $s['editable']  = $s['is_global'] ? $isSuper : ($s['organization_id'] === $orgId);
            return $s;
        }, $skills);

        $this->jsonSuccess([
            'skills' => $out,
            'total'  => count($out),
        ]);
    }

    /** POST /api/competences/skills — {name, area?, description?, global?(super_admin)} */
    public function createSkill(): void
    {
        $this->requireOrgRole(self::MANAGE_ROLES);

        $name = trim((string) $this->getParam('name', ''));
        if ($name === '') {
            $this->jsonError('Il nome della competenza e obbligatorio', 422, 'SKILL_NAME_REQUIRED');
        }
        if (mb_strlen($name) > 150) {
            $this->jsonError('Il nome della competenza supera 150 caratteri', 422, 'SKILL_NAME_TOO_LONG');
        }

        // Solo super_admin puo creare competenze globali (organization_id NULL).
        $isSuper = ($this->currentUser['role'] ?? '') === 'super_admin';
        $orgId = ($isSuper && $this->getParam('global')) ? null : $this->getCurrentOrgId();

        $id = Database::insert('skills', [
            'organization_id' => $orgId,
            'name'            => $name,
            'area'            => $this->nullableText($this->getParam('area'), 100),
            'description'     => $this->nullableText($this->getParam('description')),
            'created_by'      => $this->getCurrentUserId(),
        ]);

        $this->logAudit('skill_created', 'skill', $id, ['name' => $name, 'global' => $orgId === null]);
        $skill = Database::fetchOne('SELECT * FROM skills WHERE id = ?', [$id]);
        $this->jsonSuccess($this->normalizeSkill($skill), 'Competenza creata', 201);
    }

    /** PUT /api/competences/skills/{id} */
    public function updateSkill(int $id): void
    {
        $this->requireOrgRole(self::MANAGE_ROLES);
        $skill = $this->fetchVisibleSkillOrFail($id);
        $this->assertSkillWritable($skill);

        $updates = [];
        if ($this->hasParam('name')) {
            $name = trim((string) $this->getParam('name', ''));
            if ($name === '') {
                $this->jsonError('Il nome della competenza e obbligatorio', 422, 'SKILL_NAME_REQUIRED');
            }
            if (mb_strlen($name) > 150) {
                $this->jsonError('Il nome della competenza supera 150 caratteri', 422, 'SKILL_NAME_TOO_LONG');
            }
            $updates['name'] = $name;
        }
        if ($this->hasParam('area')) {
            $updates['area'] = $this->nullableText($this->getParam('area'), 100);
        }
        if ($this->hasParam('description')) {
            $updates['description'] = $this->nullableText($this->getParam('description'));
        }
        if (empty($updates)) {
            $this->jsonError('Nessun campo da aggiornare', 400, 'NO_UPDATES');
        }

        Database::update('skills', $updates, 'id = ?', [$id]);
        $this->logAudit('skill_updated', 'skill', $id, $updates);
        $skill = Database::fetchOne('SELECT * FROM skills WHERE id = ?', [$id]);
        $this->jsonSuccess($this->normalizeSkill($skill), 'Competenza aggiornata');
    }

    /** DELETE /api/competences/skills/{id} — cascata su role_skills/user_skills/skill_course_map. */
    public function deleteSkill(int $id): void
    {
        $this->requireOrgRole(self::MANAGE_ROLES);
        $skill = $this->fetchVisibleSkillOrFail($id);
        $this->assertSkillWritable($skill);

        $usedRole = Database::count('role_skills', 'skill_id = ?', [$id]);
        $usedUser = Database::count('user_skills', 'skill_id = ?', [$id]);

        Database::delete('skills', 'id = ?', [$id]);
        $this->logAudit('skill_deleted', 'skill', $id, ['role_links' => $usedRole, 'user_links' => $usedUser]);
        $this->jsonSuccess([
            'removed_role_links' => $usedRole,
            'removed_user_links' => $usedUser,
        ], 'Competenza eliminata');
    }

    // ═══════════════════════════════════════════════════════════════════════
    // REQUISITI PER RUOLO (role_skills)
    // ═══════════════════════════════════════════════════════════════════════

    /** GET /api/competences/roleSkills/{roleId} — competenze richieste dal ruolo. */
    public function roleSkills(int $roleId): void
    {
        $this->requireOrgAccess();
        $this->fetchRoleOrFail($roleId);

        $rows = Database::fetchAll(
            'SELECT rs.id, rs.role_id, rs.skill_id, rs.required_level, s.name AS skill_name, s.area
             FROM role_skills rs JOIN skills s ON s.id = rs.skill_id
             WHERE rs.role_id = ? AND rs.organization_id = ?
             ORDER BY s.area, s.name',
            [$roleId, $this->getCurrentOrgId()]
        );
        $this->jsonSuccess(array_map(fn($r) => [
            'id'             => (int) $r['id'],
            'role_id'        => (int) $r['role_id'],
            'skill_id'       => (int) $r['skill_id'],
            'skill_name'     => $r['skill_name'],
            'area'           => $r['area'],
            'required_level' => (int) $r['required_level'],
        ], $rows));
    }

    /** POST /api/competences/roleSkills — {role_id, skill_id, required_level} upsert. */
    public function setRoleSkill(): void
    {
        $this->requireOrgRole(self::MANAGE_ROLES);
        $this->validateRequired(['role_id', 'skill_id']);
        $orgId = $this->getCurrentOrgId();

        $roleId = (int) $this->getParam('role_id');
        $skillId = (int) $this->getParam('skill_id');
        $level = $this->clampLevel($this->getParam('required_level', 3));

        $this->fetchRoleOrFail($roleId);
        $this->fetchVisibleSkillOrFail($skillId);

        $existing = Database::fetchOne(
            'SELECT id FROM role_skills WHERE role_id = ? AND skill_id = ?',
            [$roleId, $skillId]
        );
        if ($existing) {
            Database::update('role_skills', ['required_level' => $level], 'id = ?', [(int) $existing['id']]);
            $id = (int) $existing['id'];
            $created = false;
        } else {
            $id = Database::insert('role_skills', [
                'organization_id' => $orgId,
                'role_id'         => $roleId,
                'skill_id'        => $skillId,
                'required_level'  => $level,
                'created_by'      => $this->getCurrentUserId(),
            ]);
            $created = true;
        }

        $this->logAudit('role_skill_set', 'role_skill', $id, ['role_id' => $roleId, 'skill_id' => $skillId, 'required_level' => $level]);
        $this->jsonSuccess(['id' => $id, 'required_level' => $level], $created ? 'Requisito aggiunto' : 'Requisito aggiornato', $created ? 201 : 200);
    }

    /** DELETE /api/competences/roleSkills/{id} */
    public function removeRoleSkill(int $id): void
    {
        $this->requireOrgRole(self::MANAGE_ROLES);
        $row = Database::fetchOne('SELECT id FROM role_skills WHERE id = ? AND organization_id = ?', [$id, $this->getCurrentOrgId()]);
        if (!$row) {
            $this->jsonError('Requisito non trovato', 404, 'ROLE_SKILL_NOT_FOUND');
        }
        Database::delete('role_skills', 'id = ? AND organization_id = ?', [$id, $this->getCurrentOrgId()]);
        $this->logAudit('role_skill_removed', 'role_skill', $id, null);
        $this->jsonSuccess(null, 'Requisito rimosso');
    }

    // ═══════════════════════════════════════════════════════════════════════
    // COMPETENZE POSSEDUTE (user_skills)
    // ═══════════════════════════════════════════════════════════════════════

    /** GET /api/competences/userSkills/{userId} — competenze possedute dall'utente. */
    public function userSkills(int $userId): void
    {
        $this->requireOrgAccess();
        $this->assertMember($userId);

        $rows = Database::fetchAll(
            'SELECT us.id, us.user_id, us.skill_id, us.level, us.acquired_via_course_id, us.evidence,
                    s.name AS skill_name, s.area, tc.title AS course_title
             FROM user_skills us
             JOIN skills s ON s.id = us.skill_id
             LEFT JOIN training_courses tc ON tc.id = us.acquired_via_course_id
             WHERE us.user_id = ? AND us.organization_id = ?
             ORDER BY s.area, s.name',
            [$userId, $this->getCurrentOrgId()]
        );
        $this->jsonSuccess(array_map(fn($r) => [
            'id'                     => (int) $r['id'],
            'user_id'                => (int) $r['user_id'],
            'skill_id'               => (int) $r['skill_id'],
            'skill_name'             => $r['skill_name'],
            'area'                   => $r['area'],
            'level'                  => (int) $r['level'],
            'acquired_via_course_id' => $r['acquired_via_course_id'] !== null ? (int) $r['acquired_via_course_id'] : null,
            'course_title'           => $r['course_title'],
            'evidence'               => $r['evidence'],
        ], $rows));
    }

    /** POST /api/competences/userSkills — {user_id, skill_id, level, evidence?, acquired_via_course_id?} upsert. */
    public function setUserSkill(): void
    {
        $this->requireOrgRole(self::MANAGE_ROLES);
        $this->validateRequired(['user_id', 'skill_id']);
        $orgId = $this->getCurrentOrgId();

        $userId = (int) $this->getParam('user_id');
        $skillId = (int) $this->getParam('skill_id');
        $level = $this->clampLevel($this->getParam('level', 1));

        $this->assertMember($userId);
        $this->fetchVisibleSkillOrFail($skillId);
        $courseId = $this->validateCourse($this->getParam('acquired_via_course_id'));

        $existing = Database::fetchOne(
            'SELECT id FROM user_skills WHERE organization_id = ? AND user_id = ? AND skill_id = ?',
            [$orgId, $userId, $skillId]
        );
        $data = [
            'level'                  => $level,
            'acquired_via_course_id' => $courseId,
            'evidence'               => $this->nullableText($this->getParam('evidence'), 500),
            'assessed_at'            => date('Y-m-d H:i:s'),
        ];
        if ($existing) {
            Database::update('user_skills', $data, 'id = ?', [(int) $existing['id']]);
            $id = (int) $existing['id'];
            $created = false;
        } else {
            $data['organization_id'] = $orgId;
            $data['user_id']         = $userId;
            $data['skill_id']        = $skillId;
            $data['created_by']      = $this->getCurrentUserId();
            $id = Database::insert('user_skills', $data);
            $created = true;
        }

        $this->logAudit('user_skill_set', 'user_skill', $id, ['user_id' => $userId, 'skill_id' => $skillId, 'level' => $level]);
        $this->jsonSuccess(['id' => $id, 'level' => $level], $created ? 'Competenza registrata' : 'Competenza aggiornata', $created ? 201 : 200);
    }

    /** DELETE /api/competences/userSkills/{id} */
    public function removeUserSkill(int $id): void
    {
        $this->requireOrgRole(self::MANAGE_ROLES);
        $row = Database::fetchOne('SELECT id FROM user_skills WHERE id = ? AND organization_id = ?', [$id, $this->getCurrentOrgId()]);
        if (!$row) {
            $this->jsonError('Competenza non trovata', 404, 'USER_SKILL_NOT_FOUND');
        }
        Database::delete('user_skills', 'id = ? AND organization_id = ?', [$id, $this->getCurrentOrgId()]);
        $this->logAudit('user_skill_removed', 'user_skill', $id, null);
        $this->jsonSuccess(null, 'Competenza rimossa');
    }

    // ═══════════════════════════════════════════════════════════════════════
    // MAPPATURA COMPETENZA <-> CORSO (skill_course_map)
    // ═══════════════════════════════════════════════════════════════════════

    /** POST /api/competences/skillCourses — {skill_id, training_course_id} */
    public function mapCourse(): void
    {
        $this->requireOrgRole(self::MANAGE_ROLES);
        $this->validateRequired(['skill_id', 'training_course_id']);

        $skillId = (int) $this->getParam('skill_id');
        $courseId = (int) $this->getParam('training_course_id');

        $skill = $this->fetchVisibleSkillOrFail($skillId);
        $this->assertSkillWritable($skill);
        if ($this->validateCourse($courseId) === null) {
            $this->jsonError('Corso non valido per questa organizzazione', 422, 'INVALID_COURSE');
        }

        $existing = Database::fetchOne(
            'SELECT id FROM skill_course_map WHERE skill_id = ? AND training_course_id = ?',
            [$skillId, $courseId]
        );
        if ($existing) {
            $this->jsonSuccess(['id' => (int) $existing['id']], 'Corso gia mappato', 200);
        }
        $id = Database::insert('skill_course_map', [
            'skill_id'           => $skillId,
            'training_course_id' => $courseId,
            'created_by'         => $this->getCurrentUserId(),
        ]);
        $this->logAudit('skill_course_mapped', 'skill', $skillId, ['training_course_id' => $courseId]);
        $this->jsonSuccess(['id' => $id], 'Corso mappato', 201);
    }

    /** DELETE /api/competences/skillCourses/{id} */
    public function unmapCourse(int $id): void
    {
        $this->requireOrgRole(self::MANAGE_ROLES);
        // Verifica che il mapping riguardi una skill gestibile dall'org corrente.
        $row = Database::fetchOne(
            'SELECT scm.id, s.organization_id
             FROM skill_course_map scm JOIN skills s ON s.id = scm.skill_id
             WHERE scm.id = ? AND (s.organization_id = ? OR s.organization_id IS NULL)',
            [$id, $this->getCurrentOrgId()]
        );
        if (!$row) {
            $this->jsonError('Mappatura non trovata', 404, 'MAP_NOT_FOUND');
        }
        $isGlobal = $row['organization_id'] === null;
        if ($isGlobal && ($this->currentUser['role'] ?? '') !== 'super_admin') {
            $this->jsonError('Le competenze globali sono gestite dal fornitore', 403, 'SKILL_GLOBAL_READONLY');
        }
        Database::delete('skill_course_map', 'id = ?', [$id]);
        $this->logAudit('skill_course_unmapped', 'skill', (int) ($row['organization_id'] ?? 0), ['map_id' => $id]);
        $this->jsonSuccess(null, 'Mappatura rimossa');
    }

    // ═══════════════════════════════════════════════════════════════════════
    // GAP COMPETENZE + ALERT->AZIONE
    // ═══════════════════════════════════════════════════════════════════════

    /**
     * GET /api/competences/gapGrid
     * Per ogni ruolo con titolare: competenze richieste vs possedute, gap,
     * corsi suggeriti e se esiste gia un'azione (NCR) aperta sul gap.
     */
    public function gapGrid(): void
    {
        $this->requireOrgAccess();
        $orgId = $this->getCurrentOrgId();

        $org = Database::fetchOne('SELECT entity_type FROM organizations WHERE id = ?', [$orgId]);
        $entityType = $org['entity_type'] ?? 'not_applicable';

        $rows = Database::fetchAll(
            'SELECT r.id AS role_id, r.role_name, r.holder_user_id, u.full_name AS holder_name,
                    rs.id AS role_skill_id, rs.skill_id, s.name AS skill_name, s.area,
                    rs.required_level, COALESCE(us.level, 0) AS current_level
             FROM org_roles r
             JOIN users u ON u.id = r.holder_user_id
             JOIN role_skills rs ON rs.role_id = r.id
             JOIN skills s ON s.id = rs.skill_id
             LEFT JOIN user_skills us
               ON us.user_id = r.holder_user_id AND us.skill_id = rs.skill_id AND us.organization_id = r.organization_id
             WHERE r.organization_id = ? AND r.holder_user_id IS NOT NULL
             ORDER BY r.sort_order, r.role_name, s.area, s.name',
            [$orgId]
        );

        // Corsi suggeriti per skill (corsi visibili: org o globali).
        $maps = Database::fetchAll(
            'SELECT scm.skill_id, tc.id AS course_id, tc.title
             FROM skill_course_map scm JOIN training_courses tc ON tc.id = scm.training_course_id
             WHERE tc.organization_id = ? OR tc.organization_id IS NULL',
            [$orgId]
        );
        $coursesBySkill = [];
        foreach ($maps as $m) {
            $coursesBySkill[(int) $m['skill_id']][] = ['id' => (int) $m['course_id'], 'title' => $m['title']];
        }

        // Azioni (NCR) gia aperte ancorate a un gap, indicizzate per role_skill_id.
        $openNcr = Database::fetchAll(
            "SELECT source_entity_id, id, ncr_code, status FROM non_conformities
             WHERE organization_id = ? AND source_entity_type = 'competence_gap'
               AND status NOT IN ('closed','cancelled')",
            [$orgId]
        );
        $ncrByRoleSkill = [];
        foreach ($openNcr as $n) {
            $ncrByRoleSkill[(int) $n['source_entity_id']] = ['ncr_id' => (int) $n['id'], 'ncr_code' => $n['ncr_code'], 'status' => $n['status']];
        }

        $rolesById = [];
        $totalGaps = 0;
        foreach ($rows as $r) {
            $roleId = (int) $r['role_id'];
            if (!isset($rolesById[$roleId])) {
                $rolesById[$roleId] = [
                    'role_id'        => $roleId,
                    'role_name'      => $r['role_name'],
                    'holder_user_id' => (int) $r['holder_user_id'],
                    'holder_name'    => $r['holder_name'],
                    'skills'         => [],
                    'gap_count'      => 0,
                ];
            }
            $required = (int) $r['required_level'];
            $current = (int) $r['current_level'];
            $gap = max(0, $required - $current);
            $roleSkillId = (int) $r['role_skill_id'];
            if ($gap > 0) {
                $totalGaps++;
                $rolesById[$roleId]['gap_count']++;
            }
            $rolesById[$roleId]['skills'][] = [
                'role_skill_id'    => $roleSkillId,
                'skill_id'         => (int) $r['skill_id'],
                'skill_name'       => $r['skill_name'],
                'area'             => $r['area'],
                'required_level'   => $required,
                'current_level'    => $current,
                'gap'              => $gap,
                'suggested_courses'=> $coursesBySkill[(int) $r['skill_id']] ?? [],
                'open_action'      => $ncrByRoleSkill[$roleSkillId] ?? null,
            ];
        }

        $roles = array_values($rolesById);
        $this->jsonSuccess([
            'entity_type'        => $entityType,
            'pr_at_02_applies'   => $entityType === 'essential',
            'roles'              => $roles,
            'roles_with_holder'  => count($roles),
            'roles_with_gaps'    => count(array_filter($roles, fn($r) => $r['gap_count'] > 0)),
            'total_gaps'         => $totalGaps,
        ]);
    }

    /**
     * POST /api/competences/openAction — {role_skill_id}
     * Apre una Non Conformita (NCR) ancorata al gap + una CAPA collegata,
     * riusando il workflow NCR/CAPA esistente. Idempotente: se esiste gia una
     * NCR aperta per lo stesso gap, ritorna quella (409).
     */
    public function openAction(): void
    {
        $this->requireOrgRole(self::MANAGE_ROLES);
        $this->validateRequired(['role_skill_id']);
        $orgId = $this->getCurrentOrgId();

        $roleSkillId = (int) $this->getParam('role_skill_id');
        $rs = Database::fetchOne(
            'SELECT rs.id, rs.role_id, rs.skill_id, rs.required_level, s.name AS skill_name,
                    r.role_name, r.holder_user_id
             FROM role_skills rs
             JOIN skills s ON s.id = rs.skill_id
             JOIN org_roles r ON r.id = rs.role_id
             WHERE rs.id = ? AND rs.organization_id = ?',
            [$roleSkillId, $orgId]
        );
        if (!$rs) {
            $this->jsonError('Requisito di competenza non trovato', 404, 'ROLE_SKILL_NOT_FOUND');
        }
        if ($rs['holder_user_id'] === null) {
            $this->jsonError('Il ruolo non ha un titolare: assegna prima un titolare', 422, 'ROLE_NO_HOLDER');
        }

        $holderId = (int) $rs['holder_user_id'];
        $required = (int) $rs['required_level'];
        $cur = Database::fetchOne(
            'SELECT level FROM user_skills WHERE organization_id = ? AND user_id = ? AND skill_id = ?',
            [$orgId, $holderId, (int) $rs['skill_id']]
        );
        $current = $cur ? (int) $cur['level'] : 0;
        $gap = $required - $current;
        if ($gap <= 0) {
            $this->jsonError('Nessun gap di competenza su questo requisito', 422, 'NO_GAP');
        }

        // Anti-duplicato: NCR gia aperta per lo stesso gap.
        $dup = Database::fetchOne(
            "SELECT id, ncr_code FROM non_conformities
             WHERE organization_id = ? AND source_entity_type = 'competence_gap' AND source_entity_id = ?
               AND status NOT IN ('closed','cancelled')",
            [$orgId, $roleSkillId]
        );
        if ($dup) {
            $this->jsonError('Esiste gia una non conformita aperta per questo gap (' . $dup['ncr_code'] . ')', 409, 'ACTION_EXISTS', [
                'ncr_id'   => (int) $dup['id'],
                'ncr_code' => $dup['ncr_code'],
            ]);
        }

        // PR.AT-02 vale solo per i soggetti essenziali; PR.AT-01 per tutti.
        $org = Database::fetchOne('SELECT entity_type FROM organizations WHERE id = ?', [$orgId]);
        $prAt = (($org['entity_type'] ?? '') === 'essential') ? 'PR.AT-02' : 'PR.AT-01';
        $severity = $gap >= 2 ? 'major' : 'minor';

        $title = 'Gap competenza: ' . $rs['skill_name'] . ' (' . $rs['role_name'] . ')';
        $desc = "Gap di competenza rilevato dall'organigramma.\n"
            . 'Ruolo: ' . $rs['role_name'] . "\n"
            . 'Competenza: ' . $rs['skill_name'] . "\n"
            . 'Livello richiesto: ' . $required . ' / Posseduto: ' . $current . ' (gap: ' . $gap . ")\n"
            . 'Riferimento: ' . $prAt . ' (formazione e consapevolezza); GV.RR-04 (cyber nelle pratiche HR). '
            . 'Gli obblighi di gestione del rischio fanno capo all\'art. 24 D.Lgs. 138/2024.';

        Database::beginTransaction();
        try {
            $ncrCode = $this->generateCode('NCR');
            $ncrId = Database::insert('non_conformities', [
                'organization_id'   => $orgId,
                'ncr_code'          => $ncrCode,
                'title'             => mb_substr($title, 0, 255),
                'description'       => $desc,
                'source'            => 'management_review',
                'source_entity_type'=> 'competence_gap',
                'source_entity_id'  => $roleSkillId,
                'severity'          => $severity,
                'category'          => 'Competenze',
                'nis2_article'      => $prAt,
                'status'            => 'action_planned',
                'identified_by'     => $this->getCurrentUserId(),
                'assigned_to'       => $holderId,
            ]);

            $capaCode = $this->generateCode('CAPA');
            $capaId = Database::insert('capa_actions', [
                'ncr_id'             => $ncrId,
                'organization_id'    => $orgId,
                'capa_code'          => $capaCode,
                'action_type'        => 'corrective',
                'title'              => mb_substr('Colmare il gap di competenza: ' . $rs['skill_name'], 0, 255),
                'description'        => 'Pianificare formazione/affiancamento per portare ' . $rs['skill_name']
                                        . ' al livello ' . $required . '. Valutare i corsi mappati alla competenza.',
                'status'             => 'planned',
                'responsible_user_id'=> $holderId,
            ]);

            Database::commit();
        } catch (Throwable $e) {
            Database::rollback();
            throw $e;
        }

        $this->logAudit('competence_action_opened', 'non_conformity', $ncrId, [
            'role_skill_id' => $roleSkillId, 'capa_id' => $capaId, 'gap' => $gap,
        ]);
        $this->jsonSuccess([
            'ncr_id'    => $ncrId,
            'ncr_code'  => $ncrCode,
            'capa_id'   => $capaId,
            'capa_code' => $capaCode,
        ], 'Non conformita e azione correttiva create', 201);
    }

    // ═══════════════════════════════════════════════════════════════════════
    // PRIVATI
    // ═══════════════════════════════════════════════════════════════════════

    /** Skill visibile all'org (org-owned o globale) oppure 404. */
    private function fetchVisibleSkillOrFail(int $id): array
    {
        $s = Database::fetchOne(
            'SELECT * FROM skills WHERE id = ? AND (organization_id = ? OR organization_id IS NULL)',
            [$id, $this->getCurrentOrgId()]
        );
        if (!$s) {
            $this->jsonError('Competenza non trovata', 404, 'SKILL_NOT_FOUND');
        }
        return $s;
    }

    /** Una skill e scrivibile se appartiene all'org; le globali solo da super_admin. */
    private function assertSkillWritable(array $skill): void
    {
        $isGlobal = $skill['organization_id'] === null;
        if ($isGlobal && ($this->currentUser['role'] ?? '') !== 'super_admin') {
            $this->jsonError('Le competenze globali sono gestite dal fornitore', 403, 'SKILL_GLOBAL_READONLY');
        }
    }

    /** Ruolo dell'organigramma nell'org corrente oppure 404 (anti-IDOR). */
    private function fetchRoleOrFail(int $id): array
    {
        $r = Database::fetchOne('SELECT * FROM org_roles WHERE id = ? AND organization_id = ?', [$id, $this->getCurrentOrgId()]);
        if (!$r) {
            $this->jsonError('Ruolo non trovato', 404, 'ROLE_NOT_FOUND');
        }
        return $r;
    }

    /** L'utente deve essere membro dell'org corrente. */
    private function assertMember(int $userId): void
    {
        $m = Database::fetchOne(
            'SELECT id FROM user_organizations WHERE user_id = ? AND organization_id = ?',
            [$userId, $this->getCurrentOrgId()]
        );
        if (!$m) {
            $this->jsonError('Utente non membro dell organizzazione', 422, 'USER_NOT_MEMBER');
        }
    }

    /** Corso visibile (org o globale) oppure null se non fornito; jsonError se id non valido. */
    private function validateCourse($raw): ?int
    {
        if ($raw === null || $raw === '' || (int) $raw === 0) {
            return null;
        }
        $courseId = (int) $raw;
        $c = Database::fetchOne(
            'SELECT id FROM training_courses WHERE id = ? AND (organization_id = ? OR organization_id IS NULL)',
            [$courseId, $this->getCurrentOrgId()]
        );
        if (!$c) {
            $this->jsonError('Corso non valido per questa organizzazione', 422, 'INVALID_COURSE');
        }
        return $courseId;
    }

    private function clampLevel($raw): int
    {
        $n = (int) $raw;
        if ($n < 1) { $n = 1; }
        if ($n > 5) { $n = 5; }
        return $n;
    }

    private function nullableText($v, int $max = 4000): ?string
    {
        if ($v === null) {
            return null;
        }
        $v = trim((string) $v);
        if ($v === '') {
            return null;
        }
        return mb_substr($v, 0, $max);
    }

    private function normalizeSkill(array $s): array
    {
        return [
            'id'              => (int) $s['id'],
            'organization_id' => $s['organization_id'] !== null ? (int) $s['organization_id'] : null,
            'is_global'       => $s['organization_id'] === null,
            'name'            => $s['name'],
            'area'            => $s['area'],
            'description'     => $s['description'] ?? null,
            'created_at'      => $s['created_at'] ?? null,
            'updated_at'      => $s['updated_at'] ?? null,
        ];
    }
}