-- Extended compliance framework seed: 18 additional frameworks

SET @cis8       = 'd4a9ebef-b1ab-489b-baa4-274cc840171e';
SET @cobit      = '873f7da5-f3c7-4c43-8888-ed277d15a204';
SET @iso27701   = '98ee5a53-635c-42eb-b435-8b2fa846ef2e';
SET @iso22301   = '666252f0-47b7-440a-82d9-cca6878f6b60';
SET @csaccm     = '3d1d4686-65fe-4478-8dcd-38f250e9c757';
SET @gdpr       = '161ca661-dcfc-41d6-ae04-4d515194f119';
SET @ccpa       = '375b1394-c9fa-449f-b9d0-d58b9769e5aa';
SET @nis2       = '0872b646-3c71-4b1d-96fd-c577f5e02943';
SET @dora       = 'c504c77f-bddc-4dea-8403-aa2ea3dcf9df';
SET @n80053     = '0b2dd67e-d51c-4158-8a54-e8a7df801f33';
SET @n800171    = 'e89f5ae1-655d-4355-853f-d70e68259598';
SET @cmmc       = '88665a55-9ddc-4c83-b223-b5314fc3d39c';
SET @fedramp    = 'affe804b-ffcb-4957-a6f8-d013bf745160';
SET @fisma      = '906a1b55-4849-4909-a7ab-5a4c70f730f8';
SET @nerccip    = 'e23cdd62-bdad-439b-9a8d-92c7312d3864';
SET @swiftcsp   = 'eec44c85-6639-4f22-bdea-7f33976d22ab';
SET @soxitgc    = '50db3692-94cc-48b4-91a5-a7adde83bf85';
SET @hitrust    = '06b057d3-fe17-42e1-92e3-5e3292cf8880';

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

INSERT IGNORE INTO regulatory_frameworks
  (id, code, name, description, version, issuing_body, is_global, effective_date)
VALUES
  (@cis8,     'CIS Controls', 'CIS Critical Security Controls v8',
   '18 prioritized safeguards that mitigate the most prevalent cyber attacks against systems and networks.',
   'v8', 'Center for Internet Security', TRUE, '2021-05-18'),

  (@cobit,    'COBIT 2019',   'COBIT 2019',
   'Framework for IT governance and management, providing principles, practices and analytical tools.',
   '2019', 'ISACA', TRUE, '2018-11-01'),

  (@iso27701, 'ISO 27701',    'ISO/IEC 27701:2019 — Privacy Information Management',
   'Extension to ISO 27001/27002 for privacy information management, aligned with GDPR.',
   '2019', 'ISO/IEC', TRUE, '2019-08-06'),

  (@iso22301, 'ISO 22301',    'ISO 22301:2019 — Business Continuity Management',
   'International standard for business continuity management systems (BCMS).',
   '2019', 'ISO', TRUE, '2019-10-31'),

  (@csaccm,   'CSA CCM',      'Cloud Security Alliance Cloud Controls Matrix v4',
   '17 security domains covering key cloud security principles for cloud vendors and enterprises.',
   'v4.0', 'Cloud Security Alliance', TRUE, '2021-03-01'),

  (@gdpr,     'GDPR',         'EU General Data Protection Regulation',
   'Regulation on data protection and privacy for individuals within the European Union and EEA.',
   '2018', 'European Parliament', FALSE, '2018-05-25'),

  (@ccpa,     'CCPA/CPRA',    'California Consumer Privacy Act / California Privacy Rights Act',
   'California state law giving consumers rights over their personal information.',
   '2023', 'State of California', FALSE, '2023-01-01'),

  (@nis2,     'NIS2',         'EU Network and Information Security Directive 2',
   'EU directive on measures for a high common level of cybersecurity across the Union.',
   '2022', 'European Parliament', FALSE, '2024-10-17'),

  (@dora,     'DORA',         'EU Digital Operational Resilience Act',
   'EU regulation on digital operational resilience for the financial sector.',
   '2022', 'European Parliament', FALSE, '2025-01-17'),

  (@n80053,   'NIST 800-53',  'NIST SP 800-53 Rev 5 — Security and Privacy Controls',
   'Comprehensive catalog of security and privacy controls for federal information systems.',
   'Rev 5', 'NIST', TRUE, '2020-09-23'),

  (@n800171,  'NIST 800-171', 'NIST SP 800-171 Rev 2 — Protecting CUI',
   '110 security requirements for protecting Controlled Unclassified Information in nonfederal systems.',
   'Rev 2', 'NIST', TRUE, '2021-02-02'),

  (@cmmc,     'CMMC 2.0',     'Cybersecurity Maturity Model Certification 2.0',
   'DoD framework for protecting the defense industrial base supply chain across 3 maturity levels.',
   '2.0', 'US Department of Defense', FALSE, '2021-11-04'),

  (@fedramp,  'FedRAMP',      'Federal Risk and Authorization Management Program',
   'US government program providing standardized approach to security assessment for cloud services.',
   '2023', 'GSA / FedRAMP PMO', FALSE, '2012-06-06'),

  (@fisma,    'FISMA',        'Federal Information Security Modernization Act',
   'US federal law requiring federal agencies to develop, implement and maintain information security programs.',
   '2014', 'US Congress / OMB', FALSE, '2014-12-18'),

  (@nerccip,  'NERC CIP',     'NERC Critical Infrastructure Protection Standards',
   'Reliability standards for protecting the North American bulk electric system from cyber threats.',
   'CIP-013-2', 'NERC', FALSE, '2020-07-01'),

  (@swiftcsp, 'SWIFT CSP',    'SWIFT Customer Security Programme',
   'Security controls framework for SWIFT member institutions to protect the financial messaging network.',
   '2023', 'SWIFT', TRUE, '2023-01-01'),

  (@soxitgc,  'SOX ITGC',     'Sarbanes-Oxley IT General Controls',
   'IT controls required for SOX compliance covering financial reporting systems and data integrity.',
   '2002', 'US Congress / PCAOB', FALSE, '2002-07-30'),

  (@hitrust,  'HITRUST CSF',  'HITRUST Common Security Framework',
   'Healthcare industry framework combining HIPAA, ISO 27001, NIST, PCI DSS and other standards.',
   'v11', 'HITRUST Alliance', FALSE, '2023-01-01');


-- ═══════════════════════════════════════════════════════════════════════════════
-- CIS CONTROLS v8
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @cis8, 'CIS-1',  'Inventory and Control of Enterprise Assets',
 'Actively manage all enterprise assets connected to the infrastructure to know what to defend.',
 'mandatory', 'Basic Hygiene', 10),
(UUID(), @cis8, 'CIS-2',  'Inventory and Control of Software Assets',
 'Actively manage all software on the network to prevent unauthorized software from being installed.',
 'mandatory', 'Basic Hygiene', 20),
(UUID(), @cis8, 'CIS-3',  'Data Protection',
 'Develop processes and technical controls to identify, classify, securely handle, retain, and dispose of data.',
 'mandatory', 'Basic Hygiene', 30),
(UUID(), @cis8, 'CIS-4',  'Secure Configuration of Enterprise Assets and Software',
 'Establish and maintain the secure configuration of enterprise assets and software.',
 'mandatory', 'Basic Hygiene', 40),
(UUID(), @cis8, 'CIS-5',  'Account Management',
 'Use processes and tools to assign and manage authorization to credentials for user accounts.',
 'mandatory', 'Basic Hygiene', 50),
(UUID(), @cis8, 'CIS-6',  'Access Control Management',
 'Use processes and tools to create, assign, manage, and revoke access credentials and privileges.',
 'mandatory', 'Basic Hygiene', 60),
(UUID(), @cis8, 'CIS-7',  'Continuous Vulnerability Management',
 'Develop a plan to continuously assess and track vulnerabilities on all enterprise assets.',
 'mandatory', 'Foundational', 70),
(UUID(), @cis8, 'CIS-8',  'Audit Log Management',
 'Collect, alert, review, and retain audit logs of events that could help detect or recover from an attack.',
 'mandatory', 'Foundational', 80),
(UUID(), @cis8, 'CIS-9',  'Email and Web Browser Protections',
 'Improve protections and detections of threats from email and web vectors.',
 'mandatory', 'Foundational', 90),
