-- Migration 021: ESG, DevSecOps, identity, and emerging standards
-- Frameworks: GRI Standards, SASB, TCFD, CSRD, SLSA, EO 14028,
--             SBOM Standards (SPDX & CycloneDX), SAFECode, OSCAL,
--             DevSecOps Framework, FIDO2/WebAuthn, OAuth 2.0/OIDC,
--             SAML 2.0, SCIM 2.0, NFPA 1600

SET @gri       = 'b8c9d0e1-0021-0001-0001-000000000001';
SET @sasb      = 'b8c9d0e1-0021-0002-0001-000000000001';
SET @tcfd      = 'b8c9d0e1-0021-0003-0001-000000000001';
SET @csrd      = 'b8c9d0e1-0021-0004-0001-000000000001';
SET @slsa      = 'b8c9d0e1-0021-0005-0001-000000000001';
SET @eo14028   = 'b8c9d0e1-0021-0006-0001-000000000001';
SET @sbom      = 'b8c9d0e1-0021-0007-0001-000000000001';
SET @safecode  = 'b8c9d0e1-0021-0008-0001-000000000001';
SET @oscal     = 'b8c9d0e1-0021-0009-0001-000000000001';
SET @devsecops = 'b8c9d0e1-0021-0010-0001-000000000001';
SET @fido2     = 'b8c9d0e1-0021-0011-0001-000000000001';
SET @oauth2    = 'b8c9d0e1-0021-0012-0001-000000000001';
SET @saml2     = 'b8c9d0e1-0021-0013-0001-000000000001';
SET @scim2     = 'b8c9d0e1-0021-0014-0001-000000000001';
SET @nfpa1600  = 'b8c9d0e1-0021-0015-0001-000000000001';

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

INSERT IGNORE INTO regulatory_frameworks
  (id, code, name, description, version, issuing_body, is_global, effective_date)
