-- Migration 020: Industry-specific and sector frameworks
-- Frameworks: FDA 21 CFR Part 11, GxP, EU MDR, NERC CIP, TISAX, GSMA NESAS,
--             C2M2, PCI PIN, PCI P2PE, PCI SSF, IMO Maritime Cyber,
--             DO-326A, NIST ICS Framework, CIS Kubernetes, AWS Well-Architected,
--             Azure Security Benchmark v3, Google Cloud Security Foundations

SET @fda21cfr  = 'a7b8c9d0-0020-0001-0001-000000000001';
SET @gxp       = 'a7b8c9d0-0020-0002-0001-000000000001';
SET @eumdr     = 'a7b8c9d0-0020-0003-0001-000000000001';
SET @nerccip   = 'a7b8c9d0-0020-0004-0001-000000000001';
SET @tisax     = 'a7b8c9d0-0020-0005-0001-000000000001';
SET @gsmanesas = 'a7b8c9d0-0020-0006-0001-000000000001';
SET @c2m2      = 'a7b8c9d0-0020-0007-0001-000000000001';
SET @pcipin    = 'a7b8c9d0-0020-0008-0001-000000000001';
SET @pcip2pe   = 'a7b8c9d0-0020-0009-0001-000000000001';
SET @pcissf    = 'a7b8c9d0-0020-0010-0001-000000000001';
SET @imocyber  = 'a7b8c9d0-0020-0011-0001-000000000001';
SET @do326a    = 'a7b8c9d0-0020-0012-0001-000000000001';
SET @nisticsfw = 'a7b8c9d0-0020-0013-0001-000000000001';
SET @cisk8s    = 'a7b8c9d0-0020-0014-0001-000000000001';
SET @awswaf    = 'a7b8c9d0-0020-0015-0001-000000000001';
SET @azurebmk  = 'a7b8c9d0-0020-0016-0001-000000000001';
SET @gcpsec    = 'a7b8c9d0-0020-0017-0001-000000000001';

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

INSERT IGNORE INTO regulatory_frameworks
  (id, code, name, description, version, issuing_body, is_global, effective_date)
