-- Migration 015: Extended ISO/IEC standards and related frameworks
-- Frameworks: ISO 27002:2022, 27005:2022, 27031, 27032, 27035, 27036, 15408 (CC),
--             ISO 31000, 22313, 22316, 20000-1, 38500, 37301, 37001, 13485,
--             ISO/SAE 21434, IEC 62304, IEC 62443

SET @iso27002  = 'b2c3d4e5-0015-0001-0001-000000000001';
SET @iso27005  = 'b2c3d4e5-0015-0002-0001-000000000001';
SET @iso27031  = 'b2c3d4e5-0015-0003-0001-000000000001';
SET @iso27032  = 'b2c3d4e5-0015-0004-0001-000000000001';
SET @iso27035  = 'b2c3d4e5-0015-0005-0001-000000000001';
SET @iso27036  = 'b2c3d4e5-0015-0006-0001-000000000001';
SET @iso15408  = 'b2c3d4e5-0015-0007-0001-000000000001';
SET @iso31000  = 'b2c3d4e5-0015-0008-0001-000000000001';
SET @iso22313  = 'b2c3d4e5-0015-0009-0001-000000000001';
SET @iso22316  = 'b2c3d4e5-0015-0010-0001-000000000001';
SET @iso20000  = 'b2c3d4e5-0015-0011-0001-000000000001';
SET @iso38500  = 'b2c3d4e5-0015-0012-0001-000000000001';
SET @iso37301  = 'b2c3d4e5-0015-0013-0001-000000000001';
SET @iso37001  = 'b2c3d4e5-0015-0014-0001-000000000001';
SET @iso13485  = 'b2c3d4e5-0015-0015-0001-000000000001';
SET @iso21434  = 'b2c3d4e5-0015-0016-0001-000000000001';
SET @iec62304  = 'b2c3d4e5-0015-0017-0001-000000000001';
SET @iec62443  = 'b2c3d4e5-0015-0018-0001-000000000001';

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

INSERT IGNORE INTO regulatory_frameworks
  (id, code, name, description, version, issuing_body, is_global, effective_date)
