USE [EnVisage]
GO

begin tran
if exists (select 1 from sys.tables where name like 'Department')
begin
	exec sp_rename 'dbo.Department', 'View'
end
if exists (select 1 from sys.all_columns where name like 'DepartmentId' and object_id = OBJECT_ID('Department2CostCenter'))
begin
	exec sp_rename 'dbo.Department2CostCenter.DepartmentId', 'ViewId', 'COLUMN'
end
if exists (select 1 from sys.tables where name like 'Department2CostCenter')
begin
	exec sp_rename 'dbo.Department2CostCenter', 'View2CostCenter'
end
if exists (select 1 from sys.all_columns where name like 'DepartmentId' and object_id = OBJECT_ID('User2GLDepartment'))
begin
	exec sp_rename 'dbo.User2GLDepartment.DepartmentId', 'ViewId', 'COLUMN'
end
if exists (select 1 from sys.tables where name like 'User2GLDepartment')
begin
	exec sp_rename 'dbo.User2GLDepartment', 'User2View'
end
if exists (select 1 from sys.all_columns where name like 'DepartmentId' and object_id = OBJECT_ID('Company2GLDepartment'))
begin
	exec sp_rename 'dbo.Company2GLDepartment.DepartmentId', 'ViewId', 'COLUMN'
end
if exists (select 1 from sys.tables where name like 'Company2GLDepartment')
begin
	exec sp_rename 'dbo.Company2GLDepartment', 'Company2View'
end

if exists (select * from sys.foreign_keys where name like 'FK_Team2View_TeamId')
BEGIN
	ALTER TABLE [dbo].[Team2View] DROP CONSTRAINT [FK_Team2View_TeamId]
END
if exists (select * from sys.foreign_keys where name like 'FK_Team2View_ViewId')
BEGIN
	ALTER TABLE [dbo].[Team2View] DROP CONSTRAINT [FK_Team2View_ViewId]
END
if not exists (select * from sys.tables where name like 'Team2View')
BEGIN
	CREATE TABLE [dbo].[Team2View](
		[Id] [uniqueidentifier] NOT NULL,
		[TeamId] [uniqueidentifier] NOT NULL,
		[ViewId] [uniqueidentifier] NOT NULL,
		CONSTRAINT [PK_Team2View] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	

	ALTER TABLE [dbo].[Team2View]  WITH CHECK ADD CONSTRAINT [FK_Team2View_View] FOREIGN KEY([ViewId])
	REFERENCES [dbo].[View] ([Id])

	ALTER TABLE [dbo].[Team2View] CHECK CONSTRAINT [FK_Team2View_View]

	ALTER TABLE [dbo].[Team2View]  WITH CHECK ADD  CONSTRAINT [FK_Team2View_TeamId] FOREIGN KEY([TeamId])
	REFERENCES [dbo].[Team] ([Id])

	ALTER TABLE [dbo].[Team2View] CHECK CONSTRAINT [FK_Team2View_TeamId]
END

update Security set SecurityObject='Views' where SecurityObject like 'Departments'
update Security set SecurityObject='ViewDashboard' where SecurityObject like 'DepartmentDashboard'
update AspNetUsers set PagePreferences = REPLACE(PagePreferences, 'Department_Board', 'View_Board') where PagePreferences like '%Department_Board%'

if exists (select 1 from sys.all_columns where name like 'departmentid' and object_id = OBJECT_ID('Team'))
BEGIN
exec sp_sqlexec 'insert into Team2View
	select newid(), t.Id, t.DepartmentId
	from Team t
	where t.DepartmentId is not null and not exists (select 1 from Team2View tv where tv.TeamId = t.Id and tv.ViewId = t.DepartmentId)'

	if exists (select * from sys.foreign_keys where name like 'FK_Team_Department')
	BEGIN
		ALTER TABLE [dbo].[Team] DROP CONSTRAINT [FK_Team_Department]
	END

	alter table team
		drop column departmentId
END
commit tran
--rollback tran
--select * from AspNetUsers where PagePreferences like '%department%'