/******************************************************************************************
Getting Messy with SQL Reporting: Preventive Breast Imaging
RCHC Analytics Academy: 5/12/2022
Created by: Nicholas Alonzo (nicholas@diversifyds.org)
Description:
  Execute script to create demo tables in your database
*******************************************************************************************/

CREATE TABLE IF NOT EXISTS demo_breast_imaging (
  "id"            INTEGER PRIMARY KEY,
  "patient_id"    INTEGER,
  "clinic_id"     INTEGER,
  "name"          TEXT,
  "date"          DATE,
  "result"        TEXT,
  "followup_date" DATE
);


CREATE TABLE IF NOT EXISTS demo_clinics (
  "id"   INTEGER PRIMARY KEY,
  "name" TEXT
);


CREATE TABLE IF NOT EXISTS demo_doctors (
  "id"         INTEGER PRIMARY KEY,
  "first_name" TEXT,
  "last_name"  TEXT
);


CREATE TABLE IF NOT EXISTS demo_external_breast_imaging (
  "patient_identifier" CHAR(10),
  "patient_first_name" VARCHAR(10),
  "patient_last_name"  VARCHAR(10),
  "patient_birth_date" CHAR(8),
  "notes"              TEXT
);


CREATE TABLE IF NOT EXISTS demo_patients (
  "id"                 INTEGER PRIMARY KEY,
  "clinic_id"          INTEGER,
  "doctor_id"          INTEGER,
  "mrn"                TEXT,
  "first_name"         TEXT,
  "last_name"          TEXT,
  "date_of_birth"      DATE,
  "birth_sex"          TEXT,
  "preferred_language" TEXT,
  "cell_phone"         TEXT
);