-- Migration 018: Financial services and government/defense frameworks
-- Frameworks: SOX, Basel III, FFIEC CAT, NYDFS 500, SWIFT CSP, SR 11-7,
--             MiFID II, PSD2, SEC Cyber Rules, FINRA, NACHA, OCC Heightened Standards,
--             FISMA, CMMC 1.0, DFARS 7012, DFARS 7020, ITAR, EAR,
--             CJIS, DISA STIGs, DoD SRG, TIC 3.0, GovRAMP, IRAP, ENS

SET @sox       = 'e5f6a7b8-0018-0001-0001-000000000001';
SET @basel3    = 'e5f6a7b8-0018-0002-0001-000000000001';
SET @ffiec     = 'e5f6a7b8-0018-0003-0001-000000000001';
SET @nydfs500  = 'e5f6a7b8-0018-0004-0001-000000000001';
SET @swiftcsp2 = 'e5f6a7b8-0018-0005-0001-000000000001';
SET @sr117     = 'e5f6a7b8-0018-0006-0001-000000000001';
SET @mifid2    = 'e5f6a7b8-0018-0007-0001-000000000001';
SET @psd2      = 'e5f6a7b8-0018-0008-0001-000000000001';
SET @seccyber  = 'e5f6a7b8-0018-0009-0001-000000000001';
SET @finra     = 'e5f6a7b8-0018-0010-0001-000000000001';
SET @nacha     = 'e5f6a7b8-0018-0011-0001-000000000001';
SET @occhts    = 'e5f6a7b8-0018-0012-0001-000000000001';
SET @fisma2    = 'e5f6a7b8-0018-0013-0001-000000000001';
SET @cmmc10    = 'e5f6a7b8-0018-0014-0001-000000000001';
SET @dfars7012 = 'e5f6a7b8-0018-0015-0001-000000000001';
SET @dfars7020 = 'e5f6a7b8-0018-0016-0001-000000000001';
SET @itar      = 'e5f6a7b8-0018-0017-0001-000000000001';
SET @ear       = 'e5f6a7b8-0018-0018-0001-000000000001';
SET @cjis      = 'e5f6a7b8-0018-0019-0001-000000000001';
SET @stigs     = 'e5f6a7b8-0018-0020-0001-000000000001';
SET @dodsrg    = 'e5f6a7b8-0018-0021-0001-000000000001';
SET @tic30     = 'e5f6a7b8-0018-0022-0001-000000000001';
SET @govramp   = 'e5f6a7b8-0018-0023-0001-000000000001';
SET @irap      = 'e5f6a7b8-0018-0024-0001-000000000001';
SET @ens       = 'e5f6a7b8-0018-0025-0001-000000000001';

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

INSERT IGNORE INTO regulatory_frameworks
  (id, code, name, description, version, issuing_body, is_global, effective_date)