VALUES
  (@iso27002, 'ISO 27002:2022',
   'ISO/IEC 27002:2022 — Information Security Controls',
   'Provides reference information security controls organized into four themes covering organizational, people, physical, and technological controls for implementing ISO 27001.',
   '2022', 'ISO/IEC', TRUE, '2022-02-15'),

  (@iso27005, 'ISO 27005:2022',
   'ISO/IEC 27005:2022 — Information Security Risk Management',
   'Provides guidelines for managing information security risks, supporting general concepts in ISO 27001, and aligned with ISO 31000 risk management principles.',
   '2022', 'ISO/IEC', TRUE, '2022-10-24'),

  (@iso27031, 'ISO 27031',
   'ISO/IEC 27031:2011 — ICT Readiness for Business Continuity',
   'Describes the concepts and principles of ICT readiness for business continuity and provides a framework for implementing and testing ICT readiness as part of a BCMS.',
   '2011', 'ISO/IEC', TRUE, '2011-03-01'),

  (@iso27032, 'ISO 27032:2023',
   'ISO/IEC 27032:2023 — Cybersecurity — Guidelines for Internet Security',
   'Provides guidance for improving the state of cybersecurity, covering cybersecurity in the Internet ecosystem with a focus on collaboration among stakeholders.',
   '2023', 'ISO/IEC', TRUE, '2023-06-01'),

  (@iso27035, 'ISO 27035:2023',
   'ISO/IEC 27035:2023 — Information Security Incident Management',
   'Provides structured guidance on information security incident management covering planning, detection, reporting, assessment, response, and lessons learned.',
   '2023', 'ISO/IEC', TRUE, '2023-02-01'),

  (@iso27036, 'ISO 27036:2023',
   'ISO/IEC 27036 — Information Security for Supplier Relationships',
   'Provides guidance on assessing and treating information security risks associated with the acquisition of products and services from suppliers across the supply chain.',
   '2023', 'ISO/IEC', TRUE, '2023-01-01'),

  (@iso15408, 'ISO 15408',
   'ISO/IEC 15408 — Common Criteria for IT Security Evaluation',
   'Defines a framework for expressing security requirements and for evaluating the security properties of IT products and systems through internationally recognized evaluation assurance levels.',
   '2022', 'ISO/IEC', TRUE, '2022-08-01'),

  (@iso31000, 'ISO 31000:2018',
   'ISO 31000:2018 — Risk Management Guidelines',
   'Provides principles and guidelines on managing risk applicable to any type of organization, supporting decision-making and accountability across all levels of an organization.',
   '2018', 'ISO', TRUE, '2018-02-15'),

  (@iso22313, 'ISO 22313:2020',
   'ISO 22313:2020 — Business Continuity Management — Guidance',
   'Provides guidance and recommendations for implementing a business continuity management system conformant with ISO 22301, with explanatory notes and worked examples.',
   '2020', 'ISO', TRUE, '2020-11-01'),

  (@iso22316, 'ISO 22316:2017',
   'ISO 22316:2017 — Security and Resilience — Organizational Resilience Principles and Attributes',
   'Provides guidance to enhance organizational resilience by identifying key attributes and principles that enable organizations to withstand, adapt to, and recover from disruptions.',
   '2017', 'ISO', TRUE, '2017-03-01'),

  (@iso20000, 'ISO 20000-1:2018',
   'ISO/IEC 20000-1:2018 — Information Technology Service Management',
   'Specifies requirements for an organization to establish, implement, maintain, and continually improve a service management system (SMS).',
   '2018', 'ISO/IEC', TRUE, '2018-09-01'),

  (@iso38500, 'ISO 38500:2015',
   'ISO/IEC 38500:2015 — Governance of IT for the Organization',
   'Provides a framework of principles for directors of organizations for the effective, efficient, and acceptable use of information technology.',
   '2015', 'ISO/IEC', TRUE, '2015-02-15'),

  (@iso37301, 'ISO 37301:2021',
   'ISO 37301:2021 — Compliance Management Systems',
   'Specifies requirements and provides guidelines for establishing, developing, implementing, evaluating, maintaining, and improving an effective compliance management system.',
   '2021', 'ISO', TRUE, '2021-04-13'),

  (@iso37001, 'ISO 37001:2016',
   'ISO 37001:2016 — Anti-Bribery Management Systems',
   'Specifies requirements and provides guidance for establishing, implementing, maintaining, reviewing, and improving an anti-bribery management system to prevent, detect, and respond to bribery.',
   '2016', 'ISO', TRUE, '2016-10-15'),

  (@iso13485, 'ISO 13485:2016',
   'ISO 13485:2016 — Medical Devices Quality Management Systems',
   'Specifies requirements for a quality management system where an organization needs to demonstrate its ability to provide medical devices and related services that meet customer and regulatory requirements.',
   '2016', 'ISO', TRUE, '2016-03-01'),

  (@iso21434, 'ISO/SAE 21434:2021',
   'ISO/SAE 21434:2021 — Road Vehicles Cybersecurity Engineering',
   'Specifies engineering requirements for cybersecurity risk management regarding concept, product development, production, operation, maintenance, and decommissioning of road vehicle electrical systems.',
   '2021', 'ISO/SAE', TRUE, '2021-08-31'),

  (@iec62304, 'IEC 62304:2006',
   'IEC 62304:2006 — Medical Device Software Lifecycle Processes',
   'Defines the lifecycle requirements for the development and maintenance of medical device software, covering safety classification and processes proportionate to risk.',
   '2006/AMD1:2015', 'IEC', TRUE, '2006-05-01'),

  (@iec62443, 'IEC 62443',
   'IEC 62443 — Industrial Automation and Control Systems (IACS) Security',
   'Multi-part standard addressing cybersecurity of Industrial Automation and Control Systems, covering requirements for operators, integrators, and component suppliers.',
   '2018', 'IEC', TRUE, '2018-01-01');


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO/IEC 27002:2022 — 4 Themes
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso27002, 'Clause 5',  'Organizational Controls (Clause 5)',
 '37 controls addressing governance, policies, roles, responsibilities, threat intelligence, information security in projects, and supplier relationships.',
 'mandatory', 'Control Themes', 10),
(UUID(), @iso27002, 'Clause 6',  'People Controls (Clause 6)',
 '8 controls covering screening, terms of employment, awareness and training, disciplinary process, remote working, and information security event reporting.',
 'mandatory', 'Control Themes', 20),
(UUID(), @iso27002, 'Clause 7',  'Physical Controls (Clause 7)',
 '14 controls addressing physical security perimeters, entry controls, securing offices and facilities, clear desk/screen policies, and equipment maintenance.',
 'mandatory', 'Control Themes', 30),
(UUID(), @iso27002, 'Clause 8',  'Technological Controls (Clause 8)',
 '34 controls covering user endpoint devices, privileged access, access control, cryptography, secure development, vulnerability management, and network filtering.',
 'mandatory', 'Control Themes', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO/IEC 27005:2022 — Risk Management Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso27005, '27005-4',  'Context Establishment',
 'Define the scope of risk management activities, establish organizational context, and define risk criteria including risk acceptance and evaluation criteria.',
 'mandatory', 'Risk Management Process', 10),
