-- 0. Create the database
CREATE DATABASE InternalSite;
USE InternalSite;

------------------------------------------------------------
-- TABLES
------------------------------------------------------------

-- 1. divisions
CREATE TABLE divisions (
    division_id INT PRIMARY KEY,
    division_name VARCHAR(255)
);

-- 2. roles
CREATE TABLE roles (
    role_id INT PRIMARY KEY,
    role_name VARCHAR(255)
);

-- 3. divisions_roles
CREATE TABLE divisions_roles (
    division_id INT,
    role_id INT,
    PRIMARY KEY (division_id, role_id),
    FOREIGN KEY (division_id) REFERENCES divisions(division_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id)
);

-- 4. employees
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    division_id INT,
    role_id INT,
    remarks VARCHAR(255),
    FOREIGN KEY (division_id) REFERENCES divisions(division_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id)
);

-- 5. users
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    employee_id INT,
    username VARCHAR(255),
    password VARCHAR(255),
    division_id INT,
    role_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
    FOREIGN KEY (division_id) REFERENCES divisions(division_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id)
);

-- 6. project_type
CREATE TABLE project_type (
    type_id INT PRIMARY KEY,
    type_name VARCHAR(255)
);

-- 7. project_status
CREATE TABLE project_status (
    status_id INT PRIMARY KEY,
    status_name VARCHAR(255)
);

-- 8. invoice_status
CREATE TABLE invoice_status (
    invoice_status_id INT PRIMARY KEY,
    invoice_status_name VARCHAR(255)
);

-- 9. project_priority_level
CREATE TABLE project_priority_level (
    priority_level_id INT PRIMARY KEY,
    priority_level_name VARCHAR(255)
);

-- 10. po_status
CREATE TABLE po_status(
    id INT PRIMARY KEY,
    state VARCHAR(30) NOT NULL UNIQUE
);

-- 11. projects 
CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(255),
    project_lead_emp_id INT,
    type_id INT,
    status_id INT,
    invoice_status_id INT,
    priority_level_id INT,
    project_start_date DATE,
    deadline DATE,
    percent_completed INT,
    estimated_hours DECIMAL(10,2),
    actual_hours DECIMAL(10,2),
    remarks VARCHAR(255),
    division_id INT,
    kickoff_form LONGBLOB,
    po_status_id INT,
    FOREIGN KEY (project_lead_emp_id) REFERENCES employees(employee_id),
    FOREIGN KEY (type_id) REFERENCES project_type(type_id),
    FOREIGN KEY (status_id) REFERENCES project_status(status_id),
    FOREIGN KEY (invoice_status_id) REFERENCES invoice_status(invoice_status_id),
    FOREIGN KEY (priority_level_id) REFERENCES project_priority_level(priority_level_id),
    FOREIGN KEY (division_id) REFERENCES divisions(division_id),
    FOREIGN KEY (po_status_id) REFERENCES po_status(id)
);

-- 12. employees_projects
CREATE TABLE employees_projects (
    employee_id INT,
    project_id INT,
    PRIMARY KEY (employee_id, project_id),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
    FOREIGN KEY (project_id) REFERENCES projects(project_id)
);

