-- Additional compliance frameworks: ISO 27017, ISO 27018, ISO 42001, NIST AI RMF, EU AI Act, StateRAMP

SET @iso27017  = 'f1a2b3c4-d5e6-4f1a-9b2c-3d4e5f6a7b8c';
SET @iso27018  = 'a2b3c4d5-e6f7-4a2b-9c3d-4e5f6a7b8c9d';
SET @iso42001  = 'b3c4d5e6-f7a8-4b3c-9d4e-5f6a7b8c9d0e';
SET @nistairm  = 'c4d5e6f7-a8b9-4c4d-9e5f-6a7b8c9d0e1f';
SET @euaiact   = 'd5e6f7a8-b9c0-4d5e-9f6a-7b8c9d0e1f2a';
SET @stateramp = 'e6f7a8b9-c0d1-4e6f-9a7b-8c9d0e1f2a3b';

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

INSERT IGNORE INTO regulatory_frameworks
  (id, code, name, description, version, issuing_body, is_global, effective_date)
VALUES
  (@iso27017,  'ISO 27017',   'ISO/IEC 27017:2015 — Cloud Security Controls',
   'Code of practice for information security controls based on ISO/IEC 27002 for cloud services, providing additional guidance for cloud providers and cloud customers.',
   '2015', 'ISO/IEC', TRUE, '2015-12-15'),

  (@iso27018,  'ISO 27018',   'ISO/IEC 27018:2019 — Privacy in Public Cloud (PII)',
   'Code of practice for protection of personally identifiable information (PII) in public clouds acting as PII processors.',
   '2019', 'ISO/IEC', TRUE, '2019-01-15'),

  (@iso42001,  'ISO 42001',   'ISO/IEC 42001:2023 — AI Management System',
   'International standard specifying requirements for establishing, implementing, maintaining, and continually improving an AI management system (AIMS) within organizations.',
   '2023', 'ISO/IEC', TRUE, '2023-12-18'),

  (@nistairm,  'NIST AI RMF', 'NIST AI Risk Management Framework 1.0',
   'Voluntary framework to improve the ability of organizations to incorporate trustworthiness considerations into the design, development, use, and evaluation of AI products and services.',
   '1.0', 'NIST', TRUE, '2023-01-26'),

  (@euaiact,   'EU AI Act',   'EU Artificial Intelligence Act',
   'EU regulation establishing a harmonized legal framework for AI systems, classifying AI by risk level and imposing requirements proportional to that risk.',
   '2024', 'European Parliament', FALSE, '2024-08-01'),

  (@stateramp, 'StateRAMP',   'StateRAMP — State Risk and Authorization Management Program',
   'Program providing state and local governments a standardized approach to security assessment, authorization, and continuous monitoring for cloud service providers.',
   '2023', 'StateRAMP PMO', FALSE, '2021-03-01');


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO 27017 — Cloud Security Controls
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @iso27017, 'CLD.6.3',  'Relationship Between Cloud Service Customer and Provider',
 'Define and document the shared responsibilities between cloud service customer and cloud service provider for each control.',
 'mandatory', 'Governance', 10),
(UUID(), @iso27017, 'CLD.8.1',  'Asset Inventory for Cloud Services',
 'Maintain an inventory of all assets used in cloud services and ensure ownership is clearly assigned.',
 'mandatory', 'Asset Management', 20),
(UUID(), @iso27017, 'CLD.9.5',  'Segregation in Virtual Computing Environments',
 'Virtual machines in a cloud computing environment shall be kept separate from other virtual machines and management functions.',
 'mandatory', 'Infrastructure Security', 30),
(UUID(), @iso27017, 'CLD.12.1', 'Administrator Operational Security',
 'Procedures for cloud service administration operations shall be documented and implemented.',
 'mandatory', 'Operations', 40),
(UUID(), @iso27017, 'CLD.12.4', 'Monitoring of Cloud Services',
 'Cloud service providers shall maintain audit logs and make them available to cloud service customers as agreed.',
 'mandatory', 'Logging & Monitoring', 50),
(UUID(), @iso27017, 'CLD.13.1', 'Network Security in Cloud Environments',
 'Implement network controls to protect cloud service networks from unauthorized access and attack.',
 'mandatory', 'Network Security', 60),
