-- enable foreign keys
PRAGMA foreign_keys = true;

-- set database version
PRAGMA user_version = 314159;

-- create cpes table
CREATE TABLE cpes (
  -- cpe unique ID
  cpe_id            INTEGER PRIMARY KEY,

  -- cpe uri
  cpe_uri           TEXT UNIQUE NOT NULL CHECK (
                      LENGTH(cpe_uri) > 5 AND
                      cpe_uri LIKE 'cpe:/%'
                    ),

  -- cpe 2.3 formatting string
  cpe23             TEXT UNIQUE NOT NULL CHECK (
                      LENGTH(cpe23) > 28 AND
                      cpe23 LIKE 'cpe:2.3:%:%:%:%:%:%:%:%:%:%:%'
                    )
);

-- create all fts table (cross join across all
-- titles and references, populated by triggers)
CREATE VIRTUAL TABLE cpe_fts_all USING fts5(
  -- parent CPE id
  cpe_id UNINDEXED,

  -- title
  title,

  -- reference URL
  href,

  -- reference name
  val,

  -- use porter stemming
  tokenize = 'porter'
);

-- create titles table
CREATE TABLE cpe_titles (
  -- title primary key
  cpe_title_id      INTEGER PRIMARY KEY,

  -- external CPE id
  cpe_id            INT NOT NULL
                    REFERENCES cpes(cpe_id),

  -- language code
  lang              TEXT NOT NULL
                    CHECK (LENGTH(lang) >= 2),

  -- text value
  val               TEXT NOT NULL
                    CHECK (LENGTH(val) > 0),

  -- prevent duplicate titles of the same name
  UNIQUE (cpe_id, lang)
);

-- create titles fts table
CREATE VIRTUAL TABLE cpe_fts_titles USING fts5(
  -- parent CPE id
  cpe_id UNINDEXED,

  -- title
  title,

  -- use porter stemming
  tokenize = 'porter'
);

-- create titles all fts insert trigger
CREATE TRIGGER tr_cpe_titles_insert_fts_all AFTER INSERT ON cpe_titles
  FOR EACH ROW WHEN (NEW.lang = 'en-US') BEGIN
    INSERT INTO cpe_fts_all(cpe_id, title, href, val)
      SELECT NEW.cpe_id,
             NEW.val,
             b.href,
             b.val

        FROM cpes a
        JOIN cpe_refs b
          ON (b.cpe_id = a.cpe_id)

       WHERE a.cpe_id = NEW.cpe_id;
  END;

-- create titles delete all fts trigger
CREATE TRIGGER tr_cpe_titles_delete_fts_all BEFORE DELETE ON cpe_titles
  FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN
    DELETE FROM cpe_fts_all
     WHERE cpe_id = OLD.cpe_id
       AND title = OLD.val;
  END;

-- create titles all fts insert trigger
CREATE TRIGGER tr_cpe_titles_insert_fts_titles AFTER INSERT ON cpe_titles
  FOR EACH ROW WHEN (NEW.lang = 'en-US') BEGIN
    INSERT INTO cpe_fts_titles(cpe_id, title) VALUES (NEW.cpe_id, NEW.val);
  END;

-- create titles delete titles fts trigger
CREATE TRIGGER tr_cpe_titles_delete_fts_titles BEFORE DELETE ON cpe_titles
  FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN
    DELETE FROM cpe_fts_titles
     WHERE cpe_id = OLD.cpe_id
       AND title = OLD.val;
  END;

-- create refs table
CREATE TABLE cpe_refs (
  -- reference unique id
  cpe_ref_id        INTEGER PRIMARY KEY,

  -- external CPE id
  cpe_id            INT NOT NULL
                    REFERENCES cpes(cpe_id),

  -- url
  href              TEXT NOT NULL CHECK (
                      LENGTH(href) > 0 AND
                      (href LIKE 'http://%' OR href LIKE 'https://%')
                    ),

  -- text description
  val               TEXT NOT NULL
                    CHECK (LENGTH(val) > 0)
);

