-- Hospital Payroll Management System - Database Schema V1.0 & V2.0

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

-- ==========================================
-- 1. Authentication & Authorization (RBAC)
-- ==========================================

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

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

CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role_id INT NOT NULL,
    employee_id VARCHAR(50) NULL,
    email VARCHAR(100),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    status ENUM('active', 'inactive') DEFAULT 'active',
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT
);

-- ==========================================
-- 2. Master Settings (Income & Deduction)
-- ==========================================

CREATE TABLE income_master (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    display_order INT DEFAULT 0,
    color_code VARCHAR(20),
    icon_class VARCHAR(50),
    is_taxable BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE deduction_master (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    display_order INT DEFAULT 0,
    color_code VARCHAR(20),
    icon_class VARCHAR(50),
    is_tax_deductible BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ==========================================
-- 3. Payroll Management
-- ==========================================

CREATE TABLE salary_year (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year_no INT NOT NULL UNIQUE,
    status ENUM('open', 'closed') DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE salary_month (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year_id INT NOT NULL,
    month_no INT NOT NULL,
    status ENUM('Draft', 'Imported', 'Pending Review', 'HR Approved', 'Finance Approved', 'Director Approved', 'Locked', 'Published', 'Cancelled') DEFAULT 'Draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE(year_id, month_no),
    FOREIGN KEY (year_id) REFERENCES salary_year(id) ON DELETE CASCADE
);

CREATE TABLE employee_salary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    salary_month_id INT NOT NULL,
    employee_code VARCHAR(50) NOT NULL,
    national_id VARCHAR(20) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    position VARCHAR(100),
    department VARCHAR(100),
    base_salary DECIMAL(10,2) DEFAULT 0.00,
    total_income DECIMAL(10,2) DEFAULT 0.00,
    total_deduction DECIMAL(10,2) DEFAULT 0.00,
    net_salary DECIMAL(10,2) DEFAULT 0.00,
    remark TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE(salary_month_id, employee_code),
    FOREIGN KEY (salary_month_id) REFERENCES salary_month(id) ON DELETE CASCADE
);

CREATE TABLE salary_income (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    employee_salary_id INT NOT NULL,
    income_master_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_salary_id) REFERENCES employee_salary(id) ON DELETE CASCADE,
    FOREIGN KEY (income_master_id) REFERENCES income_master(id) ON DELETE RESTRICT
);

CREATE TABLE salary_deduction (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    employee_salary_id INT NOT NULL,
    deduction_master_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_salary_id) REFERENCES employee_salary(id) ON DELETE CASCADE,
    FOREIGN KEY (deduction_master_id) REFERENCES deduction_master(id) ON DELETE RESTRICT
);

-- ==========================================
-- 4. Import Data Logging
-- ==========================================

CREATE TABLE import_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    salary_month_id INT NOT NULL,
    file_name VARCHAR(255),
    status ENUM('success', 'failed', 'partial') DEFAULT 'success',
    total_records INT DEFAULT 0,
    success_records INT DEFAULT 0,
    error_records INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (salary_month_id) REFERENCES salary_month(id) ON DELETE CASCADE
);

CREATE TABLE import_detail (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    import_history_id INT NOT NULL,
    line_number INT,
    national_id VARCHAR(20),
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (import_history_id) REFERENCES import_history(id) ON DELETE CASCADE
);

-- ==========================================
-- 5. Logging and Auditing
-- ==========================================

CREATE TABLE audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(100),
    record_id INT,
    old_value JSON,
    new_value JSON,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE salary_slip_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    employee_salary_id INT NOT NULL,
    action ENUM('view', 'print', 'download', 'email') NOT NULL,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_salary_id) REFERENCES employee_salary(id) ON DELETE CASCADE
);

CREATE TABLE report_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    report_name VARCHAR(100) NOT NULL,
    parameters JSON,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ==========================================
-- 6. Tax Certificates
-- ==========================================

CREATE TABLE tax_certificate (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year_id INT NOT NULL,
    employee_code VARCHAR(50) NOT NULL,
    total_income DECIMAL(12,2) DEFAULT 0.00,
    total_tax DECIMAL(12,2) DEFAULT 0.00,
    total_provident_fund DECIMAL(12,2) DEFAULT 0.00,
    total_social_security DECIMAL(12,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (year_id) REFERENCES salary_year(id) ON DELETE CASCADE
);

CREATE TABLE tax_certificate_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    tax_certificate_id INT NOT NULL,
    action ENUM('view', 'print', 'download') NOT NULL,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tax_certificate_id) REFERENCES tax_certificate(id) ON DELETE CASCADE
);

-- ==========================================
-- 7. File Attachments
-- ==========================================

CREATE TABLE attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    related_table VARCHAR(50),
    related_id INT,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_type VARCHAR(100),
    file_size INT,
    uploaded_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
);

-- ==========================================
-- 8. System Integrations & Settings
-- ==========================================

CREATE TABLE system_setting (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    description VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE external_database (
    id INT AUTO_INCREMENT PRIMARY KEY,
    connection_name VARCHAR(100) NOT NULL UNIQUE,
    host VARCHAR(100) NOT NULL,
    port INT DEFAULT 3306,
    db_name VARCHAR(100) NOT NULL,
    db_user VARCHAR(100) NOT NULL,
    db_password VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE backup_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(255) NOT NULL,
    file_size INT NOT NULL,
    status ENUM('success', 'failed') DEFAULT 'success',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ==========================================
-- Default Data Inserts
-- ==========================================

INSERT INTO roles (name, description) VALUES 
('Administrator', 'Full system access'),
('HR', 'Human Resources access'),
('Finance', 'Financial review access'),
('Director', 'Final approval access'),
('Auditor', 'Read-only access for auditing');

-- admin / admin123
INSERT INTO users (username, password_hash, role_id, first_name, last_name) 
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 'System', 'Administrator');