<?php
/**
 * NIS2 Agile — DDL idempotente Audit interni (Modulo A, mig.055).
 * ----------------------------------------------------------------------------
 * Crea (se mancanti) le tabelle del modulo Audit interni (ISO 27001 §9.2):
 *   - internal_audits        (programma/sessioni di audit)
 *   - internal_audit_items   (checklist: clausole 4-10, Annex A, NIS2, custom)
 * NESSUN dato di sistema: le checklist sono pre-popolate per-org dal controller
 * al create (clausole statiche + Annex A dal SoA dell'org).
 *
 * Idempotente: CREATE TABLE IF NOT EXISTS (re-eseguibile senza errore).
 * Allineato a docs/sql/055_internal_audits.sql.
 *
 * Uso (dentro il container app):
 *   docker exec nis2-app php /var/www/nis2-agile/application/cli/seed_internal_audits.php
 */

if (PHP_SAPI !== 'cli') { http_response_code(403); exit("Solo da CLI.\n"); }

require_once __DIR__ . '/../config/env.php';
require_once __DIR__ . '/../config/database.php';

$pdo = Database::getInstance();
$pdo->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");

$ddl = [
"CREATE TABLE IF NOT EXISTS internal_audits (
  id INT NOT NULL AUTO_INCREMENT, organization_id INT NOT NULL,
  code VARCHAR(20) NULL, title VARCHAR(255) NOT NULL,
  scope TEXT NULL, criteria TEXT NULL,
  planned_date DATE NULL, executed_date DATE NULL,
  status ENUM('planned','in_progress','completed','cancelled') NOT NULL DEFAULT 'planned',
  lead_auditor_user_id INT NULL, lead_auditor_role_id INT NULL,
  conclusion TEXT NULL, created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), KEY idx_intaud_org (organization_id),
  CONSTRAINT fk_intaud_org FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE,
  CONSTRAINT fk_intaud_lead_user FOREIGN KEY (lead_auditor_user_id) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT fk_intaud_lead_role FOREIGN KEY (lead_auditor_role_id) REFERENCES org_roles (id) ON DELETE SET NULL,
  CONSTRAINT fk_intaud_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

"CREATE TABLE IF NOT EXISTS internal_audit_items (
  id INT NOT NULL AUTO_INCREMENT, audit_id INT NOT NULL,
  ref_type ENUM('clause','annex_control','nis2_measure','custom') NOT NULL DEFAULT 'clause',
  ref_code VARCHAR(32) NULL, checkpoint TEXT NOT NULL,
  result ENUM('da_verificare','conforme','non_conforme','osservazione','opportunita','non_applicabile') NOT NULL DEFAULT 'da_verificare',
  note TEXT NULL, ord INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id), KEY idx_intauditem_audit (audit_id),
  CONSTRAINT fk_intauditem_audit FOREIGN KEY (audit_id) REFERENCES internal_audits (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
];

foreach ($ddl as $stmt) {
    try { $pdo->exec($stmt); }
    catch (PDOException $e) { if (!in_array($e->errorInfo[1] ?? 0, [1050, 1061], true)) { throw $e; } }
}

// Estende l'ENUM del calendario (review_schedule) con 'internal_audit' così la
// data pianificata dell'audit compare nel calendario NIS2. Idempotente in effetto.
// Include i valori già introdotti da seeder precedenti (stakeholder_activity) per
// non regredirli; se 'internal_audit' è già presente l'ALTER è un no-op.
try {
    $cur = (string) $pdo->query("SELECT COLUMN_TYPE FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'review_schedule' AND COLUMN_NAME = 'entity_type'")->fetchColumn();
    if (!str_contains($cur, "'internal_audit'")) {
        $vals = ['role','skill','inventory','procedure','risk','supplier','measure','custom','stakeholder_activity','internal_audit'];
        // conserva eventuali valori extra già presenti nell'ENUM corrente
        if (preg_match_all("/'([^']+)'/", $cur, $m)) {
            foreach ($m[1] as $v) { if (!in_array($v, $vals, true)) { $vals[] = $v; } }
        }
        $enum = "'" . implode("','", $vals) . "'";
        $pdo->exec("ALTER TABLE review_schedule MODIFY COLUMN entity_type ENUM($enum) NOT NULL");
    }
} catch (PDOException $e) {
    fwrite(STDERR, "WARN ALTER review_schedule: " . $e->getMessage() . "\n");
}

$counts = [];
foreach (['internal_audits', 'internal_audit_items'] as $t) {
    $counts[$t] = (int) $pdo->query("SELECT COUNT(*) FROM $t")->fetchColumn();
}
$enum = $pdo->query("SELECT COLUMN_TYPE FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'review_schedule' AND COLUMN_NAME = 'entity_type'")->fetchColumn();
echo "OK seed-internal-audits — " . json_encode($counts, JSON_UNESCAPED_UNICODE) . "\n";
echo "review_schedule.entity_type has internal_audit: " . (str_contains((string) $enum, 'internal_audit') ? 'YES' : 'NO') . "\n";