USE [EnVisage]
GO

/****** Object:  Table [dbo].[Resource]    Script Date: 9/22/2014 1:56:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

begin transaction

begin try
exec sp_rename 'Resource.PK_Resource', 'PK_Resource1'

CREATE TABLE [dbo].[tmp_Resource](
	[Id] [uniqueidentifier] NOT NULL,
	[FirstName] [nvarchar](250) NOT NULL,
	[LastName] [nvarchar](250) NOT NULL,
	[IsActiveEmployee] [bit] NOT NULL,
 CONSTRAINT [PK_Resource] 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]

CREATE TABLE [dbo].[Resource2ExpenditureCategories](
	[ResourceId] [uniqueidentifier] NOT NULL,
	[EpenditureCategoryId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Resource2ExpenditureCategories] PRIMARY KEY CLUSTERED 
(
	[ResourceId] ASC,
	[EpenditureCategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Resource2Teams](
	[ResourceId] [uniqueidentifier] NOT NULL,
	[TeamId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Resource2Teams] PRIMARY KEY CLUSTERED 
(
	[ResourceId] ASC,
	[TeamId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into tmp_Resource
select * from Resource

insert into Resource2ExpenditureCategories
select * from ResourceExpenditureCategories

insert into Resource2Teams
select * from ResourceTeams

ALTER TABLE [dbo].[Resource] DROP CONSTRAINT [DF_Resource_IsActiveEmployee]
ALTER TABLE [dbo].[ResourceTeams] DROP CONSTRAINT [FK_ResourceTeams_Team]
ALTER TABLE [dbo].[ResourceTeams] DROP CONSTRAINT [FK_ResourceTeams_Resource]
ALTER TABLE [dbo].[ResourceExpenditureCategories] DROP CONSTRAINT [FK_ResourceExpenditureCategories_Resource]
ALTER TABLE [dbo].[ResourceExpenditureCategories] DROP CONSTRAINT [FK_ResourceExpenditureCategories_Expenditure_Category]

DROP TABLE [dbo].[ResourceExpenditureCategories]
DROP TABLE [dbo].[ResourceTeams]
DROP TABLE [dbo].[Resource]

exec sp_rename 'dbo.tmp_Resource', 'Resource' 

ALTER TABLE [dbo].[Resource] ADD  CONSTRAINT [DF_Resource_IsActiveEmployee]  DEFAULT ((0)) FOR [IsActiveEmployee]

ALTER TABLE [dbo].[Resource2ExpenditureCategories]  WITH CHECK ADD  CONSTRAINT [FK_Resource2ExpenditureCategories_Expenditure_Category] FOREIGN KEY([EpenditureCategoryId])
REFERENCES [dbo].[Expenditure_Category] ([Id])

ALTER TABLE [dbo].[Resource2ExpenditureCategories] CHECK CONSTRAINT [FK_Resource2ExpenditureCategories_Expenditure_Category]

ALTER TABLE [dbo].[Resource2ExpenditureCategories]  WITH CHECK ADD  CONSTRAINT [FK_Resource2ExpenditureCategories_Resource] FOREIGN KEY([ResourceId])
REFERENCES [dbo].[Resource] ([Id])

ALTER TABLE [dbo].[Resource2ExpenditureCategories] CHECK CONSTRAINT [FK_Resource2ExpenditureCategories_Resource]

ALTER TABLE [dbo].[Resource2Teams]  WITH CHECK ADD  CONSTRAINT [FK_Resource2Teams_Resource] FOREIGN KEY([ResourceId])
REFERENCES [dbo].[Resource] ([Id])

ALTER TABLE [dbo].[Resource2Teams] CHECK CONSTRAINT [FK_Resource2Teams_Resource]

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

ALTER TABLE [dbo].[Resource2Teams] CHECK CONSTRAINT [FK_Resource2Teams_Team]

CREATE TABLE [dbo].[ResourceAllocation](
	[Id] [uniqueidentifier] NOT NULL,
	[ResourceId] [uniqueidentifier] NOT NULL,
	[ScenarioId] [uniqueidentifier] NOT NULL,
	[ExpenditureCategoryId] [uniqueidentifier] NOT NULL,
	[StartDate] [date] NOT NULL,
	[EndDate] [date] NOT NULL,
	[AllocatePercentage] [float] NOT NULL,
 CONSTRAINT [PK_ResourceAllocation] 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].[ResourceAllocation]  WITH CHECK ADD  CONSTRAINT [FK_ResourceAllocation_Expenditure_Category] FOREIGN KEY([ExpenditureCategoryId])
REFERENCES [dbo].[Expenditure_Category] ([Id])

ALTER TABLE [dbo].[ResourceAllocation] CHECK CONSTRAINT [FK_ResourceAllocation_Expenditure_Category]

ALTER TABLE [dbo].[ResourceAllocation]  WITH CHECK ADD  CONSTRAINT [FK_ResourceAllocation_Resource] FOREIGN KEY([ResourceId])
REFERENCES [dbo].[Resource] ([Id])

ALTER TABLE [dbo].[ResourceAllocation] CHECK CONSTRAINT [FK_ResourceAllocation_Resource]

ALTER TABLE [dbo].[ResourceAllocation]  WITH CHECK ADD  CONSTRAINT [FK_ResourceAllocation_Scenario] FOREIGN KEY([ScenarioId])
REFERENCES [dbo].[Scenario] ([Id])

ALTER TABLE [dbo].[ResourceAllocation] CHECK CONSTRAINT [FK_ResourceAllocation_Scenario]

end try
begin catch
SELECT 
         ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_LINE () AS ErrorLine
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
end catch

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

GO