-- Seed common compliance frameworks and requirements

SET @soc2   = 'a1b2c3d4-e5f6-4a1b-8c3d-4e5f6a1b2c3d';
SET @iso    = 'b2c3d4e5-f6a1-4b2c-8d4e-5f6a1b2c3d4e';
SET @nist   = 'c3d4e5f6-a1b2-4c3d-8e5f-6a1b2c3d4e5f';
SET @hipaa  = 'd4e5f6a1-b2c3-4d4e-8f6a-1b2c3d4e5f6a';
SET @pci    = 'e5f6a1b2-c3d4-4e5f-8a1b-2c3d4e5f6a1b';

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

INSERT IGNORE INTO regulatory_frameworks
  (id, code, name, description, version, issuing_body, is_global, effective_date)
VALUES
  (@soc2,  'SOC 2',      'SOC 2 Type II',
   'Service Organization Control 2 — security, availability, processing integrity, confidentiality, and privacy of customer data.',
   '2017', 'AICPA', TRUE, '2017-05-01'),

  (@iso,   'ISO 27001',  'ISO/IEC 27001:2022',
   'International standard for information security management systems (ISMS).',
   '2022', 'ISO/IEC', TRUE, '2022-10-25'),

  (@nist,  'NIST CSF',   'NIST Cybersecurity Framework',
   'Voluntary framework for managing and reducing cybersecurity risk, organized around five core functions.',
   '2.0', 'NIST', TRUE, '2024-02-26'),

  (@hipaa, 'HIPAA',      'Health Insurance Portability and Accountability Act',
   'US federal law governing the privacy and security of protected health information (PHI).',
   '2013', 'HHS / OCR', FALSE, '2013-03-26'),

  (@pci,   'PCI DSS',    'Payment Card Industry Data Security Standard',
   'Security standard for organizations that handle branded credit cards from major card schemes.',
   '4.0', 'PCI SSC', TRUE, '2022-03-31');

-- ─── SOC 2 Requirements ──────────────────────────────────────────────────────

SET @s_cc1 = UUID(); SET @s_cc2 = UUID(); SET @s_cc3 = UUID(); SET @s_cc4 = UUID();
SET @s_cc5 = UUID(); SET @s_cc6 = UUID(); SET @s_cc7 = UUID(); SET @s_cc8 = UUID();
SET @s_cc9 = UUID(); SET @s_a1  = UUID(); SET @s_pi1 = UUID(); SET @s_c1  = UUID();
SET @s_p1  = UUID();

INSERT INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
  (@s_cc1, @soc2, 'CC1',   'Control Environment',
   'The entity demonstrates a commitment to integrity and ethical values.', 'mandatory', 'Common Criteria', 10),
  (@s_cc2, @soc2, 'CC2',   'Communication and Information',
   'The entity obtains or generates and uses relevant, quality information to support the functioning of internal control.', 'mandatory', 'Common Criteria', 20),
  (@s_cc3, @soc2, 'CC3',   'Risk Assessment',
   'The entity specifies objectives with sufficient clarity to enable the identification and assessment of risks.', 'mandatory', 'Common Criteria', 30),
  (@s_cc4, @soc2, 'CC4',   'Monitoring Activities',
   'The entity selects, develops, and performs ongoing evaluations to ascertain whether the components of internal control are present and functioning.', 'mandatory', 'Common Criteria', 40),
  (@s_cc5, @soc2, 'CC5',   'Control Activities',
   'The entity selects and develops control activities that contribute to the mitigation of risks.', 'mandatory', 'Common Criteria', 50),
  (@s_cc6, @soc2, 'CC6',   'Logical and Physical Access Controls',
   'The entity implements logical access security software, infrastructure, and architectures.', 'mandatory', 'Common Criteria', 60),
  (@s_cc7, @soc2, 'CC7',   'System Operations',
   'The entity detects and monitors for system events and uses detection tools in a timely manner.', 'mandatory', 'Common Criteria', 70),
  (@s_cc8, @soc2, 'CC8',   'Change Management',
   'The entity authorizes, designs, develops, tests, approves, and implements changes to infrastructure.', 'mandatory', 'Common Criteria', 80),
  (@s_cc9, @soc2, 'CC9',   'Risk Mitigation',
   'The entity identifies, selects, and develops risk mitigation activities for risks.', 'mandatory', 'Common Criteria', 90),
  (@s_a1,  @soc2, 'A1',    'Availability',
   'The entity maintains, monitors, and evaluates current processing capacity and use.', 'mandatory', 'Availability', 100),
  (@s_pi1, @soc2, 'PI1',   'Processing Integrity',
   'The entity obtains or generates, uses, and communicates relevant, quality information.', 'mandatory', 'Processing Integrity', 110),
  (@s_c1,  @soc2, 'C1',    'Confidentiality',
   'The entity identifies and maintains confidential information to meet the entity\'s objectives.', 'mandatory', 'Confidentiality', 120),
  (@s_p1,  @soc2, 'P1',    'Privacy',
   'The entity provides notice to data subjects about its privacy practices.', 'mandatory', 'Privacy', 130);

