pms.sql

--CREATE--
CREATE TABLE Staff (
	staff_id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY 
,	staff_name VARCHAR2(255) NOT NULL
,	staff_email VARCHAR2(255) NOT NULL
,	staff_position VARCHAR2(255) NOT NULL
,	CONSTRAINT pk_staff PRIMARY KEY (staff_id)
,	CONSTRAINT unique_email UNIQUE (staff_email)
);

CREATE TABLE Account (
	account_id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY 
,	account_username VARCHAR2(255) NOT NULL 
,	account_password VARCHAR2(255) NOT NULL
,	account_profile_image VARCHAR2(1000) DEFAULT 'https://firebasestorage.googleapis.com/v0/b/project-management-74384.appspot.com/o/profile_images%2Fprofile.png?alt=media&token=befbfdfd-1a0a-4498-ad69-d6769b1d9755'
,	account_role VARCHAR2(255) NOT NULL
,	staff_id NUMBER(19) NOT NULL
,	CONSTRAINT pk_account PRIMARY KEY (account_id)
,	CONSTRAINT fk_account_staff FOREIGN KEY (staff_id) REFERENCES Staff (staff_id) ON DELETE SET NULL
,	CONSTRAINT unique_username UNIQUE (account_username)
,	CONSTRAINT unique_staff_id UNIQUE (staff_id)
);

CREATE TABLE Project (
	project_id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY 
,	project_code VARCHAR2(5) NOT NULL 
,	project_description VARCHAR2(255) NOT NULL
,	project_details VARCHAR2(1000)
,	project_is_active NUMBER(1) NOT NULL
, 	manager_id NUMBER(19) 
,	CONSTRAINT pk_project PRIMARY KEY (project_id)
,	CONSTRAINT fk_project_staff FOREIGN KEY (manager_id) REFERENCES Staff (staff_id) ON DELETE SET NULL 
,   CONSTRAINT unique_project_code UNIQUE (project_code)
,	CONSTRAINT unique_manager_id UNIQUE (manager_id)
,	CONSTRAINT check_is_active CHECK (project_is_active IN (0, 1))
);

CREATE TABLE Issue (
	issue_id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY 
,	issue_code VARCHAR(5) NOT NULL 
,	issue_description VARCHAR2(255) NOT NULL 
,	issue_details VARCHAR2(1000)
,	due_date DATE NOT NULL 
,	issue_status VARCHAR(50) DEFAULT 'CLOSE'
,   assignee_id NUMBER(19) 
,	project_id NUMBER(19) NOT NULL 
,	CONSTRAINT pk_issue PRIMARY KEY (issue_id)
,	CONSTRAINT fk_issue_staff FOREIGN KEY (assignee_id) REFERENCES Staff (staff_id) ON DELETE SET NULL 
,	CONSTRAINT fk_issue_project FOREIGN KEY (project_id) REFERENCES Project (project_id) ON DELETE SET NULL 
,	CONSTRAINT unique_issue_code UNIQUE (issue_code)
,	CONSTRAINT unique_assignee_id UNIQUE (assignee_id)
);

CREATE TABLE Issue_History (
	ihistory_id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY
,	issue_id NUMBER(19)
,	issue_description VARCHAR2(255)
,	issue_details CLOB
,	due_date DATE  
,	issue_status VARCHAR(50)  
,	updated_time TIMESTAMP DEFAULT SYSTIMESTAMP 
,	assignee_id NUMBER(19)
,	CONSTRAINT pk_ihistory PRIMARY KEY (ihistory_id)
);

CREATE TABLE Deleted_Staff (
	id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY 
,	staff_id NUMBER(19) 
,	staff_name VARCHAR2(255)
,	staff_email VARCHAR2(255)
,	staff_position VARCHAR2(255)
,	time TIMESTAMP DEFAULT SYSTIMESTAMP
,	CONSTRAINT pk_deleted_staff PRIMARY KEY (id)
);

CREATE TABLE Deleted_Account (
	id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY
,	account_id NUMBER(19)  
,	account_username VARCHAR2(255)
,	account_password VARCHAR2(255)
,	account_profile_image VARCHAR2(1000)
,	account_role VARCHAR2(255) 
,	staff_id NUMBER(19)
,	time TIMESTAMP DEFAULT SYSTIMESTAMP
,	CONSTRAINT pk_deleted_account PRIMARY KEY (id)
);

CREATE TABLE Deleted_Project (
	id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY	
,	project_id NUMBER(19)
,	project_code VARCHAR2(5) 
,	project_description VARCHAR2(255) 
,	project_details VARCHAR2(1000)
, 	manager_id NUMBER(19)
,	time TIMESTAMP DEFAULT SYSTIMESTAMP
,	CONSTRAINT pk_deleted_project PRIMARY KEY (id)
);