(UUID(), @cis8, 'CIS-10', 'Malware Defenses',
 'Prevent or control the installation, spread, and execution of malicious applications.',
 'mandatory', 'Foundational', 100),
(UUID(), @cis8, 'CIS-11', 'Data Recovery',
 'Establish and maintain data recovery practices to restore in-scope assets to a pre-incident state.',
 'mandatory', 'Foundational', 110),
(UUID(), @cis8, 'CIS-12', 'Network Infrastructure Management',
 'Establish, implement, and actively manage network devices to prevent attackers from exploiting services.',
 'mandatory', 'Foundational', 120),
(UUID(), @cis8, 'CIS-13', 'Network Monitoring and Defense',
 'Operate processes and tooling to establish and maintain comprehensive network monitoring.',
 'mandatory', 'Foundational', 130),
(UUID(), @cis8, 'CIS-14', 'Security Awareness and Skills Training',
 'Establish and maintain a security awareness program to influence behavior among the workforce.',
 'mandatory', 'Foundational', 140),
(UUID(), @cis8, 'CIS-15', 'Service Provider Management',
 'Develop a process to evaluate service providers who hold sensitive data or are responsible for IT platforms.',
 'mandatory', 'Organizational', 150),
(UUID(), @cis8, 'CIS-16', 'Application Software Security',
 'Manage the security life cycle of in-house developed, hosted, or acquired software.',
 'mandatory', 'Organizational', 160),
(UUID(), @cis8, 'CIS-17', 'Incident Response Management',
 'Establish a program to develop and maintain an incident response capability.',
 'mandatory', 'Organizational', 170),
(UUID(), @cis8, 'CIS-18', 'Penetration Testing',
 'Test the effectiveness and resiliency of enterprise assets through identifying and exploiting weaknesses.',
 'mandatory', 'Organizational', 180);


-- ═══════════════════════════════════════════════════════════════════════════════
-- COBIT 2019
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @cobit, 'EDM01', 'Ensured Governance Framework Setting and Maintenance',
 'Analyze and articulate the requirements for governance of enterprise IT and establish and maintain effective governance.',
 'mandatory', 'Governance', 10),
(UUID(), @cobit, 'EDM02', 'Ensured Benefits Delivery',
 'Secure optimal value from IT-enabled initiatives, services and assets.',
 'mandatory', 'Governance', 20),
(UUID(), @cobit, 'EDM03', 'Ensured Risk Optimization',
 'Ensure that the enterprise''s risk appetite and tolerance are understood and that IT-related enterprise risk is identified.',
 'mandatory', 'Governance', 30),
(UUID(), @cobit, 'EDM04', 'Ensured Resource Optimization',
 'Ensure that adequate and sufficient IT-related capabilities are available to support enterprise objectives.',
 'mandatory', 'Governance', 40),
(UUID(), @cobit, 'EDM05', 'Ensured Stakeholder Engagement',
 'Ensure that enterprise IT stakeholder communication and reporting is effective and timely.',
 'mandatory', 'Governance', 50),
(UUID(), @cobit, 'APO01', 'Managed I&T Management Framework',
 'Clarify and maintain the governance of enterprise I&T management by implementing a management framework.',
 'mandatory', 'Management', 60),
(UUID(), @cobit, 'APO02', 'Managed Strategy',
 'Provide a holistic view of current business and IT environment, future direction, and initiatives.',
 'mandatory', 'Management', 70),
(UUID(), @cobit, 'APO03', 'Managed Enterprise Architecture',
 'Represent the different building blocks that make up the enterprise and their interrelationships.',
 'mandatory', 'Management', 80),
(UUID(), @cobit, 'APO06', 'Managed Budget and Costs',
 'Manage IT-related financial activities in business and IT functions covering budget, cost, and benefit management.',
 'mandatory', 'Management', 90),
(UUID(), @cobit, 'APO07', 'Managed Human Resources',
 'Provide a structured approach to ensure optimal structuring, placement, decision rights and skills of human resources.',
 'mandatory', 'Management', 100),
(UUID(), @cobit, 'APO10', 'Managed Vendors',
 'Manage IT-related services provided by all types of vendors to meet enterprise requirements.',
 'mandatory', 'Management', 110),
(UUID(), @cobit, 'APO12', 'Managed Risk',
 'Continually identify, assess and reduce IT-related risk within levels of tolerance set by enterprise executive management.',
 'mandatory', 'Management', 120),
(UUID(), @cobit, 'APO14', 'Managed Data',
 'Understand and manage the planning and design of data to improve quality of decision making and business agility.',
 'mandatory', 'Management', 130),
(UUID(), @cobit, 'BAI01', 'Managed Programs',
 'Manage all programs in investment portfolio in alignment with the enterprise strategy.',
 'mandatory', 'Build & Acquire', 140),
(UUID(), @cobit, 'BAI03', 'Managed Solutions Identification and Build',
 'Establish and maintain identified solutions in line with enterprise requirements covering design, development, and integration.',
 'mandatory', 'Build & Acquire', 150),
(UUID(), @cobit, 'BAI06', 'Managed IT Changes',
 'Manage all changes in a controlled manner including standard and emergency changes to business processes.',
 'mandatory', 'Build & Acquire', 160),
(UUID(), @cobit, 'BAI10', 'Managed Configuration',
 'Define and maintain descriptions and relationships between key resources and capabilities required to deliver IT services.',
 'mandatory', 'Build & Acquire', 170),
(UUID(), @cobit, 'DSS01', 'Managed Operations',
 'Coordinate and execute activities and operational procedures required to deliver internal and outsourced IT services.',
 'mandatory', 'Deliver & Support', 180),
(UUID(), @cobit, 'DSS02', 'Managed Service Requests and Incidents',
 'Provide timely and effective response to user requests and resolution of all types of incidents.',
 'mandatory', 'Deliver & Support', 190),
(UUID(), @cobit, 'DSS04', 'Managed Continuity',
 'Establish and maintain a plan to enable the business and IT to respond to incidents and disruptions.',
 'mandatory', 'Deliver & Support', 200),
(UUID(), @cobit, 'DSS05', 'Managed Security Services',
 'Protect enterprise information to maintain the level of information security risk acceptable to the enterprise.',
 'mandatory', 'Deliver & Support', 210),
(UUID(), @cobit, 'MEA01', 'Managed Performance and Conformance Monitoring',
 'Collect, validate and evaluate business, IT and process goals and metrics.',
 'mandatory', 'Monitor & Evaluate', 220),
(UUID(), @cobit, 'MEA02', 'Managed System of Internal Control',
 'Continuously monitor and evaluate the control environment including self-assessments and independent assurance reviews.',
 'mandatory', 'Monitor & Evaluate', 230),
(UUID(), @cobit, 'MEA04', 'Managed Assurance',
 'Obtain assurance about whether IT-related enterprise risk is within acceptable levels.',
 'mandatory', 'Monitor & Evaluate', 240);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO 27701 (Privacy Information Management)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @iso27701, '5.1',   'PIMS-specific Requirements for ISO 27001 Context',
 'Understand the organization and its context including data subjects and privacy obligations.',
 'mandatory', 'ISMS Extension', 10),
(UUID(), @iso27701, '5.2',   'Leadership and Privacy Commitment',
 'Top management shall demonstrate leadership and commitment with respect to the PIMS.',
 'mandatory', 'ISMS Extension', 20),
(UUID(), @iso27701, '5.3',   'Privacy Policies and Objectives',
 'Establish a privacy policy and privacy objectives consistent with legal obligations.',
 'mandatory', 'ISMS Extension', 30),
(UUID(), @iso27701, '6.1',   'Privacy Risk Assessment',
 'Plan and implement processes for privacy impact assessments and risk treatment.',
 'mandatory', 'Privacy Planning', 40),
(UUID(), @iso27701, '7.1',   'Conditions for Collection and Processing',
 'Determine and document the legal basis for processing personal data.',
 'mandatory', 'Controller Guidance', 50),
(UUID(), @iso27701, '7.2',   'Obligations to PII Principals',
 'Provide privacy notice, obtain consent, and support data subject rights requests.',
 'mandatory', 'Controller Guidance', 60),
(UUID(), @iso27701, '7.3',   'Privacy by Design and Default',
 'Integrate privacy controls into system design and apply data minimization principles.',
 'mandatory', 'Controller Guidance', 70),
