-- Tables for storing index file counts
-- Keyed names for various closed-class types
CREATE TABLE scheme(
  id    INTEGER PRIMARY KEY NOT NULL, --  AUTOINCREMENT
  name  VARCHAR(16)
);
INSERT INTO scheme(name) VALUES ('http'),('https');

CREATE TABLE mpt(
  id    INTEGER PRIMARY KEY NOT NULL, --  AUTOINCREMENT
  name  VARCHAR(11)
);
INSERT INTO mpt(name) VALUES
  ('text'),('image'),('audio'),('video'),('application');

CREATE TABLE mst(name)(
  id    INTEGER PRIMARY KEY NOT NULL, --  AUTOINCREMENT
  name  VARCHAR(11)
);

CREATE TABLE mediaType(
  id INTEGER PRIMARY KEY NOT NULL, --  AUTOINCREMENT
  pt INTEGER NOT NULL,
  st INTEGER NOT NULL,
  FOREIGN KEY(pt) REFERENCES mpt(id),
  FOREIGN KEY(st) REFERENCES mst(id)
);

CREATE TABLE lang(
  id    INTEGER PRIMARY KEY NOT NULL, --  AUTOINCREMENT
  code  CHAR(3) NOT NULL,
  fname VARCHAR
);

CREATE TABLE langs(
  id    INTEGER PRIMARY KEY NOT NULL, --  AUTOINCREMENT
  count INTEGER NOT NULL,
  primary INTEGER NOT NULL,
  secondary INTEGER,
  tertiary INTEGER,
  FOREIGN KEY(primary) REFERENCES lang(id),
  FOREIGN KEY(secondary) REFERENCES lang(id),
  FOREIGN KEY(tertiary) REFERENCES lang(id)
);

CREATE TABLE props(
  index     INTEGER NOT NULL, 
  ftype CHAR(1), -- 'r', 'w', 'c'
  scheme INTEGER NOT NULL,
  ct INTEGER NOT NULL,
  sniffed INTEGER NOT NULL,
  langs INTEGER,
  FOREIGN KEY(scheme) REFERENCES scheme(id),
  FOREIGN KEY(ct) REFERENCES mediaType(id),
  FOREIGN KEY(sniffed) REFERENCES mediaType(id),
  FOREIGN KEY(langs) REFERENCES langs(id),
);