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

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NULL,
    department_id INT NULL,
    position_id INT NULL,
    supervisor_position_id INT NULL,
    job_title VARCHAR(150) NULL,
    employment_status VARCHAR(50) NOT NULL DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
);

CREATE TABLE positions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    position_title VARCHAR(150) NOT NULL,
    department_id INT NULL,
    employee_id INT NULL UNIQUE,
    reports_to_position_id INT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pos_dept FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
    CONSTRAINT fk_pos_reports_to FOREIGN KEY (reports_to_position_id) REFERENCES positions(id) ON DELETE SET NULL,
    CONSTRAINT fk_pos_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL
);

ALTER TABLE employees
    ADD CONSTRAINT fk_emp_position FOREIGN KEY (position_id) REFERENCES positions(id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_emp_supervisor_pos FOREIGN KEY (supervisor_position_id) REFERENCES positions(id) ON DELETE SET NULL;

CREATE TABLE position_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    position_id INT NOT NULL,
    effective_date DATE NOT NULL,
    change_reason VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (position_id) REFERENCES positions(id) ON DELETE CASCADE
);

CREATE TABLE requisitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    position_id INT NOT NULL,
    request_type ENUM('new_position','fill_existing') NOT NULL,
    request_reason TEXT NULL,
    requested_by_user_id INT NOT NULL,
    approval_status ENUM('pending','approved','rejected','auto_approved') NOT NULL DEFAULT 'pending',
    approved_by_user_id INT NULL,
    approved_at DATETIME NULL,
    approval_note VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (position_id) REFERENCES positions(id),
    FOREIGN KEY (requested_by_user_id) REFERENCES users(id),
    FOREIGN KEY (approved_by_user_id) REFERENCES users(id)
);

CREATE TABLE job_postings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    requisition_id INT NOT NULL,
    position_id INT NOT NULL,
    posting_title VARCHAR(150) NOT NULL,
    posting_description TEXT NULL,
    posting_status ENUM('Draft','Open','Closed') NOT NULL DEFAULT 'Open',
    posted_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (requisition_id) REFERENCES requisitions(id) ON DELETE CASCADE,
    FOREIGN KEY (position_id) REFERENCES positions(id)
);

CREATE TABLE rehire_blacklist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NULL,
    email VARCHAR(150) NOT NULL,
    reason VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_blacklist_email (email)
);

CREATE TABLE applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_posting_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    is_rehire TINYINT(1) NOT NULL DEFAULT 0,
    blacklist_match TINYINT(1) NOT NULL DEFAULT 0,
    status VARCHAR(50) NOT NULL DEFAULT 'Submitted',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_posting_id) REFERENCES job_postings(id) ON DELETE CASCADE
);

CREATE TABLE discipline_actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    level_name VARCHAR(100) NOT NULL,
    reason VARCHAR(255) NOT NULL,
    override_flag TINYINT(1) NOT NULL DEFAULT 0,
    employee_status_result VARCHAR(50) NULL,
    action_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

INSERT INTO departments (name) VALUES
('Human Resources'),
('Business Services'),
('Transportation'),
('Operations'),
('Instruction');

INSERT INTO roles (name) VALUES
('Self-Service'),
('Supervisor'),
('Area Leader'),
('HR Generalist'),
('HR Manager'),
('Module Admin'),
('Super Admin');

INSERT INTO employees (first_name, last_name, email, department_id, job_title, employment_status)
VALUES ('System', 'Administrator', 'admin@webberschools.org', 1, 'HRIS Administrator', 'Active');

INSERT INTO rehire_blacklist (full_name, email, reason)
VALUES ('Example Former Employee', 'blocked@example.com', 'Do not rehire test record');
