USE [EnVisage]
GO

begin tran

	IF NOT EXISTS(SELECT * FROM sys.columns 
				WHERE Name = N'TeamId' AND Object_ID = Object_ID(N'PeopleResourceAllocation'))
	BEGIN
		ALTER TABLE PeopleResourceAllocation ADD TeamId uniqueidentifier NULL
	END
	GO

	UPDATE PeopleResourceAllocation SET PeopleResourceAllocation.TeamId = PR.TeamId
	FROM PeopleResourceAllocation PRA
		inner join PeopleResource PR ON PR.Id = PRA.PeopleResourceId
	WHERE PRA.TeamId is null
	GO

	ALTER TABLE PeopleResourceAllocation ALTER COLUMN TeamId uniqueidentifier NOT NULL
	GO

	if not exists (select 1 from sys.foreign_keys where name = 'FK_PeopleResourceAllocation_Team')
	begin
		ALTER TABLE [dbo].[PeopleResourceAllocation]  WITH CHECK ADD  CONSTRAINT [FK_PeopleResourceAllocation_Team] FOREIGN KEY([TeamId])
		REFERENCES [dbo].[Team] ([Id])

		ALTER TABLE [dbo].[PeopleResourceAllocation] CHECK CONSTRAINT [FK_PeopleResourceAllocation_Team]
	END
	
	if exists (select 1 from sys.indexes where name = 'IX_PeopleResourceAllocation_U' and object_id = OBJECT_ID('PeopleResourceAllocation'))
	begin
		CREATE UNIQUE NONCLUSTERED INDEX [IX_PeopleResourceAllocation_U] ON [dbo].[PeopleResourceAllocation]
		(
			[WeekEndingDate] ASC,
			[PeopleResourceId] ASC,
			[TeamId] ASC,
			[ScenarioId] 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]
	end

commit tran