jesus

CREATE TABLE patients (
  mrn VARCHAR NOT NULL UNIQUE PRIMARY KEY,
  first_name VARCHAR NOT NULL,
  first_name VARCHAR NOT NULL,
  maiden_name VARCHAR,
  last_name VARCHAR,
  birth_date VARCHAR NOT NULL UNIQUE,
  birth_date VARCHAR NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  street VARCHAR NOT NULL,
  city VARCHAR NOT NULL,
  zip VARCHAR NOT NULL,
  race VARCHAR,
  ethnicity VARCHAR,
  preferred_language VARCHAR DEFAULT 'EN',
);

CREATE TABLE treatment (
  tid VARCHAR NOT NULL UNIQUE PRIMARY KEY,
  patient_mrn VARCHAR NOT NULL,
  type VARCHAR NOT NULL,
  description VARCHAR,
  adverse_effect VARCHAR,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (patient_mrn) REFERENCES patients (mrn) ON DELETE CASCADE
);

CREATE TABLE substance (
  drug_name VARCHAR NOT NULL UNIQUE PRIMARY KEY,
  patient_mrn VARCHAR NOT NULL,
  code VARCHAR NOT NULL,
  coding_system VARCHAR NOT NULL,
  amount INT,
  adverse_effect VARCHAR,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (patient_mrn) REFERENCES patients (mrn) ON DELETE CASCADE
);

CREATE TABLE case_manager (
  cm_id VARCHAR NOT NULL UNIQUE PRIMARY KEY,
  first_name VARCHAR NOT NULL,
  last_name VARCHAR NOT NULL,
  gender VARCHAR NOT NULL,
  birth_year INT NOT NULL,
  FOREIGN KEY (patient_mrn) REFERENCES patients (mrn) ON DELETE CASCADE
);