(UUID(), @iso27005, '27005-5',  'Risk Assessment',
 'Perform comprehensive risk assessment including risk identification, risk analysis (likelihood and consequence), and risk evaluation against defined criteria.',
 'mandatory', 'Risk Management Process', 20),
(UUID(), @iso27005, '27005-6',  'Risk Treatment',
 'Select and implement appropriate risk treatment options (modify, retain, avoid, or share) and prepare a risk treatment plan with residual risk determination.',
 'mandatory', 'Risk Management Process', 30),
(UUID(), @iso27005, '27005-7',  'Risk Acceptance',
 'Formally accept residual risks that cannot be further reduced or that fall within acceptable thresholds, with documented authorization from risk owners.',
 'mandatory', 'Risk Management Process', 40),
(UUID(), @iso27005, '27005-8',  'Risk Communication and Consultation',
 'Communicate risk information to internal and external stakeholders and consult with them to support decision-making throughout the risk management process.',
 'mandatory', 'Risk Management Process', 50),
(UUID(), @iso27005, '27005-9',  'Risk Monitoring and Review',
 'Monitor and review risks, controls, and the risk management process on an ongoing basis to detect changes in context that may impact risk decisions.',
 'mandatory', 'Risk Management Process', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO/IEC 27031:2011 — ICT Readiness for Business Continuity
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso27031, '27031-P',   'Policy and Management',
 'Establish ICT readiness for business continuity policy, assign management responsibility, and integrate ICT readiness into the overall business continuity management system.',
 'mandatory', 'ICT Readiness', 10),
(UUID(), @iso27031, '27031-PL',  'Planning for IRBC',
 'Conduct business impact analysis for ICT, perform risk assessments, define recovery time and recovery point objectives, and plan ICT redundancy and recovery strategies.',
 'mandatory', 'ICT Readiness', 20),
(UUID(), @iso27031, '27031-IM',  'Implementation',
 'Implement ICT continuity solutions including redundant infrastructure, data replication, backup systems, and documented recovery procedures.',
 'mandatory', 'ICT Readiness', 30),
(UUID(), @iso27031, '27031-CH',  'Checking (Testing and Exercises)',
 'Test and exercise ICT continuity capabilities including failover tests, recovery drills, and tabletop exercises; monitor ICT readiness indicators.',
 'mandatory', 'ICT Readiness', 40),
(UUID(), @iso27031, '27031-IM2', 'Improving',
 'Review and improve ICT readiness capabilities based on test results, lessons learned, and changes to the business environment or technology.',
 'mandatory', 'ICT Readiness', 50),
(UUID(), @iso27031, '27031-ICT', 'ICT Readiness for Business Continuity (IRBC)',
 'Maintain ICT services and infrastructure at levels consistent with business continuity requirements, covering people, facilities, technology, data, processes, and suppliers.',
 'mandatory', 'ICT Readiness', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO/IEC 27032:2023 — Cybersecurity Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso27032, '27032-GV',  'Cybersecurity Governance',
 'Establish governance structures, policies, roles, and responsibilities for managing cybersecurity in the Internet ecosystem.',
 'mandatory', 'Cybersecurity Sections', 10),
(UUID(), @iso27032, '27032-RM',  'Cybersecurity Risk Management',
 'Identify and assess cybersecurity risks from Internet-based threats and implement risk treatments appropriate to the organizational context.',
 'mandatory', 'Cybersecurity Sections', 20),
(UUID(), @iso27032, '27032-IS',  'Information Sharing',
 'Participate in cybersecurity information sharing with peers, CERTs, and government agencies to improve collective defense and threat awareness.',
 'recommended', 'Cybersecurity Sections', 30),
(UUID(), @iso27032, '27032-SM',  'Stakeholder Management',
 'Coordinate cybersecurity responsibilities and activities across the ecosystem of consumers, organizations, providers, and governments.',
 'recommended', 'Cybersecurity Sections', 40),
(UUID(), @iso27032, '27032-IR',  'Incident Response',
 'Prepare for and respond to cybersecurity incidents affecting Internet-connected services, coordinating with relevant stakeholders.',
 'mandatory', 'Cybersecurity Sections', 50),
(UUID(), @iso27032, '27032-AS',  'Application Security',
 'Apply security controls to web applications and services to protect against common Internet-based attacks.',
 'mandatory', 'Cybersecurity Sections', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO/IEC 27035:2023 — Incident Management Parts
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso27035, '27035-PP',  'Planning and Preparation',
 'Establish an incident management policy, create an incident response plan, form an incident response team, and provide awareness and training.',
 'mandatory', 'Incident Management', 10),
