schema.sql

                         ------> Schema
-- Synthetic Data of mimic_demo
-- Patients
CREATE TABLE patients (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL UNIQUE,
    gender TEXT,
    dob TEXT,
    dod TEXT,
    dod_hosp TEXT,
    dod_ssn TEXT,
    expire_flag INTEGER
);

-- Admissions
CREATE TABLE admissions (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER NOT NULL UNIQUE,
    admittime TEXT,
    dischtime TEXT,
    deathtime TEXT,
    admission_type TEXT,
    admission_location TEXT,
    discharge_location TEXT,
    insurance TEXT,
    language TEXT,
    religion TEXT,
    marital_status TEXT,
    ethnicity TEXT,
    edregtime TEXT,
    edouttime TEXT,
    diagnosis TEXT,
    hospital_expire_flag INTEGER,
    has_chartevents_data INTEGER
);

-- ICU Stays
CREATE TABLE icustays (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER NOT NULL,
    icustay_id INTEGER NOT NULL UNIQUE,
    dbsource TEXT,
    first_careunit TEXT,
    last_careunit TEXT,
    first_wardid INTEGER,
    last_wardid INTEGER,
    intime TEXT,
    outtime TEXT,
    los REAL
);

-- Diagnoses ICD
CREATE TABLE diagnoses_icd (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER NOT NULL,
    seq_num INTEGER,
    icd9_code TEXT
);

-- Procedures ICD
CREATE TABLE procedures_icd (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER NOT NULL,
    seq_num INTEGER,
    icd9_code INTEGER
);

-- D_ICD_Diagnoses
CREATE TABLE d_icd_diagnoses (
    row_id INTEGER PRIMARY KEY,
    icd9_code TEXT,
    short_title TEXT,
    long_title TEXT
);

-- D_ICD_Procedures
CREATE TABLE d_icd_procedures (
    row_id INTEGER PRIMARY KEY,
    icd9_code INTEGER,
    short_title TEXT,
    long_title TEXT
);

-- D_Items
CREATE TABLE d_items (
    row_id INTEGER PRIMARY KEY,
    itemid INTEGER UNIQUE,
    label TEXT,
    abbreviation TEXT,
    dbsource TEXT,
    linksto TEXT,
    category TEXT,
    unitname TEXT,
    param_type TEXT,
    conceptid REAL
);

-- D_LabItems
CREATE TABLE d_labitems (
    row_id INTEGER PRIMARY KEY,
    itemid INTEGER UNIQUE,
    label TEXT,
    fluid TEXT,
    category TEXT,
    loinc_code TEXT
);

-- Chartevents
CREATE TABLE chartevents (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER,
    icustay_id REAL,
    itemid INTEGER,
    charttime TEXT,
    storetime TEXT,
    cgid INTEGER,
    value TEXT,
    valuenum REAL,
    valueuom TEXT,
    warning REAL,
    error REAL,
    resultstatus TEXT,
    stopped TEXT
);

-- CPTEVENTS
CREATE TABLE cptevents (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER,
    costcenter TEXT,
    chartdate TEXT,
    cpt_cd INTEGER,
    cpt_number INTEGER,
    cpt_suffix REAL,
    ticket_id_seq REAL,
    sectionheader TEXT,
    subsectionheader TEXT,
    description TEXT
);

-- D_CPT
CREATE TABLE d_cpt (
    row_id INTEGER PRIMARY KEY,
    category INTEGER,
    sectionrange TEXT,
    sectionheader TEXT,
    subsectionrange TEXT,
    subsectionheader TEXT,
    codesuffix TEXT,
    mincodeinsubsection INTEGER,
    maxcodeinsubsection INTEGER
);

-- Prescriptions
CREATE TABLE prescriptions (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER NOT NULL,
    icustay_id REAL,
    startdate TEXT,
    enddate TEXT,
    drug_type TEXT,
    drug TEXT,
    drug_name_poe TEXT,
    drug_name_generic TEXT,
    formulary_drug_cd TEXT,
    gsn REAL,
    ndc REAL,
    prod_strength TEXT,
    dose_val_rx TEXT,
    dose_unit_rx TEXT,
    form_val_disp TEXT,
    form_unit_disp TEXT,
    route TEXT
);

-- Inputevents_CV
CREATE TABLE inputevents_cv (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER NOT NULL,
    icustay_id INTEGER,
    charttime TEXT,
    itemid INTEGER,
    amount REAL,
    amountuom TEXT,
    rate REAL,
    rateuom TEXT,
    storetime TEXT,
    cgid REAL,
    orderid INTEGER,
    linkorderid INTEGER,
    stopped TEXT,
    newbottle REAL,
    originalamount REAL,
    originalamountuom TEXT,
    originalroute TEXT,
    originalrate REAL,
    originalrateuom TEXT,
    originalsite TEXT
);

