nis2-agile/application/cli/build_nuova_agile_p5_remediation.php

<?php
/**
 * build_nuova_agile_p5_remediation.php — Bonifica post-audit (2o ciclo operativo + chiusura NC). IDEMPOTENTE.
 * Crea evidenze (trattamento rischi, formazione, esecuzione controlli), aggiorna SoA, apre CAPA, chiude le NC chiudibili.
 */
if (PHP_SAPI !== 'cli') { http_response_code(403); exit("CLI only\n"); }
require_once __DIR__ . '/../config/env.php';
require_once __DIR__ . '/../config/database.php';
$pdo = Database::getInstance();
function ex(PDO $p,string $s,array $a=[]):void{$st=$p->prepare($s);$st->execute($a);}
function one(PDO $p,string $s,array $a=[]){$st=$p->prepare($s);$st->execute($a);return $st->fetch(PDO::FETCH_ASSOC);}
function all(PDO $p,string $s,array $a=[]){$st=$p->prepare($s);$st->execute($a);return $st->fetchAll(PDO::FETCH_ASSOC);}
$NOW='2026-06-20 11:45:00'; $TODAY='2026-06-20'; $ORGID=996003; $RSGSI=326; $ITSEC=330; $PRES=103;
$model=one($pdo,"SELECT id FROM isms_models WHERE organization_id=?",[$ORGID]); $modelId=(int)$model['id'];

/* ---- 1) Trattamento dei rischi (chiude NCR-9002) --------------------------- */
$rt=0;
foreach(all($pdo,"SELECT id,risk_code,title,likelihood,impact,category FROM risks WHERE organization_id=?",[$ORGID]) as $r){
  $exi=one($pdo,"SELECT id FROM risk_treatments WHERE risk_id=?",[$r['id']]);
  if($exi) continue;
  $resp=in_array($r['category'],['compliance','human'])?$RSGSI:$ITSEC;
  $act="Piano di trattamento per {$r['risk_code']}: attuazione dei controlli ISO/NIS2 collegati (politiche, procedure, controlli periodici) e verifica dell'efficacia. Mitigazione tramite controlli tecnici e organizzativi gia documentati nel SGSI.";
  ex($pdo,"INSERT INTO risk_treatments (risk_id,action_description,responsible_user_id,due_date,status,notes,created_at,updated_at) VALUES (?,?,?,?, 'in_progress', ?, ?, ?)",
    [$r['id'],$act,$resp,'2026-09-30','Generato in chiusura NC audit AUD-001.',$NOW,$NOW]);
  // riduci il rischio residuo
  $rl=max(1,(int)$r['likelihood']-1); $ri=(int)$r['impact'];
  ex($pdo,"UPDATE risks SET residual_likelihood=?,residual_impact=?,residual_risk_score=?,status='monitored',updated_at=? WHERE id=?",[$rl,$ri,$rl*$ri,$NOW,$r['id']]);
  $rt++;
}
echo "Trattamenti rischio: +$rt\n";

/* ---- 2) Formazione erogata (chiude NCR-9003) ------------------------------- */
$courses=all($pdo,"SELECT id,title,target_role FROM training_courses WHERE organization_id=?",[$ORGID]);
$assignMap=[ 'all'=>[103,326,330], 'compliance_manager'=>[326], 'technical'=>[330,326], 'board_member'=>[103], 'employee'=>[103,326,330] ];
$ta=0;
foreach($courses as $c){
  $users=$assignMap[$c['target_role']]??[103,326,330];
  foreach($users as $uid){
    $exi=one($pdo,"SELECT id FROM training_assignments WHERE course_id=? AND user_id=?",[$c['id'],$uid]);
    if($exi) continue;
    ex($pdo,"INSERT INTO training_assignments (course_id,user_id,organization_id,status,due_date,started_at,completed_at,quiz_score,created_at,updated_at)
             VALUES (?,?,?, 'completed', ?, ?, ?, 88, ?, ?)",
      [$c['id'],$uid,$ORGID,'2026-07-31',$NOW,$NOW,$NOW,$NOW]); $ta++;
  }
}
echo "Assegnazioni formazione (completate): +$ta\n";

