use master;
GO
Alter database Project3  set single_user with rollback immediate;
GO
DROP Database Project3;
GO 

CREATE DATABASE Project3;
GO

USE Project3;
GO

CREATE TABLE dbo.Departments (
    DepartmentID       INT IDENTITY PRIMARY KEY,
    DepartmentName NVARCHAR(50),
    DepartmentDesc  NVARCHAR(150) NOT NULL CONSTRAINT DF_DFDeptDesc DEFAULT 'Dept. Desc to be determined'
);

CREATE TABLE dbo.Employees (
    EmployeeID               INT IDENTITY PRIMARY KEY,
    DepartmentID            INT CONSTRAINT FK_Employee_Department FOREIGN KEY REFERENCES dbo.Departments ( DepartmentID ),
    ManagerEmployeeID INT CONSTRAINT FK_Employee_Manager FOREIGN KEY REFERENCES dbo.Employees ( EmployeeID ),
    FirstName                  NVARCHAR(50),
    LastName                  NVARCHAR(50),
    Salary                        MONEY CONSTRAINT CK_EmployeeSalary CHECK ( Salary >= 0 ),
    CommissionBonus    MONEY CONSTRAINT CK_EmployeeCommission CHECK ( CommissionBonus >= 0 ),
    FileFolder                  NVARCHAR(256) CONSTRAINT DF_FileFolder DEFAULT 'ToBeCreated'
);

GO
INSERT INTO dbo.Departments ( DepartmentName, DepartmentDesc )
VALUES ( 'Management', 'Executive Management' ),
       ( 'HR', 'Human Resources' ),
       ( 'DatabaseMgmt', 'Database Management'),
       ( 'Support', 'Product Support' ),
       ( 'Software', 'Software Sales' ),
       ( 'Marketing', 'Digital Marketing' );
GO

SET IDENTITY_INSERT dbo.Employees ON;
GO

INSERT INTO dbo.Employees ( EmployeeID, DepartmentID, ManagerEmployeeID, FirstName, LastName, Salary, CommissionBonus, FileFolder )
VALUES ( 1, 4, NULL, 'Sarah', 'Campbell', 78000, NULL, 'SarahCampbell' ),
       ( 2, 3, 1, 'James', 'Donoghue',     68000 , NULL, 'JamesDonoghue'),
       ( 3, 1, 1, 'Hank', 'Braby',        76000 , NULL, 'HankBraby'),
       ( 4, 2, 1, 'Samantha', 'Jonus',    72000, NULL , 'SamanthaJonus'),
       ( 5, 3, 4, 'Fred', 'Judd',         44000, 5000, 'FredJudd'),
       ( 6, 3, NULL, 'Hanah', 'Grant',   65000, 4000 ,  'HanahGrant'),
       ( 7, 3, 4, 'Dhruv', 'Patel',       66000, 2000 ,  'DhruvPatel'),
       ( 8, 4, 3, 'Dash', 'Mansfeld',     54000, 5000 ,  'DashMansfeld');
GO

SET IDENTITY_INSERT dbo.Employees OFF;
GO

CREATE FUNCTION dbo.GetEmployeeID (
    -- Parameter datatype and scale match their targets
    @FirstName NVARCHAR(50),
    @LastName  NVARCHAR(50) )
RETURNS INT
AS
BEGIN;


    DECLARE @ID INT;

    SELECT @ID = EmployeeID
    FROM dbo.Employees
    WHERE FirstName = @FirstName
          AND LastName = @LastName;

    -- Note that it is not necessary to initialize @ID or test for NULL, 
    -- NULL is the default, so if it is not overwritten by the select statement
    -- above, NULL will be returned.
    RETURN @ID;
END;
GO

/* REQUIREMENT 1*/

CREATE OR ALTER PROCEDURE dbo.CreateDepartment (
@DepartmentID INT OUTPUT,
@DepartmentDesc NVARCHAR(150),
@DepartmentName NVARCHAR(50) = NULL
)
AS
BEGIN;
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT INTO dbo.Departments (DepartmentName, DepartmentDesc)
VALUES (@DepartmentName, @DepartmentDesc);
SET @DepartmentID = SCOPE_IDENTITY();
COMMIT TRANSACTION;
END;
GO

/* REQUIREMENT 2*/

DECLARE @DepartmentID INT;
EXECUTE dbo.CreateDepartment
@DepartmentDesc = 'Software Testing and Quality Assurance',
@DepartmentName = 'QA',
@DepartmentID = @DepartmentID OUTPUT;

EXECUTE dbo.CreateDepartment
@DepartmentDesc = 'Systems Design and Development',
@DepartmentName = 'SysDev',
@DepartmentID = @DepartmentID OUTPUT;

EXECUTE dbo.CreateDepartment
@DepartmentDesc = 'Deployment and Production Support',
@DepartmentName = 'Deployment',
@DepartmentID = @DepartmentID OUTPUT;

EXECUTE dbo.CreateDepartment
@DepartmentDesc = 'Online Technical Support',
@DepartmentName = 'TechSupport',
@DepartmentID = @DepartmentID OUTPUT;

SELECT * FROM dbo.Departments;
GO

/* REQUIREMENT 3*/

CREATE FUNCTION dbo.GetDepartmentID (
@DepartmentName NVARCHAR(100)
)
RETURNS INT
AS
BEGIN;
DECLARE @DepartmentID INT;
SELECT @DepartmentID = DepartmentID
FROM Departments
WHERE DepartmentName = @DepartmentName;
RETURN @DepartmentID;
END;
GO

