-- Migration 016: Governance, audit, and assurance frameworks
-- Frameworks: COSO IC, COSO ERM, COBIT 5, FAIR, OCTAVE, SABSA, TOGAF Security,
--             ITIL v4, ISF SoGP, SOC 1, SOC 3, SSAE 18, ISAE 3000, ISAE 3402,
--             Trust Services Criteria, IIA Standards, PCAOB, Cyber Essentials, Cyber Essentials Plus

SET @cosoic    = 'c3d4e5f6-0016-0001-0001-000000000001';
SET @cosoerm   = 'c3d4e5f6-0016-0002-0001-000000000001';
SET @cobit5    = 'c3d4e5f6-0016-0003-0001-000000000001';
SET @fair      = 'c3d4e5f6-0016-0004-0001-000000000001';
SET @octave    = 'c3d4e5f6-0016-0005-0001-000000000001';
SET @sabsa     = 'c3d4e5f6-0016-0006-0001-000000000001';
SET @togafsec  = 'c3d4e5f6-0016-0007-0001-000000000001';
SET @itilv4    = 'c3d4e5f6-0016-0008-0001-000000000001';
SET @isfsogp   = 'c3d4e5f6-0016-0009-0001-000000000001';
SET @soc1      = 'c3d4e5f6-0016-0010-0001-000000000001';
SET @soc3      = 'c3d4e5f6-0016-0011-0001-000000000001';
SET @ssae18    = 'c3d4e5f6-0016-0012-0001-000000000001';
SET @isae3000  = 'c3d4e5f6-0016-0013-0001-000000000001';
SET @isae3402  = 'c3d4e5f6-0016-0014-0001-000000000001';
SET @tsc       = 'c3d4e5f6-0016-0015-0001-000000000001';
SET @iia       = 'c3d4e5f6-0016-0016-0001-000000000001';
SET @pcaob     = 'c3d4e5f6-0016-0017-0001-000000000001';
SET @cyberuk   = 'c3d4e5f6-0016-0018-0001-000000000001';
SET @cyberukp  = 'c3d4e5f6-0016-0019-0001-000000000001';

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

INSERT IGNORE INTO regulatory_frameworks
  (id, code, name, description, version, issuing_body, is_global, effective_date)