/* ---- 3) Esecuzione controlli periodici (chiude NCR-9004) ------------------- */
$next=['settimanale'=>'2026-06-27','mensile'=>'2026-07-20','trimestrale'=>'2026-09-20','semestrale'=>'2026-12-20','annuale'=>'2027-06-20'];
$pe=0;
foreach(all($pdo,"SELECT id,frequency,owner_role_id FROM periodic_controls WHERE organization_id=?",[$ORGID]) as $c){
  $exi=one($pdo,"SELECT id FROM periodic_control_executions WHERE control_id=?",[$c['id']]);
  if($exi) continue;
  ex($pdo,"INSERT INTO periodic_control_executions (control_id,executed_at,executed_by,outcome,notes,created_at) VALUES (?,?,?, 'conforme', ?, ?)",
    [$c['id'],$TODAY,$ITSEC,'Prima esecuzione del controllo: esito conforme. Evidenza raccolta in chiusura NC audit AUD-001.',$NOW]);
  ex($pdo,"UPDATE periodic_controls SET last_executed_at=?, next_due_date=?, updated_at=? WHERE id=?",[$TODAY,$next[$c['frequency']]??'2026-09-20',$NOW,$c['id']]);
  $pe++;
}
echo "Esecuzioni controlli: +$pe\n";

/* ---- 4) SoA: avanzamento attuazione ---------------------------------------- */
ex($pdo,"UPDATE isms_soa SET implementation_status='in_progress', implementation_pct=45, updated_at=? WHERE isms_model_id=? AND implementation_status='not_started'",[$NOW,$modelId]);
$done=['A.5.1','A.5.15','A.5.18','A.6.3','A.8.5','A.8.13','A.8.24'];
$ph=implode(',',array_fill(0,count($done),'?'));
ex($pdo,"UPDATE isms_soa SET implementation_status='implemented', implementation_pct=100, updated_at=? WHERE isms_model_id=? AND control_code IN ($ph)",array_merge([$NOW,$modelId],$done));
echo "SoA aggiornata (in_progress 45% + ".count($done)." controlli implemented 100%)\n";

/* ---- 5) DPO interim (NCR-9005 -> correcting) ------------------------------- */
$dporole=one($pdo,"SELECT id FROM org_roles WHERE organization_id=? AND role_name LIKE 'Referente Protezione Dati%'",[$ORGID]);
if($dporole){ ex($pdo,"UPDATE org_roles SET holder_user_id=?, description=CONCAT(description,' [INTERIM: responsabilita assegnata ad interim al RSGSI in attesa di nomina formale del DPO esterno]'), updated_at=? WHERE id=?",[$RSGSI,$NOW,$dporole['id']]); }
ex($pdo,"UPDATE isms_roles SET user_id=? WHERE isms_model_id=? AND role_name LIKE 'Referente Privacy%'",[$RSGSI,$modelId]);
echo "DPO: assegnato ad interim al RSGSI (in attesa di nomina formale)\n";