VALUES
  (@sox, 'SOX',
   'Sarbanes-Oxley Act (SOX)',
   'US federal law establishing audit and financial regulations for public companies, with key provisions for CEO/CFO certification, internal controls over financial reporting, and whistleblower protections.',
   '2002', 'US Congress / SEC / PCAOB', FALSE, '2002-07-30'),

  (@basel3, 'Basel III',
   'Basel III — International Regulatory Framework for Banks',
   'International banking regulation framework developed by the Basel Committee on Banking Supervision, establishing capital adequacy, stress testing, and liquidity requirements.',
   '2010/2023', 'Basel Committee on Banking Supervision', TRUE, '2013-01-01'),

  (@ffiec, 'FFIEC CAT',
   'FFIEC Cybersecurity Assessment Tool',
   'A voluntary tool developed by the FFIEC to help financial institutions identify cybersecurity risks and determine their cybersecurity preparedness across five domains.',
   '2015', 'FFIEC', FALSE, '2015-06-01'),

  (@nydfs500, 'NYDFS 23 NYCRR 500',
   'NYDFS Cybersecurity Regulation (23 NYCRR Part 500)',
   'New York State regulation requiring covered financial entities to establish and maintain a cybersecurity program, submit annual certifications, and report material cybersecurity events.',
   '2023 Amendment', 'NYDFS', FALSE, '2017-03-01'),

  (@swiftcsp2, 'SWIFT CSP',
   'SWIFT Customer Security Programme (CSP)',
   'Programme providing mandatory and advisory security controls that SWIFT users must implement to protect their local SWIFT infrastructure from cyberattack.',
   '2023 (CSCF v2023)', 'SWIFT', TRUE, '2017-01-01'),

  (@sr117, 'SR 11-7',
   'SR 11-7 — Guidance on Model Risk Management',
   'Federal Reserve and OCC guidance establishing supervisory expectations for sound model risk management practices at banking organizations.',
   '2011', 'Federal Reserve / OCC', FALSE, '2011-04-04'),

  (@mifid2, 'MiFID II',
   'Markets in Financial Instruments Directive II (MiFID II/MiFIR)',
   'EU directive and regulation governing financial markets, establishing requirements for investment firms on product governance, client categorization, and transaction reporting.',
   '2018', 'European Parliament / ESMA', FALSE, '2018-01-03'),

  (@psd2, 'PSD2',
   'Payment Services Directive 2 (PSD2)',
   'EU directive regulating payment services and payment service providers, introducing strong customer authentication, open banking, and third-party provider access.',
   '2018', 'European Parliament / EBA', FALSE, '2018-01-13'),

  (@seccyber, 'SEC Cybersecurity Rules 2023',
   'SEC Cybersecurity Risk Management, Strategy, Governance, and Incident Disclosure Rules (2023)',
   'SEC rules requiring public companies to disclose material cybersecurity incidents within 4 business days and provide annual disclosures on risk management, strategy, and governance.',
   '2023', 'US Securities and Exchange Commission', FALSE, '2023-12-18'),

  (@finra, 'FINRA Rules',
   'FINRA Cybersecurity and Business Continuity Rules',
   'FINRA rules governing business continuity planning, books and records retention, incident reporting, and supervisory controls for broker-dealers.',
   '2023', 'FINRA', FALSE, '2004-01-01'),

  (@nacha, 'NACHA Rules',
   'NACHA ACH Network Rules — ACH Security Framework',
   'Operating rules governing the ACH payment network, including security requirements for protecting account numbers, conducting risk assessments, and originator obligations.',
   '2023', 'NACHA', FALSE, '2019-06-01'),

  (@occhts, 'OCC Heightened Standards',
   'OCC Guidelines Establishing Heightened Standards (12 CFR Part 30 Appendix D)',
   'Guidelines establishing heightened standards for governance, risk appetite, risk management, and talent management at large insured national banks.',
   '2014', 'OCC', FALSE, '2014-09-02'),

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

  (@cmmc10, 'CMMC 1.0',
   'Cybersecurity Maturity Model Certification 1.0',
   'Original DoD framework for protecting the defense industrial base across five maturity levels, superseded by CMMC 2.0 but referenced for legacy contracts.',
   '1.02', 'US Department of Defense', FALSE, '2020-01-31'),

  (@dfars7012, 'DFARS 252.204-7012',
   'DFARS 252.204-7012 — Safeguarding Covered Defense Information',
   'DoD contract clause requiring contractors to implement NIST SP 800-171 security controls, report cyber incidents within 72 hours, and preserve data for 90 days.',
   '2016', 'US Department of Defense', FALSE, '2016-12-30'),

  (@dfars7020, 'DFARS 252.204-7020',
   'DFARS 252.204-7020 — NIST SP 800-171 DoD Assessment',
   'DoD contract clause requiring contractors to conduct assessments of NIST SP 800-171 implementation and submit summary scores to the Supplier Performance Risk System (SPRS).',
   '2020', 'US Department of Defense', FALSE, '2020-11-30'),

  (@itar, 'ITAR',
   'International Traffic in Arms Regulations (ITAR)',
   'US regulations administered by the State Department controlling the export and import of defense-related articles and services on the United States Munitions List.',
   '2023', 'US Department of State / DDTC', FALSE, '1976-01-01'),

  (@ear, 'EAR',
   'Export Administration Regulations (EAR)',
   'US regulations administered by the Commerce Department controlling the export, re-export, and transfer of commercial and dual-use items on the Commerce Control List.',
   '2023', 'US Department of Commerce / BIS', FALSE, '1979-01-01'),

  (@cjis, 'CJIS Security Policy',
   'FBI CJIS Security Policy',
   'Minimum security requirements for agencies accessing FBI Criminal Justice Information Services data, covering physical, personnel, technical, and administrative security controls.',
   'v5.9.3', 'FBI CJIS Division', FALSE, '1992-01-01'),

  (@stigs, 'DISA STIGs',
   'DISA Security Technical Implementation Guides (STIGs)',
   'Cybersecurity configuration standards for DoD IT systems covering operating systems, applications, network devices, and databases with vulnerability severity categorizations.',
   '2023', 'DISA', FALSE, '1998-01-01'),

  (@dodsrg, 'DoD SRG',
   'DoD Cloud Computing Security Requirements Guide',
   'Provides cloud security requirements and authorization guidance for cloud services used by DoD, organized by impact levels based on data sensitivity.',
   'v1r4', 'DISA', FALSE, '2017-03-07'),

  (@tic30, 'TIC 3.0',
   'Trusted Internet Connection 3.0 (TIC 3.0)',
   'CISA/OMB program establishing security capabilities and telemetry requirements for federal agency network connections, updated to support cloud and remote work use cases.',
   '3.0', 'CISA / OMB', FALSE, '2019-09-12'),

  (@govramp, 'GovRAMP',
   'GovRAMP — Government Risk and Authorization Management Program',
   'Multi-state program providing state and local governments a standardized approach to security assessment and authorization for cloud service providers, mirroring FedRAMP.',
   '2023', 'GovRAMP PMO', FALSE, '2021-01-01'),

  (@irap, 'IRAP',
   'Information Security Registered Assessors Program (IRAP) — Australia',
   'Australian Signals Directorate program for assessing cloud and ICT systems that hold Australian government data against the ISM controls at different classification levels.',
   '2023', 'ASD (Australian Signals Directorate)', FALSE, '2014-01-01'),

  (@ens, 'ENS',
   'Esquema Nacional de Seguridad (ENS) — Spain',
   'Spanish national security framework establishing the principles and requirements that information systems must meet to support the secure electronic exchange of data by public administrations.',
   '2022 (RD 311/2022)', 'Spanish Government / CCN', FALSE, '2022-05-03');


-- ═══════════════════════════════════════════════════════════════════════════════
-- SOX — Key Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @sox, 'SOX-302',  'Section 302: Corporate Responsibility for Financial Reports',
 'Requires CEO and CFO to certify the accuracy of financial reports and the effectiveness of internal disclosure controls and procedures.',
 'mandatory', 'SOX Sections', 10),
(UUID(), @sox, 'SOX-404',  'Section 404: Management Assessment of Internal Controls',
 'Requires management to assess and report on the effectiveness of internal control over financial reporting; auditors must attest to management''s assessment.',
 'mandatory', 'SOX Sections', 20),
(UUID(), @sox, 'SOX-409',  'Section 409: Real-Time Issuer Disclosures',
 'Requires public companies to disclose material changes in financial condition or operations rapidly (4 business days for material cybersecurity events under SEC 2023 rules).',
 'mandatory', 'SOX Sections', 30),
(UUID(), @sox, 'SOX-802',  'Section 802: Criminal Penalties for Altering Documents',
 'Establishes criminal penalties for knowingly altering, destroying, or falsifying records in federal investigations or in connection with bankruptcy proceedings.',
 'mandatory', 'SOX Sections', 40),
