-- Migration 019: Security testing, offensive/defensive frameworks, and security architecture
-- Frameworks: OWASP Top 10, ASVS, SAMM, MASVS, MITRE ATT&CK, MITRE D3FEND,
--             BSIMM, CIS Benchmarks, CSA STAR, CISA ZT Maturity Model,
--             NSA ZT Framework, NIST SP 800-207, SCF, UCF

SET @owaspt10  = 'f6a7b8c9-0019-0001-0001-000000000001';
SET @owaspasvs = 'f6a7b8c9-0019-0002-0001-000000000001';
SET @owaspsamm = 'f6a7b8c9-0019-0003-0001-000000000001';
SET @owaspmasvs= 'f6a7b8c9-0019-0004-0001-000000000001';
SET @mitreatk  = 'f6a7b8c9-0019-0005-0001-000000000001';
SET @mitred3f  = 'f6a7b8c9-0019-0006-0001-000000000001';
SET @bsimm     = 'f6a7b8c9-0019-0007-0001-000000000001';
SET @cisbench  = 'f6a7b8c9-0019-0008-0001-000000000001';
SET @csastar   = 'f6a7b8c9-0019-0009-0001-000000000001';
SET @cisazt    = 'f6a7b8c9-0019-0010-0001-000000000001';
SET @nsazt     = 'f6a7b8c9-0019-0011-0001-000000000001';
SET @n800207   = 'f6a7b8c9-0019-0012-0001-000000000001';
SET @scf       = 'f6a7b8c9-0019-0013-0001-000000000001';
SET @ucf       = 'f6a7b8c9-0019-0014-0001-000000000001';

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

INSERT IGNORE INTO regulatory_frameworks
  (id, code, name, description, version, issuing_body, is_global, effective_date)
VALUES
  (@owaspt10, 'OWASP Top 10',
   'OWASP Top 10 Web Application Security Risks (2021)',
   'The most critical web application security risks compiled by OWASP, representing a broad consensus about the most critical security risks to web applications.',
   '2021', 'OWASP Foundation', TRUE, '2021-09-24'),

  (@owaspasvs, 'OWASP ASVS',
   'OWASP Application Security Verification Standard v4.0',
   'Defines security requirements for designing, developing, and testing secure web applications and web services across three levels of verification depth.',
   'v4.0.3', 'OWASP Foundation', TRUE, '2021-10-01'),

  (@owaspsamm, 'OWASP SAMM',
   'OWASP Software Assurance Maturity Model v2',
   'A prescriptive model for software assurance enabling organizations to assess and improve their secure software development practices across five business functions.',
   'v2.0', 'OWASP Foundation', TRUE, '2020-01-01'),

  (@owaspmasvs, 'OWASP MASVS',
   'OWASP Mobile Application Security Verification Standard v2',
   'A standard for mobile application security establishing security requirements for iOS and Android applications across eight security control categories.',
   'v2.1.0', 'OWASP Foundation', TRUE, '2023-05-01'),

  (@mitreatk, 'MITRE ATT&CK',
   'MITRE ATT&CK Enterprise Framework',
   'A globally accessible knowledge base of adversary tactics and techniques based on real-world observations, used for threat modeling and detection development.',
   'v15.0', 'MITRE Corporation', TRUE, '2018-01-01'),

  (@mitred3f, 'MITRE D3FEND',
   'MITRE D3FEND — Defensive Cyber Countermeasures Knowledge Graph',
   'A knowledge graph of cybersecurity countermeasure techniques aligned to ATT&CK offensive techniques to guide defensive capability development.',
   '1.0', 'MITRE Corporation / NSA', TRUE, '2021-06-22'),

  (@bsimm, 'BSIMM',
   'Building Security In Maturity Model (BSIMM)',
   'A measuring tool for software security initiatives based on observations across hundreds of software security programs, organized into four domains and twelve practices.',
   'BSIMM14', 'Synopsys', TRUE, '2009-01-01'),

  (@cisbench, 'CIS Benchmarks',
   'CIS Benchmarks — Configuration Hardening Guidelines',
   'Prescriptive configuration recommendations for hundreds of technologies to safeguard systems against cyber threats, developed through community consensus.',
   '2023', 'Center for Internet Security', TRUE, '2001-01-01'),

  (@csastar, 'CSA STAR',
   'CSA Security Trust Assurance and Risk (STAR) Program',
   'Cloud security assurance program built on the CSA Cloud Controls Matrix (CCM), providing three levels of assurance for cloud service providers.',
   'v2023', 'Cloud Security Alliance', TRUE, '2013-09-01'),

  (@cisazt, 'CISA ZT Maturity Model',
   'CISA Zero Trust Maturity Model v2.0',
   'Provides guidance on the journey toward zero trust architecture across five pillars with three cross-cutting capabilities and four maturity stages.',
   'v2.0', 'CISA', FALSE, '2023-04-01'),

  (@nsazt, 'NSA Zero Trust Framework',
   'NSA Embracing a Zero Trust Security Model',
   'NSA guidance on implementing zero trust principles organized across seven pillars of the NSA zero trust framework for DoD and national security systems.',
   '2021', 'National Security Agency', TRUE, '2021-02-01'),

  (@n800207, 'NIST SP 800-207',
   'NIST SP 800-207 — Zero Trust Architecture',
   'Provides abstract definition and conceptual model of zero trust architecture with deployment scenarios and use cases for federal agencies.',
   '2020', 'NIST', TRUE, '2020-08-10'),

  (@scf, 'SCF',
   'Secure Controls Framework (SCF)',
   'A comprehensive, vendor-neutral, meta-framework of cybersecurity and privacy controls covering all 31 SCF domains to harmonize multiple regulatory requirements.',
   '2023.4', 'SCF Council', TRUE, '2019-01-01'),

  (@ucf, 'UCF',
   'Unified Compliance Framework (UCF)',
   'A harmonization approach that maps hundreds of authority documents to a common set of controls, enabling organizations to address multiple compliance mandates efficiently.',
   '2023', 'Network Frontiers', TRUE, '2007-01-01');


