-- 1. Create the Departments Table (without foreign key constraints)
CREATE TABLE Departments (
    ID INT PRIMARY KEY,
    DepartmentName VARCHAR(50) NOT NULL,
    MainCity VARCHAR(50),
    DepartmentManagerID INT
);

-- 2. Create the Projects Table (with TimeRemaining as INT in days)
CREATE TABLE Projects (
    ID INT PRIMARY KEY,
    ProjectName VARCHAR(100) NOT NULL,
    ProjectManagerID INT,
    DepartmentID INT,
    TimeRemaining INT
);

-- 3. Create the Employees Table (with ManagedID restricted to manager IDs)
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    EmployeeName VARCHAR(50) NOT NULL,
    City VARCHAR(50),
    DepartmentID INT,
    Salary DECIMAL(10, 2),
    YearsWithCompany INT,
    EmployeeManagerID INT,
    ProjectID INT
);

-- Insert Values into Departments Table
INSERT INTO Departments (ID, DepartmentName, MainCity, DepartmentManagerID) VALUES
(6, 'IT', 'New York', 4),
(2, 'HR', 'Chicago', 8),
(3, 'Finance', 'Los Angeles', 17),
(8, 'Sales', 'New York', 22),
(5, 'Marketing', 'San Francisco', 30),
(4, 'Research', 'Boston', NULL),
(1, 'Support', 'Austin', NULL),
(7, 'Operations', 'Seattle', NULL);

-- Insert Values into Projects Table (with TimeRemaining in days)
INSERT INTO Projects (ID, ProjectName, ProjectManagerID, DepartmentID, TimeRemaining) VALUES
(1, 'Website Redesign', 4, 6, 60),
(2, 'Employee Training Program', 8, 2, 21),
(3, 'Annual Budgeting', 17, 3, 30),
(4, 'Product Launch', NULL, NULL, 180),
(5, 'Social Media Campaign', 30, 5, 7),
(6, 'New Product Research', NULL, 6, 90),
(7, 'Customer Service Initiative', 8, 2, 14),
(8, 'Logistics Optimization', NULL, 7, 150),
(9, 'Mobile App Development', 4, 6, 120),
(10, 'Internal Audit', 17, 3, 90);

-- Insert Values into Employees Table with shuffled ProjectID and restricted ManagerID
INSERT INTO Employees (ID, EmployeeName, City, DepartmentID, Salary, YearsWithCompany, EmployeeManagerID, ProjectID) VALUES
(1, 'John Doe', 'New York', 1, 85000.00, 5, 4, 9),
(2, 'Jane Smith', 'Chicago', 2, 90000.00, 8, 8, 10),
(3, 'Mike Brown', 'Los Angeles', 3, 95000.00, 7, 17, 3),
(4, 'Emily Davis', 'New York', 4, 175000.00, 7, NULL, 8),
(5, 'David Wilson', 'San Francisco', 5, 82000.00, 4, 30, 1),
(6, 'Linda Johnson', 'Boston', 6, 77000.00, 2, 4, 4),
(7, 'James Miller', 'Austin', 7, 65000.00, 6, 8, 2),
(8, 'Sarah Lee', 'Seattle', 8, 108000.00, 10, NULL, 7),
(9, 'Kevin Moore', 'New York', 1, 92000.00, 9, 4, 6),
(10, 'Paul Garcia', 'Chicago', 2, 98000.00, 5, 8, 5),
(11, 'Rachel Martinez', 'Los Angeles', 3, 102000.00, 2, 17, 1),
(12, 'Daniel Hernandez', 'San Francisco', 5, 94000.00, 1, 30, 2),
(13, 'Lisa Taylor', 'Boston', 6, 73000.00, 6, 4, 3),
(14, 'Matthew Anderson', 'Austin', 7, 64000.00, 7, 8, 9),
(15, 'Amanda Thomas', 'Seattle', 8, 105000.00, 4, 22, 4),
(16, 'Joshua Jackson', 'New York', 1, 110000.00, 4, 4, 10),
(17, 'Karen White', 'Chicago', 2, 76000.00, 3, NULL, 7),
(18, 'Christopher Lewis', 'Los Angeles', 3, 87000.00, 5, 17, 8),
(19, 'Nancy Walker', 'San Francisco', 5, 81000.00, 5, 30, 9),
(20, 'Justin Hall', 'Boston', 6, 72000.00, 2, 4, 6),
(21, 'Angela Allen', 'Austin', 7, 68000.00, 1, 8, 3),
(22, 'Brandon Young', 'Seattle', 8, 115000.00, 6, NULL, 5),
(23, 'Hannah King', 'New York', 1, 100000.00, 5, 4, 2),
(24, 'Steven Wright', 'Chicago', 2, 89000.00, 4, 8, 10),
(25, 'Rebecca Scott', 'Los Angeles', 3, 94000.00, 3, 17, 1),
(26, 'Patrick Green', 'San Francisco', 5, 104000.00, 8, 30, 6),
(27, 'Megan Adams', 'Boston', 6, 84000.00, 3, 4, 5),
(28, 'Samuel Baker', 'Austin', 7, 76000.00, 4, 8, 4),
(29, 'Olivia Nelson', 'Seattle', 8, 130000.00, 15, 22, 9),
(30, 'Dylan Carter', 'New York', 1, 135000.00, 10, NULL, 10);

-- Add Foreign Key Constraints

-- 4. Add Foreign Key to Departments Table
ALTER TABLE Departments
ADD CONSTRAINT FK_Departments_DepartmentManagerID
FOREIGN KEY (DepartmentManagerID) REFERENCES Employees(ID);

-- 5. Add Foreign Key to Projects Table
ALTER TABLE Projects
ADD CONSTRAINT FK_Projects_ProjectManagerID
FOREIGN KEY (ProjectManagerID) REFERENCES Employees(ID);

ALTER TABLE Projects
ADD CONSTRAINT FK_Projects_DepartmentID
FOREIGN KEY (DepartmentID) REFERENCES Departments(ID);

-- 6. Add Foreign Key to Employees Table
ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_DepartmentID
FOREIGN KEY (DepartmentID) REFERENCES Departments(ID);

ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_EmployeeManagerID
FOREIGN KEY (EmployeeManagerID) REFERENCES Employees(ID);

ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_ProjectID
FOREIGN KEY (ProjectID) REFERENCES Projects(ID);


-- Write a Select statement to create a report that displays
-- The name of the employee 
-- The City of the employee 
-- The project name. Unassigned projects and employees without projects should not appear
-- In the Results. Order the results in the ascending order
-- First by ProjectName, then by EmployeeName. 


SELECT Employees.EmployeeName , Employees.City, Projects.ProjectName 
FROM Employees 
INNER JOIN Projects ON Employees.ProjectID = Projects.ID 
ORDER BY Projects.ProjectName ASC, Employees.EmployeeName;