(UUID(), @iso27701, '7.4',   'PII Sharing, Transfer and Disclosure',
 'Ensure lawful basis for transfers to third parties and cross-border data transfers.',
 'mandatory', 'Controller Guidance', 80),
(UUID(), @iso27701, '8.2',   'Processor Conditions and Obligations',
 'Process personal data only under documented instructions from a controller.',
 'mandatory', 'Processor Guidance', 90),
(UUID(), @iso27701, '8.3',   'Privacy by Design for Processors',
 'Implement technical and organizational measures to protect PII in processor systems.',
 'mandatory', 'Processor Guidance', 100),
(UUID(), @iso27701, '8.4',   'PII Principals Rights Support',
 'Provide mechanisms for processors to support controller obligations to data subjects.',
 'mandatory', 'Processor Guidance', 110),
(UUID(), @iso27701, '8.5',   'Sub-processor Management',
 'Ensure sub-processors meet equivalent privacy and security obligations.',
 'mandatory', 'Processor Guidance', 120),
(UUID(), @iso27701, 'A.7',   'Extended Guidance — ISO 27002 Privacy Controls',
 'Apply privacy-specific guidance to ISO 27002 controls covering human resources, asset management, and access.',
 'recommended', 'Annex A', 130),
(UUID(), @iso27701, 'A.8',   'Extended Guidance — Technological Controls for Privacy',
 'Technical privacy controls including pseudonymization, encryption, and privacy-preserving technologies.',
 'recommended', 'Annex A', 140);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO 22301 (Business Continuity)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @iso22301, 'Cl.4',  'Context of the Organization',
 'Understand the organization, its stakeholders, and determine the scope of the BCMS.',
 'mandatory', 'Foundation', 10),
(UUID(), @iso22301, 'Cl.5',  'Leadership',
 'Top management shall demonstrate commitment to the BCMS and assign roles and responsibilities.',
 'mandatory', 'Foundation', 20),
(UUID(), @iso22301, 'Cl.6',  'Planning',
 'Determine risks and opportunities, establish BC objectives, and plan actions to achieve them.',
 'mandatory', 'Foundation', 30),
(UUID(), @iso22301, 'Cl.7',  'Support',
 'Determine and provide resources, competence, awareness, communication and documented information.',
 'mandatory', 'Foundation', 40),
(UUID(), @iso22301, '8.2',   'Business Impact Analysis',
 'Identify functions critical to delivery of products/services and assess the impact of disruption.',
 'mandatory', 'Operations', 50),
(UUID(), @iso22301, '8.3',   'Business Continuity Risk Assessment',
 'Identify, analyze, and evaluate risks of disruption to prioritized activities.',
 'mandatory', 'Operations', 60),
(UUID(), @iso22301, '8.4',   'Business Continuity Strategy and Solutions',
 'Determine appropriate strategies for stabilization, continuity and recovery of prioritized activities.',
 'mandatory', 'Operations', 70),
(UUID(), @iso22301, '8.5',   'Business Continuity Plans and Procedures',
 'Document plans and procedures to manage disruptions and restore business activities.',
 'mandatory', 'Operations', 80),
(UUID(), @iso22301, '8.6',   'Exercising and Testing',
 'Test and exercise BCMS plans and procedures to validate their effectiveness.',
 'mandatory', 'Operations', 90),
(UUID(), @iso22301, 'Cl.9',  'Performance Evaluation',
 'Monitor, measure, analyse and evaluate BCMS performance through internal audits and management review.',
 'mandatory', 'Improvement', 100),
(UUID(), @iso22301, 'Cl.10', 'Improvement',
 'Identify nonconformities, take corrective actions, and continually improve the BCMS.',
 'mandatory', 'Improvement', 110),
(UUID(), @iso22301, 'RTO',   'Recovery Time Objectives',
 'Define and test RTO and RPO for all critical systems and business processes.',
 'mandatory', 'Operations', 120),
(UUID(), @iso22301, 'Crisis','Crisis Communication Plan',
 'Establish internal and external communication procedures for crisis and disruption events.',
 'mandatory', 'Operations', 130);


-- ═══════════════════════════════════════════════════════════════════════════════
-- CSA CCM v4
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @csaccm, 'A&A',   'Audit and Assurance',
 'Independent reviews of cloud security controls, policies, and compliance status.',
 'mandatory', 'Governance', 10),
(UUID(), @csaccm, 'AIS',   'Application and Interface Security',
 'Secure design, development, deployment, and testing of applications and APIs.',
 'mandatory', 'Application Security', 20),
(UUID(), @csaccm, 'BCR',   'Business Continuity Management and Operational Resilience',
 'Planning, implementation and testing of business continuity and disaster recovery capabilities.',
 'mandatory', 'Resilience', 30),
(UUID(), @csaccm, 'CCC',   'Change Control and Configuration Management',
 'Policies and procedures for managing changes to cloud systems and configurations.',
 'mandatory', 'Operations', 40),
(UUID(), @csaccm, 'CEK',   'Cryptography, Encryption and Key Management',
 'Policies for the use of cryptographic controls and management of cryptographic keys.',
 'mandatory', 'Data Protection', 50),
(UUID(), @csaccm, 'DSP',   'Data Security and Privacy Lifecycle Management',
 'Classification, handling, retention, and secure disposal of data throughout its lifecycle.',
 'mandatory', 'Data Protection', 60),
(UUID(), @csaccm, 'GRC',   'Governance, Risk and Compliance',
 'Establish governance structures, manage risk, and maintain compliance with applicable regulations.',
 'mandatory', 'Governance', 70),
(UUID(), @csaccm, 'HRS',   'Human Resources',
 'Security awareness, background verification, and access revocation for personnel.',
 'mandatory', 'People', 80),
(UUID(), @csaccm, 'IAM',   'Identity and Access Management',
 'Manage user identities, credentials, and access rights using least privilege principles.',
 'mandatory', 'Access Control', 90),
(UUID(), @csaccm, 'IPY',   'Interoperability and Portability',
 'Ensure data portability and interoperability between cloud providers and on-premises systems.',
 'recommended', 'Architecture', 100),
(UUID(), @csaccm, 'IVS',   'Infrastructure and Virtualization Security',
 'Protect hypervisors, virtual machines, containers, and cloud infrastructure components.',
 'mandatory', 'Infrastructure', 110),
(UUID(), @csaccm, 'LOG',   'Logging and Monitoring',
 'Implement comprehensive logging, monitoring, and alerting across cloud environments.',
 'mandatory', 'Operations', 120),
(UUID(), @csaccm, 'SEF',   'Security Incident Management, E-Discovery and Cloud Forensics',
 'Detect, report, and respond to security incidents including forensic capabilities.',
 'mandatory', 'Incident Response', 130),
(UUID(), @csaccm, 'STA',   'Supply Chain Management, Transparency and Accountability',
 'Evaluate and manage cloud supply chain risk and third-party security posture.',
 'mandatory', 'Third-party Risk', 140),
(UUID(), @csaccm, 'TVM',   'Threat and Vulnerability Management',
 'Identify, prioritize, and remediate vulnerabilities and monitor threat intelligence.',
 'mandatory', 'Vulnerability Management', 150),
(UUID(), @csaccm, 'UEM',   'Universal Endpoint Management',
 'Manage and secure all endpoint devices accessing cloud services including mobile and IoT.',
 'mandatory', 'Endpoint Security', 160);


-- ═══════════════════════════════════════════════════════════════════════════════
-- GDPR
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @gdpr, 'Art.5',   'Principles Relating to Processing of Personal Data',
 'Personal data shall be processed lawfully, fairly, transparently, for specified purposes, minimised, accurate, retained only as long as necessary, and processed securely.',
 'mandatory', 'Core Principles', 10),
(UUID(), @gdpr, 'Art.6',   'Lawfulness of Processing',
 'Processing shall be lawful only if at least one legal basis applies: consent, contract, legal obligation, vital interests, public task, or legitimate interests.',
 'mandatory', 'Core Principles', 20),
(UUID(), @gdpr, 'Art.7',   'Conditions for Consent',
 'Where processing is based on consent, demonstrate that the data subject has consented; consent must be freely given, specific, informed and unambiguous.',
 'mandatory', 'Core Principles', 30),