VALUES
  (@gri, 'GRI Standards 2021',
   'GRI Universal Standards 2021 — Global Reporting Initiative',
   'The world''s most widely used sustainability reporting standards, enabling organizations to publicly report on their economic, environmental, and social impacts.',
   '2021', 'Global Reporting Initiative', TRUE, '2023-01-01'),

  (@sasb, 'SASB Standards',
   'SASB Industry-Specific Sustainability Accounting Standards',
   'Industry-specific sustainability accounting standards helping businesses identify, manage, and report on financially material sustainability topics for investors.',
   '2023 (IFRS/ISSB alignment)', 'SASB / IFRS Foundation', TRUE, '2018-11-01'),

  (@tcfd, 'TCFD',
   'Task Force on Climate-related Financial Disclosures (TCFD)',
   'Framework for voluntary climate-related financial disclosures across four pillars to help companies provide better information to investors and other stakeholders.',
   '2023 (Final)', 'Financial Stability Board', TRUE, '2017-06-29'),

  (@csrd, 'CSRD',
   'EU Corporate Sustainability Reporting Directive (CSRD)',
   'EU directive requiring large companies to report on sustainability topics using European Sustainability Reporting Standards (ESRS) covering environmental, social, and governance matters.',
   '2022/2464', 'European Parliament', FALSE, '2024-01-01'),

  (@slsa, 'SLSA',
   'Supply-chain Levels for Software Artifacts (SLSA)',
   'Security framework providing a checklist of standards and controls to prevent software tampering, improve artifact integrity, and secure the software supply chain.',
   'v1.0', 'OpenSSF', TRUE, '2023-04-01'),

  (@eo14028, 'EO 14028',
   'Executive Order 14028 — Improving the Nation''s Cybersecurity',
   'US Executive Order directing federal agencies and contractors to improve cybersecurity through SBOM requirements, zero trust architecture, and secure software development practices.',
   '2021', 'White House / NIST / CISA', FALSE, '2021-05-12'),

  (@sbom, 'SBOM Standards',
   'Software Bill of Materials Standards (SPDX and CycloneDX)',
   'Open standards for creating, sharing, and consuming software bills of materials to enable vulnerability management, license compliance, and supply chain transparency.',
   'SPDX 2.3 / CycloneDX 1.5', 'Linux Foundation / OWASP', TRUE, '2021-01-01'),

  (@safecode, 'SAFECode',
   'SAFECode Fundamental Practices for Secure Software Development',
   'Industry-led guidance on fundamental secure software development practices for organizations to implement throughout the software development lifecycle.',
   '3rd Edition', 'SAFECode', TRUE, '2018-03-01'),

  (@oscal, 'OSCAL',
   'Open Security Controls Assessment Language (OSCAL)',
   'NIST-developed machine-readable language for expressing security controls, assessments, plans, and results to automate and standardize compliance activities.',
   '1.1.2', 'NIST', TRUE, '2023-06-01'),

  (@devsecops, 'DevSecOps Framework',
   'DevSecOps Framework — Integrating Security into DevOps',
   'A set of practices for integrating security tools and processes throughout the CI/CD pipeline to identify and remediate security issues earlier in development.',
   '2023', 'NIST / DoD / Industry', TRUE, '2022-01-01'),

  (@fido2, 'FIDO2 / WebAuthn',
   'FIDO2 — Web Authentication Standard',
   'Open authentication standard enabling password-less authentication using public key cryptography, hardware security keys, and platform authenticators.',
   'Level 3 (2023)', 'FIDO Alliance / W3C', TRUE, '2019-03-04'),

  (@oauth2, 'OAuth 2.0 / OIDC',
   'OAuth 2.0 Authorization Framework and OpenID Connect 1.0',
   'Industry-standard protocols for authorization (OAuth 2.0) and identity (OIDC) enabling secure delegated access and federated identity for web and mobile applications.',
   'RFC 6749 / OIDC Core 1.0', 'IETF / OpenID Foundation', TRUE, '2012-10-01'),

  (@saml2, 'SAML 2.0',
   'Security Assertion Markup Language (SAML) 2.0',
   'XML-based open standard for exchanging authentication and authorization data between identity providers and service providers for enterprise single sign-on.',
   '2.0', 'OASIS', TRUE, '2005-03-15'),

  (@scim2, 'SCIM 2.0',
   'System for Cross-domain Identity Management (SCIM) 2.0',
   'Open standard for automating the exchange of user identity information between identity domains and IT systems, simplifying provisioning and deprovisioning.',
   '2.0 (RFC 7642-7644)', 'IETF', TRUE, '2015-09-01'),

  (@nfpa1600, 'NFPA 1600',
   'NFPA 1600 — Standard on Continuity, Emergency, and Crisis Management',
   'Comprehensive standard establishing a common set of criteria for all-hazards emergency management, business continuity, and crisis management programs.',
   '2022', 'NFPA', TRUE, '1995-01-01');


-- ═══════════════════════════════════════════════════════════════════════════════
-- GRI Standards 2021 — Universal Standards
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @gri, 'GRI-1',   'GRI 1: Foundation 2021',
 'Sets out the purpose of GRI Standards, the requirements for using them, how to claim use of the Standards, and the role of different standards in the framework.',
 'mandatory', 'GRI Universal Standards', 10),
(UUID(), @gri, 'GRI-2',   'GRI 2: General Disclosures 2021',
 'Covers organizational profile, governance, strategy, policies, practices, and stakeholder engagement information applicable to all organizations.',
 'mandatory', 'GRI Universal Standards', 20),
(UUID(), @gri, 'GRI-3',   'GRI 3: Material Topics 2021',
 'Provides requirements and guidance for determining material topics and for how an organization reports on its management of these topics.',
 'mandatory', 'GRI Universal Standards', 30),
(UUID(), @gri, 'GRI-ENV', 'Environmental Topic Standards (GRI 300 Series)',
 'Covers environmental disclosures including materials, energy, water, biodiversity, emissions, effluents and waste, and environmental compliance.',
 'mandatory', 'GRI Topic Standards', 40),
(UUID(), @gri, 'GRI-SOC', 'Social Topic Standards (GRI 400 Series)',
 'Covers social disclosures including employment, labor/management relations, health and safety, training, diversity, human rights, local communities, and customer privacy.',
 'mandatory', 'GRI Topic Standards', 50),