-- ═══════════════════════════════════════════════════════════════════════════════
-- OWASP Top 10 (2021) — 10 Risks
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @owaspt10, 'A01:2021',  'A01: Broken Access Control',
 'Access control enforces policy such that users cannot act outside of their intended permissions; failures typically lead to unauthorized information disclosure, modification, or destruction.',
 'mandatory', 'OWASP Top 10 Risks', 10),
(UUID(), @owaspt10, 'A02:2021',  'A02: Cryptographic Failures',
 'Failures related to cryptography (or lack thereof) often lead to exposure of sensitive data including passwords, credit card numbers, health records, and personal information.',
 'mandatory', 'OWASP Top 10 Risks', 20),
(UUID(), @owaspt10, 'A03:2021',  'A03: Injection',
 'Injection flaws occur when untrusted data is sent to an interpreter as part of a command or query; SQL, NoSQL, OS, and LDAP injection can result in data loss or corruption.',
 'mandatory', 'OWASP Top 10 Risks', 30),
(UUID(), @owaspt10, 'A04:2021',  'A04: Insecure Design',
 'Risks related to design and architectural flaws, requiring a shift toward threat modeling, secure design patterns, and reference architectures.',
 'mandatory', 'OWASP Top 10 Risks', 40),
(UUID(), @owaspt10, 'A05:2021',  'A05: Security Misconfiguration',
 'The most common security issue due to insecure default configurations, incomplete configurations, open cloud storage, and verbose error messages.',
 'mandatory', 'OWASP Top 10 Risks', 50),
(UUID(), @owaspt10, 'A06:2021',  'A06: Vulnerable and Outdated Components',
 'Using components with known vulnerabilities such as libraries, frameworks, and software modules can undermine application defenses and enable attacks.',
 'mandatory', 'OWASP Top 10 Risks', 60),
(UUID(), @owaspt10, 'A07:2021',  'A07: Identification and Authentication Failures',
 'Confirmation of user identity, authentication, and session management are critical to protect against authentication-related attacks.',
 'mandatory', 'OWASP Top 10 Risks', 70),
(UUID(), @owaspt10, 'A08:2021',  'A08: Software and Data Integrity Failures',
 'Code and infrastructure that does not protect against integrity violations including insecure deserialization and untrusted software updates.',
 'mandatory', 'OWASP Top 10 Risks', 80),
(UUID(), @owaspt10, 'A09:2021',  'A09: Security Logging and Monitoring Failures',
 'Insufficient logging and monitoring, including missing detection of breaches, results in attackers achieving their goals without detection.',
 'mandatory', 'OWASP Top 10 Risks', 90),
