AWSTemplateFormatVersion: '2010-09-09'
Description: Creates a QuickSight dashboard for browsing omics data

Parameters:
  Project:
    Type: String
    
  QuicksightServiceRoleName:
    Type: String
    Default: aws-quicksight-service-role-v0
    
  QuicksightUserId:
    Type: String


Resources:
  QuicksightAccessPolicy:
    Type: AWS::IAM::Policy
    Properties:
      Roles:
        - !Ref QuicksightServiceRoleName
      PolicyName: !Sub "${Project}-access"
      PolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Action:
              - s3:ListBucket
              - s3:GetBucketLocation
              - s3:ListBucketMultipartUploads
            Resource: !Sub
              - "arn:aws:s3:::${DataLakeBucket}"
              - DataLakeBucket: !ImportValue
                  Fn::Sub: '${Project}-DataLakeBucket'
          - Effect: Allow
            Action:
              - s3:GetObject
              - s3:GetObjectVersion
              - s3:AbortMultipartUpload
              - s3:ListMultipartUploadParts
            Resource: !Sub
              - "arn:aws:s3:::${DataLakeBucket}/*"
              - DataLakeBucket: !ImportValue
                  Fn::Sub: '${Project}-DataLakeBucket'
          - Effect: Allow
            Action: s3:PutObject
            Resource: !Sub
              - "arn:aws:s3:::${DataLakeBucket}/results/*"
              - DataLakeBucket: !ImportValue
                  Fn::Sub: '${Project}-DataLakeBucket'
          - Effect: Allow
            Action:
              - kms:GenerateDataKey
              - kms:Decrypt
              - kms:Encrypt
            Resource:
              - !ImportValue
                  Fn::Sub: '${Project}-DataCatalogEncryptionKeyArn'
  
  DataSource:
    Type: AWS::QuickSight::DataSource
    DependsOn: QuicksightAccessPolicy
    Properties:
      DataSourceId: !Sub "${Project}-DataSource"
      Name: !Sub "${Project}-Data"
      AwsAccountId: !Ref AWS::AccountId
      Type: ATHENA
      DataSourceParameters:
        AthenaParameters:
          WorkGroup:
            !Join
              - '-'
              - - !ImportValue
                    Fn::Sub: '${Project}-ResourcePrefixLowercase'
                - !Ref "AWS::Region"
          

  DataSet:
    Type: AWS::QuickSight::DataSet
    Properties:
      DataSetId: !Sub "${Project}-DataSet"
      Name: !Ref Project
      AwsAccountId: !Ref AWS::AccountId
      ImportMode: SPICE
      PhysicalTableMap:
        PhysicalTable1:
          RelationalTable:
            DataSourceArn: !GetAtt DataSource.Arn
            Schema: !ImportValue
              Fn::Sub: '${Project}-ResourcePrefixLowercase'
            Name: clinical_patient
            InputColumns:
              - Name: bcr_patient_uuid
                Type: STRING
              - Name: bcr_patient_barcode
                Type: STRING
              - Name: form_completion_date
                Type: STRING
              - Name: histologic_diagnosis3
                Type: STRING
              - Name: race
                Type: STRING
              - Name: ethnicity
                Type: STRING
              - Name: location_lung_parenchyma
                Type: STRING
              - Name: height_cm_at_diagnosis
                Type: STRING
              - Name: prospective_collection
                Type: STRING
              - Name: weight_kg_at_diagnosis
                Type: STRING
              - Name: retrospective_collection
                Type: STRING
              - Name: gender
                Type: STRING
              - Name: birth_days_to
                Type: STRING
              - Name: history_other_malignancy
                Type: STRING
              - Name: history_neoadjuvant_treatment
                Type: STRING
              - Name: cause_of_death
                Type: STRING
              - Name: cause_of_death_other
                Type: STRING
              - Name: residual_tumor
                Type: STRING
              - Name: tumor_status
                Type: STRING
              - Name: ajcc_staging_edition
                Type: STRING
              - Name: ecog_score
                Type: STRING
              - Name: ajcc_nodes_pathologic_pn
                Type: STRING
              - Name: karnofsky_score
                Type: STRING
              - Name: ajcc_metastasis_pathologic_pm
                Type: STRING
              - Name: radiation_treatment_adjuvant
                Type: STRING
              - Name: vital_status
                Type: STRING
              - Name: pharmaceutical_tx_adjuvant
                Type: STRING
              - Name: tobacco_smoking_history_indicator
                Type: STRING
              - Name: tobacco_smoking_age_started
                Type: STRING
              - Name: last_contact_days_to
                Type: STRING
              - Name: tobacco_smoking_pack_years_smoked
                Type: STRING
              - Name: hiv_status
                Type: STRING
              - Name: death_days_to
                Type: STRING
              - Name: nadir_cd4_counts
                Type: STRING
              - Name: cd4_counts_at_diagnosis
                Type: STRING
              - Name: pulmonary_function_test_indicator
                Type: STRING
              - Name: hiv_rna_load_at_diagnosis
                Type: STRING
              - Name: fev1_percent_ref_prebroncholiator
                Type: STRING
              - Name: prior_aids_conditions
                Type: STRING
              - Name: fev1_percent_ref_postbroncholiator
                Type: STRING
              - Name: hbv_test_results
                Type: STRING
              - Name: fev1_fvc_ratio_prebroncholiator
                Type: STRING
              - Name: hcv_test_results
                Type: STRING
              - Name: fev1_fvc_ratio_postbroncholiator
                Type: STRING
              - Name: hpv_test_results
                Type: STRING
              - Name: carbon_monoxide_diffusion_dlco
                Type: STRING
              - Name: kshv_hhv8_test_results
                Type: STRING
              - Name: kras_gene_analysis_indicator
                Type: STRING
              - Name: haart_therapy_prior_to_dx
                Type: STRING
              - Name: kras_mutation_found
                Type: STRING
              - Name: haart_therapy_at_dx
                Type: STRING
              - Name: kras_mutation_identified_type
                Type: STRING
              - Name: cdc_hiv_risk_group
                Type: STRING
              - Name: egfr_mutation_status
                Type: STRING
              - Name: egfr_mutation_identified_type
                Type: STRING
              - Name: prior_mailgnancy_type
                Type: STRING
              - Name: eml4_alk_translocation_status
                Type: STRING
              - Name: history_immunological_disease
                Type: STRING
              - Name: eml4_alk_translocation_variant
                Type: STRING
              - Name: history_immunological_disease_other
                Type: STRING
              - Name: eml4_alk_analysis_type
                Type: STRING
              - Name: history_immunosuppresive_rx
                Type: STRING
              - Name: history_immunosuppressive_rx_other
                Type: STRING
              - Name: tobacco_smoking_year_started
                Type: STRING
              - Name: history_relevant_infectious_dx
                Type: STRING
              - Name: tobacco_smoking_year_stopped
                Type: STRING
              - Name: history_immunosuppresive_dx_other
                Type: STRING
              - Name: histologic_diagnosis67
                Type: STRING
              - Name: submitted_tumor_site
                Type: STRING
              - Name: performance_status_timing
                Type: STRING
              - Name: laterality
                Type: STRING
              - Name: anatomic_organ_subdivision
                Type: STRING
              - Name: site_of_primary_tumor_other
                Type: STRING
              - Name: treatment_outcome_first_course
                Type: STRING
              - Name: new_tumor_event_dx_indicator
                Type: STRING
              - Name: initial_pathologic_dx_year
                Type: STRING
              - Name: method_initial_path_dx
                Type: STRING
              - Name: method_initial_path_dx_other
                Type: STRING
              - Name: lymph_nodes_examined
                Type: STRING
              - Name: lymph_nodes_examined_count
                Type: STRING
              - Name: lymph_nodes_examined_he_count
                Type: STRING
              - Name: lymph_nodes_examined_ihc_count
                Type: STRING
              - Name: pos_lymph_node_location
                Type: STRING
              - Name: other_pos_node_location
                Type: STRING
              - Name: ajcc_tumor_clinical_ct
                Type: STRING
              - Name: ajcc_tumor_pathologic_pt
                Type: STRING
              - Name: ajcc_nodes_clinical_cn
                Type: STRING
              - Name: ajcc_metastasis_clinical_cm
                Type: STRING
              - Name: ajcc_clinical_tumor_stage
                Type: STRING
              - Name: ajcc_pathologic_tumor_stage
                Type: STRING
              - Name: followup_lost_to
                Type: STRING
              - Name: age_at_initial_pathologic_diagnosis
                Type: STRING
              - Name: cancer_diagnosis_cancer_type_icd9_text_name
                Type: STRING
              - Name: days_to_form_completion
                Type: STRING
              - Name: days_to_hiv_diagnosis
                Type: STRING
              - Name: days_to_initial_pathologic_diagnosis
                Type: STRING
              - Name: days_to_patient_progression_free
                Type: STRING
              - Name: days_to_sample_procurement
                Type: STRING
              - Name: days_to_tumor_progression
                Type: STRING
              - Name: disease_code
                Type: STRING
              - Name: egfr_mutation_identified
                Type: STRING
              - Name: eml4_alk_translocation_identified
                Type: STRING
              - Name: extranodal_involvement
                Type: STRING
              - Name: family_member_relationship_type
                Type: STRING
              - Name: icd_10
                Type: STRING
              - Name: icd_o_3_histology
                Type: STRING
              - Name: icd_o_3_site
                Type: STRING
              - Name: informed_consent_verified
                Type: STRING
              - Name: margin_status
                Type: STRING
              - Name: measure_of_response
                Type: STRING
              - Name: metastatic_site_at_diagnosis
                Type: STRING
              - Name: metastatic_site_at_diagnosis_other
                Type: STRING
              - Name: number_cycles
                Type: STRING
              - Name: patient_id
                Type: STRING
              - Name: pharm_regimen
                Type: STRING
              - Name: pharm_regimen_other
                Type: STRING
              - Name: prior_systemic_therapy_type
                Type: STRING
              - Name: project_code
                Type: STRING
              - Name: regimen_indication
                Type: STRING
              - Name: relative_family_cancer_history
                Type: STRING
              - Name: stage_other
                Type: STRING
              - Name: stem_cell_transplantation
                Type: STRING
              - Name: tissue_source_site
                Type: STRING
              - Name: histologic_diagnosis5
                Type: STRING
              - Name: histologic_diagnosis18
                Type: STRING
              - Name: age_began_smoking_in_years
                Type: STRING
              - Name: anatomic_neoplasm_subdivision_other
                Type: STRING
              - Name: death_cause_text
                Type: STRING
              - Name: hbv_test
                Type: STRING
              - Name: hcv_test
                Type: STRING
              - Name: height
                Type: STRING
              - Name: history_of_other_malignancy
                Type: STRING
              - Name: history_relevant_infectious_dx_other
                Type: STRING
              - Name: hpv_test
                Type: STRING
              - Name: kshv_hhv8_test
                Type: STRING
              - Name: lost_follow_up
                Type: STRING
              - Name: lymph_node_examined_count
                Type: STRING
              - Name: number_of_lymphnodes_positive_by_he
                Type: STRING
              - Name: number_of_lymphnodes_positive_by_ihc
                Type: STRING
              - Name: on_haart_therapy_at_cancer_diagnosis
                Type: STRING
              - Name: on_haart_therapy_prior_to_cancer_diagnosis
                Type: STRING
              - Name: patient_death_reason
                Type: STRING
              - Name: pos_lymph_node_location_other
                Type: STRING
              - Name: primary_lymph_node_presentation_assessment
                Type: STRING
              - Name: tumor_tissue_site
                Type: STRING
              - Name: weight
                Type: STRING
              - Name: partition_0
                Type: STRING
        PhysicalTable2:
          RelationalTable:
            DataSourceArn: !GetAtt DataSource.Arn
            Schema: !ImportValue
              Fn::Sub: '${Project}-ResourcePrefixLowercase'
            Name: tcga_summary
            InputColumns:
              - Name: patient_id
                Type: STRING
              - Name: num_images
                Type: DECIMAL
              - Name: num_image_series
                Type: DECIMAL
              - Name: num_mutation_records
                Type: DECIMAL
              - Name: num_expression_records
                Type: DECIMAL
              - Name: num_cnv_records
                Type: DECIMAL
              - Name: num_clin_drug_records
                Type: DECIMAL
              - Name: num_clin_rad_records
                Type: DECIMAL
              - Name: num_clin_fol_records
                Type: DECIMAL
              - Name: num_clin_omf_records
                Type: DECIMAL
              - Name: num_clin_nte_records
                Type: DECIMAL
      LogicalTableMap:
        LogicalTable1:
          Alias: clinical_patient
          DataTransforms:
            - RenameColumnOperation:
                ColumnName: patient_id
                NewColumnName: patient_id[clinical_patient]
            - RenameColumnOperation:
                ColumnName: partition_0
                NewColumnName: study_name
          Source:
            PhysicalTableId: PhysicalTable1
        LogicalTable2:
          Alias: tcga_summary
          DataTransforms:
            - CastColumnTypeOperation:
                ColumnName: num_images
                NewColumnType: INTEGER
            - CastColumnTypeOperation:
                ColumnName: num_image_series
                NewColumnType: INTEGER
            - CastColumnTypeOperation:
                ColumnName: num_mutation_records
                NewColumnType: INTEGER
            - CastColumnTypeOperation:
                ColumnName: num_expression_records
                NewColumnType: INTEGER
            - CastColumnTypeOperation:
                ColumnName: num_cnv_records
                NewColumnType: INTEGER
            - CastColumnTypeOperation:
                ColumnName: num_clin_drug_records
                NewColumnType: INTEGER
            - CastColumnTypeOperation:
                ColumnName: num_clin_rad_records
                NewColumnType: INTEGER
            - CastColumnTypeOperation:
                ColumnName: num_clin_fol_records
                NewColumnType: INTEGER
            - CastColumnTypeOperation:
                ColumnName: num_clin_omf_records
                NewColumnType: INTEGER
            - CastColumnTypeOperation:
                ColumnName: num_clin_nte_records
                NewColumnType: INTEGER
          Source:
            PhysicalTableId: PhysicalTable2
        LogicalTable3:
          Alias: Intermediate Table
          DataTransforms:
            - CreateColumnsOperation:
                Columns:
                  - ColumnName: age_at_dx_string
                    ColumnId: 93b9c54c-ddec-471f-b113-32faf869f61c
                    Expression: "ifelse(locate({age_at_initial_pathologic_diagnosis},\"[\") = 0,{age_at_initial_pathologic_diagnosis},\"0\")"
            - CreateColumnsOperation:
                Columns:
                  - ColumnName: age_at_dx_numeric
                    ColumnId: b38d8b30-d2dd-4c94-9d27-39e5f9fde4dc
                    Expression: "parseInt({age_at_dx_string})"
            - ProjectOperation:
                ProjectedColumns:
                  - bcr_patient_barcode
                  - form_completion_date
                  - race
                  - ethnicity
                  - location_lung_parenchyma
                  - prospective_collection
                  - retrospective_collection
                  - gender
                  - birth_days_to
                  - history_other_malignancy
                  - history_neoadjuvant_treatment
                  - residual_tumor
                  - tumor_status
                  - ajcc_staging_edition
                  - ecog_score
                  - ajcc_nodes_pathologic_pn
                  - karnofsky_score
                  - ajcc_metastasis_pathologic_pm
                  - radiation_treatment_adjuvant
                  - vital_status
                  - pharmaceutical_tx_adjuvant
                  - tobacco_smoking_history_indicator
                  - last_contact_days_to
                  - tobacco_smoking_pack_years_smoked
                  - death_days_to
                  - pulmonary_function_test_indicator
                  - fev1_percent_ref_prebroncholiator
                  - fev1_percent_ref_postbroncholiator
                  - fev1_fvc_ratio_prebroncholiator
                  - fev1_fvc_ratio_postbroncholiator
                  - carbon_monoxide_diffusion_dlco
                  - kras_gene_analysis_indicator
                  - kras_mutation_found
                  - kras_mutation_identified_type
                  - egfr_mutation_status
                  - egfr_mutation_identified_type
                  - eml4_alk_translocation_status
                  - eml4_alk_analysis_type
                  - tobacco_smoking_year_started
                  - tobacco_smoking_year_stopped
                  - histologic_diagnosis67
                  - performance_status_timing
                  - anatomic_organ_subdivision
                  - site_of_primary_tumor_other
                  - treatment_outcome_first_course
                  - new_tumor_event_dx_indicator
                  - initial_pathologic_dx_year
                  - ajcc_tumor_pathologic_pt
                  - ajcc_pathologic_tumor_stage
                  - age_at_initial_pathologic_diagnosis
                  - disease_code
                  - icd_10
                  - icd_o_3_histology
                  - icd_o_3_site
                  - patient_id[clinical_patient]
                  - tissue_source_site
                  - histologic_diagnosis18
                  - anatomic_neoplasm_subdivision_other
                  - study_name
                  - num_images
                  - num_image_series
                  - num_mutation_records
                  - num_expression_records
                  - num_cnv_records
                  - num_clin_drug_records
                  - num_clin_rad_records
                  - num_clin_fol_records
                  - num_clin_omf_records
                  - num_clin_nte_records
                  - age_at_dx_numeric
                  - age_at_dx_string
          Source:
            JoinInstruction:
              LeftOperand: LogicalTable1
              RightOperand: LogicalTable2
              Type: LEFT
              OnClause: "{bcr_patient_barcode} = {patient_id}"
      Permissions:
        - Principal: !Sub "arn:aws:quicksight:${AWS::Region}:${AWS::AccountId}:user/default/${QuicksightUserId}"
          Actions:
            - quicksight:UpdateDataSetPermissions
            - quicksight:DescribeDataSet
            - quicksight:DescribeDataSetPermissions
            - quicksight:PassDataSet
            - quicksight:DescribeIngestion
            - quicksight:ListIngestions
            - quicksight:UpdateDataSet
            - quicksight:DeleteDataSet
            - quicksight:CreateIngestion
            - quicksight:CancelIngestion
