CREATE OR REPLACE FUNCTION hr_payroll.capture_payroll_audit_event()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = hr_payroll, public
AS $$
DECLARE
    v_row jsonb := CASE WHEN TG_OP = 'DELETE' THEN to_jsonb(OLD) ELSE to_jsonb(NEW) END;
    v_before jsonb := CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) ELSE '{}'::jsonb END;
    v_after jsonb := CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) ELSE '{}'::jsonb END;
    v_tenant uuid := (v_row->>'tenant_id')::uuid;
    v_actor_text text := COALESCE(v_row->>'updated_by', v_row->>'created_by', v_row->>'approved_by', v_row->>'paid_by', v_row->>'published_by');
    v_actor uuid := CASE WHEN v_actor_text ~* '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$' THEN v_actor_text::uuid END;
    v_run_id bigint := CASE WHEN COALESCE(v_row->>'payroll_run_id', '') ~ '^\d+$' THEN (v_row->>'payroll_run_id')::bigint END;
    v_employee_id bigint := CASE WHEN COALESCE(v_row->>'employee_id', '') ~ '^\d+$' THEN (v_row->>'employee_id')::bigint END;
    v_entity_id text := v_row->>'id';
    v_reference text := COALESCE(v_row->>'payroll_run_no', v_row->>'payslip_no', v_row->>'adjustment_no', v_row->>'exception_no', v_row->>'component_code', v_row->>'request_no', v_entity_id);
    v_event_type text := replace(TG_TABLE_NAME, '_', '.') || '.' || lower(TG_OP);
    v_category text := CASE WHEN TG_TABLE_NAME = 'payroll_runs' THEN CASE WHEN TG_OP = 'UPDATE' AND COALESCE(OLD.status, '') IS DISTINCT FROM COALESCE(NEW.status, '') THEN 'approval' ELSE 'payroll_run' END WHEN TG_TABLE_NAME = 'payroll_adjustments' THEN 'override' WHEN TG_TABLE_NAME IN ('payroll_exceptions', 'payroll_validation_issues') THEN 'validation' WHEN TG_TABLE_NAME = 'employee_self_service_change_requests' THEN 'master_data' WHEN TG_TABLE_NAME = 'payslips' THEN 'payslip' ELSE 'component' END;
    v_risk text := CASE WHEN TG_OP = 'DELETE' OR TG_TABLE_NAME = 'payroll_adjustments' THEN 'high' WHEN TG_TABLE_NAME = 'payroll_runs' AND lower(COALESCE(v_row->>'status', '')) IN ('rejected', 'reopened', 'posted', 'paid') THEN 'high' WHEN TG_TABLE_NAME IN ('payroll_exceptions', 'payroll_validation_issues') THEN 'medium' ELSE 'low' END;
    v_correlation_text text := NULLIF(current_setting('app.audit_correlation_id', true), '');
    v_correlation uuid := CASE WHEN v_correlation_text ~* '^[0-9a-f-]{36}$' THEN v_correlation_text::uuid ELSE gen_random_uuid() END;
    v_source_ip_text text := NULLIF(current_setting('app.audit_source_ip', true), '');
    v_previous_hash text;
    v_event_id uuid := gen_random_uuid();
    v_occurred_at timestamptz := CURRENT_TIMESTAMP;
    v_hash text;
BEGIN
    PERFORM pg_advisory_xact_lock(hashtext(v_tenant::text), hashtext('payroll_audit_events'));
    SELECT event_hash INTO v_previous_hash FROM hr_payroll.payroll_audit_events WHERE tenant_id = v_tenant ORDER BY occurred_at DESC, id DESC LIMIT 1;
    v_hash := encode(digest(concat_ws('|', v_previous_hash, v_event_id::text, v_tenant::text, v_event_type, TG_TABLE_NAME, v_entity_id, COALESCE(v_actor::text, ''), v_occurred_at::text, v_before::text, v_after::text), 'sha256'), 'hex');
    INSERT INTO hr_payroll.payroll_audit_events (tenant_id,event_id,event_type,category,risk_level,payroll_run_id,employee_id,entity_type,entity_id,entity_reference,actor_user_id,actor_name,actor_role,action,reason,approval_reference,source_ip,correlation_id,before_state,after_state,metadata,previous_hash,event_hash,verification_status,occurred_at)
    VALUES (v_tenant,v_event_id,v_event_type,v_category,v_risk,CASE WHEN TG_TABLE_NAME='payroll_runs' THEN v_entity_id::bigint ELSE v_run_id END,v_employee_id,TG_TABLE_NAME,v_entity_id,v_reference,v_actor,
        COALESCE(NULLIF(current_setting('app.audit_actor_name', true), ''),v_row->>'updated_by_name',v_row->>'created_by_name',v_row->>'approved_by_name',v_row->>'paid_by_name'),
        NULLIF(current_setting('app.audit_actor_role', true), ''),initcap(replace(TG_TABLE_NAME,'_',' '))||' '||initcap(lower(TG_OP)),COALESCE(v_row->>'decision_reason',v_row->>'reason',v_row->>'notes'),COALESCE(v_row->>'approval_reference',v_row->>'accounting_journal_no',v_row->>'payment_journal_no'),
        CASE WHEN v_source_ip_text IS NOT NULL THEN v_source_ip_text::inet END,v_correlation,v_before,v_after,
        jsonb_build_object('schema',TG_TABLE_SCHEMA,'table',TG_TABLE_NAME,'operation',TG_OP,'route',NULLIF(current_setting('app.audit_route',true),''),'http_method',NULLIF(current_setting('app.audit_operation',true),''),'user_agent',NULLIF(current_setting('app.audit_user_agent',true),'')),
        v_previous_hash,v_hash,'verified',v_occurred_at);
    RETURN CASE WHEN TG_OP='DELETE' THEN OLD ELSE NEW END;
END;
$$;

REVOKE ALL ON FUNCTION hr_payroll.capture_payroll_audit_event() FROM PUBLIC;