USE CanvasWarehouse
GO

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

CREATE TABLE [dbo].[Canvas-pseudonym_dim-staging]
(
	id bigint NOT NULL
	, canvas_id bigint NOT NULL
	, user_id bigint NOT NULL
	, account_id bigint NOT NULL
	, workflow_state varchar(256) NOT NULL
	, last_request_at varchar(max) NULL
	, last_login_at varchar(max) NULL
	, current_login_at varchar(max) NULL
	, last_login_ip varchar(256) NULL
	, current_login_ip varchar(256) NULL
	, position int NOT NULL
	, created_at varchar(max) NOT NULL
	, updated_at varchar(max) NOT NULL
	, password_auto_generated varchar(5) NOT NULL
	, deleted_at varchar(max) NULL
	, sis_user_id varchar(256) NULL
	, unique_name varchar(256) NOT NULL
	, integration_id varchar(256) NULL
	, authentication_provider_id varchar(256) NULL
)
GO

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

CREATE TABLE [dbo].[Canvas-pseudonym_dim]
(
	id bigint NOT NULL
	, canvas_id bigint NOT NULL
	, user_id bigint NOT NULL
--	, account_id bigint NOT NULL
	, workflow_state varchar(256) NOT NULL
	, last_request_at datetime NULL
	, last_login_at datetime NULL
--	, current_login_at datetime NULL
--	, last_login_ip varchar(256) NULL
--	, current_login_ip varchar(256) NULL
	, position int NOT NULL
	, created_at datetime NOT NULL
	, updated_at datetime NOT NULL
	, password_auto_generated bit NOT NULL
	, deleted_at datetime NULL
	, sis_user_id varchar(256) NULL
	, unique_name varchar(256) NOT NULL
	, integration_id varchar(256) NULL
	, authentication_provider_id varchar(256) NULL
)
GO

INSERT INTO [dbo].[Canvas-pseudonym_dim]
SELECT
	id
	, canvas_id
	, user_id
--	, account_id
	, workflow_state
	, CASE
		WHEN last_request_at IS NULL THEN NULL
		WHEN LEFT(last_request_at, 4) < 1900 THEN NULL
		ELSE CAST(last_request_at AS datetime)
		END as last_request_at
	, CASE
		WHEN last_login_at IS NULL THEN NULL
		WHEN LEFT(last_login_at, 4) < 1900 THEN NULL
		ELSE CAST(last_login_at AS datetime)
		END as last_login_at
--	, CASE
--		WHEN current_login_at IS NULL THEN NULL
--		WHEN LEFT(current_login_at, 4) < 1900 THEN NULL
--		ELSE CAST(current_login_at AS datetime)
--		END as current_login_at
--	, last_login_ip varchar(256) NULL
--	, current_login_ip varchar(256) NULL
	, position
	, 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
	, CAST(
		CASE
			WHEN password_auto_generated = 'TRUE' THEN 1
			WHEN password_auto_generated = 'FALSE' THEN 0
			ELSE NULL
			END as bit
		) as password_auto_generated
	, 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
	, sis_user_id
	, unique_name
	, integration_id
	, authentication_provider_id
FROM
	[dbo].[Canvas-pseudonym_dim-staging]

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