
IF NOT EXISTS (SELECT 1 FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'pm')
BEGIN
EXEC sp_executesql N'CREATE SCHEMA [pm] AUTHORIZATION [dbo]';
END

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'PositionFamilies' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[PositionFamilies](
	[PositionFamilyId] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[Created] [datetime] NOT NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.PositionFamilies] PRIMARY KEY CLUSTERED ([PositionFamilyId] ASC)
)
END

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'Positions' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[Positions](
	[PositionId] [bigint] IDENTITY(1,1) NOT NULL,
	[PositionFamilyId] [bigint] NULL,
	[Name] [nvarchar](max) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[Competencies] [nvarchar](max) NULL,
	[Expectations] [nvarchar](max) NULL,
	[IsManagement] [bit] NOT NULL,
	[BonusType] [int] NOT NULL,
	[MinBonusPotential] [float] NULL,
	[MaxBonusPotential] [float] NULL,
	[MinSalary] [decimal](18, 2) NULL,
	[MidSalary] [decimal](18, 2) NULL,
	[MaxSalary] [decimal](18, 2) NULL,	
	[IsDefault] [bit] NOT NULL,
	[Created] [datetime] NOT NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.Positions] PRIMARY KEY CLUSTERED ([PositionId] ASC),
 CONSTRAINT [FK_pm.Positions_pm.PositionFamilies_PositionFamilyId] FOREIGN KEY([PositionFamilyId]) REFERENCES [pm].[PositionFamilies] ([PositionFamilyId])
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'Metrics' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[Metrics](
	[MetricId] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Description] [nvarchar](max) NULL,
	[PositionId] [bigint] NULL,
	[IsDeleted] [bit] NOT NULL,
	[Created] [datetime] NOT NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.Metrics] PRIMARY KEY CLUSTERED ([MetricId] ASC),
 CONSTRAINT [FK_pm.Metrics_pm.Positions_PositionId] FOREIGN KEY([PositionId]) REFERENCES [pm].[Positions] ([PositionId])
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
 

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'EmployeePositions' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[EmployeePositions](
	[EmployeePositionId] [bigint] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [bigint] NOT NULL,
	[PositionId] [bigint] NOT NULL,
	[StartDate] [date] NOT NULL,
	[StopDate] [date] NULL,
	[Created] [datetime] NOT NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.EmployeePositions] PRIMARY KEY CLUSTERED ([EmployeePositionId] ASC),
 CONSTRAINT [FK_pm.EmployeePositions_dbo.Employee_ID] FOREIGN KEY([EmployeeId]) REFERENCES [dbo].[Employee] ([ID]),
 CONSTRAINT [FK_pm.EmployeePositions_dbo.Positions_PositionId] FOREIGN KEY([PositionId]) REFERENCES [pm].[Positions] ([PositionId])
 )
 END

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'EmployeeMetrics' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[EmployeeMetrics](
	[EmployeeMetricId] [bigint] IDENTITY(1,1) NOT NULL,
	[EmployeePositionId] [bigint] NOT NULL,
	[MetricId] [bigint] NOT NULL,
	[Created] [datetime] NOT NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.EmployeeMetrics] PRIMARY KEY CLUSTERED ([EmployeeMetricId] ASC),
 CONSTRAINT [FK_pm.EmployeeMetrics_pm.EmployeePositions_EmployeePositionId] FOREIGN KEY([EmployeePositionId]) REFERENCES [pm].[EmployeePositions] ([EmployeePositionId]),
 CONSTRAINT [FK_pm.EmployeeMetrics_pm.Metrics_MetricId] FOREIGN KEY([MetricId]) REFERENCES [pm].[Metrics] ([MetricId])
)
END

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'EmployeeMetricDetails' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[EmployeeMetricDetails](
	[EmployeeMetricDetailId] [bigint] IDENTITY(1,1) NOT NULL,
	[EmployeeMetricId] [bigint] NOT NULL,
	[Comments] [nvarchar](1024) NULL,
	[CurrentWeight] [decimal](18, 2) NOT NULL,
	[IsCurrent] [bit] NOT NULL,
	[StartDate] [date] NOT NULL,
	[StopDate] [date] NULL,
	[Created] [datetime] NOT NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.EmployeeMetricDetails] PRIMARY KEY CLUSTERED ([EmployeeMetricDetailId] ASC),
 CONSTRAINT [FK_pm.EmployeeMetricDetails_pm.EmployeeMetrics_EmployeeMetricId] FOREIGN KEY([EmployeeMetricId]) REFERENCES [pm].[EmployeeMetrics] ([EmployeeMetricId])
) ON [PRIMARY]
END

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'Recognitions' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[Recognitions](
	[RecognitionId] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[Description] [nvarchar](1024) NOT NULL,
	[Icon] [nvarchar](max) NULL,
	[Points] [int] NOT NULL,
	[Type] [int] NOT NULL,
	[Created] [datetime] NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.Recognitions] PRIMARY KEY CLUSTERED ([RecognitionId] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'EmployeeRecognitions' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[EmployeeRecognitions](
	[EmployeeRecognitionId] [bigint] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [bigint] NOT NULL,
	[RecognitionId] [bigint] NOT NULL,
	[RecognizedDate] [date] NOT NULL,
	[EarnedPoints] [int] NOT NULL,
	[EarnedReason] [nvarchar](512) NOT NULL,
	[Created] [datetime] NOT NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.EmployeeRecognitions] PRIMARY KEY CLUSTERED ([EmployeeRecognitionId] ASC),
 CONSTRAINT [FK_pm.EmployeeRecognitions_dbo.Employee_ID] FOREIGN KEY([EmployeeId]) REFERENCES [dbo].[Employee] ([ID]),
 CONSTRAINT [FK_pm.EmployeeRecognitions_pm.Recognitions_RecognitionId] FOREIGN KEY([RecognitionId]) REFERENCES [pm].[Recognitions] ([RecognitionId])
) ON [PRIMARY]
END

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'Feedback' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[Feedback](
	[FeedbackId] [bigint] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [bigint] NOT NULL,
	[BodyEncrypted] [nvarchar](max) NULL,
	[Date] [datetime] NOT NULL,
	[FromEmployeeId] [bigint] NULL,
	[IsAnonymous] [bit] NOT NULL,
	[Tags] [nvarchar](max) NULL,
	[IsVisible] [bit] NOT NULL,
	[Created] [datetime] NOT NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.Feedback] PRIMARY KEY CLUSTERED ([FeedbackId] ASC),
 CONSTRAINT [FK_pm.Feedback_dbo.Employees_ID] FOREIGN KEY([EmployeeId]) REFERENCES [dbo].[Employee] ([ID]),