/* ---- 6) CAPA + chiusura NC ------------------------------------------------- */
// ncTitle => [capaTitle, action_type, capaStatus, responsible, due, completion, ncStatus, ncClose]
$plan=[
 'Determinazione di applicabilita NIS2 non formalizzata' =>
   ['Formalizzare la determinazione di applicabilita NIS2 con delibera della Direzione','corrective','planned',$PRES,'2026-07-31',null,'action_planned',null],
 'Trattamento dei rischi non documentato' =>
   ['Definire i piani di trattamento per tutti i rischi e allineare la SoA','corrective','completed',$RSGSI,'2026-09-30',$TODAY,'closed',$TODAY],
 'Formazione e consapevolezza non erogate' =>
   ['Assegnare i corsi obbligatori a tutto il personale e registrare i completamenti','corrective','completed',$RSGSI,'2026-07-31',$TODAY,'closed',$TODAY],
 'Controlli periodici non eseguiti - assenza di evidenze operative' =>
   ['Avviare l\'esecuzione dei controlli periodici e registrarne gli esiti','corrective','completed',$ITSEC,'2026-09-30',$TODAY,'closed',$TODAY],
 'Referente Protezione Dati (DPO) non assegnato' =>
   ['Nominare il Referente Protezione Dati (DPO); assegnazione ad interim al RSGSI nelle more','corrective','in_progress',$PRES,'2026-09-30',null,'correcting',null],
];
$ci=0;
foreach($plan as $ncTitle=>$x){
  [$ct,$atype,$cstatus,$resp,$due,$compl,$ncStatus,$ncClose]=$x;
  $nc=one($pdo,"SELECT id,ncr_code FROM non_conformities WHERE organization_id=? AND title=? AND source='audit'",[$ORGID,$ncTitle]);
  if(!$nc){ echo "  ! NC non trovata: $ncTitle\n"; continue; }
  $ncId=(int)$nc['id'];
  $cc='CAPA-'.substr($nc['ncr_code'],-4);
  $exc=one($pdo,"SELECT id FROM capa_actions WHERE ncr_id=? AND title=?",[$ncId,$ct]);
  if(!$exc){
    $isEff = $cstatus==='completed' ? 1 : null;
    ex($pdo,"INSERT INTO capa_actions (ncr_id,organization_id,capa_code,action_type,title,description,status,responsible_user_id,due_date,completion_date,is_effective,created_at,updated_at)
             VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
      [$ncId,$ORGID,$cc,$atype,$ct,'Azione correttiva derivante dall\'audit AUD-001.',$cstatus,$resp,$due,$compl,$isEff,$NOW,$NOW]);
    $ci++;
  }
  // aggiorna stato NC
  if($ncClose) ex($pdo,"UPDATE non_conformities SET status=?, actual_close_date=?, root_cause_analysis=?, updated_at=? WHERE id=?",
      [$ncStatus,$ncClose,'Causa radice: sistema di nuova istituzione, evidenze operative non ancora prodotte al momento dell\'audit. Azione correttiva attuata e verificata.',$NOW,$ncId]);
  else ex($pdo,"UPDATE non_conformities SET status=?, updated_at=? WHERE id=?",[$ncStatus,$NOW,$ncId]);
  echo "  NC [$ncStatus] $ncTitle -> CAPA $cc ($cstatus)\n";
}
echo "CAPA: +$ci\n";

/* ---- 7) Gap analysis completata (post-bonifica) ---------------------------- */
$ass=one($pdo,"SELECT id FROM assessments WHERE organization_id=? AND title=?",[$ORGID,'Gap Analysis iniziale NIS2 2026']);
if($ass){
  $aid=(int)$ass['id'];
  // distribuzione migliorata post-bonifica
  $pat=['implemented','partial','implemented','implemented','partial','implemented','partial','implemented','partial','implemented'];
  $v2s=['not_implemented'=>0,'partial'=>50,'implemented'=>100,'not_applicable'=>null];
  $v2m=['not_implemented'=>1,'partial'=>2,'implemented'=>4];
  $rows=all($pdo,"SELECT id,category FROM assessment_responses WHERE assessment_id=? ORDER BY id",[$aid]);
  $i=0;$catSum=[];$catN=[];$tot=0;$cnt=0;
  foreach($rows as $r){
    $rv=$pat[$i%count($pat)];$i++;
    ex($pdo,"UPDATE assessment_responses SET response_value=?, maturity_level=?, answered_at=? WHERE id=?",[$rv,$v2m[$rv],$NOW,$r['id']]);
    $catSum[$r['category']]=($catSum[$r['category']]??0)+$v2s[$rv]; $catN[$r['category']]=($catN[$r['category']]??0)+1;
    $tot+=$v2s[$rv];$cnt++;
  }
  $cat=[]; foreach($catSum as $k=>$v) $cat[$k]=round($v/$catN[$k],1);
  $overall=$cnt?round($tot/$cnt,2):0;
  ex($pdo,"UPDATE assessments SET status='completed', overall_score=?, category_scores=?, completed_by=?, completed_at=?, updated_at=? WHERE id=?",
    [$overall,json_encode($cat),$RSGSI,$NOW,$NOW,$aid]);
  echo "Gap analysis: COMPLETATA, score $overall%\n";
}
echo "\nFASE R (bonifica) OK\n";