-- Migration 014: NIST Special Publications seed data
-- Frameworks: NIST SP 800-37, 800-30, 800-34, 800-61, 800-63, 800-115, 800-137, 800-172,
--             NIST Privacy Framework, NIST SSDF (SP 800-218)

SET @n80037   = 'a1b2c3d4-0014-0001-0001-000000000001';
SET @n80030   = 'a1b2c3d4-0014-0002-0001-000000000001';
SET @n80034   = 'a1b2c3d4-0014-0003-0001-000000000001';
SET @n80061   = 'a1b2c3d4-0014-0004-0001-000000000001';
SET @n80063   = 'a1b2c3d4-0014-0005-0001-000000000001';
SET @n80115   = 'a1b2c3d4-0014-0006-0001-000000000001';
SET @n80137   = 'a1b2c3d4-0014-0007-0001-000000000001';
SET @n80172   = 'a1b2c3d4-0014-0008-0001-000000000001';
SET @nistpf   = 'a1b2c3d4-0014-0009-0001-000000000001';
SET @ssdf     = 'a1b2c3d4-0014-0010-0001-000000000001';

-- ─── Frameworks ──────────────────────────────────────────────────────────────

INSERT IGNORE INTO regulatory_frameworks
  (id, code, name, description, version, issuing_body, is_global, effective_date)
VALUES
  (@n80037, 'NIST SP 800-37',
   'NIST SP 800-37 Rev 2 — Risk Management Framework (RMF)',
   'Provides guidelines for applying the Risk Management Framework to federal information systems, covering seven steps from preparation through continuous monitoring.',
   'Rev 2', 'NIST', TRUE, '2018-12-20'),

  (@n80030, 'NIST SP 800-30',
   'NIST SP 800-30 Rev 1 — Guide for Conducting Risk Assessments',
   'Provides guidance for conducting risk assessments of federal information systems and organizations, covering all four steps of the risk assessment process.',
   'Rev 1', 'NIST', TRUE, '2012-09-01'),

  (@n80034, 'NIST SP 800-34',
   'NIST SP 800-34 Rev 1 — Contingency Planning Guide for Federal Information Systems',
   'Provides instructions, recommendations, and considerations for federal information system contingency planning, including business impact analysis and recovery strategies.',
   'Rev 1', 'NIST', TRUE, '2010-05-01'),

  (@n80061, 'NIST SP 800-61',
   'NIST SP 800-61 Rev 2 — Computer Security Incident Handling Guide',
   'Assists organizations in establishing computer security incident response capabilities and handling incidents efficiently and effectively.',
   'Rev 2', 'NIST', TRUE, '2012-08-01'),

  (@n80063, 'NIST SP 800-63',
   'NIST SP 800-63-3 — Digital Identity Guidelines',
   'Provides technical requirements for federal agencies implementing digital identity services, covering identity proofing, authentication, and federation across three volumes.',
   'Rev 3', 'NIST', TRUE, '2017-06-22'),

  (@n80115, 'NIST SP 800-115',
   'NIST SP 800-115 — Technical Guide to Information Security Testing and Assessment',
   'Provides guidance on planning, conducting, and reporting on security testing and assessment activities for federal systems.',
   '2008', 'NIST', TRUE, '2008-09-01'),

  (@n80137, 'NIST SP 800-137',
   'NIST SP 800-137 — Information Security Continuous Monitoring (ISCM)',
   'Defines a six-step process for establishing an information security continuous monitoring program for federal information systems and organizations.',
   '2011', 'NIST', TRUE, '2011-09-01'),

  (@n80172, 'NIST SP 800-172',
   'NIST SP 800-172 — Enhanced Security Requirements for Protecting CUI',
   'Provides enhanced security requirements for protecting Controlled Unclassified Information in nonfederal systems against advanced persistent threats.',
   '2021', 'NIST', TRUE, '2021-02-02'),

  (@nistpf, 'NIST Privacy Framework',
   'NIST Privacy Framework 1.0 — A Tool for Improving Privacy Through Enterprise Risk Management',
   'Voluntary framework that enables organizations to better identify and manage privacy risk to build innovative products and services while protecting individuals'' privacy.',
   '1.0', 'NIST', TRUE, '2020-01-16'),

  (@ssdf, 'NIST SP 800-218',
   'NIST SP 800-218 — Secure Software Development Framework (SSDF)',
   'Provides a core set of high-level secure software development practices to integrate security into each SDLC implementation, grouped into four practice groups.',
   '1.1', 'NIST', TRUE, '2022-02-03');


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST SP 800-37 — Risk Management Framework (7 Steps)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @n80037, 'RMF-P',  'Step 0: Prepare',
 'Carry out essential activities at the organization and system levels to help prepare the organization to manage security and privacy risks using the RMF.',
 'mandatory', 'RMF Steps', 10),