(UUID(), @iso27035, '27035-DR',  'Detection and Reporting',
 'Detect and report information security events using monitoring tools, alert mechanisms, and structured reporting channels.',
 'mandatory', 'Incident Management', 20),
(UUID(), @iso27035, '27035-AD',  'Assessment and Decision',
 'Assess reported events to determine whether they constitute incidents, classify severity, and decide on the appropriate response actions.',
 'mandatory', 'Incident Management', 30),
(UUID(), @iso27035, '27035-RP',  'Response',
 'Execute incident response activities including evidence collection, containment, eradication, recovery, and communication to stakeholders.',
 'mandatory', 'Incident Management', 40),
(UUID(), @iso27035, '27035-LL',  'Lessons Learned',
 'Conduct post-incident reviews to identify root causes, improve controls, and update the incident management plan based on findings.',
 'mandatory', 'Incident Management', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO/IEC 27036:2023 — Supplier Relationships Parts
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso27036, '27036-1',  'Part 1: Overview and Concepts',
 'Provides an overview of information security in supplier relationships, defining key concepts and risk factors in acquiring products and services from external parties.',
 'mandatory', 'Supplier Relationships', 10),
(UUID(), @iso27036, '27036-2',  'Part 2: Requirements',
 'Specifies fundamental information security requirements applicable to both acquirers and suppliers to address information security risks in supply chain relationships.',
 'mandatory', 'Supplier Relationships', 20),
(UUID(), @iso27036, '27036-3',  'Part 3: ICT Supply Chain Security',
 'Provides guidelines for the security of ICT products and services throughout the supply chain, covering risk assessment, supplier selection, and ongoing monitoring.',
 'mandatory', 'Supplier Relationships', 30),
(UUID(), @iso27036, '27036-4',  'Part 4: Cloud Service Security',
 'Provides guidelines for cloud service customers to assess security risks of cloud services and establish appropriate security controls in cloud provider agreements.',
 'mandatory', 'Supplier Relationships', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO/IEC 15408 — Common Criteria
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso15408, 'PP',    'Protection Profile (PP)',
 'An implementation-independent statement of security requirements for a category of TOEs that meet specific consumer needs.',
 'mandatory', 'CC Documents', 10),
(UUID(), @iso15408, 'ST',    'Security Target (ST)',
 'A set of security requirements and specifications to be used as the basis for evaluation of an identified TOE, including both functional and assurance requirements.',
 'mandatory', 'CC Documents', 20),
(UUID(), @iso15408, 'TOE',   'TOE Summary Specification',
 'A description of how the TOE meets each SFR, describing the security mechanisms implemented and how they satisfy the stated security objectives.',
 'mandatory', 'CC Documents', 30),
(UUID(), @iso15408, 'EAL1',  'EAL1 — Functionally Tested',
 'The TOE is informally tested; provides basic confidence that the TOE operates as claimed without developer involvement beyond documentation.',
 'optional', 'Assurance Levels', 40),
(UUID(), @iso15408, 'EAL2',  'EAL2 — Structurally Tested',
 'Requires design information and testing; suitable when developers or users require low to moderate assurance with limited access to developer source.',
 'optional', 'Assurance Levels', 50),
(UUID(), @iso15408, 'EAL3',  'EAL3 — Methodically Tested and Checked',
 'Requires rigorous development and test documentation; suitable when developers require security engineering at design stage without major development restructuring.',
 'optional', 'Assurance Levels', 60),
(UUID(), @iso15408, 'EAL4',  'EAL4 — Methodically Designed, Tested and Reviewed',
 'The highest level economically feasible for existing products; involves rigorous commercial development practices and independent vulnerability analysis.',
 'optional', 'Assurance Levels', 70),
(UUID(), @iso15408, 'EAL5',  'EAL5 — Semiformally Designed and Tested',
 'Requires specialist security engineering techniques; developers plan for EAL5 from the start; suitable for moderately high assurance needs.',
 'optional', 'Assurance Levels', 80),
(UUID(), @iso15408, 'EAL6',  'EAL6 — Semiformally Verified Design and Tested',
 'Requires security engineering techniques for high-risk environments; significant development cost and time investment with formal model and analysis.',
 'optional', 'Assurance Levels', 90),
(UUID(), @iso15408, 'EAL7',  'EAL7 — Formally Verified Design and Tested',
 'The highest CC assurance level; requires comprehensive formal analysis; applicable only to highest-risk situations such as military or government critical systems.',
 'optional', 'Assurance Levels', 100);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO 31000:2018 — Risk Management Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso31000, '31000-5',   'Scope, Context and Criteria',
 'Define the scope of risk management, establish external and internal context, and define risk criteria for evaluating risk significance.',
 'mandatory', 'Risk Management Process', 10),