CREATE TABLE Deleted_Issue (
	id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY
,	issue_id NUMBER(19)  
,	issue_code VARCHAR(5)
,	issue_description VARCHAR2(255)
,	issue_details VARCHAR2(1000)
,	due_date DATE
,	issue_status VARCHAR(50)
,   assignee_id NUMBER(19)
,	project_id NUMBER(19)
,	time TIMESTAMP DEFAULT SYSTIMESTAMP
,	CONSTRAINT pk_deleted_issue PRIMARY KEY (id)
);

--INSERT--
CREATE OR REPLACE PROCEDURE insert_staff (p_name IN Staff.staff_name%TYPE,
										  p_email IN Staff.staff_email%TYPE,
										  p_position IN Staff.staff_position%TYPE) 
IS 
BEGIN 
	INSERT INTO Staff ("STAFF_NAME", "STAFF_EMAIL", "STAFF_POSITION") 
				VALUES (UPPER(p_name), UPPER(p_email), p_position);
	COMMIT;
END;

CREATE OR REPLACE PROCEDURE insert_account (p_username IN Account.account_username%TYPE,
										  p_password IN Account.account_password%TYPE,
										  p_role IN Account.account_role%TYPE,
										  p_staff_id IN Account.staff_id%TYPE) 
IS 
BEGIN 
	INSERT INTO Account ("ACCOUNT_USERNAME", "ACCOUNT_PASSWORD", "ACCOUNT_ROLE", "STAFF_ID") 
				VALUES (p_username, p_password, p_role, p_staff_id);
	COMMIT;
END;

CREATE OR REPLACE PROCEDURE insert_project (p_code IN Project.project_code%TYPE,
										  p_description IN Project.project_description%TYPE,
										  p_details IN Project.project_details%TYPE,
										  p_is_active IN Project.project_is_active%TYPE := 1,
										  p_manager_id IN Project.manager_id%TYPE := NULL) 
IS 
BEGIN 
	INSERT INTO Project ("PROJECT_CODE", "PROJECT_DESCRIPTION", "PROJECT_DETAILS", "PROJECT_IS_ACTIVE", "MANAGER_ID") 
				VALUES (UPPER(p_code), UPPER(p_description), p_details, p_is_active, p_manager_id);
	COMMIT;
END;

CREATE OR REPLACE PROCEDURE insert_issue (p_code IN Issue.issue_code%TYPE,
										  p_description IN Issue.issue_description%TYPE,
										  p_details IN Issue.issue_details%TYPE,
										  p_due_date IN Issue.due_date%TYPE,
										  p_status IN Issue.issue_status%TYPE := 'CLOSE',
										  p_project_id IN Issue.project_id%TYPE) 
IS 
BEGIN 
	INSERT INTO Issue ("ISSUE_CODE", "ISSUE_DESCRIPTION", "ISSUE_DETAILS", "DUE_DATE", "ISSUE_STATUS", "PROJECT_ID") 
				VALUES (UPPER(p_code), UPPER(p_description), p_details, p_due_date, p_status, p_project_id);
	COMMIT;
END;

--UPDATE--
CREATE OR REPLACE PROCEDURE update_staff (p_name IN Staff.staff_name%TYPE,
										  p_email IN Staff.staff_email%TYPE,
										  p_position IN Staff.staff_position%TYPE,
										  p_staff_id IN Staff.staff_id%TYPE) 
IS 
BEGIN 
	UPDATE Staff SET staff_name = UPPER(p_name), staff_email = UPPER(p_email), staff_position = p_position WHERE staff_id = p_staff_id;
	COMMIT;
END;

CREATE OR REPLACE PROCEDURE update_account (p_username IN Account.account_username%TYPE,
										  p_password IN Account.account_password%TYPE,
										  p_role IN Account.account_role%TYPE,
										  p_profile_image IN Account.account_profile_image%TYPE,
										  p_account_id IN Account.account_id%TYPE) 
IS 
BEGIN 
	UPDATE Account SET account_username = UPPER(p_username), account_password = p_password, account_role = p_role, account_profile_image = p_profile_image WHERE account_id = p_account_id;
	COMMIT;
END;

CREATE OR REPLACE PROCEDURE update_project (p_code IN Project.project_code%TYPE,
										  p_description IN Project.project_description%TYPE,
										  p_details IN Project.project_details%TYPE,
										  p_is_active IN Project.project_is_active%TYPE,
										  p_manager_id IN Project.manager_id%TYPE,	
										  p_project_id IN Project.project_id%TYPE) 
IS 
BEGIN 
	UPDATE Project SET project_code = UPPER(p_code), project_description = UPPER(p_description), project_details = p_details, project.project_is_active = p_is_active, manager_id = p_manager_id WHERE project_id = p_project_id;
	COMMIT;