(UUID(), @n80037, 'RMF-1',  'Step 1: Categorize',
 'Categorize the system and the information processed, stored, and transmitted by the system based on an impact analysis.',
 'mandatory', 'RMF Steps', 20),
(UUID(), @n80037, 'RMF-2',  'Step 2: Select',
 'Select, tailor, and document the controls necessary to protect the system and the organization based on risk assessment.',
 'mandatory', 'RMF Steps', 30),
(UUID(), @n80037, 'RMF-3',  'Step 3: Implement',
 'Implement the controls and document how the controls are deployed within the system and its environment of operation.',
 'mandatory', 'RMF Steps', 40),
(UUID(), @n80037, 'RMF-4',  'Step 4: Assess',
 'Assess the controls to determine if they are implemented correctly, operating as intended, and producing the desired outcome.',
 'mandatory', 'RMF Steps', 50),
(UUID(), @n80037, 'RMF-5',  'Step 5: Authorize',
 'Authorize the system or common controls based on a determination that the risk is acceptable; a senior official accepts responsibility.',
 'mandatory', 'RMF Steps', 60),
(UUID(), @n80037, 'RMF-6',  'Step 6: Monitor',
 'Monitor the system and the associated controls on an ongoing basis to assess control effectiveness and changes in risk.',
 'mandatory', 'RMF Steps', 70);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST SP 800-30 — Risk Assessment (4 Steps)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @n80030, 'RA-P',   'Step 1: Prepare for Risk Assessment',
 'Establish the context for the risk assessment including scope, assumptions, constraints, sources of threat and vulnerability information, and risk model.',
 'mandatory', 'Risk Assessment Steps', 10),
(UUID(), @n80030, 'RA-C',   'Step 2: Conduct the Risk Assessment',
 'Identify threat sources and events, identify vulnerabilities, determine likelihood, determine magnitude of impact, and determine risk.',
 'mandatory', 'Risk Assessment Steps', 20),
(UUID(), @n80030, 'RA-COM', 'Step 3: Communicate Risk Assessment Results',
 'Communicate the risk assessment results and share risk-related information with key organizational stakeholders.',
 'mandatory', 'Risk Assessment Steps', 30),
(UUID(), @n80030, 'RA-M',   'Step 4: Maintain the Risk Assessment',
 'Maintain the risk assessment on an ongoing basis by monitoring risk factors and updating when significant changes occur.',
 'mandatory', 'Risk Assessment Steps', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST SP 800-34 — Contingency Planning (7 Steps)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @n80034, 'CP-1',  'Contingency Planning Policy and Procedures',
 'Develop an organizational contingency planning policy and procedures that address purpose, scope, roles, responsibilities, management commitment, and compliance.',
 'mandatory', 'Contingency Planning', 10),
(UUID(), @n80034, 'CP-2',  'Business Impact Analysis (BIA)',
 'Identify critical systems and functions, determine outage impacts and acceptable recovery time objectives (RTO) and recovery point objectives (RPO).',
 'mandatory', 'Contingency Planning', 20),