VALUES
  (@cosoic, 'COSO IC',
   'COSO Internal Control — Integrated Framework (2013)',
   'Provides a comprehensive framework for designing, implementing, and evaluating internal controls using five interrelated components.',
   '2013', 'COSO', TRUE, '2013-05-14'),

  (@cosoerm, 'COSO ERM',
   'COSO Enterprise Risk Management Framework (2017)',
   'Integrates enterprise risk management into strategy setting and performance management through five interrelated components.',
   '2017', 'COSO', TRUE, '2017-09-01'),

  (@cobit5, 'COBIT 5',
   'COBIT 5 — A Business Framework for the Governance and Management of Enterprise IT',
   'Provides an IT governance and management framework covering five domains with 37 processes for effective enterprise IT governance.',
   '5.0', 'ISACA', TRUE, '2012-04-01'),

  (@fair, 'FAIR',
   'Factor Analysis of Information Risk (FAIR) Framework',
   'A quantitative risk analysis model providing a taxonomy of risk factors and a method for measuring and managing information security risk in financial terms.',
   '2020', 'FAIR Institute', TRUE, '2005-01-01'),

  (@octave, 'OCTAVE',
   'OCTAVE — Operationally Critical Threat, Asset, and Vulnerability Evaluation',
   'A risk-based strategic assessment and planning technique for security, developed at Carnegie Mellon University for self-directed risk assessment.',
   'Allegro', 'CERT/CC (CMU)', TRUE, '2003-01-01'),

  (@sabsa, 'SABSA',
   'SABSA — Sherwood Applied Business Security Architecture',
   'A framework and methodology for enterprise security architecture and service management providing a six-layer architecture model aligned with business requirements.',
   '2009', 'SABSA Institute', TRUE, '1995-01-01'),

  (@togafsec, 'TOGAF Security',
   'TOGAF Security Architecture',
   'Security architecture component of The Open Group Architecture Framework, addressing security across all TOGAF architecture domains.',
   'TOGAF 9.2', 'The Open Group', TRUE, '2018-04-01'),

  (@itilv4, 'ITIL v4',
   'ITIL 4 — IT Infrastructure Library',
   'A framework of best practices for IT service management providing guidance on service value system, service value chain, and management practices.',
   'v4', 'AXELOS', TRUE, '2019-02-01'),

  (@isfsogp, 'ISF SoGP',
   'ISF Standard of Good Practice for Information Security',
   'A comprehensive framework covering information security good practice organized into topical areas for enterprises and practitioners.',
   '2022', 'Information Security Forum', TRUE, '2022-01-01'),

  (@soc1, 'SOC 1',
   'SOC 1 — Service Organization Control Report (SSAE 18)',
   'An audit report on controls at a service organization relevant to user entities'' internal control over financial reporting.',
   'SSAE 18', 'AICPA', FALSE, '2017-05-01'),

  (@soc3, 'SOC 3',
   'SOC 3 — General Use Report on Service Organization Controls',
   'A public-use report based on SOC 2 criteria, suitable for general distribution to demonstrate security and availability commitments.',
   '2022', 'AICPA', FALSE, '2017-01-01'),

  (@ssae18, 'SSAE 18',
   'SSAE 18 — Statements on Standards for Attestation Engagements No. 18',
   'The AICPA standard for attestation engagements performed by CPAs, replacing SSAE 16 and covering system and organization control examinations.',
   '2017', 'AICPA', FALSE, '2017-05-01'),

  (@isae3000, 'ISAE 3000',
   'ISAE 3000 — International Standard on Assurance Engagements (Revised)',
   'International standard for assurance engagements other than audits or reviews of historical financial information, issued by the IAASB.',
   '2013 Revised', 'IAASB/IFAC', TRUE, '2015-01-01'),

  (@isae3402, 'ISAE 3402',
   'ISAE 3402 — Assurance Reports on Controls at a Service Organization',
   'International standard for service organization control reports covering the description, design (Type I), and operating effectiveness (Type II) of controls.',
   '2011', 'IAASB/IFAC', TRUE, '2011-06-15'),

  (@tsc, 'TSC',
   'AICPA Trust Services Criteria (TSC)',
   'Criteria used to evaluate controls relevant to security, availability, processing integrity, confidentiality, and privacy in SOC 2 examinations.',
   '2017 (Updated 2022)', 'AICPA', FALSE, '2018-03-01'),

  (@iia, 'IIA Standards',
   'IIA International Professional Practices Framework (IPPF) Standards',
   'Mandatory guidance for the internal audit profession covering attribute and performance standards for planning, executing, and communicating internal audit work.',
   '2017 (2024 Update)', 'Institute of Internal Auditors', TRUE, '2017-01-01'),

  (@pcaob, 'PCAOB Standards',
   'PCAOB Auditing Standards',
   'Standards established by the Public Company Accounting Oversight Board governing audits of public company financial statements and internal controls.',
   '2023', 'PCAOB', FALSE, '2004-01-01'),

  (@cyberuk, 'Cyber Essentials',
   'Cyber Essentials (UK)',
   'UK government-backed certification scheme helping organizations guard against common cyber threats through five fundamental security controls.',
   '2023', 'NCSC UK / IASME', FALSE, '2014-06-01'),

  (@cyberukp, 'Cyber Essentials Plus',
   'Cyber Essentials Plus (UK)',
   'Enhanced version of Cyber Essentials that includes independent technical verification of the five security controls through hands-on testing.',
   '2023', 'NCSC UK / IASME', FALSE, '2014-06-01');


-- ═══════════════════════════════════════════════════════════════════════════════
-- COSO Internal Control Framework (2013) — 5 Components
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @cosoic, 'CE',   'Control Environment',
 'The set of standards, processes, and structures that provide the basis for carrying out internal control across the organization, including integrity, ethical values, and competence.',
 'mandatory', 'COSO Components', 10),
(UUID(), @cosoic, 'RA',   'Risk Assessment',
 'The process for identifying and assessing risks to the achievement of organizational objectives, including specifying objectives clearly to enable risk identification.',
 'mandatory', 'COSO Components', 20),
(UUID(), @cosoic, 'CA',   'Control Activities',
 'The actions established through policies and procedures that help ensure management directives to mitigate risks are carried out.',
 'mandatory', 'COSO Components', 30),
(UUID(), @cosoic, 'IC',   'Information and Communication',
 'The generation and use of relevant, quality information to support the functioning of other internal control components, both internally and externally.',
 'mandatory', 'COSO Components', 40),
