-- ------------------------------------------------------------------
-- Build the Sepsis-3 onset table on a vanilla PostgreSQL MIMIC-III
-- v1.3 DB.
--
-- Usage (assuming you have already restored the MIMIC-III dump into a
-- database called `mimic` and have the base tables in the `mimiciii`
-- schema):
--
--   psql -d mimic -v ON_ERROR_STOP=1 \
--        -c 'SET search_path TO mimiciii, public;' \
--        -f sql/build_sepsis3.sql
--
-- Resulting tables created in the current search_path:
--   echo_data
--   urine_output
--   ventilation_classification
--   ventilation_durations
--   weight_durations
--   {dobutamine,dopamine,epinephrine,norepinephrine}_dose
--   blood_gas_arterial            (all-time PaO2/FiO2)
--   gcs_all                       (all-time GCS)
--   sofa_grid, sofa_vs, sofa_gcs, sofa_bili, sofa_cr, sofa_plt,
--     sofa_pf, sofa_uo, sofa_vaso, sofa_wide, sofa_components
--                                 (intermediate hourly stages,
--                                  retained for inspection)
--   sofa_hourly                   (final hourly SOFA, one row per
--                                  ICU hour, with 24-h rolling MAX)
--   antibiotic                    (filtered antibiotic prescriptions)
--   suspicion_of_infection        (Seymour 2016 abx<>culture pairing)
--   sepsis3                       (final sepsis-3 onset, one row per
--                                  ICU stay)
--
-- Runtime: expect a few hours on a stock single-node PostgreSQL with
-- the default `chartevents` and `labevents` indexes.  Most of the
-- cost is the eight raw-table scans driving the sofa_* staging
-- tables; each stage prints its progress via psql's default ECHO.
-- ------------------------------------------------------------------

\set ON_ERROR_STOP on

-- 0. PL/pgSQL shims for BigQuery-style DATETIME_DIFF / _ADD / _SUB
\i postgres-functions.sql

-- 1. Helpers shared with SAPS-II
\i echo_data.sql
\i fluid_balance/urine_output.sql
\i durations/ventilation_classification.sql
\i durations/ventilation_durations.sql
\i durations/weight_durations.sql

-- 2. Vasopressor dose tables (each merges CareVue + MetaVision)
\i durations/dobutamine_dose.sql
\i durations/dopamine_dose.sql
\i durations/epinephrine_dose.sql
\i durations/norepinephrine_dose.sql

-- 3. All-time pivots feeding hourly SOFA
\i sepsis/blood_gas_arterial.sql
\i sepsis/gcs_all.sql

-- 4. Hourly SOFA pipeline (staged temp tables -> sofa_hourly)
\i sepsis/sofa_hourly.sql

-- 5. Suspicion of infection
\i sepsis/antibiotic.sql
\i sepsis/suspicion_of_infection.sql

-- 6. Final onset table
\i sepsis/sepsis3.sql

\echo 'Sepsis-3 build complete.'
\echo 'Query results with:'
\echo '  SELECT icustay_id, suspected_infection_time, sofa_time, sofa_score, sepsis3'
\echo '    FROM sepsis3 WHERE sepsis3 = TRUE LIMIT 10;'