(UUID(), @iso27017, 'CLD.16.1', 'Notification of Security Incidents',
 'Cloud service providers shall notify cloud service customers of security incidents affecting their data without undue delay.',
 'mandatory', 'Incident Response', 70),
(UUID(), @iso27017, 'CLD.17.2', 'ICT Continuity for Cloud Services',
 'Establish, maintain, and test ICT continuity plans for cloud services to support customer business continuity.',
 'mandatory', 'Business Continuity', 80),
(UUID(), @iso27017, 'CLD.18.1', 'Compliance with Legal and Contractual Requirements',
 'Identify and document all relevant legal, regulatory, and contractual requirements for cloud services.',
 'mandatory', 'Compliance', 90);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO 27018 — Privacy in Public Cloud (PII)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @iso27018, 'A.1',   'Consent and Choice',
 'Process PII only under instructions from the cloud service customer (controller); do not use PII for marketing without explicit consent.',
 'mandatory', 'Privacy Principles', 10),
(UUID(), @iso27018, 'A.2',   'Purpose Legitimacy and Specification',
 'Process PII for the specified, explicit, and legitimate purposes defined in the contract with the customer.',
 'mandatory', 'Privacy Principles', 20),
(UUID(), @iso27018, 'A.3',   'Collection Limitation',
 'Collect only the minimum PII necessary for the specified purpose; avoid excess collection.',
 'mandatory', 'Privacy Principles', 30),
(UUID(), @iso27018, 'A.5',   'Use, Retention and Disclosure Limitation',
 'Use, retain, and disclose PII only as necessary for the contracted purpose; delete PII when no longer required.',
 'mandatory', 'Data Lifecycle', 40),
(UUID(), @iso27018, 'A.9',   'Accountability',
 'Cloud service provider shall designate a point of contact for privacy matters and maintain accountability records.',
 'mandatory', 'Governance', 50),
(UUID(), @iso27018, 'A.10',  'Information Security',
 'Implement technical and organizational security measures to protect PII against accidental or unlawful destruction, alteration, or unauthorized disclosure.',
 'mandatory', 'Security Controls', 60),
(UUID(), @iso27018, 'A.11',  'Privacy Compliance',
 'Identify and comply with applicable laws and regulations relating to the processing of PII.',
 'mandatory', 'Compliance', 70),
(UUID(), @iso27018, 'B.1',   'Temporary Files Containing PII',
 'Temporary files and data created during cloud processing of PII shall be documented and securely deleted.',
 'mandatory', 'Data Management', 80),
(UUID(), @iso27018, 'B.2',   'Disclosure of Sub-processors',
 'Cloud service providers shall inform customers of sub-processors used to process PII and obtain approval before engaging new sub-processors.',
 'mandatory', 'Third-party Risk', 90),
(UUID(), @iso27018, 'B.3',   'Data Return and Deletion',
 'On contract termination, return all PII to the customer and securely delete all copies within an agreed timeframe.',
 'mandatory', 'Data Lifecycle', 100),
(UUID(), @iso27018, 'B.4',   'PII Breach Notification',
 'Notify the cloud service customer of any personal data breach without undue delay and provide sufficient detail for the customer to meet regulatory reporting obligations.',
 'mandatory', 'Incident Response', 110),
(UUID(), @iso27018, 'B.5',   'Lawful Disclosure of PII',
 'Disclose PII to law enforcement only when legally required; inform the customer unless prohibited by law.',
 'mandatory', 'Legal', 120);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ISO 42001 — AI Management System
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @iso42001, 'Cl.4',   'Context of the Organization',
 'Understand the organization, its context, stakeholder needs, and determine the scope of the AI management system.',
 'mandatory', 'Foundation', 10),
(UUID(), @iso42001, 'Cl.5',   'Leadership and AI Policy',
 'Top management shall demonstrate commitment to the AIMS, establish an AI policy, and assign roles and responsibilities.',
 'mandatory', 'Governance', 20),
(UUID(), @iso42001, 'Cl.6',   'AI Risk and Impact Planning',
 'Identify AI-related risks and opportunities; plan actions to address them; define AI objectives.',
 'mandatory', 'Risk Management', 30),
