admin / Strike

-- =====================================================================
-- StrikeXi — Seed data (FULL NCSC CAF taxonomy)
-- 4 Objectives, all 14 Principles, a weighted question per principle,
-- weighted answer options, and principle-specific remediation guidance.
-- (Admin user is created by the backend on startup using bcrypt.)
-- =====================================================================

-- ---------- Objectives ----------
INSERT INTO caf_objectives (id, title, description, sort_order) VALUES
 ('A', 'Managing security risk',             'Appropriate organisational structures, policies, processes and procedures to understand, assess and systematically manage security risks to essential functions.', 1),
 ('B', 'Protecting against cyber attack',    'Proportionate security measures in place to protect essential functions and systems from cyber attack.', 2),
 ('C', 'Detecting cyber security events',    'Capabilities to ensure security defences remain effective and to detect cyber security events affecting essential functions.', 3),
 ('D', 'Minimising the impact of incidents', 'Capabilities to minimise the adverse impact of a cyber security incident on essential functions, including restoration of those functions.', 4)
ON CONFLICT (id) DO NOTHING;

-- ---------- Principles (all 14) ----------
INSERT INTO caf_principles (id, objective_id, title, description, sort_order) VALUES
 -- Objective A
 ('A1', 'A', 'Governance',                              'Appropriate organisational structures, policies, processes and procedures to understand, assess and manage security risk.', 1),
 ('A2', 'A', 'Risk management',                         'Identification, assessment and understanding of security risks; establishment of an overall organisational approach to risk management.', 2),
 ('A3', 'A', 'Asset management',                        'Everything required to deliver, maintain or support essential functions is determined and understood.', 3),
 ('A4', 'A', 'Supply chain',                            'Understanding and management of security risks to essential functions arising from dependencies on external suppliers.', 4),
 -- Objective B
 ('B1', 'B', 'Service protection policies and processes','Defined, implemented, communicated and enforced policies and processes that direct overall security.', 5),
 ('B2', 'B', 'Identity and access control',             'Understanding, documenting and controlling access to networks and information systems supporting essential functions.', 6),
 ('B3', 'B', 'Data security',                           'Protection of stored or electronically transmitted data from actions that may cause adverse impact.', 7),
 ('B4', 'B', 'System security',                         'Protection of network and information systems and technology from cyber attack.', 8),
 ('B5', 'B', 'Resilient networks and systems',          'Building resilience against cyber attack and system failure into the design, implementation and operation.', 9),
 ('B6', 'B', 'Staff awareness and training',            'Appropriately supporting staff to ensure they make a positive contribution to cyber security.', 10),
 -- Objective C
 ('C1', 'C', 'Security monitoring',                     'Monitoring to detect potential security problems and to track the ongoing effectiveness of protective measures.', 11),
 ('C2', 'C', 'Proactive security event discovery',      'Detecting anomalous events relevant to potential security compromise that other measures would not detect.', 12),
 -- Objective D
 ('D1', 'D', 'Response and recovery planning',          'Well-defined and tested incident management processes in place to ensure continuity of essential functions.', 13),
 ('D2', 'D', 'Lessons learned',                         'When an incident occurs, taking steps to understand its root causes and to ensure appropriate remediation.', 14)
ON CONFLICT (id) DO NOTHING;