VALUES
  (@fda21cfr, 'FDA 21 CFR Part 11',
   'FDA 21 CFR Part 11 — Electronic Records; Electronic Signatures',
   'US FDA regulation establishing criteria under which electronic records and electronic signatures are considered trustworthy and equivalent to paper records.',
   '2003 Guidance', 'US FDA', FALSE, '1997-08-20'),

  (@gxp, 'GxP',
   'Good Practice (GxP) Guidelines — Pharmaceutical and Life Sciences',
   'A collection of quality guidelines and regulations for pharmaceutical, medical device, and food industries covering manufacturing, laboratory, clinical, and distribution practices.',
   '2023', 'WHO / EMA / FDA', TRUE, '1990-01-01'),

  (@eumdr, 'EU MDR',
   'EU Medical Device Regulation (2017/745)',
   'EU regulation establishing requirements for the placing on the market and putting into service of medical devices and accessories for human use.',
   '2017/745', 'European Parliament', FALSE, '2021-05-26'),

  (@nerccip, 'NERC CIP',
   'NERC Critical Infrastructure Protection (CIP) Standards',
   'Mandatory cybersecurity standards for the North American electric utility sector covering physical and cyber security of bulk electric system assets.',
   'CIP-013-2 (2023)', 'NERC', FALSE, '2008-06-01'),

  (@tisax, 'TISAX',
   'Trusted Information Security Assessment Exchange (TISAX)',
   'Automotive industry information security assessment and exchange mechanism based on VDA ISA, enabling OEMs and suppliers to share security assessment results.',
   '6.0', 'ENX Association / VDA', TRUE, '2017-04-01'),

  (@gsmanesas, 'GSMA NESAS',
   'GSMA Network Equipment Security Assurance Scheme (NESAS)',
   'Industry-wide security assurance framework for mobile network equipment, covering vendor development and product lifecycle management and network product security.',
   '3.0', 'GSMA', TRUE, '2020-09-01'),

  (@c2m2, 'C2M2',
   'Cybersecurity Capability Maturity Model (C2M2)',
   'Voluntary program helping energy sector and other critical infrastructure organizations evaluate and improve their cybersecurity capabilities across 10 domains.',
   '2.1', 'US DOE', FALSE, '2012-01-01'),

  (@pcipin, 'PCI PIN Security',
   'PCI PIN Security Requirements',
   'PCI SSC standard establishing requirements for the secure management, processing, and transmission of personal identification numbers (PINs) in the payment card industry.',
   'v3.1', 'PCI SSC', TRUE, '2018-06-01'),

  (@pcip2pe, 'PCI P2PE',
   'PCI Point-to-Point Encryption (P2PE) Standard',
   'PCI SSC standard for point-to-point encryption solutions protecting account data from the point of interaction through decryption in a secure environment.',
   'v3.0', 'PCI SSC', TRUE, '2019-08-01'),

  (@pcissf, 'PCI SSF',
   'PCI Software Security Framework (SSF)',
   'Replaces PA-DSS with two standards: Secure SLC for software lifecycle security and Secure Software for payment software security requirements.',
   'v1.2', 'PCI SSC', TRUE, '2022-01-01'),

  (@imocyber, 'IMO Cyber Risk Guidelines',
   'IMO Maritime Cyber Risk Management Guidelines (MSC-FAL.1/Circ.3)',
   'IMO guidelines providing high-level recommendations on maritime cyber risk management to safeguard shipping from current and emerging cyber threats.',
   '2017', 'International Maritime Organization', TRUE, '2017-07-05'),

  (@do326a, 'DO-326A',
   'DO-326A — Airworthiness Security Process Specification (Aviation Cybersecurity)',
   'RTCA/EUROCAE standard defining processes for cybersecurity of aircraft systems and airborne electronic hardware/software to achieve airworthiness certification.',
   'DO-326A/ED-202A', 'RTCA / EUROCAE', TRUE, '2014-09-01'),

  (@nisticsfw, 'NIST ICS Framework',
   'NIST Cybersecurity Framework Applied to Industrial Control Systems (ICS/SCADA)',
   'Application of the NIST CSF five core functions to industrial control systems, operational technology, and SCADA environments with sector-specific guidance.',
   '1.1', 'NIST / ICS-CERT', TRUE, '2018-04-16'),

  (@cisk8s, 'CIS Kubernetes Benchmark',
   'CIS Kubernetes Benchmark',
   'Prescriptive configuration guidance for hardening Kubernetes clusters, covering control plane components, worker nodes, policies, and networking.',
   'v1.8.0', 'Center for Internet Security', TRUE, '2023-07-01'),

  (@awswaf, 'AWS Well-Architected Security',
   'AWS Well-Architected Framework — Security Pillar',
   'AWS guidance for applying security best practices across identity and access management, detection, infrastructure protection, data protection, and incident response.',
   '2023', 'Amazon Web Services', FALSE, '2015-11-01'),

  (@azurebmk, 'Azure Security Benchmark v3',
   'Microsoft Cloud Security Benchmark v3 (Azure Security Benchmark)',
   'Microsoft''s security best practices and recommendations for Azure, covering 12 control domains mapped to industry standards including CIS Controls and NIST.',
   'v3.0', 'Microsoft', FALSE, '2022-10-01'),

  (@gcpsec, 'GCP Security Foundations',
   'Google Cloud Security Foundations Blueprint',
   'Google Cloud''s prescriptive security guidance covering organization policy, IAM, networking, monitoring, data security, and incident response best practices.',
   '2023', 'Google Cloud', FALSE, '2022-01-01');


-- ═══════════════════════════════════════════════════════════════════════════════
-- FDA 21 CFR Part 11 — Requirements
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @fda21cfr, '11.10',   'Closed System Controls',
 'Persons using closed systems to create, modify, maintain, or transmit electronic records shall employ validated systems, accurate and complete copies, record protection, and computer-generated audit trails.',
 'mandatory', 'Part 11 Requirements', 10),
(UUID(), @fda21cfr, '11.30',   'Open System Controls',
 'Persons using open systems to create, modify, maintain, or transmit electronic records shall employ additional measures to ensure record authenticity and integrity.',
 'mandatory', 'Part 11 Requirements', 20),
(UUID(), @fda21cfr, '11.50',   'Electronic Signature Requirements',
 'Electronic signatures shall include the printed name of the signer, date and time, and the meaning associated with the signature; must be linked to respective electronic records.',
 'mandatory', 'Part 11 Requirements', 30),
(UUID(), @fda21cfr, '11.100',  'General Electronic Signature Provisions',
 'Each electronic signature must be unique to one individual and must not be reused or reassigned; organizations must verify identity before assigning signatures.',
 'mandatory', 'Part 11 Requirements', 40),
