BEGIN;

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE SCHEMA IF NOT EXISTS hr_payroll;

CREATE TABLE IF NOT EXISTS hr_payroll.employee_emergency_contacts (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    contact_name character varying(150) NOT NULL,
    relationship character varying(80) NULL,
    phone character varying(50) NULL,
    email character varying(150) NULL,
    address text NULL,
    is_primary boolean NOT NULL DEFAULT FALSE,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_employee_emergency_contacts_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_emergency_contacts_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS hr_payroll.employee_documents (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    document_no character varying(50) NULL,
    document_name character varying(180) NOT NULL,
    category character varying(80) NOT NULL,
    file_reference character varying(255) NULL,
    file_name character varying(255) NULL,
    file_size_bytes bigint NULL,
    mime_type character varying(120) NULL,
    status character varying(40) NOT NULL DEFAULT 'pending_review',
    review_notes text NULL,
    reviewed_at timestamp without time zone NULL,
    reviewed_by uuid NULL,
    expires_at date NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_employee_documents_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_documents_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS hr_payroll.employee_lifecycle_events (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    event_type character varying(60) NOT NULL,
    effective_date date NOT NULL,
    from_department_id bigint NULL,
    to_department_id bigint NULL,
    from_designation_id bigint NULL,
    to_designation_id bigint NULL,
    from_salary numeric(18, 2) NULL,
    to_salary numeric(18, 2) NULL,
    currency_code character varying(10) NULL DEFAULT 'GHS',
    notes text NULL,
    approved_by uuid NULL,
    approved_at timestamp without time zone NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_employee_lifecycle_events_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_lifecycle_events_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_lifecycle_events_from_department FOREIGN KEY (from_department_id) REFERENCES org.departments(id),
    CONSTRAINT fk_employee_lifecycle_events_to_department FOREIGN KEY (to_department_id) REFERENCES org.departments(id),
    CONSTRAINT fk_employee_lifecycle_events_from_designation FOREIGN KEY (from_designation_id) REFERENCES org.designations(id),
    CONSTRAINT fk_employee_lifecycle_events_to_designation FOREIGN KEY (to_designation_id) REFERENCES org.designations(id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.employee_leave_balances (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    leave_type_id bigint NOT NULL,
    leave_year integer NOT NULL,
    opening_balance numeric(8, 2) NOT NULL DEFAULT 0,
    entitled_days numeric(8, 2) NOT NULL DEFAULT 0,
    accrued_days numeric(8, 2) NOT NULL DEFAULT 0,
    used_days numeric(8, 2) NOT NULL DEFAULT 0,
    pending_days numeric(8, 2) NOT NULL DEFAULT 0,
    carried_over_days numeric(8, 2) NOT NULL DEFAULT 0,
    available_days numeric(8, 2) NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_employee_leave_balances_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_leave_balances_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_leave_balances_leave_type FOREIGN KEY (leave_type_id) REFERENCES hr_payroll.leave_types(id),
    CONSTRAINT uq_employee_leave_balance UNIQUE (tenant_id, employee_id, leave_type_id, leave_year)
);

ALTER TABLE hr_payroll.leave_requests
    ADD COLUMN IF NOT EXISTS rejected_at timestamp without time zone NULL,
    ADD COLUMN IF NOT EXISTS approved_by uuid NULL,
    ADD COLUMN IF NOT EXISTS rejected_by uuid NULL,
    ADD COLUMN IF NOT EXISTS rejection_reason text NULL,
    ADD COLUMN IF NOT EXISTS workflow_reference character varying(100) NULL;

CREATE TABLE IF NOT EXISTS hr_payroll.payroll_currencies (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    currency_code character varying(10) NOT NULL,
    currency_name character varying(120) NOT NULL,
    symbol character varying(10) NULL,
    is_base_currency boolean NOT NULL DEFAULT FALSE,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_payroll_currencies_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_payroll_currencies_code UNIQUE (tenant_id, currency_code)
);

CREATE TABLE IF NOT EXISTS hr_payroll.payroll_exchange_rates (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    from_currency_code character varying(10) NOT NULL,
    to_currency_code character varying(10) NOT NULL,
    rate numeric(18, 8) NOT NULL,
    source character varying(80) NULL,
    effective_date date NOT NULL,
    expires_at date NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_payroll_exchange_rates_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_payroll_exchange_rates_date UNIQUE (tenant_id, from_currency_code, to_currency_code, effective_date)
);

CREATE TABLE IF NOT EXISTS hr_payroll.payroll_components (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    component_code character varying(50) NOT NULL,
    component_name character varying(150) NOT NULL,
    component_type character varying(40) NOT NULL,
    category character varying(80) NULL,
    calculation_method character varying(50) NOT NULL DEFAULT 'fixed',
    default_value character varying(120) NULL,
    formula text NULL,
    is_taxable boolean NOT NULL DEFAULT FALSE,
    is_statutory boolean NOT NULL DEFAULT FALSE,
    sort_order integer NOT NULL DEFAULT 1,
    description text NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_payroll_components_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_payroll_components_code UNIQUE (tenant_id, component_code)
);

CREATE TABLE IF NOT EXISTS hr_payroll.salary_structures (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    structure_code character varying(50) NOT NULL,
    structure_name character varying(150) NOT NULL,
    description text NULL,
    pay_band character varying(100) NULL,
    currency_code character varying(10) NOT NULL DEFAULT 'GHS',
    basic_min numeric(18, 2) NULL,
    basic_max numeric(18, 2) NULL,
    status character varying(40) NOT NULL DEFAULT 'active',
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_salary_structures_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_salary_structures_code UNIQUE (tenant_id, structure_code)
);

CREATE TABLE IF NOT EXISTS hr_payroll.salary_structure_components (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    salary_structure_id bigint NOT NULL,
    payroll_component_id bigint NOT NULL,
    calculation_method character varying(50) NULL,
    component_value character varying(120) NULL,
    sort_order integer NOT NULL DEFAULT 1,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_salary_structure_components_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_salary_structure_components_structure FOREIGN KEY (salary_structure_id) REFERENCES hr_payroll.salary_structures(id) ON DELETE CASCADE,
    CONSTRAINT fk_salary_structure_components_component FOREIGN KEY (payroll_component_id) REFERENCES hr_payroll.payroll_components(id),
    CONSTRAINT uq_salary_structure_component UNIQUE (tenant_id, salary_structure_id, payroll_component_id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.employee_payroll_components (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    payroll_component_id bigint NOT NULL,
    amount numeric(18, 2) NULL,
    percentage numeric(8, 4) NULL,
    currency_code character varying(10) NULL DEFAULT 'GHS',
    starts_at date NULL,
    ends_at date NULL,
    status character varying(40) NOT NULL DEFAULT 'active',
    notes text NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_employee_payroll_components_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_payroll_components_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_payroll_components_component FOREIGN KEY (payroll_component_id) REFERENCES hr_payroll.payroll_components(id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.payroll_adjustments (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    payroll_run_id bigint NULL,
    payroll_component_id bigint NULL,
    adjustment_no character varying(50) NULL,
    adjustment_type character varying(80) NOT NULL,
    amount numeric(18, 2) NOT NULL,
    currency_code character varying(10) NOT NULL DEFAULT 'GHS',
    effective_date date NOT NULL,
    status character varying(40) NOT NULL DEFAULT 'draft',
    reason text NULL,
    approved_at timestamp without time zone NULL,
    approved_by uuid NULL,
    processed_at timestamp without time zone NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_payroll_adjustments_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_payroll_adjustments_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_payroll_adjustments_run FOREIGN KEY (payroll_run_id) REFERENCES hr_payroll.payroll_runs(id),
    CONSTRAINT fk_payroll_adjustments_component FOREIGN KEY (payroll_component_id) REFERENCES hr_payroll.payroll_components(id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.employee_loans (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    loan_no character varying(50) NOT NULL,
    loan_type character varying(80) NOT NULL,
    amount numeric(18, 2) NOT NULL,
    currency_code character varying(10) NOT NULL DEFAULT 'GHS',
    purpose text NULL,
    applied_date date NOT NULL DEFAULT CURRENT_DATE,
    approved_date date NULL,
    repayment_months integer NOT NULL DEFAULT 1,
    monthly_deduction numeric(18, 2) NOT NULL DEFAULT 0,
    amount_repaid numeric(18, 2) NOT NULL DEFAULT 0,
    status character varying(40) NOT NULL DEFAULT 'pending',
    approved_by uuid NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_employee_loans_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_loans_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT uq_employee_loans_no UNIQUE (tenant_id, loan_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.payslips (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    payroll_run_id bigint NOT NULL,
    payroll_run_item_id bigint NULL,
    payslip_no character varying(50) NOT NULL,
    gross_pay numeric(18, 2) NOT NULL DEFAULT 0,
    total_deductions numeric(18, 2) NOT NULL DEFAULT 0,
    net_pay numeric(18, 2) NOT NULL DEFAULT 0,
    currency_code character varying(10) NOT NULL DEFAULT 'GHS',
    status character varying(40) NOT NULL DEFAULT 'draft',
    file_reference character varying(255) NULL,
    published_at timestamp without time zone NULL,
    published_by uuid NULL,
    downloaded_at timestamp without time zone NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_payslips_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_payslips_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_payslips_run FOREIGN KEY (payroll_run_id) REFERENCES hr_payroll.payroll_runs(id) ON DELETE CASCADE,
    CONSTRAINT fk_payslips_run_item FOREIGN KEY (payroll_run_item_id) REFERENCES hr_payroll.payroll_run_items(id),
    CONSTRAINT uq_payslips_no UNIQUE (tenant_id, payslip_no),
    CONSTRAINT uq_payslips_employee_run UNIQUE (tenant_id, employee_id, payroll_run_id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.statutory_deduction_rules (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    rule_code character varying(50) NOT NULL,
    rule_name character varying(150) NOT NULL,
    rule_type character varying(60) NOT NULL,
    country_code character varying(10) NOT NULL DEFAULT 'GH',
    calculation_method character varying(60) NOT NULL,
    rate numeric(10, 4) NULL,
    amount numeric(18, 2) NULL,
    bands jsonb NOT NULL DEFAULT '[]'::jsonb,
    employer_rate numeric(10, 4) NULL,
    employee_rate numeric(10, 4) NULL,
    effective_date date NOT NULL,
    expires_at date NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_statutory_deduction_rules_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_statutory_deduction_rules_code UNIQUE (tenant_id, rule_code, effective_date)
);

CREATE TABLE IF NOT EXISTS hr_payroll.statutory_contribution_records (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    payroll_run_id bigint NOT NULL,
    statutory_rule_id bigint NULL,
    contribution_type character varying(80) NOT NULL,
    employee_amount numeric(18, 2) NOT NULL DEFAULT 0,
    employer_amount numeric(18, 2) NOT NULL DEFAULT 0,
    taxable_income numeric(18, 2) NULL,
    currency_code character varying(10) NOT NULL DEFAULT 'GHS',
    filing_status character varying(40) NOT NULL DEFAULT 'pending',
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    CONSTRAINT fk_statutory_contribution_records_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_statutory_contribution_records_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_statutory_contribution_records_run FOREIGN KEY (payroll_run_id) REFERENCES hr_payroll.payroll_runs(id) ON DELETE CASCADE,
    CONSTRAINT fk_statutory_contribution_records_rule FOREIGN KEY (statutory_rule_id) REFERENCES hr_payroll.statutory_deduction_rules(id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_devices (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    device_code character varying(50) NOT NULL,
    device_name character varying(150) NOT NULL,
    device_type character varying(40) NOT NULL,
    location_id bigint NULL,
    location_label character varying(180) NULL,
    status character varying(40) NOT NULL DEFAULT 'offline',
    last_sync_at timestamp without time zone NULL,
    firmware_version character varying(80) NULL,
    ip_address character varying(80) NULL,
    serial_number character varying(120) NULL,
    settings jsonb NOT NULL DEFAULT '{}'::jsonb,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_attendance_devices_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_devices_location FOREIGN KEY (location_id) REFERENCES org.locations(id),
    CONSTRAINT uq_attendance_devices_code UNIQUE (tenant_id, device_code)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_device_logs (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    attendance_device_id bigint NOT NULL,
    logged_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    event_type character varying(80) NOT NULL,
    status character varying(40) NOT NULL,
    detail text NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT fk_attendance_device_logs_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_device_logs_device FOREIGN KEY (attendance_device_id) REFERENCES hr_payroll.attendance_devices(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_device_mappings (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    attendance_device_id bigint NULL,
    employee_id bigint NOT NULL,
    external_attendant_id character varying(100) NOT NULL,
    external_name character varying(150) NULL,
    status character varying(40) NOT NULL DEFAULT 'active',
    mapped_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    mapped_by uuid NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_attendance_device_mappings_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_device_mappings_device FOREIGN KEY (attendance_device_id) REFERENCES hr_payroll.attendance_devices(id),
    CONSTRAINT fk_attendance_device_mappings_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT uq_attendance_device_mapping UNIQUE (tenant_id, external_attendant_id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_data_sources (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    source_code character varying(50) NOT NULL,
    source_name character varying(150) NOT NULL,
    source_type character varying(40) NOT NULL,
    host_or_path text NULL,
    sync_schedule character varying(80) NULL,
    auto_sync boolean NOT NULL DEFAULT FALSE,
    status character varying(40) NOT NULL DEFAULT 'disconnected',
    last_sync_at timestamp without time zone NULL,
    records_imported bigint NOT NULL DEFAULT 0,
    connection_config jsonb NOT NULL DEFAULT '{}'::jsonb,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_attendance_data_sources_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_attendance_data_sources_code UNIQUE (tenant_id, source_code)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_import_templates (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    attendance_data_source_id bigint NULL,
    template_code character varying(50) NOT NULL,
    template_name character varying(150) NOT NULL,
    source_type character varying(40) NOT NULL,
    status character varying(40) NOT NULL DEFAULT 'draft',
    mappings jsonb NOT NULL DEFAULT '[]'::jsonb,
    validation_rules jsonb NOT NULL DEFAULT '[]'::jsonb,
    last_used_at timestamp without time zone NULL,
    times_used integer NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_attendance_import_templates_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_import_templates_source FOREIGN KEY (attendance_data_source_id) REFERENCES hr_payroll.attendance_data_sources(id),
    CONSTRAINT uq_attendance_import_templates_code UNIQUE (tenant_id, template_code)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_import_batches (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    attendance_data_source_id bigint NULL,
    attendance_import_template_id bigint NULL,
    batch_no character varying(50) NOT NULL,
    source_file_reference character varying(255) NULL,
    status character varying(40) NOT NULL DEFAULT 'queued',
    total_rows integer NOT NULL DEFAULT 0,
    imported_rows integer NOT NULL DEFAULT 0,
    failed_rows integer NOT NULL DEFAULT 0,
    duplicate_rows integer NOT NULL DEFAULT 0,
    started_at timestamp without time zone NULL,
    completed_at timestamp without time zone NULL,
    error_summary text NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    CONSTRAINT fk_attendance_import_batches_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_import_batches_source FOREIGN KEY (attendance_data_source_id) REFERENCES hr_payroll.attendance_data_sources(id),
    CONSTRAINT fk_attendance_import_batches_template FOREIGN KEY (attendance_import_template_id) REFERENCES hr_payroll.attendance_import_templates(id),
    CONSTRAINT uq_attendance_import_batches_no UNIQUE (tenant_id, batch_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_import_rows (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    attendance_import_batch_id bigint NOT NULL,
    employee_id bigint NULL,
    attendance_log_id bigint NULL,
    row_number integer NULL,
    raw_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    normalized_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    status character varying(40) NOT NULL DEFAULT 'pending',
    error_message text NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_attendance_import_rows_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_import_rows_batch FOREIGN KEY (attendance_import_batch_id) REFERENCES hr_payroll.attendance_import_batches(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_import_rows_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id),
    CONSTRAINT fk_attendance_import_rows_log FOREIGN KEY (attendance_log_id) REFERENCES hr_payroll.attendance_logs(id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_sync_jobs (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    attendance_data_source_id bigint NULL,
    job_no character varying(50) NOT NULL,
    status character varying(40) NOT NULL DEFAULT 'queued',
    scheduled_at timestamp without time zone NULL,
    started_at timestamp without time zone NULL,
    completed_at timestamp without time zone NULL,
    records_processed integer NOT NULL DEFAULT 0,
    records_failed integer NOT NULL DEFAULT 0,
    error_log text NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    CONSTRAINT fk_attendance_sync_jobs_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_sync_jobs_source FOREIGN KEY (attendance_data_source_id) REFERENCES hr_payroll.attendance_data_sources(id),
    CONSTRAINT uq_attendance_sync_jobs_no UNIQUE (tenant_id, job_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.shift_assignments (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    shift_id bigint NOT NULL,
    starts_at date NOT NULL,
    ends_at date NULL,
    status character varying(40) NOT NULL DEFAULT 'active',
    notes text NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_shift_assignments_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_shift_assignments_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_shift_assignments_shift FOREIGN KEY (shift_id) REFERENCES hr_payroll.shifts(id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_rules (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    rule_code character varying(50) NOT NULL,
    rule_name character varying(150) NOT NULL,
    rule_category character varying(80) NOT NULL,
    applies_to character varying(80) NOT NULL DEFAULT 'all',
    department_id bigint NULL,
    shift_id bigint NULL,
    rule_config jsonb NOT NULL DEFAULT '{}'::jsonb,
    status character varying(40) NOT NULL DEFAULT 'active',
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_attendance_rules_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_rules_department FOREIGN KEY (department_id) REFERENCES org.departments(id),
    CONSTRAINT fk_attendance_rules_shift FOREIGN KEY (shift_id) REFERENCES hr_payroll.shifts(id),
    CONSTRAINT uq_attendance_rules_code UNIQUE (tenant_id, rule_code)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_exceptions (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    attendance_log_id bigint NULL,
    shift_id bigint NULL,
    exception_no character varying(50) NULL,
    exception_type character varying(80) NOT NULL,
    exception_date date NOT NULL,
    severity character varying(40) NOT NULL DEFAULT 'low',
    status character varying(40) NOT NULL DEFAULT 'open',
    source character varying(120) NULL,
    description text NULL,
    resolved_at timestamp without time zone NULL,
    resolved_by uuid NULL,
    resolution_notes text NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    CONSTRAINT fk_attendance_exceptions_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_exceptions_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_exceptions_log FOREIGN KEY (attendance_log_id) REFERENCES hr_payroll.attendance_logs(id),
    CONSTRAINT fk_attendance_exceptions_shift FOREIGN KEY (shift_id) REFERENCES hr_payroll.shifts(id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_review_items (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    attendance_exception_id bigint NULL,
    employee_id bigint NOT NULL,
    review_no character varying(50) NOT NULL,
    issue_type character varying(80) NOT NULL,
    review_date date NOT NULL,
    severity character varying(40) NOT NULL DEFAULT 'medium',
    status character varying(40) NOT NULL DEFAULT 'pending',
    scheduled_window character varying(80) NULL,
    actual_window character varying(80) NULL,
    source character varying(120) NULL,
    description text NULL,
    raised_by character varying(120) NULL,
    decision_reason text NULL,
    decided_at timestamp without time zone NULL,
    decided_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    CONSTRAINT fk_attendance_review_items_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_review_items_exception FOREIGN KEY (attendance_exception_id) REFERENCES hr_payroll.attendance_exceptions(id),
    CONSTRAINT fk_attendance_review_items_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT uq_attendance_review_items_no UNIQUE (tenant_id, review_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_adjustments (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    attendance_log_id bigint NULL,
    adjustment_no character varying(50) NOT NULL,
    adjustment_type character varying(80) NOT NULL,
    original_clock_in timestamp without time zone NULL,
    original_clock_out timestamp without time zone NULL,
    adjusted_clock_in timestamp without time zone NULL,
    adjusted_clock_out timestamp without time zone NULL,
    reason text NOT NULL,
    status character varying(40) NOT NULL DEFAULT 'pending',
    requested_by uuid NULL,
    approved_by uuid NULL,
    approved_at timestamp without time zone NULL,
    applied_at timestamp without time zone NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    CONSTRAINT fk_attendance_adjustments_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_adjustments_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_adjustments_log FOREIGN KEY (attendance_log_id) REFERENCES hr_payroll.attendance_logs(id),
    CONSTRAINT uq_attendance_adjustments_no UNIQUE (tenant_id, adjustment_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_approval_requests (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    request_no character varying(50) NOT NULL,
    request_type character varying(80) NOT NULL,
    request_date date NOT NULL DEFAULT CURRENT_DATE,
    status character varying(40) NOT NULL DEFAULT 'submitted',
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    reason text NULL,
    decided_at timestamp without time zone NULL,
    decided_by uuid NULL,
    decision_reason text NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    CONSTRAINT fk_attendance_approval_requests_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_approval_requests_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT uq_attendance_approval_requests_no UNIQUE (tenant_id, request_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_disciplinary_flags (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    flag_no character varying(50) NOT NULL,
    flag_type character varying(80) NOT NULL,
    severity character varying(40) NOT NULL DEFAULT 'medium',
    status character varying(40) NOT NULL DEFAULT 'open',
    flagged_date date NOT NULL DEFAULT CURRENT_DATE,
    description text NULL,
    action_taken text NULL,
    resolved_at timestamp without time zone NULL,
    resolved_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    CONSTRAINT fk_attendance_disciplinary_flags_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_disciplinary_flags_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT uq_attendance_disciplinary_flags_no UNIQUE (tenant_id, flag_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.attendance_export_runs (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    export_no character varying(50) NOT NULL,
    export_format character varying(20) NOT NULL,
    date_from date NULL,
    date_to date NULL,
    selected_fields jsonb NOT NULL DEFAULT '[]'::jsonb,
    row_count integer NOT NULL DEFAULT 0,
    status character varying(40) NOT NULL DEFAULT 'processing',
    file_reference character varying(255) NULL,
    size_bytes bigint NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    CONSTRAINT fk_attendance_export_runs_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_attendance_export_runs_no UNIQUE (tenant_id, export_no)
);

ALTER TABLE hr_payroll.attendance_logs
    ADD COLUMN IF NOT EXISTS attendance_device_id bigint NULL,
    ADD COLUMN IF NOT EXISTS attendance_import_batch_id bigint NULL,
    ADD COLUMN IF NOT EXISTS external_reference character varying(120) NULL,
    ADD COLUMN IF NOT EXISTS source character varying(80) NULL,
    ADD COLUMN IF NOT EXISTS latitude numeric(10, 7) NULL,
    ADD COLUMN IF NOT EXISTS longitude numeric(10, 7) NULL;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'attendance_logs_attendance_device_id_fkey'
          AND conrelid = 'hr_payroll.attendance_logs'::regclass
    ) THEN
        ALTER TABLE hr_payroll.attendance_logs
            ADD CONSTRAINT attendance_logs_attendance_device_id_fkey
            FOREIGN KEY (attendance_device_id) REFERENCES hr_payroll.attendance_devices(id);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'attendance_logs_attendance_import_batch_id_fkey'
          AND conrelid = 'hr_payroll.attendance_logs'::regclass
    ) THEN
        ALTER TABLE hr_payroll.attendance_logs
            ADD CONSTRAINT attendance_logs_attendance_import_batch_id_fkey
            FOREIGN KEY (attendance_import_batch_id) REFERENCES hr_payroll.attendance_import_batches(id);
    END IF;
END $$;

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_jobs (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    job_no character varying(50) NOT NULL,
    title character varying(180) NOT NULL,
    department_id bigint NULL,
    designation_id bigint NULL,
    location_id bigint NULL,
    open_positions integer NOT NULL DEFAULT 1,
    applications_count integer NOT NULL DEFAULT 0,
    salary_min numeric(18, 2) NULL,
    salary_max numeric(18, 2) NULL,
    salary_band character varying(100) NULL,
    employment_type character varying(40) NOT NULL DEFAULT 'full_time',
    status character varying(40) NOT NULL DEFAULT 'draft',
    posted_date date NULL,
    closing_date date NULL,
    hiring_manager_user_id uuid NULL,
    description text NULL,
    requirements text NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_recruitment_jobs_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_recruitment_jobs_department FOREIGN KEY (department_id) REFERENCES org.departments(id),
    CONSTRAINT fk_recruitment_jobs_designation FOREIGN KEY (designation_id) REFERENCES org.designations(id),
    CONSTRAINT fk_recruitment_jobs_location FOREIGN KEY (location_id) REFERENCES org.locations(id),
    CONSTRAINT uq_recruitment_jobs_no UNIQUE (tenant_id, job_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_candidates (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    candidate_no character varying(50) NOT NULL,
    recruitment_job_id bigint NULL,
    candidate_name character varying(180) NOT NULL,
    email character varying(150) NULL,
    phone character varying(50) NULL,
    location_label character varying(150) NULL,
    current_stage character varying(40) NOT NULL DEFAULT 'applied',
    applied_date date NULL,
    rating integer NULL,
    source character varying(60) NULL,
    notes text NULL,
    resume_file_reference character varying(255) NULL,
    hired_employee_id bigint NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_recruitment_candidates_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_recruitment_candidates_job FOREIGN KEY (recruitment_job_id) REFERENCES hr_payroll.recruitment_jobs(id),
    CONSTRAINT fk_recruitment_candidates_hired_employee FOREIGN KEY (hired_employee_id) REFERENCES hr_payroll.employees(id),
    CONSTRAINT uq_recruitment_candidates_no UNIQUE (tenant_id, candidate_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_candidate_stage_history (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    recruitment_candidate_id bigint NOT NULL,
    from_stage character varying(40) NULL,
    to_stage character varying(40) NOT NULL,
    changed_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    changed_by uuid NULL,
    notes text NULL,
    CONSTRAINT fk_recruitment_candidate_stage_history_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_recruitment_candidate_stage_history_candidate FOREIGN KEY (recruitment_candidate_id) REFERENCES hr_payroll.recruitment_candidates(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_interviews (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    recruitment_candidate_id bigint NOT NULL,
    recruitment_job_id bigint NULL,
    interview_no character varying(50) NOT NULL,
    interview_type character varying(60) NOT NULL,
    scheduled_at timestamp without time zone NOT NULL,
    duration_minutes integer NULL,
    interviewer_user_id uuid NULL,
    panel jsonb NOT NULL DEFAULT '[]'::jsonb,
    location_label character varying(150) NULL,
    status character varying(40) NOT NULL DEFAULT 'scheduled',
    score numeric(6, 2) NULL,
    notes text NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    CONSTRAINT fk_recruitment_interviews_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_recruitment_interviews_candidate FOREIGN KEY (recruitment_candidate_id) REFERENCES hr_payroll.recruitment_candidates(id) ON DELETE CASCADE,
    CONSTRAINT fk_recruitment_interviews_job FOREIGN KEY (recruitment_job_id) REFERENCES hr_payroll.recruitment_jobs(id),
    CONSTRAINT uq_recruitment_interviews_no UNIQUE (tenant_id, interview_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_offers (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    recruitment_candidate_id bigint NOT NULL,
    recruitment_job_id bigint NULL,
    offer_no character varying(50) NOT NULL,
    status character varying(40) NOT NULL DEFAULT 'draft',
    salary_amount numeric(18, 2) NULL,
    currency_code character varying(10) NOT NULL DEFAULT 'GHS',
    benefits jsonb NOT NULL DEFAULT '[]'::jsonb,
    start_date date NULL,
    sent_at timestamp without time zone NULL,
    expires_at timestamp without time zone NULL,
    responded_at timestamp without time zone NULL,
    notes text NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    CONSTRAINT fk_recruitment_offers_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_recruitment_offers_candidate FOREIGN KEY (recruitment_candidate_id) REFERENCES hr_payroll.recruitment_candidates(id) ON DELETE CASCADE,
    CONSTRAINT fk_recruitment_offers_job FOREIGN KEY (recruitment_job_id) REFERENCES hr_payroll.recruitment_jobs(id),
    CONSTRAINT uq_recruitment_offers_no UNIQUE (tenant_id, offer_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.employee_promotions (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    promotion_no character varying(50) NOT NULL,
    old_designation_id bigint NULL,
    new_designation_id bigint NULL,
    old_pay_band character varying(100) NULL,
    new_pay_band character varying(100) NULL,
    old_salary numeric(18, 2) NULL,
    new_salary numeric(18, 2) NULL,
    currency_code character varying(10) NOT NULL DEFAULT 'GHS',
    effective_date date NOT NULL,
    status character varying(40) NOT NULL DEFAULT 'pending',
    reason text NULL,
    performance_score numeric(6, 2) NULL,
    approved_by uuid NULL,
    approved_at timestamp without time zone NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_employee_promotions_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_promotions_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_promotions_old_designation FOREIGN KEY (old_designation_id) REFERENCES org.designations(id),
    CONSTRAINT fk_employee_promotions_new_designation FOREIGN KEY (new_designation_id) REFERENCES org.designations(id),
    CONSTRAINT uq_employee_promotions_no UNIQUE (tenant_id, promotion_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.employee_transfers (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    transfer_no character varying(50) NOT NULL,
    transfer_type character varying(80) NOT NULL,
    from_department_id bigint NULL,
    to_department_id bigint NULL,
    from_location_id bigint NULL,
    to_location_id bigint NULL,
    from_designation_id bigint NULL,
    to_designation_id bigint NULL,
    effective_date date NOT NULL,
    status character varying(40) NOT NULL DEFAULT 'pending',
    reason text NULL,
    approved_by uuid NULL,
    approved_at timestamp without time zone NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_employee_transfers_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_transfers_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_transfers_from_department FOREIGN KEY (from_department_id) REFERENCES org.departments(id),
    CONSTRAINT fk_employee_transfers_to_department FOREIGN KEY (to_department_id) REFERENCES org.departments(id),
    CONSTRAINT fk_employee_transfers_from_location FOREIGN KEY (from_location_id) REFERENCES org.locations(id),
    CONSTRAINT fk_employee_transfers_to_location FOREIGN KEY (to_location_id) REFERENCES org.locations(id),
    CONSTRAINT fk_employee_transfers_from_designation FOREIGN KEY (from_designation_id) REFERENCES org.designations(id),
    CONSTRAINT fk_employee_transfers_to_designation FOREIGN KEY (to_designation_id) REFERENCES org.designations(id),
    CONSTRAINT uq_employee_transfers_no UNIQUE (tenant_id, transfer_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.disciplinary_cases (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    case_no character varying(50) NOT NULL,
    case_type character varying(100) NOT NULL,
    severity character varying(40) NOT NULL DEFAULT 'medium',
    incident_date date NOT NULL,
    status character varying(40) NOT NULL DEFAULT 'under_investigation',
    investigator_user_id uuid NULL,
    description text NULL,
    action_taken text NULL,
    appealed_at timestamp without time zone NULL,
    resolved_at timestamp without time zone NULL,
    resolved_by uuid NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_disciplinary_cases_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_disciplinary_cases_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT uq_disciplinary_cases_no UNIQUE (tenant_id, case_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.training_programs (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    program_no character varying(50) NOT NULL,
    program_name character varying(180) NOT NULL,
    category character varying(80) NULL,
    trainer character varying(150) NULL,
    starts_at date NULL,
    ends_at date NULL,
    capacity integer NULL,
    budget_amount numeric(18, 2) NULL,
    currency_code character varying(10) NOT NULL DEFAULT 'GHS',
    status character varying(40) NOT NULL DEFAULT 'upcoming',
    description text NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_training_programs_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_training_programs_no UNIQUE (tenant_id, program_no)
);

CREATE TABLE IF NOT EXISTS hr_payroll.employee_training_enrollments (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    training_program_id bigint NOT NULL,
    employee_id bigint NOT NULL,
    status character varying(40) NOT NULL DEFAULT 'enrolled',
    enrolled_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at timestamp without time zone NULL,
    score numeric(6, 2) NULL,
    certificate_file_reference character varying(255) NULL,
    feedback text NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    CONSTRAINT fk_employee_training_enrollments_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_training_enrollments_program FOREIGN KEY (training_program_id) REFERENCES hr_payroll.training_programs(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_training_enrollments_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT uq_employee_training_enrollment UNIQUE (tenant_id, training_program_id, employee_id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.performance_review_cycles (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    cycle_code character varying(50) NOT NULL,
    cycle_name character varying(150) NOT NULL,
    period_label character varying(100) NULL,
    starts_at date NULL,
    ends_at date NULL,
    status character varying(40) NOT NULL DEFAULT 'draft',
    description text NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_performance_review_cycles_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_performance_review_cycles_code UNIQUE (tenant_id, cycle_code)
);

CREATE TABLE IF NOT EXISTS hr_payroll.performance_reviews (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    performance_review_cycle_id bigint NOT NULL,
    employee_id bigint NOT NULL,
    reviewer_user_id uuid NULL,
    review_no character varying(50) NOT NULL,
    status character varying(40) NOT NULL DEFAULT 'draft',
    overall_score numeric(6, 2) NULL,
    supervisor_comment text NULL,
    strengths jsonb NOT NULL DEFAULT '[]'::jsonb,
    improvements jsonb NOT NULL DEFAULT '[]'::jsonb,
    next_review_date date NULL,
    promotion_recommended boolean NOT NULL DEFAULT FALSE,
    submitted_at timestamp without time zone NULL,
    completed_at timestamp without time zone NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_performance_reviews_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_performance_reviews_cycle FOREIGN KEY (performance_review_cycle_id) REFERENCES hr_payroll.performance_review_cycles(id) ON DELETE CASCADE,
    CONSTRAINT fk_performance_reviews_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT uq_performance_reviews_no UNIQUE (tenant_id, review_no),
    CONSTRAINT uq_performance_reviews_employee_cycle UNIQUE (tenant_id, employee_id, performance_review_cycle_id)
);

CREATE TABLE IF NOT EXISTS hr_payroll.performance_review_categories (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    performance_review_id bigint NOT NULL,
    category_name character varying(150) NOT NULL,
    score numeric(6, 2) NULL,
    weight numeric(6, 2) NOT NULL DEFAULT 0,
    comments text NULL,
    sort_order integer NOT NULL DEFAULT 1,
    CONSTRAINT fk_performance_review_categories_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_performance_review_categories_review FOREIGN KEY (performance_review_id) REFERENCES hr_payroll.performance_reviews(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS hr_payroll.performance_kpis (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    performance_review_id bigint NULL,
    kpi_code character varying(50) NULL,
    kpi_name character varying(150) NOT NULL,
    target_value numeric(18, 2) NULL,
    actual_value numeric(18, 2) NULL,
    score numeric(6, 2) NULL,
    weight numeric(6, 2) NOT NULL DEFAULT 0,
    period_label character varying(100) NULL,
    status character varying(40) NOT NULL DEFAULT 'active',
    notes text NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    CONSTRAINT fk_performance_kpis_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_performance_kpis_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_performance_kpis_review FOREIGN KEY (performance_review_id) REFERENCES hr_payroll.performance_reviews(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS hr_payroll.employee_feedback (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    employee_id bigint NOT NULL,
    feedback_from_user_id uuid NULL,
    performance_review_id bigint NULL,
    feedback_type character varying(60) NOT NULL,
    visibility character varying(40) NOT NULL DEFAULT 'private',
    rating numeric(6, 2) NULL,
    sentiment character varying(40) NULL,
    comments text NOT NULL,
    submitted_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_employee_feedback_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_feedback_employee FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_feedback_review FOREIGN KEY (performance_review_id) REFERENCES hr_payroll.performance_reviews(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_employee_emergency_contacts_employee ON hr_payroll.employee_emergency_contacts (tenant_id, employee_id) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_employee_documents_employee ON hr_payroll.employee_documents (tenant_id, employee_id, category) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_employee_lifecycle_events_employee ON hr_payroll.employee_lifecycle_events (tenant_id, employee_id, effective_date DESC) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_employee_leave_balances_employee ON hr_payroll.employee_leave_balances (tenant_id, employee_id, leave_year) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_payroll_components_tenant ON hr_payroll.payroll_components (tenant_id, component_type) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_salary_structures_tenant ON hr_payroll.salary_structures (tenant_id, pay_band) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_employee_payroll_components_employee ON hr_payroll.employee_payroll_components (tenant_id, employee_id, status) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_payroll_adjustments_employee ON hr_payroll.payroll_adjustments (tenant_id, employee_id, effective_date DESC) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_employee_loans_employee ON hr_payroll.employee_loans (tenant_id, employee_id, status) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_payslips_employee ON hr_payroll.payslips (tenant_id, employee_id, payroll_run_id) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_attendance_devices_tenant ON hr_payroll.attendance_devices (tenant_id, status) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_attendance_device_logs_device ON hr_payroll.attendance_device_logs (tenant_id, attendance_device_id, logged_at DESC);
CREATE INDEX IF NOT EXISTS idx_attendance_import_rows_batch ON hr_payroll.attendance_import_rows (tenant_id, attendance_import_batch_id, status);
CREATE INDEX IF NOT EXISTS idx_shift_assignments_employee ON hr_payroll.shift_assignments (tenant_id, employee_id, starts_at, ends_at) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_attendance_exceptions_employee ON hr_payroll.attendance_exceptions (tenant_id, employee_id, exception_date DESC, status);
CREATE INDEX IF NOT EXISTS idx_attendance_review_items_status ON hr_payroll.attendance_review_items (tenant_id, status, severity, review_date DESC);
CREATE INDEX IF NOT EXISTS idx_recruitment_jobs_tenant ON hr_payroll.recruitment_jobs (tenant_id, status, closing_date) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_recruitment_candidates_job ON hr_payroll.recruitment_candidates (tenant_id, recruitment_job_id, current_stage) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_employee_promotions_employee ON hr_payroll.employee_promotions (tenant_id, employee_id, effective_date DESC) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_employee_transfers_employee ON hr_payroll.employee_transfers (tenant_id, employee_id, effective_date DESC) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_disciplinary_cases_employee ON hr_payroll.disciplinary_cases (tenant_id, employee_id, incident_date DESC) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_training_programs_tenant ON hr_payroll.training_programs (tenant_id, status, starts_at) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_performance_reviews_employee ON hr_payroll.performance_reviews (tenant_id, employee_id, performance_review_cycle_id) WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_performance_kpis_employee ON hr_payroll.performance_kpis (tenant_id, employee_id, period_label);
CREATE INDEX IF NOT EXISTS idx_employee_feedback_employee ON hr_payroll.employee_feedback (tenant_id, employee_id, submitted_at DESC) WHERE is_deleted = FALSE;

COMMIT;