-- ---------- One representative weighted question per principle ----------
-- 'guidance' supplies the CAF context: why the question is asked, what the
-- principle expects, and what 'good' (Achieved) looks like.
INSERT INTO questions (principle_id, code, text, guidance, weight, sort_order) VALUES
 ('A1', 'A1-Q1',
  'Is there a board-level individual accountable for cyber security, with clear governance structures and policies that are regularly reviewed?',
  'CAF Objective A is about managing security risk. Principle A1 (Governance) expects senior-level direction and oversight of cyber security, with appropriate policies, defined responsibilities and a risk appetite agreed by leadership. ''Achieved'' means a named board-level owner is accountable, governance structures are documented and decisions are reviewed regularly; ''Partially'' means some governance exists but is informal or not consistently reviewed.',
  2.0, 1),
 ('A2', 'A2-Q1',
  'Do you maintain a documented cyber security risk assessment process and a risk register that is reviewed at planned intervals?',
  'Principle A2 (Risk management) requires you to identify, analyse and understand security risks to your essential functions using a repeatable, documented process. ''Achieved'' means risks are systematically assessed, recorded in a maintained register with owners, and reviewed at planned intervals; ''Not achieved'' means risk is managed ad hoc or undocumented.',
  1.5, 1),
 ('A3', 'A3-Q1',
  'Do you maintain a complete and current inventory of the assets (systems, data, services) required to deliver your essential functions?',
  'Principle A3 (Asset management) expects you to know everything needed to deliver, maintain or support your essential functions. You cannot protect what you do not know you have. ''Achieved'' means an authoritative, current inventory of hardware, software, data and dependencies exists with ownership and classification.',
  1.5, 1),
 ('A4', 'A4-Q1',
  'Do you identify, assess and manage the cyber security risks introduced by your suppliers and third parties?',
  'Principle A4 (Supply chain) recognises that dependencies on external suppliers can introduce risk to essential functions. ''Achieved'' means critical suppliers are identified, security expectations are set contractually, and third-party risk is assessed and monitored over time, not just at onboarding.',
  1.5, 1),
 ('B1', 'B1-Q1',
  'Are security policies and processes defined, approved, communicated and enforced across the organisation?',
  'CAF Objective B is about protecting against cyber attack. Principle B1 (Service protection policies and processes) expects security to be directed by clear, approved policies and operational processes that staff actually follow. ''Achieved'' means policies are documented, communicated, enforced and kept current, with exceptions managed formally.',
  1.5, 1),
 ('B2', 'B2-Q1',
  'Is multi-factor authentication enforced and are access rights granted on a least-privilege basis and reviewed regularly?',
  'Principle B2 (Identity and access control) requires you to understand, document and control who and what can access systems supporting essential functions. ''Achieved'' means strong (ideally phishing-resistant) MFA is enforced, access follows least privilege with joiner/mover/leaver processes, and rights are recertified periodically.',
  2.0, 1),
 ('B3', 'B3-Q1',
  'Is sensitive data protected at rest and in transit using appropriate encryption and access controls?',
  'Principle B3 (Data security) expects data that would cause harm if compromised to be protected wherever it is stored or transmitted. ''Achieved'' means sensitive data is classified, encrypted at rest and in transit with managed keys, access is need-to-know, and secure disposal is in place.',
  2.0, 1),
 ('B4', 'B4-Q1',
  'Are systems securely configured, hardened and patched against known vulnerabilities within defined timeframes?',
  'Principle B4 (System security) is about protecting technology from cyber attack through secure design, configuration and maintenance. ''Achieved'' means systems use hardened baselines, unnecessary services are removed, vulnerabilities are scanned for, and critical patches are applied within defined SLAs.',
  2.0, 1),
 ('B5', 'B5-Q1',
  'Are networks and systems designed for resilience, with tested backups and redundancy for essential functions?',
  'Principle B5 (Resilient networks and systems) expects resilience to attack and failure to be built into design and operation. ''Achieved'' means networks are segregated, redundancy exists for essential functions, and backups are taken, protected (e.g. offline/immutable) and their restoration is tested.',
  1.5, 1),
 ('B6', 'B6-Q1',
  'Do staff receive role-appropriate cyber security awareness training on a recurring basis?',
  'Principle B6 (Staff awareness and training) recognises that people are central to security. ''Achieved'' means staff receive recurring, role-appropriate awareness training (including phishing resilience), completion is tracked, and security-positive behaviour is supported.',
  1.0, 1),
 ('C1', 'C1-Q1',
  'Do you collect and centrally monitor security logs from key systems to detect potential security events?',
  'CAF Objective C is about detecting cyber security events. Principle C1 (Security monitoring) expects monitoring capable of detecting potential security problems and confirming controls are effective. ''Achieved'' means logs from key systems are centrally collected, retained, and reviewed against defined detection use cases with timely alerting.',
  1.5, 1),
 ('C2', 'C2-Q1',
  'Do you proactively hunt for and investigate anomalous activity that routine monitoring might miss?',
  'Principle C2 (Proactive security event discovery) goes beyond rule-based monitoring to find threats that would otherwise evade detection. ''Achieved'' means you proactively hunt for anomalies, use threat intelligence, and investigate suspicious activity that standard alerts would not surface.',
  1.5, 1),
 ('D1', 'D1-Q1',
  'Do you have a documented and tested incident response and recovery plan covering your essential functions?',
  'CAF Objective D is about minimising the impact of incidents. Principle D1 (Response and recovery planning) expects well-defined, exercised plans to maintain and restore essential functions. ''Achieved'' means response and recovery plans exist, define roles and escalation, and are tested (e.g. tabletop exercises) at least annually.',
  2.0, 1),
 ('D2', 'D2-Q1',
  'After an incident, do you conduct root-cause analysis and feed lessons learned back into your controls?',
  'Principle D2 (Lessons learned) expects the organisation to learn from incidents and near-misses. ''Achieved'' means structured post-incident reviews and root-cause analysis are performed, and the resulting improvements are fed back into controls and risk treatment.',
  1.0, 1)