(UUID(), @sox, 'SOX-906',  'Section 906: Corporate Responsibility for Financial Reports (Criminal)',
 'Imposes criminal penalties on CEOs and CFOs who knowingly certify materially false financial reports (up to $5M fine and 20 years imprisonment).',
 'mandatory', 'SOX Sections', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- Basel III — Pillars and Ratios
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @basel3, 'B3-P1',   'Pillar 1: Minimum Capital Requirements',
 'Banks must hold minimum capital equal to 8% of risk-weighted assets, with a Common Equity Tier 1 (CET1) ratio of at least 4.5% and Tier 1 capital of 6%.',
 'mandatory', 'Basel III Pillars', 10),
(UUID(), @basel3, 'B3-P2',   'Pillar 2: Supervisory Review Process',
 'Banks must have internal processes for assessing capital adequacy relative to their risk profiles; supervisors review and may require additional capital buffers.',
 'mandatory', 'Basel III Pillars', 20),
(UUID(), @basel3, 'B3-P3',   'Pillar 3: Market Discipline',
 'Banks must disclose their capital structure, risk exposures, capital adequacy assessments, and risk management approaches to enable market participants to assess their soundness.',
 'mandatory', 'Basel III Pillars', 30),
(UUID(), @basel3, 'B3-LCR',  'Liquidity Coverage Ratio (LCR)',
 'Banks must hold sufficient high-quality liquid assets to cover total net cash outflows over a 30-day stress scenario; minimum ratio is 100%.',
 'mandatory', 'Basel III Pillars', 40),
(UUID(), @basel3, 'B3-NSFR', 'Net Stable Funding Ratio (NSFR)',
 'Banks must maintain a stable funding profile relative to their assets and off-balance-sheet activities; available stable funding must exceed required stable funding.',
 'mandatory', 'Basel III Pillars', 50),
(UUID(), @basel3, 'B3-LR',   'Leverage Ratio',
 'Banks must maintain a minimum leverage ratio of 3% (Tier 1 capital as a percentage of total exposure), serving as a backstop to risk-weighted capital requirements.',
 'mandatory', 'Basel III Pillars', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- FFIEC CAT — 5 Domains
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @ffiec, 'CAT-1',  'Domain 1: Cyber Risk Management and Oversight',
 'Board and management oversight, policies and procedures, IT asset management, risk management, and audit functions for cybersecurity.',
 'mandatory', 'FFIEC CAT Domains', 10),
(UUID(), @ffiec, 'CAT-2',  'Domain 2: Threat Intelligence and Collaboration',
 'Gathering, analyzing, and sharing cyber threat information; participation in information sharing forums; monitoring of emerging threats.',
 'mandatory', 'FFIEC CAT Domains', 20),
(UUID(), @ffiec, 'CAT-3',  'Domain 3: Cybersecurity Controls',
 'Preventive, detective, and corrective controls covering infrastructure management, access management, device/end-point security, secure coding, and patch management.',
 'mandatory', 'FFIEC CAT Domains', 30),
(UUID(), @ffiec, 'CAT-4',  'Domain 4: External Dependency Management',
 'Management of third-party and vendor relationships including due diligence, contracts, ongoing monitoring, and termination procedures.',
 'mandatory', 'FFIEC CAT Domains', 40),
(UUID(), @ffiec, 'CAT-5',  'Domain 5: Cyber Incident Management and Resilience',
 'Incident response planning, detection, response, mitigation, escalation, and recovery capabilities; testing and lessons learned.',
 'mandatory', 'FFIEC CAT Domains', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NYDFS 23 NYCRR 500 — Key Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @nydfs500, '§500.02',  'Cybersecurity Program',
 'Covered entities must maintain a cybersecurity program based on risk assessment and designed to protect the confidentiality, integrity, and availability of information systems.',
 'mandatory', 'NYDFS 500 Sections', 10),
(UUID(), @nydfs500, '§500.03',  'Cybersecurity Policy',
 'Covered entities must implement and maintain written cybersecurity policies covering data governance, asset inventory, access controls, and other specified areas.',
 'mandatory', 'NYDFS 500 Sections', 20),
(UUID(), @nydfs500, '§500.05',  'Penetration Testing and Vulnerability Assessments',
 'Annual penetration testing and bi-annual vulnerability assessments are required; results must be remediated on a risk-based schedule.',
 'mandatory', 'NYDFS 500 Sections', 30),
(UUID(), @nydfs500, '§500.06',  'Audit Trail',
 'Covered entities must maintain audit trails designed to detect and respond to cybersecurity events; records must be retained for at least 3 years.',
 'mandatory', 'NYDFS 500 Sections', 40),
(UUID(), @nydfs500, '§500.07',  'Access Privileges and Management',
 'Covered entities must limit access privileges to information systems providing access only based on need; review access privileges at least annually.',
 'mandatory', 'NYDFS 500 Sections', 50),
(UUID(), @nydfs500, '§500.11',  'Third-Party Service Provider Security Policy',
 'Covered entities must maintain policies and procedures for the security of information systems accessible by third-party service providers.',
 'mandatory', 'NYDFS 500 Sections', 60),
(UUID(), @nydfs500, '§500.12',  'Multi-Factor Authentication (MFA)',
 'MFA is required for remote access, privileged accounts, and web applications that provide access to nonpublic information.',
 'mandatory', 'NYDFS 500 Sections', 70),
(UUID(), @nydfs500, '§500.13',  'Limitations on Data Retention',
 'Covered entities must establish policies for secure disposal of nonpublic information that is no longer necessary for business operations.',
 'mandatory', 'NYDFS 500 Sections', 80),
(UUID(), @nydfs500, '§500.14',  'Training and Monitoring',
 'Covered entities must provide regular cybersecurity awareness training and monitor authorized users'' activities on information systems.',
 'mandatory', 'NYDFS 500 Sections', 90),
(UUID(), @nydfs500, '§500.15',  'Encryption of Nonpublic Information',
 'Covered entities must encrypt nonpublic information in transit over external networks and at rest, using compensating controls where encryption is not feasible.',
 'mandatory', 'NYDFS 500 Sections', 100),