(UUID(), @cosoic, 'MA',   'Monitoring Activities',
 'Ongoing evaluations and separate evaluations used to ascertain whether each component of internal control is present and functioning.',
 'mandatory', 'COSO Components', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- COSO ERM (2017) — 5 Components
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @cosoerm, 'GC',   'Governance and Culture',
 'Board oversight, operating structures, ethical culture, and commitment to core values establish the tone for ERM and provide oversight of the risk management function.',
 'mandatory', 'ERM Components', 10),
(UUID(), @cosoerm, 'SS',   'Strategy and Objective-Setting',
 'ERM is integrated with strategy and objective setting, including analysis of business context, risk appetite definition, and alignment of objectives with risk appetite.',
 'mandatory', 'ERM Components', 20),
(UUID(), @cosoerm, 'PE',   'Performance',
 'Risks are identified and assessed for impact on strategy and business objectives, prioritized by severity, and risk responses are selected and implemented.',
 'mandatory', 'ERM Components', 30),
(UUID(), @cosoerm, 'RR',   'Review and Revision',
 'Performance is reviewed to determine how well ERM is functioning; revisions are made based on changes in business context and risk exposure.',
 'mandatory', 'ERM Components', 40),
(UUID(), @cosoerm, 'ICR',  'Information, Communication, and Reporting',
 'ERM information is leveraged from various sources; risk information is communicated to all levels of the organization to support decision-making.',
 'mandatory', 'ERM Components', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- COBIT 5 — 5 Principles + Domains
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @cobit5, 'EDM',  'EDM — Evaluate, Direct and Monitor',
 'Governance domain covering stakeholder needs evaluation, setting direction for IT resources, and monitoring IT performance and compliance (5 processes: EDM01–EDM05).',
 'mandatory', 'COBIT Domains', 10),
(UUID(), @cobit5, 'APO',  'APO — Align, Plan and Organise',
 'Management domain covering strategy alignment, enterprise architecture, risk management, resource management, and stakeholder management (13 processes: APO01–APO13).',
 'mandatory', 'COBIT Domains', 20),
(UUID(), @cobit5, 'BAI',  'BAI — Build, Acquire and Implement',
 'Management domain covering solutions delivery, change management, asset management, and knowledge management (10 processes: BAI01–BAI10).',
 'mandatory', 'COBIT Domains', 30),
(UUID(), @cobit5, 'DSS',  'DSS — Deliver, Service and Support',
 'Management domain covering service delivery, incident and problem management, business process controls, and security services (6 processes: DSS01–DSS06).',
 'mandatory', 'COBIT Domains', 40),
(UUID(), @cobit5, 'MEA',  'MEA — Monitor, Evaluate and Assess',
 'Management domain covering performance and conformance monitoring, system of internal controls, and compliance with external requirements (3 processes: MEA01–MEA03).',
 'mandatory', 'COBIT Domains', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- FAIR Risk Framework — Ontology
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @fair, 'FAIR-R',    'Risk',
 'The probable frequency and probable magnitude of future loss, quantified in financial terms using the FAIR ontology and analytical methodology.',
 'mandatory', 'FAIR Ontology', 10),
(UUID(), @fair, 'FAIR-T',    'Threat',
 'Anything (e.g., an adversary, nature, or internal failure) that can act against an asset in a way that could result in harm.',
 'mandatory', 'FAIR Ontology', 20),
(UUID(), @fair, 'FAIR-V',    'Vulnerability',
 'The probability that an asset will be unable to resist the actions of a threat agent, expressed as the ratio of threat capability to resistance strength.',
 'mandatory', 'FAIR Ontology', 30),
(UUID(), @fair, 'FAIR-L',    'Loss',
 'The undesirable consequences of a threat event, quantified across six loss forms: productivity, response, replacement, competitive advantage, fines/judgments, and reputation.',
 'mandatory', 'FAIR Ontology', 40),
(UUID(), @fair, 'FAIR-LEF',  'Loss Event Frequency (LEF)',
 'The probable frequency within a given time frame that a threat agent will inflict harm upon an asset, combining contact frequency and probability of action.',
 'mandatory', 'FAIR Ontology', 50),
(UUID(), @fair, 'FAIR-SLEF', 'Secondary Loss Event Frequency (SLEF)',
 'The probable frequency that secondary stakeholders will react to a primary loss event in a way that results in additional loss to the organization.',
 'mandatory', 'FAIR Ontology', 60),
