USE [EnVisage]
GO
IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_NAME = 'Department'))
set noexec on

begin transaction

CREATE TABLE [dbo].[Department](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_Department] 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]

GO

ALTER TABLE [dbo].[Department] ADD  CONSTRAINT [DF_Department_Id]  DEFAULT (newid()) FOR [Id]
GO


Insert into [dbo].[Department]
select id, name from dbo.GLAccount;
GO


alter table [dbo].[Team] drop [FK_Team_GLDepartment]
go

alter table [dbo].[Team] add DepartmentId uniqueidentifier null
go

update [dbo].[Team] set DepartmentId = GLAccountId 


ALTER TABLE [dbo].[Team]  WITH CHECK ADD  CONSTRAINT [FK_Team_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([Id])
GO

ALTER TABLE [dbo].[Team] CHECK CONSTRAINT [FK_Team_Department]
GO



alter table [dbo].[Company2GLDepartment] add DepartmentId uniqueidentifier null
go

update [dbo].[Company2GLDepartment] set DepartmentId = GLAccountId --(select top(1) id from [dbo].[Department])


ALTER TABLE [dbo].[Company2GLDepartment]  WITH CHECK ADD  CONSTRAINT [FK_Company2Department_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([Id])
GO

ALTER TABLE [dbo].[Company2GLDepartment] CHECK CONSTRAINT [FK_Company2Department_Department]
GO




alter table [dbo].[User2GLDepartment] add DepartmentId uniqueidentifier null
go

update [dbo].[User2GLDepartment] set DepartmentId = GLDepartmentId


ALTER TABLE [dbo].[User2GLDepartment]  WITH CHECK ADD  CONSTRAINT [FK_User2Department_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([Id])
GO

ALTER TABLE [dbo].[User2GLDepartment] CHECK CONSTRAINT [FK_User2Department_Department]
GO

CREATE TABLE [dbo].[Department2CostCenter](
	[Id] [uniqueidentifier] NOT NULL,
	[DepartmentId] [uniqueidentifier] NOT NULL,
	[CostCenterId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Department2CostCenter] 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]

GO

ALTER TABLE [dbo].[Department2CostCenter] ADD  CONSTRAINT [DF_Department2CostCenter_Id]  DEFAULT (newid()) FOR [Id]
GO

ALTER TABLE [dbo].[Department2CostCenter]  WITH CHECK ADD  CONSTRAINT [FK_Department2CostCenter_CreditDepartment] FOREIGN KEY([CostCenterId])
REFERENCES [dbo].[CreditDepartment] ([Id])
GO

ALTER TABLE [dbo].[Department2CostCenter] CHECK CONSTRAINT [FK_Department2CostCenter_CreditDepartment]
GO

ALTER TABLE [dbo].[Department2CostCenter]  WITH CHECK ADD  CONSTRAINT [FK_Department2CostCenter_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([Id])
GO

ALTER TABLE [dbo].[Department2CostCenter] CHECK CONSTRAINT [FK_Department2CostCenter_Department]
GO




ALTER TABLE [dbo].[Team] DROP COLUMN GlAccountId
go

ALTER TABLE [dbo].[Company2GLDepartment] DROP CONSTRAINT FK_Company2GLDepartment_GLDepartmentId
go
alter table [dbo].[Company2GLDepartment] DROP COLUMN GlAccountId
go

ALTER TABLE [dbo].[User2GLDepartment] DROP CONSTRAINT FK_User2GLDepartment_GLDepartment
go
ALTER TABLE [dbo].[User2GLDepartment] DROP COLUMN GlDepartmentId
go


commit transaction