-- ------------------------------------------------------------------
-- Build the SAPS-II severity score 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_sapsii.sql
--
-- Resulting tables created in the current search_path:
--   urine_output                       (not used by SAPS-II directly,
--                                       included for completeness)
--   ventilation_classification
--   ventilation_durations
--   blood_gas_first_day
--   blood_gas_first_day_arterial
--   gcs_first_day
--   labs_first_day
--   urine_output_first_day
--   vitals_first_day
--   sapsii   <-- final score table
-- ------------------------------------------------------------------

\set ON_ERROR_STOP on

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

-- 1. Optional helper view (not required by SAPS-II, but useful and harmless)
\i fluid_balance/urine_output.sql

-- 2. Ventilation: classification first, then durations
\i durations/ventilation_classification.sql
\i durations/ventilation_durations.sql

-- 3. First-day derived views (blood_gas_first_day must precede the arterial one)
\i firstday/blood_gas_first_day.sql
\i firstday/blood_gas_first_day_arterial.sql
\i firstday/gcs_first_day.sql
\i firstday/labs_first_day.sql
\i firstday/urine_output_first_day.sql
\i firstday/vitals_first_day.sql

-- 4. The score itself
\i severityscores/sapsii.sql

\echo 'SAPS-II build complete. Query results with:  SELECT * FROM sapsii LIMIT 10;'