BEGIN;

CREATE TABLE IF NOT EXISTS hr_payroll.employee_self_service_change_requests (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    request_no varchar(80) NOT NULL,
    employee_id bigint NOT NULL,
    request_type varchar(80) NOT NULL DEFAULT 'personal_data',
    requested_changes jsonb NOT NULL DEFAULT '{}'::jsonb,
    status varchar(40) NOT NULL DEFAULT 'pending',
    employee_note text,
    decision_note text,
    requested_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    decided_at timestamp,
    decided_by uuid,
    applied_at timestamp,
    applied_by uuid,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    deleted_at timestamp,
    deleted_by uuid,
    created_by uuid,
    updated_by uuid,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_employee_self_service_change_requests_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_self_service_change_requests_employee
        FOREIGN KEY (employee_id) REFERENCES hr_payroll.employees(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_employee_self_service_change_requests_no
    ON hr_payroll.employee_self_service_change_requests (tenant_id, request_no)
    WHERE is_deleted = FALSE;

CREATE INDEX IF NOT EXISTS idx_employee_self_service_change_requests_employee
    ON hr_payroll.employee_self_service_change_requests (tenant_id, employee_id, status)
    WHERE is_deleted = FALSE;

COMMIT;