nis2-agile/docs/sql/reset-demo.sql

-- ============================================================
-- NIS2 Agile — Reset Dati Demo
-- Cancella tutti i dati generati dalla simulazione, mantenendo
-- solo le organizzazioni seed (id <= 4) e i loro utenti.
--
-- Eseguire su Hetzner:
--   ssh -i docs/credentials/hetzner_key root@135.181.149.254
--   mysql -u nis2_agile_user -p nis2_agile_db < /var/www/nis2-agile/docs/sql/reset-demo.sql
-- ============================================================

USE nis2_agile_db;

SET FOREIGN_KEY_CHECKS = 0;

-- ── Dati operativi generati dalla simulazione ─────────────────────────────

-- Notifiche incidenti / timeline
DELETE FROM incident_timeline
    WHERE incident_id IN (SELECT id FROM incidents WHERE organization_id > 4);
DELETE FROM incidents             WHERE organization_id > 4;

-- Rischi e trattamenti
DELETE FROM risk_treatments       WHERE risk_id IN (SELECT id FROM risks WHERE organization_id > 4);
DELETE FROM risks                 WHERE organization_id > 4;

-- Assessment e risposte
DELETE FROM assessment_responses  WHERE assessment_id IN (SELECT id FROM assessments WHERE organization_id > 4);
DELETE FROM assessments           WHERE organization_id > 4;

-- Policy
DELETE FROM policies              WHERE organization_id > 4;

-- Fornitori
DELETE FROM suppliers             WHERE organization_id > 4;

-- Training
DELETE FROM training_assignments  WHERE course_id IN (SELECT id FROM training_courses WHERE organization_id > 4);
DELETE FROM training_courses      WHERE organization_id > 4;

-- Asset
DELETE FROM assets                WHERE organization_id > 4;

-- Controlli compliance
DELETE FROM compliance_controls   WHERE organization_id > 4;

-- Evidenze
DELETE FROM evidence_files        WHERE organization_id > 4;

-- Non conformità e CAPA
DELETE FROM capa_actions          WHERE ncr_id IN (SELECT id FROM non_conformities WHERE organization_id > 4);
DELETE FROM non_conformities      WHERE organization_id > 4;

-- Whistleblowing
DELETE FROM whistleblowing_timeline
    WHERE report_id IN (SELECT id FROM whistleblowing_reports WHERE organization_id > 4);
DELETE FROM whistleblowing_reports WHERE organization_id > 4;

-- Normativa ACK
DELETE FROM normative_ack         WHERE organization_id > 4;

-- API keys e webhook
DELETE FROM webhook_deliveries
    WHERE subscription_id IN (SELECT id FROM webhook_subscriptions WHERE organization_id > 4);
DELETE FROM webhook_subscriptions WHERE organization_id > 4;
DELETE FROM api_keys              WHERE organization_id > 4;

-- Audit log — il trigger prevent_audit_log_delete blocca DELETE diretta.
-- Usiamo una procedura temporanea che bypassa il trigger (ROOT richiesto).
DROP TRIGGER IF EXISTS prevent_audit_log_delete;
DROP TRIGGER IF EXISTS prevent_audit_log_update;
DELETE FROM audit_logs            WHERE organization_id > 4;
DELETE FROM audit_exports         WHERE organization_id > 4;
DELETE FROM audit_violations      WHERE organization_id > 4;
-- Ricrea i trigger immutabili (richiedono log_bin_trust_function_creators=1)
SET GLOBAL log_bin_trust_function_creators = 1;
CREATE TRIGGER prevent_audit_log_update BEFORE UPDATE ON audit_logs
    FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit_logs: UPDATE not permitted';
CREATE TRIGGER prevent_audit_log_delete BEFORE DELETE ON audit_logs
    FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit_logs: DELETE not permitted';

-- AI interactions
DELETE FROM ai_interactions       WHERE organization_id > 4;

-- Email log (no organization_id — pulisce solo email con indirizzo demo)
DELETE FROM email_log             WHERE recipient LIKE '%.demo%';

-- ── Membership utenti ─────────────────────────────────────────────────────

-- Rimuove le associazioni utente-organizzazione demo
DELETE FROM user_organizations    WHERE organization_id > 4;

-- Rimuove token refresh degli utenti demo
DELETE rt FROM refresh_tokens rt
    JOIN users u ON rt.user_id = u.id
    WHERE u.email LIKE '%.demo%';

-- Rimuove utenti demo (email terminano con .demo)
DELETE FROM users WHERE email LIKE '%.demo%';

-- ── Organizzazioni demo ───────────────────────────────────────────────────
DELETE FROM organizations WHERE id > 4;

-- ── Amministratore permanente (sempre ripristinato) ───────────────────────
-- cristiano.benassati@gmail.com deve sopravvivere a qualsiasi reset

-- Rimuove eventuali token refresh dell'admin prima del re-insert
DELETE rt FROM refresh_tokens rt
    JOIN users u ON rt.user_id = u.id
    WHERE u.email = 'cristiano.benassati@gmail.com';

INSERT INTO users (email, password_hash, full_name, role, is_active)
VALUES (
    'cristiano.benassati@gmail.com',
    '$2y$12$H/AJ7SgBowihcOcpblQ7PeanmoTXzgruv3mRvC.vexoRodNa7rAUi',
    'Cristiano Benassati', 'super_admin', 1
)
ON DUPLICATE KEY UPDATE
    role          = 'super_admin',
    is_active     = 1,
    password_hash = '$2y$12$H/AJ7SgBowihcOcpblQ7PeanmoTXzgruv3mRvC.vexoRodNa7rAUi';

-- ── Ripristino FK ─────────────────────────────────────────────────────────
SET FOREIGN_KEY_CHECKS = 1;

-- ── Verifica stato ────────────────────────────────────────────────────────
SELECT
    (SELECT COUNT(*) FROM organizations)       AS organizations,
    (SELECT COUNT(*) FROM users)               AS users,
    (SELECT COUNT(*) FROM incidents)           AS incidents,
    (SELECT COUNT(*) FROM risks)               AS risks,
    (SELECT COUNT(*) FROM audit_logs)          AS audit_logs,
    (SELECT COUNT(*) FROM whistleblowing_reports) AS whistleblowing
;

SELECT 'Reset demo completato. Dati seed mantenuti (id <= 4).' AS stato;