 COMMIT;
 
 BEGIN;
-CREATE TABLE dw_patients AS			-- "patient" may well be the wrong name for it
-SELECT
-	people.id,
-	people.entity_id,			-- Keeping this just in case
---	race_ec.code_description AS race,					-- code_description?
-	birth_gender_ec.code_description AS birth_gender,			-- code_description?
---	current_gender_ec.code_description AS current_gender,			-- code_description?
-	ethnicity_ec.code_description AS ethnicity,				-- code_description?
-	primary_language_ec.code_description AS primary_language,		-- code_description?
-	people.first_name,
-	people.middle_name,
-	people.last_name,
-	upsert_date(people.birth_date) AS birth_date,
-	upsert_date(people.date_of_death) AS date_of_death,
---	food_handler_ec.code_description AS food_handler,			-- code_description?
---	healthcare_worker_ec.code_description AS healthcare_worker,		-- code_description?
---	group_living_ec.code_description AS group_living,			-- code_description?
---	day_care_association_ec.code_description AS day_care_association,	-- code_description?
---	age_type_ec.code_description AS age_type,				-- code_description?
---	people.risk_factors,
---	people.risk_factors_notes,
---	people.approximate_age_no_birthday,
-    FALSE::BOOLEAN AS is_morbidity_patient,
-    FALSE::BOOLEAN AS is_contact_patient,
-	people.first_name_soundex,
-	people.last_name_soundex
-FROM
-	people
-	LEFT JOIN external_codes birth_gender_ec
-		ON (birth_gender_ec.id = people.age_type_id)
-	LEFT JOIN external_codes ethnicity_ec
-		ON (ethnicity_ec.id = people.age_type_id)
-	LEFT JOIN external_codes primary_language_ec
-		ON (primary_language_ec.id = people.age_type_id)
-WHERE
-	EXISTS (
-		SELECT 1
-		FROM participations part
-		WHERE 
-			part.type = 'InterestedParty' AND
-			part.primary_entity_id = people.entity_id
-	)
-;
-
+CREATE TABLE dw_morbidity_patients AS
+SELECT
+    people.id,
+    people.entity_id,            -- Keeping this just in case
+    birth_gender_ec.code_description AS birth_gender,            -- code_description?
+    ethnicity_ec.code_description AS ethnicity,                -- code_description?
+    primary_language_ec.code_description AS primary_language,        -- code_description?
+    people.first_name,
+    people.middle_name,
+    people.last_name,
+    upsert_date(people.birth_date) AS birth_date,
+    upsert_date(people.date_of_death) AS date_of_death,
+    people.first_name_soundex,
+    people.last_name_soundex
+FROM
+    people
+    LEFT JOIN external_codes birth_gender_ec
+        ON (birth_gender_ec.id = people.age_type_id)
+    LEFT JOIN external_codes ethnicity_ec
+        ON (ethnicity_ec.id = people.age_type_id)
+    LEFT JOIN external_codes primary_language_ec
+        ON (primary_language_ec.id = people.age_type_id)
+    INNER JOIN (
+        SELECT
+            part.primary_entity_id
+        FROM
+            participations part
+            INNER JOIN events
+                ON (events.id = part.event_id)
+        WHERE
+            part.type = 'InterestedParty' AND
+            events.type = 'MorbidityEvent'
+        GROUP BY part.primary_entity_id
+    ) f
+        ON (f.primary_entity_id = people.entity_id)
+;
+
+CREATE TABLE dw_contact_patients AS
+SELECT
+    people.id,
+    people.entity_id,            -- Keeping this just in case
+    birth_gender_ec.code_description AS birth_gender,            -- code_description?
+    ethnicity_ec.code_description AS ethnicity,                -- code_description?
+    primary_language_ec.code_description AS primary_language,        -- code_description?
+    people.first_name,
+    people.middle_name,
+    people.last_name,
+    upsert_date(people.birth_date) AS birth_date,
+    upsert_date(people.date_of_death) AS date_of_death,
+    people.first_name_soundex,
+    people.last_name_soundex
+FROM
+    people
+    LEFT JOIN external_codes birth_gender_ec
+        ON (birth_gender_ec.id = people.age_type_id)
+    LEFT JOIN external_codes ethnicity_ec
+        ON (ethnicity_ec.id = people.age_type_id)
+    LEFT JOIN external_codes primary_language_ec
+        ON (primary_language_ec.id = people.age_type_id)
+    INNER JOIN (
+        SELECT
+            part.primary_entity_id
+        FROM
+            participations part
+            INNER JOIN events
+                ON (events.id = part.event_id)
+        WHERE
+            part.type = 'InterestedParty' AND
+            events.type = 'ContactEvent'
+        GROUP BY part.primary_entity_id
+    ) f
+        ON (f.primary_entity_id = people.entity_id)
+;
+
+CREATE TABLE dw_encounter_patients AS
+SELECT
+    people.id,
+    people.entity_id,            -- Keeping this just in case
+    birth_gender_ec.code_description AS birth_gender,            -- code_description?
+    ethnicity_ec.code_description AS ethnicity,                -- code_description?
+    primary_language_ec.code_description AS primary_language,        -- code_description?
+    people.first_name,
+    people.middle_name,
+    people.last_name,
+    upsert_date(people.birth_date) AS birth_date,
+    upsert_date(people.date_of_death) AS date_of_death,
+    people.first_name_soundex,
+    people.last_name_soundex
+FROM
+    people
+    LEFT JOIN external_codes birth_gender_ec
+        ON (birth_gender_ec.id = people.age_type_id)
+    LEFT JOIN external_codes ethnicity_ec
+        ON (ethnicity_ec.id = people.age_type_id)
+    LEFT JOIN external_codes primary_language_ec
+        ON (primary_language_ec.id = people.age_type_id)
+    INNER JOIN (
+        SELECT
+            part.primary_entity_id
+        FROM
+            participations part
+            INNER JOIN events
+                ON (events.id = part.event_id)
+        WHERE
+            part.type = 'InterestedParty' AND
+            events.type = 'EncounterEvent'
+        GROUP BY part.primary_entity_id
+    ) f
+        ON (f.primary_entity_id = people.entity_id)
+;
 
 CREATE TABLE dw_morbidity_events AS
 SELECT