(UUID(), @fda21cfr, '11.200',  'Electronic Signature Components and Controls',
 'Electronic signatures must employ at least two distinct identification components (e.g., ID code and password); non-biometric signatures require periodic re-authentication.',
 'mandatory', 'Part 11 Requirements', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- GxP — Good Practice Types
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @gxp, 'GMP',  'Good Manufacturing Practice (GMP)',
 'Quality standards governing the manufacture of pharmaceuticals, biologics, and medical devices to ensure products are consistently produced and controlled to quality standards.',
 'mandatory', 'GxP Types', 10),
(UUID(), @gxp, 'GLP',  'Good Laboratory Practice (GLP)',
 'Quality system for non-clinical laboratory studies, ensuring systematic, well-documented, and reproducible results for safety studies submitted to regulatory authorities.',
 'mandatory', 'GxP Types', 20),
(UUID(), @gxp, 'GCP',  'Good Clinical Practice (GCP)',
 'International ethical and scientific quality standard for designing, conducting, recording, and reporting clinical trials involving human subjects.',
 'mandatory', 'GxP Types', 30),
(UUID(), @gxp, 'GDP',  'Good Distribution Practice (GDP)',
 'Quality framework ensuring that medicinal products are consistently stored, transported, and handled under suitable conditions as required by marketing authorization.',
 'mandatory', 'GxP Types', 40),
(UUID(), @gxp, 'GVP',  'Good Pharmacovigilance Practice (GVP)',
 'Guidelines for post-marketing surveillance of medicinal products, covering safety monitoring, signal detection, risk management, and regulatory reporting.',
 'mandatory', 'GxP Types', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- EU MDR (2017/745) — Chapters
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @eumdr, 'MDR-I',    'Chapter I: General Provisions and Definitions',
 'Defines scope, definitions, and purpose of the EU MDR including what constitutes a medical device and accessories for the regulation.',
 'mandatory', 'EU MDR Chapters', 10),
(UUID(), @eumdr, 'MDR-II',   'Chapter II: Making Available on the Market and Putting into Service',
 'Requirements for economic operators (manufacturers, importers, distributors), registration in EUDAMED, and UDI system implementation.',
 'mandatory', 'EU MDR Chapters', 20),
(UUID(), @eumdr, 'MDR-III',  'Chapter III: Device Identification and Traceability',
 'Unique Device Identification (UDI) system requirements, EUDAMED registration, and implant card requirements for traceability throughout the supply chain.',
 'mandatory', 'EU MDR Chapters', 30),
(UUID(), @eumdr, 'MDR-IV',   'Chapter IV: Notified Bodies',
 'Requirements for designation, monitoring, and operational standards for notified bodies conducting conformity assessments.',
 'mandatory', 'EU MDR Chapters', 40),
(UUID(), @eumdr, 'MDR-V',    'Chapter V: Classification and Conformity Assessment',
 'Classification rules (Class I, IIa, IIb, III) and applicable conformity assessment routes for demonstrating compliance with General Safety and Performance Requirements.',
 'mandatory', 'EU MDR Chapters', 50),
(UUID(), @eumdr, 'MDR-VI',   'Chapter VI: Clinical Evidence and Investigations',
 'Requirements for clinical evaluation, clinical investigations, post-market clinical follow-up, and PMCF to demonstrate clinical benefit and safety.',
 'mandatory', 'EU MDR Chapters', 60),
(UUID(), @eumdr, 'MDR-VII',  'Chapter VII: Post-Market Surveillance',
 'Proactive PMS system requirements including PMS plan, PSUR, periodic safety update reports, and serious incident reporting.',
 'mandatory', 'EU MDR Chapters', 70),
(UUID(), @eumdr, 'MDR-VIII', 'Chapter VIII: Vigilance and Market Surveillance',
 'Serious incident and field safety corrective action reporting to competent authorities; market surveillance by authorities.',
 'mandatory', 'EU MDR Chapters', 80),
(UUID(), @eumdr, 'MDR-IX',   'Chapter IX: Confidentiality, Data Protection, Funding and Penalties',
 'Provisions on confidentiality of information, GDPR applicability to personal data processed under MDR, and member state penalties.',
 'mandatory', 'EU MDR Chapters', 90);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NERC CIP — Standards
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @nerccip, 'CIP-002',  'CIP-002: BES Cyber System Categorization',
 'Identify and categorize Bulk Electric System (BES) Cyber Systems and their associated BES Cyber Assets for the application of NERC CIP cybersecurity standards.',
 'mandatory', 'NERC CIP Standards', 10),
