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->>'filing_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(v_before->>'status', '') IS DISTINCT FROM COALESCE(v_after->>'status', '')
                 THEN 'approval' ELSE 'payroll_run' END
        WHEN TG_TABLE_NAME IN ('statutory_filings', 'statutory_filing_items') THEN 'statutory_filing'
        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 = 'statutory_filings' AND lower(COALESCE(v_row->>'status', '')) IN ('submitted', 'accepted', 'authority_rejected', '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' AND COALESCE(v_entity_id, '') ~ '^\d+$' 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->>'rejection_reason', v_row->>'reason', v_row->>'notes'),
        COALESCE(v_row->>'approval_reference', v_row->>'submission_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;