-	events.id,
-	events.parent_id,               -- Reporting tool might provide a field "was_a_contact" == parent_id IS NOT NULL
-	ppl.id AS dw_patients_id,
-
-	ds.id AS disease_id,
-	ijpl.name AS investigating_jurisdiction,
+    events.id,
+    events.parent_id,               -- Reporting tool might provide a field "was_a_contact" == parent_id IS NOT NULL
+    ppl.id AS dw_patients_id,
+
+    ds.id AS disease_id,
+    ijpl.name AS investigating_jurisdiction,
     ijpl.id AS investigating_jurisdiction_id,
     jorpl.name AS jurisdiction_of_residence,
     jorpl.id AS jurisdiction_of_residence_id,
-	scsi.code_description AS state_case_status_code, 	-- code_description?
-	lcsi.code_description AS lhd_case_status_code,		-- code_description?
-	events."MMWR_week",
-	events."MMWR_year",
-
-	events.event_name,
-	events.record_number,
+    scsi.code_description AS state_case_status_code,     -- code_description?
+    lcsi.code_description AS lhd_case_status_code,        -- code_description?
+    events."MMWR_week",
+    events."MMWR_year",
+
+    events.event_name,
+    events.record_number,
 
     events.age_at_onset AS actual_age_at_onset,
-	agetypeec.code_description AS actual_age_type,
+    agetypeec.code_description AS actual_age_type,
     ppl.approximate_age_no_birthday AS estimated_age_at_onset,
     est_ec.code_description AS estimated_age_type,
-	events.parent_guardian,
+    events.parent_guardian,
 
     fhec.code_description AS food_handler,
     hcwec.code_description AS healthcare_worker,

    upsert_date(pt.treatment_date) AS date_of_treatment,
    pt.treatment AS treatment_notes
FROM
	participations_treatments pt
	LEFT JOIN participations p
		ON (p.id = pt.participation_id)
	LEFT JOIN events
		ON (events.id = p.event_id)
    LEFT JOIN external_codes tgec
        ON (tgec.id = pt.treatment_given_yn_id)
WHERE
    events.type IN ('ContactEvents', 'MorbidityEvents')
;

