/* Assignment Factors */
/* Constructs assignment factor breakdowns for Canvas*/
/* Query constructed in BigQuery*/
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.course_assignments_factors.assignment_factors` AS
WITH courses AS (
    SELECT a.id as course_id,
    a.sis_source_id as course_sis_id,
    a.workflow_state as course_status,
    b.id as course_section_id,
    b.sis_source_id as course_section_sis_id,
    b.workflow_state as course_section_status,
    count(c.id) as enrollments    
    FROM `iu-uits-tlt-la.canvas_data.course_dim` a
    INNER JOIN `iu-uits-tlt-la.canvas_data.course_section_dim` b
    ON a.id = b.course_id
    INNER JOIN `iu-uits-tlt-la.canvas_data.enrollment_dim` c 
    ON b.id = c.course_section_id
    WHERE a.sis_source_id IS NOT NULL AND a.sis_source_id not like 'ETRAIN%' /* Excludes Non-SIS provisioned courses */
    AND c.type = 'StudentEnrollment'
    AND c.workflow_state = 'active'
    AND a.enrollment_term_id = 290000000006462 /* Fall 2019 */
    GROUP BY a.id, a.sis_source_id, a.workflow_state, b.id, b.sis_source_id, b.workflow_state
),
assn_wghts as (
    SELECT
    a.id,
    a.canvas_id,
    a.course_id,
    a.name,
    a.default_assignment_name,
    a.workflow_state,
    b.group_weight
FROM `iu-uits-tlt-la.canvas_data.assignment_group_dim` a  
INNER JOIN `iu-uits-tlt-la.canvas_data.assignment_group_fact` b ON a.id = b.assignment_group_id
WHERE workflow_state = 'available' 
),
assn_ovrd_adhoc AS (
    SELECT id as ovrd_id, assignment_id 
    FROM `iu-uits-tlt-la.canvas_data.assignment_override_dim` 
    WHERE workflow_state = 'active' AND set_type = 'adhoc'
),
assn_ovrd_group AS (
    select id as ovrd_id, assignment_id, group_id 
    FROM `iu-uits-tlt-la.canvas_data.assignment_override_dim` 
    WHERE workflow_state = 'active' AND set_type = 'group'
),
assn_ovrd_course_sec AS (
    select id as ovrd_id, course_section_id
    FROM `iu-uits-tlt-la.canvas_data.assignment_override_dim`
    WHERE workflow_state = 'active' AND set_type = 'course_section'
),
course_assn_md AS (
SELECT a.course_id, 
       a.id as assignment_id,
       CASE WHEN regexp_contains(submission_types, "online") = TRUE  OR submission_types = 'discussion_topics' OR submission_types = 'social_annotation' OR submission_types = 'wiki_page' THEN "Online"
       WHEN submission_types = 'external_tool' OR submission_types = "[external_tool]" THEN 'External Tool'
       WHEN regexp_contains(submission_types, 'media') = TRUE AND regexp_contains(submission_types, 'online') = FALSE THEN 'Media Upload' 
       WHEN submission_types = 'on_paper' THEN 'On Paper'
       WHEN submission_types = 'not_graded' THEN 'Not Graded'
       ELSE 'Not Specified'  END  AS submission_type /* Is blank, 'Assignments'*/,
       CASE WHEN a.due_at IS NOT NULL THEN 'Yes' ELSE 'No' END as has_deadline,
       CASE WHEN peer_reviews IS TRUE THEN 'Yes' ELSE 'No' END as has_peer_reviews, 
       CASE WHEN grading_type = 'not_graded' THEN 'Not Graded' ELSE 'Graded' END as grading_type,
       CASE WHEN b.points_possible IS NOT NULL AND b.points_possible > 0 THEN 'Has Points' ELSE 'No Points' END as has_points,
       a.assignment_group_id                      
FROM `iu-uits-tlt-la.canvas_data.assignment_dim` a 
INNER JOIN `iu-uits-tlt-la.canvas_data.assignment_fact` b ON a.id = b.assignment_id
WHERE a.workflow_state = 'published' 
ORDER BY course_id
),
deagg_join as (
SELECT 
    a.course_id,
    a.course_sis_id,
    a.course_status,
    a.course_section_id,
    a.course_section_sis_id,
    a.course_section_status,
    a.enrollments,
    b.assignment_id,
    CASE WHEN b.has_deadline IS NULL THEN 'Not Specified' ELSE has_deadline END as has_deadline,
    CASE WHEN b.submission_type IS NULL THEN 'Not Specified' ELSE submission_type END  as submission_type,
    CASE WHEN b.grading_type IS NULL THEN 'Not Specified' ELSE grading_type END as grading_type,
    CASE WHEN b.has_points IS NULL THEN 'Not Specified' ELSE has_points END as has_points,
    CASE WHEN c.group_weight <> 0 AND c.group_weight IS NOT NULL THEN 'Yes' ELSE 'No' END as has_weights,
    CASE WHEN b.assignment_id in (select distinct assignment_id FROM assn_ovrd_adhoc) AND b.assignment_id NOT IN (select distinct assignment_id FROM assn_ovrd_group) THEN 'Differentiated Adhoc'
         WHEN b.assignment_id IN (select distinct assignment_id FROM assn_ovrd_group) AND b.assignment_id NOT IN (select distinct assignment_id FROM assn_ovrd_adhoc) THEN 'Differentiated Group'
         WHEN a.course_section_id IN (SELECT distinct course_section_id FROM assn_ovrd_course_sec) THEN 'Differentiated Section' ELSE 'Not Differentiated' END as differentiated
FROM courses a 
LEFT JOIN course_assn_md b ON a.course_id = b.course_id
LEFT JOIN assn_wghts c ON b.assignment_group_id = c.id
WHERE enrollments > 5)
SELECT 
    course_id,
    course_sis_id,
    course_status,
    course_section_id,
    course_section_sis_id ,
    course_section_status,
    enrollments,
    has_deadline,
    submission_type,
    grading_type,
    has_points,
    has_weights,
    differentiated,
    count(assignment_id) as factor_total
FROM deagg_join
WHERE assignment_id IS NOT NULL 
GROUP BY course_id, course_sis_id, course_status, course_section_sis_id, course_section_id, course_section_status,  enrollments, has_deadline, submission_type, grading_type, has_points, has_weights, differentiated
/* Processes 1.2 GB, Returns 56,614 rows in ~ 20 secs) */

/* SIS construction and joins occur in Denodo*/
/* Get view of distinct students and gender designation and ethnic designation*/
CREATE OR REPLACE VIEW fa19_gndr_ethnic FOLDER = '/assignment factors' AS 
SELECT DISTINCT sr_stu_term_gt.prsn_univ_id AS prsn_univ_id, sr_stu_term_gt.prsn_gndr_cd AS prsn_gndr_cd, sr_stu_term_gt.prsn_prm_ethnic_desc AS prsn_prm_ethnic_desc 
FROM sr_stu_term_gt 
WHERE acad_term_cd = 4198;
ALTER VIEW fa19_gndr_ethnic
 LAYOUT (sr_stu_term_gt = [20, 20, 286, 1776]);
/* Join with enrollment table*/
CREATE OR REPLACE VIEW stu_enrl_gndr_ethn FOLDER = '/assignment factors' AS 
SELECT sr_enrl_st.cls_key AS cls_key, sr_enrl_st.stu_term_key AS stu_term_key, sr_enrl_st.prsn_univ_id AS prsn_univ_id, fa19_gndr_ethnic.prsn_gndr_cd AS prsn_gndr_cd, fa19_gndr_ethnic.prsn_prm_ethnic_desc AS prsn_prm_ethnic_desc, sr_enrl_st.acad_career_cd AS acad_career_cd, sr_enrl_st.acad_career_shrt_desc AS acad_career_shrt_desc, sr_enrl_st.acad_career_desc AS acad_career_desc, sr_enrl_st.crs_crr_cd AS crs_crr_cd, sr_enrl_st.cls_nbr AS cls_nbr, sr_enrl_st.inst_cd AS inst_cd, sr_enrl_st.inst_desc AS inst_desc, sr_enrl_st.acad_term_cd AS acad_term_cd, sr_enrl_st.acad_term_desc AS acad_term_desc, sr_enrl_st.stu_enrl_stat_dt AS stu_enrl_stat_dt, sr_enrl_st.acad_unt_tkn_nbr AS acad_unt_tkn_nbr, sr_enrl_st.acad_prgrss_unt_nbr AS acad_prgrss_unt_nbr, sr_enrl_st.acad_bill_unt_nbr AS acad_bill_unt_nbr, sr_enrl_st.stu_grd_basis_cd AS stu_grd_basis_cd, sr_enrl_st.stu_grd_basis_shrt_desc AS stu_grd_basis_shrt_desc, sr_enrl_st.stu_grd_basis_desc AS stu_grd_basis_desc, sr_enrl_st.crs_ofcl_grd_cd AS crs_ofcl_grd_cd, sr_enrl_st.crs_grd_dt AS crs_grd_dt, sr_enrl_st.stu_earn_crdt_ind AS stu_earn_crdt_ind, sr_enrl_st.stu_unt_atmpd_cd AS stu_unt_atmpd_cd, sr_enrl_st.stu_grd_pts_nbr AS stu_grd_pts_nbr, sr_enrl_st.stu_enrl_stat_cd AS stu_enrl_stat_cd, sr_enrl_st.stu_enrl_stat_shrt_desc AS stu_enrl_stat_shrt_desc, sr_enrl_st.stu_enrl_stat_desc AS stu_enrl_stat_desc, sr_enrl_st.stu_enrl_stat_reas_cd AS stu_enrl_stat_reas_cd, sr_enrl_st.stu_enrl_stat_reas_shrt_desc AS stu_enrl_stat_reas_shrt_desc, sr_enrl_st.stu_enrl_stat_reas_desc AS stu_enrl_stat_reas_desc, sr_enrl_st.stu_lst_enrl_reas_cd AS stu_lst_enrl_reas_cd, sr_enrl_st.stu_lst_enrl_reas_shrt_desc AS stu_lst_enrl_reas_shrt_desc, sr_enrl_st.stu_lst_enrl_reas_desc AS stu_lst_enrl_reas_desc, sr_enrl_st.stu_enrl_add_dt AS stu_enrl_add_dt, sr_enrl_st.stu_enrl_drp_dt AS stu_enrl_drp_dt, sr_enrl_st.crs_repeat_cd AS crs_repeat_cd, sr_enrl_st.pplsft_acad_org_hier_val AS pplsft_acad_org_hier_val, sr_enrl_st.pplsft_acad_org_lvl_1_cd AS pplsft_acad_org_lvl_1_cd, sr_enrl_st.pplsft_acad_org_lvl_2_cd AS pplsft_acad_org_lvl_2_cd, sr_enrl_st.pplsft_acad_org_lvl_3_cd AS pplsft_acad_org_lvl_3_cd, sr_enrl_st.pplsft_acad_org_lvl_4_cd AS pplsft_acad_org_lvl_4_cd, sr_enrl_st.pplsft_acad_org_lvl_5_cd AS pplsft_acad_org_lvl_5_cd, sr_enrl_st.prsn_ferpa_cmplt_rstrct_ind AS prsn_ferpa_cmplt_rstrct_ind, sr_enrl_st.ro_row_lvl_sec_cd AS ro_row_lvl_sec_cd, sr_enrl_st.crs_id AS crs_id, sr_enrl_st.crsofr_nbr AS crsofr_nbr, sr_enrl_st.crs_desc AS crs_desc, sr_enrl_st.crs_subj_cd AS crs_subj_cd, sr_enrl_st.crs_catlg_nbr AS crs_catlg_nbr, sr_enrl_st.stu_pos_nbr AS stu_pos_nbr, sr_enrl_st.cls_drp_if_enrl_nbr AS cls_drp_if_enrl_nbr, sr_enrl_st.acad_grp_cd AS acad_grp_cd, sr_enrl_st.acad_grp_desc AS acad_grp_desc, sr_enrl_st.crs_subj_dept_cd AS crs_subj_dept_cd, sr_enrl_st.cls_sect_cd AS cls_sect_cd, sr_enrl_st.cls_drp_crs_subj_cd AS cls_drp_crs_subj_cd, sr_enrl_st.cls_drp_crs_catlg_nbr AS cls_drp_crs_catlg_nbr, sr_enrl_st.row_last_updt_dt AS row_last_updt_dt, sr_enrl_st.enrl_rqst_src_cd AS enrl_rqst_src_cd, sr_enrl_st.enrl_rqst_src_desc AS enrl_rqst_src_desc, sr_enrl_st.enrl_rqst_src_lst_updt_cd AS enrl_rqst_src_lst_updt_cd, sr_enrl_st.enrl_rqst_src_lst_updt_desc AS enrl_rqst_src_lst_updt_desc, sr_enrl_st.crs_grd_inpt_cd AS crs_grd_inpt_cd, sr_enrl_st.cls_lnk_ovrd_ind AS cls_lnk_ovrd_ind, sr_enrl_st.crs_lapse_to_grd_cd AS crs_lapse_to_grd_cd, sr_enrl_st.crs_lapse_grd_stat_cd AS crs_lapse_grd_stat_cd, sr_enrl_st.crs_lapse_grd_stat_desc AS crs_lapse_grd_stat_desc, sr_enrl_st.crs_lapse_ddln_dt AS crs_lapse_ddln_dt, sr_enrl_st.cls_instr_nm AS cls_instr_nm, sr_enrl_st.cls_instr_gds_cmp_email_addr AS cls_instr_gds_cmp_email_addr, sr_enrl_st.crs_cmpnt_cd AS crs_cmpnt_cd, sr_enrl_st.cls_instrc_mode_cd AS cls_instrc_mode_cd, sr_enrl_st.cls_instrc_mode_desc AS cls_instrc_mode_desc, sr_enrl_st.crs_cnt AS crs_cnt, sr_enrl_st.crs_drvd_fn_ind AS crs_drvd_fn_ind, sr_enrl_st.cls_sesn_cd AS cls_sesn_cd, sr_enrl_st.cls_sesn_desc AS cls_sesn_desc, sr_enrl_st.stu_rec_lst_drp_updt_dt AS stu_rec_lst_drp_updt_dt, sr_enrl_st.stu_rec_lst_drp_updt_ts AS stu_rec_lst_drp_updt_ts, sr_enrl_st.cls_drvd_sesn_cd AS cls_drvd_sesn_cd, sr_enrl_st.cls_drvd_sesn_desc AS cls_drvd_sesn_desc, sr_enrl_st.cls_rqmt_dsgt_cd AS cls_rqmt_dsgt_cd, sr_enrl_st.cls_rqmt_dsgt_optn_ind AS cls_rqmt_dsgt_optn_ind, sr_enrl_st.cls_rqmt_dsgt_grd_cd AS cls_rqmt_dsgt_grd_cd, sr_enrl_st.stu_drvd_cls_enrl_stat_ind AS stu_drvd_cls_enrl_stat_ind, sr_enrl_st.stu_drvd_cls_enrl_stat_rpt_ind AS stu_drvd_cls_enrl_stat_rpt_ind, sr_enrl_st.tscrpt_nte_id AS tscrpt_nte_id, sr_enrl_st.acad_org_cd AS acad_org_cd, sr_enrl_st.cls_instr_ntwrk_id AS cls_instr_ntwrk_id, sr_enrl_st.crs_eqvl_id AS crs_eqvl_id, sr_enrl_st.cls_drvd_lvl_cd AS cls_drvd_lvl_cd, sr_enrl_st.cmp_loc_cd AS cmp_loc_cd, sr_enrl_st.cls_strt_dt AS cls_strt_dt, sr_enrl_st.cls_end_dt AS cls_end_dt, sr_enrl_st.iu_wait_type AS iu_wait_type, sr_enrl_st.stu_enrl_dist_ed_cd AS stu_enrl_dist_ed_cd, sr_enrl_st.cls_instr_univ_id AS cls_instr_univ_id, sr_enrl_st.occ_hm_cmp_ind AS occ_hm_cmp_ind, sr_enrl_st.occ_orig_cls_nbr AS occ_orig_cls_nbr, sr_enrl_st.cls_dcr_ind AS cls_dcr_ind, sr_enrl_st.cls_dcr_ocmp_ind AS cls_dcr_ocmp_ind, sr_enrl_st.hrs_instr_typ_cd AS hrs_instr_typ_cd, sr_enrl_st.hrs_instr_typ_desc AS hrs_instr_typ_desc, sr_enrl_st.hrs_instr_clsif_desc AS hrs_instr_clsif_desc, sr_enrl_st.hrs_instr_tnr_elig_ind AS hrs_instr_tnr_elig_ind, sr_enrl_st.hrs_acad_admin_ind AS hrs_acad_admin_ind, sr_enrl_st.cls_acp_ind AS cls_acp_ind, sr_enrl_st.cmp_loc_desc AS cmp_loc_desc, sr_enrl_st.crs_subj_iu_fin_coa_cd AS crs_subj_iu_fin_coa_cd, sr_enrl_st.crs_subj_iu_org_cd AS crs_subj_iu_org_cd, sr_enrl_st.crs_subj_iu_org_nm AS crs_subj_iu_org_nm, sr_enrl_st.crs_subj_iu_rc_cd AS crs_subj_iu_rc_cd, sr_enrl_st.crs_subj_iu_rc_nm AS crs_subj_iu_rc_nm, sr_enrl_st.stu_drvd_cls_enrl_stat_desc AS stu_drvd_cls_enrl_stat_desc, sr_enrl_st.stu_drvd_cls_enrl_stat_rptdesc AS stu_drvd_cls_enrl_stat_rptdesc, sr_enrl_st.cls_ftw_crn_nbr AS cls_ftw_crn_nbr, sr_enrl_st.cls_online_ind AS cls_online_ind, sr_enrl_st.cls_ovst_ind AS cls_ovst_ind, sr_enrl_st.cls_ovst_or_abroad_ind AS cls_ovst_or_abroad_ind 
FROM sr_enrl_st AS sr_enrl_st 
INNER JOIN fa19_gndr_ethnic AS fa19_gndr_ethnic ON sr_enrl_st.prsn_univ_id = fa19_gndr_ethnic.prsn_univ_id  W
HERE (sr_enrl_st.acad_term_cd = 4198 AND sr_enrl_st.stu_drvd_cls_enrl_stat_ind = 'E');
ALTER VIEW stu_enrl_gndr_ethn
 LAYOUT (sr_enrl_st = [224, 181, 200, 225], fa19_gndr_ethnic = [670, 150, 200, 86]);

/* Create Aggregate View*/
CREATE OR REPLACE VIEW cls_enrl_cnts FOLDER = '/assignment factors' AS 
SELECT inst_cd AS inst_cd, crs_subj_cd AS crs_subj_cd, crs_catlg_nbr AS crs_catlg_nbr, cls_instrc_mode_desc AS cls_instrc_mode_desc, cls_nbr AS cls_nbr, inst_desc AS inst_desc, sum(case WHEN (prsn_gndr_cd = 'F') THEN 1 ELSE 0 END) AS fm_cnt, count(DISTINCT prsn_univ_id) AS stu_enrl_cnt, sum(case WHEN (prsn_prm_ethnic_desc = 'White') THEN 1 ELSE 0 END) AS white_ind_cnt, sum(case WHEN (crs_ofcl_grd_cd in ('D', 'D+', 'D-', 'DX', 'D+X', 'D-X', 'F', 'FX', 'W', 'WX')) THEN 1 ELSE 0 END) AS dfw_ind_cnt, acad_grp_desc AS acad_grp_desc 
FROM stu_enrl_gndr_ethn 
GROUP BY inst_desc, inst_cd, acad_grp_desc, crs_subj_cd, crs_catlg_nbr, cls_instrc_mode_desc, cls_nbr;
ALTER VIEW cls_enrl_cnts
 LAYOUT (stu_enrl_gndr_ethn = [20, 20, 292, 1776]);


/* Create View with %*/
CREATE OR REPLACE VIEW cls_enrl_cnt_prcnts FOLDER = '/assignment factors' AS 
SELECT cls_enrl_cnts.inst_cd AS inst_cd, cls_enrl_cnts.acad_grp_desc AS acad_grp_desc, cls_enrl_cnts.crs_subj_cd AS crs_subj_cd, cls_enrl_cnts.crs_catlg_nbr AS crs_catlg_nbr, cls_enrl_cnts.cls_instrc_mode_desc AS cls_instrc_mode_desc, cls_enrl_cnts.cls_nbr AS cls_nbr, cls_enrl_cnts.fm_cnt AS fm_cnt, cls_enrl_cnts.stu_enrl_cnt AS stu_enrl_cnt, cls_enrl_cnts.white_ind_cnt AS white_ind_cnt, cls_enrl_cnts.dfw_ind_cnt AS dfw_ind_cnt, (round((cls_enrl_cnts.white_ind_cnt/cls_enrl_cnts.stu_enrl_cnt), 2)*100) AS prcnt_white, (round((cls_enrl_cnts.dfw_ind_cnt/cls_enrl_cnts.stu_enrl_cnt), 2)*100) AS prcnt_dfw, (round((cls_enrl_cnts.fm_cnt/cls_enrl_cnts.stu_enrl_cnt), 2)*100) AS prcnt_fm 
FROM cls_enrl_cnts WHERE stu_enrl_cnt > 5;
ALTER VIEW cls_enrl_cnt_prcnts
 LAYOUT (cls_enrl_cnts = [20, 20, 217, 226]);



/* Join with assignment_factors from BigQuery*/
CREATE OR REPLACE VIEW assignment_factor_counts FOLDER = '/assignment factors' AS 
SELECT assignment_factors.course_id AS course_id, assignment_factors.course_sis_id AS course_sis_id, assignment_factors.course_status AS course_status, concat(trim(cls_enrl_cnt_prcnts.crs_subj_cd), '-', trim(course_section_dim_sis.crs_catlg_nbr)) AS course, assignment_factors.course_section_id AS course_section_id, assignment_factors.course_section_sis_id AS course_section_sis_id, assignment_factors.course_section_status AS course_section_status, cls_enrl_cnt_prcnts.inst_cd AS inst_cd, cls_enrl_cnt_prcnts.acad_grp_desc AS acad_grp_desc, course_section_dim_sis.crs_subj_dept_cd AS crs_subj_dept_cd, sr_cls_gt.crs_subj_desc AS crs_subj_desc, cls_enrl_cnt_prcnts.cls_instrc_mode_desc AS cls_instrc_mode_desc, cls_enrl_cnt_prcnts.stu_enrl_cnt AS stu_enrl_cnt, cls_enrl_cnt_prcnts.prcnt_fm AS prcnt_fm, cls_enrl_cnt_prcnts.prcnt_white AS prcnt_white, cls_enrl_cnt_prcnts.prcnt_dfw AS prcnt_dfw, assignment_factors.has_deadline AS has_deadline, assignment_factors.submission_type AS submission_type, assignment_factors.grading_type AS grading_type, assignment_factors.has_points AS has_points, assignment_factors.has_weights AS has_weights, assignment_factors.differentiated AS differentiated, assignment_factors.factor_total AS factor_total 
FROM ((assignment_factors AS assignment_factors 
INNER JOIN canvas_warehouse.course_section_dim_sis AS course_section_dim_sis ON assignment_factors.course_section_id = course_section_dim_sis.id ) 
INNER JOIN cls_enrl_cnt_prcnts AS cls_enrl_cnt_prcnts ON course_section_dim_sis.cls_nbr = cls_enrl_cnt_prcnts.cls_nbr ) 
INNER JOIN sr_cls_gt AS sr_cls_gt ON course_section_dim_sis.cls_nbr = sr_cls_gt.cls_nbr  
WHERE (course_section_dim_sis.acad_term_cd = 4198 AND sr_cls_gt.acad_term_cd = 4198);
ALTER VIEW assignment_factor_counts
 LAYOUT (assignment_factors = [116, 145, 200, 225], course_section_dim_sis = [470, 80, 200, 225], cls_enrl_cnt_prcnts = [880, 90, 200, 225], sr_cls_gt = [902, 465, 200, 225]);


/* Final join produces 49,952*/