(UUID(), @fair, 'FAIR-TC',   'Threat Capability (TCap)',
 'The probable level of force that a threat agent is capable of applying against an asset, measured relative to the strength of the asset''s resistance.',
 'mandatory', 'FAIR Ontology', 70),
(UUID(), @fair, 'FAIR-TR',   'Threat Resistance (TR)',
 'The strength of a control relative to a baseline level of force; expressed as the difficulty a threat agent faces in successfully acting against an asset.',
 'mandatory', 'FAIR Ontology', 80),
(UUID(), @fair, 'FAIR-C',    'Controls',
 'The safeguards or countermeasures that affect the frequency or magnitude of loss, categorized as preventive or responsive controls.',
 'mandatory', 'FAIR Ontology', 90);


-- ═══════════════════════════════════════════════════════════════════════════════
-- OCTAVE — 3 Phases
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @octave, 'OCT-1',  'Phase 1: Build Asset-Based Threat Profiles',
 'Identify critical assets, define security requirements for each critical asset, and identify threats to each critical asset from all threat sources.',
 'mandatory', 'OCTAVE Phases', 10),
(UUID(), @octave, 'OCT-2',  'Phase 2: Identify Infrastructure Vulnerabilities',
 'Identify key network components and systems, evaluate selected components for vulnerabilities and weaknesses that expose critical assets to threats.',
 'mandatory', 'OCTAVE Phases', 20),
(UUID(), @octave, 'OCT-3',  'Phase 3: Develop Security Strategy and Plans',
 'Evaluate risks to critical assets, define protection strategy and risk mitigation plans, and develop an action plan for security improvements.',
 'mandatory', 'OCTAVE Phases', 30);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SABSA — 6 Architecture Layers
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @sabsa, 'SABSA-1',  'Contextual (Business) Layer',
 'Defines business context for security: assets, motivations, processes, locations, time, and people from the perspective of the business owner.',
 'mandatory', 'SABSA Layers', 10),
(UUID(), @sabsa, 'SABSA-2',  'Conceptual (Architect) Layer',
 'Translates business requirements into conceptual security policies: risk model, trust model, security domains, and security services.',
 'mandatory', 'SABSA Layers', 20),
(UUID(), @sabsa, 'SABSA-3',  'Logical (Designer) Layer',
 'Defines logical security services, entity schema, security policies, and the information security model with process flows and rules.',
 'mandatory', 'SABSA Layers', 30),
(UUID(), @sabsa, 'SABSA-4',  'Physical (Builder) Layer',
 'Specifies the physical mechanisms, platforms, and standards to implement logical security services, including specific technologies and protocols.',
 'mandatory', 'SABSA Layers', 40),
(UUID(), @sabsa, 'SABSA-5',  'Component (Tradesman) Layer',
 'Defines specific products, tools, and component configurations that instantiate the physical security architecture in the operational environment.',
 'mandatory', 'SABSA Layers', 50),
(UUID(), @sabsa, 'SABSA-6',  'Operational (Facilities Manager) Layer',
 'Addresses ongoing operational support, service management, and assurance activities needed to maintain the security architecture in production.',
 'mandatory', 'SABSA Layers', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- TOGAF Security Architecture — Phases
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @togafsec, 'TOGAF-A',  'Security Architecture Vision (Phase A)',
 'Define the security scope and vision, identify security stakeholders, develop security architecture principles, and align with enterprise architecture vision.',
 'mandatory', 'TOGAF Security Phases', 10),
(UUID(), @togafsec, 'TOGAF-B',  'Business Security Architecture (Phase B)',
 'Define business security requirements, governance structures, compliance requirements, and business process security controls.',
 'mandatory', 'TOGAF Security Phases', 20),
(UUID(), @togafsec, 'TOGAF-C',  'Information Security Architecture (Phase C)',
 'Define data classification, information lifecycle security controls, privacy requirements, and data sovereignty considerations.',
 'mandatory', 'TOGAF Security Phases', 30),
(UUID(), @togafsec, 'TOGAF-D',  'Application Security Architecture (Phase D)',
 'Define application security patterns, secure SDLC requirements, API security, and authentication and authorization frameworks for applications.',
 'mandatory', 'TOGAF Security Phases', 40),
