CREATE OR REPLACE FUNCTION public.get_paginate(tbl_name text, select_columns text[], joins text DEFAULT NULL::text, where_conditions text DEFAULT NULL::text, order_by_columns text[] DEFAULT NULL::text[], page_no integer DEFAULT 1, items_per_page integer DEFAULT 10)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
DECLARE
    query_str TEXT;
    count_query TEXT;
    total_item_count INT;
    total_pages INT;
    page_offset INT;
    page_item_data JSON;
BEGIN
    -- Calculate the offset for pagination
    page_offset := (page_no - 1) * items_per_page;

    -- Build the count query to get the total number of items
    count_query := 'SELECT COUNT(*) FROM ' || tbl_name;

    -- Add JOIN clauses to the count query if provided
    IF joins IS NOT NULL THEN
        count_query := count_query || ' ' || joins;
    END IF;

    -- Add WHERE conditions to the count query if provided
    IF where_conditions IS NOT NULL THEN
        count_query := count_query || ' WHERE ' || where_conditions;
    END IF;

    -- Execute the count query to get the total number of items
    EXECUTE count_query INTO total_item_count;

    -- Calculate the total number of pages
    total_pages := CEIL(total_item_count::NUMERIC / items_per_page::NUMERIC);

    -- Start building the main query
    query_str := 'SELECT ' || array_to_string(select_columns, ', ') || ' FROM ' || tbl_name;

    -- Add JOIN clauses if provided
    IF joins IS NOT NULL THEN
        query_str := query_str || ' ' || joins;
    END IF;

    -- Add WHERE conditions if provided
    IF where_conditions IS NOT NULL THEN
        query_str := query_str || ' WHERE ' || where_conditions;
    END IF;

    -- Add ORDER BY clause if provided
    IF order_by_columns IS NOT NULL THEN
        query_str := query_str || ' ORDER BY ' || array_to_string(order_by_columns, ', ');
    END IF;

    -- Add LIMIT and OFFSET for pagination
    query_str := query_str || ' LIMIT ' || items_per_page || ' OFFSET ' || page_offset;

    -- Execute the main query and store the result in page_item_data
    EXECUTE format('SELECT json_agg(t) FROM (%s) AS t', query_str) INTO page_item_data;

    -- Return the result as a JSON object
    RETURN json_build_object(
        'totalItemCount', total_item_count,
        'totalPages', total_pages,
        'pageNo', page_no,
        'limitPerPage', items_per_page,
        'pageItemData', page_item_data
    );
END;
$function$
