--1
CREATE TABLE [Users] (
			[Id] INT PRIMARY KEY IDENTITY(1,1) NOT NULL ,
			[Username] VARCHAR(30) UNIQUE NOT NULL,
			[Password] VARCHAR(50) NOT NULL,
			[Name] VARCHAR(50) ,
			[Birthdate] DATE ,
			[Age] INT CHECK ([Age] > 14 AND [Age] < 110),
			[Email] VARCHAR(50) NOT NULL
	)


CREATE TABLE [Departments] (
			[Id] INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
			[Name] VARCHAR(50) NOT NULL
)

CREATE TABLE [Employees] (
			[Id] INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
			[FirstName] VARCHAR(25),
			[LastName] VARCHAR(25),
			[Birthdate] DATE ,
			[Age] INT CHECK ([Age] > 18 AND [Age] < 110),
			[DepartmentId] INT FOREIGN KEY REFERENCES Departments([Id])
)


CREATE TABLE [Categories] (
			[Id] INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
			[Name] VARCHAR(50) NOT NULL,
			[DepartmentId] INT FOREIGN KEY REFERENCES Departments([Id])
)


CREATE TABLE [Status] (
			[Id] INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
			[Label] VARCHAR(30) NOT NULL
)

CREATE TABLE [Reports] (
			[Id] INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
			[CategoryId] INT FOREIGN KEY REFERENCES Categories([Id]) NOT NULL,
			[StatusId] INT FOREIGN KEY REFERENCES [Status]([Id]) NOT NULL,
			[OpenDate] DATE NOT NULL ,
			[CloseDate] DATE ,
			[Description] VARCHAR(200) NOT NULL,
			[UserId] INT FOREIGN KEY REFERENCES Users([Id]) NOT NULL ,
			[EmployeeId] INT FOREIGN KEY REFERENCES Employees([Id])
)

--2
INSERT INTO Employees ([FirstName] , [LastName], [Birthdate] , [DepartmentId])
VALUES
('Marlo' , 'O''Malley' , '1958-9-21' , 1),
('Niki', 'Stanaghan' , '1969-11-26' , 4),
('Ayrton', 'Senna' , '1960-03-21' , 9),
('Ronnie' , 'Peterson' , '1944-02-14' , 9),
('Giovanna', 'Amati', '1959-07-20' , 5)


INSERT INTO Reports ([CategoryId], [StatusId], [OpenDate], [CloseDate] , [Description], [UserId] , [EmployeeId])
VALUES
(1, 1, '2017-04-13' , NULL , 'Stuck Road on Str.133', 6, 2),
(6 , 3 , '2015-09-05' , '2015-12-06', 'Charity trail running', 3 , 5),
(14, 2 , '2015-09-07' , NULL, 'Falling bricks on Str.58' , 5, 2),
(4, 3 , '2017-07-03' , '2017-07-06' , 'Cut off streetlight on Str.11', 1,1)


--3
UPDATE Reports
SET [CloseDate] = [OpenDate]
WHERE [CloseDate] IS NULL


--4
DELETE FROM Reports
WHERE [StatusId] IN(SELECT [Id] FROM [Status] WHERE [Id] = 4)
DELETE FROM [Status]
WHERE [Id] = 4

--5
SELECT r.[Description] , FORMAT(r.[OpenDate], 'dd-MM-yyyy') AS [OpenDate]
FROM [Reports] AS r
WHERE r.[EmployeeId] IS NULL
ORDER BY r.[OpenDate] , r.[Description]

--6
SELECT r.[Description] , c.[Name] AS [CategoryName]
FROM [Reports] AS r
JOIN [Categories] AS c ON c.Id = r.CategoryId
ORDER BY r.[Description] , c.[Name]

--7
SELECT TOP(5) c.[Name] AS [CategoryName] , COUNT(r.Id) AS [ReportsNumber]
FROM Categories AS c
JOIN [Reports] AS r ON r.CategoryId = c.[Id]
GROUP BY c.[Name]
ORDER BY [ReportsNumber] DESC , [CategoryName]


--8
SELECT u.Username , c.[Name] AS [CategoryName]
FROM Users AS u
LEFT JOIN Reports AS r ON r.UserId = u.Id
LEFT JOIN Categories AS c ON c.Id = r.CategoryId
WHERE (MONTH(u.Birthdate) = MONTH(r.OpenDate) AND DAY(u.Birthdate) = DAY(r.OpenDate))
ORDER BY u.Username , [CategoryName]

--9
SELECT CONCAT(e.FirstName , ' ', e.LastName) AS [FullName] , COUNT(DISTINCT r.UserId) AS [UsersCount]
FROM Employees AS e
LEFT JOIN [Reports] AS r ON r.EmployeeId = e.Id
GROUP BY e.FirstName , e.LastName
ORDER BY [UsersCount] DESC , [FullName]


-- 11

CREATE FUNCTION udf_HoursToComplete(@StartDate DATETIME, @EndDate DATETIME)
RETURNS INT
AS
BEGIN
IF (@StartDate IS NULL)
BEGIN
RETURN 0
END

IF (@EndDate IS NULL)
BEGIN
RETURN 0
END

RETURN	DATEDIFF(hour , @StartDate, @EndDate)
END