(UUID(), @togafsec, 'TOGAF-E',  'Technology Security Architecture (Phase E)',
 'Define technology security controls, infrastructure hardening standards, network security architecture, and cryptographic standards.',
 'mandatory', 'TOGAF Security Phases', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ITIL v4 — Service Value Chain + Dimensions
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @itilv4, 'SVC-EN',  'Engage',
 'Provide understanding of stakeholder needs, transparency, and continual engagement with all stakeholders to ensure co-creation of value.',
 'mandatory', 'Service Value Chain', 10),
(UUID(), @itilv4, 'SVC-PL',  'Plan',
 'Ensure a shared understanding of the vision, status, and improvement direction across the organization and its ecosystem.',
 'mandatory', 'Service Value Chain', 20),
(UUID(), @itilv4, 'SVC-IM',  'Improve',
 'Ensure continual improvement of products, services, and practices across all value chain activities and the four dimensions of service management.',
 'mandatory', 'Service Value Chain', 30),
(UUID(), @itilv4, 'SVC-DT',  'Design and Transition',
 'Ensure that products and services continually meet stakeholder expectations for quality, costs, and time-to-market.',
 'mandatory', 'Service Value Chain', 40),
(UUID(), @itilv4, 'SVC-OB',  'Obtain/Build',
 'Ensure that service components are available when and where they are needed and that they meet agreed specifications.',
 'mandatory', 'Service Value Chain', 50),
(UUID(), @itilv4, 'SVC-DS',  'Deliver and Support',
 'Ensure that services are delivered and supported according to agreed specifications and stakeholders'' expectations.',
 'mandatory', 'Service Value Chain', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISF Standard of Good Practice — Topic Areas
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @isfsogp, 'SM',   'Security Management',
 'Covers information security strategy, governance, risk management, legal and regulatory compliance, and security organization.',
 'mandatory', 'SoGP Areas', 10),
(UUID(), @isfsogp, 'CBA',  'Critical Business Applications',
 'Covers application security controls for critical business applications including access control, data validation, and application-specific risk management.',
 'mandatory', 'SoGP Areas', 20),
(UUID(), @isfsogp, 'CI',   'Computer Installations',
 'Covers physical and environmental security, system administration, operations management, and server security for computer installations.',
 'mandatory', 'SoGP Areas', 30),
(UUID(), @isfsogp, 'NW',   'Networks',
 'Covers network management, network security controls, remote access, wireless networking, and management of network infrastructure.',
 'mandatory', 'SoGP Areas', 40),
(UUID(), @isfsogp, 'EU',   'End-User Environments',
 'Covers security of end-user devices, mobile computing, teleworking, and user responsibilities for protecting information in distributed environments.',
 'mandatory', 'SoGP Areas', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SOC 1 (SSAE 18) — Report Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @soc1, 'SOC1-1',  'Service Auditor''s Report',
 'Independent auditor''s report containing the scope, opinion (Type I or Type II), and any qualifications or emphasis-of-matter paragraphs.',
 'mandatory', 'SOC 1 Sections', 10),
(UUID(), @soc1, 'SOC1-2',  'Management''s Assertion',
 'Management''s written assertion that the system description is fairly presented and that controls are suitably designed and operating effectively.',
 'mandatory', 'SOC 1 Sections', 20),
(UUID(), @soc1, 'SOC1-3',  'System Description',
 'Description of the service organization''s system boundaries, infrastructure, software, processes, data, and people relevant to ICFR.',
 'mandatory', 'SOC 1 Sections', 30),
(UUID(), @soc1, 'SOC1-4',  'Control Objectives and Controls',
 'Statement of control objectives and the controls designed to achieve those objectives, with test procedures and results for Type II reports.',
 'mandatory', 'SOC 1 Sections', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SOC 3 — Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @soc3, 'SOC3-1',  'Management''s Report',
 'Management''s assertion that controls were effective throughout the period and that the system met the applicable Trust Services Criteria.',
 'mandatory', 'SOC 3 Sections', 10),
(UUID(), @soc3, 'SOC3-2',  'Auditor''s Report',
 'Independent CPA''s opinion on whether the service organization maintained effective controls over the examination period.',
 'mandatory', 'SOC 3 Sections', 20),
(UUID(), @soc3, 'SOC3-3',  'System Description',
 'General description of the system, principal service commitments, and the nature of the services provided (less detail than SOC 2).',
 'mandatory', 'SOC 3 Sections', 30);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SSAE 18 — Attestation Standards Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @ssae18, 'ATC-105',  'AT-C Section 105: Concepts Common to All Attestation Engagements',
 'Defines overarching concepts for attestation engagements including the practitioner''s responsibilities, subject matter, suitable criteria, and materiality.',
 'mandatory', 'SSAE 18 Sections', 10),
