-- ------------------------------------------------------------------
-- All-time GCS pivot.
--
-- Adapted from the upstream MIMIC-III concepts_postgres file
--   firstday/gcs_first_day.sql
-- with the day-1 time predicate removed and the row reduced to one
-- row per (icustay_id, charttime, gcs) for the entire ICU stay.
--
-- The carry-forward logic (impute missing components from the
-- immediately preceding charttime within 6 h) is preserved.  GCS
-- during sedation/intubation is set to 15, matching upstream.
-- ------------------------------------------------------------------

DROP TABLE IF EXISTS gcs_all;
CREATE TABLE gcs_all AS

WITH base AS
(
  SELECT pvt.icustay_id
       , pvt.charttime
       , MAX(CASE WHEN pvt.itemid = 454 THEN pvt.valuenum END) AS gcsmotor
       , MAX(CASE WHEN pvt.itemid = 723 THEN pvt.valuenum END) AS gcsverbal
       , MAX(CASE WHEN pvt.itemid = 184 THEN pvt.valuenum END) AS gcseyes
       , CASE
           WHEN MAX(CASE WHEN pvt.itemid = 723 THEN pvt.valuenum END) = 0
           THEN 1 ELSE 0
         END AS endotrachflag
       , ROW_NUMBER() OVER (PARTITION BY pvt.icustay_id ORDER BY pvt.charttime ASC) AS rn
  FROM (
    SELECT l.icustay_id
         , CASE
             WHEN l.itemid IN (723, 223900) THEN 723
             WHEN l.itemid IN (454, 223901) THEN 454
             WHEN l.itemid IN (184, 220739) THEN 184
             ELSE l.itemid
           END AS itemid
         , CASE
             WHEN l.itemid = 723    AND l.value = '1.0 ET/Trach'    THEN 0
             WHEN l.itemid = 223900 AND l.value = 'No Response-ETT' THEN 0
             ELSE l.valuenum
           END AS valuenum
         , l.charttime
    FROM   chartevents l
    INNER  JOIN icustays b
           ON l.icustay_id = b.icustay_id
    WHERE  l.itemid IN (184, 454, 723, 223900, 223901, 220739)
      AND  l.charttime BETWEEN b.intime AND b.outtime
      AND  COALESCE(l.error, 0) = 0
  ) pvt
  GROUP  BY pvt.icustay_id, pvt.charttime
)
, gcs AS
(
  SELECT b.icustay_id
       , b.charttime
       , CASE
           WHEN b.gcsverbal = 0 THEN 15
           WHEN b.gcsverbal IS NULL AND b2.gcsverbal = 0 THEN 15
           WHEN b2.gcsverbal = 0 THEN
                  COALESCE(b.gcsmotor , 6)
                + COALESCE(b.gcsverbal, 5)
                + COALESCE(b.gcseyes  , 4)
           ELSE
                  COALESCE(b.gcsmotor , COALESCE(b2.gcsmotor , 6))
                + COALESCE(b.gcsverbal, COALESCE(b2.gcsverbal, 5))
                + COALESCE(b.gcseyes  , COALESCE(b2.gcseyes  , 4))
         END AS gcs
       , b.endotrachflag
  FROM   base b
  LEFT   JOIN base b2
         ON  b.icustay_id = b2.icustay_id
         AND b.rn = b2.rn + 1
         AND b2.charttime > DATETIME_SUB(b.charttime, INTERVAL '6' HOUR)
)
SELECT icustay_id, charttime, gcs, endotrachflag
FROM   gcs;

CREATE INDEX IF NOT EXISTS gcs_all_idx ON gcs_all (icustay_id, charttime);