-- create refs fts table
CREATE VIRTUAL TABLE cpe_fts_refs USING fts5(
  -- parent CPE id
  cpe_id UNINDEXED,

  -- reference URL
  href,

  -- reference name
  val,

  -- use porter stemming
  tokenize = 'porter'
);

-- create refs insert trigger for all fts
CREATE TRIGGER tr_cpe_refs_insert_fts_all AFTER INSERT ON cpe_refs
  FOR EACH ROW BEGIN
    INSERT INTO cpe_fts_all(cpe_id, title, href, val)
      SELECT NEW.cpe_id,
             b.val,
             NEW.href,
             NEW.val

        FROM cpes a
        JOIN cpe_titles b
          ON (b.cpe_id = a.cpe_id)

       WHERE a.cpe_id = NEW.cpe_id
         AND b.lang = 'en-US';
  END;

-- create refs delete trigger for all fts
CREATE TRIGGER tr_cpe_refs_delete_fts_all BEFORE DELETE ON cpe_refs
  FOR EACH ROW BEGIN
    DELETE FROM cpe_fts_all
     WHERE cpe_id = OLD.cpe_id
       AND href = OLD.lang
       AND val = OLD.val;
  END;

-- create refs insert trigger for refs fts
CREATE TRIGGER tr_cpe_refs_insert_fts_refs AFTER INSERT ON cpe_refs
  FOR EACH ROW BEGIN
    INSERT INTO cpe_fts_refs(cpe_id, href, val) VALUES (NEW.cpe_id, NEW.href, NEW.val);
  END;

-- create refs delete trigger for refs fts
CREATE TRIGGER tr_cpe_refs_delete_fts_refs BEFORE DELETE ON cpe_refs
  FOR EACH ROW BEGIN
    DELETE FROM cpe_fts_refs
     WHERE cpe_id = OLD.cpe_id
       AND href = OLD.lang
       AND val = OLD.val;
  END;

--
-- cpe matches table
--
CREATE TABLE cpe_matches (
  -- cpe match unique ID
  cpe_match_id  INTEGER PRIMARY KEY,

  -- modern CPE 2.3 formatted string
  cpe23             TEXT UNIQUE NOT NULL CHECK (
                      LENGTH(cpe23) > 28 AND
                      cpe23 LIKE 'cpe:2.3:%:%:%:%:%:%:%:%:%:%:%'
                    ),

  -- old CPE 2.2 URI
  cpe22_uri         TEXT NOT NULL
);

-- cpe match vulnerabilities
CREATE TABLE cpe_match_vulnerables (
  cpe_match_id      INTEGER PRIMARY KEY,

  -- true if this match is vulnerable, and false
  -- otherwise
  is_vulnerable     BOOLEAN NOT NULL,

  -- add foreign key constraint
  FOREIGN KEY (cpe_match_id)
    REFERENCES cpe_matches(cpe_match_id)
);

--
-- cpe match version minimums (e.g. version starts)
--
CREATE TABLE cpe_match_version_mins (
  cpe_match_id      INTEGER PRIMARY KEY,

  -- valid values
  --
  -- * true: boundary includes this version (e.g. it was specified by
  --   versionStartIncluding)
  -- * false: boundary excludes this version (e.g., it was specified by
  --   versionStartExcluding)
  is_inclusive      BOOLEAN NOT NULL,

  -- minimum version
  min_version       TEXT NOT NULL
                    CHECK (LENGTH(min_version) > 0),

  -- add foreign key constraint
  FOREIGN KEY (cpe_match_id)
    REFERENCES cpe_matches(cpe_match_id)
);

--
-- cpe match version maximums (e.g. version ends)
--
CREATE TABLE cpe_match_version_maxs (
  -- cpe match unique id
  cpe_match_id      INTEGER PRIMARY KEY,

  -- valid values
  --
  -- * true: boundary includes this version (e.g. it was specified by
  --   versionEndIncluding)
  -- * false: boundary excludes this version (e.g., it was specified by
  --   versionEndExcluding)
  is_inclusive      BOOLEAN NOT NULL,

  -- maximum version
  max_version       TEXT NOT NULL
                    CHECK (LENGTH(max_version) > 0),

  -- add foreign key constraint
  FOREIGN KEY (cpe_match_id)
    REFERENCES cpe_matches(cpe_match_id)
);