(UUID(), @gdpr, 'Art.12',  'Transparent Information and Communication',
 'Provide information to data subjects in a concise, transparent, intelligible and accessible form.',
 'mandatory', 'Data Subject Rights', 40),
(UUID(), @gdpr, 'Art.13',  'Information to be Provided at Collection',
 'Provide privacy notice at the time of data collection including controller identity, purposes, and legal basis.',
 'mandatory', 'Data Subject Rights', 50),
(UUID(), @gdpr, 'Art.15',  'Right of Access by Data Subject',
 'Data subjects have the right to obtain confirmation of processing and access to their personal data.',
 'mandatory', 'Data Subject Rights', 60),
(UUID(), @gdpr, 'Art.16',  'Right to Rectification',
 'Data subjects have the right to obtain rectification of inaccurate personal data without undue delay.',
 'mandatory', 'Data Subject Rights', 70),
(UUID(), @gdpr, 'Art.17',  'Right to Erasure (Right to be Forgotten)',
 'Data subjects have the right to obtain erasure of personal data without undue delay under certain conditions.',
 'mandatory', 'Data Subject Rights', 80),
(UUID(), @gdpr, 'Art.20',  'Right to Data Portability',
 'Data subjects have the right to receive personal data in a structured, commonly used, machine-readable format.',
 'mandatory', 'Data Subject Rights', 90),
(UUID(), @gdpr, 'Art.21',  'Right to Object',
 'Data subjects have the right to object at any time to processing based on legitimate interests or direct marketing.',
 'mandatory', 'Data Subject Rights', 100),
(UUID(), @gdpr, 'Art.25',  'Data Protection by Design and by Default',
 'Implement technical and organisational measures for data protection by design and by default including data minimisation.',
 'mandatory', 'Security', 110),
(UUID(), @gdpr, 'Art.28',  'Processor Obligations',
 'Only use processors that provide sufficient guarantees; implement controller-processor contracts.',
 'mandatory', 'Third-party Risk', 120),
(UUID(), @gdpr, 'Art.30',  'Records of Processing Activities',
 'Maintain a record of all processing activities including purposes, categories of data, retention periods, and security measures.',
 'mandatory', 'Governance', 130),
(UUID(), @gdpr, 'Art.32',  'Security of Processing',
 'Implement appropriate technical and organisational measures to ensure a level of security appropriate to the risk including encryption, resilience, and testing.',
 'mandatory', 'Security', 140),
(UUID(), @gdpr, 'Art.33',  'Notification of Personal Data Breach to Supervisory Authority',
 'Notify the supervisory authority of a personal data breach within 72 hours of becoming aware.',
 'mandatory', 'Incident Response', 150),
(UUID(), @gdpr, 'Art.34',  'Communication of Personal Data Breach to Data Subject',
 'Communicate a high-risk personal data breach to affected data subjects without undue delay.',
 'mandatory', 'Incident Response', 160),
(UUID(), @gdpr, 'Art.35',  'Data Protection Impact Assessment',
 'Carry out a DPIA prior to processing likely to result in high risk to the rights and freedoms of natural persons.',
 'mandatory', 'Risk Assessment', 170),
(UUID(), @gdpr, 'Art.37',  'Designation of Data Protection Officer',
 'Designate a DPO where required (public authority, large-scale systematic monitoring, or processing of special category data).',
 'mandatory', 'Governance', 180),
(UUID(), @gdpr, 'Art.44',  'International Data Transfers',
 'Transfers of personal data to third countries shall only take place with adequate safeguards in place.',
 'mandatory', 'Data Transfers', 190);


-- ═══════════════════════════════════════════════════════════════════════════════
-- CCPA / CPRA
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @ccpa, '§1798.100', 'Right to Know About Personal Information Collected',
 'Consumers have the right to request disclosure of personal information collected, used, disclosed, or sold.',
 'mandatory', 'Consumer Rights', 10),
(UUID(), @ccpa, '§1798.105', 'Right to Delete Personal Information',
 'Consumers have the right to request deletion of personal information collected from them.',
 'mandatory', 'Consumer Rights', 20),
(UUID(), @ccpa, '§1798.106', 'Right to Correct Inaccurate Personal Information',
 'Consumers have the right to request correction of inaccurate personal information.',
 'mandatory', 'Consumer Rights', 30),
(UUID(), @ccpa, '§1798.110', 'Right to Know Categories of Personal Information',
 'Disclose categories of personal information collected, sources, business purposes, and third parties.',
 'mandatory', 'Consumer Rights', 40),
(UUID(), @ccpa, '§1798.120', 'Right to Opt-Out of Sale or Sharing',
 'Consumers have the right to opt-out of the sale or sharing of their personal information.',
 'mandatory', 'Consumer Rights', 50),
(UUID(), @ccpa, '§1798.121', 'Right to Limit Use of Sensitive Personal Information',
 'Consumers have the right to limit the use and disclosure of sensitive personal information.',
 'mandatory', 'Consumer Rights', 60),
(UUID(), @ccpa, '§1798.125', 'Right to Non-Discrimination',
 'Businesses shall not discriminate against a consumer for exercising their CCPA rights.',
 'mandatory', 'Consumer Rights', 70),
(UUID(), @ccpa, '§1798.130', 'Privacy Notice Requirements',
 'Provide a privacy policy disclosing categories of PI collected, purposes, consumer rights, and contact information.',
 'mandatory', 'Transparency', 80),
(UUID(), @ccpa, '§1798.135', 'Opt-Out Mechanism',
 'Provide a clear and conspicuous "Do Not Sell or Share My Personal Information" link.',
 'mandatory', 'Transparency', 90),
(UUID(), @ccpa, '§1798.150', 'Data Security Requirements',
 'Implement and maintain reasonable security procedures and practices appropriate to the nature of the personal information.',
 'mandatory', 'Security', 100),
(UUID(), @ccpa, '§1798.185', 'Privacy Risk Assessment',
 'Conduct cybersecurity audits and submit privacy risk assessments for high-risk processing activities.',
 'mandatory', 'Governance', 110),
(UUID(), @ccpa, 'CPRA-Reg',  'CPRA Enforcement Regulations',
 'Comply with CPPA regulations on data minimization, purpose limitation, and retention.',
 'mandatory', 'Governance', 120);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIS2 Directive
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @nis2, 'Art.20',  'Cybersecurity Governance',
 'Management bodies of essential and important entities must approve, oversee, and be trained on cybersecurity risk-management measures.',
 'mandatory', 'Governance', 10),
(UUID(), @nis2, 'Art.21a', 'Policies on Risk Analysis and Information System Security',
 'Adopt policies on risk analysis and information security covering all assets and processes.',
 'mandatory', 'Risk Management', 20),
(UUID(), @nis2, 'Art.21b', 'Incident Handling',
 'Implement incident handling procedures including detection, analysis, containment, and recovery.',
 'mandatory', 'Incident Response', 30),
(UUID(), @nis2, 'Art.21c', 'Business Continuity',
 'Maintain business continuity including backup management, disaster recovery, and crisis management.',
 'mandatory', 'Resilience', 40),
(UUID(), @nis2, 'Art.21d', 'Supply Chain Security',
 'Manage security of the supply chain including relationships with direct suppliers and service providers.',
 'mandatory', 'Third-party Risk', 50),
(UUID(), @nis2, 'Art.21e', 'Secure Acquisition, Development and Maintenance',
 'Apply security in development including vulnerability handling and disclosure policies.',
 'mandatory', 'Secure Development', 60),
(UUID(), @nis2, 'Art.21f', 'Cybersecurity Risk Management Effectiveness',
 'Implement policies and procedures to assess the effectiveness of cybersecurity measures.',
 'mandatory', 'Governance', 70),
(UUID(), @nis2, 'Art.21g', 'Cyber Hygiene and Training',
 'Establish basic cyber hygiene practices and provide cybersecurity training.',
 'mandatory', 'Awareness', 80),
(UUID(), @nis2, 'Art.21h', 'Cryptography and Encryption',
 'Implement policies on the use of cryptography and, where appropriate, encryption.',
 'mandatory', 'Technical Controls', 90),
(UUID(), @nis2, 'Art.21i', 'Human Resources Security and Access Control',
 'Implement human resources security measures and access control policies including MFA.',
 'mandatory', 'Access Control', 100),
