<?php
/**
 * HR module DDL (Kenya-oriented). Executed by hr_init.php on first load.
 * Do not output before headers — this file returns an array only.
 */
return [
    "CREATE TABLE IF NOT EXISTS hr_departments (
        department_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        department_code VARCHAR(30) DEFAULT NULL,
        department_name VARCHAR(160) NOT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_hr_dept_name (department_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_job_titles (
        job_title_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        title_name VARCHAR(160) NOT NULL,
        grade_level VARCHAR(50) DEFAULT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_hr_job_title (title_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_employees (
        employee_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        employee_number VARCHAR(40) NOT NULL,
        user_id INT UNSIGNED DEFAULT NULL,
        first_name VARCHAR(100) NOT NULL,
        last_name VARCHAR(100) NOT NULL,
        other_names VARCHAR(100) DEFAULT NULL,
        national_id VARCHAR(50) DEFAULT NULL,
        kra_pin VARCHAR(30) DEFAULT NULL,
        gender ENUM('M','F','OTHER','X') NOT NULL DEFAULT 'X',
        date_of_birth DATE DEFAULT NULL,
        phone VARCHAR(50) DEFAULT NULL,
        email VARCHAR(150) DEFAULT NULL,
        address_line VARCHAR(280) DEFAULT NULL,
        county VARCHAR(100) DEFAULT NULL,
        branch_id INT UNSIGNED DEFAULT NULL,
        department_id INT UNSIGNED DEFAULT NULL,
        job_title_id INT UNSIGNED DEFAULT NULL,
        manager_employee_id INT UNSIGNED DEFAULT NULL,
        employment_type ENUM('PERMANENT','CONTRACT','CASUAL','INTERN','LOCUM') NOT NULL DEFAULT 'PERMANENT',
        hire_date DATE NOT NULL,
        probation_end_date DATE DEFAULT NULL,
        termination_date DATE DEFAULT NULL,
        employment_status ENUM('ACTIVE','ON_LEAVE','SUSPENDED','TERMINATED') NOT NULL DEFAULT 'ACTIVE',
        bank_name VARCHAR(150) DEFAULT NULL,
        bank_account VARCHAR(80) DEFAULT NULL,
        bank_branch VARCHAR(120) DEFAULT NULL,
        next_of_kin_name VARCHAR(160) DEFAULT NULL,
        next_of_kin_phone VARCHAR(50) DEFAULT NULL,
        notes TEXT DEFAULT NULL,
        created_by INT UNSIGNED DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_hr_emp_number (employee_number),
        KEY ix_hr_emp_user (user_id),
        KEY ix_hr_emp_dept (department_id),
        KEY ix_hr_emp_status (employment_status),
        KEY ix_hr_emp_names (last_name, first_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_employee_documents (
        document_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        employee_id INT UNSIGNED NOT NULL,
        doc_label VARCHAR(160) NOT NULL,
        file_name VARCHAR(255) NOT NULL,
        stored_path VARCHAR(600) NOT NULL,
        mime_type VARCHAR(120) DEFAULT NULL,
        uploaded_by INT UNSIGNED DEFAULT NULL,
        uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY ix_hr_doc_emp (employee_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_leave_types (
        leave_type_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        type_name VARCHAR(120) NOT NULL,
        days_per_year DECIMAL(8,2) NOT NULL DEFAULT 0,
        allow_carry_forward TINYINT(1) NOT NULL DEFAULT 0,
        requires_attachment TINYINT(1) NOT NULL DEFAULT 0,
        is_paid TINYINT(1) NOT NULL DEFAULT 1,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_hr_leave_type (type_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_leave_balances (
        balance_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        employee_id INT UNSIGNED NOT NULL,
        leave_type_id INT UNSIGNED NOT NULL,
        balance_year SMALLINT UNSIGNED NOT NULL,
        opening_days DECIMAL(8,2) NOT NULL DEFAULT 0,
        accrued_days DECIMAL(8,2) NOT NULL DEFAULT 0,
        used_days DECIMAL(8,2) NOT NULL DEFAULT 0,
        carried_in DECIMAL(8,2) NOT NULL DEFAULT 0,
        updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_hr_leave_bal (employee_id, leave_type_id, balance_year),
        KEY ix_hr_lb_emp (employee_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_leave_requests (
        request_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        employee_id INT UNSIGNED NOT NULL,
        leave_type_id INT UNSIGNED NOT NULL,
        start_date DATE NOT NULL,
        end_date DATE NOT NULL,
        days_requested DECIMAL(8,2) NOT NULL,
        reason TEXT DEFAULT NULL,
        status ENUM('PENDING','APPROVED','REJECTED','CANCELLED') NOT NULL DEFAULT 'PENDING',
        decided_by INT UNSIGNED DEFAULT NULL,
        decided_at DATETIME DEFAULT NULL,
        decision_note VARCHAR(500) DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY ix_hr_lr_emp (employee_id),
        KEY ix_hr_lr_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_attendance (
        attendance_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        employee_id INT UNSIGNED NOT NULL,
        work_date DATE NOT NULL,
        clock_in DATETIME DEFAULT NULL,
        clock_out DATETIME DEFAULT NULL,
        hours_worked DECIMAL(6,2) DEFAULT NULL,
        source ENUM('WEB_SELF','ADMIN','IMPORT') NOT NULL DEFAULT 'WEB_SELF',
        remarks VARCHAR(500) DEFAULT NULL,
        created_by INT UNSIGNED DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_hr_att_emp_day (employee_id, work_date),
        KEY ix_hr_att_date (work_date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_onboarding_templates (
        template_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        template_name VARCHAR(160) NOT NULL,
        description VARCHAR(500) DEFAULT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_onboarding_template_items (
        item_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        template_id INT UNSIGNED NOT NULL,
        sort_order INT UNSIGNED NOT NULL DEFAULT 0,
        title VARCHAR(200) NOT NULL,
        description TEXT DEFAULT NULL,
        KEY ix_hr_oti_tpl (template_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_onboarding_tasks (
        task_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        employee_id INT UNSIGNED NOT NULL,
        template_id INT UNSIGNED DEFAULT NULL,
        title VARCHAR(200) NOT NULL,
        description TEXT DEFAULT NULL,
        due_date DATE DEFAULT NULL,
        completed_at DATETIME DEFAULT NULL,
        completed_by INT UNSIGNED DEFAULT NULL,
        sort_order INT UNSIGNED NOT NULL DEFAULT 0,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY ix_hr_ot_emp (employee_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_training_courses (
        course_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        course_code VARCHAR(40) DEFAULT NULL,
        course_name VARCHAR(200) NOT NULL,
        description TEXT DEFAULT NULL,
        mandatory_for_clinical TINYINT(1) NOT NULL DEFAULT 0,
        validity_months INT UNSIGNED DEFAULT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_training_records (
        record_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        employee_id INT UNSIGNED NOT NULL,
        course_id INT UNSIGNED NOT NULL,
        completed_on DATE NOT NULL,
        expiry_date DATE DEFAULT NULL,
        certificate_path VARCHAR(600) DEFAULT NULL,
        trainer VARCHAR(200) DEFAULT NULL,
        notes VARCHAR(500) DEFAULT NULL,
        recorded_by INT UNSIGNED DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY ix_hr_tr_emp (employee_id),
        KEY ix_hr_tr_course (course_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_employee_compensation (
        compensation_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        employee_id INT UNSIGNED NOT NULL,
        effective_from DATE NOT NULL,
        effective_to DATE DEFAULT NULL,
        basic_salary DECIMAL(14,2) NOT NULL DEFAULT 0,
        housing_allowance DECIMAL(14,2) NOT NULL DEFAULT 0,
        transport_allowance DECIMAL(14,2) NOT NULL DEFAULT 0,
        other_taxable_allowance DECIMAL(14,2) NOT NULL DEFAULT 0,
        pension_voluntary_employee DECIMAL(14,2) NOT NULL DEFAULT 0,
        notes VARCHAR(500) DEFAULT NULL,
        created_by INT UNSIGNED DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY ix_hr_ec_emp (employee_id),
        KEY ix_hr_ec_eff (effective_from)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_pay_periods (
        pay_period_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        period_year SMALLINT UNSIGNED NOT NULL,
        period_month TINYINT UNSIGNED NOT NULL,
        period_label VARCHAR(40) NOT NULL,
        start_date DATE NOT NULL,
        end_date DATE NOT NULL,
        status ENUM('OPEN','COMPUTED','LOCKED','PAID') NOT NULL DEFAULT 'OPEN',
        created_by INT UNSIGNED DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_hr_pp_ym (period_year, period_month)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_payroll_lines (
        line_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        pay_period_id INT UNSIGNED NOT NULL,
        employee_id INT UNSIGNED NOT NULL,
        gross_pay DECIMAL(14,2) NOT NULL DEFAULT 0,
        taxable_pay DECIMAL(14,2) NOT NULL DEFAULT 0,
        nssf_employee DECIMAL(14,2) NOT NULL DEFAULT 0,
        nssf_employer DECIMAL(14,2) NOT NULL DEFAULT 0,
        shif_employee DECIMAL(14,2) NOT NULL DEFAULT 0,
        shif_employer DECIMAL(14,2) NOT NULL DEFAULT 0,
        housing_levy_ee DECIMAL(14,2) NOT NULL DEFAULT 0,
        housing_levy_er DECIMAL(14,2) NOT NULL DEFAULT 0,
        paye_tax DECIMAL(14,2) NOT NULL DEFAULT 0,
        pension_voluntary DECIMAL(14,2) NOT NULL DEFAULT 0,
        other_deductions DECIMAL(14,2) NOT NULL DEFAULT 0,
        net_pay DECIMAL(14,2) NOT NULL DEFAULT 0,
        calc_json TEXT DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_hr_pl_period_emp (pay_period_id, employee_id),
        KEY ix_hr_pl_emp (employee_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_settings (
        setting_key VARCHAR(80) NOT NULL PRIMARY KEY,
        setting_value TEXT NOT NULL,
        description VARCHAR(255) DEFAULT NULL,
        updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_requisitions (
        requisition_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        job_title VARCHAR(200) NOT NULL,
        department_id INT UNSIGNED DEFAULT NULL,
        positions_open TINYINT UNSIGNED NOT NULL DEFAULT 1,
        status ENUM('DRAFT','OPEN','ON_HOLD','FILLED','CANCELLED') NOT NULL DEFAULT 'OPEN',
        opened_at DATE DEFAULT NULL,
        closed_at DATE DEFAULT NULL,
        notes TEXT DEFAULT NULL,
        created_by INT UNSIGNED DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

    "CREATE TABLE IF NOT EXISTS hr_candidates (
        candidate_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        requisition_id INT UNSIGNED DEFAULT NULL,
        full_name VARCHAR(200) NOT NULL,
        phone VARCHAR(50) DEFAULT NULL,
        email VARCHAR(150) DEFAULT NULL,
        stage ENUM('NEW','SCREENING','INTERVIEW','OFFER','HIRED','REJECTED') NOT NULL DEFAULT 'NEW',
        cv_path VARCHAR(600) DEFAULT NULL,
        notes TEXT DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY ix_hr_cand_req (requisition_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
];