(UUID(), @nerccip, 'CIP-003',  'CIP-003: Security Management Controls',
 'Specify consistent and sustainable security management controls that establish responsibility and accountability to protect BES Cyber Systems.',
 'mandatory', 'NERC CIP Standards', 20),
(UUID(), @nerccip, 'CIP-004',  'CIP-004: Personnel and Training',
 'Minimize the risk against compromise of BES Cyber Systems through personnel security awareness training, personnel risk assessments, and access management.',
 'mandatory', 'NERC CIP Standards', 30),
(UUID(), @nerccip, 'CIP-005',  'CIP-005: Electronic Security Perimeter(s)',
 'Manage electronic access to BES Cyber Systems by defining Electronic Security Perimeters and implementing controls for interactive remote access.',
 'mandatory', 'NERC CIP Standards', 40),
(UUID(), @nerccip, 'CIP-006',  'CIP-006: Physical Security of BES Cyber Systems',
 'Manage physical access to BES Cyber Systems by implementing physical security controls, logging, and visitor management.',
 'mandatory', 'NERC CIP Standards', 50),
(UUID(), @nerccip, 'CIP-007',  'CIP-007: Systems Security Management',
 'Manage system security for BES Cyber Systems through ports and services management, security patch management, malicious code prevention, and event logging.',
 'mandatory', 'NERC CIP Standards', 60),
(UUID(), @nerccip, 'CIP-008',  'CIP-008: Incident Reporting and Response Planning',
 'Mitigate impact of cybersecurity incidents through preparation, response, and recovery for incidents that affect BES reliability.',
 'mandatory', 'NERC CIP Standards', 70),
(UUID(), @nerccip, 'CIP-009',  'CIP-009: Recovery Plans for BES Cyber Systems',
 'Recover reliability functions performed by BES Cyber Systems following a cybersecurity incident through documented recovery plans and exercises.',
 'mandatory', 'NERC CIP Standards', 80),
(UUID(), @nerccip, 'CIP-010',  'CIP-010: Configuration Change Management and Vulnerability Management',
 'Prevent and detect unauthorized changes to BES Cyber Systems through baseline configuration management and vulnerability assessments.',
 'mandatory', 'NERC CIP Standards', 90),
(UUID(), @nerccip, 'CIP-011',  'CIP-011: Information Protection',
 'Prevent unauthorized access to BES Cyber System Information by implementing information protection policies and media disposal procedures.',
 'mandatory', 'NERC CIP Standards', 100),
(UUID(), @nerccip, 'CIP-013',  'CIP-013: Supply Chain Risk Management',
 'Mitigate cybersecurity risks in the supply chain for industrial control system hardware, software, and services for high and medium impact BES Cyber Systems.',
 'mandatory', 'NERC CIP Standards', 110);


-- ═══════════════════════════════════════════════════════════════════════════════
-- TISAX — Assessment Objectives
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @tisax, 'TISAX-IS',  'Information Security Assessment',
 'Assessment of information security controls based on the VDA ISA catalogue covering organizational measures, technical controls, and supplier management.',
 'mandatory', 'TISAX Assessment Objectives', 10),
(UUID(), @tisax, 'TISAX-PP',  'Prototype Protection Assessment',
 'Enhanced assessment for organizations handling vehicle prototypes or test vehicles, requiring additional physical security measures.',
 'mandatory', 'TISAX Assessment Objectives', 20),
(UUID(), @tisax, 'TISAX-DP',  'Data Protection Assessment',
 'Assessment of compliance with GDPR and automotive industry data protection requirements for handling personal data of employees and customers.',
 'mandatory', 'TISAX Assessment Objectives', 30);


-- ═══════════════════════════════════════════════════════════════════════════════
-- GSMA NESAS — Domains
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @gsmanesas, 'NESAS-DLC',  'Development and Lifecycle Management',
 'Security requirements and audit criteria for vendor development processes covering SDLC security, vulnerability management, patch processes, and end-of-life management.',
 'mandatory', 'NESAS Domains', 10),
(UUID(), @gsmanesas, 'NESAS-NPC',  'Network Product Class Requirements',
 'Security evaluation requirements for specific network product classes covering 5G, LTE, IMS, and other mobile network equipment configurations.',
 'mandatory', 'NESAS Domains', 20);