(UUID(), @gri, 'GRI-ECO', 'Economic Topic Standards (GRI 200 Series)',
 'Covers economic disclosures including economic performance, market presence, indirect economic impacts, procurement practices, and anti-corruption.',
 'mandatory', 'GRI Topic Standards', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SASB — 10 Sector Groups
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @sasb, 'SASB-FIN',  'Financials Sector',
 'Industry-specific sustainability topics for asset management, commercial banking, insurance, and financial services companies.',
 'mandatory', 'SASB Sectors', 10),
(UUID(), @sasb, 'SASB-HC',   'Health Care Sector',
 'Sustainability topics for biotechnology, drug retail, health care delivery, managed care, medical equipment, and pharmaceutical industries.',
 'mandatory', 'SASB Sectors', 20),
(UUID(), @sasb, 'SASB-TC',   'Technology and Communications Sector',
 'Sustainability topics for electronic manufacturing, hardware, internet media, semiconductors, software, and telecommunications companies.',
 'mandatory', 'SASB Sectors', 30),
(UUID(), @sasb, 'SASB-NR',   'Non-Renewable Resources Sector',
 'Sustainability topics for coal, construction materials, iron and steel, metals and mining, oil and gas exploration, and refining companies.',
 'mandatory', 'SASB Sectors', 40),
(UUID(), @sasb, 'SASB-TR',   'Transportation Sector',
 'Sustainability topics for air freight, airlines, auto parts, automobiles, cruise lines, marine, rail, and road transportation companies.',
 'mandatory', 'SASB Sectors', 50),
(UUID(), @sasb, 'SASB-SV',   'Services Sector',
 'Sustainability topics for advertising, casinos, education, hotels, leisure facilities, media, restaurants, and professional services companies.',
 'mandatory', 'SASB Sectors', 60),
(UUID(), @sasb, 'SASB-RT',   'Resource Transformation Sector',
 'Sustainability topics for aerospace, chemicals, containers, electrical equipment, industrial machinery, and waste management companies.',
 'mandatory', 'SASB Sectors', 70),
(UUID(), @sasb, 'SASB-CN',   'Consumer Goods Sector',
 'Sustainability topics for apparel, building products, e-commerce, food, household products, and toys and sporting goods industries.',
 'mandatory', 'SASB Sectors', 80),
(UUID(), @sasb, 'SASB-IF',   'Infrastructure Sector',
 'Sustainability topics for electric utilities, engineering and construction, gas utilities, home builders, REITs, waste management, and water utilities.',
 'mandatory', 'SASB Sectors', 90),
(UUID(), @sasb, 'SASB-RR',   'Renewable Resources and Alternative Energy Sector',
 'Sustainability topics for biofuels, forestry, fuel cells, pulp and paper, solar, and wind energy companies.',
 'mandatory', 'SASB Sectors', 100);


-- ═══════════════════════════════════════════════════════════════════════════════
-- TCFD — 4 Pillars
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @tcfd, 'TCFD-GV',  'Governance',
 'Describe the organization''s governance around climate-related risks and opportunities including board oversight and management''s role.',
 'mandatory', 'TCFD Pillars', 10),
(UUID(), @tcfd, 'TCFD-ST',  'Strategy',
 'Describe the actual and potential impacts of climate-related risks and opportunities on the organization''s businesses, strategy, and financial planning using climate scenarios.',
 'mandatory', 'TCFD Pillars', 20),
(UUID(), @tcfd, 'TCFD-RM',  'Risk Management',
 'Describe the processes used to identify, assess, and manage climate-related risks and how those processes are integrated into overall risk management.',
 'mandatory', 'TCFD Pillars', 30),
(UUID(), @tcfd, 'TCFD-MT',  'Metrics and Targets',
 'Disclose metrics and targets used to assess and manage relevant climate-related risks and opportunities, including Scope 1, 2, and 3 GHG emissions.',
 'mandatory', 'TCFD Pillars', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- CSRD / ESRS — Reporting Areas
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @csrd, 'ESRS-E1',  'ESRS E1: Climate Change',
 'Disclose transition plans, physical and transition climate risks, and GHG emission reduction targets and progress.',
 'mandatory', 'ESRS Standards', 10),
(UUID(), @csrd, 'ESRS-E2',  'ESRS E2: Pollution',
 'Disclose pollution-related impacts, risks, and opportunities including air, water, and soil pollution and substances of concern.',
 'mandatory', 'ESRS Standards', 20),
