-- ------------------------------------------------------------------
-- Title: Sepsis-3 onset
--
-- Adapted from the MIMIC-IV upstream
--   concepts/sepsis/sepsis3.sql
-- and ported to MIMIC-III v1.3 vanilla PostgreSQL.
--
-- Definition (Singer et al., JAMA 2016):
--   Sepsis-3 = SOFA >= 2 AND suspicion of infection,
--   where the SOFA window must overlap the suspected-infection time
--   by at most 48 h before / 24 h after.
--   The "onset time" is the suspected-infection-time of the earliest
--   row that satisfies these criteria for each ICU stay.
--
-- Implicitly assumes baseline SOFA = 0 prior to ICU admission, since
-- we do not have premorbid organ-dysfunction data.
--
-- Dependencies:
--   sepsis/suspicion_of_infection.sql
--   sepsis/sofa_hourly.sql
-- ------------------------------------------------------------------

DROP TABLE IF EXISTS sepsis3;
CREATE TABLE sepsis3 AS

WITH sofa AS
(
  SELECT icustay_id
       , starttime
       , endtime
       , respiration_24hours    AS respiration
       , coagulation_24hours    AS coagulation
       , liver_24hours          AS liver
       , cardiovascular_24hours AS cardiovascular
       , cns_24hours            AS cns
       , renal_24hours          AS renal
       , sofa_24hours           AS sofa_score
  FROM   sofa_hourly
  WHERE  sofa_24hours >= 2
)
, s1 AS
(
  SELECT soi.subject_id
       , soi.icustay_id
       , soi.ab_id
       , soi.antibiotic
       , soi.antibiotic_time
       , soi.culture_time
       , soi.suspected_infection
       , soi.suspected_infection_time
       , soi.specimen
       , soi.positive_culture
       , sofa.starttime
       , sofa.endtime
       , sofa.respiration
       , sofa.coagulation
       , sofa.liver
       , sofa.cardiovascular
       , sofa.cns
       , sofa.renal
       , sofa.sofa_score
       , (sofa.sofa_score >= 2 AND soi.suspected_infection = 1) AS sepsis3
       , ROW_NUMBER() OVER (
           PARTITION BY soi.icustay_id
           ORDER BY     soi.suspected_infection_time
                      , soi.antibiotic_time
                      , soi.culture_time
                      , sofa.endtime
         ) AS rn_sus
  FROM   suspicion_of_infection soi
  INNER  JOIN sofa
         ON  soi.icustay_id = sofa.icustay_id
         AND sofa.endtime  >= DATETIME_SUB(soi.suspected_infection_time, INTERVAL '48' HOUR)
         AND sofa.endtime  <= DATETIME_ADD(soi.suspected_infection_time, INTERVAL '24' HOUR)
  WHERE  soi.icustay_id IS NOT NULL
    AND  soi.suspected_infection_time IS NOT NULL
)
SELECT subject_id
     , icustay_id
     , antibiotic_time
     , culture_time
     , suspected_infection_time
     , endtime AS sofa_time
     , sofa_score
     , respiration, coagulation, liver, cardiovascular, cns, renal
     , sepsis3
FROM   s1
WHERE  rn_sus = 1;

CREATE INDEX IF NOT EXISTS sepsis3_idx ON sepsis3 (icustay_id);