(UUID(), @n80034, 'CP-3',  'Preventive Controls',
 'Implement preventive measures to reduce the effects of system disruptions and increase system availability.',
 'recommended', 'Contingency Planning', 30),
(UUID(), @n80034, 'CP-4',  'Recovery Strategies',
 'Develop recovery strategies aligned with system criticality and the BIA, addressing backup sites, telecommunications, and equipment replacement.',
 'mandatory', 'Contingency Planning', 40),
(UUID(), @n80034, 'CP-5',  'Contingency Plan Development',
 'Develop a formal, written contingency plan identifying essential functions, resources, interoperability requirements, and detailed recovery procedures.',
 'mandatory', 'Contingency Planning', 50),
(UUID(), @n80034, 'CP-6',  'Plan Testing and Exercises',
 'Test the contingency plan to evaluate the plan''s effectiveness and the organization''s readiness to execute it; update based on results.',
 'mandatory', 'Contingency Planning', 60),
(UUID(), @n80034, 'CP-7',  'Plan Maintenance',
 'Update the contingency plan to address changes to the organization, information system, or environment, and problems encountered during testing or plan activation.',
 'mandatory', 'Contingency Planning', 70);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST SP 800-61 — Incident Response (4 Phases)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @n80061, 'IR-1',  'Phase 1: Preparation',
 'Establish an incident response capability including policy, plan, procedures, team structure, communication guidelines, and necessary tools and resources.',
 'mandatory', 'IR Phases', 10),
(UUID(), @n80061, 'IR-2',  'Phase 2: Detection and Analysis',
 'Detect, alert, and report on potential incidents; analyze precursors and indicators; prioritize incident handling based on impact.',
 'mandatory', 'IR Phases', 20),
(UUID(), @n80061, 'IR-3',  'Phase 3: Containment, Eradication, and Recovery',
 'Contain the incident to prevent further damage, eradicate the root cause, and recover affected systems to normal operation.',
 'mandatory', 'IR Phases', 30),
(UUID(), @n80061, 'IR-4',  'Phase 4: Post-Incident Activity',
 'Conduct lessons-learned meetings, produce incident reports, retain evidence, update policies and procedures, and track recommendations for improvement.',
 'mandatory', 'IR Phases', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST SP 800-63 — Digital Identity (3 Volumes)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @n80063, '800-63A',  'Volume A: Enrollment and Identity Proofing',
 'Defines requirements for identity proofing at three assurance levels (IAL1, IAL2, IAL3), covering remote and in-person proofing, document verification, and biometric collection.',
 'mandatory', 'Identity Volumes', 10),
(UUID(), @n80063, '800-63B',  'Volume B: Authentication and Lifecycle Management',
 'Defines requirements for authenticators and authentication protocols at three assurance levels (AAL1, AAL2, AAL3), covering passwords, OTPs, cryptographic devices, and biometrics.',
 'mandatory', 'Identity Volumes', 20),
(UUID(), @n80063, '800-63C',  'Volume C: Federation and Assertions',
 'Defines requirements for federation protocols and assertions at three assurance levels (FAL1, FAL2, FAL3), covering identity provider, relying party, and assertion binding requirements.',
 'mandatory', 'Identity Volumes', 30);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST SP 800-115 — Security Testing (4 Sections)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @n80115, 'ST-PL',  'Planning',
 'Define the scope, objectives, rules of engagement, team roles, and schedule for the security assessment; obtain approvals and coordinate logistics.',
 'mandatory', 'Testing Phases', 10),
(UUID(), @n80115, 'ST-DI',  'Discovery',
 'Gather information about the target environment through network scanning, enumeration, vulnerability scanning, and wireless testing without active exploitation.',
 'mandatory', 'Testing Phases', 20),