CONSTRAINT [FK_pm.Feedback_dbo.Employees_FromId] FOREIGN KEY([FromEmployeeId]) REFERENCES [dbo].[Employee] ([ID])
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'PerformancePlans' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[PerformancePlans](
	[PerformancePlanId] [bigint] IDENTITY(1,1) NOT NULL,			
	[Name] [nvarchar] (100) NOT NULL,
	[StartDate] [date] NOT NULL,
	[EndDate] [date] NULL,
	[Created] [datetime] NOT NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.PerformancePlans] PRIMARY KEY CLUSTERED ([PerformancePlanId] ASC)
)
END

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'Ratings' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[Ratings](
	[RatingId] [bigint] IDENTITY(1, 1) NOT NULL,
	[PerformancePlanId] [bigint] NOT NULL,
	[Abbr] [nvarchar] (5) NOT NULL,			
	[Name] [nvarchar] (100) NOT NULL,
	[Description] [nvarchar] (max) NULL,
	[Order] [int] NOT NULL,
	[Color] [nvarchar] (9) NULL,
	[Tooltip] [nvarchar] (255) NULL,
	[IsDefault] [bit] NOT NULL,
	[Created] [datetime] NOT NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.RatingId] PRIMARY KEY CLUSTERED ([RatingId] ASC),
 CONSTRAINT [FK_pm.Ratings_pm.PerformancePlans_PerformancePlanId] FOREIGN KEY([PerformancePlanId]) REFERENCES [pm].[PerformancePlans] ([PerformancePlanId])
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'PerformanceCheckins' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[PerformanceCheckins](
	[PerformanceCheckinId] [bigint] IDENTITY(1,1) NOT NULL,
	[EmployeePositionId] [bigint] NOT NULL,
	[PerformancePlanId] [bigint] NOT NULL,	
	[CheckinDate] [datetime] NOT NULL,
	[IsFinalPeriodCheckin] [bit] NOT NULL,
	[IndividualComments] [nvarchar](max) NULL,
	[IndividualRating] [nvarchar](max) NULL,
	[IndividualRatingValue] [decimal] NULL,
	[IndividualSubmittedDate] [datetime] NULL,
	[SupervisorComments] [nvarchar](max) NULL,
	[SupervisorRating] [nvarchar](max) NULL,
	[SupervisorRatingValue] [decimal] NULL,
	[SupervisorSubmittedDate] [datetime] NULL,	
	[Created] [datetime] NOT NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.PerformanceCheckins] PRIMARY KEY CLUSTERED ([PerformanceCheckinId] ASC),
 CONSTRAINT [FK_pm.PerformanceCheckin_pm.EmployeePositions_EmployeePositionId] FOREIGN KEY([EmployeePositionId]) REFERENCES [pm].[EmployeePositions] ([EmployeePositionId]),
 CONSTRAINT [FK_pm.PerformanceCheckin_pm.PerformancePlans_PerformancePlanId] FOREIGN KEY([PerformancePlanId]) REFERENCES [pm].[PerformancePlans] ([PerformancePlanId])
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