-- ═══════════════════════════════════════════════════════════════════════════════
-- C2M2 — 10 Domains
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @c2m2, 'C2M2-ACM',  'Asset, Change, and Configuration Management (ACM)',
 'Manage IT and OT assets and changes to those assets, including configurations and their impact on cybersecurity risk.',
 'mandatory', 'C2M2 Domains', 10),
(UUID(), @c2m2, 'C2M2-IAM',  'Identity and Access Management (IAM)',
 'Create and manage identities, authenticate users and non-user entities, and authorize access to assets commensurate with risk.',
 'mandatory', 'C2M2 Domains', 20),
(UUID(), @c2m2, 'C2M2-TVM',  'Threat and Vulnerability Management (TVM)',
 'Establish and maintain plans, procedures, and technologies to detect, identify, analyze, manage, and respond to threats and vulnerabilities.',
 'mandatory', 'C2M2 Domains', 30),
(UUID(), @c2m2, 'C2M2-SA',   'Situational Awareness (SA)',
 'Establish and maintain activities and technologies to collect, analyze, and report cybersecurity information for an operating picture.',
 'mandatory', 'C2M2 Domains', 40),
(UUID(), @c2m2, 'C2M2-ISC',  'Information Sharing and Communications (ISC)',
 'Establish and maintain relationships with internal and external entities to collect and provide cybersecurity information.',
 'mandatory', 'C2M2 Domains', 50),
(UUID(), @c2m2, 'C2M2-EIR',  'Event and Incident Response, Continuity of Operations (EIR)',
 'Establish and maintain plans, procedures, and technologies to detect, analyze, mitigate, and recover from cybersecurity events and incidents.',
 'mandatory', 'C2M2 Domains', 60),
(UUID(), @c2m2, 'C2M2-SCM',  'Supply Chain and External Dependencies Management (SCM)',
 'Establish and maintain controls to manage cybersecurity risks associated with services and assets that are dependent on external entities.',
 'mandatory', 'C2M2 Domains', 70),
(UUID(), @c2m2, 'C2M2-WM',   'Workforce Management (WM)',
 'Establish and maintain plans, procedures, technologies, and controls to create a cybersecurity workforce and culture.',
 'mandatory', 'C2M2 Domains', 80),
(UUID(), @c2m2, 'C2M2-CPM',  'Cybersecurity Program Management (CPM)',
 'Establish and maintain an enterprise cybersecurity program that provides governance, strategic planning, and sponsorship for the organization''s program.',
 'mandatory', 'C2M2 Domains', 90),
(UUID(), @c2m2, 'C2M2-PM',   'Program Management (PROG)',
 'Establish and manage cybersecurity program activities, including funding, budgeting, and reporting to organizational leadership.',
 'mandatory', 'C2M2 Domains', 100);


-- ═══════════════════════════════════════════════════════════════════════════════
-- PCI PIN Security — Control Objectives
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @pcipin, 'PIN-KM',   'Key Management Control Objectives',
 'Cryptographic key management covering key generation, distribution, injection, storage, use, destruction, and key compromise recovery procedures.',
 'mandatory', 'PCI PIN Objectives', 10),
(UUID(), @pcipin, 'PIN-DM',   'Device Management Control Objectives',
 'Physical and logical security requirements for PIN entry devices including approval, inventory, installation, maintenance, and decommissioning.',
 'mandatory', 'PCI PIN Objectives', 20),
(UUID(), @pcipin, 'PIN-EA',   'Encryption Algorithms and Key Lengths',
 'Approved encryption algorithms and minimum key lengths for PIN protection in compliance with current cryptographic standards.',
 'mandatory', 'PCI PIN Objectives', 30),
(UUID(), @pcipin, 'PIN-PB',   'PIN Block Formats',
 'Requirements for PIN block formats (ISO 9564) used to securely encode PINs for transmission and processing.',
 'mandatory', 'PCI PIN Objectives', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- PCI P2PE — Domains
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @pcip2pe, 'P2PE-1',  'P2PE Solution Components and Security',
 'Requirements for P2PE solution architecture ensuring that account data is encrypted at the point of interaction and never exposed in the merchant environment.',
 'mandatory', 'P2PE Domains', 10),
(UUID(), @pcip2pe, 'P2PE-2',  'Encryption at the Point of Interaction',
 'Hardware, software, and procedural requirements for encrypting cardholder data at the point-of-interaction device before transmission.',
 'mandatory', 'P2PE Domains', 20),
