USE CanvasWarehouse
GO

IF 
	OBJECT_ID(N'[dbo].[Canvas-v2-courses-staging]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[Canvas-v2-courses-staging]
END
;

CREATE TABLE [dbo].[Canvas-v2-courses-staging]
(
	id bigint NOT NULL
	, storage_quota bigint NULL
	, integration_id varchar(256) NULL
	, lti_context_id varchar(256) NULL
	, sis_batch_id varchar(256) NULL
	, created_at varchar(max) NOT NULL
	, updated_at varchar(max) NOT NULL
	, workflow_state varchar(256) NOT NULL
	, account_id bigint NOT NULL
	, grading_standard_id bigint NULL
	, start_at varchar(256) NULL
	, sis_source_id varchar(256) NULL
	, group_weighting_scheme varchar(256) NULL
	, conclude_at varchar(256) NULL
	, is_public varchar(256) NULL
	, allow_student_wiki_edits varchar(256) NULL
	, syllabus_body varchar(max) NULL
	, default_wiki_editing_roles varchar(256) NULL
	, wiki_id bigint NULL
	, allow_student_organized_groups varchar(256) NOT NULL
	, course_code varchar(256) NULL
	, default_view varchar(256) NULL
	, abstract_course_id bigint NULL
	, enrollment_term_id bigint NULL
	, open_enrollment varchar(256) NULL
	, tab_configuration varchar(max) NULL
	, turnitin_comments varchar(max) NULL
	, self_enrollment varchar(256) NULL
	, license varchar(256) NULL
	, indexed varchar(256) NULL
	, restrict_enrollments_to_course_dates varchar(256) NULL
	, template_course_id bigint NULL
	, replacement_course_id bigint NULL
	, public_description varchar(max) NULL
	, self_enrollment_code varchar(256) NULL
	, self_enrollment_limit bigint NULL
	, turnitin_id bigint NULL
	, show_announcements_on_home_page varchar(256) NULL
	, home_page_announcement_limit bigint NULL
	, latest_outcome_import_id bigint NULL
	, grade_passback_setting varchar(256) NULL
	, template varchar(256) NOT NULL
	, homeroom_course varchar(256) NOT NULL
	, sync_enrollments_from_homeroom varchar(256) NOT NULL
	, homeroom_course_id bigint NULL
	, locale varchar(256) NULL
	, name varchar(256) NULL
	, time_zone varchar(256) NULL
	, uuid varchar(256) NULL
	, settings_allow_student_discussion_editing varchar(max) NULL
	, settings_allow_student_discussion_topics varchar(max) NULL
	, settings_course_format varchar(max) NULL
	, settings_filter_speed_grader_by_student_group varchar(max) NULL
	, settings_hide_distribution_graphs varchar(max) NULL
	, settings_hide_final_grade varchar(max) NULL
	, settings_is_public_to_auth_users varchar(max) NULL
	, settings_lock_all_announcements varchar(max) NULL
	, settings_public_syllabus varchar(max) NULL
	, settings_public_syllabus_to_auth varchar(max) NULL
	, settings_restrict_student_future_view varchar(max) NULL
	, settings_restrict_student_past_view varchar(max) NULL
	, settings_syllabus_updated_at varchar(max) NULL
	, settings_usage_rights_required varchar(max) NULL
	, settings_allow_student_forum_attachments varchar(max) NULL
	, meta_ts varchar(256) NULL
)
GO

BULK INSERT [dbo].[Canvas-v2-courses-staging]
FROM
	'F:\Canvas\unpackedFiles\courses.0.tsv'
WITH
	(
		FIRSTROW = 2
		, FIELDTERMINATOR = '\t'
		--, ROWTERMINATOR = '\r\n'
		-- This is due to unix encoding, the row terminator is not \n
		, ROWTERMINATOR = '0x0A'
		, KEEPNULLS
	)
GO

IF 
	OBJECT_ID(N'[dbo].[Canvas-v2-courses]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[Canvas-v2-courses]
END
;

CREATE TABLE [dbo].[Canvas-v2-courses]
(
	id bigint NOT NULL
	, storage_quota bigint NULL
	, integration_id varchar(256) NULL
	, lti_context_id varchar(256) NULL
	, sis_batch_id varchar(256) NULL
	, created_at  datetime NOT NULL
	, updated_at datetime NOT NULL
	, workflow_state varchar(256) NOT NULL
	, account_id bigint NOT NULL
	, grading_standard_id bigint NULL
	, start_at datetime NULL
	, sis_source_id varchar(256) NULL
	, group_weighting_scheme varchar(256) NULL
	, conclude_at datetime NULL
	, is_public bit NULL
	, allow_student_wiki_edits bit NULL
	, syllabus_body varchar(max) NULL
	, default_wiki_editing_roles varchar(256) NULL
	, wiki_id bigint NULL
	, allow_student_organized_groups bit NOT NULL
	, course_code varchar(256) NULL
	, default_view varchar(256) NULL
	, abstract_course_id bigint NULL
	, enrollment_term_id bigint NULL
	, open_enrollment bit NULL
	, tab_configuration varchar(max) NULL
	, turnitin_comments varchar(max) NULL
	, self_enrollment bit NULL
	, license varchar(256) NULL
	, indexed bit NULL
	, restrict_enrollments_to_course_dates bit NULL
	, template_course_id bigint NULL
	, replacement_course_id bigint NULL
	, public_description varchar(max) NULL
	, self_enrollment_code varchar(256) NULL
	, self_enrollment_limit bigint NULL
	, turnitin_id bigint NULL
	, show_announcements_on_home_page bit NULL
	, home_page_announcement_limit bigint NULL
	, latest_outcome_import_id bigint NULL
	, grade_passback_setting varchar(256) NULL
	, template bit NOT NULL
	, homeroom_course bit NOT NULL
	, sync_enrollments_from_homeroom bit NOT NULL
	, homeroom_course_id bigint NULL
	, locale varchar(256) NULL
	, name varchar(256) NULL
	, time_zone varchar(256) NULL
	, uuid varchar(256) NULL
	, settings_allow_student_discussion_editing varchar(max) NULL
	, settings_allow_student_discussion_topics varchar(max) NULL
	, settings_course_format varchar(max) NULL
	, settings_filter_speed_grader_by_student_group varchar(max) NULL
	, settings_hide_distribution_graphs varchar(max) NULL
	, settings_hide_final_grade varchar(max) NULL
	, settings_is_public_to_auth_users varchar(max) NULL
	, settings_lock_all_announcements varchar(max) NULL
	, settings_public_syllabus varchar(max) NULL
	, settings_public_syllabus_to_auth varchar(max) NULL
	, settings_restrict_student_future_view varchar(max) NULL
	, settings_restrict_student_past_view varchar(max) NULL
	, settings_syllabus_updated_at datetime NULL
	, settings_usage_rights_required varchar(max) NULL
	, settings_allow_student_forum_attachments varchar(max) NULL
	, meta_ts datetime NULL
)
GO

INSERT INTO [dbo].[Canvas-v2-courses]
SELECT
	id
	, storage_quota
	, integration_id
	, lti_context_id
	, sis_batch_id
	, CASE
		WHEN created_at IS NULL THEN NULL
		WHEN LEFT(created_at, 4) < 1900 THEN NULL
		ELSE CAST(created_at AS datetime)
		END as created_at
	, CASE
		WHEN updated_at IS NULL THEN NULL
		WHEN LEFT(updated_at, 4) < 1900 THEN NULL
		ELSE CAST(updated_at AS datetime)
		END as updated_at
	, workflow_state
	, account_id
	, grading_standard_id
	, CASE
		WHEN start_at IS NULL THEN NULL
		WHEN LEFT(start_at, 4) < 1900 THEN NULL
		ELSE CAST(start_at AS datetime)
		END as start_at
	, sis_source_id
	, group_weighting_scheme
	, CASE
		WHEN conclude_at IS NULL THEN NULL
		WHEN LEFT(conclude_at, 4) < 1900 THEN NULL
		ELSE CAST(conclude_at AS datetime)
		END as conclude_at
	, CAST(
		CASE
			WHEN is_public LIKE 'TRUE' THEN 1
			WHEN is_public LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as is_public
	, CAST(
		CASE
			WHEN allow_student_wiki_edits LIKE 'TRUE' THEN 1
			WHEN allow_student_wiki_edits LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as allow_student_wiki_edits
	, syllabus_body
	, default_wiki_editing_roles
	, wiki_id
	, CAST(
		CASE
			WHEN allow_student_organized_groups LIKE 'TRUE' THEN 1
			WHEN allow_student_organized_groups LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as allow_student_organized_groups
	, course_code
	, default_view
	, abstract_course_id
	, enrollment_term_id
	, CAST(
		CASE
			WHEN open_enrollment LIKE 'TRUE' THEN 1
			WHEN open_enrollment LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as open_enrollment
	, tab_configuration
	, turnitin_comments
	, CAST(
		CASE
			WHEN self_enrollment LIKE 'TRUE' THEN 1
			WHEN self_enrollment LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as self_enrollment
	, license
	, CAST(
		CASE
			WHEN indexed LIKE 'TRUE' THEN 1
			WHEN indexed LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as indexed
	, CAST(
		CASE
			WHEN restrict_enrollments_to_course_dates LIKE 'TRUE' THEN 1
			WHEN restrict_enrollments_to_course_dates LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as restrict_enrollments_to_course_dates
	, template_course_id
	, replacement_course_id
	, public_description
	, self_enrollment_code
	, self_enrollment_limit
	, turnitin_id
	, CAST(
		CASE
			WHEN show_announcements_on_home_page LIKE 'TRUE' THEN 1
			WHEN show_announcements_on_home_page LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as show_announcements_on_home_page
	, home_page_announcement_limit
	, latest_outcome_import_id
	, grade_passback_setting
	, CAST(
		CASE
			WHEN template LIKE 'TRUE' THEN 1
			WHEN template LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as template
	, CAST(
		CASE
			WHEN homeroom_course LIKE 'TRUE' THEN 1
			WHEN homeroom_course LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as homeroom_course
	, CAST(
		CASE
			WHEN sync_enrollments_from_homeroom LIKE 'TRUE' THEN 1
			WHEN sync_enrollments_from_homeroom LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as sync_enrollments_from_homeroom
	, homeroom_course_id
	, locale
	, name
	, time_zone
	, uuid
	, CAST(
		CASE
			WHEN settings_allow_student_discussion_editing LIKE 'TRUE' THEN 1
			WHEN settings_allow_student_discussion_editing LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_allow_student_discussion_editing
	, CAST(
		CASE
			WHEN settings_allow_student_discussion_topics LIKE 'TRUE' THEN 1
			WHEN settings_allow_student_discussion_topics LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_allow_student_discussion_topics
	, settings_course_format
	, CAST(
		CASE
			WHEN settings_filter_speed_grader_by_student_group LIKE 'TRUE' THEN 1
			WHEN settings_filter_speed_grader_by_student_group LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_filter_speed_grader_by_student_group
	, CAST(
		CASE
			WHEN settings_hide_distribution_graphs LIKE 'TRUE' THEN 1
			WHEN settings_hide_distribution_graphs LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_hide_distribution_graphs
	, CAST(
		CASE
			WHEN settings_hide_final_grade LIKE 'TRUE' THEN 1
			WHEN settings_hide_final_grade LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_hide_final_grade
	, CAST(
		CASE
			WHEN settings_is_public_to_auth_users LIKE 'TRUE' THEN 1
			WHEN settings_is_public_to_auth_users LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_is_public_to_auth_users
	, CAST(
		CASE
			WHEN settings_lock_all_announcements LIKE 'TRUE' THEN 1
			WHEN settings_lock_all_announcements LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_lock_all_announcements
	, CAST(
		CASE
			WHEN settings_public_syllabus LIKE 'TRUE' THEN 1
			WHEN settings_public_syllabus LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_public_syllabus
	, CAST(
		CASE
			WHEN settings_public_syllabus_to_auth LIKE 'TRUE' THEN 1
			WHEN settings_public_syllabus_to_auth LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_public_syllabus_to_auth
	, CAST(
		CASE
			WHEN settings_restrict_student_future_view LIKE 'TRUE' THEN 1
			WHEN settings_restrict_student_future_view LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_restrict_student_future_view
	, CAST(
		CASE
			WHEN settings_restrict_student_past_view LIKE 'TRUE' THEN 1
			WHEN settings_restrict_student_past_view LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_restrict_student_past_view
	, CASE
		WHEN settings_syllabus_updated_at IS NULL THEN NULL
		WHEN LEFT(REPLACE(REPLACE(settings_syllabus_updated_at,'"',''),' UTC',''), 4) < 1900 THEN NULL
		ELSE CAST(REPLACE(REPLACE(settings_syllabus_updated_at,'"',''),' UTC','') AS datetime)
		END as settings_syllabus_updated_at
	, CAST(
		CASE
			WHEN settings_usage_rights_required LIKE 'TRUE' THEN 1
			WHEN settings_usage_rights_required LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_usage_rights_required
	, CAST(
		CASE
			WHEN settings_allow_student_forum_attachments LIKE 'TRUE' THEN 1
			WHEN settings_allow_student_forum_attachments LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as settings_allow_student_forum_attachments
	, CASE
		WHEN meta_ts IS NULL THEN NULL
		WHEN LEFT(meta_ts, 4) < 1900 THEN NULL
		ELSE CAST(REPLACE(REPLACE(REPLACE(meta_ts,CHAR(9),''),CHAR(10),''),CHAR(13),'') AS datetime)
		END as meta_ts
FROM
	[dbo].[Canvas-v2-courses-staging]

IF 
	OBJECT_ID(N'[dbo].[Canvas-v2-courses-staging]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[Canvas-v2-courses-staging]
END
;