#####
#
#  Name: 	Canvas.API.SelectedTableDownload
#  Author:	Joseph Gullo, California Northstate University
#
#  Description: This script selectively downloads tables from Canvas using
#    the canvas data cli then prepares those downloads for import into
#    Microsoft SQL Server
#  
#  Dependencies: 
#	Canvas Data CLI: https://github.com/instructure/canvas-data-cli#readme
#	NPM: https://docs.npmjs.com/downloading-and-installing-node-js-and-npm
#
#  Usage:
#    Edit this script with an updated link to the config.js file (configured
#    per the README in the linked github repository) and the location of
#    unpacked files, which will be defined in the config.js file.  Take a
#    look at the list of tables and comment out or uncomment any tables
#    that are desired for import.  Once these are downloaded, re-running
#    this script will update the downloaded data and prepare it for import
#    with a SQL bulk insert statement, which can be run as the SQL server
#    agent.
#
#####

$ConfigPath = 'G:\Canvas\config.js'
$UnpackedPath = 'G:\Canvas\unpackedFiles\'
$SchemaFile = 'G:\Canvas\dataFiles\schema.json'
$TableList = @(
	"course_dim"
	, "account_dim"
	, "user_dim"
	, "pseudonym_dim"
	#, "pseudonym_fact"
	#, "assignment_dim"
	#, "assignment_fact"
	#, "assignment_rule_dim"
	#, "submission_dim"
	#, "submission_fact"
	#, "submission_comment_fact"
	#, "submission_comment_dim"
	#, "assignment_group_dim"
	#, "assignment_group_fact"
	#, "assignment_group_rule_dim"
	#, "assignment_override_user_dim"
	#, "assignment_override_user_fact"
	#, "assignment_override_dim"
	#, "assignment_override_fact"
	#, "assignment_override_user_rollup_fact"
	#, "submission_file_fact"
	#, "catalog_dim"
	#, "catalog_product_dim"
	#, "catalog_product_fact"
	#, "catalog_enrollment_dim"
	#, "catalog_user_registration_dim"
	#, "catalog_program_requirement_fact"
	#, "catalog_order_dim"
	#, "catalog_order_fact"
	#, "catalog_order_item_dim"
	#, "catalog_order_item_fact"
	#, "catalog_applicant_dim"
	#, "catalog_payment_dim"
	#, "catalog_payment_fact"
	#, "catalog_promotion_dim"
	#, "catalog_promotion_fact"
	#, "catalog_tag_dim"
	#, "catalog_product_tag_dim"
	#, "communication_channel_dim"
	#, "communication_channel_fact"
	#, "conference_dim"
	#, "conference_fact"
	#, "conference_participant_dim"
	#, "conference_participant_fact"
	#, "conversation_dim"
	#, "conversation_message_dim"
	#, "conversation_message_participant_fact"
	#, "discussion_topic_dim"
	#, "discussion_topic_fact"
	#, "discussion_entry_dim"
	#, "discussion_entry_fact"
	, "enrollment_term_dim"
	#, "course_section_dim"
	, "role_dim"
	, "enrollment_dim"
	, "enrollment_fact"
	#, "enrollment_rollup_dim"
	#, "assignment_group_score_fact"
	, "course_score_fact"
	#, "grading_period_score_fact"
	#, "assignment_group_score_dim"
	, "course_score_dim"
	#, "grading_period_score_dim"
	#, "grading_period_fact"
	#, "grading_period_dim"
	#, "grading_period_group_dim"
	#, "file_dim"
	#, "file_fact"
	#, "group_dim"
	#, "group_fact"
	#, "group_membership_fact"
	#, "group_membership_dim"
	#, "module_dim"
	#, "module_fact"
	#, "module_item_dim"
	#, "module_item_fact"
	#, "module_progression_dim"
	#, "module_progression_fact"
	#, "module_completion_requirement_dim"
	#, "module_completion_requirement_fact"
	#, "module_prerequisite_dim"
	#, "module_prerequisite_fact"
	#, "module_progression_completion_requirement_dim"
	#, "module_progression_completion_requirement_fact"
	#, "course_ui_canvas_navigation_dim"
	#, "course_ui_navigation_item_dim"
	#, "course_ui_navigation_item_fact"
	#, "learning_outcome_dim"
	#, "learning_outcome_fact"
	#, "learning_outcome_rubric_criterion_dim"
	#, "learning_outcome_rubric_criterion_fact"
	#, "learning_outcome_group_dim"
	#, "learning_outcome_group_fact"
	#, "learning_outcome_group_association_fact"
	#, "learning_outcome_result_dim"
	#, "learning_outcome_result_fact"
	#, "learning_outcome_question_result_dim"
	#, "learning_outcome_question_result_fact"
	#, "quiz_dim"
	#, "quiz_fact"
	#, "quiz_submission_historical_dim"
	#, "quiz_submission_historical_fact"
	#, "quiz_submission_dim"
	#, "quiz_submission_fact"
	#, "quiz_question_group_dim"
	#, "quiz_question_group_fact"
	#, "quiz_question_dim"
	#, "quiz_question_fact"
	#, "quiz_question_answer_dim"
	#, "quiz_question_answer_fact"
	#, "requests"
	#, "external_tool_activation_dim"
	#, "external_tool_activation_fact"
	#, "wiki_dim"
	#, "wiki_fact"
	#, "wiki_page_dim"
	#, "wiki_page_fact"
)

# Download the latest version of schema.json
invoke-webrequest https://portal.inshosteddata.com/api/schema/latest -Outfile $SchemaFile

forEach ($Table in $TableList) {

	$UnpackedFilePath = $UnpackedPath + $Table + ".txt"

	# Fetch the compressed Canvas data file to the location defined in config.js
	& "C:\node-modules\canvasDataCli.ps1" fetch -c $ConfigPath -t $Table
	
	# Unpack the compressed Canvas data file to the location defined in canvas.js
	& "C:\node-modules\canvasDataCli.ps1" unpack -c $ConfigPath -f $Table
	
	# Process the uncompressed files, converting "\N" to blank representing a NULL for import into Microsoft SQL
	((Get-Content -path $UnpackedFilePath -Raw) -replace '\\N','') | Set-Content -Path $UnpackedFilePath
	
	# Convert line endings and unneeded blank lines for import into Microsoft SQL
	(Get-Content -path $UnpackedFilePath) | ? {$_.trim() -ne "" } | Set-Content -Path $UnpackedFilePath
}