(UUID(), @pcip2pe, 'P2PE-3',  'Decryption and Decryption Environments',
 'Security requirements for the decryption environment, ensuring it is PCI DSS compliant and that decrypted data is protected.',
 'mandatory', 'P2PE Domains', 30),
(UUID(), @pcip2pe, 'P2PE-4',  'P2PE Solution Key Management',
 'Cryptographic key management procedures for P2PE solutions covering key generation, injection, distribution, and destruction.',
 'mandatory', 'P2PE Domains', 40),
(UUID(), @pcip2pe, 'P2PE-5',  'Application Security in P2PE Solutions',
 'Security requirements for payment applications that are part of a P2PE solution, covering secure coding and application integrity.',
 'mandatory', 'P2PE Domains', 50),
(UUID(), @pcip2pe, 'P2PE-6',  'P2PE Monitoring and Response',
 'Monitoring, logging, and incident response requirements for P2PE solutions to detect and respond to tampering and compromise.',
 'mandatory', 'P2PE Domains', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- PCI SSF — Two Standards
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @pcissf, 'SSF-SLC',  'Secure SLC Standard — Software Lifecycle Security',
 'Requirements for software vendors to demonstrate their software lifecycle management processes are secure, covering SSDLC, threat modeling, and vulnerability management.',
 'mandatory', 'PCI SSF Standards', 10),
(UUID(), @pcissf, 'SSF-SS',   'Secure Software Standard — Payment Software Security',
 'Requirements for payment software to be designed and developed securely, covering critical security controls for protecting account data during processing.',
 'mandatory', 'PCI SSF Standards', 20);


-- ═══════════════════════════════════════════════════════════════════════════════
-- IMO Maritime Cyber Risk Guidelines — Functional Elements
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @imocyber, 'IMO-ID',   'Identify',
 'Identify risks to OT and IT systems, define roles and responsibilities for cyber risk management, and establish a cyber risk baseline for shipboard systems.',
 'mandatory', 'IMO Functional Elements', 10),
(UUID(), @imocyber, 'IMO-PR',   'Protect',
 'Implement risk control processes and measures to protect against identified cyber risks affecting shipboard systems and networks.',
 'mandatory', 'IMO Functional Elements', 20),
(UUID(), @imocyber, 'IMO-DT',   'Detect',
 'Develop and implement activities to detect cyber threats and vulnerabilities affecting shipboard systems on a timely basis.',
 'mandatory', 'IMO Functional Elements', 30),
(UUID(), @imocyber, 'IMO-RS',   'Respond',
 'Develop and implement contingency plans for responding to cyber incidents affecting shipboard systems and operations.',
 'mandatory', 'IMO Functional Elements', 40),
(UUID(), @imocyber, 'IMO-RC',   'Recover',
 'Identify measures to back up and restore systems affected by a cyber incident and return to safe operations.',
 'mandatory', 'IMO Functional Elements', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- DO-326A — Aviation Cybersecurity Processes
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @do326a, 'DA-SRA',  'Airworthiness Security Risk Assessment',
 'Identify and assess security threats and vulnerabilities for aircraft functions to determine security risk and the need for security controls.',
 'mandatory', 'DO-326A Processes', 10),
(UUID(), @do326a, 'DA-SR',   'Security Requirements Development',
 'Derive security requirements from the risk assessment to achieve acceptable security risk for aircraft airworthiness certification.',
 'mandatory', 'DO-326A Processes', 20),
(UUID(), @do326a, 'DA-AS',   'Security Assurance',
 'Provide evidence that security requirements are correctly implemented and effective through verification, testing, and documentation.',
 'mandatory', 'DO-326A Processes', 30),
(UUID(), @do326a, 'DA-CERT', 'Airworthiness Security Certification',
 'Demonstrate to certification authorities (FAA/EASA) that security risks to aircraft safety are adequately addressed to achieve airworthiness approval.',
 'mandatory', 'DO-326A Processes', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST ICS Framework — 5 Core Functions Applied to ICS
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @nisticsfw, 'ICS-ID',  'Identify (ICS Context)',
 'Develop organizational understanding of cybersecurity risk to ICS/SCADA systems, including OT asset inventory, control system architecture, and ICS-specific risk assessment.',
 'mandatory', 'ICS Core Functions', 10),