(UUID(), @owaspt10, 'A10:2021',  'A10: Server-Side Request Forgery (SSRF)',
 'SSRF flaws occur when a web application fetches a remote resource without validating the user-supplied URL, allowing attackers to send crafted requests from the vulnerable server.',
 'mandatory', 'OWASP Top 10 Risks', 100);


-- ═══════════════════════════════════════════════════════════════════════════════
-- OWASP ASVS v4.0 — 14 Chapters
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @owaspasvs, 'V1',   'V1: Architecture, Design, and Threat Modeling',
 'Security architecture requirements for all applications; threat modeling and security design principles to prevent security issues from architectural flaws.',
 'mandatory', 'ASVS Chapters', 10),
(UUID(), @owaspasvs, 'V2',   'V2: Authentication',
 'Controls ensuring that users are who they claim to be, covering passwords, credential management, multi-factor authentication, and lookup secrets.',
 'mandatory', 'ASVS Chapters', 20),
(UUID(), @owaspasvs, 'V3',   'V3: Session Management',
 'Controls for creating, managing, and terminating user sessions securely, including session token properties and cookie security.',
 'mandatory', 'ASVS Chapters', 30),
(UUID(), @owaspasvs, 'V4',   'V4: Access Control',
 'Controls ensuring that users can only access functions, data files, URLs, controllers, and services for which they have specific authorization.',
 'mandatory', 'ASVS Chapters', 40),
(UUID(), @owaspasvs, 'V5',   'V5: Validation, Sanitization, and Encoding',
 'Controls ensuring all input is validated and sanitized; all output is encoded to prevent injection and XSS vulnerabilities.',
 'mandatory', 'ASVS Chapters', 50),
(UUID(), @owaspasvs, 'V6',   'V6: Stored Cryptography',
 'Controls ensuring cryptographic modules are used correctly, keys are managed securely, and algorithms are appropriate for the data they protect.',
 'mandatory', 'ASVS Chapters', 60),
(UUID(), @owaspasvs, 'V7',   'V7: Error Handling and Logging',
 'Controls ensuring that errors are handled securely without exposing sensitive information and that security events are logged appropriately.',
 'mandatory', 'ASVS Chapters', 70),
(UUID(), @owaspasvs, 'V8',   'V8: Data Protection',
 'Controls ensuring that sensitive data is protected in transit, at rest, and during processing; data minimization and lifecycle controls.',
 'mandatory', 'ASVS Chapters', 80),
(UUID(), @owaspasvs, 'V9',   'V9: Communication',
 'Controls ensuring that communications are secure using TLS with appropriate cipher suites and certificate validation.',
 'mandatory', 'ASVS Chapters', 90),
(UUID(), @owaspasvs, 'V10',  'V10: Malicious Code',
 'Controls ensuring the application does not contain backdoors, Easter eggs, or logic bombs; defenses against timing attacks and side channels.',
 'mandatory', 'ASVS Chapters', 100),
(UUID(), @owaspasvs, 'V11',  'V11: Business Logic',
 'Controls ensuring that business logic flows cannot be abused or circumvented, enforcing business rules and preventing fraud.',
 'mandatory', 'ASVS Chapters', 110),
(UUID(), @owaspasvs, 'V12',  'V12: Files and Resources',
 'Controls ensuring that file uploads are validated and safely processed; protection against path traversal and file inclusion attacks.',
 'mandatory', 'ASVS Chapters', 120),
(UUID(), @owaspasvs, 'V13',  'V13: API and Web Service',
 'Controls ensuring that web services and APIs are protected against common attacks including parameter pollution, mass assignment, and excessive data exposure.',
 'mandatory', 'ASVS Chapters', 130),
(UUID(), @owaspasvs, 'V14',  'V14: Configuration',
 'Controls ensuring that application configuration is secure, components are up-to-date, and unneeded features are removed.',
 'mandatory', 'ASVS Chapters', 140);


-- ═══════════════════════════════════════════════════════════════════════════════
-- OWASP SAMM v2 — 5 Business Functions
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @owaspsamm, 'SAMM-G',  'Governance',
 'Establishes security practices at the organizational level covering Strategy & Metrics, Policy & Compliance, and Education & Guidance practices.',
 'mandatory', 'SAMM Business Functions', 10),