(UUID(), @iso31000, '31000-6.1', 'Risk Identification',
 'Identify and describe risks by finding, recognizing, and describing risks that might help or prevent the organization achieving its objectives.',
 'mandatory', 'Risk Management Process', 20),
(UUID(), @iso31000, '31000-6.2', 'Risk Analysis',
 'Comprehend the nature, sources, and characteristics of risk including the level of risk, considering uncertainty, consequences, and likelihood.',
 'mandatory', 'Risk Management Process', 30),
(UUID(), @iso31000, '31000-6.3', 'Risk Evaluation',
 'Compare risk analysis results with risk criteria to determine whether risk requires treatment and to support treatment prioritization decisions.',
 'mandatory', 'Risk Management Process', 40),
(UUID(), @iso31000, '31000-7',   'Risk Treatment',
 'Select and implement risk treatment options to modify risk, including avoiding, reducing, sharing, or retaining risk, with a documented risk treatment plan.',
 'mandatory', 'Risk Management Process', 50),
(UUID(), @iso31000, '31000-8',   'Monitoring and Review',
 'Monitor risks, treatments, and the effectiveness of the risk management process on an ongoing basis; adapt to changes in context.',
 'mandatory', 'Risk Management Process', 60),
(UUID(), @iso31000, '31000-9',   'Recording and Reporting',
 'Record and report risk management activities and outcomes to support decision-making, accountability, and communication with stakeholders.',
 'mandatory', 'Risk Management Process', 70);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO 22313:2020 — BCMS Guidance (mirroring ISO 22301 structure)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso22313, '22313-4',   'Context of the Organization',
 'Understand the organization and its context, stakeholder needs, and define the scope of the business continuity management system.',
 'mandatory', 'BCMS Clauses', 10),
(UUID(), @iso22313, '22313-5',   'Leadership',
 'Demonstrate top management commitment to BCMS, establish a BC policy, assign roles and responsibilities, and ensure resources are provided.',
 'mandatory', 'BCMS Clauses', 20),
(UUID(), @iso22313, '22313-6',   'Planning',
 'Address risks and opportunities, set business continuity objectives, and plan actions to achieve them including consideration of critical activities.',
 'mandatory', 'BCMS Clauses', 30),
(UUID(), @iso22313, '22313-7',   'Support',
 'Provide resources, ensure competence, promote awareness, establish communication processes, and maintain documented information.',
 'mandatory', 'BCMS Clauses', 40),
(UUID(), @iso22313, '22313-8',   'Operation',
 'Conduct business impact analysis, risk assessment, develop BC strategies and plans, and establish warning and communication procedures.',
 'mandatory', 'BCMS Clauses', 50),
(UUID(), @iso22313, '22313-9',   'Performance Evaluation',
 'Monitor, measure, analyze, and evaluate BCMS performance; conduct internal audits and management reviews.',
 'mandatory', 'BCMS Clauses', 60),
(UUID(), @iso22313, '22313-10',  'Improvement',
 'Address nonconformities, take corrective action, and continually improve the suitability, adequacy, and effectiveness of the BCMS.',
 'mandatory', 'BCMS Clauses', 70);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO 22316:2017 — Organizational Resilience
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso22316, '22316-P',   'Principles of Organizational Resilience',
 'Establish shared vision and clarity of purpose, understand the organization''s context, and foster a resilience culture across the organization.',
 'mandatory', 'Resilience Framework', 10),
(UUID(), @iso22316, '22316-A',   'Attributes of Resilience',
 'Identify and develop the key attributes that contribute to resilience including leadership, organizational culture, change readiness, and anticipatory awareness.',
 'mandatory', 'Resilience Framework', 20),
(UUID(), @iso22316, '22316-AS',  'Assessment of Resilience',
 'Assess the organization''s current resilience maturity against the resilience attributes and benchmark against peers or industry standards.',
 'recommended', 'Resilience Framework', 30),
(UUID(), @iso22316, '22316-IM',  'Improvement of Resilience',
 'Develop and implement an improvement plan to strengthen resilience capabilities based on assessment findings and changes in operating environment.',
 'recommended', 'Resilience Framework', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO/IEC 20000-1:2018 — IT Service Management
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso20000, '20000-4',   'SMS Context',
 'Determine the internal and external context of the SMS, identify interested parties, and define the scope of services covered by the SMS.',
 'mandatory', 'SMS Clauses', 10),
(UUID(), @iso20000, '20000-5',   'Leadership',
 'Top management shall demonstrate leadership and commitment, establish a service management policy, and assign roles and responsibilities.',
 'mandatory', 'SMS Clauses', 20),