ON CONFLICT (code) DO NOTHING;

-- ---------- Weighted answer options (standard CAF 3-point scale) ----------
-- Achieved = 1.0, Partially achieved = 0.5, Not achieved = 0.0
INSERT INTO answer_options (question_id, label, score, sort_order)
SELECT q.id, v.label, v.score, v.so
FROM questions q
CROSS JOIN (VALUES
    ('Achieved',            1.000, 1),
    ('Partially achieved',  0.500, 2),
    ('Not achieved',        0.000, 3)
) AS v(label, score, so);

-- ---------- Remediation suggestion library (principle-specific) ----------
-- Detail text gives concrete mitigation steps and references the principle.
INSERT INTO remediation_suggestions (id, title, detail, priority, effort) VALUES
 ('a1000000-0000-0000-0000-000000000001',
  'Establish cyber governance and board accountability (Principle A1: Governance)',
  'Appoint a named board-level owner (e.g. SIRO) accountable for cyber risk. Approve a cyber security policy set, define reporting lines and risk appetite, and review governance at least annually. This directly addresses CAF Principle A1 (Governance).', 1, 'medium'),
 ('a2000000-0000-0000-0000-000000000002',
  'Implement a structured risk management process (Principle A2: Risk management)',
  'Adopt a documented risk assessment methodology, create and maintain a risk register with named owners and scoring, and review it quarterly. Ensure risks to essential functions are explicitly tracked. Addresses CAF Principle A2 (Risk management).', 1, 'medium'),
 ('a3000000-0000-0000-0000-000000000003',
  'Build and maintain an asset inventory (Principle A3: Asset management)',
  'Establish an authoritative inventory of hardware, software, data and services underpinning essential functions, with ownership and classification, and keep it current through automated discovery where possible. Addresses CAF Principle A3 (Asset management).', 2, 'medium'),
 ('a4000000-0000-0000-0000-000000000004',
  'Manage supply chain security risk (Principle A4: Supply chain)',
  'Identify critical suppliers, embed security requirements into contracts, assess supplier cyber posture, and monitor third-party risk on an ongoing basis. Addresses CAF Principle A4 (Supply chain).', 2, 'high'),
 ('b1000000-0000-0000-0000-000000000005',
  'Define and enforce security policies and processes (Principle B1)',
  'Document, approve and communicate security policies and operational processes; ensure they are enforced and reviewed. Track exceptions formally. Addresses CAF Principle B1 (Service protection policies and processes).', 2, 'medium'),
 ('b2000000-0000-0000-0000-000000000006',
  'Strengthen identity and access control (Principle B2)',
  'Enforce phishing-resistant MFA for all privileged and remote access, apply least-privilege RBAC, implement joiner/mover/leaver processes and conduct periodic access recertification. Maintain break-glass procedures. Addresses CAF Principle B2 (Identity and access control).', 1, 'medium'),
 ('b3000000-0000-0000-0000-000000000007',
  'Protect data at rest and in transit (Principle B3: Data security)',
  'Classify sensitive data, encrypt it at rest and in transit with managed keys, restrict access on a need-to-know basis, and apply secure disposal. Addresses CAF Principle B3 (Data security).', 1, 'medium'),
 ('b4000000-0000-0000-0000-000000000008',
  'Harden and patch systems (Principle B4: System security)',
  'Apply secure baseline configurations, remove unnecessary services, run vulnerability scanning, and patch critical vulnerabilities within defined SLAs (e.g. 14 days). Addresses CAF Principle B4 (System security).', 1, 'high'),
 ('b5000000-0000-0000-0000-000000000009',
  'Engineer network and system resilience (Principle B5)',
  'Design for redundancy, segregate networks, take regular backups stored offline/immutably, and test restoration so essential functions can survive disruption. Addresses CAF Principle B5 (Resilient networks and systems).', 2, 'high'),
 ('b6000000-0000-0000-0000-00000000000a',
  'Deliver staff security awareness and training (Principle B6)',
  'Run recurring, role-based security awareness training and phishing simulations, and track completion. Addresses CAF Principle B6 (Staff awareness and training).', 3, 'low'),
 ('c1000000-0000-0000-0000-00000000000b',
  'Implement centralised security monitoring (Principle C1)',
  'Aggregate logs from key systems into a SIEM/log platform, define use cases and alerting, set retention, and review alerts within defined timeframes. Addresses CAF Principle C1 (Security monitoring).', 2, 'high'),
 ('c2000000-0000-0000-0000-00000000000c',
  'Establish proactive threat discovery (Principle C2)',
  'Introduce threat hunting, anomaly detection and threat intelligence to surface activity routine monitoring would miss. Addresses CAF Principle C2 (Proactive security event discovery).', 3, 'high'),
 ('d1000000-0000-0000-0000-00000000000d',
  'Create and test incident response and recovery plans (Principle D1)',
  'Document incident response and recovery plans covering essential functions, define roles and escalation, and exercise them at least annually (e.g. tabletop tests). Addresses CAF Principle D1 (Response and recovery planning).', 1, 'medium'),
 ('d2000000-0000-0000-0000-00000000000e',
  'Embed a lessons-learned process (Principle D2)',
  'After incidents, perform structured root-cause analysis and post-incident reviews, and feed improvements back into controls and risk treatment. Addresses CAF Principle D2 (Lessons learned).', 3, 'low')
