
-- Database Schema for EduStaff Pro
CREATE DATABASE IF NOT EXISTS edustaff_db;
USE edustaff_db;

-- Departments Table
CREATE TABLE IF NOT EXISTS departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Rankings / Job Titles Table (NEW)
CREATE TABLE IF NOT EXISTS rankings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Staff Table (HR Records)
CREATE TABLE IF NOT EXISTS staff (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_code VARCHAR(20) UNIQUE NOT NULL,
    full_name VARCHAR(200) NOT NULL,
    dob DATE NOT NULL,
    gender ENUM('Male', 'Female', 'Other', 'Prefer not to say') DEFAULT 'Prefer not to say',
    phone VARCHAR(20),
    email VARCHAR(150),
    address TEXT,
    emergency_name VARCHAR(100),
    emergency_phone VARCHAR(20),
    job_title VARCHAR(100) NOT NULL, -- Storing the name of the rank for easier reporting
    department VARCHAR(100) NOT NULL,
    staff_type ENUM('Permanent', 'Contract', 'Part-time') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE DEFAULT NULL,
    supervisor VARCHAR(100),
    highest_qualification VARCHAR(255),
    certifications_summary TEXT,
    notes TEXT, -- Restricted to HR/SuperAdmin
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Documents Table
CREATE TABLE IF NOT EXISTS documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    category ENUM('Contract', 'ID', 'Certificate', 'Appraisal', 'Discipline', 'Leave', 'Other') NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    size INT NOT NULL,
    expiry_date DATE DEFAULT NULL,
    uploaded_by INT NULL, -- Nullable for external uploads
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- Leave Requests Table
CREATE TABLE IF NOT EXISTS leave_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    leave_type ENUM('Annual', 'Sick', 'Casual', 'Maternity', 'Paternity', 'Study', 'Other') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    reason TEXT,
    attachment_document_id INT DEFAULT NULL,
    status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
    requested_by INT NULL, -- Nullable if requested via external link
    approved_by INT DEFAULT NULL,
    approval_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE,
    FOREIGN KEY (requested_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- Appraisals Table
CREATE TABLE IF NOT EXISTS appraisals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    review_period VARCHAR(100) NOT NULL, -- e.g. "Q1 2023" or "Annual 2023"
    score INT NOT NULL, -- 1 to 5 or 1 to 10
    comments TEXT,
    evaluator_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE,
    FOREIGN KEY (evaluator_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- Audit Logs Table
CREATE TABLE IF NOT EXISTS audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT DEFAULT NULL,
    details_json JSON DEFAULT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Seed Departments
INSERT IGNORE INTO departments (name) VALUES 
('Academic'), ('Administration'), ('Mathematics'), ('Science'), ('Humanities'), ('Arts'), ('Sports');

-- Seed Rankings
INSERT IGNORE INTO rankings (name) VALUES 
('Principal'), ('Vice Principal'), ('Senior Tutor'), ('Senior IT Manager'), ('Teacher'), ('Junior Staff'), ('Intern');

-- Sample Staff
INSERT INTO staff (staff_code, full_name, dob, gender, phone, email, job_title, department, staff_type, start_date, status) VALUES 
('STF001', 'John Doe', '1985-05-15', 'Male', '555-0101', 'john.doe@school.com', 'Senior Tutor', 'Mathematics', 'Permanent', '2020-09-01', 'active'),
('STF002', 'Jane Smith', '1990-11-22', 'Female', '555-0102', 'jane.smith@school.com', 'Senior IT Manager', 'Administration', 'Permanent', '2021-02-15', 'active');