(UUID(), @n80115, 'ST-AT',  'Attack Phase',
 'Attempt to exploit discovered vulnerabilities to demonstrate exploitability and evaluate the actual risk, including password cracking, penetration testing, and social engineering.',
 'recommended', 'Testing Phases', 30),
(UUID(), @n80115, 'ST-RP',  'Reporting',
 'Document findings in a security assessment report covering methodology, results, risk ratings, and prioritized recommendations for remediation.',
 'mandatory', 'Testing Phases', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST SP 800-137 — Continuous Monitoring (6 Steps)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @n80137, 'ISCM-1',  'Define',
 'Define an ISCM strategy that addresses risk tolerance, metrics, frequency of monitoring, and the types of information required by organizational officials.',
 'mandatory', 'ISCM Steps', 10),
(UUID(), @n80137, 'ISCM-2',  'Establish',
 'Establish an ISCM program determining metrics, monitoring and assessment frequencies, and the ISCM technical architecture.',
 'mandatory', 'ISCM Steps', 20),
(UUID(), @n80137, 'ISCM-3',  'Implement',
 'Implement the ISCM program by collecting security-related information required for ongoing authorizations and reporting.',
 'mandatory', 'ISCM Steps', 30),
(UUID(), @n80137, 'ISCM-4',  'Analyze and Report',
 'Analyze the monitoring data and report findings to enable timely risk management decisions by authorizing officials and other stakeholders.',
 'mandatory', 'ISCM Steps', 40),
(UUID(), @n80137, 'ISCM-5',  'Respond',
 'Respond to findings with technically sound and fiscally responsible corrective actions, updating security plans and risk assessments as needed.',
 'mandatory', 'ISCM Steps', 50),
(UUID(), @n80137, 'ISCM-6',  'Review and Update',
 'Review the ISCM strategy and program annually and update based on changes in risk tolerance, threats, vulnerabilities, mission, or organizational priorities.',
 'mandatory', 'ISCM Steps', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST SP 800-172 — Enhanced CUI Protection (15 Domains)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @n80172, '3.1',   'Access Control',
 'Employ enhanced access control mechanisms including attribute-based access control and dynamic access decisions to protect CUI against APT.',
 'mandatory', 'Security Domains', 10),
(UUID(), @n80172, '3.2',   'Awareness and Training',
 'Provide advanced security awareness training and role-based training covering insider threat indicators and APT tactics, techniques, and procedures.',
 'mandatory', 'Security Domains', 20),
(UUID(), @n80172, '3.3',   'Audit and Accountability',
 'Implement enhanced audit logging and correlation capabilities to detect, analyze, and respond to anomalous activity indicative of advanced threats.',
 'mandatory', 'Security Domains', 30),
(UUID(), @n80172, '3.4',   'Configuration Management',
 'Employ advanced configuration management practices including CIS benchmarks, automated configuration checking, and configuration drift detection.',
 'mandatory', 'Security Domains', 40),
(UUID(), @n80172, '3.5',   'Identification and Authentication',
 'Implement strong authentication mechanisms including hardware-based multi-factor authentication and privileged access workstations for CUI systems.',
 'mandatory', 'Security Domains', 50),
(UUID(), @n80172, '3.6',   'Incident Response',
 'Develop advanced incident response capabilities to detect, respond to, and recover from security incidents involving CUI, including APT-specific playbooks.',
 'mandatory', 'Security Domains', 60),
(UUID(), @n80172, '3.7',   'Maintenance',
 'Sanitize equipment before maintenance and verify the integrity of tools and personnel before allowing maintenance activities on CUI systems.',
 'mandatory', 'Security Domains', 70),
(UUID(), @n80172, '3.8',   'Media Protection',
 'Implement enhanced media protection controls including controlled interfaces, media sanitization, and encryption for all CUI stored on removable media.',
 'mandatory', 'Security Domains', 80),