-- map of matching
CREATE TABLE cpe_match_cpes (
  -- CPE match ID
  cpe_match_id      INTEGER NOT NULL
                    REFERENCES cpe_matches(cpe_match_id),

  -- CPE ID
  cpe_id            INTEGER NOT NULL
                    REFERENCES cpes(cpe_id),

  -- nullable cpe22 URI
  -- normally we would want this in a separate table, but
  -- we don't care that much about this value
  cpe22_uri         TEXT NOT NULL,

  PRIMARY KEY (cpe_match_id, cpe_id)
);

--
-- Descriptions of CVEs and CVE problem types.
--
CREATE TABLE descriptions (
  -- description unique ID
  desc_id           INTEGER PRIMARY KEY,

  -- description language
  lang              TEXT NOT NULL
                    CHECK (LENGTH(lang) >= 2),

  -- description text
  value             TEXT NOT NULL CHECK (
                      LENGTH(value) > 0
                    )
);

--
-- CVE feeds.
--
CREATE TABLE feeds (
  -- feed unique ID
  feed_id           INTEGER PRIMARY KEY,

  -- time that feed was added
  created_at        TIMESTAMP WITH TIME ZONE NOT NULL
                    DEFAULT CURRENT_TIMESTAMP,

  -- data timestamp
  data_time         TIMESTAMP WITH TIME ZONE NOT NULL
);

--
-- CVE feed items.
--
CREATE TABLE feed_items (
  item_id           INTEGER PRIMARY KEY,

  -- feed that this CVE belongs to
  feed_id           INTEGER NOT NULL
                    REFERENCES feeds(feed_id),

  -- published timestamp
  published_at      TIMESTAMP WITH TIME ZONE NOT NULL,

  -- last modified timestamp
  modified_at       TIMESTAMP WITH TIME ZONE NOT NULL
);

-- create index
CREATE INDEX in_feed_items_feed_id ON feed_items(feed_id);

--
-- CVE assigners
--
CREATE TABLE assigners (
  -- assigner unique ID
  assigner_id       INTEGER PRIMARY KEY,

  -- assigner value
  assigner          TEXT UNIQUE NOT NULL CHECK (
                      LENGTH(assigner) > 0
                    )
);

--
-- CVEs
--
-- NOTE: this combines the concept of an "item" and a "cve" from the
-- source feed.  maybe we should split them up again?
--
CREATE TABLE cves (
  -- CVE unique ID
  cve_id            INTEGER PRIMARY KEY,

  -- feed item that this CVE belongs to
  item_id           INTEGER NOT NULL
                    REFERENCES feed_items(item_id),

  -- year of NVD CVE ID, (e.g. the "XXXX" in "CVE-XXXX-YYYY")
  cve_year          SMALLINT NOT NULL CHECK (
                      cve_year BETWEEN 1999 AND 2126
                    ),

  -- number of NVD CVE ID, (e.g. the "YYYY" in "CVE-XXXX-YYYY")
  cve_num           SMALLINT NOT NULL CHECK (
                      -- there is no upper bound, but let's pick
                      -- something sane
                      cve_num BETWEEN 1 AND 33554432
                    ),

  -- assigner ID
  assigner_id       INTEGER NOT NULL
                    REFERENCES assigners(assigner_id)
);

-- create index
CREATE INDEX in_cves_item_id ON cves(item_id);

--
-- NVD CVE IDs (e.g. "CVE-YYYY-NNNN").
--
CREATE VIEW cve_nvd_ids AS
  SELECT cve_id,
         printf('CVE-%04d-%04d', cve_year, cve_num) AS nvd_id
    FROM cves;

--
-- CVE descriptions
--
CREATE TABLE cve_descriptions (
  -- CVE unique ID
  cve_id            INTEGER NOT NULL
                    REFERENCES cves(cve_id),

  -- description unique ID
  desc_id           INTEGER NOT NULL
                    REFERENCES descriptions(desc_id),

  PRIMARY KEY (cve_id, desc_id)
);

