CREATE TABLE attendance (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    attendance_date DATE NOT NULL,
    shift_name VARCHAR(60) NULL,
    check_in DATETIME NULL,
    check_out DATETIME NULL,
    worked_hours DECIMAL(9,2) NOT NULL DEFAULT 0,
    late_minutes INT UNSIGNED NOT NULL DEFAULT 0,
    overtime_hours DECIMAL(9,2) NOT NULL DEFAULT 0,
    status ENUM('present', 'absent', 'leave', 'holiday', 'weekend', 'half_day') NOT NULL DEFAULT 'present',
    source ENUM('manual', 'biometric', 'import', 'system') NOT NULL DEFAULT 'manual',
    remarks VARCHAR(500) NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_employee_attendance_date (employee_id, attendance_date),
    KEY idx_attendance_company_date (company_id, attendance_date),
    CONSTRAINT fk_attendance_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_attendance_employee FOREIGN KEY (employee_id) REFERENCES employees (id),
    CONSTRAINT fk_attendance_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE leave_applications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    application_number VARCHAR(60) NOT NULL UNIQUE,
    employee_id BIGINT UNSIGNED NOT NULL,
    leave_type ENUM('annual', 'casual', 'sick', 'maternity', 'paternity', 'unpaid', 'other') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_days DECIMAL(7,2) NOT NULL,
    reason VARCHAR(500) NOT NULL,
    status ENUM('pending', 'approved', 'rejected', 'cancelled') NOT NULL DEFAULT 'pending',
    is_paid BOOLEAN NOT NULL DEFAULT TRUE,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    approval_notes VARCHAR(500) NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    KEY idx_leave_company_dates (company_id, start_date, end_date),
    CONSTRAINT fk_leave_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_leave_employee FOREIGN KEY (employee_id) REFERENCES employees (id),
    CONSTRAINT fk_leave_approver FOREIGN KEY (approved_by) REFERENCES users (id),
    CONSTRAINT fk_leave_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE overtime (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    overtime_number VARCHAR(60) NOT NULL UNIQUE,
    employee_id BIGINT UNSIGNED NOT NULL,
    overtime_date DATE NOT NULL,
    start_time TIME NULL,
    end_time TIME NULL,
    hours DECIMAL(9,2) NOT NULL,
    hourly_rate DECIMAL(18,4) NOT NULL DEFAULT 0,
    multiplier DECIMAL(7,4) NOT NULL DEFAULT 1,
    amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    reason VARCHAR(500) NULL,
    status ENUM('pending', 'approved', 'rejected', 'paid') NOT NULL DEFAULT 'pending',
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    KEY idx_overtime_company_date (company_id, overtime_date),
    CONSTRAINT fk_overtime_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_overtime_employee FOREIGN KEY (employee_id) REFERENCES employees (id),
    CONSTRAINT fk_overtime_approver FOREIGN KEY (approved_by) REFERENCES users (id),
    CONSTRAINT fk_overtime_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE salary_components (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NULL,
    code VARCHAR(40) NOT NULL,
    name VARCHAR(150) NOT NULL,
    component_type ENUM('earning', 'deduction') NOT NULL,
    calculation_type ENUM('fixed', 'percentage') NOT NULL DEFAULT 'fixed',
    amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    percentage DECIMAL(9,4) NOT NULL DEFAULT 0,
    taxable BOOLEAN NOT NULL DEFAULT FALSE,
    recurring BOOLEAN NOT NULL DEFAULT TRUE,
    effective_from DATE NULL,
    effective_to DATE NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    KEY idx_salary_component_employee (company_id, employee_id, status),
    CONSTRAINT fk_salary_component_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_salary_component_employee FOREIGN KEY (employee_id) REFERENCES employees (id)
) ENGINE=InnoDB;

CREATE TABLE payroll (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    payroll_number VARCHAR(60) NOT NULL UNIQUE,
    employee_id BIGINT UNSIGNED NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    payable_days DECIMAL(7,2) NOT NULL DEFAULT 0,
    present_days DECIMAL(7,2) NOT NULL DEFAULT 0,
    paid_leave_days DECIMAL(7,2) NOT NULL DEFAULT 0,
    unpaid_leave_days DECIMAL(7,2) NOT NULL DEFAULT 0,
    basic_salary DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_earnings DECIMAL(18,4) NOT NULL DEFAULT 0,
    overtime_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    gross_salary DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_deductions DECIMAL(18,4) NOT NULL DEFAULT 0,
    net_salary DECIMAL(18,4) NOT NULL DEFAULT 0,
    component_summary JSON NULL,
    status ENUM('draft', 'approved', 'paid', 'cancelled') NOT NULL DEFAULT 'draft',
    payment_date DATE NULL,
    payment_reference VARCHAR(100) NULL,
    generated_by BIGINT UNSIGNED NOT NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_employee_payroll_period (employee_id, period_start, period_end),
    CONSTRAINT fk_payroll_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_payroll_employee FOREIGN KEY (employee_id) REFERENCES employees (id),
    CONSTRAINT fk_payroll_generator FOREIGN KEY (generated_by) REFERENCES users (id),
    CONSTRAINT fk_payroll_approver FOREIGN KEY (approved_by) REFERENCES users (id)
) ENGINE=InnoDB;