(UUID(), @ssae18, 'ATC-205',  'AT-C Section 205: Examination Engagements',
 'Provides requirements for examination engagements where the practitioner obtains sufficient evidence to express an opinion on subject matter.',
 'mandatory', 'SSAE 18 Sections', 20),
(UUID(), @ssae18, 'ATC-305',  'AT-C Section 305: Prospective Financial Information',
 'Provides requirements for attestation engagements on prospective financial information such as financial forecasts and projections.',
 'mandatory', 'SSAE 18 Sections', 30),
(UUID(), @ssae18, 'ATC-315',  'AT-C Section 315: Compliance Attestation',
 'Provides requirements for agreed-upon procedures engagements and examinations on an entity''s compliance with specified requirements.',
 'mandatory', 'SSAE 18 Sections', 40),
(UUID(), @ssae18, 'ATC-320',  'AT-C Section 320: Reporting on an Examination of Controls at a Service Organization (SOC 1)',
 'Provides requirements specific to SOC 1 examinations on controls at a service organization relevant to user entities'' ICFR.',
 'mandatory', 'SSAE 18 Sections', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISAE 3000 — Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @isae3000, 'ISAE3-OBJ',  'Objective and Scope',
 'Defines the objective of assurance engagements — to obtain sufficient appropriate evidence to express a conclusion about subject matter against suitable criteria.',
 'mandatory', 'ISAE 3000 Sections', 10),
(UUID(), @isae3000, 'ISAE3-REQ',  'Requirements',
 'Core requirements for assurance engagements including acceptance, planning, evidence gathering, using the work of others, and forming conclusions.',
 'mandatory', 'ISAE 3000 Sections', 20),
(UUID(), @isae3000, 'ISAE3-APP',  'Application and Explanatory Guidance',
 'Guidance on applying requirements including materiality, sampling, evaluating evidence, and preparing assurance reports.',
 'recommended', 'ISAE 3000 Sections', 30);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISAE 3402 — Service Organization Reports
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @isae3402, 'ISAE3402-SC',  'Scope and Control Objectives',
 'Define the scope of the assurance engagement, specify control objectives and the controls designed to achieve those objectives.',
 'mandatory', 'ISAE 3402 Sections', 10),
(UUID(), @isae3402, 'ISAE3402-CO',  'Control Objectives and Description',
 'Present the description of the service organization''s system and the control objectives that have been specified by management.',
 'mandatory', 'ISAE 3402 Sections', 20),
(UUID(), @isae3402, 'ISAE3402-AR',  'Auditor''s Report (Type I and Type II)',
 'Independent assurance report expressing opinion on fair presentation of system description and design adequacy (Type I) or also operating effectiveness (Type II).',
 'mandatory', 'ISAE 3402 Sections', 30);


-- ═══════════════════════════════════════════════════════════════════════════════
-- Trust Services Criteria (TSC) — 5 Criteria Categories
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @tsc, 'CC',  'Common Criteria (CC) — Security',
 'Controls addressing logical and physical access, change management, risk mitigation, and system operations applicable to all SOC 2 examinations.',
 'mandatory', 'Trust Services Categories', 10),
(UUID(), @tsc, 'A',   'Availability (A)',
 'Controls ensuring that the system is available for operation and use as committed or agreed, including performance monitoring and incident management.',
 'mandatory', 'Trust Services Categories', 20),
(UUID(), @tsc, 'PI',  'Processing Integrity (PI)',
 'Controls ensuring that system processing is complete, valid, accurate, timely, and authorized to meet entity objectives.',
 'mandatory', 'Trust Services Categories', 30),
(UUID(), @tsc, 'C',   'Confidentiality (C)',
 'Controls ensuring that information designated as confidential is protected as committed or agreed during collection, processing, and disposal.',
 'mandatory', 'Trust Services Categories', 40),
(UUID(), @tsc, 'P',   'Privacy (P)',
 'Controls ensuring that personal information is collected, used, retained, disclosed, and disposed of in conformity with the entity''s privacy notice.',
 'mandatory', 'Trust Services Categories', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- IIA Internal Audit Standards — Domains
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @iia, 'IIA-1000',  'Attribute Standards (1000 Series)',
 'Standards covering the purpose, authority, and responsibility (1000), independence and objectivity (1100), proficiency and due professional care (1200), and quality assurance (1300) of internal audit.',
 'mandatory', 'IIA Standards', 10),