(UUID(), @nisticsfw, 'ICS-PR',  'Protect (ICS Context)',
 'Implement safeguards for ICS/SCADA systems including network segmentation, secure remote access, patch management with operational safety considerations, and supply chain security.',
 'mandatory', 'ICS Core Functions', 20),
(UUID(), @nisticsfw, 'ICS-DT',  'Detect (ICS Context)',
 'Implement monitoring capabilities for ICS environments using OT-aware security monitoring tools, anomaly detection, and event logging without impacting operational availability.',
 'mandatory', 'ICS Core Functions', 30),
(UUID(), @nisticsfw, 'ICS-RS',  'Respond (ICS Context)',
 'Develop and implement ICS-specific incident response plans that account for operational safety, process integrity, and coordination between IT and OT teams.',
 'mandatory', 'ICS Core Functions', 40),
(UUID(), @nisticsfw, 'ICS-RC',  'Recover (ICS Context)',
 'Develop resilient ICS/SCADA systems with backup configurations, recovery procedures that prioritize operational safety, and post-incident lessons learned.',
 'mandatory', 'ICS Core Functions', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- CIS Kubernetes Benchmark — Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @cisk8s, 'K8S-CP',   'Control Plane Components',
 'Configuration requirements for the Kubernetes API server, controller manager, scheduler, and etcd to ensure a hardened control plane.',
 'mandatory', 'Kubernetes Benchmark Sections', 10),
(UUID(), @cisk8s, 'K8S-WN',   'Worker Node Security',
 'Hardening requirements for Kubernetes worker nodes including kubelet configuration, node OS hardening, and container runtime security settings.',
 'mandatory', 'Kubernetes Benchmark Sections', 20),
(UUID(), @cisk8s, 'K8S-POL',  'Policies',
 'Kubernetes policy configuration including Pod Security Admission, network policies, RBAC configuration, and namespace isolation.',
 'mandatory', 'Kubernetes Benchmark Sections', 30),
(UUID(), @cisk8s, 'K8S-ETC',  'etcd Security',
 'Security configuration for the etcd key-value store including encryption at rest, TLS peer and client communication, and access control.',
 'mandatory', 'Kubernetes Benchmark Sections', 40),
(UUID(), @cisk8s, 'K8S-NET',  'Network Policies and Segmentation',
 'Network policy requirements for restricting pod-to-pod communication, enforcing namespace isolation, and controlling egress traffic.',
 'mandatory', 'Kubernetes Benchmark Sections', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- AWS Well-Architected Security Pillar — Best Practice Areas
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @awswaf, 'AWS-SEC-1',  'Identity and Access Management',
 'Implement strong identity foundation using least privilege, enable identity federation, and eliminate long-term static credentials using IAM roles and identity policies.',
 'mandatory', 'AWS Security Best Practices', 10),
(UUID(), @awswaf, 'AWS-SEC-2',  'Detection',
 'Configure service-level logging (CloudTrail, VPC Flow Logs, Config), enable GuardDuty, and implement automated alerting and response for security events.',
 'mandatory', 'AWS Security Best Practices', 20),
(UUID(), @awswaf, 'AWS-SEC-3',  'Infrastructure Protection',
 'Protect compute resources using security groups, NACLs, WAF, Shield, and ensure resources are deployed in private subnets with minimal public exposure.',
 'mandatory', 'AWS Security Best Practices', 30),
(UUID(), @awswaf, 'AWS-SEC-4',  'Data Protection',
 'Classify data, encrypt data at rest and in transit, manage encryption keys using KMS, and implement fine-grained data access controls.',
 'mandatory', 'AWS Security Best Practices', 40),
(UUID(), @awswaf, 'AWS-SEC-5',  'Incident Response',
 'Prepare for security incidents using pre-provisioned access, forensic tools, and run books; conduct Game Days to test incident response capabilities.',
 'mandatory', 'AWS Security Best Practices', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- Azure Security Benchmark v3 — Control Domains
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @azurebmk, 'ASB-NS',   'Network Security',
 'Requirements for securing Azure virtual networks, network segmentation, DDoS protection, network filtering, and secure remote access.',
 'mandatory', 'Azure Security Benchmark Domains', 10),
(UUID(), @azurebmk, 'ASB-IM',   'Identity Management',
 'Requirements for using Microsoft Entra ID for authentication, implementing MFA, configuring privileged identity management, and monitoring identity-related risks.',
 'mandatory', 'Azure Security Benchmark Domains', 20),