-- ─── ISO 27001:2022 Requirements ─────────────────────────────────────────────

INSERT INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
  (UUID(), @iso, 'A.5',  'Organizational Controls',
   'Policies, roles, responsibilities, segregation of duties, and contact with authorities.', 'mandatory', 'Organization', 10),
  (UUID(), @iso, 'A.6',  'People Controls',
   'Screening, terms of employment, information security awareness, and disciplinary process.', 'mandatory', 'People', 20),
  (UUID(), @iso, 'A.7',  'Physical Controls',
   'Physical security perimeters, entry controls, securing offices and equipment.', 'mandatory', 'Physical', 30),
  (UUID(), @iso, 'A.8',  'Technological Controls',
   'User endpoint devices, privileged access, information access restriction, secure authentication.', 'mandatory', 'Technology', 40),
  (UUID(), @iso, 'A.8.2', 'Privileged Access Rights',
   'Allocation and use of privileged access rights shall be restricted and managed.', 'mandatory', 'Access Control', 41),
  (UUID(), @iso, 'A.8.3', 'Information Access Restriction',
   'Access to information and application system functions shall be restricted in accordance with policy.', 'mandatory', 'Access Control', 42),
  (UUID(), @iso, 'A.8.7', 'Protection Against Malware',
   'Protection against malware shall be implemented and supported by user awareness.', 'mandatory', 'Technology', 50),
  (UUID(), @iso, 'A.8.8', 'Management of Technical Vulnerabilities',
   'Information about technical vulnerabilities shall be obtained and mitigated in a timely fashion.', 'mandatory', 'Technology', 51),
  (UUID(), @iso, 'A.8.16', 'Monitoring Activities',
   'Networks, systems and applications shall be monitored for anomalous behaviour.', 'mandatory', 'Technology', 60),
  (UUID(), @iso, 'A.8.24', 'Use of Cryptography',
   'Rules for effective use of cryptography shall be defined and implemented.', 'mandatory', 'Technology', 70),
  (UUID(), @iso, 'A.8.28', 'Secure Coding',
   'Secure coding principles shall be applied to software development.', 'mandatory', 'Technology', 80),
  (UUID(), @iso, 'Clause 6', 'Risk Assessment and Treatment',
   'The organization shall define and apply an information security risk assessment process.', 'mandatory', 'Risk Management', 90),
  (UUID(), @iso, 'Clause 9', 'Performance Evaluation',
   'Monitoring, measurement, analysis, evaluation, internal audit, and management review.', 'mandatory', 'Governance', 100),
  (UUID(), @iso, 'Clause 10', 'Improvement',
   'Nonconformity, corrective action, and continual improvement.', 'mandatory', 'Governance', 110);

-- ─── NIST CSF 2.0 Requirements ───────────────────────────────────────────────

