CREATE OR REPLACE PROCEDURE public.get_attached_policies(
    IN var_input JSONB,  -- Input as JSONB object
    OUT result JSONB   -- Output as JSONB
)
LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_query_information JSONB;
    v_attached_policies JSONB;
    v_executed_query TEXT := '';
    v_entity_name TEXT;
    v_company_id INTEGER;
BEGIN
    -- Extract values from JSON input
    v_entity_name := var_input->>'entity_name';
    v_company_id := (var_input->>'company_id')::INTEGER;

    -- Store the query as a string
    v_executed_query := format(
        'SELECT query_information FROM master_entities WHERE entity_name = %L AND company_id = %s AND status_id = 1 ORDER BY id ASC LIMIT 1',
        v_entity_name, v_company_id
    );

    -- Execute the query dynamically
    EXECUTE v_executed_query INTO v_query_information;

    -- Handle NULL case: If query_information is NULL, use an empty JSONB array
    IF v_query_information IS NULL THEN
        v_attached_policies := to_jsonb(ARRAY[]::TEXT[]);
    ELSE
        -- Extract attached_policies key from query_information
        v_attached_policies := v_query_information->'attached_policies';

        -- Handle NULL case: If attached_policies is NULL, return an empty JSONB array
        IF v_attached_policies IS NULL THEN
            v_attached_policies := to_jsonb(ARRAY[]::TEXT[]);
        ELSE
            -- Ensure the result is formatted as a JSONB array
            v_attached_policies := to_jsonb(ARRAY(
                SELECT jsonb_array_elements_text(v_attached_policies)
            )::TEXT[]);
        END IF;
    END IF;

    -- Set the output result
    result := jsonb_build_object(
        'attached_policies', v_attached_policies,
        'query_executed', v_executed_query,
        'error_type', '',
        'error_message', '',
        'execution_status', TRUE
    );

EXCEPTION
    WHEN OTHERS THEN
        -- Handle exceptions and set the result with the error message
        result := jsonb_build_object(
            'attached_policies', to_jsonb(ARRAY[]::TEXT[]),
            'query_executed', v_executed_query,
            'error_type', SQLSTATE,
            'error_message', SQLERRM,
            'execution_status', FALSE
        );
END;
$procedure$;
