CREATE TABLE term_MeSH
(
  term         VARCHAR(200) NOT NULL,

  PRIMARY KEY(term)
);

CREATE TABLE term_CV
(
  term         VARCHAR(200) NOT NULL,
  PubMed_docs  INT,
  PubMed_block BOOLEAN,
  PMC_docs     INT,
  PMC_block    BOOLEAN,

  PRIMARY KEY(term)
);

CREATE TABLE term_01_C_value
(
  term         VARCHAR(200) NOT NULL,
  PubMed_docs  INT,
  PubMed_block BOOLEAN,
  PMC_docs     INT,
  PMC_block    BOOLEAN,
  C_value      FLOAT,

  PRIMARY KEY(term)
);

CREATE TABLE term_02_C_value
(
  term         VARCHAR(200) NOT NULL,
  PubMed_docs  INT,
  PubMed_block BOOLEAN,
  PMC_docs     INT,
  PMC_block    BOOLEAN,
  C_value      FLOAT,

  PRIMARY KEY(term)
);

CREATE TABLE term_03_C_value
(
  term         VARCHAR(200) NOT NULL,
  PubMed_docs  INT,
  PubMed_block BOOLEAN,
  PMC_docs     INT,
  PMC_block    BOOLEAN,
  C_value      FLOAT,

  PRIMARY KEY(term)
);

CREATE TABLE term_04_C_value
(
  term         VARCHAR(200) NOT NULL,
  PubMed_docs  INT,
  PubMed_block BOOLEAN,
  PMC_docs     INT,
  PMC_block    BOOLEAN,
  C_value      FLOAT,

  PRIMARY KEY(term)
);

CREATE TABLE term_filter
(
  term         VARCHAR(200) NOT NULL,

  PRIMARY KEY(term)
);

CREATE TABLE term_delete
(
  term         VARCHAR(200) NOT NULL,
  why          VARCHAR(200) NOT NULL,

  PRIMARY KEY(term, why)
);

CREATE TABLE term_01_new
(
  term         VARCHAR(200) NOT NULL,
  C_value      FLOAT,

  PRIMARY KEY(term)
);

CREATE TABLE term_02_new
(
  term         VARCHAR(200) NOT NULL,
  C_value      FLOAT,

  PRIMARY KEY(term)
);

CREATE TABLE term_03_new
(
  term         VARCHAR(200) NOT NULL,
  C_value      FLOAT,

  PRIMARY KEY(term)
);

CREATE TABLE term_04_new
(
  term         VARCHAR(200) NOT NULL,
  C_value      FLOAT,

  PRIMARY KEY(term)
);

CREATE TABLE term_new
(
  term         VARCHAR(200) NOT NULL,

  PRIMARY KEY(term)
);

--
-- term to document
--

CREATE TABLE index_PubMed
(
  term    VARCHAR(200) NOT NULL,
  PMID    VARCHAR(50)  NOT NULL,
 
  PRIMARY KEY(term, PMID),

  FOREIGN KEY(term) REFERENCES term_CV(term)
  ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE index_PMC
(
  term    VARCHAR(200) NOT NULL,
  PMCID   VARCHAR(50)  NOT NULL,

  PRIMARY KEY(term, PMCID),

  FOREIGN KEY(term) REFERENCES term_CV(term)
  ON DELETE CASCADE ON UPDATE CASCADE
);

--
-- corpora
--

CREATE TABLE corpus_01_MeSH_PubMed
(
  PMID       VARCHAR(50) NOT NULL,
  download   BOOLEAN,

  PRIMARY KEY(PMID)
);

CREATE TABLE corpus_02_MeSH_PMC
(
  PMCID      VARCHAR(50) NOT NULL,
  PMID       VARCHAR(50),
  download   BOOLEAN,

  PRIMARY KEY(PMCID)
);

CREATE TABLE corpus_03_CV_PubMed
(
  PMID       VARCHAR(50) NOT NULL,
  download   BOOLEAN,

  PRIMARY KEY(PMID)
);

CREATE TABLE corpus_04_CV_PMC
(
  PMCID      VARCHAR(50) NOT NULL,
  PMID       VARCHAR(50),
  download   BOOLEAN,

  PRIMARY KEY(PMCID)
);

CREATE TABLE citation_PubMed
(
  PMID     VARCHAR(50) NOT NULL,
  ref      TEXT,
  authors  TEXT,
  title    TEXT,
  abstract TEXT,
  download BOOLEAN,

  PRIMARY KEY(PMID)
);

CREATE TABLE citation_PMC
(
  PMCID    VARCHAR(50) NOT NULL,
  PMID     VARCHAR(50),
  ref      TEXT,
  authors  TEXT,
  title    TEXT,
  abstract TEXT,
  download BOOLEAN,

  PRIMARY KEY(PMCID)
);