(UUID(), @iso20000, '20000-6',   'Planning',
 'Plan actions to address risks and opportunities, set service management objectives, and plan changes to the SMS.',
 'mandatory', 'SMS Clauses', 30),
(UUID(), @iso20000, '20000-7',   'Support',
 'Determine and provide necessary resources, ensure competence, manage awareness, communication, and documented information.',
 'mandatory', 'SMS Clauses', 40),
(UUID(), @iso20000, '20000-8.2', 'Service Delivery',
 'Plan, implement, control, and improve service delivery processes including service catalogue management, capacity, availability, and continuity.',
 'mandatory', 'SMS Clauses', 50),
(UUID(), @iso20000, '20000-8.3', 'Relationship and Agreement',
 'Manage business relationships, service level agreements, supplier contracts, and service agreements to meet customer requirements.',
 'mandatory', 'SMS Clauses', 60),
(UUID(), @iso20000, '20000-8.6', 'Resolution and Fulfillment',
 'Manage incidents, service requests, and problems to restore service and prevent recurrence; maintain a known-error database.',
 'mandatory', 'SMS Clauses', 70),
(UUID(), @iso20000, '20000-8.7', 'Control',
 'Control configuration, assets, changes, releases, and deployments to maintain service integrity and minimize disruption.',
 'mandatory', 'SMS Clauses', 80),
(UUID(), @iso20000, '20000-9',   'Performance Evaluation',
 'Monitor, measure, and evaluate SMS performance; conduct internal audits, manage service performance, and perform management reviews.',
 'mandatory', 'SMS Clauses', 90),
(UUID(), @iso20000, '20000-10',  'Improvement',
 'Manage nonconformity and corrective actions, and continually improve the SMS and services based on evaluation results.',
 'mandatory', 'SMS Clauses', 100);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO/IEC 38500:2015 — IT Governance (6 Principles)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso38500, '38500-P1', 'Principle 1: Responsibility',
 'Individuals and groups throughout the organization understand and accept their responsibilities in respect of both supply of, and demand for, IT.',
 'mandatory', 'IT Governance Principles', 10),
(UUID(), @iso38500, '38500-P2', 'Principle 2: Strategy',
 'The organization''s business strategy takes into account the current and future capabilities of IT; IT strategic plans satisfy the current and ongoing needs of the business strategy.',
 'mandatory', 'IT Governance Principles', 20),
(UUID(), @iso38500, '38500-P3', 'Principle 3: Acquisition',
 'IT acquisitions are made for valid reasons, on the basis of appropriate and ongoing analysis, with clear and transparent decision making.',
 'mandatory', 'IT Governance Principles', 30),
(UUID(), @iso38500, '38500-P4', 'Principle 4: Performance',
 'IT is fit for purpose in supporting the organization, providing services, levels of service, and service quality required to meet current and future business requirements.',
 'mandatory', 'IT Governance Principles', 40),
(UUID(), @iso38500, '38500-P5', 'Principle 5: Conformance',
 'IT complies with all mandatory legislation and regulations; policies and practices are clearly defined, implemented, and enforced.',
 'mandatory', 'IT Governance Principles', 50),
(UUID(), @iso38500, '38500-P6', 'Principle 6: Human Behavior',
 'IT policies, practices, and decisions demonstrate respect for human behavior, including the current and evolving needs of all the people in the process.',
 'mandatory', 'IT Governance Principles', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO 37301:2021 — Compliance Management
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso37301, '37301-4',  'Context of the Organization',
 'Understand the organization''s context including relevant obligations, interested parties, and define the scope of the compliance management system.',
 'mandatory', 'CMS Clauses', 10),
(UUID(), @iso37301, '37301-5',  'Leadership and Commitment',
 'Governing body and top management demonstrate active commitment to compliance, establish a compliance policy, and assign a compliance function.',
 'mandatory', 'CMS Clauses', 20),
(UUID(), @iso37301, '37301-6',  'Planning',
 'Identify compliance obligations and risks, set compliance objectives, and plan actions to address compliance risks and opportunities.',
 'mandatory', 'CMS Clauses', 30),
(UUID(), @iso37301, '37301-7',  'Support',
 'Provide resources for compliance, ensure competence of personnel, foster compliance culture, and manage compliance documentation.',
 'mandatory', 'CMS Clauses', 40),
(UUID(), @iso37301, '37301-8',  'Operation',
 'Implement compliance processes including due diligence, controls, reporting mechanisms, investigations, and compliance in procurement.',
 'mandatory', 'CMS Clauses', 50),
