-- ==========================================
-- UNIVERSITY PAYROLL SYSTEM FINAL SCHEMA
-- ==========================================

DROP DATABASE IF EXISTS university_payroll;
CREATE DATABASE university_payroll;
USE university_payroll;

-- ==========================================
-- MASTER TABLES
-- ==========================================

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

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

CREATE TABLE banks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    short_name VARCHAR(100),
    bank_code VARCHAR(20),
    bank_identifier VARCHAR(50)
);

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

CREATE TABLE staff_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `unions` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employment_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ==========================================
-- SALARY STRUCTURE
-- ==========================================

CREATE TABLE salary_levels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scale_type VARCHAR(50) NOT NULL,
    grade_level VARCHAR(20) NOT NULL,
    step VARCHAR(20) NOT NULL,
    basic_salary DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    UNIQUE(scale_type, grade_level, step)
);

CREATE TABLE salary_scale_components (
    id INT AUTO_INCREMENT PRIMARY KEY,
    salary_level_id INT NOT NULL,
    component_name VARCHAR(100) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    taxable TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1 = include in tax base, 0 = non-taxable',
    FOREIGN KEY (salary_level_id)
        REFERENCES salary_levels(id)
        ON DELETE CASCADE
);

-- ==========================================
-- STAFF HISTORY
-- ==========================================

CREATE TABLE staff_salary_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT,
    salary_level_id INT,
    effective_date DATE,
    FOREIGN KEY (staff_id) REFERENCES staff(id),
    FOREIGN KEY (salary_level_id) REFERENCES salary_levels(id)
);

CREATE TABLE staff_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT,
    status VARCHAR(50),
    effective_date DATE,
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

-- ==========================================
-- ALLOWANCES & DEDUCTIONS
-- ==========================================

CREATE TABLE allowance_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE,
    taxable BOOLEAN DEFAULT TRUE
);

CREATE TABLE deduction_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE,
    statutory BOOLEAN DEFAULT FALSE
);

CREATE TABLE staff_allowances (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT,
    allowance_type_id INT,
    amount DECIMAL(15,2),
    effective_date DATE,
    FOREIGN KEY (staff_id) REFERENCES staff(id),
    FOREIGN KEY (allowance_type_id) REFERENCES allowance_types(id)
);

CREATE TABLE staff_deductions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT,
    deduction_type_id INT,
    amount DECIMAL(15,2),
    effective_date DATE,
    end_date DATE,
    FOREIGN KEY (staff_id) REFERENCES staff(id),
    FOREIGN KEY (deduction_type_id) REFERENCES deduction_types(id)
);
CREATE INDEX idx_staff_allowances_staff_id ON staff_allowances (staff_id);
CREATE INDEX idx_staff_allowances_effective ON staff_allowances (staff_id, effective_date);
CREATE INDEX idx_staff_deductions_staff_id ON staff_deductions (staff_id);
CREATE INDEX idx_staff_deductions_effective ON staff_deductions (staff_id, effective_date);

-- ==========================================
-- TAX SYSTEM
-- ==========================================

CREATE TABLE tax_brackets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    min_income DECIMAL(15,2),
    max_income DECIMAL(15,2),
    tax_rate DECIMAL(5,2)
);

-- Nigeria PAYE progressive tax (annual chargeable income)
-- Band: First 300k @ 7%, Next 300k @ 11%, Next 500k @ 15%, Next 500k @ 19%, Next 1.6M @ 21%, Above 3.2M @ 24%
INSERT INTO tax_brackets (min_income, max_income, tax_rate) VALUES
(0, 300000, 7),
(300000, 600000, 11),
(600000, 1100000, 15),
(1100000, 1600000, 19),
(1600000, 3200000, 21),
(3200000, NULL, 24);

-- ==========================================
-- ARREARS SYSTEM
-- ==========================================

CREATE TABLE salary_arrears (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT,
    old_salary DECIMAL(15,2),
    new_salary DECIMAL(15,2),
    arrear_amount DECIMAL(15,2),
    arrear_month INT,
    arrear_year INT,
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);
CREATE INDEX idx_salary_arrears_staff_id ON salary_arrears (staff_id);
CREATE INDEX idx_salary_arrears_period ON salary_arrears (arrear_year, arrear_month);

-- ==========================================
-- PAYROLL PROCESSING
-- ==========================================

CREATE TABLE payroll_periods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    month INT,
    year INT,
    status ENUM('pending','approved','paid') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(month, year)
);

CREATE TABLE payroll_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payroll_period_id INT,
    staff_id INT,
    basic_salary DECIMAL(15,2),
    total_allowances DECIMAL(15,2),
    gross_salary DECIMAL(15,2),
    total_deductions DECIMAL(15,2),
    net_salary DECIMAL(15,2),
    FOREIGN KEY (payroll_period_id) REFERENCES payroll_periods(id),
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

CREATE TABLE payroll_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payroll_record_id INT,
    item_type ENUM('allowance','deduction','basic'),
    item_name VARCHAR(100),
    amount DECIMAL(15,2),
    FOREIGN KEY (payroll_record_id)
        REFERENCES payroll_records(id)
        ON DELETE CASCADE
);
CREATE INDEX idx_payroll_records_period ON payroll_records (payroll_period_id);
CREATE INDEX idx_payroll_records_staff ON payroll_records (staff_id);
CREATE INDEX idx_payroll_items_record ON payroll_items (payroll_record_id);
CREATE INDEX idx_payroll_items_type_name ON payroll_items (payroll_record_id, item_type, item_name);

-- ==========================================
-- PAYROLL APPROVAL WORKFLOW
-- ==========================================

CREATE TABLE payroll_approvals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payroll_period_id INT,
    approved_by INT,
    approval_date DATETIME,
    status VARCHAR(50),
    FOREIGN KEY (payroll_period_id)
        REFERENCES payroll_periods(id)
);