(UUID(), @csrd, 'ESRS-E3',  'ESRS E3: Water and Marine Resources',
 'Disclose water and marine resource use, water stress impacts, and ocean and marine ecosystem impacts.',
 'mandatory', 'ESRS Standards', 30),
(UUID(), @csrd, 'ESRS-E4',  'ESRS E4: Biodiversity and Ecosystems',
 'Disclose impacts and dependencies on biodiversity and ecosystems across operations and value chain.',
 'mandatory', 'ESRS Standards', 40),
(UUID(), @csrd, 'ESRS-E5',  'ESRS E5: Resource Use and Circular Economy',
 'Disclose resource inflows and outflows, waste generation, and circular economy strategy and targets.',
 'mandatory', 'ESRS Standards', 50),
(UUID(), @csrd, 'ESRS-S1',  'ESRS S1: Own Workforce',
 'Disclose working conditions, equal treatment, wages, health and safety, and social dialogue with own employees and workers.',
 'mandatory', 'ESRS Standards', 60),
(UUID(), @csrd, 'ESRS-S2',  'ESRS S2: Workers in the Value Chain',
 'Disclose impacts on and engagement with workers in the supply chain beyond direct employees.',
 'mandatory', 'ESRS Standards', 70),
(UUID(), @csrd, 'ESRS-S3',  'ESRS S3: Affected Communities',
 'Disclose impacts on local communities affected by the organization''s operations and engagement activities.',
 'mandatory', 'ESRS Standards', 80),
(UUID(), @csrd, 'ESRS-S4',  'ESRS S4: Consumers and End-Users',
 'Disclose impacts related to consumer and end-user safety, privacy, access, and fair treatment.',
 'mandatory', 'ESRS Standards', 90),
(UUID(), @csrd, 'ESRS-G1',  'ESRS G1: Business Conduct',
 'Disclose corporate culture, anti-corruption policies, political engagement, payment practices, and whistleblower protections.',
 'mandatory', 'ESRS Standards', 100);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SLSA — 4 Levels and Requirement Groups
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @slsa, 'SLSA-L1',  'SLSA Level 1: Documentation of the Build Process',
 'The build process is scripted/automated and provenance is available; no tamper protection but provides a starting point for build integrity.',
 'mandatory', 'SLSA Levels', 10),
(UUID(), @slsa, 'SLSA-L2',  'SLSA Level 2: Tamper Protection of the Build Service',
 'The build service is hosted and generates signed provenance; provides protection against source and build platform tampering.',
 'mandatory', 'SLSA Levels', 20),
(UUID(), @slsa, 'SLSA-L3',  'SLSA Level 3: Hardened Builds',
 'The build platform meets additional security requirements and provenance is non-forgeable; provides strong protection against build tampering.',
 'mandatory', 'SLSA Levels', 30),
(UUID(), @slsa, 'SLSA-SRC', 'Source Requirements',
 'Source integrity requirements covering version control, source retention, and two-person review to ensure source code authenticity.',
 'mandatory', 'SLSA Requirements', 40),
(UUID(), @slsa, 'SLSA-BLD', 'Build Requirements',
 'Build process requirements ensuring builds are scripted, run on managed platforms, and produce provenance documenting how the artifact was built.',
 'mandatory', 'SLSA Requirements', 50),
(UUID(), @slsa, 'SLSA-PRV', 'Provenance Requirements',
 'Requirements for build provenance completeness, authenticity, and non-forgeability to enable downstream consumers to verify artifact integrity.',
 'mandatory', 'SLSA Requirements', 60),
(UUID(), @slsa, 'SLSA-COM', 'Common Requirements',
 'Security requirements applicable to all SLSA levels covering security best practices for the build environment and supply chain.',
 'mandatory', 'SLSA Requirements', 70);


-- ═══════════════════════════════════════════════════════════════════════════════
-- EO 14028 — Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @eo14028, 'EO-SBOM',  'SBOM Requirements',
 'Software vendors selling to the federal government must provide SBOMs to enable identification of vulnerabilities in software components used in federal systems.',
 'mandatory', 'EO 14028 Sections', 10),