-- create index
CREATE INDEX in_cve_descriptions_cve_id ON cve_descriptions(cve_id);

--
-- CVE problem types
--
CREATE TABLE cve_problems (
  -- problem type unique ID
  pt_id             INTEGER PRIMARY KEY,

  -- CVE that this problem type belongs to
  cve_id            INTEGER NOT NULL
                    REFERENCES cves(cve_id)
);

-- create index
CREATE INDEX in_cve_problems_cve_id ON cve_problems(cve_id);

--
-- CVE problem type descriptions
--
CREATE TABLE cve_problem_descriptions (
  -- problem type that this description belongs to
  pt_id             INTEGER NOT NULL
                    REFERENCES cve_problems(pt_id),

  -- description unique ID
  desc_id           INTEGER NOT NULL
                    REFERENCES descriptions(desc_id),

  PRIMARY KEY (pt_id, desc_id)
);

--
-- CVE references
--
CREATE TABLE cve_refs (
  -- cve reference unique ID
  cve_ref_id        INTEGER PRIMARY KEY,

  -- CVE unique ID
  cve_id            INTEGER NOT NULL
                    REFERENCES cves(cve_id),

  -- reference URL
  url               TEXT NOT NULL CHECK (
                      -- FIXME: not sure about length here
                      LENGTH(url) BETWEEN 7 AND 1024
                    ),

  -- reference name
  name              TEXT NOT NULL CHECK (
                      -- FIXME: not sure about length here
                      LENGTH(name) BETWEEN 1 AND 1024
                    ),

  -- reference name
  source            TEXT NOT NULL
);

-- create index
CREATE INDEX in_cve_refs_cve_id ON cve_refs(cve_id);

--
-- cve reference tags
--
CREATE TABLE cve_ref_tags (
  cve_ref_id        INTEGER NOT NULL
                    REFERENCES cve_refs(cve_ref_id),

  -- tag
  tag               TEXT NOT NULL CHECK (
                      -- FIXME: not sure about maximum length
                      LENGTH(tag) BETWEEN 1 AND 128
                    ),

  -- prevent duplicate tags
  UNIQUE (cve_ref_id, tag)
);

-- create index
CREATE INDEX in_cve_ref_tags_cve_ref_id ON cve_ref_tags(cve_ref_id);

--
-- CVSS severities.
--
CREATE TABLE severities (
  -- severity unique ID
  severity_id       INTEGER PRIMARY KEY,

  -- severity name
  name              TEXT UNIQUE NOT NULL CHECK (
                      -- limit to know severities
                      name IN ('NONE', 'LOW', 'MEDIUM', 'HIGH', 'CRITICAL')
                    ),

  -- severity sort order, from low to high
  sort              SMALLINT UNIQUE NOT NULL
);

-- add severities
INSERT INTO severities(severity_id, name, sort) VALUES
  (1, 'NONE', 1),
  (2, 'LOW', 2),
  (3, 'MEDIUM', 3),
  (4, 'HIGH', 4),
  (5, 'CRITICAL', 5);