(UUID(), @nis2, 'Art.21j', 'Asset Management',
 'Maintain an asset management framework covering hardware, software, and data assets.',
 'mandatory', 'Asset Management', 110),
(UUID(), @nis2, 'Art.23',  'Reporting Obligations',
 'Report significant incidents to national CSIRT or competent authority within 24 hours (early warning) and 72 hours (notification).',
 'mandatory', 'Incident Reporting', 120),
(UUID(), @nis2, 'Art.32',  'Supervisory Measures for Essential Entities',
 'Cooperate with competent authorities on audits, inspections, and security scans.',
 'mandatory', 'Compliance', 130);


-- ═══════════════════════════════════════════════════════════════════════════════
-- DORA (Digital Operational Resilience Act)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @dora, 'Art.5',   'ICT Risk Management Framework',
 'Establish and maintain a sound, comprehensive, and well-documented ICT risk management framework.',
 'mandatory', 'ICT Risk Management', 10),
(UUID(), @dora, 'Art.6',   'ICT Risk Management Systems and Controls',
 'Identify, classify, and document ICT assets; protect against ICT risks using appropriate controls.',
 'mandatory', 'ICT Risk Management', 20),
(UUID(), @dora, 'Art.7',   'ICT Systems, Protocols and Tools',
 'Use and maintain updated ICT systems, protocols, and tools that minimize ICT risk impact.',
 'mandatory', 'ICT Risk Management', 30),
(UUID(), @dora, 'Art.8',   'Identification of ICT Risk',
 'Continuously identify all sources of ICT risk and assess cyber threats and ICT vulnerabilities.',
 'mandatory', 'ICT Risk Management', 40),
(UUID(), @dora, 'Art.9',   'Protection and Prevention',
 'Implement ICT security policies, procedures, and controls to protect confidentiality, integrity, and availability.',
 'mandatory', 'ICT Risk Management', 50),
(UUID(), @dora, 'Art.10',  'Detection',
 'Implement mechanisms to promptly detect anomalous activities including ICT network performance issues.',
 'mandatory', 'ICT Risk Management', 60),
(UUID(), @dora, 'Art.11',  'Response and Recovery',
 'Establish dedicated management policies for ICT-related incidents and implement response and recovery plans.',
 'mandatory', 'ICT Risk Management', 70),
(UUID(), @dora, 'Art.12',  'Backup Policies and Recovery Procedures',
 'Implement backup policies and recovery procedures with specific RTO and RPO for critical functions.',
 'mandatory', 'ICT Risk Management', 80),
(UUID(), @dora, 'Art.17',  'ICT-related Incident Management Process',
 'Define, establish, and implement an ICT-related incident management process.',
 'mandatory', 'Incident Management', 90),
(UUID(), @dora, 'Art.19',  'Reporting of Major ICT-related Incidents',
 'Report major ICT-related incidents to competent authorities within prescribed timeframes.',
 'mandatory', 'Incident Reporting', 100),
(UUID(), @dora, 'Art.24',  'Digital Operational Resilience Testing Programme',
 'Establish, maintain, and review a digital operational resilience testing programme.',
 'mandatory', 'TLPT', 110),
(UUID(), @dora, 'Art.25',  'Testing of ICT Tools and Systems',
 'Conduct threat-led penetration testing (TLPT) at least every 3 years for critical functions.',
 'mandatory', 'TLPT', 120),
(UUID(), @dora, 'Art.28',  'General Principles of ICT Third-party Risk',
 'Manage ICT third-party risk as integral component of the ICT risk management framework.',
 'mandatory', 'Third-party Risk', 130),
(UUID(), @dora, 'Art.29',  'ICT Third-party Risk Management Policy',
 'Adopt a strategy on ICT third-party risk and maintain a register of all ICT third-party service providers.',
 'mandatory', 'Third-party Risk', 140),
(UUID(), @dora, 'Art.45',  'Information Sharing Arrangements',
 'May exchange cyber threat information and intelligence in trusted communities of financial entities.',
 'optional', 'Information Sharing', 150);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST SP 800-53 Rev 5
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @n80053, 'AC',  'Access Control',
 'Limit information system access to authorized users, processes acting on behalf of users, and devices. Enforce least privilege and separation of duties.',
 'mandatory', 'Technical Controls', 10),
(UUID(), @n80053, 'AT',  'Awareness and Training',
 'Ensure that personnel are aware of security risks and trained to carry out their responsibilities.',
 'mandatory', 'People', 20),
(UUID(), @n80053, 'AU',  'Audit and Accountability',
 'Create, protect, and retain system audit records to enable monitoring, analysis, investigation, and reporting of unlawful, unauthorized activity.',
 'mandatory', 'Technical Controls', 30),
(UUID(), @n80053, 'CA',  'Assessment, Authorization and Monitoring',
 'Periodically assess security controls; ensure risks from system operation are acceptable; monitor controls on an ongoing basis.',
 'mandatory', 'Governance', 40),
(UUID(), @n80053, 'CM',  'Configuration Management',
 'Establish and maintain baseline configurations; document and control changes; enforce security configuration settings.',
 'mandatory', 'Technical Controls', 50),
(UUID(), @n80053, 'CP',  'Contingency Planning',
 'Establish, maintain, and effectively implement plans for emergency response, backup operations, and post-disaster recovery.',
 'mandatory', 'Resilience', 60),
(UUID(), @n80053, 'IA',  'Identification and Authentication',
 'Identify and authenticate users, processes, and devices before allowing access to organizational systems.',
 'mandatory', 'Technical Controls', 70),
(UUID(), @n80053, 'IR',  'Incident Response',
 'Establish operational incident-handling capability including preparation, detection, analysis, containment, recovery, and reporting.',
 'mandatory', 'Incident Response', 80),
(UUID(), @n80053, 'MA',  'Maintenance',
 'Perform maintenance on organizational systems; control maintenance tools, activities, personnel, and remotely performed maintenance.',
 'mandatory', 'Operations', 90),
(UUID(), @n80053, 'MP',  'Media Protection',
 'Protect system media, limit access to information on media, and sanitize or destroy media before disposal.',
 'mandatory', 'Data Protection', 100),
(UUID(), @n80053, 'PE',  'Physical and Environmental Protection',
 'Limit physical access to systems; protect the physical facility and support infrastructure.',
 'mandatory', 'Physical Security', 110),
(UUID(), @n80053, 'PL',  'Planning',
 'Develop security plans; establish rules of behavior for system users.',
 'mandatory', 'Governance', 120),
(UUID(), @n80053, 'PM',  'Program Management',
 'Implement program management controls to oversee the information security program at the organizational level.',
 'mandatory', 'Governance', 130),
(UUID(), @n80053, 'PS',  'Personnel Security',
 'Ensure that individuals in positions of responsibility are trustworthy; establish personnel termination and transfer procedures.',
 'mandatory', 'People', 140),
(UUID(), @n80053, 'PT',  'PII Processing and Transparency',
 'Implement controls for processing personally identifiable information; provide appropriate transparency.',
 'mandatory', 'Privacy', 150),
(UUID(), @n80053, 'RA',  'Risk Assessment',
 'Assess the risk to organizational operations, organizational assets, individuals, and other organizations.',
 'mandatory', 'Risk Management', 160),
(UUID(), @n80053, 'SA',  'System and Services Acquisition',
 'Allocate resources to protect organizational systems; employ system development life cycle processes.',
 'mandatory', 'Governance', 170),
(UUID(), @n80053, 'SC',  'System and Communications Protection',
 'Monitor, control, and protect organizational communications at external and key internal boundaries.',
 'mandatory', 'Technical Controls', 180),
(UUID(), @n80053, 'SI',  'System and Information Integrity',
 'Identify, report, and correct information system flaws; protect systems from malicious code.',
 'mandatory', 'Technical Controls', 190),
(UUID(), @n80053, 'SR',  'Supply Chain Risk Management',
 'Establish a risk-based approach to protecting against supply chain risks to organizational systems and components.',
 'mandatory', 'Third-party Risk', 200);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST SP 800-171 Rev 2
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @n800171, '3.1',  'Access Control',
 'Limit system access to authorized users and the transactions and functions that authorized users are permitted to execute. 22 requirements.',
 'mandatory', 'Technical Controls', 10),
