USE EnVisage2
GO

if exists (select * from sys.all_objects where name like 'AccessGroup' and type = 'U')
begin
DROP TABLE [AccessGroup]
end

if exists (select * from sys.all_objects where name like 'User2AccessGroup' and type = 'U')
begin
DROP TABLE User2AccessGroup
end

if exists (select * from sys.all_objects where name like 'FK_Security_SecurityObject' and parent_object_id = OBJECT_ID('Security') and type = 'F ')
begin
ALTER TABLE [Security] DROP CONSTRAINT FK_Security_SecurityObject
end

if exists (select * from sys.all_objects where name like 'SecurityObject' and type = 'U')
begin
DROP TABLE SecurityObject
end

DELETE FROM [Security]
GO

IF NOT EXISTS (SELECT Id FROM AspNetRoles WHERE Id = '156d9357-9563-49b0-a8ca-fc0dbd86bb17')
INSERT INTO AspNetRoles VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 'Administrator')
GO

IF NOT EXISTS (SELECT Id FROM AspNetUsers WHERE Id = 'b08956da-bd79-452b-8ce9-6efe12764b53')
INSERT INTO AspNetUsers VALUES ('b08956da-bd79-452b-8ce9-6efe12764b53', 'admin', 'AOoPHwCkgPZpb37HImNnpTqwhVmuwzjdLAg1qdTzvGow3qngsYhfwZa5iuNqiHASTg==', 
'1418d494-e5b6-4e99-b68a-94ac8f18cf68', 'ApplicationUser', 'test@tulasoft.com')
GO

if exists (select * from sys.all_columns where name like 'AccessModifier')
begin
ALTER TABLE [Security] DROP COLUMN AccessModifier
end

ALTER TABLE [Security] ADD SecurityObject NVARCHAR(50) NOT NULL
GO

if exists (select * from sys.all_objects where name like 'PK_Security' and parent_object_id = OBJECT_ID('Security') and type = 'PK')
begin
	ALTER TABLE dbo.[Security] DROP CONSTRAINT PK_Security
end

ALTER TABLE dbo.[Security] ADD CONSTRAINT
	PK_Security PRIMARY KEY CLUSTERED 
	(
	PrincipalId, SecurityObject
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

if exists (select * from sys.all_columns where name like 'SecurityObjectId')
begin
ALTER TABLE [Security] DROP COLUMN SecurityObjectId
end

INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'CreditDepartments')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'ProjectClassifications')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'Scenarios')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'FiscalCalendar')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'ImportActuals')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'Expenditures')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'Projects')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'GLDepartments')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'Roles')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'Portfolio')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'Company')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'Clients')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'Dashboard')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'Users')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'ProjectStatuses')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'Reports')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'ScenarioTemplates')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'ExpenditureCategories')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'ScheduleBoard')
GO
INSERT INTO [Security] ([PrincipalId], [Read], [Write], [SecurityObject]) VALUES ('156d9357-9563-49b0-a8ca-fc0dbd86bb17', 1, 1, 'UnitsOfMeasure')
GO