IF NOT EXISTS  (SELECT 1 FROM information_schema.tables WHERE table_name = 'PerformanceRatings' AND table_schema = 'pm')
BEGIN
CREATE TABLE [pm].[PerformanceRatings](
	[PerformanceRatingId] [bigint] IDENTITY(1,1) NOT NULL,
	[PerformanceCheckinId] [bigint] NOT NULL,	
	[EmployeeMetricId] [bigint] NOT NULL,
	[EmployeeMetricDetailId] [bigint] NOT NULL,
	[RatingId] [bigint] NOT NULL,
	[IsIndividual] [bit] NOT NULL,
	[Created] [datetime] NOT NULL,
	[CreatedBy] [bigint] NOT NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_pm.PerformanceRatings] PRIMARY KEY CLUSTERED ([PerformanceRatingId] ASC),
 CONSTRAINT [FK_pm.PerformanceRatings_pm.PerformanceCheckins_PerformanceCheckinId] FOREIGN KEY([PerformanceCheckinId]) REFERENCES [pm].[PerformanceCheckins] ([PerformanceCheckinId]),
 CONSTRAINT [FK_pm.PerformanceRatings_pm.EmployeeMetrics_EmployeeMetricId] FOREIGN KEY([EmployeeMetricId]) REFERENCES [pm].[EmployeeMetrics] ([EmployeeMetricId]),
 CONSTRAINT [FK_pm.PerformanceRatings_pm.EmployeeMetricDetails_EmployeeMetricDetailId] FOREIGN KEY([EmployeeMetricDetailId]) REFERENCES [pm].[EmployeeMetricDetails] ([EmployeeMetricDetailId]),
 CONSTRAINT [FK_pm.PerformanceRatings_pm.Ratings_RatingId] FOREIGN KEY([RatingId]) REFERENCES [pm].[Ratings] ([RatingId])
) ON [PRIMARY]
END

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[pm].[PM_SP_Common_IsEmployeeIdInManagementChain]') AND type in (N'P', N'PC'))
DROP PROCEDURE [pm].[PM_SP_Common_IsEmployeeIdInManagementChain]
GO

CREATE PROCEDURE [pm].[PM_SP_Common_IsEmployeeIdInManagementChain]
    @subjectId [int],
    @managerId [nvarchar](50)
AS
BEGIN
    WITH RecursiveCTEQuery AS( 
    SELECT mgr.ID, mgr.SupervisorID FROM [dbo].[Employee] mgr WHERE EmpID = @managerId 
    UNION ALL 
    SELECT emp.ID, emp.SupervisorID FROM RecursiveCTEQuery cte INNER JOIN [dbo].[Employee] emp ON cte.ID = emp.SupervisorID) 
    SELECT ID FROM RecursiveCTEQuery WHERE ID = @subjectId;
END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[pm].[PM_SP_Common_IsEmployeePositionIdInManagementChain]') AND type in (N'P', N'PC'))
DROP PROCEDURE [pm].[PM_SP_Common_IsEmployeePositionIdInManagementChain]
GO

CREATE PROCEDURE [pm].[PM_SP_Common_IsEmployeePositionIdInManagementChain]
    @empPositionId [bigint],
    @managerId [bigint]
AS
BEGIN
	DECLARE @subjectId [bigint];
	SET @subjectId = (SELECT EmployeeId FROM [pm].EmployeePositions WHERE EmployeePositionId = @empPositionId);

    WITH RecursiveCTEQuery AS( 
    SELECT mgr.ID, mgr.SupervisorID FROM [dbo].[Employee] mgr WHERE ID = @managerId 
    UNION ALL 
    SELECT emp.ID, emp.SupervisorID FROM RecursiveCTEQuery cte INNER JOIN [dbo].[Employee] emp ON cte.ID = emp.SupervisorID) 
    SELECT ID FROM RecursiveCTEQuery WHERE ID = @subjectId;
END

GO