(UUID(), @owaspsamm, 'SAMM-D',  'Design',
 'Addresses software security from a design perspective covering Threat Assessment, Security Requirements, and Security Architecture practices.',
 'mandatory', 'SAMM Business Functions', 20),
(UUID(), @owaspsamm, 'SAMM-I',  'Implementation',
 'Covers secure coding practices including Secure Build, Secure Deployment, and Defect Management security practices.',
 'mandatory', 'SAMM Business Functions', 30),
(UUID(), @owaspsamm, 'SAMM-V',  'Verification',
 'Covers security testing and review activities including Architecture Assessment, Requirements-driven Testing, and Security Testing practices.',
 'mandatory', 'SAMM Business Functions', 40),
(UUID(), @owaspsamm, 'SAMM-O',  'Operations',
 'Addresses security in production operations covering Incident Management, Environment Management, and Operational Management practices.',
 'mandatory', 'SAMM Business Functions', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- OWASP MASVS v2 — 8 Categories
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @owaspmasvs, 'MASVS-STORAGE',     'MASVS-STORAGE: Storage',
 'Requirements for securely storing sensitive data on mobile devices, covering encryption, key storage, and avoiding insecure storage locations.',
 'mandatory', 'MASVS Categories', 10),
(UUID(), @owaspmasvs, 'MASVS-CRYPTO',      'MASVS-CRYPTO: Cryptography',
 'Requirements for implementing cryptography correctly in mobile apps, covering algorithm selection, key management, and random number generation.',
 'mandatory', 'MASVS Categories', 20),
(UUID(), @owaspmasvs, 'MASVS-AUTH',        'MASVS-AUTH: Authentication and Authorization',
 'Requirements for secure authentication and authorization in mobile applications, including local authentication and session management.',
 'mandatory', 'MASVS Categories', 30),
(UUID(), @owaspmasvs, 'MASVS-NETWORK',     'MASVS-NETWORK: Network Communication',
 'Requirements for securing network communications in mobile apps including TLS configuration, certificate validation, and traffic security.',
 'mandatory', 'MASVS Categories', 40),
(UUID(), @owaspmasvs, 'MASVS-PLATFORM',    'MASVS-PLATFORM: Platform Interaction',
 'Requirements for secure interaction with the mobile platform APIs, IPC mechanisms, WebViews, and platform-provided security features.',
 'mandatory', 'MASVS Categories', 50),
(UUID(), @owaspmasvs, 'MASVS-CODE',        'MASVS-CODE: Code Quality',
 'Requirements for secure coding practices, including input validation, memory management, and avoiding known insecure coding patterns.',
 'mandatory', 'MASVS Categories', 60),
(UUID(), @owaspmasvs, 'MASVS-RESILIENCE',  'MASVS-RESILIENCE: Resilience Against Reverse Engineering',
 'Requirements for mobile app protections against reverse engineering and tampering, including anti-debugging, obfuscation, and integrity checks.',
 'recommended', 'MASVS Categories', 70),
(UUID(), @owaspmasvs, 'MASVS-PRIVACY',     'MASVS-PRIVACY: Privacy',
 'Requirements for protecting user privacy in mobile applications, covering data minimization, consent, and transparency of data practices.',
 'mandatory', 'MASVS Categories', 80);


-- ═══════════════════════════════════════════════════════════════════════════════
-- MITRE ATT&CK Enterprise — 14 Tactics
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @mitreatk, 'TA0043',  'Reconnaissance',
 'Adversary is trying to gather information to plan future operations; techniques include active scanning, phishing for information, and gathering victim identity information.',
 'mandatory', 'ATT&CK Tactics', 10),
(UUID(), @mitreatk, 'TA0042',  'Resource Development',
 'Adversary is trying to establish resources to support operations; techniques include acquiring infrastructure, compromising accounts, and developing capabilities.',
 'mandatory', 'ATT&CK Tactics', 20),
(UUID(), @mitreatk, 'TA0001',  'Initial Access',
 'Adversary is trying to get into the network; techniques include phishing, exploiting public-facing applications, supply chain compromise, and drive-by compromise.',
 'mandatory', 'ATT&CK Tactics', 30),
(UUID(), @mitreatk, 'TA0002',  'Execution',
 'Adversary is trying to run malicious code; techniques include command and scripting interpreters, scheduled tasks, and exploitation for code execution.',
 'mandatory', 'ATT&CK Tactics', 40),
