IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = 'OAuthToken' AND c.COLUMN_NAME = 'TokenType'))
BEGIN
    ALTER TABLE dbo.OAuthToken  
    ADD  
        TokenType VARCHAR(50) NOT NULL DEFAULT 'STANDARD', 
        SSAEmailAddress NVARCHAR(255) NULL,  
        SSAFirstName NVARCHAR(100) NULL,  
        SSALastName NVARCHAR(100) NULL,  
        AutodeskOxygenId VARCHAR(50) NULL,  
        KeyCreationDate DATETIME NULL,  
        KeyId VARCHAR(50) NULL,  
        PrivateKey VARCHAR(MAX) NULL;
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ModelExport'))
BEGIN
    CREATE TABLE [dbo].[ModelExport](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[ClarityModelId] [uniqueidentifier] NOT NULL,
		[ModelIdType] [varchar](100) NULL,
		[ProjectId] [int] NOT NULL,
		[LastExportDateTime] [datetime] NOT NULL
	) ON [PRIMARY]
	
	ALTER TABLE [dbo].[ModelExport] ADD  CONSTRAINT [PK_ModelExport] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	
	CREATE NONCLUSTERED INDEX [Index_ModelExport_ClarityModelId] ON [dbo].[ModelExport]
	(
		[ClarityModelId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	
	CREATE NONCLUSTERED INDEX [Index_ModelExport_ProjectId] ON [dbo].[ModelExport]
	(
		[ProjectId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = 'RevitServerModels' AND c.COLUMN_NAME = 'ClarityModelId'))
BEGIN
    ALTER TABLE [dbo].[RevitServerModels] ADD [ClarityModelId] [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID() WITH VALUES;

	CREATE UNIQUE NONCLUSTERED INDEX [Index_RevitServerModels_ClarityModelId] ON [dbo].[RevitServerModels]
	(
		[ClarityModelId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

	CREATE NONCLUSTERED INDEX [Index_RevitServerModels_ProjectId] ON [dbo].[RevitServerModels]
	(
		[ProjectId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Bim360ProjectTypes'))
BEGIN
	CREATE TABLE [dbo].[Bim360ProjectTypes](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[ExternalId] [UNIQUEIDENTIFIER] NOT NULL,
	[Name] [NVARCHAR](255) NOT NULL,
	[DisplayName] [NVARCHAR](255) NOT NULL,
	[IsActive] [BIT] NOT NULL,
	[IsDefault] [BIT] NOT NULL
	) ON [PRIMARY]

	ALTER TABLE [dbo].[Bim360ProjectTypes] ADD  CONSTRAINT [PK_Bim360ProjectTypes] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

	CREATE UNIQUE NONCLUSTERED INDEX [IX_Bim360ProjectTypes_ExternalId] ON [dbo].[Bim360ProjectTypes]
	(
		[ExternalId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

	ALTER TABLE [dbo].[Bim360ProjectTypes] ADD  CONSTRAINT [DEFAULT_Bim360ProjectTypes_ExternalId]  DEFAULT (NEWSEQUENTIALID()) FOR [ExternalId]

	ALTER TABLE [dbo].[Bim360ProjectTypes] ADD  CONSTRAINT [DEFAULT_Bim360ProjectTypes_IsActive]  DEFAULT ((1)) FOR [IsActive]

	ALTER TABLE [dbo].[Bim360ProjectTypes] ADD  CONSTRAINT [DEFAULT_Bim360ProjectTypes_IsDefault]  DEFAULT ((0)) FOR [IsDefault]

END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'RevitServerModelPublished'))
BEGIN

	CREATE TABLE [dbo].[RevitServerModelPublished](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[ExternalId] [uniqueidentifier] NOT NULL,
		[RevitServerModelId] [int] NOT NULL,
		[RevitServerModelExternalId] [nvarchar](50) NULL,
		[ClarityModelId] [uniqueidentifier] NOT NULL,
		[ProjectId] [int] NOT NULL,
		[CreatedAtUtc] [datetime] NOT NULL
	) ON [PRIMARY]

	ALTER TABLE [dbo].[RevitServerModelPublished] ADD  CONSTRAINT [PK_RevitServerModelPublished] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

	CREATE NONCLUSTERED INDEX [Index_RevitServerModelPublished_ProjectId] ON [dbo].[RevitServerModelPublished]
	(
		[ProjectId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

	ALTER TABLE [dbo].[RevitServerModelPublished] ADD  CONSTRAINT [DEFAULT_RevitServerModelPublished_ExteranlId]  DEFAULT (newsequentialid()) FOR [ExternalId]

	ALTER TABLE [dbo].[RevitServerModelPublished] ADD  CONSTRAINT [DEFAULT_RevitServerModelPublished_CreatedAtUtc]  DEFAULT (getutcdate()) FOR [CreatedAtUtc]

	ALTER TABLE [dbo].[RevitServerModelPublished]  WITH CHECK ADD  CONSTRAINT [FK_RevitServerModelPublished_RevitServerModels] FOREIGN KEY([RevitServerModelId])
	REFERENCES [dbo].[RevitServerModels] ([Id])
	ON DELETE CASCADE

	ALTER TABLE [dbo].[RevitServerModelPublished] CHECK CONSTRAINT [FK_RevitServerModelPublished_RevitServerModels]

END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Notification'))
BEGIN

	CREATE TABLE [dbo].[Notification](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ExternalId] [uniqueidentifier] NOT NULL,
	[CreatedAt] [datetime] NOT NULL,
	[DoNotShowAfter] [datetime] NULL,
	[DaysToNotify] [int] NULL,
	[Text] [nvarchar](4000) NULL,
	[EntityName] [nvarchar](255) NULL,
	[EntityId] [int] NULL,
	[EntityTarget] [nvarchar](255) NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Notification] ADD  CONSTRAINT [PK_Notification] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

ALTER TABLE [dbo].[Notification] ADD  CONSTRAINT [DF_Notification_ExternalId]  DEFAULT (newid()) FOR [ExternalId]

ALTER TABLE [dbo].[Notification] ADD  CONSTRAINT [DF_Notification_CreatedAt]  DEFAULT (getutcdate()) FOR [CreatedAt]

END