END;

CREATE OR REPLACE PROCEDURE update_issue (p_description IN Issue.issue_description%TYPE,
										  p_details IN Issue.issue_details%TYPE,
										  p_due_date IN Issue.due_date%TYPE,
										  p_status IN Issue.issue_status%TYPE,
										  p_assignee_id IN Issue.assignee_id%TYPE,
										  p_issue_id IN Issue.issue_id%TYPE) 
IS 
BEGIN 
	UPDATE Issue SET issue_description = UPPER(p_description), issue_details = p_details, due_date = p_due_date, issue_status = p_status, assignee_id = p_assignee_id WHERE issue_id = p_issue_id; 
	COMMIT;
END;

BEGIN
	update_issue('UPDATE DESC-1', 'UPDATE DETAILS-1', DATE '2021-02-2', 'RESOLVED', NULL, 7);
END;

CREATE OR REPLACE TRIGGER update_issue_trigger 
	AFTER UPDATE ON Issue FOR EACH ROW 
BEGIN 
	INSERT INTO Issue_History (issue_id, issue_description, issue_details, due_date, issue_status, assignee_id)
						VALUES (:OLD.issue_id, :OLD.issue_description, :OLD.issue_details, :OLD.due_date, :OLD.issue_status, :OLD.assignee_id);
END;

--DELETE--
CREATE OR REPLACE TRIGGER delete_staff_trigger
	AFTER DELETE ON Staff FOR EACH ROW
BEGIN 
	INSERT INTO Deleted_Staff (staff_id, staff_name, staff_email, staff_position)
						VALUES (:OLD.staff_id, :OLD.staff_name, :OLD.staff_email, :OLD.staff_position);
	DELETE FROM Account WHERE staff_id = :OLD.staff_id;
END;

CREATE OR REPLACE TRIGGER delete_account_trigger
	AFTER DELETE ON Account FOR EACH ROW
BEGIN 
	INSERT INTO Deleted_Account (account_id, account_username, account_password, account_profile_image, account_role, staff_id)
						VALUES (:OLD.account_id, :OLD.account_username, :OLD.account_password, :OLD.account_profile_image, :OLD.account_role, :OLD.staff_id);
END;

CREATE OR REPLACE TRIGGER delete_project_trigger
	AFTER DELETE ON Project FOR EACH ROW
BEGIN 
	INSERT INTO Deleted_Project (project_id, project_code, project_description, project_details, manager_id)
						VALUES (:OLD.project_id, :OLD.project_code, :OLD.project_description, :OLD.project_details, :OLD.manager_id);
END;

CREATE OR REPLACE TRIGGER delete_issue_trigger
	AFTER DELETE ON Issue FOR EACH ROW
BEGIN 
	INSERT INTO Deleted_Issue (issue_id, issue_code, issue_description, issue_details, due_date, issue_status, assignee_id, project_id)
						VALUES (:OLD.issue_id, :OLD.issue_code, :OLD.issue_description, :OLD.issue_details, :OLD.due_date, :OLD.issue_status, :OLD.assignee_id, :OLD.project_id);
END;

--VIEW--
CREATE OR REPLACE VIEW get_project_with_manager AS
	SELECT p.project_id, p.project_code, p.project_description, p.project_details, p.project_is_active, p.manager_id, s.staff_name AS manager_name, s.staff_email AS manager_email 
	FROM Project p JOIN Staff s ON p.manager_id = s.staff_id ORDER BY p.project_is_active DESC; 

CREATE OR REPLACE VIEW get_issue_with_assignee AS
	SELECT i.*, s.staff_name AS assignee_name, s.staff_email AS assignee_email
	FROM Issue i LEFT JOIN Staff s ON i.assignee_id = s.staff_id ORDER BY i.due_date, i.issue_status;

CREATE OR REPLACE VIEW get_manager_without_project AS
	SELECT staff_id, staff_name, staff_email FROM Staff WHERE staff_position = 'PROJECT_MANAGER' AND staff_id NOT IN (SELECT manager_id FROM get_project_with_manager);

CREATE OR REPLACE VIEW get_staff_with_username_and_role AS 
	SELECT s.*, a.account_username, a.account_role FROM Staff s LEFT JOIN Account a ON s.staff_id = a.staff_id;

CREATE OR REPLACE VIEW get_issue_history_with_assignee AS
	SELECT ih.issue_id, ih.issue_description, ih.issue_details, ih.due_date, ih.issue_status, ih.updated_time, s.staff_name AS assignee_name, s.staff_email AS assignee_email FROM Issue_History ih LEFT JOIN Staff s ON ih.assignee_id = s.staff_id;