(UUID(), @nydfs500, '§500.16',  'Business Continuity and Disaster Recovery',
 'Covered entities must establish business continuity and disaster recovery plans ensuring availability of critical information systems in a cybersecurity event.',
 'mandatory', 'NYDFS 500 Sections', 110),
(UUID(), @nydfs500, '§500.17',  'Incident Notification',
 'Covered entities must notify NYDFS within 72 hours of determining a material cybersecurity event has occurred; notify consumers and regulators per applicable law.',
 'mandatory', 'NYDFS 500 Sections', 120),
(UUID(), @nydfs500, '§500.19',  'Limitations and Exemptions',
 'Limited exemptions apply to covered entities with fewer than 10 employees, limited NY gross revenue under $5M, or fewer than $10M total assets.',
 'mandatory', 'NYDFS 500 Sections', 130),
(UUID(), @nydfs500, '§500.20',  'Enforcement and Penalties',
 'NYDFS may impose civil monetary penalties for violations; criminal penalties may apply for knowingly submitting false certifications.',
 'mandatory', 'NYDFS 500 Sections', 140);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SWIFT CSP — Domains
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @swiftcsp2, 'CSP-1',  'Restrict Internet Footprint',
 'Minimize the cyber-attack surface by ensuring the local SWIFT infrastructure is protected and isolated from the broader enterprise IT environment.',
 'mandatory', 'SWIFT CSP Domains', 10),
(UUID(), @swiftcsp2, 'CSP-2',  'Minimize Attack Surface',
 'Reduce the potential attack surface of SWIFT-related components through least-privilege, application controls, and system hardening.',
 'mandatory', 'SWIFT CSP Domains', 20),
(UUID(), @swiftcsp2, 'CSP-3',  'Secure Your Environment',
 'Physically and logically secure the local SWIFT environment by controlling physical access, protecting credentials, and managing software updates.',
 'mandatory', 'SWIFT CSP Domains', 30),
(UUID(), @swiftcsp2, 'CSP-4',  'Prevent Compromise of Credentials',
 'Implement strong authentication and privileged access management to prevent unauthorized access to SWIFT-related systems and applications.',
 'mandatory', 'SWIFT CSP Domains', 40),
(UUID(), @swiftcsp2, 'CSP-5',  'Detect and Respond to Anomalous Activity',
 'Detect and respond to anomalous activity in SWIFT-related systems through logging, monitoring, alerting, and incident response procedures.',
 'mandatory', 'SWIFT CSP Domains', 50),
(UUID(), @swiftcsp2, 'CSP-6',  'Share Information and Prepare to Respond',
 'Participate in SWIFT information sharing programs and be prepared to respond to fraud attempts by reporting cybersecurity incidents to SWIFT.',
 'recommended', 'SWIFT CSP Domains', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SR 11-7 — Model Risk Management Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @sr117, 'MRM-DEV',  'Model Development, Implementation, and Use',
 'Models must be developed with sound conceptual basis, rigorous implementation, and used appropriately within their intended scope and limitations.',
 'mandatory', 'Model Risk Management', 10),
(UUID(), @sr117, 'MRM-VAL',  'Model Validation',
 'Effective model validation includes evaluation of conceptual soundness, ongoing monitoring, outcomes analysis, and benchmarking against alternative approaches.',
 'mandatory', 'Model Risk Management', 20),
(UUID(), @sr117, 'MRM-GOV',  'Governance, Policies, and Controls',
 'Board and senior management must establish and maintain a sound model risk management framework including an inventory, tiered risk-based review schedule, and model ownership.',
 'mandatory', 'Model Risk Management', 30),
(UUID(), @sr117, 'MRM-MON',  'Ongoing Monitoring and Reporting',
 'Models must be monitored continuously for performance degradation, changes in operating context, and data quality; results reported to senior management.',
 'mandatory', 'Model Risk Management', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- MiFID II — Key Requirements
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @mifid2, 'MIFID-PG',  'Product Governance',
 'Manufacturers and distributors must ensure financial products are designed and distributed appropriately for defined target markets.',
 'mandatory', 'MiFID II Requirements', 10),
(UUID(), @mifid2, 'MIFID-CC',  'Client Categorization',
 'Clients must be classified as retail, professional, or eligible counterparty; different protections and obligations apply to each category.',
 'mandatory', 'MiFID II Requirements', 20),
(UUID(), @mifid2, 'MIFID-BE',  'Best Execution',
 'Investment firms must take all sufficient steps to obtain the best possible result for clients when executing orders.',
 'mandatory', 'MiFID II Requirements', 30),
(UUID(), @mifid2, 'MIFID-TR',  'Transaction Reporting',
 'Investment firms must report details of transactions in financial instruments to national regulators within one working day.',
 'mandatory', 'MiFID II Requirements', 40),
(UUID(), @mifid2, 'MIFID-MS',  'Market Structure and Trading Venues',
 'Establishes requirements for regulated markets, multilateral trading facilities, organized trading facilities, and systematic internalisers.',
 'mandatory', 'MiFID II Requirements', 50),
(UUID(), @mifid2, 'MIFID-TP',  'Pre- and Post-Trade Transparency',
 'Trading venues and systematic internalisers must make pre- and post-trade information publicly available on a reasonable commercial basis.',
 'mandatory', 'MiFID II Requirements', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- PSD2 — Key Requirements
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @psd2, 'PSD2-SCA',  'Strong Customer Authentication (SCA)',
 'Payment service providers must apply SCA using at least two independent authentication factors from knowledge, possession, and inherence categories.',
 'mandatory', 'PSD2 Requirements', 10),
(UUID(), @psd2, 'PSD2-OBA',  'Open Banking APIs',
 'Account servicing payment service providers must provide access to payment accounts via standardized, secure APIs for authorized third-party providers.',
 'mandatory', 'PSD2 Requirements', 20),
(UUID(), @psd2, 'PSD2-TPP',  'Third-Party Provider (TPP) Access',
 'Account information service providers (AISP) and payment initiation service providers (PISP) must be authorized and may access accounts with customer consent.',
 'mandatory', 'PSD2 Requirements', 30),