(UUID(), @eo14028, 'EO-ZTA',   'Zero Trust Architecture',
 'Federal agencies must develop plans to implement Zero Trust Architecture consistent with NIST SP 800-207 and migrate to ZTA within defined timelines.',
 'mandatory', 'EO 14028 Sections', 20),
(UUID(), @eo14028, 'EO-IR',    'Incident Response Improvements',
 'Federal agencies and contractors must improve detection of cybersecurity incidents and share cyber threat information with CISA and FBI.',
 'mandatory', 'EO 14028 Sections', 30),
(UUID(), @eo14028, 'EO-STD',   'Security Standards',
 'NIST must publish guidelines for critical software security standards; federal agencies must only use software meeting those standards.',
 'mandatory', 'EO 14028 Sections', 40),
(UUID(), @eo14028, 'EO-IOT',   'IoT Security',
 'NIST must define IoT cybersecurity criteria for consumer-grade IoT devices; FTC and NIST coordinate labeling to inform consumers.',
 'recommended', 'EO 14028 Sections', 50),
(UUID(), @eo14028, 'EO-LABEL', 'Consumer Labeling',
 'Pilot program for consumer labeling of software and IoT devices based on security criteria to promote security-conscious purchasing decisions.',
 'recommended', 'EO 14028 Sections', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SBOM Standards — SPDX and CycloneDX
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @sbom, 'SPDX-DC',  'SPDX: Document Creation Information',
 'SPDX document metadata including SPDX version, data license, document namespace, creator information, and creation timestamp.',
 'mandatory', 'SPDX Elements', 10),
(UUID(), @sbom, 'SPDX-PK',  'SPDX: Package Information',
 'Describes software packages with name, version, download location, checksum, supplier, originator, and license information.',
 'mandatory', 'SPDX Elements', 20),
(UUID(), @sbom, 'SPDX-FI',  'SPDX: File Information',
 'Per-file metadata including file name, checksum, license information, copyright text, and file types.',
 'optional', 'SPDX Elements', 30),
(UUID(), @sbom, 'SPDX-SN',  'SPDX: Snippet Information',
 'Information about portions of files that have different licensing from the file as a whole.',
 'optional', 'SPDX Elements', 40),
(UUID(), @sbom, 'SPDX-LI',  'SPDX: License Information',
 'Custom license text for licenses not in the SPDX License List, with license identifier and extracted text.',
 'optional', 'SPDX Elements', 50),
(UUID(), @sbom, 'CDX-META', 'CycloneDX: Metadata',
 'BOM-level metadata including timestamp, tools used, component type, and the component being described.',
 'mandatory', 'CycloneDX Elements', 60),
(UUID(), @sbom, 'CDX-COMP', 'CycloneDX: Components',
 'Describes components with type, BOM reference, supplier, name, version, description, hashes, licenses, and PURL.',
 'mandatory', 'CycloneDX Elements', 70),
(UUID(), @sbom, 'CDX-DEP',  'CycloneDX: Dependencies',
 'Defines dependency relationships between components to enable complete supply chain analysis and vulnerability impact assessment.',
 'mandatory', 'CycloneDX Elements', 80),
(UUID(), @sbom, 'CDX-SVC',  'CycloneDX: Services',
 'Describes external services and APIs that the software calls or depends on, including authentication requirements.',
 'recommended', 'CycloneDX Elements', 90),
(UUID(), @sbom, 'CDX-VUL',  'CycloneDX: Vulnerabilities',
 'Documents known vulnerabilities in components with CVE references, ratings, and remediation state.',
 'recommended', 'CycloneDX Elements', 100);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SAFECode Fundamental Practices
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @safecode, 'SC-SD',  'Secure Design Principles',
 'Apply secure design principles throughout the SDLC including threat modeling, attack surface analysis, and security architecture reviews.',
 'mandatory', 'SAFECode Practices', 10),
(UUID(), @safecode, 'SC-ST',  'Security Testing',
 'Conduct security testing including SAST, DAST, SCA, fuzzing, and penetration testing proportionate to the risk level of the software.',
 'mandatory', 'SAFECode Practices', 20),