(UUID(), @iso42001, 'Cl.8',   'AI System Lifecycle Operations',
 'Plan, implement, and control processes for the AI system lifecycle including design, development, deployment, and decommissioning.',
 'mandatory', 'AI Operations', 40),
(UUID(), @iso42001, 'A.2',    'AI Policy',
 'Establish, implement, maintain, and communicate an AI policy that sets out principles for responsible AI use.',
 'mandatory', 'Governance', 50),
(UUID(), @iso42001, 'A.3',    'Internal Organization for AI',
 'Define roles, responsibilities, and authorities for AI governance including an AI officer or equivalent function.',
 'mandatory', 'Governance', 60),
(UUID(), @iso42001, 'A.4',    'Resources for AI Systems',
 'Identify and provide resources required for AI systems including data, compute, tools, and human expertise.',
 'mandatory', 'AI Operations', 70),
(UUID(), @iso42001, 'A.5',    'Assessing Impacts of AI Systems',
 'Conduct AI impact assessments prior to deployment, including assessment of human rights, fairness, safety, and societal impacts.',
 'mandatory', 'Risk Management', 80),
(UUID(), @iso42001, 'A.6',    'AI System Lifecycle',
 'Manage the full lifecycle of AI systems from initial concept through decommissioning with defined gates and documentation.',
 'mandatory', 'AI Operations', 90),
(UUID(), @iso42001, 'A.7',    'Data for AI Systems',
 'Govern data used in AI systems including data quality, provenance, bias assessment, and data rights.',
 'mandatory', 'Data Governance', 100),
(UUID(), @iso42001, 'A.8',    'Information for Interested Parties',
 'Provide appropriate information to users and affected parties about AI systems, their capabilities, limitations, and intended use.',
 'mandatory', 'Transparency', 110),
(UUID(), @iso42001, 'A.9',    'Use of AI Systems',
 'Ensure AI systems are used only for their intended purpose and that users are appropriately trained.',
 'mandatory', 'AI Operations', 120),
(UUID(), @iso42001, 'A.10',   'Third-Party and Customer Relationships',
 'Manage risks arising from AI systems developed or provided by third parties or deployed to customers.',
 'mandatory', 'Third-party Risk', 130);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NIST AI Risk Management Framework 1.0
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @nistairm, 'GOVERN',     'Govern',
 'Establish policies, processes, procedures, and practices for AI risk management across the organization. AI risk and trustworthiness are prioritized in organization-wide decisions.',
 'mandatory', 'Core Function', 10),
(UUID(), @nistairm, 'GOV.1',      'Policies and Accountability',
 'Establish, communicate, and enforce organizational policies covering AI risk management responsibilities, accountability, and culture.',
 'mandatory', 'Governance', 20),
(UUID(), @nistairm, 'GOV.2',      'Roles and Responsibilities',
 'Designate AI roles (e.g., AI risk officer) and establish clear responsibilities for AI risk management across teams.',
 'mandatory', 'Governance', 30),
(UUID(), @nistairm, 'GOV.3',      'Organizational Teams',
 'Engage cross-functional teams including legal, privacy, security, and domain experts in AI risk governance.',
 'mandatory', 'Governance', 40),
(UUID(), @nistairm, 'MAP',        'Map',
 'Identify the context in which AI systems operate and categorize AI risks including impacts to individuals and society.',
 'mandatory', 'Core Function', 50),
(UUID(), @nistairm, 'MAP.1',      'AI Context Identification',
 'Identify and document the intended use, deployment context, user population, and potential impacts of each AI system.',
 'mandatory', 'Risk Identification', 60),
(UUID(), @nistairm, 'MAP.2',      'Scientific and Technical Risks',
 'Identify and document risks arising from AI system design, data quality, model performance, and technical limitations.',
 'mandatory', 'Risk Identification', 70),
(UUID(), @nistairm, 'MAP.3',      'AI Risk Categorization',
 'Categorize AI risks according to likelihood and impact considering harms to individuals, groups, organizations, and society.',
 'mandatory', 'Risk Assessment', 80),
(UUID(), @nistairm, 'MEASURE',    'Measure',
 'Analyze and assess identified AI risks using quantitative and qualitative methods to support informed decision-making.',
 'mandatory', 'Core Function', 90),