(UUID(), @n80172, '3.9',   'Personnel Security',
 'Screen individuals prior to authorizing access to CUI and enforce separation of duties to reduce risk of unauthorized access or insider threat.',
 'mandatory', 'Security Domains', 90),
(UUID(), @n80172, '3.10',  'Physical Protection',
 'Protect CUI systems with advanced physical security controls including access monitoring, visitor control, and protection of physical assets.',
 'mandatory', 'Security Domains', 100),
(UUID(), @n80172, '3.11',  'Risk Assessment',
 'Conduct comprehensive risk assessments with threat modeling specific to APT targeting of CUI and update continuously based on intelligence.',
 'mandatory', 'Security Domains', 110),
(UUID(), @n80172, '3.12',  'Security Assessment',
 'Conduct periodic security assessments and penetration testing to evaluate effectiveness of enhanced security requirements for CUI protection.',
 'mandatory', 'Security Domains', 120),
(UUID(), @n80172, '3.13',  'System and Communications Protection',
 'Architect systems to achieve visibility and include isolation boundaries to provide protection against advanced threats targeting CUI.',
 'mandatory', 'Security Domains', 130),
(UUID(), @n80172, '3.14',  'System and Information Integrity',
 'Employ advanced threat intelligence, intrusion detection, and security information and event management capabilities to detect and respond to APT.',
 'mandatory', 'Security Domains', 140),
(UUID(), @n80172, '3.15',  'Supply Chain Risk Management',
 'Protect against supply chain risks from adversarial insertion of counterfeits, malicious software, or backdoors into CUI-related systems and components.',
 'mandatory', 'Security Domains', 150);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST Privacy Framework 1.0 — 5 Functions
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @nistpf, 'ID-P',   'Identify-P',
 'Develop organizational understanding to manage privacy risks for individuals arising from data processing, including inventory of data processing activities and risk assessment.',
 'mandatory', 'Privacy Framework Functions', 10),
(UUID(), @nistpf, 'GV-P',   'Govern-P',
 'Develop and implement organizational governance structures, policies, processes, and procedures to manage privacy risks and meet legal, regulatory, and contractual obligations.',
 'mandatory', 'Privacy Framework Functions', 20),
(UUID(), @nistpf, 'CT-P',   'Control-P',
 'Develop and implement appropriate activities to enable organizations and individuals to manage data with sufficient granularity to manage privacy risks.',
 'mandatory', 'Privacy Framework Functions', 30),
(UUID(), @nistpf, 'CM-P',   'Communicate-P',
 'Develop and implement appropriate activities to enable organizations and individuals to have a reliable understanding and engage in discourse about how data is processed.',
 'recommended', 'Privacy Framework Functions', 40),
(UUID(), @nistpf, 'PR-P',   'Protect-P',
 'Develop and implement safeguards for data processing to prevent cybersecurity-related privacy events that affect individuals'' privacy interests.',
 'mandatory', 'Privacy Framework Functions', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST SP 800-218 — SSDF (4 Practice Groups)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @ssdf, 'PO',  'Prepare the Organization (PO)',
 'Ensure the organization has the people, processes, and technology ready to perform secure software development at the organization level.',
 'mandatory', 'SSDF Practice Groups', 10),
(UUID(), @ssdf, 'PS',  'Protect the Software (PS)',
 'Protect all components of the software from tampering and unauthorized access throughout the software development lifecycle.',
 'mandatory', 'SSDF Practice Groups', 20),
(UUID(), @ssdf, 'PW',  'Produce Well-Secured Software (PW)',
 'Produce well-secured software with minimal security vulnerabilities in its release by applying security practices at each stage of development.',
 'mandatory', 'SSDF Practice Groups', 30),
(UUID(), @ssdf, 'RV',  'Respond to Vulnerabilities (RV)',
 'Identify residual vulnerabilities in software releases and respond appropriately to address those vulnerabilities, including coordinated disclosure.',
 'mandatory', 'SSDF Practice Groups', 40);
