-- enable foreign keys
PRAGMA foreign_keys = true;

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

  -- cpe uri 
  cpe_uri TEXT UNIQUE NOT NULL,

  -- cpe 2.3 formatting string
  cpe23   TEXT UNIQUE NOT NULL
);

-- create titles table
CREATE TABLE cpe_titles (
  -- external CPE id
  cpe_id  INT NOT NULL
          REFERENCES cpes(cpe_id),

  -- language code
  lang    TEXT NOT NULL,

  -- text value
  val     TEXT NOT NULL,

  UNIQUE (cpe_id, lang)
);

-- create titles fts table
CREATE VIRTUAL TABLE cpe_titles_fts USING fts5(title, tokenize = 'porter');

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

-- create titles delete trigger
CREATE TRIGGER tr_cpe_titles_delete BEFORE DELETE ON cpe_titles
  FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN
    DELETE FROM cpe_titles_fts WHERE rowid = OLD.cpe_id;
  END;

-- create refs table
CREATE TABLE cpe_refs (
  -- external CPE id
  cpe_id  INT NOT NULL
          REFERENCES cpes(cpe_id),

  -- url
  href    TEXT NOT NULL,

  -- text description
  val     TEXT NOT NULL
);

-- create refs fts
CREATE VIRTUAL TABLE cpe_refs_fts USING fts5(cpe_id UNINDEXED, href, val);

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

-- create refs delete trigger
CREATE TRIGGER tr_cpe_refs_delete BEFORE DELETE ON cpe_refs
  FOR EACH ROW BEGIN
    DELETE FROM cpe_refs_fts WHERE rowid = OLD.cpe_id;
  END;