(UUID(), @psd2, 'PSD2-SEC',  'Security Measures',
 'Payment service providers must implement incident reporting to competent authorities and EBA, maintain transaction monitoring, and establish fraud detection mechanisms.',
 'mandatory', 'PSD2 Requirements', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SEC Cybersecurity Rules 2023
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @seccyber, 'SEC-ID',   'Incident Disclosure (4-Business-Day Rule)',
 'Public companies must disclose material cybersecurity incidents on Form 8-K within four business days of determining materiality.',
 'mandatory', 'SEC Cyber Rules', 10),
(UUID(), @seccyber, 'SEC-ARD',  'Annual Risk Disclosure',
 'Public companies must provide annual disclosures on 10-K of cybersecurity risk management processes, strategy, and governance.',
 'mandatory', 'SEC Cyber Rules', 20),
(UUID(), @seccyber, 'SEC-BO',   'Board Oversight',
 'Annual disclosures must describe board oversight of cybersecurity risks and management''s role in assessing and managing material cybersecurity risks.',
 'mandatory', 'SEC Cyber Rules', 30);


-- ═══════════════════════════════════════════════════════════════════════════════
-- FINRA Rules — Key Rules
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @finra, 'FINRA-4370',  'Rule 4370: Business Continuity Planning',
 'Broker-dealers must create and maintain written BCPs identifying procedures to follow in the event of an emergency or significant business disruption.',
 'mandatory', 'FINRA Rules', 10),
(UUID(), @finra, 'FINRA-4511',  'Rule 4511: General Requirements for Books and Records',
 'Broker-dealers must make and preserve books and records in a form and manner required by FINRA rules and Exchange Act regulations.',
 'mandatory', 'FINRA Rules', 20),
(UUID(), @finra, 'FINRA-4530',  'Rule 4530: Reporting Requirements',
 'Broker-dealers must report specified events including regulatory actions, customer complaints, and internal conclusions of violations to FINRA.',
 'mandatory', 'FINRA Rules', 30),
(UUID(), @finra, 'FINRA-3110',  'Rule 3110: Supervision',
 'Broker-dealers must establish and maintain a system to supervise the activities of each associated person that is reasonably designed to achieve compliance.',
 'mandatory', 'FINRA Rules', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NACHA Rules — ACH Security Framework
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @nacha, 'NACHA-PAN',  'Protect Account Numbers',
 'Originators must use commercially reasonable methods to protect account numbers used in ACH transactions from unauthorized access.',
 'mandatory', 'NACHA ACH Security', 10),
(UUID(), @nacha, 'NACHA-RA',   'Risk Assessments',
 'Third-party senders and originators must conduct risk assessments of their ACH activities and implement controls commensurate with risk.',
 'mandatory', 'NACHA ACH Security', 20),
(UUID(), @nacha, 'NACHA-OO',   'Originator Obligations',
 'Originators are responsible for ensuring their originators and third-party senders comply with NACHA rules, including proper authorization and fraud monitoring.',
 'mandatory', 'NACHA ACH Security', 30);


-- ═══════════════════════════════════════════════════════════════════════════════
-- OCC Heightened Standards — Categories
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @occhts, 'OCC-GV',  'Governance',
 'Covered institutions must have an appropriate governance structure including board-level risk committee, independent risk management, and internal audit functions.',
 'mandatory', 'OCC Heightened Standards', 10),
(UUID(), @occhts, 'OCC-RA',  'Risk Appetite',
 'Covered institutions must articulate a risk appetite statement that aligns with strategic and financial plans, serves as a communication tool, and drives risk culture.',
 'mandatory', 'OCC Heightened Standards', 20),
(UUID(), @occhts, 'OCC-RM',  'Risk Management',
 'Covered institutions must have effective risk management covering credit, market, operational, liquidity, and strategic risk with front-line units, risk management functions, and internal audit.',
 'mandatory', 'OCC Heightened Standards', 30),
(UUID(), @occhts, 'OCC-TM',  'Talent Management',
 'Covered institutions must have compensation structures for senior officers in risk management and internal audit that are commensurate with their responsibilities.',
 'mandatory', 'OCC Heightened Standards', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- FISMA — Requirements
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @fisma2, 'FISMA-INV',  'System Inventory',
 'Agencies must maintain an inventory of federal information systems operated by or on behalf of the agency.',
 'mandatory', 'FISMA Requirements', 10),
(UUID(), @fisma2, 'FISMA-CAT',  'Categorization (FIPS 199)',
 'Agencies must categorize information and systems according to FIPS 199 impact levels (Low, Moderate, High) based on confidentiality, integrity, and availability.',
 'mandatory', 'FISMA Requirements', 20),
(UUID(), @fisma2, 'FISMA-CTL',  'Security Controls Selection (NIST 800-53)',
 'Agencies must select and implement appropriate security and privacy controls from NIST SP 800-53 based on system categorization.',
 'mandatory', 'FISMA Requirements', 30),
(UUID(), @fisma2, 'FISMA-ASS',  'Assessment',
 'Agencies must assess the security controls using NIST SP 800-53A procedures to determine effectiveness.',
 'mandatory', 'FISMA Requirements', 40),
(UUID(), @fisma2, 'FISMA-ATO',  'Authorization to Operate (ATO)',
 'An authorizing official must authorize the system to operate based on an acceptable level of risk documented in a risk acceptance decision.',
 'mandatory', 'FISMA Requirements', 50),
(UUID(), @fisma2, 'FISMA-CM',   'Continuous Monitoring',
 'Agencies must monitor the security state of systems on an ongoing basis using automated and manual mechanisms; report security status to CISA via CyberScope/CDM.',
 'mandatory', 'FISMA Requirements', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- CMMC 1.0 — 5 Levels
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @cmmc10, 'CMMC1-L1',  'Level 1: Basic Cyber Hygiene',
 '17 practices covering basic safeguarding of FCI; corresponds to FAR 52.204-21 requirements.',
 'mandatory', 'CMMC 1.0 Levels', 10),