(UUID(), @safecode, 'SC-TP',  'Third-Party Component Management',
 'Manage third-party and open source components through inventory tracking, license compliance, and vulnerability monitoring.',
 'mandatory', 'SAFECode Practices', 30),
(UUID(), @safecode, 'SC-VR',  'Vulnerability Response',
 'Establish a vulnerability response process including coordinated disclosure, severity assessment, patch development, and customer notification.',
 'mandatory', 'SAFECode Practices', 40),
(UUID(), @safecode, 'SC-GV',  'Security Governance',
 'Establish security governance for software development including executive accountability, security policies, training, and metrics.',
 'mandatory', 'SAFECode Practices', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- OSCAL — Layers/Documents
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @oscal, 'OSCAL-CAT',  'Catalog',
 'Machine-readable representation of a security controls catalog (e.g., NIST SP 800-53) enabling automated import and processing of control definitions.',
 'mandatory', 'OSCAL Layers', 10),
(UUID(), @oscal, 'OSCAL-PRF',  'Profile',
 'Represents a baseline or overlay by selecting, tailoring, and extending controls from one or more catalogs to create an organization-specific control set.',
 'mandatory', 'OSCAL Layers', 20),
(UUID(), @oscal, 'OSCAL-COM',  'Component Definition',
 'Documents how a specific component (hardware, software, service) implements security controls, enabling reuse of component-level compliance evidence.',
 'mandatory', 'OSCAL Layers', 30),
(UUID(), @oscal, 'OSCAL-SSP',  'System Security Plan',
 'Machine-readable SSP documenting system boundaries, system implementation, and how controls are satisfied by the system and its environment.',
 'mandatory', 'OSCAL Layers', 40),
(UUID(), @oscal, 'OSCAL-AP',   'Assessment Plan',
 'Documents the scope, objectives, methods, and schedule for a security assessment in a machine-readable format linked to the SSP.',
 'mandatory', 'OSCAL Layers', 50),
(UUID(), @oscal, 'OSCAL-AR',   'Assessment Results',
 'Machine-readable record of assessment findings, observations, and findings linked to the control objectives and methods in the assessment plan.',
 'mandatory', 'OSCAL Layers', 60),
(UUID(), @oscal, 'OSCAL-POAM', 'Plan of Action and Milestones (POA&M)',
 'Tracks identified risks, weaknesses, and deficiencies along with remediation plans, milestones, and responsible parties.',
 'mandatory', 'OSCAL Layers', 70);


-- ═══════════════════════════════════════════════════════════════════════════════
-- DevSecOps Framework — Phases
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @devsecops, 'DSO-PLAN',  'Plan Security',
 'Integrate security requirements into sprint planning, conduct threat modeling for new features, and define security acceptance criteria.',
 'mandatory', 'DevSecOps Phases', 10),
(UUID(), @devsecops, 'DSO-CODE',  'Secure Code',
 'Apply secure coding standards, use pre-commit hooks for secrets scanning, and enforce code reviews with security focus.',
 'mandatory', 'DevSecOps Phases', 20),
(UUID(), @devsecops, 'DSO-TEST',  'SAST / DAST / SCA',
 'Run Static Application Security Testing (SAST), Dynamic Application Security Testing (DAST), and Software Composition Analysis (SCA) in the CI pipeline.',
 'mandatory', 'DevSecOps Phases', 30),
(UUID(), @devsecops, 'DSO-CON',   'Container and Infrastructure Security',
 'Scan container images for vulnerabilities, enforce image signing, use Infrastructure as Code security scanning, and enforce least-privilege for workloads.',
 'mandatory', 'DevSecOps Phases', 40),
(UUID(), @devsecops, 'DSO-DEP',   'Secure Deploy',
 'Enforce deployment gates based on security test results, use signed artifacts, and apply SLSA requirements for release integrity.',
 'mandatory', 'DevSecOps Phases', 50),
(UUID(), @devsecops, 'DSO-MON',   'Monitor',
 'Implement runtime security monitoring, RASP, application-level logging, and behavioral anomaly detection in production.',
 'mandatory', 'DevSecOps Phases', 60),
(UUID(), @devsecops, 'DSO-RSP',   'Respond',
 'Automate incident response playbooks for security events detected in production, feed findings back into the vulnerability management process.',
 'mandatory', 'DevSecOps Phases', 70);


