USE CanvasWarehouse
GO

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

CREATE TABLE [dbo].[Canvas-v2-users-staging]
(
	id bigint NOT NULL
	, deleted_at varchar(max) NULL
	, storage_quota bigint NULL
	, lti_context_id varchar(256) NOT NULL
	, created_at varchar(max) NOT NULL
	, updated_at varchar(max) NOT NULL
	, workflow_state varchar(256) NOT NULL
	, sortable_name varchar(256) NOT NULL
	, avatar_image_url varchar(256) NOT NULL
	, avatar_image_source varchar(256) NOT NULL
	, avatar_image_updated_at varchar(max) NULL
	, short_name varchar(256) NOT NULL
	, last_logged_out varchar(max) NULL
	, pronouns varchar(max) NOT NULL
	, merged_into_user_id bigint NULL
	, locale varchar(256) NOT NULL
	, name varchar(256) NOT NULL
	, time_zone varchar(256) NOT NULL
	, uuid varchar(256) NOT NULL
	, school_name varchar(256) NOT NULL
	, school_position varchar(256) NOT NULL
	, public_flag varchar(max) NULL
	, meta_ts varchar(max) NOT NULL
)
GO

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

CREATE TABLE [dbo].[Canvas-v2-users]
(
	id bigint NOT NULL
	, deleted_at datetime NULL
	, storage_quota bigint NULL
	, lti_context_id varchar(256) NOT NULL
	, created_at datetime NOT NULL
	, updated_at datetime NOT NULL
	, workflow_state varchar(256) NOT NULL
	, sortable_name varchar(256) NOT NULL
	, avatar_image_url varchar(256) NOT NULL
	, avatar_image_source varchar(256) NOT NULL
	, avatar_image_updated_at datetime NULL
	, short_name varchar(256) NOT NULL
	, last_logged_out datetime NULL
	, pronouns varchar(max) NOT NULL
	, merged_into_user_id bigint NULL
	, locale varchar(256) NOT NULL
	, name varchar(256) NOT NULL
	, time_zone varchar(256) NOT NULL
	, uuid varchar(256) NOT NULL
	, school_name varchar(256) NOT NULL
	, school_position varchar(256) NOT NULL
	, public_flag bit NULL
	, meta_ts datetime NOT NULL
)
GO

INSERT INTO [dbo].[Canvas-v2-users]
SELECT
	id
	, CASE
		WHEN deleted_at IS NULL THEN NULL
		WHEN LEFT(deleted_at, 4) < 1900 THEN NULL
		ELSE CAST(deleted_at AS datetime)
		END as deleted_at
	, storage_quota
	, lti_context_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
	, sortable_name
	, avatar_image_url
	, avatar_image_source
	, CASE
		WHEN avatar_image_updated_at IS NULL THEN NULL
		WHEN LEFT(avatar_image_updated_at, 4) < 1900 THEN NULL
		ELSE CAST(avatar_image_updated_at AS datetime)
		END as avatar_image_updated_at
	, short_name
	, CASE
		WHEN last_logged_out IS NULL THEN NULL
		WHEN LEFT(last_logged_out, 4) < 1900 THEN NULL
		ELSE CAST(last_logged_out AS datetime)
		END as last_logged_out
	, pronouns
	, merged_into_user_id
	, locale
	, name
	, time_zone
	, uuid
	, school_name
	, school_position
	, CAST(
		CASE
			WHEN public_flag LIKE 'TRUE' THEN 1
			WHEN public_flag LIKE 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as public_flag
	, 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-users-staging]

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