(UUID(), @cmmc10, 'CMMC1-L2',  'Level 2: Intermediate Cyber Hygiene',
 '72 practices serving as a transition step toward Level 3; covers a subset of NIST SP 800-171 controls.',
 'mandatory', 'CMMC 1.0 Levels', 20),
(UUID(), @cmmc10, 'CMMC1-L3',  'Level 3: Good Cyber Hygiene',
 '130 practices covering all 110 NIST SP 800-171 requirements plus additional practices to protect CUI.',
 'mandatory', 'CMMC 1.0 Levels', 30),
(UUID(), @cmmc10, 'CMMC1-L4',  'Level 4: Proactive',
 '156 practices addressing advanced persistent threats; adds practices from NIST SP 800-172 and other sources.',
 'mandatory', 'CMMC 1.0 Levels', 40),
(UUID(), @cmmc10, 'CMMC1-L5',  'Level 5: Advanced/Progressive',
 '171 practices providing the highest level of cyber protection for DoD''s most sensitive programs; proactive threat detection and response.',
 'mandatory', 'CMMC 1.0 Levels', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- DFARS 252.204-7012 — Requirements
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @dfars7012, '7012-AS',  'Adequate Security',
 'Contractors must implement NIST SP 800-171 security requirements to provide adequate security for all covered defense information on contractor information systems.',
 'mandatory', 'DFARS 7012 Requirements', 10),
(UUID(), @dfars7012, '7012-CIR', 'Cyber Incident Reporting',
 'Contractors must report cyber incidents that affect covered defense information or the contractor''s ability to perform requirements within 72 hours to DoD.',
 'mandatory', 'DFARS 7012 Requirements', 20),
(UUID(), @dfars7012, '7012-MS',  'Malicious Software',
 'Contractors must submit malicious software discovered and isolated in connection with a reported cyber incident to the DoD Cyber Crime Center.',
 'mandatory', 'DFARS 7012 Requirements', 30),
(UUID(), @dfars7012, '7012-MP',  'Media Preservation and Protection',
 'Contractors must preserve images of compromised systems and all data for 90 days following a cyber incident report to support DoD damage assessment.',
 'mandatory', 'DFARS 7012 Requirements', 40),
(UUID(), @dfars7012, '7012-AI',  'Access to Additional Information',
 'Contractors must provide DoD access to contractor''s systems and any other information needed to conduct a forensic analysis of a cyber incident.',
 'mandatory', 'DFARS 7012 Requirements', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- DFARS 252.204-7020 — NIST SP 800-171 DoD Assessment
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @dfars7020, '7020-BA',  'Basic Assessment',
 'Contractors must conduct a self-assessment of their implementation of NIST SP 800-171 and post the summary score to SPRS at least every 3 years.',
 'mandatory', 'DFARS 7020 Requirements', 10),
(UUID(), @dfars7020, '7020-MA',  'Medium Assessment',
 'A Government assessment that includes review of the contractor''s Basic Assessment plus a medium-level review of supporting evidence.',
 'mandatory', 'DFARS 7020 Requirements', 20),
(UUID(), @dfars7020, '7020-HA',  'High Assessment',
 'A Government assessment that includes review of the contractor''s system security plan and examination of policies, procedures, practices, and system configurations.',
 'mandatory', 'DFARS 7020 Requirements', 30);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ITAR — Categories
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @itar, 'ITAR-JU',  'Jurisdiction Determination',
 'Determine whether articles are subject to ITAR (USML) or EAR (CCL); jurisdiction is determined by commodity jurisdiction procedures administered by the State Department.',
 'mandatory', 'ITAR Categories', 10),
(UUID(), @itar, 'ITAR-CL',  'Classification on the USML',
 'Classify defense articles against the 21 categories of the US Munitions List (USML) to determine applicable export licensing requirements.',
 'mandatory', 'ITAR Categories', 20),
(UUID(), @itar, 'ITAR-LR',  'License Requirements',
 'Obtain required export licenses or agreements (DSP-5, DSP-73, MLA, TAA) from the State Department DDTC before exporting defense articles or technical data.',
 'mandatory', 'ITAR Categories', 30),
(UUID(), @itar, 'ITAR-EX',  'Exemptions',
 'Identify and properly apply applicable ITAR exemptions (e.g., Canada exemption, government-to-government exemptions) with required documentation.',
 'mandatory', 'ITAR Categories', 40),
(UUID(), @itar, 'ITAR-CP',  'Compliance Program',
 'Maintain a Technology Control Plan (TCP) and ITAR compliance program including training, recordkeeping, access controls, and self-disclosure procedures.',
 'mandatory', 'ITAR Categories', 50),
(UUID(), @itar, 'ITAR-VL',  'Violations and Voluntary Disclosure',
 'Proactively disclose potential violations to DDTC through the voluntary disclosure process to mitigate penalties; cooperate with any investigations.',
 'mandatory', 'ITAR Categories', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- EAR — Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @ear, 'EAR-CCL',  'CCL Classification',
 'Classify items against the Commerce Control List (CCL) using Export Control Classification Numbers (ECCNs) to determine applicable controls.',
 'mandatory', 'EAR Sections', 10),
(UUID(), @ear, 'EAR-LD',   'License Determination',
 'Determine whether an export license is required by checking the item''s ECCN, destination country, end-user, and end-use; apply for license or use license exception.',
 'mandatory', 'EAR Sections', 20),
(UUID(), @ear, 'EAR-PP',   'Prohibited Parties Screening',
 'Screen all parties in transactions against denied persons, entities, and unverified lists; do not export to embargoed destinations (OFAC).',
 'mandatory', 'EAR Sections', 30),
(UUID(), @ear, 'EAR-DE',   'Deemed Export Controls',
 'Control the release of controlled technology to foreign nationals within the US (deemed export); obtain license if required by the item''s ECCN.',
 'mandatory', 'EAR Sections', 40),
