-- WireAdmin Phase 9: Advanced HR Features

-- 1. Attendance Control
CREATE TABLE IF NOT EXISTS wa_hr_attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    date DATE NOT NULL,
    check_in TIME,
    check_out TIME,
    hours_worked DECIMAL(4,2) DEFAULT 0,
    overtime_hours DECIMAL(4,2) DEFAULT 0,
    status ENUM('present', 'absent', 'late', 'half_day') DEFAULT 'present',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES wa_hr_employees(id) ON DELETE CASCADE,
    UNIQUE KEY unique_employee_date (employee_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Vacation Balance
CREATE TABLE IF NOT EXISTS wa_hr_vacation_balance (
    employee_id INT NOT NULL,
    year INT NOT NULL,
    days_earned DECIMAL(5,2) DEFAULT 15.00, -- 15 days base Chile
    days_used DECIMAL(5,2) DEFAULT 0,
    days_available DECIMAL(5,2) DEFAULT 15.00,
    PRIMARY KEY (employee_id, year),
    FOREIGN KEY (employee_id) REFERENCES wa_hr_employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Vacation Requests
CREATE TABLE IF NOT EXISTS wa_hr_vacation_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    days_requested DECIMAL(5,2) NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    approved_by INT,
    approved_at DATETIME,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES wa_hr_employees(id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by) REFERENCES wa_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Document Templates
CREATE TABLE IF NOT EXISTS wa_hr_document_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(50) NOT NULL, -- 'contract_indefinido', 'contract_plazo_fijo', 'regulation', 'termination'
    name VARCHAR(255) NOT NULL,
    content LONGTEXT NOT NULL, -- HTML template with {{variables}}
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