(UUID(), @mitreatk, 'TA0003',  'Persistence',
 'Adversary is trying to maintain their foothold; techniques include creating accounts, boot/logon autostart execution, and modifying authentication processes.',
 'mandatory', 'ATT&CK Tactics', 50),
(UUID(), @mitreatk, 'TA0004',  'Privilege Escalation',
 'Adversary is trying to gain higher-level permissions; techniques include access token manipulation, exploitation, and abuse of elevation control mechanisms.',
 'mandatory', 'ATT&CK Tactics', 60),
(UUID(), @mitreatk, 'TA0005',  'Defense Evasion',
 'Adversary is trying to avoid being detected; techniques include obfuscating code, disabling security tools, masquerading, and indicator removal.',
 'mandatory', 'ATT&CK Tactics', 70),
(UUID(), @mitreatk, 'TA0006',  'Credential Access',
 'Adversary is trying to steal account names and passwords; techniques include brute force, credential dumping, and keylogging.',
 'mandatory', 'ATT&CK Tactics', 80),
(UUID(), @mitreatk, 'TA0007',  'Discovery',
 'Adversary is trying to figure out the environment; techniques include account discovery, network scanning, and system information gathering.',
 'mandatory', 'ATT&CK Tactics', 90),
(UUID(), @mitreatk, 'TA0008',  'Lateral Movement',
 'Adversary is trying to move through the environment; techniques include exploitation of remote services, pass-the-hash, and remote desktop hijacking.',
 'mandatory', 'ATT&CK Tactics', 100),
(UUID(), @mitreatk, 'TA0009',  'Collection',
 'Adversary is trying to gather data of interest; techniques include accessing local files, email collection, screen capture, and keylogging.',
 'mandatory', 'ATT&CK Tactics', 110),
(UUID(), @mitreatk, 'TA0011',  'Command and Control (C2)',
 'Adversary is trying to communicate with compromised systems; techniques include application layer protocols, encrypted channels, and web service abuse.',
 'mandatory', 'ATT&CK Tactics', 120),
(UUID(), @mitreatk, 'TA0010',  'Exfiltration',
 'Adversary is trying to steal data; techniques include automated exfiltration, data compression, transfer size limits, and alternative protocols.',
 'mandatory', 'ATT&CK Tactics', 130),
(UUID(), @mitreatk, 'TA0040',  'Impact',
 'Adversary is trying to manipulate, interrupt, or destroy systems and data; techniques include data encryption (ransomware), defacement, and denial of service.',
 'mandatory', 'ATT&CK Tactics', 140);


-- ═══════════════════════════════════════════════════════════════════════════════
-- MITRE D3FEND — 5 Defensive Tactics
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @mitred3f, 'D3-HD',  'Harden',
 'Increase the difficulty of adversary exploitation by reducing attack surface through hardening techniques like credential hardening and message hardening.',
 'mandatory', 'D3FEND Tactics', 10),
(UUID(), @mitred3f, 'D3-DT',  'Detect',
 'Identify adversary presence or actions through detection techniques including file analysis, identifier analysis, network traffic analysis, and user behavior analysis.',
 'mandatory', 'D3FEND Tactics', 20),
(UUID(), @mitred3f, 'D3-IS',  'Isolate',
 'Create logical or physical barriers between compromised components and the rest of the network using network isolation, execution isolation, and enclave creation.',
 'mandatory', 'D3FEND Tactics', 30),
(UUID(), @mitred3f, 'D3-DC',  'Deceive',
 'Mislead or misdirect adversaries using decoy techniques such as honeypots, decoy files, and lure credentials to detect and study attacker activity.',
 'recommended', 'D3FEND Tactics', 40),
(UUID(), @mitred3f, 'D3-EV',  'Evict',
 'Remove adversaries from networks and systems through restoration and eviction techniques including file restoration, credential eviction, and process termination.',
 'mandatory', 'D3FEND Tactics', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- BSIMM — 4 Domains
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @bsimm, 'BSIMM-GV',  'Governance (Strategy, Metrics, Compliance & Policy, Training)',
 'Practices for organizing, managing, and measuring a software security program including executive support, metrics, compliance gates, and security training.',
 'mandatory', 'BSIMM Domains', 10),
