nis2-agile/application/cli/migrate_059_requisito_policy.php

<?php
/**
 * migrate_059_requisito_policy.php — Ticket #384 (p.2.2 + p.3): modello
 * relazionale REQUISITO <-> POLICY. Runner IDEMPOTENTE e GUARDATO (PDO app, TLS).
 *
 * TUTTO ADDITIVO (nessun DROP/DELETE):
 *   1) policies.proc_code VARCHAR(16) NULL  (chiave di join verso il catalogo)
 *   2) tabella requisito_policy (M:N org-scoped requisito<->policy)
 *   3) backfill SICURO: proc_code per match esatto title==proc_descr +
 *      requisito_policy dalle associazioni di default esistenti (INSERT IGNORE).
 *
 * Uso:
 *   docker exec nis2-app php .../application/cli/migrate_059_requisito_policy.php
 *   ... --dry-run
 */
if (PHP_SAPI !== 'cli') { http_response_code(403); exit("CLI only\n"); }
require_once __DIR__ . '/../config/env.php';
require_once __DIR__ . '/../config/database.php';

$DRY = in_array('--dry-run', $argv, true);
$pdo = Database::getInstance();
$db  = $pdo->query('SELECT DATABASE()')->fetchColumn();
echo "mig.059 — schema=$db | dry-run=" . ($DRY ? 'SI' : 'no') . "\n\n";

function col1(PDO $p, $s, $a = []) { $st = $p->prepare($s); $st->execute($a); return $st->fetchColumn(); }
function colExists(PDO $p, $db, $t, $c) { return (int)col1($p, "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND COLUMN_NAME=?", [$db, $t, $c]) > 0; }
function tblExists(PDO $p, $db, $t) { return (int)col1($p, "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=? AND TABLE_NAME=?", [$db, $t]) > 0; }

$applied = 0; $skipped = 0; $failed = 0;
function run(PDO $pdo, bool $DRY, string $label, string $sql) {
    global $applied, $skipped, $failed;
    if ($DRY) { echo "  [DRY] $label\n        $sql\n"; return; }
    try { $pdo->exec($sql); echo "  [OK ] $label\n"; $applied++; }
    catch (\PDOException $e) {
        $c = (int)($e->errorInfo[1] ?? 0);
        if (in_array($c, [1060, 1061, 1826, 1022, 1062, 1050], true)) { echo "  [SKIP] $label (gia' presente, code=$c)\n"; $skipped++; }
        else { echo "  [FAIL] $label -> " . $e->getMessage() . "\n"; $failed++; }
    }
}

/* ── 1) policies.proc_code ── */
echo "── 1) policies.proc_code ──\n";
if (colExists($pdo, $db, 'policies', 'proc_code')) { echo "  [SKIP] policies.proc_code gia' presente\n"; $skipped++; }
else run($pdo, $DRY, 'policies ADD COLUMN proc_code VARCHAR(16) NULL',
    "ALTER TABLE policies ADD COLUMN proc_code VARCHAR(16) NULL AFTER nis2_article");

/* ── 2) tabella requisito_policy ── */
echo "\n── 2) tabella requisito_policy ──\n";
if (tblExists($pdo, $db, 'requisito_policy')) { echo "  [SKIP] requisito_policy gia' presente\n"; $skipped++; }
else run($pdo, $DRY, 'CREATE TABLE requisito_policy',
    "CREATE TABLE requisito_policy (
        id INT NOT NULL AUTO_INCREMENT,
        organization_id INT NOT NULL,
        requisito_id INT NOT NULL,
        policy_id INT NOT NULL,
        is_default TINYINT(1) NOT NULL DEFAULT 0,
        created_by INT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY uq_req_pol (organization_id, requisito_id, policy_id),
        KEY idx_rp_org (organization_id),
        KEY idx_rp_req (requisito_id),
        KEY idx_rp_pol (policy_id),
        CONSTRAINT fk_rp_org  FOREIGN KEY (organization_id) REFERENCES organizations (id)      ON DELETE CASCADE,
        CONSTRAINT fk_rp_req  FOREIGN KEY (requisito_id)    REFERENCES cfg_nis2_requisiti (id) ON DELETE CASCADE,
        CONSTRAINT fk_rp_pol  FOREIGN KEY (policy_id)       REFERENCES policies (id)           ON DELETE CASCADE,
        CONSTRAINT fk_rp_user FOREIGN KEY (created_by)      REFERENCES users (id)              ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

/* ── 3) backfill SICURO (additivo) ── */
echo "\n── 3) backfill (additivo, idempotente) ──\n";
if (!$DRY && colExists($pdo, $db, 'policies', 'proc_code') && tblExists($pdo, $db, 'cfg_nis2_procedure')) {
    // 3a) proc_code per match esatto del titolo (title = mb_substr(proc_descr,0,255))
    $n1 = $pdo->exec("UPDATE policies p
                        JOIN cfg_nis2_procedure c ON p.title = c.proc_descr
                         SET p.proc_code = c.proc_code
                       WHERE p.proc_code IS NULL");
    echo "  [OK ] proc_code valorizzato su $n1 policy (match esatto titolo)\n"; $applied++;

    // 3b) requisito_policy dalle associazioni di default (per ogni proc_code)
    if (tblExists($pdo, $db, 'requisito_policy') && tblExists($pdo, $db, 'cfg_nis2_requisiti')) {
        $n2 = $pdo->exec("INSERT IGNORE INTO requisito_policy (organization_id, requisito_id, policy_id, is_default)
                          SELECT p.organization_id, q.id, p.id, 1
                            FROM policies p
                            JOIN cfg_nis2_requisiti q ON q.proc_code = p.proc_code
                           WHERE p.proc_code IS NOT NULL");
        echo "  [OK ] requisito_policy popolata: $n2 associazioni di default\n"; $applied++;
    }
} else {
    echo "  [SKIP] backfill (dry-run o prerequisiti assenti)\n"; $skipped++;
}

/* ── verifica finale ── */
echo "\n── VERIFICA ──\n";
if (!$DRY) {
    $cWith = (int)col1($pdo, "SELECT COUNT(*) FROM policies WHERE proc_code IS NOT NULL");
    $cTot  = (int)col1($pdo, "SELECT COUNT(*) FROM policies");
    echo "  policies con proc_code: $cWith / $cTot\n";
    if (tblExists($pdo, $db, 'requisito_policy')) {
        $cLinks = (int)col1($pdo, "SELECT COUNT(*) FROM requisito_policy");
        echo "  requisito_policy righe: $cLinks\n";
    }
}

echo "\n── RIEPILOGO ── applicati=$applied skip=$skipped falliti=$failed\n";
echo ($failed === 0 ? "ESITO OK\n" : "ESITO: rivedere i [FAIL]\n");
exit($failed === 0 ? 0 : 1);