(UUID(), @n800171, '3.2',  'Awareness and Training',
 'Ensure that personnel are aware of security risks associated with their activities and trained to carry out duties. 3 requirements.',
 'mandatory', 'People', 20),
(UUID(), @n800171, '3.3',  'Audit and Accountability',
 'Create and retain system audit records to enable monitoring, analysis, investigation of unlawful activity. 9 requirements.',
 'mandatory', 'Technical Controls', 30),
(UUID(), @n800171, '3.4',  'Configuration Management',
 'Establish and maintain baseline configurations; track, review, approve/disapprove changes. 9 requirements.',
 'mandatory', 'Technical Controls', 40),
(UUID(), @n800171, '3.5',  'Identification and Authentication',
 'Identify system users, processes, and devices; authenticate identities before allowing access. 11 requirements.',
 'mandatory', 'Technical Controls', 50),
(UUID(), @n800171, '3.6',  'Incident Response',
 'Establish operational incident-handling capability including preparation, detection, analysis, containment. 3 requirements.',
 'mandatory', 'Incident Response', 60),
(UUID(), @n800171, '3.7',  'Maintenance',
 'Perform maintenance on organizational systems; provide controls on the tools, techniques, and personnel. 6 requirements.',
 'mandatory', 'Operations', 70),
(UUID(), @n800171, '3.8',  'Media Protection',
 'Protect system media containing CUI, both paper and digital. 9 requirements.',
 'mandatory', 'Data Protection', 80),
(UUID(), @n800171, '3.9',  'Personnel Security',
 'Screen individuals prior to authorizing access to systems containing CUI. 2 requirements.',
 'mandatory', 'People', 90),
(UUID(), @n800171, '3.10', 'Physical Protection',
 'Limit physical access to organizational systems, equipment, and operating environments. 6 requirements.',
 'mandatory', 'Physical Security', 100),
(UUID(), @n800171, '3.11', 'Risk Assessment',
 'Periodically assess the risk to organizational operations, assets, and individuals. 3 requirements.',
 'mandatory', 'Risk Management', 110),
(UUID(), @n800171, '3.12', 'Security Assessment',
 'Periodically assess security controls; develop and implement plans of action to correct deficiencies. 4 requirements.',
 'mandatory', 'Governance', 120),
(UUID(), @n800171, '3.13', 'System and Communications Protection',
 'Monitor, control, and protect communications at external boundaries and key internal boundaries. 16 requirements.',
 'mandatory', 'Technical Controls', 130),
(UUID(), @n800171, '3.14', 'System and Information Integrity',
 'Identify, report, and correct system flaws; provide protection from malicious code; monitor alerts. 7 requirements.',
 'mandatory', 'Technical Controls', 140);


-- ═══════════════════════════════════════════════════════════════════════════════
-- CMMC 2.0
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @cmmc, 'L1-AC',  'Access Control (Level 1)',
 'Limit system access to authorized users, processes acting on behalf of users, and devices. 2 practices.',
 'mandatory', 'Level 1 – Foundational', 10),
(UUID(), @cmmc, 'L1-IA',  'Identification and Authentication (Level 1)',
 'Identify and authenticate system users and devices. 2 practices.',
 'mandatory', 'Level 1 – Foundational', 20),
(UUID(), @cmmc, 'L1-MP',  'Media Protection (Level 1)',
 'Sanitize or destroy information system media before disposal or reuse. 1 practice.',
 'mandatory', 'Level 1 – Foundational', 30),
(UUID(), @cmmc, 'L1-PE',  'Physical Protection (Level 1)',
 'Limit physical access to systems and equipment. 4 practices.',
 'mandatory', 'Level 1 – Foundational', 40),
(UUID(), @cmmc, 'L1-SC',  'System and Communications Protection (Level 1)',
 'Monitor, control, and protect communications. 2 practices.',
 'mandatory', 'Level 1 – Foundational', 50),
(UUID(), @cmmc, 'L1-SI',  'System and Information Integrity (Level 1)',
 'Identify and manage information system flaws; use malicious code protection. 3 practices.',
 'mandatory', 'Level 1 – Foundational', 60),
(UUID(), @cmmc, 'L2-AC',  'Access Control (Level 2)',
 'Implement all NIST 800-171 access control requirements. 22 practices.',
 'mandatory', 'Level 2 – Advanced', 70),
(UUID(), @cmmc, 'L2-AT',  'Awareness and Training (Level 2)',
 'Implement security awareness training across the organization. 3 practices.',
 'mandatory', 'Level 2 – Advanced', 80),
(UUID(), @cmmc, 'L2-CM',  'Configuration Management (Level 2)',
 'Establish and maintain secure baseline configurations. 9 practices.',
 'mandatory', 'Level 2 – Advanced', 90),
(UUID(), @cmmc, 'L2-IA',  'Identification and Authentication (Level 2)',
 'Implement multi-factor authentication and privileged access controls. 11 practices.',
 'mandatory', 'Level 2 – Advanced', 100),
(UUID(), @cmmc, 'L2-IR',  'Incident Response (Level 2)',
 'Establish and maintain an operational incident response capability. 3 practices.',
 'mandatory', 'Level 2 – Advanced', 110),
(UUID(), @cmmc, 'L2-RM',  'Risk Management (Level 2)',
 'Periodically assess and manage risk to operations, assets, and individuals. 3 practices.',
 'mandatory', 'Level 2 – Advanced', 120),
(UUID(), @cmmc, 'L2-CA',  'Security Assessment (Level 2)',
 'Assess security controls; develop plans of action to correct deficiencies. 4 practices.',
 'mandatory', 'Level 2 – Advanced', 130),
(UUID(), @cmmc, 'L3',     'Level 3 — Expert (NIST 800-172 subset)',
 'Additional practices beyond Level 2 to protect against advanced persistent threats (APTs). 24 additional practices.',
 'mandatory', 'Level 3 – Expert', 140);


-- ═══════════════════════════════════════════════════════════════════════════════
-- FedRAMP
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @fedramp, 'FedRAMP-Low',  'Low Impact Baseline',
 'Minimum security controls for systems where loss of confidentiality, integrity, or availability would have limited adverse effect.',
 'mandatory', 'Authorization', 5),
(UUID(), @fedramp, 'FedRAMP-Mod',  'Moderate Impact Baseline',
 'Security controls for systems where loss could have serious adverse effect on operations, assets, or individuals.',
 'mandatory', 'Authorization', 6),
(UUID(), @fedramp, 'FedRAMP-High', 'High Impact Baseline',
 'Security controls for systems where loss could have severe or catastrophic adverse effect.',
 'mandatory', 'Authorization', 7),
(UUID(), @fedramp, 'FR-AC',  'Access Control (FedRAMP)',
 'All NIST 800-53 AC controls plus FedRAMP-specific parameter requirements for cloud environments.',
 'mandatory', 'Technical Controls', 10),
(UUID(), @fedramp, 'FR-AU',  'Audit and Accountability (FedRAMP)',
 'Audit log requirements with specific retention periods and SIEM integration requirements.',
 'mandatory', 'Technical Controls', 20),
(UUID(), @fedramp, 'FR-CA',  'Assessment, Authorization and Monitoring',
 'Authorization to Operate (ATO) process, continuous monitoring, and annual assessments by 3PAO.',
 'mandatory', 'Authorization', 30),
(UUID(), @fedramp, 'FR-CM',  'Configuration Management (FedRAMP)',
 'Baseline configurations; FIPS-validated cryptography; approved products in marketplace.',
 'mandatory', 'Technical Controls', 40),
(UUID(), @fedramp, 'FR-CP',  'Contingency Planning (FedRAMP)',
 'Government-specified RTO/RPO requirements and data backup in government-accessible storage.',
 'mandatory', 'Resilience', 50),
(UUID(), @fedramp, 'FR-IA',  'Identification and Authentication (FedRAMP)',
 'PIV/CAC card support; MFA for all privileged and non-privileged accounts accessing federal systems.',
 'mandatory', 'Technical Controls', 60),
(UUID(), @fedramp, 'FR-IR',  'Incident Response (FedRAMP)',
 'Report incidents to US-CERT within 1 hour; maintain incident response plan updated annually.',
 'mandatory', 'Incident Response', 70),
