DROP SCHEMA IF EXISTS visdm;
CREATE DATABASE visdm;

#GENE
CREATE TABLE IF NOT EXISTS visdm.gene (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name      VARCHAR(255),
  chr       VARCHAR(50),
  entrezID  INT,
  ensemblID VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS visdm.genePosition (
  geneID        INT UNSIGNED,
  genomeVersion VARCHAR(50),
  position      VARCHAR(3000),
  FOREIGN KEY (geneID) REFERENCES visdm.gene (id)
);

#Pathway
CREATE TABLE IF NOT EXISTS visdm.pathway (
  id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  commonName       VARCHAR(255),
  keggID           VARCHAR(50),
  reactomeID       VARCHAR(50),
  pathwayCommonsID VARCHAR(50),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS visdm.genePathway (
  geneID    INT UNSIGNED,
  pathwayID INT UNSIGNED,
  FOREIGN KEY (geneID) REFERENCES visdm.gene (id),
  FOREIGN KEY (pathwayID) REFERENCES visdm.pathway (id)
);

#Variant
CREATE TABLE IF NOT EXISTS visdm.variant (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  type VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS visdm.variantPosition (
  variantID     INT UNSIGNED,
  genomeVersion VARCHAR(50),
  position      VARCHAR(3000),
  FOREIGN KEY (variantID) REFERENCES visdm.variant (id)
);

CREATE TABLE IF NOT EXISTS visdm.geneVariant (
  geneID    INT UNSIGNED,
  variantID INT UNSIGNED,
  FOREIGN KEY (geneID) REFERENCES visdm.gene (id),
  FOREIGN KEY (variantID) REFERENCES visdm.variant (id)
);

#Transcript
CREATE TABLE IF NOT EXISTS visdm.geneTranscript (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  geneID          INT UNSIGNED,
  refSeqID        VARCHAR(255),
  refSeqProteinID VARCHAR(255),
  ensemblID       VARCHAR(255),
  uniProt         VARCHAR(255),
  PRIMARY KEY (id),
  FOREIGN KEY (geneID) REFERENCES visdm.gene (id)
);

CREATE TABLE IF NOT EXISTS visdm.geneVariantTranscript (
  geneID              INT UNSIGNED,
  variantID           INT UNSIGNED,
  transcriptID        INT UNSIGNED,
  position            VARCHAR(255),
  domain              VARCHAR(255),
  variantConsequence VARCHAR(255),
  riskScore FLOAT,
  FOREIGN KEY (geneID) REFERENCES visdm.gene (id),
  FOREIGN KEY (variantID) REFERENCES visdm.variant (id),
  FOREIGN KEY (transcriptID) REFERENCES visdm.geneTranscript (id)
);

#Sample
CREATE TABLE IF NOT EXISTS visdm.sample (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tumorPurity FLOAT,
  tnm         VARCHAR(20),
  tag         SET ('primary', 'relapse'),
  PRIMARY KEY (id)
);

#Cancer
CREATE TABLE IF NOT EXISTS visdm.cancerType (
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name   VARCHAR(255),
  umlsID VARCHAR(255),
  hpoID  VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS visdm.cancerVariant (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  variantID      INT UNSIGNED,
  typeID         INT UNSIGNED,
  biomarkerClass VARCHAR(255),
  clinicalRelevanceLevel VARCHAR(255),
  PRIMARY KEY (id),
  FOREIGN KEY (variantID) REFERENCES visdm.variant (id),
  FOREIGN KEY (typeID) REFERENCES visdm.cancerType (id)
);

CREATE TABLE IF NOT EXISTS visdm.cancerVariantSample (
  cancerVariantID INT UNSIGNED,
  sampleID        INT UNSIGNED,
  somaticClass    VARCHAR(50),
  alleleFrequency FLOAT,
  FOREIGN KEY (cancerVariantID) REFERENCES visdm.cancerVariant (id),
  FOREIGN KEY (sampleID) REFERENCES visdm.sample (id)
);

#Drug
CREATE TABLE IF NOT EXISTS visdm.drug (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name       VARCHAR(255),
  drugBankID VARCHAR(255),
  pharmGKBID VARCHAR(255),
  fdaID      TEXT,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS visdm.drugMechanism (
  drugID    INT UNSIGNED,
  mechanism TEXT,
  FOREIGN KEY (drugID) REFERENCES visdm.drug (id)
);

CREATE TABLE IF NOT EXISTS visdm.cancerVariantDrugEffect (
  cancerVariantID  INT UNSIGNED,
  drugID           INT UNSIGNED,
  effect           TEXT,
  evidenceLevel    VARCHAR(10),
  evidenceSubLevel VARCHAR(10),
  FOREIGN KEY (cancerVariantID) REFERENCES visdm.cancerVariant (id),
  FOREIGN KEY (drugID) REFERENCES visdm.drug (id)
);

CREATE TABLE IF NOT EXISTS visdm.cancerVariantDrug (
  cancerVariantID INT UNSIGNED,
  drugID          INT UNSIGNED,
  FOREIGN KEY (cancerVariantID) REFERENCES visdm.cancerVariant (id),
  FOREIGN KEY (drugID) REFERENCES visdm.drug (id)
);

#Sources
CREATE TABLE IF NOT EXISTS visdm.dataElement (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  class         VARCHAR(255),
  rowID         BIGINT,
  attributeName VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS visdm.dataSource (
  elementID  INT UNSIGNED,
  name       VARCHAR(255),
  dataSet    VARCHAR(255),
  lastUpdate TIMESTAMP,
  FOREIGN KEY (elementID) REFERENCES visdm.dataElement (id)
);

CREATE TABLE IF NOT EXISTS visdm.reference (
  elementID   INT UNSIGNED,
  type        VARCHAR(255),
  referenceID VARCHAR(255),
  FOREIGN KEY (elementID) REFERENCES visdm.dataElement (id)
);

#  CONCAt('E', LPAD(CONV(elementID, 10, 36), 8, '0'));