-- ═══════════════════════════════════════════════════════════════════════════════
-- FIDO2 / WebAuthn — Components and Attestation
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @fido2, 'FIDO-AUTH',  'Authenticator (Authenticator API)',
 'Hardware or software component that performs user verification (PIN, biometric, presence) and generates cryptographic assertions; may be platform-bound or roaming.',
 'mandatory', 'FIDO2 Components', 10),
(UUID(), @fido2, 'FIDO-CLI',   'Client (Browser / Platform API)',
 'Client-side software mediating between the authenticator and relying party, implementing the WebAuthn API and CTAP protocol handling.',
 'mandatory', 'FIDO2 Components', 20),
(UUID(), @fido2, 'FIDO-SRV',   'Relying Party (Server)',
 'Server-side component implementing WebAuthn, managing credential registration, challenge generation, and assertion verification.',
 'mandatory', 'FIDO2 Components', 30),
(UUID(), @fido2, 'FIDO-ATT',   'Attestation Types',
 'Mechanisms for verifying the provenance and trust level of an authenticator during registration: packed, TPM, FIDO U2F, anonymization CA, and none.',
 'mandatory', 'FIDO2 Components', 40),
(UUID(), @fido2, 'FIDO-UV',    'User Verification Levels',
 'Levels of user verification: Required (UV=true), Preferred, and Discouraged; combined with user presence check to balance security and usability.',
 'mandatory', 'FIDO2 Components', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- OAuth 2.0 / OpenID Connect — Flows and Components
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @oauth2, 'OAUTH-AC',    'Authorization Code Flow',
 'The most secure OAuth 2.0 flow for server-side applications; uses an authorization code exchanged for tokens through the back channel.',
 'mandatory', 'OAuth 2.0 Flows', 10),
(UUID(), @oauth2, 'OAUTH-PKCE',  'Authorization Code with PKCE',
 'Extension of the Authorization Code flow for public clients (SPAs, mobile apps) using a cryptographic code verifier/challenge to prevent interception attacks.',
 'mandatory', 'OAuth 2.0 Flows', 20),
(UUID(), @oauth2, 'OAUTH-CC',    'Client Credentials Flow',
 'Machine-to-machine flow where the client authenticates with its own credentials to obtain an access token for API access without user involvement.',
 'mandatory', 'OAuth 2.0 Flows', 30),
(UUID(), @oauth2, 'OAUTH-DF',    'Device Authorization Flow',
 'Flow for devices with limited input capabilities; user authorizes on a secondary device using a user code displayed on the constrained device.',
 'recommended', 'OAuth 2.0 Flows', 40),
(UUID(), @oauth2, 'OIDC-IDT',    'OIDC: ID Token',
 'JWT containing identity claims about the authenticated user; issued by the identity provider alongside the access token in the token response.',
 'mandatory', 'OIDC Components', 50),
(UUID(), @oauth2, 'OIDC-UI',     'OIDC: UserInfo Endpoint',
 'Protected resource returning claims about the authenticated end-user; called by the relying party using the access token.',
 'mandatory', 'OIDC Components', 60),
(UUID(), @oauth2, 'OIDC-DISC',   'OIDC: Discovery',
 'Mechanism for relying parties to automatically discover identity provider configuration via the .well-known/openid-configuration endpoint.',
 'recommended', 'OIDC Components', 70);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SAML 2.0 — Profiles and Bindings
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @saml2, 'SAML-WBSSO',  'Profile: Web Browser SSO',
 'The primary SAML profile enabling single sign-on for browser-based applications; supports SP-initiated and IdP-initiated flows.',
 'mandatory', 'SAML 2.0 Profiles', 10),
(UUID(), @saml2, 'SAML-ECP',    'Profile: Enhanced Client or Proxy (ECP)',
 'SAML profile for non-browser clients such as thick applications that can perform SOAP-based SAML exchanges.',
 'optional', 'SAML 2.0 Profiles', 20),
(UUID(), @saml2, 'SAML-SLO',    'Profile: Single Logout',
 'SAML profile enabling coordinated logout across all sessions established through a single sign-on event.',
 'recommended', 'SAML 2.0 Profiles', 30),