(UUID(), @nistairm, 'MEASURE.1',  'AI Risk Measurement Methods',
 'Implement methods and metrics to measure identified AI risks including accuracy, bias, robustness, and explainability.',
 'mandatory', 'Risk Measurement', 100),
(UUID(), @nistairm, 'MEASURE.2',  'Trustworthiness Characteristics',
 'Evaluate AI systems against trustworthiness characteristics: valid, reliable, safe, secure, explainable, fair, privacy-enhanced.',
 'mandatory', 'Risk Measurement', 110),
(UUID(), @nistairm, 'MEASURE.3',  'Bias and Fairness Assessment',
 'Assess AI systems for bias and fairness issues using representative test datasets and disaggregated metrics.',
 'mandatory', 'Risk Measurement', 120),
(UUID(), @nistairm, 'MANAGE',     'Manage',
 'Prioritize and address AI risks based on measurement outputs; implement mitigations and monitor effectiveness.',
 'mandatory', 'Core Function', 130),
(UUID(), @nistairm, 'MANAGE.1',   'Risk Treatment',
 'Prioritize AI risks based on likelihood and impact; implement mitigations, transfer, accept, or avoid identified risks.',
 'mandatory', 'Risk Treatment', 140),
(UUID(), @nistairm, 'MANAGE.2',   'AI Incident Response',
 'Establish and maintain incident response plans specifically for AI system failures, harmful outputs, or misuse.',
 'mandatory', 'Incident Response', 150),
(UUID(), @nistairm, 'MANAGE.3',   'Residual Risk Monitoring',
 'Monitor residual AI risks continuously and update risk assessments based on new information or changed conditions.',
 'mandatory', 'Risk Monitoring', 160);


-- ═══════════════════════════════════════════════════════════════════════════════
-- EU AI Act
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @euaiact, 'Art.5',    'Prohibited AI Practices',
 'AI systems using subliminal manipulation, exploiting vulnerabilities, social scoring, or real-time biometric surveillance in public spaces are prohibited.',
 'mandatory', 'Prohibited Practices', 10),
(UUID(), @euaiact, 'Art.6',    'High-Risk AI Classification',
 'Classify AI systems as high-risk based on Annex III use cases including critical infrastructure, education, employment, essential services, law enforcement, and biometrics.',
 'mandatory', 'Risk Classification', 20),
(UUID(), @euaiact, 'Art.9',    'Risk Management System',
 'Establish, implement, document, and maintain a risk management system for high-risk AI systems throughout the entire lifecycle.',
 'mandatory', 'High-Risk AI', 30),
(UUID(), @euaiact, 'Art.10',   'Data and Data Governance',
 'Training, validation, and testing data for high-risk AI must meet quality criteria: relevant, representative, free of errors, and complete.',
 'mandatory', 'High-Risk AI', 40),
(UUID(), @euaiact, 'Art.11',   'Technical Documentation',
 'Providers of high-risk AI systems must maintain technical documentation demonstrating compliance before placing on the market.',
 'mandatory', 'High-Risk AI', 50),
(UUID(), @euaiact, 'Art.12',   'Record-Keeping and Logging',
 'High-risk AI systems shall be designed with automatic logging capabilities to enable post-deployment monitoring.',
 'mandatory', 'High-Risk AI', 60),
(UUID(), @euaiact, 'Art.13',   'Transparency and Information Provision',
 'High-risk AI systems shall be designed to be sufficiently transparent for users to interpret outputs and use them appropriately.',
 'mandatory', 'Transparency', 70),
(UUID(), @euaiact, 'Art.14',   'Human Oversight',
 'High-risk AI systems shall be designed to allow effective human oversight, including the ability to override or stop the system.',
 'mandatory', 'Human Oversight', 80),
(UUID(), @euaiact, 'Art.15',   'Accuracy, Robustness and Cybersecurity',
 'High-risk AI systems shall achieve appropriate accuracy and be resilient against errors, faults, and adversarial attacks.',
 'mandatory', 'Technical Requirements', 90),
(UUID(), @euaiact, 'Art.16',   'Obligations of Providers',
 'AI providers must implement quality management systems, register high-risk AI, affix CE marking, and cooperate with authorities.',
 'mandatory', 'Provider Obligations', 100),