(UUID(), @azurebmk, 'ASB-PA',   'Privileged Access',
 'Requirements for protecting privileged access to Azure management plane and control plane through PIM, PAW, and just-in-time access.',
 'mandatory', 'Azure Security Benchmark Domains', 30),
(UUID(), @azurebmk, 'ASB-DP',   'Data Protection',
 'Requirements for data discovery, classification, encryption at rest, encryption in transit, and key management using Azure Key Vault.',
 'mandatory', 'Azure Security Benchmark Domains', 40),
(UUID(), @azurebmk, 'ASB-AM',   'Asset Management',
 'Requirements for maintaining inventory of Azure resources, enforcing resource policies, and managing the security posture of cloud assets.',
 'mandatory', 'Azure Security Benchmark Domains', 50),
(UUID(), @azurebmk, 'ASB-LT',   'Logging and Threat Detection',
 'Requirements for enabling Defender for Cloud, configuring Microsoft Sentinel, collecting audit logs, and implementing threat detection rules.',
 'mandatory', 'Azure Security Benchmark Domains', 60),
(UUID(), @azurebmk, 'ASB-IR',   'Incident Response',
 'Requirements for incident response preparation, detection, containment, eradication, and recovery in Azure environments.',
 'mandatory', 'Azure Security Benchmark Domains', 70),
(UUID(), @azurebmk, 'ASB-PV',   'Posture and Vulnerability Management',
 'Requirements for vulnerability scanning, security posture assessment using Microsoft Defender for Cloud Secure Score, and rapid remediation.',
 'mandatory', 'Azure Security Benchmark Domains', 80),
(UUID(), @azurebmk, 'ASB-ES',   'Endpoint Security',
 'Requirements for using Microsoft Defender for Endpoint, enabling threat and vulnerability management, and protecting servers and virtual machines.',
 'mandatory', 'Azure Security Benchmark Domains', 90),
(UUID(), @azurebmk, 'ASB-BR',   'Backup and Recovery',
 'Requirements for using Azure Backup, testing recovery, and ensuring business continuity for critical Azure workloads.',
 'mandatory', 'Azure Security Benchmark Domains', 100),
(UUID(), @azurebmk, 'ASB-DS',   'DevOps Security',
 'Requirements for integrating security into DevOps pipelines using Defender for DevOps, secret scanning, and infrastructure as code security checks.',
 'mandatory', 'Azure Security Benchmark Domains', 110),
(UUID(), @azurebmk, 'ASB-GS',   'Governance and Strategy',
 'Requirements for defining cloud security governance, establishing a cloud security strategy, and defining roles and responsibilities.',
 'mandatory', 'Azure Security Benchmark Domains', 120);


-- ═══════════════════════════════════════════════════════════════════════════════
-- Google Cloud Security Foundations — Pillars
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @gcpsec, 'GCP-ORG',  'Organization Policy and Resource Hierarchy',
 'Configure GCP organization policies, resource hierarchy, and project structure to enforce security guardrails and governance at scale.',
 'mandatory', 'GCP Security Pillars', 10),
(UUID(), @gcpsec, 'GCP-IAM',  'Identity and Access Management',
 'Implement least-privilege IAM using predefined and custom roles, enable Workload Identity Federation, and audit IAM configurations regularly.',
 'mandatory', 'GCP Security Pillars', 20),
(UUID(), @gcpsec, 'GCP-NET',  'Networking and Network Security',
 'Design and implement secure VPC architecture, configure Cloud Armor WAF, enable Private Google Access, and restrict external IP addresses.',
 'mandatory', 'GCP Security Pillars', 30),
(UUID(), @gcpsec, 'GCP-MON',  'Logging, Monitoring, and Audit',
 'Enable Cloud Audit Logs, configure Security Command Center, deploy Chronicle SIEM, and implement log sinks for security event correlation.',
 'mandatory', 'GCP Security Pillars', 40),
(UUID(), @gcpsec, 'GCP-DATA', 'Data Security and Encryption',
 'Encrypt data using Cloud KMS, implement Cloud DLP for sensitive data discovery, enforce data residency policies, and manage customer-managed keys.',
 'mandatory', 'GCP Security Pillars', 50),
(UUID(), @gcpsec, 'GCP-IR',   'Incident Response',
 'Establish GCP-specific incident response runbooks, use Assured Workloads for sensitive data, and configure automated responses using Security Command Center findings.',
 'mandatory', 'GCP Security Pillars', 60);