--
-- item cvss v2 impacts
--
CREATE TABLE item_cvss_v2_impacts (
  -- feed item that this impact belongs to
  item_id           INTEGER NOT NULL
                    REFERENCES feed_items(item_id),

  -- cvss v2 version
  -- FIXME: don't need this, check during import
  -- v2_version        TEXT NOT NULL CHECK (
  --                     LENGTH(v2_version) > 0
  --                   ),

  -- cvss v2 vector
  v2_vector         TEXT NOT NULL CHECK (
                      -- TODO: enforce this
                      LENGTH(v2_vector) > 0
                    ),

  -- cvss v2 base score
  -- (NOTE: multiplied by 10 and stored as a tinyint)
  v2_base_score     TINYINT NOT NULL CHECK (
                      v2_base_score BETWEEN 0 AND 100
                    ),

  -- cvss v2 severity
  v2_severity_id    INTEGER NOT NULL
                    REFERENCES severities(severity_id),

  -- cvss v2 impact score
  -- (NOTE: multiplied by 10 and stored as a tinyint)
  v2_impact_score   TINYINT NOT NULL CHECK (
                      v2_impact_score BETWEEN 0 AND 100
                    ),

  -- cvss v2 exploitability score
  -- (NOTE: multiplied by 10 and stored as a tinyint)
  v2_expl_score     TINYINT NOT NULL CHECK (
                      v2_expl_score BETWEEN 0 AND 100
                    ),

  -- cvss v2 insufficient info
  v2_insufficient_info  BOOLEAN NOT NULL,

  -- cvss v2 insufficient info
  v2_obtain_all_priv    BOOLEAN NOT NULL,

  -- cvss v2 obtain user privileges
  v2_obtain_user_priv   BOOLEAN NOT NULL,

  -- cvss v2 obtain other privileges
  v2_obtain_other_priv  BOOLEAN NOT NULL,

  -- cvss v2 user interaction required?
  v2_ui_req             BOOLEAN NOT NULL
);

-- create indices
CREATE INDEX in_item_cvss_v2_impacts_item_id ON item_cvss_v2_impacts(item_id);
CREATE INDEX in_item_cvss_v2_impacts_v2_severity_id ON item_cvss_v2_impacts(v2_severity_id);

--
-- item cvss v3 impacts
--
CREATE TABLE item_cvss_v3_impacts (
  -- feed item that this impact belongs to
  item_id           INTEGER NOT NULL
                    REFERENCES feed_items(item_id),

  -- FIXME: don't need this, check during import
  -- v3_version        TEXT NOT NULL CHECK (
  --                     LENGTH(v3_version) > 0
  --                   ),

  -- cvss v3 vector
  v3_vector         TEXT NOT NULL CHECK (
                      -- TODO: enforce this
                      LENGTH(v3_vector) > 0 AND
                      v3_vector LIKE 'CVSS:3._/%'
                    ),

  -- cvss v3 base score
  -- (NOTE: multiplied by 10 and stored as a tinyint)
  v3_base_score     TINYINT NOT NULL CHECK (
                      v3_base_score BETWEEN 0 AND 100
                    ),

  -- cvss v3 base severity
  v3_severity_id    INTEGER NOT NULL
                    REFERENCES severities(severity_id),

  -- cvss v3 exploitability score
  -- (NOTE: multiplied by 10 and stored as a tinyint)
  v3_expl_score     TINYINT NOT NULL CHECK (
                      v3_expl_score BETWEEN 0 AND 100
                    ),

  -- cvss v3 impact score
  -- (NOTE: multiplied by 10 and stored as a tinyint)
  v3_impact_score   TINYINT NOT NULL CHECK (
                      v3_impact_score BETWEEN 0 AND 100
                    )
);

-- create indices
CREATE INDEX in_item_cvss_v3_impacts_item_id ON item_cvss_v3_impacts(item_id);
CREATE INDEX in_item_cvss_v3_impacts_v3_severity_id ON item_cvss_v3_impacts(v3_severity_id);

--
-- create cve all fts table
--
-- join across all of the following:
-- * NVD CVE ID
-- * assigner
-- * cve description(s)
-- * cve problem type description(s)
-- * cve reference(s) (url, name, source, and tags)
-- * cvss v2 severity name
-- * cvss v3 severity name
--
-- this virtual table is populated and cleared by triggers on the
-- corresponding cve tables (see triggers below).
--
CREATE VIRTUAL TABLE cve_fts_all USING fts5(
  -- parent item ID
  item_id UNINDEXED,

  -- parent CVE id
  cve_id UNINDEXED,

  -- cve description ID
  cve_desc_id UNINDEXED,

  -- problem type ID
  pt_desc_id UNINDEXED,

  -- cve ref ID
  cve_ref_id UNINDEXED,

  -- v2 severity ID
  v2_severity_id UNINDEXED,

  -- v3 severity ID
  v3_severity_id UNINDEXED,

  -- NVD CVE ID (e.g. CVE-XXXX-YYYY)
  nvd_cve_id,

  -- CVE assigner
  assigner,

  -- CVE description
  cve_desc,

  -- problem type description
  pt_desc,

  -- reference URL
  ref_url,

  -- reference name
  ref_name,

  -- reference source
  ref_source,

  -- reference tag
  ref_tag,

  -- cvss v2 severity
  v2_severity,

  -- cvss v3 severity
  v3_severity,

  -- use porter stemming
  tokenize = 'porter'
);