-- Inputevents_MV
CREATE TABLE inputevents_mv (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER NOT NULL,
    icustay_id INTEGER,
    starttime TEXT,
    endtime TEXT,
    itemid INTEGER,
    amount REAL,
    amountuom TEXT,
    rate REAL,
    rateuom TEXT,
    storetime TEXT,
    cgid INTEGER,
    orderid INTEGER,
    linkorderid INTEGER,
    ordercategoryname TEXT,
    secondaryordercategoryname TEXT,
    ordercomponenttypedescription TEXT,
    ordercategorydescription TEXT,
    patientweight REAL,
    totalamount REAL,
    totalamountuom TEXT,
    isopenbag INTEGER,
    continueinnextdept INTEGER,
    cancelreason INTEGER,
    statusdescription TEXT,
    comments_editedby TEXT,
    comments_canceledby TEXT,
    comments_date TEXT,
    originalamount REAL,
    originalrate REAL
);

-- Outputevents
CREATE TABLE outputevents (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER,
    icustay_id REAL,
    charttime TEXT,
    itemid INTEGER,
    value REAL,
    valueuom TEXT,
    storetime TEXT,
    cgid INTEGER,
    stopped REAL,
    newbottle REAL,
    iserror REAL
);

-- Labevents
CREATE TABLE labevents (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id REAL,
    itemid INTEGER,
    charttime TEXT,
    value TEXT,
    valuenum REAL,
    valueuom TEXT,
    flag TEXT
);

-- Microbiologyevents
CREATE TABLE microbiologyevents (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER,
    chartdate TEXT,
    charttime TEXT,
    spec_itemid INTEGER,
    spec_type_desc TEXT,
    org_itemid REAL,
    org_name TEXT,
    isolate_num REAL,
    ab_itemid REAL,
    ab_name TEXT,
    dilution_text TEXT,
    dilution_comparison TEXT,
    dilution_value REAL,
    interpretation TEXT
);

-- Procedureevents_MV
CREATE TABLE procedureevents_mv (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER,
    icustay_id INTEGER,
    starttime TEXT,
    endtime TEXT,
    itemid INTEGER,
    value INTEGER,
    valueuom TEXT,
    location TEXT,
    locationcategory TEXT,
    storetime TEXT,
    cgid INTEGER,
    orderid INTEGER,
    linkorderid INTEGER,
    ordercategoryname TEXT,
    secondaryordercategoryname REAL,
    ordercategorydescription TEXT,
    isopenbag INTEGER,
    continueinnextdept INTEGER,
    cancelreason INTEGER,
    statusdescription TEXT,
    comments_editedby TEXT,
    comments_canceledby TEXT,
    comments_date TEXT
);

-- Drgcodes
CREATE TABLE drgcodes (
    row_id INTEGER PRIMARY KEY,
    subject_id INTEGER NOT NULL,
    hadm_id INTEGER,
    drg_type TEXT,
    drg_code INTEGER,
    description TEXT,
    drg_severity INTEGER,
    drg_mortality INTEGER
);

                    ------> Views
-- What are the patients whole summaries?
-- How long they live how many time they spend in hospital?
-- The view that represent the whole patient summary
CREATE VIEW patient_summary AS
SELECT
    ad.row_id,
    ad.subject_id,
    pt.gender,
    ad.hospital_expire_flag,
    pt.expire_flag,

    -- Age capped at 89 (due to de-identification)
    CASE
        WHEN ROUND((julianday(ad.admittime) - julianday(pt.dob)) / 365, 2) > 89
        THEN 89
        ELSE ROUND((julianday(ad.admittime) - julianday(pt.dob)) / 365, 2)
    END AS age,

    -- Lifespan: cap unrealistic values, ignore if dod is null
    CASE
        WHEN pt.dod IS NULL THEN NULL
        WHEN ROUND((julianday(pt.dod) - julianday(pt.dob)) / 365, 2) > 120 THEN 120
        WHEN ROUND((julianday(pt.dod) - julianday(pt.dob)) / 365, 2) < 0 THEN NULL
        ELSE ROUND((julianday(pt.dod) - julianday(pt.dob)) / 365, 2)
    END AS life_span,

    -- LOS in hours and days (with COALESCE)
    COALESCE(ROUND((julianday(ad.dischtime) - julianday(ad.admittime)) * 24, 2), 0) AS spend_hours_in_hosp,
    COALESCE(ROUND((julianday(ad.dischtime) - julianday(ad.admittime)), 2), 0) AS spend_days_in_hosp

FROM admissions AS ad
JOIN patients AS pt
ON ad.subject_id = pt.subject_id;

                        ---------> Indexes
-- 1. Index by ICU stay (most common join key)
CREATE INDEX idx_outputevents_icustay
ON outputevents (icustay_id);

-- 2. Index by hospital admission (sometimes used for joins)
CREATE INDEX idx_outputevents_hadm
ON outputevents (hadm_id);

-- 3. Index by patient subject (for patient-level analysis)
CREATE INDEX idx_outputevents_subject
ON outputevents (subject_id);

-- 4. Index by charttime (for time-series queries)
CREATE INDEX idx_outputevents_charttime
ON outputevents (charttime);

-- 5. Index by valueuom
CREATE INDEX value_index
ON chartevents (valueuom);

--6. Index by valuenum
CREATE INDEX value_index_num
ON chartevents (valuenum);