admin / Strike

-- =====================================================================
-- StrikeXi — Cyber Maturity Assessment Platform (NCSC CAF)
-- Relational schema: users, CAF taxonomy, questions, assessments,
-- answers, remediation mappings, queued remediation actions, audit log
-- =====================================================================

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ---------- Users & Authentication ----------
CREATE TABLE IF NOT EXISTS users (
    id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username             TEXT UNIQUE NOT NULL,
    password_hash        TEXT NOT NULL,
    full_name            TEXT,
    role                 TEXT NOT NULL DEFAULT 'assessor',  -- user | assessor | admin
    is_active            BOOLEAN NOT NULL DEFAULT TRUE,
    must_change_password BOOLEAN NOT NULL DEFAULT FALSE,
    mfa_secret           TEXT,                  -- base32 TOTP secret (null when no MFA)
    mfa_enabled          BOOLEAN NOT NULL DEFAULT FALSE,
    mfa_required         BOOLEAN NOT NULL DEFAULT FALSE,  -- admin-enforced MFA
    -- V2 self-service signup profile fields
    first_name           TEXT,
    surname              TEXT,
    company_name         TEXT,
    email                TEXT,
    contact_number       TEXT,
    -- How the account was created: 'self_service' (public signup) or 'admin'
    signup_origin        TEXT NOT NULL DEFAULT 'admin',
    -- True until the user passes their very first successful login (drives
    -- first-login MFA enforcement for self-service signups).
    first_login_pending  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_login           TIMESTAMPTZ
);

-- Idempotent migration for databases created before user-admin/MFA support.
ALTER TABLE users ADD COLUMN IF NOT EXISTS must_change_password BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS mfa_secret   TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS mfa_enabled  BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS mfa_required BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS updated_at   TIMESTAMPTZ NOT NULL DEFAULT now();
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login   TIMESTAMPTZ;
-- V2 idempotent migrations
ALTER TABLE users ADD COLUMN IF NOT EXISTS first_name          TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS surname             TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS company_name        TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS email               TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS contact_number      TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS signup_origin       TEXT NOT NULL DEFAULT 'admin';
ALTER TABLE users ADD COLUMN IF NOT EXISTS first_login_pending BOOLEAN NOT NULL DEFAULT FALSE;