(UUID(), @iso37301, '37301-9',  'Performance Evaluation',
 'Monitor compliance obligations and performance through audits, management reviews, and compliance reporting.',
 'mandatory', 'CMS Clauses', 60),
(UUID(), @iso37301, '37301-10', 'Improvement',
 'Take corrective actions for nonconformities and continually improve the effectiveness of the compliance management system.',
 'mandatory', 'CMS Clauses', 70);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO 37001:2016 — Anti-Bribery Management
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso37001, '37001-4',  'Context of the Organization',
 'Analyze bribery risk context, identify interested parties, define scope of the ABMS, and understand the organization''s exposure to bribery.',
 'mandatory', 'ABMS Clauses', 10),
(UUID(), @iso37001, '37001-5',  'Leadership',
 'Top management and governing body demonstrate commitment to anti-bribery, establish an anti-bribery policy, and designate a compliance function.',
 'mandatory', 'ABMS Clauses', 20),
(UUID(), @iso37001, '37001-6',  'Planning',
 'Conduct a bribery risk assessment, set anti-bribery objectives, and plan controls proportionate to bribery risks.',
 'mandatory', 'ABMS Clauses', 30),
(UUID(), @iso37001, '37001-7',  'Support',
 'Provide adequate resources, ensure personnel competence in anti-bribery, promote awareness, and establish reporting channels.',
 'mandatory', 'ABMS Clauses', 40),
(UUID(), @iso37001, '37001-8',  'Operation',
 'Implement anti-bribery controls including due diligence on business associates, financial controls, non-financial controls, and gifts and hospitality procedures.',
 'mandatory', 'ABMS Clauses', 50),
(UUID(), @iso37001, '37001-9',  'Performance Evaluation',
 'Monitor anti-bribery controls, conduct internal audits, and perform management reviews of the ABMS effectiveness.',
 'mandatory', 'ABMS Clauses', 60),
(UUID(), @iso37001, '37001-10', 'Improvement',
 'Address nonconformities with corrective actions and continually improve the anti-bribery management system.',
 'mandatory', 'ABMS Clauses', 70),
(UUID(), @iso37001, '37001-A',  'Annex A: Additional Guidance on Controls',
 'Provides supplementary guidance on anti-bribery controls including board oversight, due diligence procedures, and sector-specific considerations.',
 'recommended', 'ABMS Clauses', 80);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO 13485:2016 — Medical Device QMS
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso13485, '13485-4',  'Quality Management System',
 'Establish, document, implement, maintain, and continually improve a QMS for medical devices, including risk management throughout product realization.',
 'mandatory', 'QMS Sections', 10),
(UUID(), @iso13485, '13485-5',  'Management Responsibility',
 'Top management commits to QMS development, establishes quality policy and objectives, ensures regulatory requirements are communicated, and reviews the QMS.',
 'mandatory', 'QMS Sections', 20),
(UUID(), @iso13485, '13485-6',  'Resource Management',
 'Determine and provide resources for QMS implementation and maintenance, ensure personnel competence, manage infrastructure, and maintain work environment.',
 'mandatory', 'QMS Sections', 30),
(UUID(), @iso13485, '13485-7',  'Product Realization',
 'Plan and control product realization processes including customer-related processes, design and development, purchasing, production, and service provision.',
 'mandatory', 'QMS Sections', 40),
(UUID(), @iso13485, '13485-8',  'Measurement, Analysis and Improvement',
 'Monitor and measure processes and product, analyze data, control nonconforming product, conduct corrective and preventive action, and issue MDR/vigilance reports.',
 'mandatory', 'QMS Sections', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO/SAE 21434:2021 — Automotive Cybersecurity Engineering
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iso21434, '21434-5',  'Cybersecurity Governance',
 'Establish and maintain a cybersecurity management system including policy, responsibilities, cybersecurity culture, and organizational cybersecurity audit.',
 'mandatory', 'Automotive Cybersecurity', 10),
(UUID(), @iso21434, '21434-8',  'Cybersecurity Risk Assessment',
 'Identify and assess cybersecurity risks for items and components throughout their lifecycle using threat analysis and risk assessment (TARA).',
 'mandatory', 'Automotive Cybersecurity', 20),
(UUID(), @iso21434, '21434-9',  'Concept Phase',
 'Define item definition, cybersecurity goals, cybersecurity concept, and cybersecurity requirements at the vehicle or system level.',
 'mandatory', 'Automotive Cybersecurity', 30),
(UUID(), @iso21434, '21434-10', 'Product Development',
 'Implement cybersecurity requirements in hardware and software design, including cybersecurity-informed design and integration testing.',
 'mandatory', 'Automotive Cybersecurity', 40),
