-- ------------------------------------------------------------------
-- Title: Suspicion of Infection
--
-- Adapted from the MIMIC-IV upstream
--   concepts/sepsis/suspicion_of_infection.sql
-- and ported to MIMIC-III v1.3 vanilla PostgreSQL.
--
-- Definition (from the original Sepsis-3 paper, Seymour 2016):
-- a patient is "suspected of infection" if a culture and an
-- antibiotic are ordered close in time:
--   - culture <= 72 h before antibiotic, OR
--   - culture <= 24 h after antibiotic.
-- The antibiotic time is taken as the suspected-infection time when
-- a culture comes second; the culture time when it comes first.
--
-- PORT NOTES:
--   1. ID column is `icustay_id`, not `stay_id`.
--   2. MIMIC-III has no `micro_specimen_id`; specimens are identified
--      by the tuple (subject_id, hadm_id, chartdate, charttime,
--      spec_itemid, spec_type_desc) and we deduplicate organism
--      rows by aggregating with that tuple.
--   3. MIMIC-III `prescriptions.startdate` is DATE-precision only.
--      Consequently `antibiotic_time` always lands on midnight; the
--      MIMIC-IV branches that compare to `me.charttime` still work
--      (DATE auto-casts to TIMESTAMP at 00:00) but give day-level
--      onset precision.
-- ------------------------------------------------------------------

DROP TABLE IF EXISTS suspicion_of_infection;
CREATE TABLE suspicion_of_infection AS

WITH ab_tbl AS
(
  SELECT abx.subject_id
       , abx.hadm_id
       , abx.icustay_id
       , abx.antibiotic
       , CAST(abx.starttime AS TIMESTAMP) AS antibiotic_time
       , CAST(abx.starttime AS DATE)      AS antibiotic_date
       , CAST(abx.stoptime  AS TIMESTAMP) AS antibiotic_stoptime
       , ROW_NUMBER() OVER (
           PARTITION BY abx.subject_id
           ORDER BY     abx.starttime, abx.stoptime, abx.antibiotic
         ) AS ab_id
  FROM   antibiotic abx
)
, me AS
(
  SELECT subject_id, hadm_id
       , spec_itemid
       , spec_type_desc
       , MAX(CAST(chartdate AS DATE)) AS chartdate
       , MAX(charttime)               AS charttime
       , MAX(CASE WHEN org_name IS NOT NULL AND org_name != ''
                  THEN 1 ELSE 0 END)  AS positiveculture
  FROM   microbiologyevents
  GROUP  BY subject_id, hadm_id, chartdate, charttime,
           spec_itemid, spec_type_desc
)
, me_then_ab AS
(
  SELECT ab_tbl.subject_id
       , ab_tbl.hadm_id
       , ab_tbl.icustay_id
       , ab_tbl.ab_id
       , COALESCE(me72.charttime,
                  CAST(me72.chartdate AS TIMESTAMP)) AS last72_charttime
       , me72.positiveculture                       AS last72_positiveculture
       , me72.spec_type_desc                        AS last72_specimen
       , ROW_NUMBER() OVER (
           PARTITION BY ab_tbl.subject_id, ab_tbl.ab_id
           ORDER BY     me72.chartdate, me72.charttime NULLS LAST
         ) AS micro_seq
  FROM   ab_tbl
  LEFT   JOIN me me72
         ON  ab_tbl.subject_id = me72.subject_id
         AND (
              (
                me72.charttime IS NOT NULL
                AND ab_tbl.antibiotic_time >  me72.charttime
                AND ab_tbl.antibiotic_time <= DATETIME_ADD(me72.charttime, INTERVAL '72' HOUR)
              )
           OR (
                me72.charttime IS NULL
                AND ab_tbl.antibiotic_date >= me72.chartdate
                AND ab_tbl.antibiotic_date <= me72.chartdate + INTERVAL '3 day'
              )
         )
)
, ab_then_me AS
(
  SELECT ab_tbl.subject_id
       , ab_tbl.hadm_id
       , ab_tbl.icustay_id
       , ab_tbl.ab_id
       , COALESCE(me24.charttime,
                  CAST(me24.chartdate AS TIMESTAMP)) AS next24_charttime
       , me24.positiveculture                       AS next24_positiveculture
       , me24.spec_type_desc                        AS next24_specimen
       , ROW_NUMBER() OVER (
           PARTITION BY ab_tbl.subject_id, ab_tbl.ab_id
           ORDER BY     me24.chartdate, me24.charttime NULLS LAST
         ) AS micro_seq
  FROM   ab_tbl
  LEFT   JOIN me me24
         ON  ab_tbl.subject_id = me24.subject_id
         AND (
              (
                me24.charttime IS NOT NULL
                AND ab_tbl.antibiotic_time >= DATETIME_SUB(me24.charttime, INTERVAL '24' HOUR)
                AND ab_tbl.antibiotic_time <  me24.charttime
              )
           OR (
                me24.charttime IS NULL
                AND ab_tbl.antibiotic_date >= me24.chartdate - INTERVAL '1 day'
                AND ab_tbl.antibiotic_date <= me24.chartdate
              )
         )
)
SELECT ab_tbl.subject_id
     , ab_tbl.icustay_id
     , ab_tbl.hadm_id
     , ab_tbl.ab_id
     , ab_tbl.antibiotic
     , ab_tbl.antibiotic_time

     , CASE
         WHEN me2ab.last72_specimen IS NULL AND ab2me.next24_specimen IS NULL
         THEN 0 ELSE 1
       END AS suspected_infection

     , CASE
         WHEN me2ab.last72_specimen IS NULL AND ab2me.next24_specimen IS NULL
         THEN NULL
         ELSE COALESCE(me2ab.last72_charttime, ab_tbl.antibiotic_time)
       END AS suspected_infection_time

     , COALESCE(me2ab.last72_charttime, ab2me.next24_charttime) AS culture_time
     , COALESCE(me2ab.last72_specimen,  ab2me.next24_specimen)  AS specimen
     , COALESCE(me2ab.last72_positiveculture,
                ab2me.next24_positiveculture) AS positive_culture
FROM   ab_tbl
LEFT   JOIN ab_then_me ab2me
       ON  ab_tbl.subject_id = ab2me.subject_id
       AND ab_tbl.ab_id      = ab2me.ab_id
       AND ab2me.micro_seq   = 1
LEFT   JOIN me_then_ab me2ab
       ON  ab_tbl.subject_id = me2ab.subject_id
       AND ab_tbl.ab_id      = me2ab.ab_id
       AND me2ab.micro_seq   = 1;

CREATE INDEX IF NOT EXISTS suspicion_of_infection_idx
  ON suspicion_of_infection (icustay_id, suspected_infection_time);