INSERT INTO dw_events_treatments 
(
    id, dw_morbidity_events_id, dw_contact_events_id,
    treatment_id, treatment_given, date_of_treatment,

    events.type = 'EncounterEvent'
;

ALTER TABLE dw_encounters_treatments
    ADD CONSTRAINT pk_dw_encounters_treatments PRIMARY KEY (id);

CREATE TABLE dw_morbidity_questions AS
SELECT
    q.*
FROM
    questions q
    INNER JOIN (
        SELECT
            DISTINCT questions.id
        FROM
            questions
            INNER JOIN answers a
                ON (a.question_id = questions.id)
            INNER JOIN events e
                ON (a.event_id = e.id AND e.type = 'MorbidityEvent')
    ) f
        ON (f.id = q.id)
;

CREATE TABLE dw_contact_questions AS
SELECT
    q.*
FROM
    questions q
    INNER JOIN (
        SELECT
            DISTINCT questions.id
        FROM
            questions
            INNER JOIN answers a
                ON (a.question_id = questions.id)
            INNER JOIN events e
                ON (a.event_id = e.id AND e.type = 'ContactEvent')
    ) f
        ON (f.id = q.id)
;

CREATE TABLE dw_encounter_questions AS
SELECT
    q.*
FROM
    questions q
    INNER JOIN (
        SELECT
            DISTINCT questions.id
        FROM
            questions
            INNER JOIN answers a
                ON (a.question_id = questions.id)
            INNER JOIN events e
                ON (a.event_id = e.id AND e.type = 'EncounterEvent')
    ) f
        ON (f.id = q.id)
;

CREATE TABLE dw_place_questions AS
SELECT
    q.*
FROM
    questions q
    INNER JOIN (
        SELECT
            DISTINCT questions.id
        FROM
            questions
            INNER JOIN answers a
                ON (a.question_id = questions.id)
            INNER JOIN events e
                ON (a.event_id = e.id AND e.type = 'PlaceEvent')
    ) f
        ON (f.id = q.id)
;

-- CREATE TABLE dw_questions AS
-- SELECT
--     *,
--     CASE
--         WHEN EXISTS (
--             SELECT 1
--             FROM
--                 questions
--                 INNER JOIN events
--                     ON (events.id = questions.id)
--                 WHERE
--                     events.type = 'MorbidityEvent' AND
--                     questions.id = q.id
--         )
--         THEN TRUE::BOOLEAN
--         ELSE FALSE::BOOLEAN
--     END AS is_morbidity,
--     CASE
--         WHEN EXISTS (
--             SELECT 1
--             FROM
--                 questions
--                 INNER JOIN events
--                     ON (events.id = questions.id)
--                 WHERE
--                     events.type = 'ContactEvent' AND
--                     questions.id = q.id
--         )
--         THEN TRUE::BOOLEAN
--         ELSE FALSE::BOOLEAN
--     END AS is_contact,
--     CASE
--         WHEN EXISTS (
--             SELECT 1
--             FROM
--                 questions
--                 INNER JOIN events
--                     ON (events.id = questions.id)
--                 WHERE
--                     events.type = 'PlaceEvent' AND
--                     questions.id = q.id
--         )
--         THEN TRUE::BOOLEAN
--         ELSE FALSE::BOOLEAN
--     END AS is_place,
--     CASE
--         WHEN EXISTS (
--             SELECT 1
--             FROM
--                 questions
--                 INNER JOIN events
--                     ON (events.id = questions.id)
--                 WHERE
--                     events.type = 'EncounterEvent' AND
--                     questions.id = q.id
--         )
--         THEN TRUE::BOOLEAN
--         ELSE FALSE::BOOLEAN
--     END AS is_encounter
-- FROM questions q;

CREATE TABLE dw_morbidity_answers AS
SELECT
    a.*
FROM
    answers a
    INNER JOIN events e
        ON (e.id = a.event_id)
WHERE
    e.type = 'MorbidityEvent'
;

CREATE TABLE dw_contact_answers AS
SELECT
    a.*
FROM
    answers a
    INNER JOIN events e
        ON (e.id = a.event_id)
WHERE
    e.type = 'ContactEvent'
;

CREATE TABLE dw_encounter_answers AS
SELECT
    a.*
FROM
    answers a
    INNER JOIN events e
        ON (e.id = a.event_id)
WHERE
    e.type = 'EncounterEvent'
;

CREATE TABLE dw_place_answers AS
SELECT
    a.*
FROM
    answers a
    INNER JOIN events e
        ON (e.id = a.event_id)
WHERE
    e.type = 'PlaceEvent'
;

-- CREATE TABLE dw_answers AS
-- SELECT
--     *,
--     CASE
--         WHEN EXISTS (
--             SELECT 1
--             FROM events
--             WHERE
--                 events.id = a.event_id AND
--                 events.type = 'MorbidityEvent'
--         )
--         THEN TRUE::BOOLEAN
--         ELSE FALSE::BOOLEAN
--     END AS is_morbidity,
--     CASE
--         WHEN EXISTS (
--             SELECT 1
--             FROM events
--             WHERE
--                 events.id = a.event_id AND
--                 events.type = 'ContactEvent'
--         )
--         THEN TRUE::BOOLEAN
--         ELSE FALSE::BOOLEAN
--     END AS is_contact,
--     CASE
--         WHEN EXISTS (
--             SELECT 1
--             FROM events
--             WHERE
--                 events.id = a.event_id AND
--                 events.type = 'PlaceEvent'
--         )
--         THEN TRUE::BOOLEAN
--         ELSE FALSE::BOOLEAN
--     END AS is_place,
--     CASE
--         WHEN EXISTS (
--             SELECT 1
--             FROM events
--             WHERE
--                 events.id = a.event_id AND
--                 events.type = 'EncounterEvent'
--         )
--         THEN TRUE::BOOLEAN
--         ELSE FALSE::BOOLEAN
--     END AS is_encounter
-- FROM answers a;

CREATE TABLE dw_morbidity_diseases AS
SELECT
    d.*
FROM
    diseases d
    INNER JOIN (
        SELECT
            DISTINCT diseases.id
        FROM
            diseases
            INNER JOIN dw_morbidity_events dme
                ON (dme.disease_id = diseases.id)
    ) f
        ON (f.id = d.id)
;

CREATE TABLE dw_contact_diseases AS
SELECT
    d.*
FROM
    diseases d
    INNER JOIN (
        SELECT
            DISTINCT diseases.id
        FROM
            diseases
            INNER JOIN dw_contact_events dme
                ON (dme.disease_id = diseases.id)
    ) f
        ON (f.id = d.id)
;

ANALYZE;

TRUNCATE trisano.etl_success;
INSERT INTO trisano.etl_success (success) VALUES (TRUE);