INSERT INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
  (UUID(), @nist, 'GV',   'Govern',
   'Establish and monitor the organization\'s cybersecurity risk management strategy, expectations, and policy.', 'mandatory', 'Govern', 5),
  (UUID(), @nist, 'ID',   'Identify',
   'Develop an organizational understanding to manage cybersecurity risk to systems, assets, data, and capabilities.', 'mandatory', 'Identify', 10),
  (UUID(), @nist, 'ID.AM', 'Asset Management',
   'The data, personnel, devices, systems, and facilities that enable the organization to achieve business purposes are identified.', 'mandatory', 'Identify', 11),
  (UUID(), @nist, 'ID.RA', 'Risk Assessment',
   'The organization understands the cybersecurity risk to organizational operations, assets, and individuals.', 'mandatory', 'Identify', 12),
  (UUID(), @nist, 'PR',   'Protect',
   'Develop and implement appropriate safeguards to ensure delivery of critical services.', 'mandatory', 'Protect', 20),
  (UUID(), @nist, 'PR.AC', 'Identity Management and Access Control',
   'Access to physical and logical assets and associated facilities is limited to authorized users.', 'mandatory', 'Protect', 21),
  (UUID(), @nist, 'PR.AT', 'Awareness and Training',
   'The organization\'s personnel and partners are provided cybersecurity awareness education.', 'mandatory', 'Protect', 22),
  (UUID(), @nist, 'PR.DS', 'Data Security',
   'Information and records are managed consistent with the organization\'s risk strategy.', 'mandatory', 'Protect', 23),
  (UUID(), @nist, 'PR.IP', 'Information Protection Processes',
   'Security policies, processes, and procedures are maintained and used to manage protection of information systems.', 'mandatory', 'Protect', 24),
  (UUID(), @nist, 'DE',   'Detect',
   'Develop and implement appropriate activities to identify the occurrence of a cybersecurity event.', 'mandatory', 'Detect', 30),
  (UUID(), @nist, 'DE.CM', 'Continuous Monitoring',
   'The information system and assets are monitored at discrete intervals to identify cybersecurity events.', 'mandatory', 'Detect', 31),
  (UUID(), @nist, 'DE.AE', 'Anomalies and Events',
   'Anomalous activity is detected in a timely manner and the potential impact of events is understood.', 'mandatory', 'Detect', 32),
  (UUID(), @nist, 'RS',   'Respond',
   'Develop and implement appropriate activities to take action regarding a detected cybersecurity event.', 'mandatory', 'Respond', 40),
  (UUID(), @nist, 'RS.RP', 'Response Planning',
   'Response processes and procedures are executed and maintained.', 'mandatory', 'Respond', 41),
  (UUID(), @nist, 'RS.CO', 'Communications',
   'Response activities are coordinated with internal and external stakeholders.', 'mandatory', 'Respond', 42),
  (UUID(), @nist, 'RC',   'Recover',
   'Develop and implement appropriate activities to maintain plans for resilience and restore any capabilities or services impaired due to a cybersecurity event.', 'mandatory', 'Recover', 50),
  (UUID(), @nist, 'RC.RP', 'Recovery Planning',
   'Recovery processes and procedures are executed and maintained to ensure timely restoration.', 'mandatory', 'Recover', 51);

-- ─── HIPAA Requirements ──────────────────────────────────────────────────────