(UUID(), @bsimm, 'BSIMM-IN',  'Intelligence (Attack Models, Security Features & Design, Standards & Requirements)',
 'Practices for creating and using proactive security intelligence including threat modeling, security feature libraries, and regulatory requirements gathering.',
 'mandatory', 'BSIMM Domains', 20),
(UUID(), @bsimm, 'BSIMM-ST',  'SSDL Touchpoints (Architecture Analysis, Code Review, Security Testing, Penetration Testing)',
 'Practices that are applied to software artifacts and carry out analysis and testing throughout the software development lifecycle.',
 'mandatory', 'BSIMM Domains', 30),
(UUID(), @bsimm, 'BSIMM-DP',  'Deployment (Vulnerability Management, Environment Hardening, Configuration Management & Vulnerability Response)',
 'Practices that govern software configuration, patch management, vulnerability disclosure handling, and operational security monitoring.',
 'mandatory', 'BSIMM Domains', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- CIS Benchmarks — Technology Categories
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @cisbench, 'CIS-OS',     'Operating System Benchmarks',
 'Configuration hardening guidelines for Windows Server/Desktop, Linux distributions (Ubuntu, RHEL, Debian), and macOS.',
 'mandatory', 'CIS Benchmark Categories', 10),
(UUID(), @cisbench, 'CIS-CLOUD',  'Cloud Provider Benchmarks',
 'Security configuration baselines for AWS, Microsoft Azure, and Google Cloud Platform, covering identity, storage, networking, and monitoring.',
 'mandatory', 'CIS Benchmark Categories', 20),
(UUID(), @cisbench, 'CIS-NET',    'Network Device Benchmarks',
 'Hardening guidelines for network devices including Cisco IOS, Juniper, Palo Alto, and F5 devices.',
 'mandatory', 'CIS Benchmark Categories', 30),
(UUID(), @cisbench, 'CIS-SRV',    'Server Software Benchmarks',
 'Configuration guidelines for web servers (Apache, Nginx, IIS), database servers (MySQL, PostgreSQL, MSSQL, Oracle), and middleware.',
 'mandatory', 'CIS Benchmark Categories', 40),
(UUID(), @cisbench, 'CIS-DESK',   'Desktop Software Benchmarks',
 'Hardening guidelines for desktop applications including web browsers (Chrome, Edge, Firefox), Microsoft Office, and productivity tools.',
 'recommended', 'CIS Benchmark Categories', 50),
(UUID(), @cisbench, 'CIS-MOB',    'Mobile Device Benchmarks',
 'Configuration guidelines for iOS and Android devices including device management settings, app permissions, and enterprise enrollment.',
 'recommended', 'CIS Benchmark Categories', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- CSA STAR — 3 Levels
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @csastar, 'STAR-L1',  'Level 1: STAR Self-Assessment',
 'CSPs self-assess their security controls against the CCM or CAIQ and publish results to the CSA STAR Registry; free and open to all CSPs.',
 'recommended', 'CSA STAR Levels', 10),
(UUID(), @csastar, 'STAR-L2',  'Level 2: STAR Certification and Attestation',
 'CSPs undergo independent third-party assessment resulting in STAR Certification (based on ISO 27001) or STAR Attestation (based on SOC 2).',
 'mandatory', 'CSA STAR Levels', 20),
(UUID(), @csastar, 'STAR-L3',  'Level 3: STAR Continuous Monitoring',
 'Advanced STAR program providing real-time security posture monitoring against CCM controls with automated, continuous assurance reporting.',
 'recommended', 'CSA STAR Levels', 30);


-- ═══════════════════════════════════════════════════════════════════════════════
-- CISA Zero Trust Maturity Model v2.0 — Pillars and Cross-Cutting Themes
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @cisazt, 'ZT-ID',   'Pillar: Identity',
 'Establish a dynamic and contextual identity verification for all users, service accounts, and non-person entities before granting access to resources.',
 'mandatory', 'CISA ZT Pillars', 10),
(UUID(), @cisazt, 'ZT-DEV',  'Pillar: Devices',
 'Ensure all endpoints (managed, unmanaged, IoT) are known, validated, monitored, and compliant before being granted access to enterprise resources.',
 'mandatory', 'CISA ZT Pillars', 20),
(UUID(), @cisazt, 'ZT-NET',  'Pillar: Networks',
 'Segment networks to limit lateral movement, encrypt all traffic, and implement software-defined perimeters and microsegmentation.',
 'mandatory', 'CISA ZT Pillars', 30),