-- ---------- CAF Taxonomy: Objectives -> Principles ----------
-- Objectives: A (Managing security risk), B (Protecting against cyber attack),
--             C (Detecting cyber security events), D (Minimising impact)
CREATE TABLE IF NOT EXISTS caf_objectives (
    id          TEXT PRIMARY KEY,            -- 'A','B','C','D'
    title       TEXT NOT NULL,
    description TEXT,
    sort_order  INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS caf_principles (
    id           TEXT PRIMARY KEY,           -- 'A1','A2','B1' ...
    objective_id TEXT NOT NULL REFERENCES caf_objectives(id),
    title        TEXT NOT NULL,
    description  TEXT,
    sort_order   INT NOT NULL DEFAULT 0
);

-- ---------- Questions & weighted answer options ----------
CREATE TABLE IF NOT EXISTS questions (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    principle_id  TEXT NOT NULL REFERENCES caf_principles(id),
    code          TEXT UNIQUE NOT NULL,      -- e.g. 'A1.a-Q1'
    text          TEXT NOT NULL,
    -- CAF context/narrative explaining the basis of the question and what 'good' looks like
    guidance      TEXT,
    -- weight = relative importance of this question within its principle
    weight        NUMERIC(5,2) NOT NULL DEFAULT 1.0,
    sort_order    INT NOT NULL DEFAULT 0
);

-- Each answer option carries a score 0.0 - 1.0 (fraction of full marks)
CREATE TABLE IF NOT EXISTS answer_options (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    question_id UUID NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
    label       TEXT NOT NULL,               -- 'Achieved','Partially','Not achieved'
    score       NUMERIC(4,3) NOT NULL,       -- 1.000 / 0.500 / 0.000
    sort_order  INT NOT NULL DEFAULT 0
);

-- ---------- Remediation library + mapping ----------
CREATE TABLE IF NOT EXISTS remediation_suggestions (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title       TEXT NOT NULL,
    detail      TEXT NOT NULL,
    -- priority drives roadmap ordering (1 = do first)
    priority    INT NOT NULL DEFAULT 3,
    effort      TEXT NOT NULL DEFAULT 'medium'  -- low | medium | high
);

-- Map a principle (and a "fail" threshold) to a remediation.
-- When a principle's normalised score < threshold, the remediation is queued.
CREATE TABLE IF NOT EXISTS remediation_mappings (
    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    principle_id   TEXT NOT NULL REFERENCES caf_principles(id),
    remediation_id UUID NOT NULL REFERENCES remediation_suggestions(id),
    threshold      NUMERIC(4,3) NOT NULL DEFAULT 0.700,  -- below => triggered
    UNIQUE (principle_id, remediation_id)
);

-- ---------- Assessments ----------
CREATE TABLE IF NOT EXISTS assessments (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id           UUID NOT NULL REFERENCES users(id),
    organisation_name TEXT NOT NULL,
    assessment_date   DATE NOT NULL DEFAULT CURRENT_DATE,
    status            TEXT NOT NULL DEFAULT 'in_progress', -- in_progress | completed
    overall_score     NUMERIC(5,2),        -- 0-100, computed on completion
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS assessment_answers (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assessment_id UUID NOT NULL REFERENCES assessments(id) ON DELETE CASCADE,
    question_id   UUID NOT NULL REFERENCES questions(id),
    option_id     UUID NOT NULL REFERENCES answer_options(id),
    UNIQUE (assessment_id, question_id)
);

-- Snapshot of per-principle scores when an assessment is completed
CREATE TABLE IF NOT EXISTS assessment_principle_scores (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assessment_id UUID NOT NULL REFERENCES assessments(id) ON DELETE CASCADE,
    principle_id  TEXT NOT NULL REFERENCES caf_principles(id),
    score         NUMERIC(5,2) NOT NULL,    -- 0-100
    UNIQUE (assessment_id, principle_id)
);

-- Snapshot of per-objective scores when an assessment is completed
CREATE TABLE IF NOT EXISTS assessment_objective_scores (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assessment_id UUID NOT NULL REFERENCES assessments(id) ON DELETE CASCADE,
    objective_id  TEXT NOT NULL REFERENCES caf_objectives(id),
    score         NUMERIC(5,2) NOT NULL,    -- 0-100
    UNIQUE (assessment_id, objective_id)
);

-- Queued corrective actions (the future ITSM push target)
CREATE TABLE IF NOT EXISTS remediation_actions (
    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assessment_id  UUID NOT NULL REFERENCES assessments(id) ON DELETE CASCADE,
    principle_id   TEXT NOT NULL REFERENCES caf_principles(id),
    remediation_id UUID NOT NULL REFERENCES remediation_suggestions(id),
    principle_score NUMERIC(5,2) NOT NULL,
    status         TEXT NOT NULL DEFAULT 'queued', -- queued | exported | done
    -- Future ITSM integration fields (Jira / ServiceNow)
    external_ref   TEXT,
    external_system TEXT,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- Audit log ----------
CREATE TABLE IF NOT EXISTS audit_log (
    id         BIGSERIAL PRIMARY KEY,
    ts         TIMESTAMPTZ NOT NULL DEFAULT now(),
    username   TEXT,                  -- may be null on failed login of unknown user
    action     TEXT NOT NULL,         -- LOGIN_SUCCESS, LOGIN_FAILED, ASSESSMENT_STARTED ...
    detail     TEXT,
    ip_address TEXT
);

-- Helpful indexes
CREATE INDEX IF NOT EXISTS idx_assessments_user ON assessments(user_id);
CREATE INDEX IF NOT EXISTS idx_answers_assessment ON assessment_answers(assessment_id);
CREATE INDEX IF NOT EXISTS idx_actions_assessment ON remediation_actions(assessment_id);
CREATE INDEX IF NOT EXISTS idx_audit_ts ON audit_log(ts DESC);