-- broker_set function
-- Sets broker runtime options and context
-- Supports: user, application_name, and custom settings
-- Returns: p_retval (0=success, >0=error), p_errmsg (error message)

CREATE OR REPLACE FUNCTION broker_set(
    p_option_name TEXT,
    p_option_value TEXT,
    OUT p_retval INTEGER,
    OUT p_errmsg TEXT
)
RETURNS RECORD
LANGUAGE plpgsql
AS $$
DECLARE
    v_sql TEXT;
BEGIN
    p_retval := 0;
    p_errmsg := '';

    -- Validate inputs
    IF p_option_name IS NULL OR p_option_name = '' THEN
        p_retval := 1;
        p_errmsg := 'Option name is required';
        RETURN;
    END IF;

    -- Handle different option types
    CASE LOWER(p_option_name)
        WHEN 'user' THEN
            -- Set session user context
            -- This is useful for audit trails and permissions
            BEGIN
                v_sql := format('SET SESSION AUTHORIZATION %I', p_option_value);
                EXECUTE v_sql;
            EXCEPTION
                WHEN OTHERS THEN
                    p_retval := 2;
                    p_errmsg := format('Failed to set user: %s', SQLERRM);
                    RETURN;
            END;

        WHEN 'application_name' THEN
            -- Set application name (visible in pg_stat_activity)
            BEGIN
                v_sql := format('SET application_name TO %L', p_option_value);
                EXECUTE v_sql;
            EXCEPTION
                WHEN OTHERS THEN
                    p_retval := 3;
                    p_errmsg := format('Failed to set application_name: %s', SQLERRM);
                    RETURN;
            END;

        WHEN 'search_path' THEN
            -- Set schema search path
            BEGIN
                v_sql := format('SET search_path TO %s', p_option_value);
                EXECUTE v_sql;
            EXCEPTION
                WHEN OTHERS THEN
                    p_retval := 4;
                    p_errmsg := format('Failed to set search_path: %s', SQLERRM);
                    RETURN;
            END;

        WHEN 'timezone' THEN
            -- Set timezone
            BEGIN
                v_sql := format('SET timezone TO %L', p_option_value);
                EXECUTE v_sql;
            EXCEPTION
                WHEN OTHERS THEN
                    p_retval := 5;
                    p_errmsg := format('Failed to set timezone: %s', SQLERRM);
                    RETURN;
            END;

        ELSE
            -- Unknown option
            p_retval := 10;
            p_errmsg := format('Unknown option: %s', p_option_name);
            RETURN;
    END CASE;

EXCEPTION
    WHEN OTHERS THEN
        p_retval := 99;
        p_errmsg := SQLERRM;
        RAISE WARNING 'broker_set error: %', SQLERRM;
END;
$$;

-- Comments
COMMENT ON FUNCTION broker_set IS 'Sets broker runtime options and session context (user, application_name, search_path, timezone)';