def get_referral_arm_1_data():
    
    query = """
            select
                case
                    when question_type = 'checkbox' then '1'
                    else response_text 
                end response_text,
                subject_id,
                mrn,
                patient_id::float::integer,
                case
                    when question_type = 'checkbox' then concat(question_label, '___', response_text)
                    else question_label
                end question_label
            from
                data_warehouse.fhir_questionnaire_response fqr
            where
                redcap_pid = '39'
                and redcap_unique_event_name = 'referral_arm_1'
                and question_label in (
                    'rs_ohip',
                    'rs_name',
                    'last_name',
                    'rs_address',
                    'rs_city',
                    'rs_postalcode',
                    'rs_referraltype',
                    'referral_pcprovider_billingnumber',
                    'rs_dateofbirth',
                    'referral_clientid',
                    'rs_versioncode',
                    'rs_preferredname',
                    'referral_pcprovider_phone',
                    'referral_pcprovider_name',
                    'referral_pcprovider_fax'
            );
    """
    
    df = pd.read_sql(query, engine)
    
    df = df.pivot(
        index = ['subject_id','mrn','patient_id'], 
        columns = 'question_label', 
        values = 'response_text'
    )
    df = df.reset_index() 
    
    return df
    
def get_intake_arm_1_data():
    
    query = """
            select distinct
                case
                    when question_type = 'checkbox' then '1'
                    else response_text 
                end response_text,
                subject_id,
                mrn,
                patient_id::float::integer,
                case
                    when question_type = 'checkbox' then concat(question_label, '___', response_text)
                    else question_label
                end question_label
            from
                data_warehouse.fhir_questionnaire_response fqr
            where
                redcap_pid = '39'
                and redcap_unique_event_name = 'intake_arm_1'
                and question_label in (
                    'demo_mainlanguage_ef',
                    'interpreter_required',
                    'demo_gender',
                    'demo_racialgroup',
                    'demo_phone',
                    'demo_email',
                    'demo_phone_texts',
                    'demo_phone_voicemail',
                    'demo_email',
                    'demo_mainlanguage_ef',
                    'demo_pronouns',
                    'intake_osp_enrollment_date',
                    'intake_treatment_option_hi_rd',
                    'intake_txoption_hi_rd'
            );
    """
    
    df = pd.read_sql(query, engine)
        
    df = df.pivot(
        index = ['subject_id','mrn','patient_id'], 
        columns = 'question_label', 
        values = 'response_text'
    )
    df = df.reset_index() 
    
    return df
    
def get_exit_arm_1_data():
    
    query = """
            select
                case
                    when question_type = 'checkbox' then '1'
                    else response_text 
                end response_text,
                subject_id,
                mrn,
                patient_id::float::integer,
                case
                    when question_type = 'checkbox' then concat(question_label, '___', response_text)
                    else question_label
                end question_label
            from
                data_warehouse.fhir_questionnaire_response fqr
            where
                redcap_pid = '39'
                and redcap_unique_event_name = 'exit_arm_1'
                and question_label in (
                    --'exit_bounceback_mainprobdescriptor',
                    'exit_bounceback_reason',
                    'exit_bounceback_riskreport',
                    'exit_suspected_mpd'
            );
    """
    
    df = pd.read_sql(query, engine)
    
    df = df.pivot(
        index = ['subject_id','mrn','patient_id'],
        columns = 'question_label',
        values = 'response_text'
    )
    df = df.reset_index() 
    
    return df
    
def get_assessment_arm_1_data():
    
    query = """
            select
                case
                    when question_type = 'checkbox' then '1'
                    else response_text 
                end response_text,
                subject_id,
                mrn,
                patient_id::float::integer,
                case
                    when question_type = 'checkbox' then concat(question_label, '___', response_text)
                    else question_label
                end question_label
            from
                data_warehouse.fhir_questionnaire_response fqr
            where
                redcap_pid = '39'
                and redcap_unique_event_name = 'assessment_arm_1'
                and question_label in (
                    'mha_problemdescriptor_v2', 
                    'osp_enrollment_date', 
                    'treatment_option_hi_rd',
                    'whodas_2_0_basic_total_ds'
                );
    """
    
    df = pd.read_sql(query, engine)
    
    df = df.pivot(
        index = ['subject_id','mrn','patient_id'], 
        columns = 'question_label', 
        values = 'response_text'
    )
    df = df.reset_index() 
    
    return df
    
def get_recurring_data():
    
    query = """
                select
                    case
                        when question_type = 'checkbox' then '1'
                        else response_text 
                    end response_text,
                    redcap_unique_event_name,
                    subject_id,
                    mrn,
                    case 
                        when response_completion_time = '[not completed]' then null
                        else response_completion_time 
                    end response_completion_time,
                    response_status,
                    case
                        when question_type = 'checkbox' then concat(question_label, '___', response_text)
                        else question_label
                    end question_label,
                    patient_id::float::integer
                from
                    data_warehouse.fhir_questionnaire_response fqr
                where
                    redcap_pid = '39'
                    and question_label in (
                        'phq9_total_score_ds',
                        'gad7_total_score_ds',
                        'hai_total',
                        'oci_total',
                        'pdss_sr_total_score_ds',
                        'pcl5_total',
                        'pswq_total_score_ds',
                        'spin_total'
                    )
                    and response_status = 'Complete';
    """
    
    df = pd.read_sql(query, engine)
        
    df = df.pivot(
        index = [
            'subject_id',
            'mrn',
            'patient_id', 
            'redcap_unique_event_name', 
            'response_completion_time', 
            'response_status'
        ], 
        columns = 'question_label', 
        values = 'response_text'
    )
    df = df.reset_index() 
    
    return df