(UUID(), @saml2, 'SAML-REDIR',  'Binding: HTTP-Redirect',
 'SAML binding transmitting messages through browser URL query string parameters; used for authentication requests in Web SSO.',
 'mandatory', 'SAML 2.0 Bindings', 40),
(UUID(), @saml2, 'SAML-POST',   'Binding: HTTP-POST',
 'SAML binding transmitting messages through HTML form POST; used for SAML responses containing assertions.',
 'mandatory', 'SAML 2.0 Bindings', 50),
(UUID(), @saml2, 'SAML-ART',    'Binding: HTTP-Artifact',
 'SAML binding using a short artifact reference to retrieve the actual message via a back-channel SOAP call, preventing assertion exposure in browser.',
 'optional', 'SAML 2.0 Bindings', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SCIM 2.0 — Resources and Operations
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @scim2, 'SCIM-USER',   'User Resource',
 'Core SCIM schema for representing user identity attributes including username, name, email, phone, address, group membership, and enterprise attributes.',
 'mandatory', 'SCIM Resources', 10),
(UUID(), @scim2, 'SCIM-GRP',    'Group Resource',
 'SCIM schema for representing groups of users with display name and member list for provisioning role and group assignments.',
 'mandatory', 'SCIM Resources', 20),
(UUID(), @scim2, 'SCIM-CR',     'Create Operation',
 'HTTP POST to provision new users or groups in the target system with defined attributes from the identity source.',
 'mandatory', 'SCIM Operations', 30),
(UUID(), @scim2, 'SCIM-RD',     'Read Operation',
 'HTTP GET to retrieve user or group attributes from the target system for synchronization and audit purposes.',
 'mandatory', 'SCIM Operations', 40),
(UUID(), @scim2, 'SCIM-RP',     'Replace Operation',
 'HTTP PUT to replace all attributes of a user or group resource with a full updated representation.',
 'mandatory', 'SCIM Operations', 50),
(UUID(), @scim2, 'SCIM-DL',     'Delete Operation',
 'HTTP DELETE to deprovision user or group resources in the target system when access should be revoked.',
 'mandatory', 'SCIM Operations', 60),
(UUID(), @scim2, 'SCIM-SR',     'Search Operation',
 'HTTP GET or POST to /Users or /Groups with filter parameters to search for resources matching specified criteria.',
 'mandatory', 'SCIM Operations', 70),
(UUID(), @scim2, 'SCIM-BLK',    'Bulk Operation',
 'HTTP POST to /Bulk endpoint to perform multiple create, modify, and delete operations in a single request for efficiency.',
 'recommended', 'SCIM Operations', 80);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NFPA 1600 — Program Elements
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @nfpa1600, 'NFPA-PM',  'Program Management',
 'Establish the continuity, emergency, and crisis management program including policy, governance, resource management, and program coordination.',
 'mandatory', 'NFPA 1600 Elements', 10),
(UUID(), @nfpa1600, 'nfpa-pv',  'Prevention',
 'Implement measures to eliminate or reduce the likelihood of hazardous events, including regulatory compliance and risk reduction activities.',
 'mandatory', 'NFPA 1600 Elements', 20),
(UUID(), @nfpa1600, 'NFPA-MI',  'Mitigation',
 'Implement sustained actions to reduce long-term risk by modifying characteristics of hazards or reducing vulnerable elements to minimize losses.',
 'mandatory', 'NFPA 1600 Elements', 30),
(UUID(), @nfpa1600, 'NFPA-PR',  'Preparedness',
 'Develop response plans, train personnel, conduct exercises, and establish resource management systems in advance of an incident.',
 'mandatory', 'NFPA 1600 Elements', 40),
(UUID(), @nfpa1600, 'NFPA-RS',  'Response',
 'Implement immediate actions following an incident to save lives, protect property, and stabilize the situation.',
 'mandatory', 'NFPA 1600 Elements', 50),
(UUID(), @nfpa1600, 'NFPA-RC',  'Recovery',
 'Implement short-term and long-term activities to restore essential functions, repair damage, and return operations to normal or improved state.',
 'mandatory', 'NFPA 1600 Elements', 60);
