USE [EnVisage]
GO

IF (NOT EXISTS(SELECT * FROM sys.indexes 
	WHERE Name = N'IX_NonProjectTime2ResourcePos'))
BEGIN
	CREATE NONCLUSTERED INDEX [IX_NonProjectTime2ResourcePos] ON [dbo].[NonProjectTime2Resource]
	(
		[NonProjectTimeId] ASC,
		[Position] 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
GO

IF (NOT EXISTS(SELECT * FROM sys.indexes 
	WHERE Name = N'IX_NonProjectTimeResourceAllocation'))
BEGIN
	CREATE NONCLUSTERED INDEX [IX_NonProjectTimeResourceAllocation] ON [dbo].[NonProjectTimeResourceAllocation]
	(
		[NonProjectTime2ResourceId] ASC,
		[WeekEndingDate] 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
GO

IF (NOT EXISTS (SELECT * FROM sys.tables where name='NonProjectTime2Team'))
BEGIN
	CREATE TABLE [dbo].[NonProjectTime2Team](
		[Id] [uniqueidentifier] NOT NULL,
		[NonProjectTimeId] [uniqueidentifier] NOT NULL,
		[TeamId] [uniqueidentifier] NOT NULL,
		[Position] [int] NOT NULL,
	 CONSTRAINT [PK_NonProjectTime2Team] 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].[NonProjectTime2Team] ADD  CONSTRAINT [DF_NonProjectTime2Team_Id]  DEFAULT (newid()) FOR [Id]

	ALTER TABLE [dbo].[NonProjectTime2Team] ADD  CONSTRAINT [DF_NonProjectTime2Team_Position]  DEFAULT ((0)) FOR [Position]

	ALTER TABLE [dbo].[NonProjectTime2Team]  WITH CHECK ADD  CONSTRAINT [FK_NonProjectTime2Team_NonProjectTime] FOREIGN KEY([NonProjectTimeId])
	REFERENCES [dbo].[NonProjectTime] ([Id])

	ALTER TABLE [dbo].[NonProjectTime2Team] CHECK CONSTRAINT [FK_NonProjectTime2Team_NonProjectTime]

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

	ALTER TABLE [dbo].[NonProjectTime2Team] CHECK CONSTRAINT [FK_NonProjectTime2Team_Team]

	CREATE UNIQUE NONCLUSTERED INDEX [UNIQUE_NonProjectTime2Team] ON [dbo].[NonProjectTime2Team]
	(
		[NonProjectTimeId] ASC,
		[TeamId] 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]

	CREATE NONCLUSTERED INDEX [UNIQUE_NonProjectTime2Team_1] ON [dbo].[NonProjectTime2Team]
	(
		[NonProjectTimeId] ASC,
		[Position] 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
GO

IF (NOT EXISTS (SELECT * FROM sys.tables where name='NonProjectTimeTeamAllocation'))
BEGIN
	CREATE TABLE [dbo].[NonProjectTimeTeamAllocation](
		[Id] [uniqueidentifier] NOT NULL,
		[WeekEndingDate] [datetime] NOT NULL,
		[HoursOff] [int] NOT NULL,
		[NonProjectTime2TeamId] [uniqueidentifier] NOT NULL,
	 CONSTRAINT [PK_NonProjectTimeTeamAllocation] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[NonProjectTimeTeamAllocation] ADD  CONSTRAINT [DF_NonProjectTimeTeamAllocation_Id]  DEFAULT (newid()) FOR [Id]

	ALTER TABLE [dbo].[NonProjectTimeTeamAllocation]  WITH CHECK ADD  CONSTRAINT [FK_NonProjectTimeTeamAllocation_NonProjectTime2Team] FOREIGN KEY([NonProjectTime2TeamId])
	REFERENCES [dbo].[NonProjectTime2Team] ([Id])

	ALTER TABLE [dbo].[NonProjectTimeTeamAllocation] CHECK CONSTRAINT [FK_NonProjectTimeTeamAllocation_NonProjectTime2Team]

	CREATE NONCLUSTERED INDEX [IX_NonProjectTimeTeamAllocation] ON [dbo].[NonProjectTimeTeamAllocation]
	(
		[NonProjectTime2TeamId] ASC,
		[WeekEndingDate] 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
GO

IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeResourceAllocation')
BEGIN
	DROP VIEW VW_NonProjectTimeResourceAllocation
END
GO

CREATE VIEW VW_NonProjectTimeResourceAllocation AS
SELECT A.NonProjectTimeId, A.PeopleResourceId, B.WeekEndingDate, B.HoursOff, P.ExpenditureCategoryId,
	T.NonProjectTimeCategoryId
	FROM NonProjectTime2Resource A
	INNER JOIN NonProjectTimeResourceAllocation B ON (B.NonProjectTime2ResourceId = A.Id)
	INNER JOIN NonProjectTime T ON (T.Id = A.NonProjectTimeId)
	INNER JOIN PeopleResource P ON (P.Id = A.PeopleResourceId)
GO

IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeTeamAllocation')
BEGIN
	DROP VIEW VW_NonProjectTimeTeamAllocation
END
GO

CREATE VIEW VW_NonProjectTimeTeamAllocation AS
SELECT A.NonProjectTimeId, A.TeamId, R2T.PeopleResourceId, B.WeekEndingDate, B.HoursOff, P.ExpenditureCategoryId,
	T.NonProjectTimeCategoryId
	FROM NonProjectTime2Team A
	INNER JOIN NonProjectTimeTeamAllocation B ON (B.NonProjectTime2TeamId = A.Id)
	INNER JOIN NonProjectTime T ON (T.Id = A.NonProjectTimeId)
	INNER JOIN PeopleResource2Team R2T ON (R2T.TeamId = A.TeamId) AND (R2T.StartDate < B.WeekEndingDate) AND
		((R2T.EndDate IS NULL) OR (R2T.EndDate >= B.WeekEndingDate))
	INNER JOIN PeopleResource P ON (P.Id = R2T.PeopleResourceId)
GO

IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeAllocation')
BEGIN
	DROP VIEW VW_NonProjectTimeAllocation
END
GO

CREATE VIEW VW_NonProjectTimeAllocation AS

WITH MixedAllocationData (NonProjectTimeId, PeopleResourceId, WeekEndingDate, HoursOff, ExpenditureCategoryId,
	NonProjectTimeCategoryId) AS
(
	SELECT NonProjectTimeId, PeopleResourceId, WeekEndingDate, HoursOff, ExpenditureCategoryId,
		NonProjectTimeCategoryId
	FROM VW_NonProjectTimeResourceAllocation
	UNION ALL
	SELECT NonProjectTimeId, PeopleResourceId, WeekEndingDate, HoursOff, ExpenditureCategoryId,
		NonProjectTimeCategoryId
	FROM VW_NonProjectTimeTeamAllocation
)
SELECT NonProjectTimeId, PeopleResourceId, WeekEndingDate, ISNULL(SUM(HoursOff), 0) AS HoursOff, ExpenditureCategoryId,
	NonProjectTimeCategoryId 
FROM MixedAllocationData
GROUP BY NonProjectTimeId, PeopleResourceId, WeekEndingDate, ExpenditureCategoryId, NonProjectTimeCategoryId

GO

IF EXISTS(SELECT * FROM sys.procedures WHERE Name = N'sp_DeleteNonProjectTime')
DROP PROCEDURE sp_DeleteNonProjectTime
GO

CREATE PROCEDURE [dbo].[sp_DeleteNonProjectTime] (@id uniqueidentifier)
AS
BEGIN
	begin transaction

	delete from NonProjectTimeResourceAllocation 
		where NonProjectTime2ResourceId IN (
			select Id from NonProjectTime2Resource where NonProjectTimeId = @id
		)

	delete from NonProjectTimeTeamAllocation 
		where NonProjectTime2TeamId IN (
			select Id from NonProjectTime2Team where NonProjectTimeId = @id
		)

	delete from NonProjectTime2Resource where NonProjectTimeId = @id
	delete from NonProjectTime2Team where NonProjectTimeId = @id
	delete from NonProjectTime where Id = @id

	commit transaction
END
GO

ALTER PROCEDURE [dbo].[sp_DeletePeopleResource] (@id uniqueidentifier)
AS
BEGIN
	begin transaction

	delete from Holiday2PeopleResource where ResourceId = @id
	
	-- Delete non-project time for resource
	delete from NonProjectTimeResourceAllocation 
		where NonProjectTime2ResourceId IN (
			select Id from NonProjectTime2Resource where PeopleResourceId = @id
		)
	delete from NonProjectTime2Resource where PeopleResourceId = @id
	delete from NonProjectTime where (
		not exists(
			select 1 from NonProjectTime2Resource B where B.NonProjectTimeId = NonProjectTime.Id
		))
		AND
		(not exists(
			select 2 from NonProjectTime2Team C where C.NonProjectTimeId = NonProjectTime.Id
		))

	delete from PeopleResourceVacation where PeopleResourceId = @id
	delete from PeopleResourceAllocation where PeopleResourceId = @id
	delete from Skill2Resource where ResourceId = @id
	delete from PeopleResource2Team where PeopleResourceId = @id
	delete from PeopleResource where Id = @id

	commit transaction
END
GO

IF NOT EXISTS(SELECT * FROM sys.columns 
            WHERE Name = N'IsTeamMode' AND Object_ID = Object_ID(N'NonProjectTime'))
BEGIN
ALTER table [dbo].[NonProjectTime] Add [IsTeamMode] [bit] NOT NULL default 0
end
go