(UUID(), @cisazt, 'ZT-APP',  'Pillar: Applications and Workloads',
 'Protect applications and workloads across on-premises, hybrid, and multi-cloud environments with continuous authorization and runtime protection.',
 'mandatory', 'CISA ZT Pillars', 40),
(UUID(), @cisazt, 'ZT-DAT',  'Pillar: Data',
 'Categorize, label, and protect all data at rest, in transit, and in use using encryption, rights management, and data loss prevention controls.',
 'mandatory', 'CISA ZT Pillars', 50),
(UUID(), @cisazt, 'ZT-VIS',  'Cross-Cutting: Visibility and Analytics',
 'Maintain comprehensive visibility into all activities across the environment to detect anomalies, measure ZT effectiveness, and enable data-driven decisions.',
 'mandatory', 'CISA ZT Cross-Cutting', 60),
(UUID(), @cisazt, 'ZT-AUT',  'Cross-Cutting: Automation and Orchestration',
 'Automate security responses and orchestrate workflows across all ZT pillars to reduce human latency and improve consistency of policy enforcement.',
 'mandatory', 'CISA ZT Cross-Cutting', 70),
(UUID(), @cisazt, 'ZT-GOV',  'Cross-Cutting: Governance',
 'Establish ZT governance including policies, standards, roles, and accountability structures to guide the ZT transformation program.',
 'mandatory', 'CISA ZT Cross-Cutting', 80);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NSA Zero Trust Framework — 7 Pillars
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @nsazt, 'NSA-ZT-U',   'User Pillar',
 'Continuously validate users through multi-factor authentication, behavioral analytics, and dynamic access decisions based on user context and risk.',
 'mandatory', 'NSA ZT Pillars', 10),
(UUID(), @nsazt, 'NSA-ZT-D',   'Device Pillar',
 'Ensure all devices accessing resources are inventoried, authenticated, and comply with health requirements before access is granted.',
 'mandatory', 'NSA ZT Pillars', 20),
(UUID(), @nsazt, 'NSA-ZT-NE',  'Network/Environment Pillar',
 'Segment and isolate network environments, encrypt all traffic, and restrict lateral movement through dynamic access controls.',
 'mandatory', 'NSA ZT Pillars', 30),
(UUID(), @nsazt, 'NSA-ZT-AW',  'Application and Workload Pillar',
 'Apply secure access controls to all applications and workloads, validate app integrity, and implement runtime protection.',
 'mandatory', 'NSA ZT Pillars', 40),
(UUID(), @nsazt, 'NSA-ZT-DA',  'Data Pillar',
 'Classify, label, and protect all data assets; enforce data-centric security policies regardless of data location.',
 'mandatory', 'NSA ZT Pillars', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST SP 800-207 — Zero Trust Tenets
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @n800207, 'ZTA-1',  'All data sources and computing services are resources',
 'All enterprise resources must be treated as resources, regardless of physical location or whether they are on or off the enterprise-owned network.',
 'mandatory', 'ZTA Tenets', 10),
(UUID(), @n800207, 'ZTA-2',  'All communication is secured regardless of network location',
 'Network location alone does not imply trust; all communication must be secured with the goal of securing the data in motion.',
 'mandatory', 'ZTA Tenets', 20),
(UUID(), @n800207, 'ZTA-3',  'Access to individual enterprise resources is granted per-session with least privilege',
 'Access is granted on a per-request basis with least privilege; requestors must justify the access they need for each session.',
 'mandatory', 'ZTA Tenets', 30),
(UUID(), @n800207, 'ZTA-4',  'Access to resources is determined by dynamic policy',
 'Access policy is based on client identity, application, requesting asset state, and behavioral/environmental attributes gathered and measured.',
 'mandatory', 'ZTA Tenets', 40),
(UUID(), @n800207, 'ZTA-5',  'The enterprise monitors and measures the integrity and security posture of all assets',
 'All owned and associated assets are continuously monitored and measured for security posture, which informs access decisions.',
 'mandatory', 'ZTA Tenets', 50),