ON CONFLICT (id) DO NOTHING;

-- ---------- Map each principle to its remediation ----------
-- threshold: if the principle's normalised score (0-1) is BELOW this, queue it.
INSERT INTO remediation_mappings (principle_id, remediation_id, threshold) VALUES
 ('A1', 'a1000000-0000-0000-0000-000000000001', 0.700),
 ('A2', 'a2000000-0000-0000-0000-000000000002', 0.700),
 ('A3', 'a3000000-0000-0000-0000-000000000003', 0.700),
 ('A4', 'a4000000-0000-0000-0000-000000000004', 0.700),
 ('B1', 'b1000000-0000-0000-0000-000000000005', 0.700),
 ('B2', 'b2000000-0000-0000-0000-000000000006', 0.700),
 ('B3', 'b3000000-0000-0000-0000-000000000007', 0.700),
 ('B4', 'b4000000-0000-0000-0000-000000000008', 0.700),
 ('B5', 'b5000000-0000-0000-0000-000000000009', 0.700),
 ('B6', 'b6000000-0000-0000-0000-00000000000a', 0.700),
 ('C1', 'c1000000-0000-0000-0000-00000000000b', 0.700),
 ('C2', 'c2000000-0000-0000-0000-00000000000c', 0.700),
 ('D1', 'd1000000-0000-0000-0000-00000000000d', 0.700),
 ('D2', 'd2000000-0000-0000-0000-00000000000e', 0.700)
ON CONFLICT (principle_id, remediation_id) DO NOTHING;