INSERT INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
  (UUID(), @hipaa, '164.308(a)(1)', 'Security Management Process',
   'Implement policies and procedures to prevent, detect, contain, and correct security violations.', 'mandatory', 'Administrative', 10),
  (UUID(), @hipaa, '164.308(a)(2)', 'Assigned Security Responsibility',
   'Identify the security official responsible for development and implementation of policies.', 'mandatory', 'Administrative', 20),
  (UUID(), @hipaa, '164.308(a)(3)', 'Workforce Security',
   'Implement policies to ensure all workforce members have appropriate access to ePHI.', 'mandatory', 'Administrative', 30),
  (UUID(), @hipaa, '164.308(a)(4)', 'Information Access Management',
   'Implement policies for authorizing access to ePHI consistent with applicable requirements.', 'mandatory', 'Administrative', 40),
  (UUID(), @hipaa, '164.308(a)(5)', 'Security Awareness and Training',
   'Implement a security awareness and training program for all workforce members.', 'mandatory', 'Administrative', 50),
  (UUID(), @hipaa, '164.308(a)(6)', 'Security Incident Procedures',
   'Implement policies and procedures to address security incidents.', 'mandatory', 'Administrative', 60),
  (UUID(), @hipaa, '164.308(a)(7)', 'Contingency Plan',
   'Establish policies for responding to emergencies that damage systems containing ePHI.', 'mandatory', 'Administrative', 70),
  (UUID(), @hipaa, '164.310(a)(1)', 'Facility Access Controls',
   'Implement policies to limit physical access to electronic information systems.', 'mandatory', 'Physical', 80),
  (UUID(), @hipaa, '164.310(d)(1)', 'Device and Media Controls',
   'Implement policies governing the receipt and removal of hardware and electronic media.', 'mandatory', 'Physical', 90),
  (UUID(), @hipaa, '164.312(a)(1)', 'Access Control',
   'Implement technical policies for electronic information systems that only allow authorized access.', 'mandatory', 'Technical', 100),
  (UUID(), @hipaa, '164.312(b)',    'Audit Controls',
   'Implement hardware, software, and procedural mechanisms to record ePHI activity.', 'mandatory', 'Technical', 110),
  (UUID(), @hipaa, '164.312(c)(1)', 'Integrity',
   'Implement policies to protect ePHI from improper alteration or destruction.', 'mandatory', 'Technical', 120),
  (UUID(), @hipaa, '164.312(e)(1)', 'Transmission Security',
   'Implement technical security measures to guard against unauthorized access to ePHI transmitted over networks.', 'mandatory', 'Technical', 130);

-- ─── PCI DSS 4.0 Requirements ────────────────────────────────────────────────

INSERT INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
  (UUID(), @pci, 'Req 1', 'Network Security Controls',
   'Install and maintain network security controls to protect the cardholder data environment.', 'mandatory', 'Network Security', 10),
  (UUID(), @pci, 'Req 2', 'Secure Configurations',
   'Apply secure configurations to all system components.', 'mandatory', 'System Hardening', 20),
  (UUID(), @pci, 'Req 3', 'Protect Stored Account Data',
   'Protect stored account data using strong cryptography.', 'mandatory', 'Data Protection', 30),
  (UUID(), @pci, 'Req 4', 'Protect Data in Transit',
   'Protect cardholder data with strong cryptography during transmission over open, public networks.', 'mandatory', 'Data Protection', 40),
  (UUID(), @pci, 'Req 5', 'Protect Against Malicious Software',
   'Protect all systems and networks from malicious software.', 'mandatory', 'Endpoint Security', 50),
  (UUID(), @pci, 'Req 6', 'Develop and Maintain Secure Systems',
   'Develop and maintain secure systems and software.', 'mandatory', 'Secure Development', 60),
  (UUID(), @pci, 'Req 7', 'Restrict Access to System Components',
   'Restrict access to system components and cardholder data by business need to know.', 'mandatory', 'Access Control', 70),
  (UUID(), @pci, 'Req 8', 'Identify Users and Authenticate Access',
   'Identify users and authenticate access to system components.', 'mandatory', 'Access Control', 80),
  (UUID(), @pci, 'Req 9', 'Restrict Physical Access',
   'Restrict physical access to cardholder data.', 'mandatory', 'Physical Security', 90),
  (UUID(), @pci, 'Req 10', 'Log and Monitor All Access',
   'Log and monitor all access to system components and cardholder data.', 'mandatory', 'Logging', 100),
  (UUID(), @pci, 'Req 11', 'Test Security of Systems and Networks',
   'Test security of systems and networks regularly.', 'mandatory', 'Security Testing', 110),
  (UUID(), @pci, 'Req 12', 'Support Information Security with Policies',
   'Support information security with organizational policies and programs.', 'mandatory', 'Governance', 120);
