USE CanvasWarehouse
GO

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

CREATE TABLE [dbo].[Canvas-enrollment_term_dim-staging]
(
	id bigint NOT NULL
	, canvas_id bigint NOT NULL
	, root_account_id bigint NOT NULL
	, name varchar(256) NOT NULL
	, date_start varchar(max) NULL
	, date_end varchar(max) NULL
	, sis_source_id varchar(256) NULL
)
GO

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

CREATE TABLE [dbo].[Canvas-enrollment_term_dim]
(
	id bigint NOT NULL
	, canvas_id bigint NOT NULL
--	, root_account_id bigint NOT NULL
	, name varchar(256) NOT NULL
	, date_start datetime NULL
	, date_end datetime NULL
	, sis_source_id varchar(256) NULL
)
GO

INSERT INTO [dbo].[Canvas-enrollment_term_dim]
SELECT
	id
	, canvas_id
--	, root_account_id
	, name
	, CASE
		WHEN date_start IS NULL THEN NULL
		WHEN LEFT(date_start, 4) < 1900 THEN NULL
		ELSE CAST(date_start AS datetime)
		END as date_start
	, CASE
		WHEN date_end IS NULL THEN NULL
		WHEN LEFT(date_end, 4) < 1900 THEN NULL
		ELSE CAST(date_end AS datetime)
		END as date_end
	, sis_source_id
FROM
	[dbo].[Canvas-enrollment_term_dim-staging]

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