USE [EnVisage]
GO

IF (EXISTS (SELECT * FROM sys.tables where name='Skill2Resource'))
begin
	IF (NOT EXISTS(SELECT * FROM Skill2Resource))
	begin
		DROP TABLE Skill2Resource
	end
end
GO

IF (NOT EXISTS (SELECT * FROM sys.tables where name='Skill2Resource'))
BEGIN
		CREATE TABLE Skill2Resource(
			[Id] [uniqueidentifier] NOT NULL,
			[DataType] [smallint] NOT NULL,
			[EffectiveDate] [datetime] NOT NULL,
			[SkillId] [uniqueidentifier] NOT NULL,
			[ResourceId] [uniqueidentifier] NOT NULL,
			[Level] [smallint] NULL,
			[Interested] [bit] NOT NULL,
			[DateCreated] [datetime] NOT NULL,
		CONSTRAINT [PK_Skill2Resource] 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],
		CONSTRAINT [UK_Skill2Resource] UNIQUE NONCLUSTERED 
		(
			[ResourceId] ASC,
			[SkillId] ASC,
			[DataType] ASC,
			[EffectiveDate] ASC,
			[DateCreated] 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 Skill2Resource ADD  CONSTRAINT [DF_Skill2Resource_Interested]  DEFAULT ((0)) FOR [Interested]

		ALTER TABLE Skill2Resource ADD  CONSTRAINT [DF_Skill2Resource_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]

		ALTER TABLE Skill2Resource  WITH CHECK ADD  CONSTRAINT [FK_Skill2Resource_ResourceId] FOREIGN KEY([ResourceId])
		REFERENCES PeopleResource ([Id])

		ALTER TABLE Skill2Resource CHECK CONSTRAINT [FK_Skill2Resource_ResourceId]

		ALTER TABLE Skill2Resource  WITH CHECK ADD  CONSTRAINT [FK_Skill2Resource_SkillId] FOREIGN KEY([SkillId])
		REFERENCES Skill ([Id])

		ALTER TABLE Skill2Resource CHECK CONSTRAINT [FK_Skill2Resource_SkillId]
END
GO

if exists (select * from sys.views where name='VW_Skill2ResourceRecentExt')
begin
  drop view VW_Skill2ResourceRecentExt
end
GO

if exists (select * from sys.views where name='VW_Skill2ResourceRecent')
begin
  drop view VW_Skill2ResourceRecent
end
GO

if exists (select * from sys.views where name='VW_LastSkill2ResourceRecords')
begin
  drop view VW_LastSkill2ResourceRecords
end
GO

CREATE VIEW VW_LastSkill2ResourceRecords AS
SELECT DataType, EffectiveDate, SkillId, ResourceId, MAX(DateCreated) AS DateCreated
  FROM Skill2Resource
  Group by DataType, EffectiveDate, SkillId, ResourceId
GO

CREATE VIEW VW_Skill2ResourceRecent AS
SELECT A.* FROM Skill2Resource A
INNER JOIN VW_LastSkill2ResourceRecords B ON
(B.DataType = A.DataType) AND (B.EffectiveDate = A.EffectiveDate) AND
(B.ResourceId = A.ResourceId) AND (B.SkillId = A.SkillId) AND
(B.DateCreated = A.DateCreated)
GO

CREATE VIEW VW_Skill2ResourceRecentExt AS
select A.*, ISNULL(D.Id, A.SkillId) AS SkillGroupId, 
CAST((CASE WHEN (D.Id IS NULL) THEN 1 ELSE 0 END) AS bit) AS AssignedToGroup,
E.Id AS TeamId, E.CompanyId
from VW_Skill2ResourceRecent A
INNER JOIN Skill B ON (B.Id = A.SkillId)
INNER JOIN PeopleResource C ON (C.Id = A.ResourceId)
LEFT JOIN Skill D ON (D.Id = B.ParentId)
LEFT JOIN Team E ON (E.Id = C.TeamId)
GO