USE CanvasWarehouse
GO

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

CREATE TABLE [dbo].[Canvas-v2-enrollment_states-staging]
(
	enrollment_id bigint NOT NULL
	, updated_at varchar(max) NULL
	, state_is_current varchar(max) NOT NULL
	, state_started_at varchar(max) NULL
	, state_valid_until varchar(max) NULL
	, restricted_access varchar(max) NOT NULL
	, access_is_current varchar(max) NOT NULL
	, state varchar(max) NOT NULL
	, meta_ts varchar(256) NOT NULL
)
GO

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

CREATE TABLE [dbo].[Canvas-v2-enrollment_states]
(
	enrollment_id bigint NOT NULL
	, updated_at datetime NULL
	, state_is_current bit NOT NULL
	, state_started_at datetime NULL
	, state_valid_until datetime NULL
	, restricted_access bit NOT NULL
	, access_is_current bit NOT NULL
	, state varchar(max) NOT NULL
	, meta_ts datetime NOT NULL
)
GO

INSERT INTO [dbo].[Canvas-v2-enrollment_states]
SELECT
	enrollment_id
	, 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
	, CAST(
		CASE
			WHEN state_is_current LIKE 'TRUE' THEN 1
			WHEN state_is_current LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as state_is_current
	, CASE
		WHEN state_started_at IS NULL THEN NULL
		WHEN LEFT(state_started_at, 4) < 1900 THEN NULL
		ELSE CAST(state_started_at AS datetime)
		END as state_started_at
	, CASE
		WHEN state_valid_until IS NULL THEN NULL
		WHEN LEFT(state_valid_until, 4) < 1900 THEN NULL
		ELSE CAST(state_valid_until AS datetime)
		END as state_valid_until
	, CAST(
		CASE
			WHEN restricted_access LIKE 'TRUE' THEN 1
			WHEN restricted_access LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as restricted_access
	, CAST(
		CASE
			WHEN access_is_current LIKE 'TRUE' THEN 1
			WHEN access_is_current LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as access_is_current
	, state
	, 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-enrollment_states-staging]

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