(UUID(), @iia, 'IIA-2000',  'Performance Standards (2000 Series)',
 'Standards covering managing internal audit activity (2000), nature of work (2100), engagement planning (2200), performing engagements (2300), communicating results (2400), and monitoring (2500/2600).',
 'mandatory', 'IIA Standards', 20);


-- ═══════════════════════════════════════════════════════════════════════════════
-- PCAOB Standards — Key Auditing Standards
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @pcaob, 'AS2201',  'AS 2201: Auditing Internal Control Over Financial Reporting (ICFR)',
 'Establishes requirements for integrated audits of ICFR and financial statements, including risk-based approach to selecting controls to test.',
 'mandatory', 'PCAOB Standards', 10),
(UUID(), @pcaob, 'AS2301',  'AS 2301: Audit Evidence',
 'Establishes requirements for obtaining sufficient appropriate audit evidence to support the auditor''s opinion, including evidence evaluation procedures.',
 'mandatory', 'PCAOB Standards', 20),
(UUID(), @pcaob, 'AS2401',  'AS 2401: Consideration of Fraud in a Financial Statement Audit',
 'Requires auditors to identify and assess risks of material misstatement due to fraud and design audit procedures responsive to those risks.',
 'mandatory', 'PCAOB Standards', 30),
(UUID(), @pcaob, 'AS2601',  'AS 2601: Consideration of a Service Auditor''s Report',
 'Addresses the auditor''s consideration of a service auditor''s report when auditing financial statements of an entity that uses a service organization.',
 'mandatory', 'PCAOB Standards', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- Cyber Essentials (UK) — 5 Controls
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @cyberuk, 'CE-1',  'Firewalls',
 'Use firewalls to secure internet connections, ensuring all devices with an internet connection have a properly configured boundary firewall.',
 'mandatory', 'Cyber Essentials Controls', 10),
(UUID(), @cyberuk, 'CE-2',  'Secure Configuration',
 'Configure computers and network devices with security in mind, changing default passwords, disabling unnecessary accounts, and removing unused software.',
 'mandatory', 'Cyber Essentials Controls', 20),
(UUID(), @cyberuk, 'CE-3',  'User Access Control',
 'Control access to data and services through least privilege, limiting standard user accounts, and using admin accounts only when required.',
 'mandatory', 'Cyber Essentials Controls', 30),
(UUID(), @cyberuk, 'CE-4',  'Malware Protection',
 'Protect against malware using anti-malware software, application controls to prevent execution of malicious software, or both.',
 'mandatory', 'Cyber Essentials Controls', 40),
(UUID(), @cyberuk, 'CE-5',  'Patch Management',
 'Keep software up to date by applying security patches within 14 days of release and removing software no longer supported by the vendor.',
 'mandatory', 'Cyber Essentials Controls', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- Cyber Essentials Plus — 5 Controls with Technical Verification
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @cyberukp, 'CEP-1',  'Firewalls (Verified)',
 'Independent technical verification that firewall configurations are correct through vulnerability scanning and configuration review by an accredited assessor.',
 'mandatory', 'Cyber Essentials Plus Controls', 10),
(UUID(), @cyberukp, 'CEP-2',  'Secure Configuration (Verified)',
 'Technical verification that devices are hardened to an appropriate baseline through hands-on testing of a sample of representative devices.',
 'mandatory', 'Cyber Essentials Plus Controls', 20),
(UUID(), @cyberukp, 'CEP-3',  'User Access Control (Verified)',
 'Technical verification of least-privilege implementation by attempting privilege escalation and validating account provisioning controls on sample systems.',
 'mandatory', 'Cyber Essentials Plus Controls', 30),
(UUID(), @cyberukp, 'CEP-4',  'Malware Protection (Verified)',
 'Technical verification of malware protection effectiveness through simulated malware execution tests to confirm that protection controls detect and block threats.',
 'mandatory', 'Cyber Essentials Plus Controls', 40),
(UUID(), @cyberukp, 'CEP-5',  'Patch Management (Verified)',
 'Technical verification that operating systems and software are fully patched through vulnerability scanning and manual inspection of patch levels.',
 'mandatory', 'Cyber Essentials Plus Controls', 50);