(UUID(), @ear, 'EAR-RK',   'Recordkeeping',
 'Maintain export records for 5 years from the date of export, re-export, or transfer; records must be available for inspection by BIS.',
 'mandatory', 'EAR Sections', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- CJIS Security Policy — Policy Areas
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @cjis, 'CJIS-IEA',  'Information Exchange Agreements',
 'All agencies accessing CJI must have a signed CJIS Security Addendum establishing roles, responsibilities, and security commitments.',
 'mandatory', 'CJIS Policy Areas', 10),
(UUID(), @cjis, 'CJIS-SAT',  'Security Awareness Training',
 'All personnel with access to CJI must complete security awareness training within 6 months of assignment and biennially thereafter.',
 'mandatory', 'CJIS Policy Areas', 20),
(UUID(), @cjis, 'CJIS-IR',   'Incident Response',
 'Agencies must have an incident response plan for CJI-related incidents and report breaches to the FBI CJIS Division within 24 hours.',
 'mandatory', 'CJIS Policy Areas', 30),
(UUID(), @cjis, 'CJIS-AA',   'Auditing and Accountability',
 'Agencies must log and audit all access to CJI and retain audit logs for a minimum period; logs must be available for review.',
 'mandatory', 'CJIS Policy Areas', 40),
(UUID(), @cjis, 'CJIS-AC',   'Access Control',
 'Access to CJI must be restricted to authorized personnel on a need-to-know basis; implement least privilege and separation of duties.',
 'mandatory', 'CJIS Policy Areas', 50),
(UUID(), @cjis, 'CJIS-IA',   'Identification and Authentication',
 'Multi-factor authentication is required for all remote access to CJI; strong password requirements apply for local access.',
 'mandatory', 'CJIS Policy Areas', 60),
(UUID(), @cjis, 'CJIS-CM',   'Configuration Management',
 'Agencies must maintain baseline configurations for systems processing CJI and document authorized software, hardware, and firmware.',
 'mandatory', 'CJIS Policy Areas', 70),
(UUID(), @cjis, 'CJIS-MP',   'Media Protection',
 'Physical and digital media containing CJI must be securely stored, transported, and sanitized or destroyed when no longer needed.',
 'mandatory', 'CJIS Policy Areas', 80),
(UUID(), @cjis, 'CJIS-PE',   'Physical Protection',
 'Access to facilities housing CJI must be controlled; visitor logs must be maintained; equipment must be protected from theft and tampering.',
 'mandatory', 'CJIS Policy Areas', 90),
(UUID(), @cjis, 'CJIS-SC',   'Systems and Communications Protection',
 'CJI must be encrypted in transit using FIPS 140-2 validated encryption; network boundary protections must be implemented.',
 'mandatory', 'CJIS Policy Areas', 100),
(UUID(), @cjis, 'CJIS-FA',   'Formal Audits',
 'The FBI CJIS Division conducts triennial compliance audits of all CSAs; CSAs must conduct annual self-assessments of local agencies.',
 'mandatory', 'CJIS Policy Areas', 110),
(UUID(), @cjis, 'CJIS-PS',   'Personnel Security',
 'Personnel with access to CJI must undergo fingerprint-based background checks before access is granted.',
 'mandatory', 'CJIS Policy Areas', 120),
(UUID(), @cjis, 'CJIS-MD',   'Mobile Devices',
 'Mobile devices used to access CJI must meet encryption, authentication, and remote wipe requirements as defined in the CJIS Security Policy.',
 'mandatory', 'CJIS Policy Areas', 130);


-- ═══════════════════════════════════════════════════════════════════════════════
-- DISA STIGs — Severity Categories and Domains
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @stigs, 'CAT-I',   'CAT I — Critical Findings',
 'Vulnerabilities whose exploitation will directly and immediately result in loss of confidentiality, availability, or integrity; must be mitigated immediately.',
 'mandatory', 'STIG Severity Categories', 10),
(UUID(), @stigs, 'CAT-II',  'CAT II — High Findings',
 'Vulnerabilities that have potential to result in loss of CIA with significant vulnerability exploitation; must be mitigated within 30 days.',
 'mandatory', 'STIG Severity Categories', 20),
(UUID(), @stigs, 'CAT-III', 'CAT III — Medium Findings',
 'Vulnerabilities that degrade measures to protect against loss of CIA; should be mitigated within 90 days.',
 'mandatory', 'STIG Severity Categories', 30),
(UUID(), @stigs, 'DOM-OS',  'Domain: Operating System STIGs',
 'Configuration requirements for Windows, Linux/Unix, macOS, and other operating systems used in DoD environments.',
 'mandatory', 'STIG Domains', 40),
(UUID(), @stigs, 'DOM-NET', 'Domain: Network Device STIGs',
 'Configuration requirements for routers, switches, firewalls, and other network infrastructure components.',
 'mandatory', 'STIG Domains', 50),
(UUID(), @stigs, 'DOM-APP', 'Domain: Application STIGs',
 'Configuration requirements for enterprise applications, cloud services, and general-purpose application software.',
 'mandatory', 'STIG Domains', 60),
(UUID(), @stigs, 'DOM-DB',  'Domain: Database STIGs',
 'Configuration requirements for relational databases including Oracle, MS SQL Server, PostgreSQL, and other DBMS platforms.',
 'mandatory', 'STIG Domains', 70),
(UUID(), @stigs, 'DOM-WEB', 'Domain: Web Server STIGs',
 'Configuration requirements for web servers, web application firewalls, and web-based application hosting platforms.',
 'mandatory', 'STIG Domains', 80);


-- ═══════════════════════════════════════════════════════════════════════════════
-- DoD SRG — Impact Levels
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @dodsrg, 'IL2',  'Impact Level 2 (IL2)',
 'Non-CUI public data with no individual, organizational, or national security concerns; equivalent to FedRAMP Low.',
 'mandatory', 'DoD Impact Levels', 10),