SELECT dbo.GetDepartmentID ('QA');
GO

/* REQUIREMENT 4*/

CREATE OR ALTER PROCEDURE dbo.CreateEmployee (
@DepartmentName NVARCHAR(50),
@EmployeeFirstName NVARCHAR(50),
@EmployeeLastName NVARCHAR(50),
@FileFolder NVARCHAR(256),
@ManagerFirstName NVARCHAR(50),
@ManagerLastName NVARCHAR(50),
@Salary MONEY = 48000,
@CommissionBonus MONEY = 4500
)
AS
BEGIN;

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRANSACTION;

DECLARE @DepartmentID INT = dbo.GetDepartmentID (@DepartmentName);
IF (@DepartmentID IS NULL)
BEGIN;
EXECUTE dbo.CreateDepartment
@DepartmentDesc = 'No Description',
@DepartmentName = @DepartmentName,
@DepartmentID = @DepartmentID OUTPUT;
END;

DECLARE @ManagerEmployeeID INT = dbo.GetEmployeeID (@ManagerFirstName, @ManagerLastName);
IF (@ManagerEmployeeID IS NULL)
BEGIN;
INSERT INTO dbo.Employees (FirstName, LastName, FileFolder)
VALUES (@ManagerFirstName, @ManagerLastName, CONCAT(@ManagerFirstName, @ManagerLastName));
SET @ManagerEmployeeID = SCOPE_IDENTITY();
END;

INSERT INTO dbo.Employees (DepartmentID, ManagerEmployeeID, FirstName, LastName, FileFolder, CommissionBonus, Salary)
VALUES (@DepartmentID, @ManagerEmployeeID, @EmployeeFirstName, @EmployeeLastName, @FileFolder, @CommissionBonus, @Salary);

COMMIT TRANSACTION;
END;
GO

EXECUTE dbo.CreateEmployee
@DepartmentName = 'Infrastructure',
@EmployeeFirstName = 'Wherewolf',
@EmployeeLastName = 'Waldo',
@FileFolder = 'WherewolfWaldo',
@ManagerFirstName = 'Essam',
@ManagerLastName = 'Fahmy';

EXECUTE dbo.CreateEmployee
@DepartmentName = 'Support',
@EmployeeFirstName = 'Sponge',
@EmployeeLastName = 'Bob',
@FileFolder = 'SpongeBob',
@ManagerFirstName = 'Sarah',
@ManagerLastName = 'Campbell',
@Salary = 42000,
@CommissionBonus = 1500;
GO

SELECT * FROM Employees;
SELECT * FROM Departments;

/* REQUIREMENT 5*/

CREATE TYPE dbo.CommissionBonus FROM MONEY NULL;
GO

CREATE FUNCTION dbo.GetEmployeeCommission (
@CommissionBonus dbo.CommissionBonus
)
RETURNS @retEmployee TABLE(
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
ManagerEmployeeID INT,
FileFolder NVARCHAR(256),
Salary MONEY,
CommissionBonus MONEY,
DepartmentName NVARCHAR(50),
DepartmentDesc NVARCHAR(150)
)
AS
BEGIN
IF @CommissionBonus >= 0
BEGIN;
INSERT INTO @retEmployee
SELECT emp.FirstName, emp.LastName, emp.ManagerEmployeeID,emp.FileFolder, emp.salary, emp.CommissionBonus, dep.DepartmentName, dep.DepartmentDesc
FROM dbo.Employees emp INNER JOIN Departments dep ON emp.DepartmentID = dep.DepartmentID WHERE emp.CommissionBonus > @CommissionBonus
END;
RETURN;
END;
GO

DECLARE @CommissionBonus dbo.CommissionBonus = 4000;

SELECT *
FROM dbo.GetEmployeeCommission(@CommissionBonus);

/* REQUIREMENT 6*/

WITH EmployeeRank AS (
SELECT Employees.DepartmentID, DepartmentName, AVG(Salary) As DepartmentAverage
FROM Employees
JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
GROUP BY Employees.DepartmentID, DepartmentName)

SELECT FirstName, DepartmentName, Salary, DepartmentAverage, ISNULL((Salary + CommissionBonus), Salary) AS TotalCompensation,
RANK() OVER( ORDER BY Salary DESC ) AS RankNum
FROM EmployeeRank JOIN Employees ON Employees.DepartmentID = EmployeeRank.DepartmentID
ORDER BY Salary DESC;

/* REQUIREMENT 7*/

WITH EmployeePath AS (
    
    SELECT  
    EmployeeID,     
    FirstName, 
    LastName,
    FileFolder,
    CAST(FileFolder AS VARCHAR(MAX)) AS FilePath,
    ManagerEmployeeID,
    FirstName AS ManagerFirstName,
    LastName AS ManagerLastName,
    DepartmentID        
    FROM Employees
    WHERE ManagerEmployeeID IS NULL

    UNION ALL

    SELECT 
    e.EmployeeID,
    e.FirstName, 
    e.LastName,
    e.FileFolder,
    p.FilePath + '/' + CAST(e.FileFolder AS VARCHAR(MAX)) AS FilePath,
    e.ManagerEmployeeID,
    p.FirstName AS ManagerFirstName,
    p.LastName AS ManagerLastName,
    e.DepartmentID
    FROM Employees e
    INNER JOIN EmployeePath p 
    ON e.ManagerEmployeeID = p.EmployeeID
)
SELECT FirstName, LastName, DepartmentID, FileFolder, FilePath, ManagerFirstName, ManagerLastName FROM EmployeePath;