USE CanvasWarehouse
GO

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

CREATE TABLE [dbo].[Canvas-user_dim-staging]
(
	id bigint NOT NULL
	, canvas_id bigint NOT NULL
	, root_account_id bigint NULL
	, name varchar(256) NOT NULL
	, time_zone varchar(256) NULL
	, created_at varchar(max) NOT NULL
	, visibility varchar(256) NULL
	, school_name varchar(256) NULL
	, school_position varchar(256) NULL
	, gender varchar(256) NULL
	, locale varchar(256) NULL
	, [public] varchar(256) NOT NULL
	, birthdate varchar(max) NULL
	, country_code varchar(256) NULL
	, workflow_state varchar(256) NULL
	, sortable_name varchar(256) NOT NULL
	, global_canvas_id varchar(256) NOT NULL
)
GO

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

CREATE TABLE [dbo].[Canvas-user_dim]
(
	id bigint NOT NULL
	, canvas_id bigint NOT NULL
	, root_account_id bigint NULL
	, name varchar(256) NOT NULL
--	, time_zone varchar(256) NULL
	, created_at datetime NOT NULL
--	, visibility varchar(256) NULL
--	, school_name varchar(256) NULL
--	, school_position varchar(256) NULL
--	, gender varchar(256) NULL
--	, locale varchar(256) NULL
--	, [public] bit NOT NULL
--	, birthdate datetime NULL
--	, country_code varchar(256) NULL
	, workflow_state varchar(256) NULL
	, sortable_name varchar(256) NOT NULL
	, global_canvas_id varchar(256) NOT NULL
)
GO

INSERT INTO [dbo].[Canvas-user_dim]
SELECT
	id
	, canvas_id
	, root_account_id
	, name
--	, time_zone
	, 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
--	, visibility
--	, school_name
--	, school_position
--	, gender
--	, locale
--	, CAST(
--		CASE
--			WHEN public LIKE 'TRUE' THEN 1
--			WHEN public LIKE 'FALSE' THEN 0
--			ELSE NULL
--			END as bit
--		) as [public]
--	, CASE
--		WHEN birthdate IS NULL THEN NULL
--		WHEN LEFT(birthdate, 4) < 1900 THEN NULL
--		ELSE CAST(birthdate AS datetime)
--		END as birthdate
--	, country_code varchar(256) NULL
	, workflow_state
	, sortable_name
	, global_canvas_id
FROM
	[dbo].[Canvas-user_dim-staging]

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