(UUID(), @fedramp, 'FR-RA',  'Risk Assessment (FedRAMP)',
 'Annual risk assessments; vulnerability scanning at least monthly; penetration testing annually.',
 'mandatory', 'Risk Management', 80),
(UUID(), @fedramp, 'FR-SA',  'System and Services Acquisition (FedRAMP)',
 'Supply chain risk management; software assurance; developer security testing requirements.',
 'mandatory', 'Third-party Risk', 90),
(UUID(), @fedramp, 'FR-SC',  'System and Communications Protection (FedRAMP)',
 'FIPS 140-2 validated encryption; boundary protection; TIC compliance.',
 'mandatory', 'Technical Controls', 100),
(UUID(), @fedramp, 'FR-SI',  'System and Information Integrity (FedRAMP)',
 'Malware protection with auto-updates; security alerts; flaw remediation within 30 days.',
 'mandatory', 'Technical Controls', 110),
(UUID(), @fedramp, 'FR-3PAO', 'Third-Party Assessment Organization',
 'Engage an accredited 3PAO for independent security assessment during authorization.',
 'mandatory', 'Authorization', 120);


-- ═══════════════════════════════════════════════════════════════════════════════
-- FISMA
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @fisma, 'FISMA-1',  'Information System Inventory',
 'Maintain an inventory of all information systems operated by or under control of the agency.',
 'mandatory', 'Program Management', 10),
(UUID(), @fisma, 'FISMA-2',  'Categorization (FIPS 199)',
 'Categorize information and information systems according to impact levels using FIPS 199 standards.',
 'mandatory', 'Risk Management', 20),
(UUID(), @fisma, 'FISMA-3',  'System Security Plans (NIST 800-18)',
 'Develop, maintain, and implement system security plans covering all NIST 800-53 controls.',
 'mandatory', 'Documentation', 30),
(UUID(), @fisma, 'FISMA-4',  'Security Control Selection (NIST 800-53)',
 'Select, implement, and document security controls based on FIPS 199 categorization.',
 'mandatory', 'Technical Controls', 40),
(UUID(), @fisma, 'FISMA-5',  'Risk Assessments (NIST 800-30)',
 'Conduct risk assessments to identify threats, vulnerabilities, and potential impact to operations.',
 'mandatory', 'Risk Management', 50),
(UUID(), @fisma, 'FISMA-6',  'Security Control Assessments',
 'Assess the effectiveness of security controls using independent assessors or internal personnel.',
 'mandatory', 'Assessment', 60),
(UUID(), @fisma, 'FISMA-7',  'Authorization to Operate (ATO)',
 'Obtain ATO from authorizing official before placing systems into operation.',
 'mandatory', 'Authorization', 70),
(UUID(), @fisma, 'FISMA-8',  'Continuous Monitoring (NIST 800-137)',
 'Implement a continuous monitoring strategy to maintain ongoing awareness of security status.',
 'mandatory', 'Monitoring', 80),
(UUID(), @fisma, 'FISMA-9',  'Plans of Action and Milestones (POA&M)',
 'Develop and maintain POA&Ms to document planned remediation of security weaknesses.',
 'mandatory', 'Remediation', 90),
(UUID(), @fisma, 'FISMA-10', 'Incident Reporting',
 'Report information security incidents to US-CERT in accordance with OMB and agency policy.',
 'mandatory', 'Incident Response', 100),
(UUID(), @fisma, 'FISMA-11', 'Security Training and Awareness',
 'Provide annual security awareness training and role-based training for personnel with security responsibilities.',
 'mandatory', 'People', 110),
(UUID(), @fisma, 'FISMA-12', 'Configuration Management',
 'Establish and manage secure configuration baselines for all information systems.',
 'mandatory', 'Technical Controls', 120);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NERC CIP
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @nerccip, 'CIP-002', 'BES Cyber System Categorization',
 'Identify and categorize BES Cyber Systems and their associated BES Cyber Assets for the application of cybersecurity requirements.',
 'mandatory', 'Asset Management', 10),
(UUID(), @nerccip, 'CIP-003', 'Security Management Controls',
 'Specify consistent and sustainable security management controls that establish responsibility and accountability for the protection of BES Cyber Systems.',
 'mandatory', 'Governance', 20),
(UUID(), @nerccip, 'CIP-004', 'Personnel and Training',
 'Minimize the risk against compromise of BES Cyber Systems from personnel with authorized electronic or physical access.',
 'mandatory', 'People', 30),
(UUID(), @nerccip, 'CIP-005', 'Electronic Security Perimeters',
 'Manage electronic access to BES Cyber Systems by specifying a controlled Electronic Security Perimeter.',
 'mandatory', 'Network Security', 40),
(UUID(), @nerccip, 'CIP-006', 'Physical Security of BES Cyber Systems',
 'Manage physical access to BES Cyber Systems by specifying a physical security plan.',
 'mandatory', 'Physical Security', 50),
(UUID(), @nerccip, 'CIP-007', 'Systems Security Management',
 'Manage system security by specifying selected technical, operational and procedural requirements.',
 'mandatory', 'Technical Controls', 60),
(UUID(), @nerccip, 'CIP-008', 'Incident Reporting and Response Planning',
 'Respond to and recover from incidents involving BES Cyber Systems.',
 'mandatory', 'Incident Response', 70),
(UUID(), @nerccip, 'CIP-009', 'Recovery Plans for BES Cyber Systems',
 'Recover reliability functions performed by BES Cyber Systems after a cybersecurity incident.',
 'mandatory', 'Resilience', 80),
(UUID(), @nerccip, 'CIP-010', 'Configuration Change Management and Vulnerability Management',
 'Prevent and detect unauthorized changes to BES Cyber Systems by managing configuration changes.',
 'mandatory', 'Configuration Management', 90),
(UUID(), @nerccip, 'CIP-011', 'Information Protection',
 'Prevent unauthorized access to BES Cyber System Information.',
 'mandatory', 'Data Protection', 100),
(UUID(), @nerccip, 'CIP-013', 'Supply Chain Risk Management',
 'Mitigate cybersecurity risks in the supply chain for industrial control system hardware, software, and services.',
 'mandatory', 'Third-party Risk', 110),
(UUID(), @nerccip, 'CIP-014', 'Physical Security of Transmission Stations',
 'Identify and protect transmission stations and substations from physical attacks.',
 'mandatory', 'Physical Security', 120);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SWIFT CSP
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @swiftcsp, '1.1',   'SWIFT Environment Protection',
 'Ensure the protection of the user''s local SWIFT infrastructure from potentially compromised elements of the general IT environment.',
 'mandatory', 'Architecture', 10),
(UUID(), @swiftcsp, '1.2',   'Privileged Account Control',
 'Restrict and control the allocation and usage of administrator-level operating system accounts.',
 'mandatory', 'Access Control', 20),
(UUID(), @swiftcsp, '1.3A',  'Virtualisation Platform Security',
 'Secure the virtualisation platform and ensure it has a minimal footprint.',
 'mandatory', 'Infrastructure', 30),
(UUID(), @swiftcsp, '2.1',   'Internal Data Flow Security',
 'Ensure the confidentiality and integrity of data flows between SWIFT-related components.',
 'mandatory', 'Network Security', 40),
(UUID(), @swiftcsp, '2.2',   'Security Updates',
 'Minimise the occurrence of known technical vulnerabilities by applying security updates for all SWIFT-related components.',
 'mandatory', 'Vulnerability Management', 50),
(UUID(), @swiftcsp, '2.3',   'System Hardening',
 'Reduce the cyber attack surface of SWIFT-related components by performing system hardening.',
 'mandatory', 'System Hardening', 60),
(UUID(), @swiftcsp, '2.5A',  'External Transmission Data Protection',
 'Protect the confidentiality of SWIFT-related data transmitted or stored outside the secure zone.',
 'mandatory', 'Data Protection', 70),
(UUID(), @swiftcsp, '2.6',   'Operator Session Confidentiality and Integrity',
 'Protect the confidentiality and integrity of interactive operator sessions connecting to SWIFT-related infrastructure.',
 'mandatory', 'Access Control', 80),
(UUID(), @swiftcsp, '2.7',   'Vulnerability Scanning',
 'Identify known vulnerabilities within the SWIFT environment by implementing a regular vulnerability scanning process.',
 'mandatory', 'Vulnerability Management', 90),