(UUID(), @n800207, 'ZTA-6',  'All resource authentication and authorization are dynamic and strictly enforced before access is allowed',
 'Authentication and authorization are continuous processes that re-evaluate before each access request is granted; no standing trust.',
 'mandatory', 'ZTA Tenets', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- Secure Controls Framework (SCF) — 31 Domains
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @scf, 'SCF-PRI',  'Privacy (PRI)',
 'Controls addressing privacy program governance, data subject rights, consent management, and privacy by design principles.',
 'mandatory', 'SCF Domains', 10),
(UUID(), @scf, 'SCF-GOV',  'Security & Privacy Governance (GOV)',
 'Controls establishing security and privacy governance structures, policies, standards, and executive accountability.',
 'mandatory', 'SCF Domains', 20),
(UUID(), @scf, 'SCF-OPS',  'Security Operations (OPS)',
 'Controls governing day-to-day security operations including change management, patch management, and operational procedures.',
 'mandatory', 'SCF Domains', 30),
(UUID(), @scf, 'SCF-IAM',  'Identity & Access Management (IAM)',
 'Controls for managing digital identities, authentication, authorization, and privileged access across the enterprise.',
 'mandatory', 'SCF Domains', 40),
(UUID(), @scf, 'SCF-AST',  'Asset Management (AST)',
 'Controls for identifying, classifying, and managing hardware and software assets throughout their lifecycle.',
 'mandatory', 'SCF Domains', 50),
(UUID(), @scf, 'SCF-CFG',  'Configuration Management (CFG)',
 'Controls for establishing secure baselines and managing configuration changes to systems and applications.',
 'mandatory', 'SCF Domains', 60),
(UUID(), @scf, 'SCF-VPM',  'Vulnerability & Patch Management (VPM)',
 'Controls for identifying vulnerabilities, tracking remediation, and managing the patching lifecycle.',
 'mandatory', 'SCF Domains', 70),
(UUID(), @scf, 'SCF-DCH',  'Data Classification & Handling (DCH)',
 'Controls for classifying, labeling, and handling data based on sensitivity throughout its lifecycle.',
 'mandatory', 'SCF Domains', 80),
(UUID(), @scf, 'SCF-CRY',  'Cryptographic Protections (CRY)',
 'Controls for using cryptographic algorithms, key management, and certificate management to protect data.',
 'mandatory', 'SCF Domains', 90),
(UUID(), @scf, 'SCF-NET',  'Network Security (NET)',
 'Controls for securing network infrastructure including segmentation, monitoring, filtering, and boundary protection.',
 'mandatory', 'SCF Domains', 100),
(UUID(), @scf, 'SCF-END',  'Endpoint Security (END)',
 'Controls for securing end-user devices including workstations, laptops, mobile devices, and IoT endpoints.',
 'mandatory', 'SCF Domains', 110),
(UUID(), @scf, 'SCF-IRO',  'Incident Response (IRO)',
 'Controls for preparing for, detecting, responding to, and recovering from security incidents.',
 'mandatory', 'SCF Domains', 120),
(UUID(), @scf, 'SCF-BCR',  'Business Continuity & Disaster Recovery (BCR)',
 'Controls for ensuring business continuity and the ability to recover from disruptive events.',
 'mandatory', 'SCF Domains', 130),
(UUID(), @scf, 'SCF-TPM',  'Third-Party Management (TPM)',
 'Controls for assessing and managing cybersecurity risks associated with third-party vendors and service providers.',
 'mandatory', 'SCF Domains', 140),
(UUID(), @scf, 'SCF-HRS',  'Human Resources Security (HRS)',
 'Controls for managing personnel security including screening, onboarding, role-based training, and termination procedures.',
 'mandatory', 'SCF Domains', 150);


-- ═══════════════════════════════════════════════════════════════════════════════
-- Unified Compliance Framework (UCF) — Harmonization Approach
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @ucf, 'UCF-AD',   'Authority Documents',
 'Catalog and map all applicable laws, regulations, standards, and contractual requirements (authority documents) relevant to the organization.',
 'mandatory', 'UCF Components', 10),
(UUID(), @ucf, 'UCF-CC',   'Common Controls',
 'Identify the harmonized set of common controls derived from mapping across all authority documents to eliminate redundancy in compliance activities.',
 'mandatory', 'UCF Components', 20),
(UUID(), @ucf, 'UCF-CM',   'Compliance Mandates Mapping',
 'Map organizational controls and evidence to multiple compliance mandates simultaneously using the UCF''s unified control hierarchy.',
 'mandatory', 'UCF Components', 30);
