DROP DATABASE IF EXISTS fcuc_apms;
CREATE DATABASE fcuc_apms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fcuc_apms;


CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 username VARCHAR(100) NOT NULL UNIQUE,
 password VARCHAR(255) NOT NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE programmes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 programme_name VARCHAR(255) NOT NULL,
 approval_no VARCHAR(255) NULL,
 graduate_credits VARCHAR(50) NULL,
 approval_start DATE NULL,
 approval_end DATE NULL,
 coordinator_name VARCHAR(255) NULL,
 mobile_phone VARCHAR(50) NULL,
 office_phone VARCHAR(50) NULL,
 office_email VARCHAR(255) NULL,
 personal_email VARCHAR(255) NULL,
 photo_path VARCHAR(500) NULL,
 notes TEXT NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE person_records (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 record_type ENUM('staff','coordinator','external_examiner','iap','adjunct') NOT NULL,
 name VARCHAR(255) NOT NULL,
 programme_name VARCHAR(255) NULL,
 staff_id VARCHAR(100) NULL,
 affiliation VARCHAR(255) NULL,
 address TEXT NULL,
 duration_start DATE NULL,
 duration_end DATE NULL,
 years DECIMAL(5,2) NULL,
 mobile_phone VARCHAR(50) NULL,
 office_phone VARCHAR(50) NULL,
 office_email VARCHAR(255) NULL,
 personal_email VARCHAR(255) NULL,
 photo_path VARCHAR(500) NULL,
 notes TEXT NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_record_type(record_type), INDEX idx_name(name), INDEX idx_programme(programme_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 person_record_id BIGINT UNSIGNED NULL,
 programme_id BIGINT UNSIGNED NULL,
 title VARCHAR(255) NOT NULL,
 file_path VARCHAR(500) NOT NULL,
 original_name VARCHAR(255) NOT NULL,
 mime_type VARCHAR(150) NULL,
 created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_docs_person FOREIGN KEY(person_record_id) REFERENCES person_records(id) ON DELETE CASCADE,
 CONSTRAINT fk_docs_programme FOREIGN KEY(programme_id) REFERENCES programmes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO programmes(programme_name, graduate_credits) VALUES
('Foundation in Computer Science and Technology - FiCST (50)','50'),
('Diploma in Information Technology - DIT (90)','90'),
('Diploma in Computer Science - DCS (90)','90'),
('Bachelor in Information Systems (Business Management) (Honours) - BISBM (121)','121'),
('Bachelor in Computer Science (Intelligent Systems) (Honours) - BCSIS (122)','122'),
('Bachelor of Information Technology (Networking and Security) (Honours) - BITNS (127)','127'),
('Bachelor of Software Engineering (Honours) - BSE (128)','128'),
('Master of Computer Science (Research)',NULL),
('Doctor of Philosophy (PhD) in Applied Science (Research)',NULL),
('Others',NULL);

INSERT INTO users(name, username, password) VALUES
('Administrator', 'admin', '$2y$12$7XsPA1pPivAtH2OKTuvXN.eyyEzduuvP6.qsWrFYwDbOiWI5iGQQ2');