(UUID(), @dodsrg, 'IL4',  'Impact Level 4 (IL4)',
 'Controlled Unclassified Information (CUI); equivalent to FedRAMP Moderate with additional DoD-specific controls.',
 'mandatory', 'DoD Impact Levels', 20),
(UUID(), @dodsrg, 'IL5',  'Impact Level 5 (IL5)',
 'CUI requiring higher protection levels and National Security Systems (NSS) in unclassified form; stricter access and boundary controls.',
 'mandatory', 'DoD Impact Levels', 30),
(UUID(), @dodsrg, 'IL6',  'Impact Level 6 (IL6)',
 'Classified information up to SECRET; requires cloud environments specifically authorized for classified processing.',
 'mandatory', 'DoD Impact Levels', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- TIC 3.0 — Use Cases
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @tic30, 'TIC-TRAD',  'Traditional TIC Use Case',
 'Agencies routing traffic through TIC access points for traditional on-premises network environments with centralized security capabilities.',
 'mandatory', 'TIC 3.0 Use Cases', 10),
(UUID(), @tic30, 'TIC-REM',   'Remote User Use Case',
 'Security capabilities required when agency users access resources from remote locations outside agency-controlled facilities.',
 'mandatory', 'TIC 3.0 Use Cases', 20),
(UUID(), @tic30, 'TIC-CLOUD', 'Cloud Use Case',
 'Security capabilities for agencies using cloud services, including SaaS, PaaS, and IaaS, with appropriate telemetry and security controls.',
 'mandatory', 'TIC 3.0 Use Cases', 30),
(UUID(), @tic30, 'TIC-CAMP',  'Campus Branch Use Case',
 'Security capabilities for agency branch offices and campus networks connecting to enterprise resources and the internet.',
 'mandatory', 'TIC 3.0 Use Cases', 40),
(UUID(), @tic30, 'TIC-BYOD',  'BYOD Use Case',
 'Security capabilities for Bring Your Own Device scenarios where personally owned devices access agency resources.',
 'recommended', 'TIC 3.0 Use Cases', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- GovRAMP — Authorization Levels
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @govramp, 'GR-LISAAS', 'LI-SaaS Authorization',
 'Low-impact Software as a Service authorization for cloud services with limited functionality and low risk data; streamlined assessment process.',
 'mandatory', 'GovRAMP Authorization Levels', 10),
(UUID(), @govramp, 'GR-LOW',    'Low Authorization',
 'Authorization for cloud services processing low-impact data; based on NIST SP 800-53 Low baseline controls.',
 'mandatory', 'GovRAMP Authorization Levels', 20),
(UUID(), @govramp, 'GR-MOD',    'Moderate Authorization',
 'Authorization for cloud services processing Controlled Unclassified Information; based on NIST SP 800-53 Moderate baseline controls.',
 'mandatory', 'GovRAMP Authorization Levels', 30),
(UUID(), @govramp, 'GR-HIGH',   'High Authorization',
 'Authorization for cloud services processing highly sensitive state/local government data; based on NIST SP 800-53 High baseline controls.',
 'mandatory', 'GovRAMP Authorization Levels', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- IRAP (Australia) — Tiers and Phases
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @irap, 'IRAP-PROT',  'Protected Classification Tier',
 'Assessment of systems processing information up to and including the PROTECTED classification level against Australian Government ISM controls.',
 'mandatory', 'IRAP Assessment Tiers', 10),
(UUID(), @irap, 'IRAP-SEC',   'Secret Classification Tier',
 'Assessment of systems processing SECRET classified information against enhanced ISM controls and additional ASD requirements.',
 'mandatory', 'IRAP Assessment Tiers', 20),
(UUID(), @irap, 'IRAP-TS',    'Top Secret Classification Tier',
 'Assessment of systems processing TOP SECRET classified information; requires ASD direct involvement and the highest level of security controls.',
 'mandatory', 'IRAP Assessment Tiers', 30),
(UUID(), @irap, 'IRAP-PHASE', 'Assessment Process Phases',
 'IRAP assessments follow defined phases: scoping, documentation review, technical testing, report preparation, and ASD endorsement.',
 'mandatory', 'IRAP Assessment Tiers', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ENS (Spain) — Dimensions and Security Levels
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @ens, 'ENS-CON',  'Dimension: Confidentiality',
 'Requirements ensuring that information is accessible only to authorized persons and preventing unauthorized disclosure.',
 'mandatory', 'ENS Dimensions', 10),
(UUID(), @ens, 'ENS-INT',  'Dimension: Integrity',
 'Requirements ensuring that information and systems are not altered or destroyed in an unauthorized manner.',
 'mandatory', 'ENS Dimensions', 20),
(UUID(), @ens, 'ENS-AVA',  'Dimension: Availability',
 'Requirements ensuring that information and systems are accessible and usable on demand by authorized users.',
 'mandatory', 'ENS Dimensions', 30),
(UUID(), @ens, 'ENS-AUT',  'Dimension: Authenticity',
 'Requirements for verifying the identity of users, processes, and systems to prevent impersonation.',
 'mandatory', 'ENS Dimensions', 40),
(UUID(), @ens, 'ENS-TRA',  'Dimension: Traceability',
 'Requirements ensuring that actions of entities can be traced back to those entities to support accountability and forensic investigation.',
 'mandatory', 'ENS Dimensions', 50),
(UUID(), @ens, 'ENS-BAS',  'Security Level: Basic',
 'Baseline security level for systems where breach would cause limited damage; applies fundamental organizational and technical security measures.',
 'mandatory', 'ENS Security Levels', 60),
(UUID(), @ens, 'ENS-MED',  'Security Level: Medium',
 'Intermediate security level for systems where breach would cause significant damage; requires additional technical controls and formal risk management.',
 'mandatory', 'ENS Security Levels', 70),
(UUID(), @ens, 'ENS-HIG',  'Security Level: High',
 'Highest security level for systems where breach would cause very serious or catastrophic damage; requires comprehensive security measures and formal certification.',
 'mandatory', 'ENS Security Levels', 80);
