USE CanvasWarehouse
GO

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

CREATE TABLE [dbo].[Canvas-v2-enrollments-staging]
(
	id bigint NOT NULL
	, sis_batch_id bigint NULL
	, user_id bigint NOT NULL
	, created_at varchar(max) NOT NULL
	, updated_at varchar(max) NOT NULL
	, workflow_state varchar(max) NOT NULL
	, role_id bigint NOT NULL
	, start_at varchar(max) NULL
	, end_at varchar(max) NULL
	, course_id bigint NOT NULL
	, completed_at varchar(max) NULL
	, course_section_id bigint NOT NULL
	, grade_publishing_status varchar(max) NOT NULL
	, associated_user_id bigint NULL
	, self_enrolled varchar(max) NULL
	, limit_privileges_to_course_section varchar(max) NOT NULL
	, last_activity_at varchar(max) NULL
	, total_activity_time bigint NULL
	, sis_pseudonym_id bigint NULL
	, last_attended_at varchar(max) NULL
	, type varchar(max) NOT NULL
	, meta_ts varchar(max) NOT NULL
)
GO

BULK INSERT [dbo].[Canvas-v2-enrollments-staging]
FROM
	'F:\Canvas\unpackedFiles\enrollments.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-enrollments]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[Canvas-v2-enrollments]
END
;

CREATE TABLE [dbo].[Canvas-v2-enrollments]
(
	id bigint NOT NULL
	, sis_batch_id bigint NULL
	, user_id bigint NOT NULL
	, created_at datetime NOT NULL
	, updated_at datetime NOT NULL
	, workflow_state varchar(256) NOT NULL
	, role_id bigint NOT NULL
	, start_at datetime NULL
	, end_at datetime NULL
	, course_id bigint NOT NULL
	, completed_at datetime NULL
	, course_section_id bigint NOT NULL
	, grade_publishing_status varchar(256) NOT NULL
	, associated_user_id bigint NULL
	, self_enrolled bit NULL
	, limit_privileges_to_course_section bit NOT NULL
	, last_activity_at datetime NULL
	, total_activity_time bigint NULL
	, sis_pseudonym_id bigint NULL
	, last_attended_at datetime NULL
	, type varchar(max) NOT NULL
	, meta_ts datetime NOT NULL
)
GO

INSERT INTO [dbo].[Canvas-v2-enrollments]
SELECT
	id
	, sis_batch_id
	, user_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
	, role_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
	, CASE
		WHEN end_at IS NULL THEN NULL
		WHEN LEFT(end_at, 4) < 1900 THEN NULL
		ELSE CAST(end_at AS datetime)
		END as end_at
	, course_id
	, CASE
		WHEN completed_at IS NULL THEN NULL
		WHEN LEFT(completed_at, 4) < 1900 THEN NULL
		ELSE CAST(completed_at AS datetime)
		END as completed_at
	, course_section_id
	, grade_publishing_status
	, associated_user_id
	, CAST(
		CASE
			WHEN self_enrolled LIKE 'TRUE' THEN 1
			WHEN self_enrolled LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as self_enrolled
	, CAST(
		CASE
			WHEN limit_privileges_to_course_section LIKE 'TRUE' THEN 1
			WHEN limit_privileges_to_course_section LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as limit_privileges_to_course_section
	, CASE
		WHEN last_activity_at IS NULL THEN NULL
		WHEN LEFT(last_activity_at, 4) < 1900 THEN NULL
		ELSE CAST(last_activity_at AS datetime)
		END as last_activity_at
	, total_activity_time
	, sis_pseudonym_id
	, CASE
		WHEN last_attended_at IS NULL THEN NULL
		WHEN LEFT(last_attended_at, 4) < 1900 THEN NULL
		ELSE CAST(last_attended_at AS datetime)
		END as last_attended_at
	, type
	, 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-enrollments-staging]

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