--
-- Dummy table to trigger cve_fts refreshes.
--
CREATE TABLE cve_fts_refresh (
  created_at        TIMESTAMP WITH TIME ZONE NOT NULL
                    DEFAULT CURRENT_TIMESTAMP
);

-- create before insert trigger on cve_fts_refresh
CREATE TRIGGER tr_cve_fts_refresh_before_insert
  BEFORE INSERT ON cve_fts_refresh
  FOR EACH ROW BEGIN
    DELETE FROM cve_fts_all;
  END;

-- create insert trigger on item_cvss_v3_impacts
--
-- Notes:
-- * test sizes:
--   - no index: ~31M
--   - per-table triggers: ~320M
--   - full refresh trigger: ~120M
--
-- TODO:
-- * use group_concat() to concatenate tags
-- * remove all IDs except cve_id
-- * remove severities
--
CREATE TRIGGER tr_cve_fts_refresh_after_insert
  AFTER INSERT ON cve_fts_refresh
  FOR EACH ROW BEGIN
    INSERT INTO cve_fts_all(
      item_id,
      cve_id,
      cve_desc_id,
      pt_desc_id,
      cve_ref_id,
      v2_severity_id,
      v3_severity_id,

      nvd_cve_id,
      assigner,
      cve_desc,
      pt_desc,
      ref_url,
      ref_name,
      ref_source,
      ref_tag,
      v2_severity,
      v3_severity
    )
      SELECT a.item_id, -- item_id
             a.cve_id, -- cve_id
             d.desc_id, -- cve_desc_id
             g.desc_id, -- pt_desc_id
             h.cve_ref_id, -- cve_ref_id
             j.v2_severity_id, -- v2_severity_id
             l.v3_severity_id, -- v3_severity_id
             n.nvd_id AS nvd_cve_id, -- nvd_cve_id
             b.assigner, -- assigner
             COALESCE(d.value, ''), -- cve_desc
             COALESCE(g.value, ''), -- pt_desc
             COALESCE(h.url, ''), -- ref_url
             COALESCE(h.name, ''), -- ref_name
             COALESCE(h.source, ''), -- ref_source
             COALESCE(i.tag, ''), -- ref_tag
             COALESCE(k.name, ''), -- v2_severity
             COALESCE(m.name, '') -- v3_severity

        FROM cves a
        JOIN assigners b
          ON (b.assigner_id = a.assigner_id)
        LEFT JOIN cve_descriptions c
          ON (c.cve_id = a.cve_id)
        LEFT JOIN descriptions d
          ON (d.desc_id = c.desc_id)
        LEFT JOIN cve_problems e
          ON (e.cve_id = a.cve_id)
        LEFT JOIN cve_problem_descriptions f
          ON (f.pt_id = e.pt_id)
        LEFT JOIN descriptions g
          ON (g.desc_id = f.desc_id)
        LEFT JOIN cve_refs h
          ON (h.cve_id = a.cve_id)
        LEFT JOIN cve_ref_tags i
          ON (i.cve_ref_id = h.cve_ref_id)
        LEFT JOIN item_cvss_v2_impacts j
          ON (j.item_id = a.item_id)
        LEFT JOIN severities k
          ON (k.severity_id = j.v2_severity_id)
        LEFT JOIN item_cvss_v3_impacts l
          ON (l.item_id = a.item_id)
        LEFT JOIN severities m
          ON (m.severity_id = l.v3_severity_id)
        JOIN cve_nvd_ids n
          ON (n.cve_id = a.cve_id);
  END;