(UUID(), @swiftcsp, '2.9A',  'Transaction Business Controls',
 'Restrict transaction activity to documented and approved business scope.',
 'mandatory', 'Fraud Prevention', 100),
(UUID(), @swiftcsp, '4.1',   'Password Policy',
 'Ensure passwords meet defined complexity and rotation requirements for all SWIFT components.',
 'mandatory', 'Access Control', 110),
(UUID(), @swiftcsp, '4.2',   'Multi-Factor Authentication',
 'Prevent unauthorised access to SWIFT-related infrastructure components or operating systems.',
 'mandatory', 'Access Control', 120),
(UUID(), @swiftcsp, '5.1',   'Logical Access Controls',
 'Enforce the security principles of need-to-know access, least privilege, and separation of duties for SWIFT accounts.',
 'mandatory', 'Access Control', 130),
(UUID(), @swiftcsp, '6.1',   'Malware Protection',
 'Ensure that all SWIFT-related systems are protected against malware and intrusions.',
 'mandatory', 'Endpoint Security', 140),
(UUID(), @swiftcsp, '6.2',   'Software Integrity',
 'Know the software installed on SWIFT-related systems and protect against unauthorised software installation.',
 'mandatory', 'Integrity', 150),
(UUID(), @swiftcsp, '7.1',   'Cyber Incident Response Planning',
 'Ensure a consistent and effective approach to cyber incident management.',
 'mandatory', 'Incident Response', 160),
(UUID(), @swiftcsp, '7.2',   'Security Training and Awareness',
 'Ensure staff are aware of their security responsibilities and have the capabilities to detect and escalate incidents.',
 'mandatory', 'People', 170),
(UUID(), @swiftcsp, '7.3A',  'Penetration Testing',
 'Validate the operational security of the SWIFT environment against relevant threats through penetration testing.',
 'mandatory', 'Security Testing', 180);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SOX ITGC
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @soxitgc, 'ITGC-CM',  'Change Management',
 'Manage changes to systems supporting financial reporting to prevent unauthorised or untested changes. Covers development, testing, approval, and migration.',
 'mandatory', 'Change Management', 10),
(UUID(), @soxitgc, 'ITGC-LA',  'Logical Access',
 'Restrict access to systems, applications, and data supporting financial reporting based on business need. Includes user provisioning, access reviews, and privileged access.',
 'mandatory', 'Access Control', 20),
(UUID(), @soxitgc, 'ITGC-CO',  'Computer Operations',
 'Ensure reliable and consistent processing of financial data. Covers job scheduling, batch processing, error handling, and backup/recovery procedures.',
 'mandatory', 'Operations', 30),
(UUID(), @soxitgc, 'ITGC-SD',  'System Development and Acquisition',
 'Ensure new and modified systems supporting financial reporting are properly developed, tested, and approved before go-live.',
 'mandatory', 'Development', 40),
(UUID(), @soxitgc, 'ITGC-SoD', 'Segregation of Duties',
 'Prevent any single individual from having incompatible IT duties that could lead to unauthorised changes or access to financial data.',
 'mandatory', 'Access Control', 50),
(UUID(), @soxitgc, 'ITGC-DR',  'Data Backup and Recovery',
 'Ensure financial data can be recovered in the event of a disruption through regular backups and tested recovery procedures.',
 'mandatory', 'Resilience', 60),
(UUID(), @soxitgc, 'ITGC-3P',  'Third-Party Management',
 'Assess and monitor IT controls at third-party service providers that support financial reporting processes.',
 'mandatory', 'Third-party Risk', 70),
(UUID(), @soxitgc, 'ITGC-LOG', 'Audit Trail and Logging',
 'Maintain audit trails for all significant transactions and access events in financial reporting systems.',
 'mandatory', 'Audit', 80),
(UUID(), @soxitgc, 'ITGC-SEC', 'IT Security Program',
 'Maintain a security program covering policies, vulnerability management, incident response, and encryption of financial data.',
 'mandatory', 'Security Program', 90),
(UUID(), @soxitgc, 'ITGC-REV', 'User Access Reviews',
 'Conduct periodic reviews of user access rights to financial systems to ensure continued appropriateness.',
 'mandatory', 'Access Control', 100);


-- ═══════════════════════════════════════════════════════════════════════════════
-- HITRUST CSF v11
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @hitrust, 'CC-0',   'Information Security Management Program',
 'Establish and maintain an information security management program with executive sponsorship and defined scope.',
 'mandatory', 'Governance', 10),
(UUID(), @hitrust, 'CC-1',   'Access Control',
 'Manage access to information and systems based on business and security requirements through identity and access management.',
 'mandatory', 'Access Control', 20),
(UUID(), @hitrust, 'CC-2',   'Audit Logging and Monitoring',
 'Implement logging of all security-relevant events and monitor logs for anomalous or suspicious activity.',
 'mandatory', 'Monitoring', 30),
(UUID(), @hitrust, 'CC-3',   'Education, Training and Awareness',
 'Provide regular security and privacy awareness training and specialised training for personnel with security roles.',
 'mandatory', 'People', 40),
(UUID(), @hitrust, 'CC-4',   'Incident Management',
 'Establish an incident management program covering detection, response, reporting, and post-incident review.',
 'mandatory', 'Incident Response', 50),
(UUID(), @hitrust, 'CC-5',   'Business Continuity and Disaster Recovery',
 'Develop, implement, and test business continuity and disaster recovery plans for critical systems and processes.',
 'mandatory', 'Resilience', 60),
(UUID(), @hitrust, 'CC-6',   'IT Risk Management',
 'Implement an IT risk management program covering identification, assessment, treatment, and monitoring of risks.',
 'mandatory', 'Risk Management', 70),
(UUID(), @hitrust, 'CC-7',   'Third Party Assurance',
 'Assess and monitor the security posture of third-party vendors, contractors, and business associates.',
 'mandatory', 'Third-party Risk', 80),
(UUID(), @hitrust, 'CC-8',   'Configuration Management',
 'Establish secure baseline configurations and manage changes to systems and software.',
 'mandatory', 'Configuration Management', 90),
(UUID(), @hitrust, 'CC-9',   'Network Protection',
 'Implement network security controls including firewalls, IDS/IPS, network segmentation, and wireless security.',
 'mandatory', 'Network Security', 100),
(UUID(), @hitrust, 'CC-10',  'Transmission Protection',
 'Protect data in transit using encryption and secure protocols for all networks including public networks.',
 'mandatory', 'Data Protection', 110),
(UUID(), @hitrust, 'CC-11',  'Password Management',
 'Implement and enforce password policies meeting NIST and HIPAA requirements including complexity and MFA.',
 'mandatory', 'Access Control', 120),
(UUID(), @hitrust, 'CC-12',  'Audit Control and Management',
 'Implement controls to record and examine activity on systems containing ePHI as required by HIPAA.',
 'mandatory', 'Compliance', 130),
(UUID(), @hitrust, 'CC-13',  'Physical and Environmental Security',
 'Control physical access to facilities housing systems that process, store, or transmit ePHI.',
 'mandatory', 'Physical Security', 140),
(UUID(), @hitrust, 'CC-14',  'Data Protection and Privacy',
 'Implement data protection controls aligned with HIPAA Privacy Rule covering PHI use, disclosure, and safeguards.',
 'mandatory', 'Privacy', 150),
(UUID(), @hitrust, 'CC-15',  'Mobile Device Security',
 'Manage mobile devices and removable media accessing ePHI including encryption, MDM, and remote wipe.',
 'mandatory', 'Endpoint Security', 160),
(UUID(), @hitrust, 'CC-16',  'Wireless Protection',
 'Secure wireless network access to systems containing ePHI using enterprise-grade authentication and encryption.',
 'mandatory', 'Network Security', 170),
(UUID(), @hitrust, 'CC-17',  'Application and Interface Security',
 'Implement application security controls including secure SDLC, vulnerability testing, and API security.',
 'mandatory', 'Application Security', 180),
(UUID(), @hitrust, 'CC-18',  'Vulnerability Management',
 'Continuously identify, assess, prioritize, and remediate vulnerabilities across all systems and software.',
 'mandatory', 'Vulnerability Management', 190);
