 -- Do not set locks, allow dirty reads.
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
-CREATE VIEW cdm.src_clean AS
+INSERT INTO cdm.src_clean
 SELECT
     pa.participant_id               AS participant_id,
+    co_b.value                      AS survey_name,
     qr.created                      AS date_of_survey,
     co_q.short_value                AS question_ppi_code,
     qq.code_id                      AS question_code_id,

    ON  qq.code_id = co_q.code_id
LEFT JOIN rdr.code co_a
    ON  qra.value_code_id = co_a.code_id
WHERE
    -- Filter out specific survey questions.
    co_q.short_value not in (
        SELECT cqf.question_ppi_code FROM cdm.combined_question_filter cqf
    )
    -- Filter out specific surveys.
    AND rdr.fn_get_code_module(co_q.short_value) NOT IN (
        SELECT csf.survey_name FROM cdm.combined_survey_filter csf
    )
    AND pa.withdrawal_status != 2
    AND hp.name != 'TEST'
    AND (pa.is_ghost_id is null OR pa.is_ghost_id = 0)
    AND
    (
        (qra.value_code_id IS NOT NULL AND co_a.code_id IS NOT NULL)

        OR qra.value_date IS NOT NULL
        OR qra.value_datetime IS NOT NULL
        OR qra.value_string IS NOT NULL
    )
;

-- Reset ISOLATION level to previous setting
COMMIT;


ALTER TABLE cdm.src_clean ADD KEY (participant_id);

-- -------------------------------------------------------------------
-- Update cdm.src_clean to filter specific surveys.
-- -------------------------------------------------------------------
UPDATE combined_survey_filter SET survey_name = REPLACE(survey_name, '\r', '');

UPDATE cdm.src_clean
    INNER JOIN cdm.combined_survey_filter ON
        cdm.src_clean.survey_name = cdm.combined_survey_filter.survey_name
SET cdm.src_clean.filter = 1
WHERE TRUE;

-- -------------------------------------------------------------------
-- Update cdm.src_clean to filter specific survey questions.
-- -------------------------------------------------------------------

UPDATE combined_question_filter SET question_ppi_code = REPLACE(question_ppi_code, '\r', '');

UPDATE cdm.src_clean
    INNER JOIN cdm.combined_question_filter ON
        cdm.src_clean.question_ppi_code = cdm.combined_question_filter.question_ppi_code
SET cdm.src_clean.filter = 1
WHERE TRUE;

-- -------------------------------------------------------------------
-- source_file: src/src_mapped.sql