-- 13. employee_log
CREATE TABLE employee_log (
    log_id INT PRIMARY KEY,
    user_id INT,
    employee_name VARCHAR(255),
    division_name VARCHAR(255),
    role_name VARCHAR(255),
    project_names VARCHAR(255),
    action VARCHAR(255),
    remarks VARCHAR(255),
    created_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 14. project_log
CREATE TABLE project_log (
    log_id INT PRIMARY KEY,
    user_id INT,
    project_name VARCHAR(255),
    type_name VARCHAR(255),
    status_name VARCHAR(255),
    invoice_status_name VARCHAR(255),
    priority_level_name VARCHAR(255),
    deadline DATE,
    percent_completed INT,
    estimated_hours DECIMAL(10,2),
    actual_hours DECIMAL(10,2),
    remarks VARCHAR(255),
    action VARCHAR(255),
    created_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 15. kickoff_forms
CREATE TABLE kickoff_forms (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_type VARCHAR(255) NOT NULL,
    form LONGBLOB NOT NULL
);

-- 16. employee_contributions
-- New table to store employee contributions
CREATE TABLE employee_contributions (
    contribution_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    project_id INT NOT NULL,
    contribution_percentage DECIMAL(5,2) DEFAULT 0.00,
    contribution_description VARCHAR(255),
    date_logged TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
    FOREIGN KEY (project_id) REFERENCES projects(project_id)
);

------------------------------------------------------------
-- VIEWS
------------------------------------------------------------

-- 16. View: employee_project_view
CREATE VIEW employee_project_view AS
SELECT 
    e.employee_id,
    e.employee_name,
    d.division_name,
    r.role_name,
    COALESCE(GROUP_CONCAT(p.project_name SEPARATOR ', '), 'no_projects') AS project_names,
    e.remarks
FROM employees e
LEFT JOIN divisions d ON e.division_id = d.division_id
LEFT JOIN roles r ON e.role_id = r.role_id
LEFT JOIN employees_projects ep ON e.employee_id = ep.employee_id
LEFT JOIN projects p ON ep.project_id = p.project_id
GROUP BY e.employee_id, e.employee_name, d.division_name, r.role_name, e.remarks
ORDER BY e.employee_id;

-- 17. View: project_view
CREATE OR REPLACE VIEW project_view AS
SELECT 
    p.project_id,
    p.project_name,
    p.project_lead_emp_id,
    pl.employee_name AS project_lead_name,
    pt.type_name,
    ps.status_name,
    ins.invoice_status_name,
    ppl.priority_level_name,
    p.project_start_date,
    p.deadline,
    p.percent_completed,
    p.estimated_hours,
    p.actual_hours,
    p.remarks,
    d.division_name,
    pos.state AS po_status
FROM projects p
LEFT JOIN employees pl ON p.project_lead_emp_id = pl.employee_id
LEFT JOIN project_type pt ON p.type_id = pt.type_id
LEFT JOIN project_status ps ON p.status_id = ps.status_id
LEFT JOIN invoice_status ins ON p.invoice_status_id = ins.invoice_status_id
LEFT JOIN project_priority_level ppl ON p.priority_level_id = ppl.priority_level_id
LEFT JOIN divisions d ON p.division_id = d.division_id
LEFT JOIN po_status pos ON p.po_status_id = pos.id;

-- 18. View: view_employees_under_projects
CREATE VIEW view_employees_under_projects AS
SELECT
    p.project_id,
    p.project_name,
    e.employee_id,
    e.employee_name,
    d.division_name,
    r.role_name
FROM employees_projects ep
JOIN employees e ON ep.employee_id = e.employee_id
LEFT JOIN projects p ON ep.project_id = p.project_id
LEFT JOIN divisions d ON e.division_id = d.division_id
LEFT JOIN roles r ON e.role_id = r.role_id;

-- 19. View: view_project_team_with_lead
CREATE VIEW view_project_team_with_lead AS
SELECT
    p.project_id,
    p.project_name,
    ep.employee_id,
    pl.employee_name AS project_lead_name,
    ps.status_name AS project_status
FROM projects p
JOIN employees_projects ep ON p.project_id = ep.project_id
JOIN employees e ON ep.employee_id = e.employee_id
LEFT JOIN employees pl ON p.project_lead_emp_id = pl.employee_id
LEFT JOIN project_status ps ON p.status_id = ps.status_id;


------------------------------------------------------------
-- INSERTS
------------------------------------------------------------
-- Insert data into divisions
INSERT INTO divisions (division_id, division_name) VALUES
(1, 'Software and LMS'),
(2, 'Maintenance SA'),
(3, 'Maintenance OUT'),
(4, 'VR'),
(5, 'E-Learning-SV'),
(6, 'E-Learning-M'),
(7, 'Video Production'),
(8, 'AV-SV'),
(9, 'AV-M'),
(10, 'Branding Design and Printing'),
(11, 'AI'),
(12, 'Simulator'),
(13, 'Business'),
(14, 'Accounts, Admin and Coordination'),
(15, 'Directors');

-- Insert data into roles
INSERT INTO roles (role_id, role_name) VALUES
(1, 'Director'),
(2, 'Junior-Director'),
(3, 'Team Leader'),
(4, 'Supporting Leader'),
(5, 'Senior Team Member'),
(6, 'Junior Team Member'),
(7, 'Intern');

-- Insert data into divisions_roles
INSERT INTO divisions_roles (division_id, role_id) VALUES
(15, 1),
(14, 2),
(14, 3),
(4, 3),
(6, 3),
(10, 3),
(13, 3),
(10, 4);

-- Insert data into employees
INSERT INTO employees (employee_id, employee_name, division_id, role_id, remarks) VALUES
(1, 'Arnab Das', 15, 1, 'Director'),
(2, 'Indranil Choudhury', 15, 1, 'Director'),
(3, 'Partha Sarathi', 15, 1, 'Director'),
(4, 'Shounak Choudhury', 14, 2, 'Junior Director'),
(5, 'Ankit Chatterjee', 14, 2, 'Junior Director'),
(6, 'Supratim Sarkar', 14, 2, 'Management'),
(7, 'Abhilash Guhathakurta', 4, 3, 'Software Developer'),
(8, 'Snehasis Bhowmik', 6, 3, 'HR'),
(9, 'Arka Bose', 13, 3, 'VR'),
(10, 'Biswajit Mondal', 10, 3, 'Branding Designer'),
(11, 'Debolina Di', 6, 3, 'Graphic Designer'),
(12, 'Rao Da', 14, 3, 'Accounts'),
(13, 'Avijit Patra', 1, 6, 'Software Developer'),
(14, 'Ananya Ghosh', 1, 6, 'Software Developer'),
(15, 'Koushik Debnath', 1, 6, 'Software Developer');


-- Insert data into invoice_status
INSERT INTO invoice_status (invoice_status_id, invoice_status_name) VALUES
(1, 'Pending'),
(2, 'Submitted'),
(3, 'Payment Received'),
(4, 'Not Submitted'),
(5, 'Internal'),
(6, 'Part Payment Received');


-- Insert data into po_status
INSERT INTO po_status (id, state) VALUES
(1, 'PO Received'),
(2, 'PO Not Received'),
(3, 'Internal');

-- Insert data into project_priority_level
INSERT INTO project_priority_level (priority_level_id, priority_level_name) VALUES
(1, 'Low'),
(2, 'Medium'),
(3, 'High');

-- Insert data into project_status
INSERT INTO project_status (status_id, status_name) VALUES
(1, 'Ongoing'),
(2, 'Completed'),
(3, 'Extended'),
(4, 'Opened'),
(5, 'Recurring'),
(6, 'Shelved');

-- Insert data into project_type
INSERT INTO project_type (type_id, type_name) VALUES
(1, 'Software'),
(2, 'LMS'),
(3, 'Simulator'),
(4, 'VR'),
(5, 'AI'),
(6, 'E-Learning'),
(7, 'AV'),
(8, 'Branding and Design'),
(9, 'Maintenance');


-- Insert data into users with SHA2 hashed password (256-bit)
INSERT INTO users (user_id, employee_id, username, password, division_id, role_id) VALUES
(1, 13, 'avi', SHA2('1', 256), 1, 6),
(2, 14, 'ananya', SHA2('1', 256), 1, 6),
(3, 15, 'koushik', SHA2('1', 256), 1, 6);
