-- HR Module Database Schema

-- 1. Add HR-related columns to users table (employees)
ALTER TABLE users 
ADD COLUMN IF NOT EXISTS employee_id VARCHAR(20) UNIQUE,
ADD COLUMN IF NOT EXISTS position VARCHAR(100),
ADD COLUMN IF NOT EXISTS department_id INT,
ADD COLUMN IF NOT EXISTS join_date DATE,
ADD COLUMN IF NOT EXISTS salary DECIMAL(12,2),
ADD COLUMN IF NOT EXISTS status ENUM('active', 'inactive', 'terminated') DEFAULT 'active',
ADD COLUMN IF NOT EXISTS address TEXT,
ADD COLUMN IF NOT EXISTS phone VARCHAR(20),
ADD COLUMN IF NOT EXISTS emergency_contact_name VARCHAR(100),
ADD COLUMN IF NOT EXISTS emergency_contact_phone VARCHAR(20),
ADD COLUMN IF NOT EXISTS bank_account VARCHAR(50),
ADD COLUMN IF NOT EXISTS tax_id VARCHAR(50),
ADD FOREIGN KEY (department_id) REFERENCES departments(id);

-- 2. Attendance Table
CREATE TABLE attendance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    date DATE NOT NULL,
    in_time TIME NULL,
    out_time TIME NULL,
    status ENUM('present', 'absent', 'late', 'early_departure', 'leave', 'holiday') DEFAULT 'absent',
    overtime_hours DECIMAL(4,2) DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES users(id),
    UNIQUE KEY unique_employee_date (employee_id, date),
    INDEX idx_employee (employee_id),
    INDEX idx_date (date)
);

-- 3. Payroll Table
CREATE TABLE payroll (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    payroll_period VARCHAR(20) NOT NULL, -- Format: YYYY-MM
    basic_salary DECIMAL(12,2) NOT NULL,
    allowances DECIMAL(12,2) DEFAULT 0,
    deductions DECIMAL(12,2) DEFAULT 0,
    net_salary DECIMAL(12,2) AS (basic_salary + allowances - deductions) STORED,
    payment_date DATE NULL,
    status ENUM('pending', 'paid', 'cancelled') DEFAULT 'pending',
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    UNIQUE KEY unique_employee_period (employee_id, payroll_period),
    INDEX idx_status (status),
    INDEX idx_period (payroll_period)
);

-- 4. Payroll Items Table
CREATE TABLE payroll_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    payroll_id INT NOT NULL,
    item_type ENUM('allowance', 'deduction') NOT NULL,
    item_name VARCHAR(100) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (payroll_id) REFERENCES payroll(id) ON DELETE CASCADE,
    INDEX idx_payroll (payroll_id),
    INDEX idx_type (item_type)
);

-- 5. Leave Applications Table
CREATE TABLE leave_applications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    leave_type ENUM('annual', 'sick', 'emergency', 'maternity', 'paternity', 'unpaid') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    reason TEXT NOT NULL,
    status ENUM('pending', 'approved', 'rejected', 'cancelled') DEFAULT 'pending',
    approved_by INT NULL,
    approved_at TIMESTAMP NULL,
    applied_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_by INT NULL,
    processed_at TIMESTAMP NULL,
    notes TEXT,
    FOREIGN KEY (employee_id) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id),
    FOREIGN KEY (processed_by) REFERENCES users(id),
    INDEX idx_employee (employee_id),
    INDEX idx_status (status),
    INDEX idx_dates (start_date, end_date),
    CHECK (end_date >= start_date)
);

-- 6. Performance Reviews Table
CREATE TABLE performance_reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    review_period VARCHAR(20) NOT NULL, -- Format: YYYY-Q1, YYYY-MM
    reviewer_id INT NOT NULL, -- Manager or HR
    rating DECIMAL(3,2) DEFAULT 0, -- Scale 1.00 to 5.00
    comments TEXT,
    strengths TEXT,
    areas_for_improvement TEXT,
    goals TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES users(id),
    FOREIGN KEY (reviewer_id) REFERENCES users(id),
    UNIQUE KEY unique_employee_review_period (employee_id, review_period),
    INDEX idx_employee (employee_id),
    INDEX idx_reviewer (reviewer_id),
    INDEX idx_period (review_period)
);

-- 7. Training Programs Table
CREATE TABLE training_programs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    trainer VARCHAR(100),
    location VARCHAR(200),
    cost DECIMAL(12,2) DEFAULT 0,
    max_participants INT DEFAULT 0,
    status ENUM('planned', 'in_progress', 'completed', 'cancelled') DEFAULT 'planned',
    created_by INT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_status (status),
    INDEX idx_dates (start_date, end_date),
    CHECK (end_date >= start_date)
);

-- 8. Employee Training Table
CREATE TABLE employee_training (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    training_id INT NOT NULL,
    enrollment_date DATE DEFAULT (CURRENT_DATE),
    completion_status ENUM('enrolled', 'in_progress', 'completed', 'withdrawn') DEFAULT 'enrolled',
    score DECIMAL(5,2) NULL, -- For scored training
    completion_date DATE NULL,
    certificate_path VARCHAR(500),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES users(id),
    FOREIGN KEY (training_id) REFERENCES training_programs(id),
    UNIQUE KEY unique_employee_training (employee_id, training_id),
    INDEX idx_employee (employee_id),
    INDEX idx_training (training_id),
    INDEX idx_status (completion_status)
);

-- 9. Employee Benefits Table
CREATE TABLE employee_benefits (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    benefit_type VARCHAR(100) NOT NULL, -- health_insurance, transport_allowance, etc.
    provider VARCHAR(100),
    coverage_amount DECIMAL(12,2) DEFAULT 0,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    status ENUM('active', 'inactive', 'cancelled') DEFAULT 'active',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES users(id),
    INDEX idx_employee (employee_id),
    INDEX idx_type (benefit_type)
);

-- 10. HR Announcements Table
CREATE TABLE hr_announcements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    target_audience ENUM('all', 'management', 'specific_department', 'specific_employees') DEFAULT 'all',
    department_id INT NULL,
    created_by INT NOT NULL,
    published_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expiry_date DATE NULL,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_active (is_active),
    INDEX idx_published (published_date)
);

-- Insert sample employee data
INSERT INTO users (username, email, employee_id, name, position, department_id, join_date, salary, status, address, phone) 
SELECT 
    'employee1', 
    'employee1@company.com', 
    'EMP001', 
    'John Doe', 
    'Production Staff', 
    1, 
    '2023-01-15', 
    5000000, 
    'active', 
    'Jl. Merdeka No. 123, Jakarta', 
    '081234567890'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM users WHERE employee_id = 'EMP001');

-- Insert sample department if not exists
INSERT INTO departments (name, description) 
SELECT 'Production', 'Production Department'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM departments WHERE name = 'Production');