(UUID(), @iso21434, '21434-12', 'Production',
 'Control cybersecurity risks in the production process including supplier management, production controls, and security of production environments.',
 'mandatory', 'Automotive Cybersecurity', 50),
(UUID(), @iso21434, '21434-13', 'Operations and Maintenance',
 'Manage cybersecurity incidents in the field, apply patches and updates, and maintain cybersecurity monitoring throughout operational life.',
 'mandatory', 'Automotive Cybersecurity', 60),
(UUID(), @iso21434, '21434-14', 'End of Life',
 'Manage cybersecurity aspects at end of vehicle life including decommissioning, data deletion, and disclosure of vulnerabilities.',
 'recommended', 'Automotive Cybersecurity', 70);


-- ═══════════════════════════════════════════════════════════════════════════════
-- IEC 62304:2006 — Medical Device Software Lifecycle
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iec62304, '62304-5.1', 'Software Development Planning',
 'Develop a software development plan defining development lifecycle model, deliverables, activities, roles, and standards used.',
 'mandatory', 'Software Lifecycle', 10),
(UUID(), @iec62304, '62304-5.2', 'Software Requirements Analysis',
 'Establish and document software requirements derived from system requirements, user needs, and risk management outputs.',
 'mandatory', 'Software Lifecycle', 20),
(UUID(), @iec62304, '62304-5.3', 'Software Architectural Design',
 'Design the software architecture to define software items, their interfaces, and how they implement the software requirements.',
 'mandatory', 'Software Lifecycle', 30),
(UUID(), @iec62304, '62304-5.4', 'Software Detailed Design',
 'Develop detailed design for each software unit specifying algorithms, data structures, and interfaces needed to implement the software item.',
 'mandatory', 'Software Lifecycle', 40),
(UUID(), @iec62304, '62304-5.5', 'Software Unit Implementation',
 'Implement software units according to detailed design and establish unit acceptance criteria based on risk classification.',
 'mandatory', 'Software Lifecycle', 50),
(UUID(), @iec62304, '62304-5.6', 'Software Integration and Testing',
 'Integrate software items and test integrated software to verify that software items work together and meet requirements.',
 'mandatory', 'Software Lifecycle', 60),
(UUID(), @iec62304, '62304-5.8', 'Software Release',
 'Document the software version being released, archive software items and build records, and prepare release documentation.',
 'mandatory', 'Software Lifecycle', 70),
(UUID(), @iec62304, '62304-6',   'Software Maintenance',
 'Plan, implement, document, and test modifications to released software; evaluate changes for risk and re-verify affected requirements.',
 'mandatory', 'Software Lifecycle', 80);


-- ═══════════════════════════════════════════════════════════════════════════════
-- IEC 62443 — Industrial Automation and Control Systems Security
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iec62443, '62443-2-1', 'Part 2-1: Security Management System for IACS',
 'Specifies requirements for establishing, operating, and maintaining a cybersecurity management system for industrial automation and control systems.',
 'mandatory', 'IACS Security Parts', 10),
(UUID(), @iec62443, '62443-2-3', 'Part 2-3: Patch Management in the IACS Environment',
 'Provides guidance on patch management for IACS including risk assessment of patches, testing, deployment, and verification.',
 'mandatory', 'IACS Security Parts', 20),
(UUID(), @iec62443, '62443-2-4', 'Part 2-4: Security Program Requirements for IACS Service Providers',
 'Specifies security capabilities that a service provider must maintain when providing integration or maintenance services for IACS.',
 'mandatory', 'IACS Security Parts', 30),
(UUID(), @iec62443, '62443-3-2', 'Part 3-2: Security Risk Assessment for System Design',
 'Provides a risk assessment methodology for designing IACS security zones and conduits and establishing target security levels.',
 'mandatory', 'IACS Security Parts', 40),
(UUID(), @iec62443, '62443-3-3', 'Part 3-3: System Security Requirements and Security Levels',
 'Defines system-level security requirements aligned with seven foundational requirements and four security levels for IACS.',
 'mandatory', 'IACS Security Parts', 50),
(UUID(), @iec62443, '62443-4-1', 'Part 4-1: Secure Product Development Lifecycle Requirements',
 'Specifies process requirements for secure development of products used in IACS environments including design, implementation, verification, and delivery.',
 'mandatory', 'IACS Security Parts', 60),
(UUID(), @iec62443, '62443-4-2', 'Part 4-2: Technical Security Requirements for IACS Components',
 'Defines technical security requirements for IACS components (software, hardware, and network) mapped to foundational requirements and security levels.',
 'mandatory', 'IACS Security Parts', 70);