(UUID(), @euaiact, 'Art.26',   'Obligations of Deployers',
 'Deployers of high-risk AI must conduct fundamental rights impact assessments, maintain logs, ensure human oversight, and inform affected individuals.',
 'mandatory', 'Deployer Obligations', 110),
(UUID(), @euaiact, 'Art.50',   'Transparency Obligations for GPAI',
 'General purpose AI systems must disclose when content is AI-generated (deepfakes); GPAI providers must publish summaries of training data.',
 'mandatory', 'General Purpose AI', 120),
(UUID(), @euaiact, 'Art.53',   'Obligations for GPAI Model Providers',
 'Providers of general purpose AI models must maintain technical documentation, comply with copyright law, and publish model capability summaries.',
 'mandatory', 'General Purpose AI', 130),
(UUID(), @euaiact, 'Art.55',   'Systemic Risk for GPAI',
 'Providers of GPAI models with systemic risk must perform adversarial testing, report serious incidents, and implement cybersecurity measures.',
 'mandatory', 'General Purpose AI', 140);


-- ═══════════════════════════════════════════════════════════════════════════════
-- StateRAMP
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT INTO framework_requirements (id, framework_id, reference_code, title, description, requirement_type, category, sort_order) VALUES
(UUID(), @stateramp, 'SR-AUTH',   'StateRAMP Authorization',
 'Cloud service providers must achieve StateRAMP authorization (Ready, Provisional, or Authorized status) to offer services to participating state and local governments.',
 'mandatory', 'Authorization', 10),
(UUID(), @stateramp, 'SR-LOW',    'Low Impact Baseline',
 'Implement NIST 800-53 Rev 5 Low baseline controls for systems where loss of CIA would have limited adverse effect on government operations.',
 'mandatory', 'Security Baseline', 20),
(UUID(), @stateramp, 'SR-MOD',    'Moderate Impact Baseline',
 'Implement NIST 800-53 Rev 5 Moderate baseline controls for systems where loss of CIA could have serious adverse effect on government operations.',
 'mandatory', 'Security Baseline', 30),
(UUID(), @stateramp, 'SR-HIGH',   'High Impact Baseline',
 'Implement NIST 800-53 Rev 5 High baseline controls for systems where loss of CIA could have severe or catastrophic adverse effect.',
 'mandatory', 'Security Baseline', 40),
(UUID(), @stateramp, 'SR-3PAO',   'Third-Party Assessment',
 'Engage a StateRAMP-approved third-party assessment organization (3PAO) for initial and annual security assessments.',
 'mandatory', 'Assessment', 50),
(UUID(), @stateramp, 'SR-CM',     'Continuous Monitoring',
 'Implement a continuous monitoring program reporting monthly to StateRAMP PMO covering vulnerability scanning, log review, and POA&M management.',
 'mandatory', 'Continuous Monitoring', 60),
(UUID(), @stateramp, 'SR-IR',     'Incident Reporting',
 'Report significant security incidents to StateRAMP PMO and affected state governments within defined timeframes.',
 'mandatory', 'Incident Response', 70),
(UUID(), @stateramp, 'SR-POAM',   'Plan of Action and Milestones',
 'Maintain a current POA&M documenting all identified security weaknesses and planned remediation activities.',
 'mandatory', 'Remediation', 80),
(UUID(), @stateramp, 'SR-SSP',    'System Security Plan',
 'Develop and maintain a comprehensive system security plan describing the system boundary, controls implemented, and control implementation details.',
 'mandatory', 'Documentation', 90),
(UUID(), @stateramp, 'SR-DATA',   'Data Residency and Sovereignty',
 'Ensure government data is stored within the United States and subject to US law; document data flows and storage locations.',
 'mandatory', 'Data Governance', 100),
(UUID(), @stateramp, 'SR-SUPPLY', 'Supply Chain Risk Management',
 'Identify and manage risks from software and hardware supply chain components used in the cloud service.',
 'mandatory', 'Third-party Risk', 110),
(UUID(), @stateramp, 'SR-FIPS',   'Cryptographic Standards',
 'Use FIPS 140-2 or FIPS 140-3 validated cryptographic modules for all data at rest and in transit.',
 'mandatory', 'Technical Controls', 120);
