CREATE DATABASE IF NOT EXISTS total_tvc CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE total_tvc;

CREATE TABLE users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(80) NOT NULL UNIQUE,
 full_name VARCHAR(160) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('trainer','hod','qa','registrar','dpa','admin') NOT NULL DEFAULT 'trainer',
 department VARCHAR(160) NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE settings (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 setting_key VARCHAR(100) NOT NULL UNIQUE,
 setting_value TEXT NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE submissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id INT UNSIGNED NOT NULL,
 document_type ENUM('learning_plan','session_plan','record_of_work','attendance','professional_tool') NOT NULL,
 title VARCHAR(255) NOT NULL,
 course VARCHAR(190) NULL,
 class_name VARCHAR(190) NULL,
 document_date DATE NULL,
 file_path VARCHAR(500) NULL,
 description TEXT NULL,
 status ENUM('Submitted','Pending HOD Review','Approved','Rejected','Needs Correction') NOT NULL DEFAULT 'Pending HOD Review',
 reviewer_id INT UNSIGNED NULL,
 reviewer_comment TEXT NULL,
 reviewed_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(user_id), INDEX(status), INDEX(document_type),
 CONSTRAINT fk_sub_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_sub_reviewer FOREIGN KEY(reviewer_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE teaching_updates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 trainer_id INT UNSIGNED NOT NULL,
 unit_course VARCHAR(190) NOT NULL,
 class_name VARCHAR(190) NULL,
 week_session VARCHAR(100) NULL,
 planned_topic VARCHAR(255) NULL,
 actual_topic TEXT NOT NULL,
 completion_status ENUM('Completed as planned','Partially completed','Not completed','Covered ahead of plan') NOT NULL,
 teaching_date DATE NOT NULL,
 reflection TEXT NULL,
 action_required TEXT NULL,
 review_status ENUM('Pending HOD Review','Approved','Rejected','Needs Correction') NOT NULL DEFAULT 'Pending HOD Review',
 hod_id INT UNSIGNED NULL,
 hod_comment TEXT NULL,
 reviewed_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(trainer_id), INDEX(review_status),
 FOREIGN KEY(trainer_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(hod_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE timetable (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 trainer_id INT UNSIGNED NOT NULL,
 unit_course VARCHAR(190) NOT NULL,
 class_name VARCHAR(190) NOT NULL,
 day_name VARCHAR(20) NOT NULL,
 start_time TIME NOT NULL,
 end_time TIME NOT NULL,
 room VARCHAR(100) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(trainer_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE attendance (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 trainer_id INT UNSIGNED NOT NULL,
 class_name VARCHAR(190) NOT NULL,
 attendance_date DATE NOT NULL,
 total_students INT NOT NULL DEFAULT 0,
 present_students INT NOT NULL DEFAULT 0,
 notes TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(trainer_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(attendance_date)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id INT UNSIGNED NULL,
 action VARCHAR(100) NOT NULL,
 entity VARCHAR(100) NULL,
 entity_id BIGINT UNSIGNED NULL,
 details TEXT NULL,
 ip_address VARCHAR(45) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(created_at), INDEX(action)
) ENGINE=InnoDB;

INSERT INTO settings(setting_key,setting_value) VALUES
('institution_name','TOTAL TECHNICAL AND VOCATIONAL COLLEGE'),
('mail_from_name','TOTAL TVC Academic Monitoring System'),
('mail_from',''),
('smtp_host',''),
('smtp_port','587'),
('smtp_username',''),
('smtp_password',''),
('smtp_encryption','tls'),
('approval_subject','TOTAL TVC – Academic Submission Update');

-- Demo password for all seed accounts: password
INSERT INTO users(username,full_name,email,password_hash,role,department) VALUES
('admin','System Administrator','admin@example.com','$2y$12$dp9qxL6NYIE6UdJZkfaBP.l1MUtahONWdiwazQHcpZEx8Q7.0/dzS','admin','Administration'),
('hod.business','HOD Business Studies','hod.business@example.com','$2y$12$dp9qxL6NYIE6UdJZkfaBP.l1MUtahONWdiwazQHcpZEx8Q7.0/dzS','hod','Business Studies'),
('qa.officer','Quality Assurance Officer','qa@example.com','$2y$12$dp9qxL6NYIE6UdJZkfaBP.l1MUtahONWdiwazQHcpZEx8Q7.0/dzS','qa','Quality Assurance'),
('registrar','Registrar','registrar@example.com','$2y$12$dp9qxL6NYIE6UdJZkfaBP.l1MUtahONWdiwazQHcpZEx8Q7.0/dzS','registrar','Registry'),
('dpa','Deputy Principal Academics','dpa@example.com','$2y$12$dp9qxL6NYIE6UdJZkfaBP.l1MUtahONWdiwazQHcpZEx8Q7.0/dzS','dpa','Academics'),
('trainer','Demo Trainer','trainer@example.com','$2y$12$dp9qxL6NYIE6UdJZkfaBP.l1MUtahONWdiwazQHcpZEx8Q7.0/dzS','trainer','ICT');