============== **************** Functions ****************** =======================


======****** 1. Title: Function to Dynamically Build SQL Select and Join Clauses with Nested Includes in PostgreSQL ***** ========

CREATE OR REPLACE FUNCTION public.build_includes(includes jsonb, parent_table text, parent_alias text)
 RETURNS TABLE(select_clause text, join_clause text, alias_mapping jsonb)
 LANGUAGE plpgsql
AS $function$
DECLARE
    include JSONB;
    attributes JSONB;
    table_name TEXT;
    join_type TEXT;
    join_condition TEXT;
    alias TEXT;
    col JSONB;
    nested_include JSONB;
    nested_result RECORD;
    local_select_clause TEXT := '';
    local_join_clause TEXT := '';
    local_alias_mapping JSONB := '{}';
BEGIN
    FOR include IN SELECT * FROM jsonb_array_elements(includes) LOOP
        attributes := include->'attributes';
        table_name := include->>'table_name';
        join_type := include->>'join_type';
        join_condition := include->>'join_condition';
        alias := include->>'alias';

        -- Build the SELECT clause
        FOR col IN SELECT * FROM jsonb_array_elements(attributes) LOOP
            IF local_select_clause <> '' THEN
                local_select_clause := local_select_clause || ', ';
            END IF;

			IF col->>0 ~ 'json_agg|SUM|COUNT|AVG|MIN|MAX' THEN
                local_select_clause := local_select_clause || (col->>0) || ' AS ' || quote_ident((col->>1));
            ELSE
                local_select_clause := local_select_clause || table_name || '.' || (col->>0) || ' AS ' || quote_ident((col->>1));
            END IF;
            
            local_alias_mapping := local_alias_mapping || jsonb_build_object(quote_ident((col->>1)), table_name || '.' || (col->>0));
        END LOOP;

        -- Build the JOIN clause
        local_join_clause := local_join_clause || ' ' || join_type || ' JOIN ' || table_name || ' ON ' || join_condition;

        -- Handle nested includes
        IF include ? 'includes' THEN
            nested_include := include->'includes';
            FOR nested_result IN SELECT * FROM build_includes(nested_include, table_name, alias) LOOP
                local_select_clause := local_select_clause || ', ' || nested_result.select_clause;
                local_join_clause := local_join_clause || ' ' || nested_result.join_clause;
                local_alias_mapping := local_alias_mapping || nested_result.alias_mapping;
            END LOOP;
        END IF;
    END LOOP;

    RETURN QUERY SELECT local_select_clause, local_join_clause, local_alias_mapping;
END;
$function$

=======================================================================================================

=======***** 2. Title: Function to Dynamically Build SQL Select, Join, Group By, and Having Clauses Based on JSONB Query Information in PostgreSQL ****=======
CREATE OR REPLACE FUNCTION replace_placeholder_variables(
    input_text TEXT,
    variables JSONB
) RETURNS TEXT AS $$
DECLARE
    key TEXT;
    val TEXT;
BEGIN
    BEGIN
        RAISE NOTICE 'INPUT TEXT: %, VARIABLES: %', input_text, variables;

        -- Loop through each key-value pair in the JSONB object
        FOR key, val IN
            SELECT k, variables ->> k
            FROM jsonb_object_keys(variables) AS k
        LOOP
            RAISE NOTICE 'Replacing % with %', key, val;
            input_text := replace(input_text, key, val);
        END LOOP;

        RETURN input_text;

    EXCEPTION
        WHEN OTHERS THEN
            -- If any error occurs, return the original input_text
            RAISE NOTICE 'Error occurred, returning input text unchanged';
            RETURN input_text;
    END;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.build_includes1(entity_name1 text, parent_table text, parent_alias text, var_company_id integer)
 RETURNS TABLE(select_clause text, join_clause text, alias_mapping jsonb, having_clause text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    query_information1 JSONB;
    include JSONB;
    attributes JSONB;
    table_name TEXT;
    join_type TEXT;
    join_condition TEXT;
    alias TEXT;
    nested_include JSONB;
    nested_result RECORD;
    local_select_clause TEXT := '';
    local_join_clause TEXT := '';
    local_alias_mapping JSONB := '{}';
    group_by JSONB;
    includes JSONB;
    group_col TEXT;
    v_having_conditions JSONB;
    v_having_clause TEXT := '';
    v_having_cond JSONB;
    v_valid_operators JSONB := jsonb_build_array('=', '!=', '<', '>', '<=', '>=', 'IS', 'IS NOT', 'IN', 'NOT IN', 'LIKE', 'NOT LIKE', 'ILIKE', 'NOT ILIKE', 'BETWEEN');
BEGIN
    -- Fetch the query_information from master_entities table
    SELECT query_information INTO query_information1
    FROM master_entities
    WHERE entity_name = entity_name1 AND company_id = var_company_id;
    
    -- Extract includes and group_by from query_information
    includes := query_information1->'includes';
    group_by := query_information1->'group_by';
    v_having_conditions := query_information1->'having_conditions';

    -- Build the JOIN clauses
    FOR include IN SELECT * FROM jsonb_array_elements(includes) LOOP
        table_name := include->>'table_name';
        join_type := include->>'join_type';
        join_condition := include->>'join_condition';
        alias := include->>'alias';

        -- Build the JOIN clause
        local_join_clause := local_join_clause || ' ' || join_type || ' JOIN ' || table_name || ' ON ' || join_condition;

        -- Handle nested includes
        IF include ? 'includes' THEN
            nested_include := include->'includes';
            FOR nested_result IN SELECT * FROM build_includes1(entity_name1, table_name, alias, var_company_id) LOOP
                local_select_clause := local_select_clause || ', ' || nested_result.select_clause;
                local_join_clause := local_join_clause || ' ' || nested_result.join_clause;
                local_alias_mapping := local_alias_mapping || nested_result.alias_mapping;
                v_having_clause := v_having_clause || ' ' || nested_result.having_clause; -- Include nested having clause
            END LOOP;
        END IF;
    END LOOP;

    -- Build the GROUP BY clause
    IF group_by IS NOT NULL THEN
        local_select_clause := ' GROUP BY ';
        FOR group_col IN SELECT * FROM jsonb_array_elements_text(group_by) LOOP
            IF local_select_clause <> ' GROUP BY ' THEN
                local_select_clause := local_select_clause || ', ';
            END IF;
            local_select_clause := local_select_clause || group_col;
        END LOOP;
    END IF;

    -- Build the HAVING clause
    IF v_having_conditions IS NOT NULL THEN
        v_having_clause := ' HAVING ';
        FOR v_having_cond IN SELECT * FROM jsonb_array_elements(v_having_conditions) LOOP
            -- Security check for column name and operator
            IF (v_having_cond->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',=]+$' THEN
                RAISE EXCEPTION 'Invalid characters in column name in HAVING: %', (v_having_cond->>'column_name');
            END IF;
            IF NOT v_valid_operators ? (v_having_cond->>'operator') THEN
                RAISE EXCEPTION 'Invalid operator in HAVING: %', (v_having_cond->>'operator');
            END IF;
            -- Construct the condition
            IF (v_having_cond->>'operator') = 'IN' OR (v_having_cond->>'operator') = 'NOT IN' THEN
                v_having_clause := v_having_clause || (v_having_cond->>'column_name') || ' ' || (v_having_cond->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_having_cond->'value') x), ', ') || ') AND ';
            ELSIF (v_having_cond->>'operator') = 'BETWEEN' THEN
                v_having_clause := v_having_clause || (v_having_cond->>'column_name') || ' BETWEEN ' || quote_literal((v_having_cond->'value')->>0) || ' AND ' || quote_literal((v_having_cond->'value')->>1) || ' AND ';
            ELSE
                IF (v_having_cond->>'value') IS NOT NULL THEN 
                    v_having_clause := v_having_clause || (v_having_cond->>'column_name') || ' ' || (v_having_cond->>'operator') || ' ' || quote_literal((v_having_cond->>'value')) || ' AND ';
                ELSIF (v_having_cond->>'operator' = 'IS' OR v_having_cond->>'operator' = 'IS NOT') THEN
                    v_having_clause := v_having_clause || (v_having_cond->>'column_name') || ' ' || (v_having_cond->>'operator') || ' ' || 'NULL' || ' AND ';
                END IF;
            END IF;
        END LOOP;
        -- Remove the trailing ' AND '
        IF v_having_clause <> '' THEN
            v_having_clause := LEFT(v_having_clause, LENGTH(v_having_clause) - 4);
        END IF;
    END IF;

    RETURN QUERY SELECT local_select_clause, local_join_clause, local_alias_mapping, v_having_clause;
END;
$function$

========================================================================================================

==========***** 3. Title: Function to Retrieve Grid Columns with Metadata for a Specified Entity and Company in JSONB Format in PostgreSQL *****=========

CREATE OR REPLACE FUNCTION public.get_grid_columns(var_entity_name text, var_company_id integer)
 RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
DECLARE
    select_columns JSONB := '[]';
    field RECORD;
BEGIN
    FOR field IN
        SELECT mgl.field_name, mgl.display_name,
	    mgl.order_no, is_searchable, is_grid_column, mgl.is_sortable, mgl.field_name, mgl.field_type_id, mgl.clause_type, mgl.link_type,
            mgl.link_action,mgl.link_mode
        FROM master_entities mg
        JOIN master_entity_line_items mgl ON mg.id = mgl.master_grid_id
        WHERE mg.entity_name = var_entity_name AND mg.company_id = var_company_id AND mgl.status_id = 1 AND (mgl.is_grid_column = true OR mgl.display_name IN ('id','uuid','downloadables') OR mgl.display_name LIKE 'gparam_%')
		ORDER BY mgl.order_no ASC
    LOOP
        select_columns := select_columns || jsonb_build_array(
			jsonb_build_array(
				field.field_name, field.display_name, field.order_no, field.is_searchable,
				field.is_grid_column, field.is_sortable, field.field_name, field.field_type_id,
                field.clause_type, field.link_type,
                field.link_action,field.link_mode
			)
		);
    END LOOP;
    RETURN select_columns;
END;
$function$

=======================================================================================================

============== **************** Procedures ****************** =======================


============******** 1. Title: Dynamic Data Fetcher with Filtering, Sorting, Grouping, and Pagination ********==========

CREATE OR REPLACE FUNCTION replace_values(
  input_json JSONB
)
RETURNS JSONB AS $$
DECLARE
  placeholder TEXT;
  value_json JSONB;
  resolved_value JSONB;
BEGIN
  -- Extract the input parameters
  placeholder := input_json ->> 'placeholder';
  value_json := input_json -> 'value_json';

  BEGIN
    -- Try to get the value corresponding to the placeholder in value_json
    resolved_value := value_json -> placeholder;

    -- If the placeholder is found in value_json, return it
    IF resolved_value IS NOT NULL THEN
      RETURN jsonb_build_object('value', resolved_value);
    ELSE
      -- If not found, return the placeholder itself
      RETURN jsonb_build_object('value', placeholder);
    END IF;

  EXCEPTION
    WHEN OTHERS THEN
      -- If any error occurs, return the placeholder as value
      RETURN jsonb_build_object('value', placeholder);
  END;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE PROCEDURE public.get_grid_items(
	IN json_input jsonb,
	INOUT result jsonb)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    v_record_offset INT;
    v_record_limit INT;
    v_sort_columns JSONB;
    v_search_all JSONB;
    v_search_any JSONB;
    v_select_columns JSONB;
	v_grid_params JSONB;
    v_entity_table TEXT;
    v_print_query BOOLEAN;
    v_entity_name TEXT;
    v_select_clause TEXT := '';
    v_join_clause TEXT := '';
    v_where_clause TEXT := ' WHERE ';
    v_group_by_clause TEXT := '';
    v_having_clause TEXT := ''; 
    v_query_str TEXT;
	v_count_query_str TEXT;
    v_total_records_count INT;
    v_records JSONB;
    v_headers JSONB := '[]';
    v_executed_query TEXT := '';
    v_company_id INT;
    v_group_by JSONB;
    v_having_conditions JSONB;
    v_having_any_conditions JSONB;
    
    -- Variables for iteration
    v_col JSONB;
    v_col_search JSONB;
    v_sort_col JSONB;
    v_inner_col_search JSONB;
    v_group_col TEXT;
    v_having_cond RECORD;
    
    -- To handle column aliases with "." notation
    v_alias_mapping JSONB := '{}';
    v_include_result_row RECORD;

    -- For dynamic JSON construction
    v_json_fields TEXT := '';
    v_json_object TEXT;
    v_header_object TEXT;
    
    -- For OR conditions
    v_or_clause TEXT := '';
    v_or_group TEXT := '';
    
    v_cte_clause TEXT := '';

    cunique TEXT;

    -- Security: Define valid operators
    v_valid_operators JSONB := jsonb_build_array('=', '!=', '<', '>', '<=', '>=', 'IS', 'IS NOT', 'IN', 'NOT IN', 'LIKE', 'NOT LIKE', 'ILIKE', 'NOT ILIKE', 'BETWEEN');

-- Sorting Variables
    v_sort_condition TEXT DEFAULT '';
    
	 v_query_info_search JSONB;
    v_combined_search_all JSONB;

	v_query_info_search_any JSONB;
    v_combined_search_any JSONB;
	 v_query_info_sort JSONB;
    v_final_sort_columns JSONB;
	v_user_id INT := NULL;
	input_json1 JSONB;
	input_json2 JSONB;
    replace_value_result JSONB;
	replaced_query TEXT;
BEGIN
    -- Parse the JSON input
    v_record_offset := COALESCE((json_input->>'start_index')::INT, 0);
    v_record_limit := COALESCE((json_input->>'limit_range')::INT, 10);
    v_sort_columns := (json_input->'sort_columns')::jsonb;
    v_search_all := (json_input->'search_all')::jsonb;
    v_search_any := (json_input->'search_any')::jsonb;
	v_grid_params := (json_input->'grid_params')::jsonb;
    v_entity_name := json_input->>'entity_name';
    v_company_id := COALESCE((json_input->>'company_id')::INT, 0);
    cunique := COALESCE(json_input->>'unique_id', NULL);
    -- Fetch print_query from master_entities
    SELECT COALESCE(query_information->>'print_query', 'false')::BOOLEAN INTO v_print_query
    FROM master_entities
    WHERE entity_name = v_entity_name AND company_id = v_company_id;
    -- v_print_query is now always from master_entities
    v_group_by := json_input->'group_by';
    v_having_conditions := json_input->'having_conditions';
    v_having_any_conditions := json_input->'having_any_conditions';
    v_user_id := COALESCE((json_input->>'user_id')::INT, NULL);

    -- Fetch the CTE from master_entities if available
    SELECT COALESCE(query_information->>'cte', NULL) INTO v_cte_clause
    FROM master_entities
    WHERE entity_name = v_entity_name AND company_id = v_company_id;
    -- If not found in table, fallback to input JSON
    IF v_cte_clause IS NULL OR TRIM(v_cte_clause) = '' THEN
        IF json_input ? 'cte' THEN
            v_cte_clause := json_input->>'cte';
        END IF;
    END IF;

    -- Fetch the select_columns dynamically
    v_select_columns := get_grid_columns(v_entity_name, v_company_id);

    -- Fetch the primary_table dynamically based on the entity_name
    SELECT primary_table INTO v_entity_table 
    FROM master_entities 
    WHERE entity_name = v_entity_name AND company_id = v_company_id;

    -- Build the SELECT clause
    FOR v_col IN SELECT * FROM jsonb_array_elements(v_select_columns) LOOP
        IF v_select_clause <> '' THEN
            v_select_clause := v_select_clause || ', ';
        END IF;
        v_select_clause := v_select_clause || (v_col->>0);
        IF (v_col->>1) IS NOT NULL THEN
            v_select_clause := v_select_clause || ' AS ' || quote_ident((v_col->>1));
                
                
            v_header_object := jsonb_build_object(
                'header', v_col->>1,
                'column_width', v_col->>2,
                'column_order', v_col->>2,
                'is_searchable', v_col->>3,
                'is_grid_column', v_col->>4,
                'is_sortable', v_col->>5,
                'field_value', v_col->>6,
                'clause_type', v_col->>8,
                'link_type', v_col->>9,
                'link_action', v_col->>10,
                'link_mode', v_col->>11,
                'field_type_id', v_col->>7
            )::TEXT;

            v_headers := v_headers || jsonb(v_header_object);
        END IF;
        v_alias_mapping := v_alias_mapping || jsonb_build_object(quote_ident((v_col->>1)), (v_col->>0));
    END LOOP;

    -- Build the JOIN clauses and GROUP BY clause
   -- IF cunique IS NOT NULL THEN
   -- input_json1 := jsonb_build_object('$session_user_id', v_user_id, '$unique_id', cunique);
--ELSE
    input_json1 := jsonb_build_object('$session_user_id', v_user_id);
--END IF;
    FOR v_include_result_row IN SELECT * FROM build_includes1(v_entity_name, '', '', v_company_id) LOOP
		-- Apply placeholder replacement only on current clause
		replaced_query := replace_placeholder_variables(v_include_result_row.join_clause, input_json1);
        -- Append the replaced clause
        v_join_clause := v_join_clause || ' ' || replaced_query;
        RAISE NOTICE 'v_join_clause: %', v_join_clause;
		v_alias_mapping := v_alias_mapping || v_include_result_row.alias_mapping;
        v_group_by_clause := v_include_result_row.select_clause;
        v_having_clause := v_include_result_row.having_clause;
    END LOOP;

	SELECT 
	COALESCE(query_information->>'search_all', '[]'::text)::jsonb,
	COALESCE(query_information->>'search_any', '[]'::text)::jsonb,
	 COALESCE(query_information->>'sort_columns', '[]'::text)::jsonb
    INTO v_query_info_search,v_query_info_search_any,v_query_info_sort
    FROM master_entities
    WHERE entity_name = v_entity_name 
    AND company_id = v_company_id;
    -- Combine search conditions from input and query_information
     v_combined_search_all := v_search_all || v_query_info_search;
	v_combined_search_any := v_search_any || v_query_info_search_any;

    -- IF cunique IS NOT NULL THEN
    --     v_combined_search_all := jsonb_agg(
    --         jsonb_build_object(
    --             'column_name', elem->>'column_name',
    --             'operator', elem->>'operator',
    --             'value', 
    --                 CASE 
    --                     WHEN elem->>'value' LIKE '%$unique_id%' 
    --                     THEN replace(elem->>'value', '$unique_id', cunique)
    --                     ELSE elem->>'value'
    --                 END
    --         )
    --     ) 
    --     FROM jsonb_array_elements(v_combined_search_all) AS elem;

    --     v_combined_search_any := jsonb_agg(
    --         jsonb_build_object(
    --             'column_name', elem->>'column_name',
    --             'operator', elem->>'operator',
    --             'value', 
    --                 CASE 
    --                     WHEN elem->>'value' LIKE '%$unique_id%' 
    --                     THEN replace(elem->>'value', '$unique_id', cunique)
    --                     ELSE elem->>'value'
    --                 END
    --         )
    --     ) 
    --     FROM jsonb_array_elements(v_combined_search_any) AS elem;
    -- END IF;

    -- Build the WHERE clause for column-specific search
    v_where_clause := v_where_clause || v_entity_table || '.company_id' || ' = ' || quote_literal(v_company_id);
    -- Handle the "search_all" conditions with AND
    FOR v_col_search IN SELECT * FROM jsonb_array_elements(v_combined_search_all) LOOP
        -- Security check for column name and operator
        -- IF (v_col_search->>'column_name') !~ '^[a-zA-Z0-9_\\. ()'',:]+$' THEN
         IF (v_col_search->>'column_name') !~ '^[a-zA-Z0-9_\\. ()'',:|\\-]+$' THEN
            RAISE EXCEPTION 'Invalid characters in column name: %', (v_col_search->>'column_name');
        END IF;
        IF NOT v_valid_operators ? (v_col_search->>'operator') THEN
            RAISE EXCEPTION 'Invalid operator: %', (v_col_search->>'operator');
        END IF;
        -- Construct the condition
        IF (v_col_search->>'operator') = 'IN' OR (v_col_search->>'operator') = 'NOT IN' THEN
            v_where_clause := v_where_clause || ' AND ' || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_col_search->'value') x), ', ') || ')';
        ELSIF (v_col_search->>'operator') = 'BETWEEN' THEN
            v_where_clause := v_where_clause || ' AND ' || (v_col_search->>'column_name') || ' BETWEEN ' || quote_literal((v_col_search->'value')->>0) || ' AND ' || quote_literal((v_col_search->'value')->>1);
        ELSE
            IF (v_col_search->>'value') IS NOT NULL THEN 
				  input_json2 := jsonb_build_object(
				    'placeholder', (v_col_search->>'value'),  -- The placeholder to look for
				    'value_json', jsonb_build_object('$session_user_id', v_user_id)  -- Add v_user_id to the JSON object
				  );
				  
				  -- Call the replace_values function
				  replace_value_result := replace_values(input_json2);
                v_where_clause := v_where_clause || ' AND ' || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' ' || quote_literal((replace_value_result->>'value'));
            ELSIF (v_col_search->>'operator' = 'IS' OR v_col_search->>'operator' = 'IS NOT') THEN
                v_where_clause := v_where_clause || ' AND ' || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' ' || 'NULL';
            END IF;
        END IF;
    END LOOP;

    -- Handle the "search_any" conditions with nested OR
    FOR v_col_search IN SELECT * FROM jsonb_array_elements(v_combined_search_any) LOOP
        IF jsonb_typeof(v_col_search) = 'array' THEN
            -- Multiple sets of conditions
            v_or_group := '';
            FOR v_inner_col_search IN SELECT * FROM jsonb_array_elements(v_col_search) LOOP
                -- Security check for column name and operator
                --IF (v_inner_col_search->>'column_name') !~ '^[a-zA-Z0-9_\\. ()'',:]+$' THEN
                 IF (v_inner_col_search->>'column_name') !~ '^[a-zA-Z0-9_\\. ()'',:|\\-]+$' THEN
                    RAISE EXCEPTION 'Invalid characters in column name: %', (v_inner_col_search->>'column_name');
                END IF;
                IF NOT v_valid_operators ? (v_inner_col_search->>'operator') THEN
                    RAISE EXCEPTION 'Invalid operator: %', (v_inner_col_search->>'operator');
                END IF;
                -- Construct the condition
                IF (v_inner_col_search->>'operator') = 'IN' OR (v_inner_col_search->>'operator') = 'NOT IN' THEN
                    v_or_group := v_or_group || (v_inner_col_search->>'column_name') || ' ' || (v_inner_col_search->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_inner_col_search->'value') x), ', ') || ') OR ';
                ELSIF (v_inner_col_search->>'operator') = 'BETWEEN' THEN
                    v_or_group := v_or_group || (v_inner_col_search->>'column_name') || ' BETWEEN ' || quote_literal((v_inner_col_search->'value')->>0) || ' AND ' || quote_literal((v_inner_col_search->'value')->>1) || ' OR ';
                ELSE
                    IF (v_inner_col_search->>'value') IS NOT NULL THEN 
						input_json2 := jsonb_build_object(
							'placeholder', (v_inner_col_search->>'value'),  -- The placeholder to look for
							'value_json', jsonb_build_object('$session_user_id', v_user_id)  -- Add v_user_id to the JSON object
						  );
						  
						  -- Call the replace_values function
						  replace_value_result := replace_values(input_json2);
                        v_or_group := v_or_group || (v_inner_col_search->>'column_name') || ' ' || (v_inner_col_search->>'operator') || ' ' || quote_literal((replace_value_result->>'value')) || ' OR ';
                    ELSIF (v_inner_col_search->>'operator' = 'IS' OR v_inner_col_search->>'operator' = 'IS NOT') THEN
                        v_or_group := v_or_group || (v_inner_col_search->>'column_name') || ' ' || (v_inner_col_search->>'operator') || ' ' || 'NULL' || ' OR ';
                    END IF;
                END IF;
            END LOOP;
            -- Remove the trailing ' OR ' and add to the OR clause
            IF v_or_group <> '' THEN
                v_or_group := LEFT(v_or_group, LENGTH(v_or_group) - 4);
                v_or_clause := v_or_clause || '(' || v_or_group || ')' || ' AND ';
            END IF;
        ELSE
            -- Single set of conditions
            -- Security check for column name and operator
           -- IF (v_col_search->>'column_name') !~ '^[a-zA-Z0-9_\\. ()'',:]+$' THEN
            IF (v_col_search->>'column_name') !~ '^[a-zA-Z0-9_\\. ()'',:|\\-]+$' THEN
                RAISE EXCEPTION 'Invalid characters in column name: %', (v_col_search->>'column_name');
            END IF;
            IF NOT v_valid_operators ? (v_col_search->>'operator') THEN
                RAISE EXCEPTION 'Invalid operator: %', (v_col_search->>'operator');
            END IF;
            -- Construct the condition
            IF (v_col_search->>'operator') = 'IN' OR (v_col_search->>'operator') = 'NOT IN' THEN
                v_or_clause := v_or_clause || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_col_search->'value') x), ', ') || ') OR ';
            ELSIF (v_col_search->>'operator') = 'BETWEEN' THEN
                v_or_clause := v_or_clause || (v_col_search->>'column_name') || ' BETWEEN ' || quote_literal((v_col_search->'value')->>0) || ' AND ' || quote_literal((v_col_search->'value')->>1) || ' OR ';
            ELSE
                IF (v_col_search->>'value') IS NOT NULL THEN 
					input_json2 := jsonb_build_object(
							'placeholder', (v_col_search->>'value'),  -- The placeholder to look for
							'value_json', jsonb_build_object('$session_user_id', v_user_id)  -- Add v_user_id to the JSON object
						  );
						  
					-- Call the replace_values function
					replace_value_result := replace_values(input_json2);
                    v_or_clause := v_or_clause || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' ' || quote_literal((replace_value_result->>'value')) || ' OR ';
                ELSIF (v_col_search->>'operator' = 'IS' OR v_col_search->>'operator' = 'IS NOT') THEN
                    v_or_clause := v_or_clause || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' ' || 'NULL' || ' OR ';
                END IF;
            END IF;
        END IF;
    END LOOP;

    -- Remove the trailing ' OR ' and ' AND ' and add to the where clause
    IF v_or_clause <> '' THEN
        v_or_clause := LEFT(v_or_clause, LENGTH(v_or_clause) - 4);
        v_where_clause := v_where_clause || ' AND (' || v_or_clause || ')';
    END IF;

    -- Build the GROUP BY clause
    IF v_group_by IS NOT NULL THEN
        FOR v_group_col IN SELECT * FROM jsonb_array_elements_text(v_group_by) LOOP
		    IF v_group_by_clause = '' THEN
		        v_group_by_clause := ' GROUP BY ';
		    END IF;
            IF v_group_by_clause <> ' GROUP BY ' THEN
                v_group_by_clause := v_group_by_clause || ', ';
            END IF;
            v_group_by_clause := v_group_by_clause || v_group_col;
        END LOOP;
    END IF;

	-- Build the HAVING clause
    IF v_having_conditions IS NOT NULL THEN
		IF v_having_clause = '' THEN
			v_having_clause := ' HAVING ';
		END IF;
        FOR v_having_cond IN SELECT * FROM jsonb_array_elements(v_having_conditions) LOOP
            -- Security check for column name and operator
            --IF (v_having_cond.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',:]+$' THEN
            IF (v_having_cond.value->>'column_name') !~ '^[a-zA-Z0-9_\\. ()'',:|\\-]+$' THEN
                RAISE EXCEPTION 'Invalid characters in column name in HAVING: %', (v_having_cond.value->>'column_name');
            END IF;
            IF NOT v_valid_operators ? (v_having_cond.value->>'operator') THEN
                RAISE EXCEPTION 'Invalid operator in HAVING: %', (v_having_cond.value->>'operator');
            END IF;
            -- Construct the condition
            IF (v_having_cond.value->>'operator') = 'IN' OR (v_having_cond.value->>'operator') = 'NOT IN' THEN
                v_having_clause := v_having_clause || (v_having_cond.value->>'column_name') || ' ' || (v_having_cond.value->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_having_cond.value->'value') x), ', ') || ') AND ';
            ELSIF (v_having_cond.value->>'operator') = 'BETWEEN' THEN
                v_having_clause := v_having_clause || (v_having_cond.value->>'column_name') || ' BETWEEN ' || quote_literal((v_having_cond.value->'value')->>0) || ' AND ' || quote_literal((v_having_cond.value->'value')->>1) || ' AND ';
            ELSE
                IF (v_having_cond.value->>'value') IS NOT NULL THEN 
					input_json2 := jsonb_build_object(
							'placeholder', (v_having_cond.value->>'value'),  -- The placeholder to look for
							'value_json', jsonb_build_object('$session_user_id', v_user_id)  -- Add v_user_id to the JSON object
						  );
						  
					-- Call the replace_values function
					replace_value_result := replace_values(input_json2);
                    v_having_clause := v_having_clause || (v_having_cond.value->>'column_name') || ' ' || (v_having_cond.value->>'operator') || ' ' || quote_literal((replace_value_result->>'value')) || ' AND ';
                ELSIF (v_having_cond.value->>'operator' = 'IS' OR v_having_cond.value->>'operator' = 'IS NOT') THEN

                    v_having_clause := v_having_clause || (v_having_cond.value->>'column_name') || ' ' || (v_having_cond.value->>'operator') || ' ' || 'NULL' || ' AND ';
                END IF;
            END IF;
        END LOOP;
        -- Remove the trailing ' AND '
        IF v_having_clause <> '' THEN
            v_having_clause := LEFT(v_having_clause, LENGTH(v_having_clause) - 4);
        END IF;
    END IF;

    -- Build the HAVING any clause
    IF v_having_any_conditions IS NOT NULL THEN
        IF v_having_clause = '' THEN
            v_having_clause := ' HAVING ';
        ELSE
            v_having_clause := v_having_clause || ' AND (';
        END IF;
        FOR v_having_cond IN SELECT * FROM jsonb_array_elements(v_having_any_conditions) LOOP
            -- Security check for column name and operator
            --IF (v_having_cond.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',:]+$' THEN
            IF (v_having_cond.value->>'column_name') !~ '^[a-zA-Z0-9_\\. ()'',:|\\-]+$' THEN
                RAISE EXCEPTION 'Invalid characters in column name in HAVING: %', (v_having_cond.value->>'column_name');
            END IF;
            IF NOT v_valid_operators ? (v_having_cond.value->>'operator') THEN
                RAISE EXCEPTION 'Invalid operator in HAVING: %', (v_having_cond.value->>'operator');
            END IF;
            -- Construct the condition
            IF (v_having_cond.value->>'operator') = 'IN' OR (v_having_cond.value->>'operator') = 'NOT IN' THEN
                v_having_clause := v_having_clause || (v_having_cond.value->>'column_name') || ' ' || (v_having_cond.value->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_having_cond.value->'value') x), ', ') || ') OR ';
            ELSIF (v_having_cond.value->>'operator') = 'BETWEEN' THEN
                v_having_clause := v_having_clause || (v_having_cond.value->>'column_name') || ' BETWEEN ' || quote_literal((v_having_cond.value->'value')->>0) || ' AND ' || quote_literal((v_having_cond.value->'value')->>1) || ' OR ';
            ELSE
                IF (v_having_cond.value->>'value') IS NOT NULL THEN 
                    input_json2 := jsonb_build_object(
                            'placeholder', (v_having_cond.value->>'value'),  -- The placeholder to look for
                            'value_json', jsonb_build_object('$session_user_id', v_user_id)  -- Add v_user_id to the JSON object
                          );
                          
                    -- Call the replace_values function
                    replace_value_result := replace_values(input_json2);
                    v_having_clause := v_having_clause || (v_having_cond.value->>'column_name') || ' ' || (v_having_cond.value->>'operator') || ' ' || quote_literal((replace_value_result->>'value')) || ' OR ';
                ELSIF (v_having_cond.value->>'operator' = 'IS' OR v_having_cond.value->>'operator' = 'IS NOT') THEN
                    v_having_clause := v_having_clause || (v_having_cond.value->>'column_name') || ' ' || (v_having_cond.value->>'operator') || ' ' || 'NULL' || ' OR ';
                END IF;
            END IF;
        END LOOP;
        -- Remove the trailing ' OR '
        IF v_having_clause <> '' THEN
            v_having_clause := LEFT(v_having_clause, LENGTH(v_having_clause) - 3);
        END IF;
        IF v_having_conditions IS NOT NULL THEN
            v_having_clause := v_having_clause || ')';
        END IF;
    END IF;
    RAISE NOTICE 'HAVING Clause1: %', v_having_clause;

    -- Handle CTE if present
    IF json_input ? 'cte' THEN
        v_cte_clause := json_input->>'cte';
    END IF;

    -- Build the base query (without limit/offset/order yet)
    v_query_str := 'SELECT ' || v_select_clause || ' FROM ' || v_entity_table || v_join_clause || v_where_clause || v_group_by_clause || v_having_clause;

    -- Prepend CTE if present
    --IF v_cte_clause IS NOT NULL AND TRIM(v_cte_clause) <> '' THEN
      --  v_query_str := v_cte_clause || ' ' || v_query_str;
    --END IF;

    -- Total records count
    IF v_cte_clause IS NOT NULL AND TRIM(v_cte_clause) <> '' THEN
    v_count_query_str := v_cte_clause || ' SELECT COUNT(*) FROM (SELECT 1 FROM ' || v_entity_table || v_join_clause || v_where_clause || v_group_by_clause || v_having_clause || ') AS subquery';
ELSE
    v_count_query_str := 'SELECT COUNT(*) FROM (SELECT 1 FROM ' || v_entity_table || v_join_clause || v_where_clause || v_group_by_clause || v_having_clause || ') AS subquery';
END IF;


v_count_query_str := replace_placeholder_variables(v_count_query_str, v_grid_params);

	EXECUTE v_count_query_str INTO v_total_records_count;

    --IF v_cte_clause IS NOT NULL AND TRIM(v_cte_clause) <> '' THEN
      --  EXECUTE v_cte_clause || ' SELECT COUNT(*) FROM (SELECT 1 FROM ' || v_entity_table || v_join_clause || v_where_clause || v_group_by_clause || v_having_clause || ') AS subquery' INTO v_total_records_count;
    --ELSE
      --  EXECUTE 'SELECT COUNT(*) FROM (SELECT 1 FROM ' || v_entity_table || v_join_clause || v_where_clause || v_group_by_clause || v_having_clause || ') AS subquery' INTO v_total_records_count;
    --END IF;

    -- Determine final sort columns
    IF jsonb_array_length(v_sort_columns) > 0 THEN
        v_final_sort_columns := v_sort_columns;
    ELSIF jsonb_array_length(v_query_info_sort) > 0 THEN
        v_final_sort_columns := v_query_info_sort;
    ELSE
        v_final_sort_columns := '[]'::jsonb;
    END IF;

    IF v_final_sort_columns IS NOT NULL THEN
        FOR v_sort_col IN SELECT * FROM jsonb_array_elements(v_final_sort_columns) LOOP
            -- Validate column names to prevent SQL injection
            IF (v_sort_col->>0) !~ '^[a-zA-Z0-9_\\. ()'',:]+$' THEN
                RAISE EXCEPTION 'Invalid column name in sort: %', (v_sort_col->>0);
            END IF;
            IF v_sort_condition = '' THEN
                v_sort_condition := (v_sort_col->>0) || ' ' || (v_sort_col->>1);
            ELSE
                v_sort_condition := v_sort_condition || ', ' || (v_sort_col->>0) || ' ' || (v_sort_col->>1);
            END IF;
        END LOOP;

        -- Append ORDER BY clause only once
        IF v_sort_condition <> '' THEN
            v_query_str := v_query_str || ' ORDER BY ' || v_sort_condition;
        END IF;
    END IF;

    -- Add limit/offset
    --v_query_str := v_query_str || ' LIMIT ' || v_record_limit || ' OFFSET ' || v_record_offset;
    v_query_str := v_query_str || ' LIMIT ' || v_record_limit || ' OFFSET ' || v_record_offset;

    

    -- Prepend CTE to the final query string if present (for both execution and query_executed)
    --IF v_cte_clause IS NOT NULL AND TRIM(v_cte_clause) <> '' THEN
      --  v_query_str := v_cte_clause || ' ' || v_query_str;
    --END IF;

    IF v_cte_clause IS NOT NULL AND TRIM(v_cte_clause) <> '' THEN
    v_query_str := v_cte_clause || ' ' || v_query_str;
END IF;

	-- Replace all variables with its respective values here (For example $gparam_1,$gparam_2..etc.)
	v_query_str := replace_placeholder_variables(v_query_str, v_grid_params);

    -- Store the executed query if print_query is true
    IF v_print_query THEN
        v_executed_query := v_query_str;
    END IF;

    -- Return empty records array since we want plain SELECT
    v_records := '[]'::jsonb;



-- Build the records JSON manually
FOR v_include_result_row IN EXECUTE v_query_str LOOP
    v_json_object := row_to_json(v_include_result_row)::text;
    v_records := v_records || v_json_object::jsonb;
END LOOP;

  IF v_print_query THEN
    v_executed_query := v_query_str;
    END IF;
    -- Set the output result
    IF v_print_query THEN
        result := jsonb_build_object(
            'total_records_count', v_total_records_count,
            'records', v_records,
            'headers', v_headers,
            'query_executed', v_executed_query,
            'error_type', '',
            'error_message', '',
            'execution_status', TRUE
        );
    ELSE
        result := jsonb_build_object(
            'total_records_count', v_total_records_count,
            'records', v_records,
            'headers', v_headers,
            'error_type', '',
            'error_message', '',
            'execution_status', TRUE
        );
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        IF v_print_query THEN
            result := jsonb_build_object(
                'total_records_count', 0,
                'records', jsonb '[]',
                'query_executed', v_executed_query,
                'error_type', SQLSTATE,
                'error_message', SQLERRM,
                'execution_status', FALSE
            );
        ELSE
            result := jsonb_build_object(
                'total_records_count', 0,
                'records', jsonb '[]',
                'error_type', SQLSTATE,
                'error_message', SQLERRM,
                'execution_status', FALSE
            );
        END IF;
END;
$BODY$;



ALTER PROCEDURE public.get_grid_items(jsonb, jsonb)
    OWNER TO postgres;


=======================================================================================================

============********** 2. Title: Get Multiple Records with Advanced Filters and Aggregations *****=================

CREATE TYPE IncludeResult AS
(
	select_clause text,
	join_clause text,
	alias_mapping jsonb
);

CREATE OR REPLACE PROCEDURE public.get_multiple_records(
	IN json_input jsonb,
	INOUT result jsonb)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    v_record_offset INT;
    v_limit_range INT;
    
    v_sort_columns JSONB;
    v_search_all JSONB;
    v_search_any JSONB;
    v_select_columns JSONB;
    v_primary_table TEXT;
    v_includes JSONB;
    v_group_by JSONB;
    v_having_conditions JSONB; -- New variable for HAVING conditions
    v_print_query BOOLEAN;
    v_select_clause TEXT := '';
    v_join_clause TEXT := '';
    v_where_clause TEXT := ' WHERE ';
    v_group_by_clause TEXT := '';
    v_order_by_clause TEXT := '';
    v_having_clause TEXT := ''; -- New variable for HAVING clause
    v_cte_clause TEXT := ''; -- Added missing variable declaration
    v_query_str TEXT;
    v_total_records_count INT;
    v_records JSONB;
    v_executed_query TEXT := '';
	v_count_query_str TEXT;
    v_company_id INT;
    
    -- Variables for iteration
    v_col JSONB;
    v_col_search RECORD;
    v_sort_col RECORD;
    v_inner_col_search RECORD;
    v_group_col TEXT;
    v_having_cond RECORD; -- New variable for HAVING condition iteration

    -- To handle column aliases with "." notation
    v_alias_mapping JSONB := '{}';
    v_include_result_row IncludeResult;

    -- For OR conditions
    v_or_clause TEXT := '';
    v_or_group TEXT := '';

	v_grid_params JSONB;
	rec RECORD;  -- for manual row-by-row JSON building

    -- Security: Define valid operators
    v_valid_operators JSONB := jsonb_build_array('=', '!=', '<', '>', '<=', '>=', 'IS', 'IS NOT', 'IN', 'NOT IN', 'LIKE', 'NOT LIKE', 'ILIKE', 'NOT ILIKE', 'BETWEEN');
BEGIN
    -- Parse the JSON input
    v_record_offset := COALESCE((json_input->>'start_index')::INT, 0);
    v_limit_range := COALESCE((json_input->>'limit_range')::INT, 0);
    
    v_company_id := COALESCE((json_input->>'company_id')::INT, 0);
    v_sort_columns := (json_input->'sort_columns')::jsonb;
    v_search_all := (json_input->'search_all')::jsonb;
    v_search_any := (json_input->'search_any')::jsonb;
    v_select_columns := (json_input->'select_columns')::jsonb;
    v_primary_table := json_input->>'primary_table';
    v_includes := json_input->'includes';
    v_group_by := json_input->'group_by';
    v_having_conditions := json_input->'having_conditions'; -- Parse HAVING conditions
    v_print_query := COALESCE((json_input->>'print_query')::BOOLEAN, FALSE);
	v_grid_params := (json_input->'grid_params')::jsonb;

    -- Build the SELECT clause
    FOR v_col IN SELECT * FROM jsonb_array_elements(v_select_columns) LOOP
        IF v_select_clause <> '' THEN
            v_select_clause := v_select_clause || ', ';
        END IF;
        IF (v_col->>1) IS NOT NULL AND (v_col->>1) <> '' THEN
            v_select_clause := v_select_clause || (v_col->>0) || ' AS ' || quote_ident((v_col->>1));
            v_alias_mapping := v_alias_mapping || jsonb_build_object(quote_ident((v_col->>1)), (v_col->>0));
        ELSE
            v_select_clause := v_select_clause || (v_col->>0);
        END IF;
    END LOOP;

    -- Build the JOIN clauses
    v_include_result_row := build_includes(v_includes, '', '');
    v_join_clause := v_join_clause || ' ' || v_include_result_row.join_clause;
    v_alias_mapping := v_alias_mapping || v_include_result_row.alias_mapping;

    -- Build the WHERE clause for column-specific search
    v_where_clause := v_where_clause || ' ' || v_primary_table || '.company_id' || ' = ' || quote_literal(v_company_id);

    -- Handle the "search_all" conditions with AND
    FOR v_col_search IN SELECT * FROM jsonb_array_elements(v_search_all) LOOP
        -- Security check for column name and operator
        
		--IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',=!<>]+$' THEN
		--IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',=<>!:-|]+$' THEN
		--IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_. ()'',=!<>-]+$' THEN
		IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_.: ()'',=!<>-]+$' THEN
		
            RAISE EXCEPTION 'Invalid characters in column name: %', (v_col_search.value->>'column_name');
        END IF;
        IF NOT v_valid_operators ? (v_col_search.value->>'operator') THEN
            RAISE EXCEPTION 'Invalid operator: %', (v_col_search.value->>'operator');
        END IF;
        -- Construct the condition
        IF (v_col_search.value->>'operator') = 'IN' OR (v_col_search.value->>'operator') = 'NOT IN' THEN
            v_where_clause := v_where_clause || ' AND ' || (v_col_search.value->>'column_name') || ' ' || (v_col_search.value->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_col_search.value->'value') x), ', ') || ')';
        ELSIF (v_col_search.value->>'operator') = 'BETWEEN' THEN
            v_where_clause := v_where_clause || ' AND ' || (v_col_search.value->>'column_name') || ' BETWEEN ' || quote_literal((v_col_search.value->'value')->>0) || ' AND ' || quote_literal((v_col_search.value->'value')->>1);
        ELSE
            IF (v_col_search.value->>'value') IS NOT NULL THEN 
                -- v_where_clause := v_where_clause || ' AND ' || (v_col_search.value->>'column_name') || ' ' || (v_col_search.value->>'operator') || ' ' || quote_literal((v_col_search.value->>'value'));
                        IF upper(trim(v_col_search.value->>'value')) IN ('CURRENT_DATE', 'NOW()', 'CURRENT_TIMESTAMP') THEN
                            v_where_clause := v_where_clause || ' AND ' || (v_col_search.value->>'column_name') || ' ' || (v_col_search.value->>'operator') || ' ' || (v_col_search.value->>'value');
                        ELSE
                            v_where_clause := v_where_clause || ' AND ' || (v_col_search.value->>'column_name') || ' ' || (v_col_search.value->>'operator') || ' ' || quote_literal((v_col_search.value->>'value'));
                        END IF;
            ELSIF (v_col_search.value->>'operator' = 'IS' OR v_col_search.value->>'operator' = 'IS NOT') THEN
                v_where_clause := v_where_clause || ' AND ' || (v_col_search.value->>'column_name') || ' ' || (v_col_search.value->>'operator') || ' ' || 'NULL';
            END IF;
        END IF;
    END LOOP;

    -- Handle the "search_any" conditions with nested OR
    FOR v_col_search IN SELECT * FROM jsonb_array_elements(v_search_any) LOOP
        IF jsonb_typeof(v_col_search.value) = 'array' THEN
            -- Multiple sets of conditions
            v_or_group := '';
            FOR v_inner_col_search IN SELECT * FROM jsonb_array_elements(v_col_search.value) LOOP
                -- Security check for column name and operator
				--IF (v_inner_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',=<>!:-|]+$' THEN
				--IF (v_inner_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_. ()'',=!<>-]+$' THEN
				--IF (v_inner_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_. ()'',=!<>-]+$' THEN
				IF (v_inner_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_.: ()'',=!<>-]+$' THEN
                --IF (v_inner_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',=]+$' THEN
                    RAISE EXCEPTION 'Invalid characters in column name: %', (v_inner_col_search.value->>'column_name');
                END IF;
                IF NOT v_valid_operators ? (v_inner_col_search.value->>'operator') THEN
                    RAISE EXCEPTION 'Invalid operator: %', (v_inner_col_search.value->>'operator');
                END IF;
                -- Construct the condition
                IF (v_inner_col_search.value->>'operator') = 'IN' OR (v_inner_col_search.value->>'operator') = 'NOT IN' THEN
                    v_or_group := v_or_group || (v_inner_col_search.value->>'column_name') || ' ' || (v_inner_col_search.value->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_inner_col_search.value->'value') x), ', ') || ') OR ';
                ELSIF (v_inner_col_search.value->>'operator') = 'BETWEEN' THEN
                    v_or_group := v_or_group || (v_inner_col_search.value->>'column_name') || ' BETWEEN ' || quote_literal((v_inner_col_search.value->'value')->>0) || ' AND ' || quote_literal((v_inner_col_search.value->'value')->>1) || ' OR ';
                ELSE
                    IF (v_inner_col_search.value->>'value') IS NOT NULL THEN 
                        v_or_group := v_or_group || (v_inner_col_search.value->>'column_name') || ' ' || (v_inner_col_search.value->>'operator') || ' ' || quote_literal((v_inner_col_search.value->>'value')) || ' OR ';
                    ELSIF (v_inner_col_search.value->>'operator' = 'IS' OR v_inner_col_search.value->>'operator' = 'IS NOT') THEN
                        v_or_group := v_or_group || (v_inner_col_search.value->>'column_name') || ' ' || (v_inner_col_search.value->>'operator') || ' ' || 'NULL' || ' OR ';
                    END IF;
                END IF;
            END LOOP;
            -- Remove the trailing ' OR ' and add to the OR clause
            IF v_or_group <> '' THEN
                v_or_group := LEFT(v_or_group, LENGTH(v_or_group) - 4);
                v_or_clause := v_or_clause || '(' || v_or_group || ')' || ' AND ';
            END IF;
        ELSE
            -- Single set of conditions
            -- Security check for column name and operator
            --IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',=]+$' THEN
			--IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',=<>!:-|]+$' THEN
			--IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_. ()'',=!<>-]+$' THEN
			IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_.: ()'',=!<>-]+$' THEN
                RAISE EXCEPTION 'Invalid characters in column name: %', (v_col_search.value->>'column_name');
            END IF;
            IF NOT v_valid_operators ? (v_col_search.value->>'operator') THEN
                RAISE EXCEPTION 'Invalid operator: %', (v_col_search.value->>'operator');
            END IF;
            -- Construct the condition
            IF (v_col_search.value->>'operator') = 'IN' OR (v_col_search.value->>'operator') = 'NOT IN' THEN
                v_or_clause := v_or_clause || (v_col_search.value->>'column_name') || ' ' || (v_col_search.value->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_col_search.value->'value') x), ', ') || ') OR ';
            ELSIF (v_col_search.value->>'operator') = 'BETWEEN' THEN
                v_or_clause := v_or_clause || (v_col_search.value->>'column_name') || ' BETWEEN ' || quote_literal((v_col_search.value->'value')->>0) || ' AND ' || quote_literal((v_col_search.value->'value')->>1) || ' OR ';
            ELSE
                IF (v_col_search.value->>'value') IS NOT NULL THEN 
                    v_or_clause := v_or_clause || (v_col_search.value->>'column_name') || ' ' || (v_col_search.value->>'operator') || ' ' || quote_literal((v_col_search.value->>'value')) || ' OR ';
                ELSIF (v_col_search.value->>'operator' = 'IS' OR v_col_search.value->>'operator' = 'IS NOT') THEN
                    v_or_clause := v_or_clause || (v_col_search.value->>'column_name') || ' ' || (v_col_search.value->>'operator') || ' ' || 'NULL' || ' OR ';
                END IF;
            END IF;
        END IF;
    END LOOP;

    -- Remove the trailing ' OR ' and ' AND ' and add to the where clause
    IF v_or_clause <> '' THEN
        v_or_clause := LEFT(v_or_clause, LENGTH(v_or_clause) - 4);
        v_where_clause := v_where_clause || ' AND (' || v_or_clause || ')';
    END IF;

    -- Build the GROUP BY clause
    IF v_group_by IS NOT NULL THEN
        FOR v_group_col IN SELECT * FROM jsonb_array_elements_text(v_group_by) LOOP
            IF v_group_by_clause <> '' THEN
                v_group_by_clause := v_group_by_clause || ', ';
            END IF;
            v_group_by_clause := v_group_by_clause || v_group_col;
        END LOOP;
        v_group_by_clause := ' GROUP BY ' || v_group_by_clause;
    END IF;

    -- Build the HAVING clause
    IF v_having_conditions IS NOT NULL THEN
        v_having_clause := ' HAVING ';
        FOR v_having_cond IN SELECT * FROM jsonb_array_elements(v_having_conditions) LOOP
            -- Security check for column name and operator
            IF (v_having_cond.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',!=]+$' THEN
                RAISE EXCEPTION 'Invalid characters in column name in HAVING: %', (v_having_cond.value->>'column_name');
            END IF;
            IF NOT v_valid_operators ? (v_having_cond.value->>'operator') THEN
                RAISE EXCEPTION 'Invalid operator in HAVING: %', (v_having_cond.value->>'operator');
            END IF;
            -- Construct the condition
            IF (v_having_cond.value->>'operator') = 'IN' OR (v_having_cond.value->>'operator') = 'NOT IN' THEN
                v_having_clause := v_having_clause || (v_having_cond.value->>'column_name') || ' ' || (v_having_cond.value->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_having_cond.value->'value') x), ', ') || ') AND ';
            ELSIF (v_having_cond.value->>'operator') = 'BETWEEN' THEN
                v_having_clause := v_having_clause || (v_having_cond.value->>'column_name') || ' BETWEEN ' || quote_literal((v_having_cond.value->'value')->>0) || ' AND ' || quote_literal((v_having_cond.value->'value')->>1) || ' AND ';
            ELSE
                IF (v_having_cond.value->>'value') IS NOT NULL THEN 
                    v_having_clause := v_having_clause || (v_having_cond.value->>'column_name') || ' ' || (v_having_cond.value->>'operator') || ' ' || quote_literal((v_having_cond.value->>'value')) || ' AND ';
                ELSIF (v_having_cond.value->>'operator' = 'IS' OR v_having_cond.value->>'operator' = 'IS NOT') THEN
                    v_having_clause := v_having_clause || (v_having_cond.value->>'column_name') || ' ' || (v_having_cond.value->>'operator') || ' ' || 'NULL' || ' AND ';
                END IF;
            END IF;
        END LOOP;
        -- Remove the trailing ' AND '
        IF v_having_clause <> '' THEN
            v_having_clause := LEFT(v_having_clause, LENGTH(v_having_clause) - 4);
        END IF;
    END IF;

    -- Initialize query string
    v_query_str := 'SELECT ' || v_select_clause || ' FROM ' || v_primary_table || v_join_clause || v_where_clause || v_group_by_clause || v_having_clause;

    -- Handle CTE if present
    IF json_input ? 'cte' THEN
        v_cte_clause := json_input->>'cte';
    END IF;
    IF v_cte_clause IS NOT NULL AND TRIM(v_cte_clause) <> '' THEN
        v_query_str := v_cte_clause || ' ' || v_query_str;
    END IF;

    -- Total records count
    IF v_cte_clause IS NOT NULL AND TRIM(v_cte_clause) <> '' THEN
        v_count_query_str := v_cte_clause || ' SELECT COUNT(*) FROM (SELECT 1 FROM ' || v_primary_table || v_join_clause || v_where_clause || v_group_by_clause || v_having_clause || ') AS subquery';
    ELSE
        v_count_query_str := 'SELECT COUNT(*) FROM (SELECT 1 FROM ' || v_primary_table || v_join_clause || v_where_clause || v_group_by_clause || v_having_clause || ') AS subquery';
    END IF;

	-- Only replace if grid_params is not null
    IF v_grid_params IS NOT NULL THEN
		v_count_query_str := replace_placeholder_variables(v_count_query_str, v_grid_params);
	END IF;

	EXECUTE v_count_query_str INTO v_total_records_count;

    -- Order by and limit
    IF v_sort_columns IS NOT NULL THEN
        FOR v_sort_col IN SELECT * FROM jsonb_array_elements(v_sort_columns) LOOP
            -- Security check for column name
            IF (v_sort_col.value->>0) !~ '^[a-zA-Z0-9_\. ()'',=]+$' THEN
                RAISE EXCEPTION 'Invalid characters in column name in sort: %', (v_sort_col.value->>0);
            END IF;
            IF v_order_by_clause = '' THEN
                v_order_by_clause := v_order_by_clause || ' ORDER BY ';
            ELSE
                v_order_by_clause := v_order_by_clause || ', ';
            END IF;
            v_order_by_clause := v_order_by_clause || (v_sort_col.value->>0) || ' ' || (v_sort_col.value->>1);
            v_query_str := v_query_str || v_order_by_clause;
        END LOOP;
    END IF;

    -- Apply LIMIT and OFFSET
    IF v_limit_range > 0 THEN
        v_query_str := v_query_str || ' LIMIT ' || v_limit_range;
    END IF;

    IF v_record_offset > 0 THEN
        v_query_str := v_query_str || ' OFFSET ' || v_record_offset;
    END IF;

    -- Final JSON aggregation query
    --v_query_str := 'SELECT COALESCE(jsonb_agg(t), ''[]'') FROM (' || v_query_str || ') t';
    IF v_cte_clause IS NOT NULL AND TRIM(v_cte_clause) <> '' THEN
        v_query_str := v_cte_clause || ' ' || v_query_str;
    END IF;

	
		-- Only replace if grid_params is not null
    IF v_grid_params IS NOT NULL THEN
		-- Replace all variables with its respective values here (For example $gparam_1,$gparam_2..etc.)
		v_query_str := replace_placeholder_variables(v_query_str, v_grid_params);
	END IF;

	-- Store the executed query if print_query is true
    IF v_print_query THEN
        v_executed_query := v_query_str;
    END IF;

    -- Get the records
    v_records := '[]'::jsonb;
    FOR rec IN EXECUTE v_query_str LOOP
        v_records := v_records || jsonb_build_array(to_jsonb(rec));
    END LOOP;

    -- Set the output result
    IF v_print_query THEN
        result := jsonb_build_object(
            'total_records_count', v_total_records_count,
            'records', v_records,
            'query_executed', v_executed_query,
            'error_type', '',
            'error_message', '',
            'execution_status', TRUE
        );
    ELSE
        result := jsonb_build_object(
            'total_records_count', v_total_records_count,
            'records', v_records,
            'query_executed', v_executed_query,
            'error_type', '',
            'error_message', '',
            'execution_status', TRUE
        );
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        IF v_print_query THEN
            result := jsonb_build_object(
                'total_records_count', 0,
                'records', jsonb '[]',
                'query_executed', v_executed_query,
                'error_type', SQLSTATE,
                'error_message', SQLERRM,
                'execution_status', FALSE
            );
        ELSE
            result := jsonb_build_object(
                'total_records_count', 0,
                'records', jsonb '[]',
                'error_type', SQLSTATE,
                'error_message', SQLERRM,
                'execution_status', FALSE
            );
        END IF;
END;
$BODY$;

=======================================================================================================

============= ***** 3. Title: Get Key-Related Language Contents for a Specific Company ******=============

CREATE OR REPLACE PROCEDURE get_key_language_contents(
    IN json_input JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_company_id INT;
BEGIN
    v_company_id := COALESCE((json_input->>'company_id')::INT, 0);

    -- Initialize the result parameter as an empty JSON object if it's not provided
    IF result IS NULL THEN
        result := '[]'::jsonb;
    END IF;

    -- Aggregate the data into the result JSONB parameter
    result := (
        SELECT jsonb_agg(
            jsonb_build_object(
                'key1', ek.key1,
                'language_content', (
                    SELECT jsonb_agg(
                        jsonb_build_object(
                            'language_id', l.id,
                            'language_name', l.name,
                            'name_in_english', l.name_in_english,
                            'description', COALESCE(sekv.values, NULL)
                        )
                          ORDER BY sekv.key_content ASC
                    )
                    FROM languages l
                    LEFT JOIN language_contents sekv ON ek.key1 = sekv.key_content AND sekv.language_id = l.id AND sekv.status_id = 1
                    WHERE
                        l.company_id = v_company_id
                        AND l.status_id = 1
                )
            )
                 ORDER BY ek.key1 ASC

        )
        FROM (
            SELECT display_name AS key1 FROM master_entity_line_items WHERE company_id = v_company_id AND status_id = 1
            UNION
            SELECT key_content AS key1 FROM language_contents WHERE company_id = v_company_id AND status_id = 1
        ) AS ek
        WHERE ek.key1 IS NOT NULL
    );

END;
$$;

=======================================================================================================

==========****** 4. Title: Check for Related Records in Associated Tables for an Entity  *****=============================

CREATE OR REPLACE PROCEDURE check_for_related_records(
    IN json_input JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql AS $$
DECLARE
    entity_name_var TEXT;
    record_id INT;
    associated_tables JSONB;
    table_entry JSONB;
    table_name TEXT;
    where_clause TEXT;
    count_result INT;
BEGIN
    -- Extract values from JSON input
    entity_name_var := json_input->>'entity_name';
    record_id := (json_input->>'record_id')::INT;

    -- Initialize the result as an empty JSON object if it's not provided
    IF result IS NULL THEN
        result := '{}'::JSONB;
    END IF;

    -- Get associated tables from master_entities
    SELECT m.associated_tables
    INTO associated_tables
    FROM master_entities m
    WHERE m.entity_name = entity_name_var;

    -- Ensure associated_tables is a valid JSON array
    IF associated_tables IS NULL OR jsonb_typeof(associated_tables) <> 'array' THEN
        RAISE NOTICE 'No related records found or associated_tables is invalid.';
        RETURN;
    END IF;

    -- Loop through each entry in the associated_tables array
    FOR table_entry IN SELECT * FROM jsonb_array_elements(associated_tables) LOOP
        table_name := table_entry->>'table';
        where_clause := table_entry->>'where_clause';

        -- Directly use the where_clause, assuming it contains the placeholder $1 for record_id
        RAISE NOTICE 'Checking table: %, Where Clause: %', table_name, where_clause;

        -- Dynamically execute the query with the where_clause and bind the record_id
        EXECUTE format('SELECT COUNT(*) FROM %I WHERE %s', table_name, where_clause)
        INTO count_result
        USING record_id;

        RAISE NOTICE 'Count result for table %: %', table_name, count_result;

        IF count_result > 0 THEN
            -- Add the associated table and count to the result JSONB object
            result := jsonb_set(
                result,
                ARRAY[table_name],
                to_jsonb(count_result),
                TRUE
            );
        END IF;
    END LOOP;

    -- Check if the result JSONB object is empty
    IF jsonb_typeof(result) = 'object' AND NOT EXISTS (SELECT 1 FROM jsonb_each(result)) THEN
        RAISE NOTICE 'No related records found.';
    ELSE
        RAISE NOTICE 'Related records: %', result::TEXT;
    END IF;
END;
$$;


============================================================================================================================

=========***** 5. Title: Retrieve Configuration Values for Provided Category IDs *********================================


-- Step 2: Create the procedure fresh
CREATE OR REPLACE PROCEDURE get_configurations_values_v1(
    IN request JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql AS $$
DECLARE
    rec RECORD;
    current_category_id TEXT;
    user_rec RECORD;
    config_map JSONB := '{}'::JSONB;
    categories JSONB;
    input_user_id INT;
    latest_updated TIMESTAMP := NULL;
    temp_updated TIMESTAMP;
BEGIN
    -- Initialize the result as an empty JSON object if it's not provided
    IF result IS NULL THEN
        result := '{}'::JSONB;
    END IF;

    -- Extract categories and user_id from the request JSONB
    categories := (request->>'categories')::JSONB;

    -- Safely extract user_id, handle null case
    IF request ? 'user_id' AND request->>'user_id' != '' AND request->>'user_id' != 'null' THEN
        BEGIN
            input_user_id := (request->>'user_id')::INT;
        EXCEPTION 
            WHEN others THEN
                input_user_id := NULL;
        END;
    ELSE
        input_user_id := NULL;
    END IF;

    -- Check if categories exists
    IF categories IS NULL THEN
        result := '{"error": "categories parameter is required"}'::JSONB;
        RETURN;
    END IF;

    -- Loop through each value in the categories object
    FOR current_category_id IN 
        SELECT jsonb_each_text.value 
        FROM jsonb_each_text(categories)
    LOOP
        -- 1. Fetch all default configs for this category
        FOR rec IN 
            SELECT ac.config_key, ac.config_value, ac.updated_at
            FROM app_configurations ac
            WHERE ac.category_id = current_category_id 
            ORDER BY ac.order_no ASC
        LOOP
            config_map := jsonb_set(
                config_map,
                ARRAY[rec.config_key],
                COALESCE(to_jsonb(rec.config_value), 'null'::JSONB),
                TRUE
            );
            
            -- Track latest updated_at
            IF rec.updated_at IS NOT NULL THEN
                IF latest_updated IS NULL OR rec.updated_at > latest_updated THEN
                    latest_updated := rec.updated_at;
                END IF;
            END IF;
        END LOOP;

        -- 2. If user_id is provided, fetch user configs and override/add
        IF input_user_id IS NOT NULL THEN
            FOR user_rec IN 
                SELECT auc.config_key, auc.config_value, auc.updated_at
                FROM app_user_configurations auc
                WHERE auc.category_id = current_category_id
                  AND auc.user_id = input_user_id
            LOOP
                config_map := jsonb_set(
                    config_map,
                    ARRAY[user_rec.config_key],
                    COALESCE(to_jsonb(user_rec.config_value), 'null'::JSONB),
                    TRUE
                );
                
                -- Track latest updated_at
                IF user_rec.updated_at IS NOT NULL THEN
                    IF latest_updated IS NULL OR user_rec.updated_at > latest_updated THEN
                        latest_updated := user_rec.updated_at;
                    END IF;
                END IF;
            END LOOP;
        END IF;
    END LOOP;

    -- Final result
    result := jsonb_set(result, '{data}', config_map, TRUE);
    IF latest_updated IS NOT NULL THEN
        result := jsonb_set(result, '{latest_updated_at}', to_jsonb(latest_updated::TEXT), TRUE);
    END IF;
END;
$$;







=============================================================================================================================

============***** 6. Retrieve Menu Permissions Based on User or Role *************************========================

CREATE OR REPLACE PROCEDURE public.get_menu_permissions(
    IN input_params jsonb,
    INOUT result jsonb)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    v_user_id INT;
    v_menu_id INT;
    v_role_id INT;
    v_permission_type TEXT;
BEGIN
    -- Extract values from the input JSONB
    v_user_id := (input_params ->> 'user_id')::INT;
    v_menu_id := (input_params ->> 'menu_id')::INT;
    v_role_id := (input_params ->> 'role_id')::INT;
    v_permission_type := input_params ->> 'permission_type';

    -- Initialize the result as an empty JSONB array if it's not provided
    IF result IS NULL THEN
        result := '[]'::jsonb;
    END IF;

    -- Recursive query to build the hierarchical structure first
    WITH RECURSIVE RecursiveMenu AS (
        -- Base case: Select all top-level parent menu items (parent_id IS NULL) with the provided menu_id
        SELECT 
            m.id AS id,
            m.name AS parent_name,
            m.entity_id,
            m.menu_id,
            m.permission_id,
            m.link_type,
            m.menu_img,  -- Include menu_img here
            NULL::INTEGER AS parent_id_ref
        FROM menu_items m
        WHERE m.parent_id IS NULL AND m.menu_id = v_menu_id AND m.status_id = 1
        
        UNION ALL
        
        -- Recursive case: Join with the next level of child items
        SELECT 
            c.id AS id,
            c.name AS parent_name,
            c.entity_id,
            c.menu_id,
            c.permission_id,
            c.link_type,
            c.menu_img,  -- Include menu_img here
            rm.id AS parent_id_ref
        FROM menu_items c
        INNER JOIN RecursiveMenu rm ON c.parent_id = rm.id
        WHERE c.menu_id = v_menu_id AND c.status_id = 1
    ),
    
    -- Prepare a subquery to handle permissions based on userwise or rolewise
    AggregatedMenu AS (
        SELECT 
            p.id AS id,
            p.parent_id_ref AS parent_id,
            p.parent_name,
            p.entity_id,
            p.menu_img,  -- Include menu_img here
            json_build_object(
                'id', p.id,
                'parent_id', p.parent_id_ref,
                'parent_name', p.parent_name,
                'menu_img', p.menu_img  -- Include menu_img in the JSON object
            ) AS menu_item_json,
            CASE
                -- If the parent's entity_id is not null, aggregate both the parent and all children into a JSON array
                WHEN p.entity_id IS NOT NULL THEN (
                    SELECT json_agg(
                        json_build_object(
                            'id', child.id,
                            'name', child.parent_name,
                            'entity_id', child.entity_id,
                            'menu_img', child.menu_img,  -- Include menu_img here
                            'link_type', child.link_type,
                            'permission_id', child.permission_id,
                            'permission_name', perm.name,
                            'permission_value', CASE
                                WHEN v_permission_type = 'userwise' THEN
                                    CASE
                                        WHEN up.permission_id IS NOT NULL THEN 'true'
                                        ELSE 'false'
                                    END
                                WHEN v_permission_type = 'rolewise' THEN
                                    CASE
                                        WHEN rp.permission_id IS NOT NULL THEN 'true'
                                        ELSE 'false'
                                    END
                                ELSE 'false'
                            END,
                            -- Extra permissions (view permission)
                            'entity_permission_id', view_perm.id,
                            'entity_permission_name', view_perm.name,
                            'entity_permission_value', CASE
                                WHEN v_permission_type = 'userwise' THEN
                                    CASE
                                        WHEN up_view.permission_id IS NOT NULL THEN 'true'
                                        ELSE 'false'
                                    END
                                WHEN v_permission_type = 'rolewise' THEN
                                    CASE
                                        WHEN rp_view.permission_id IS NOT NULL THEN 'true'
                                        ELSE 'false'
                                    END
                                ELSE 'false'
                            END
                        )
                    )
                    FROM RecursiveMenu child
                    LEFT JOIN permissions perm ON child.permission_id = perm.id
                    -- Conditional JOIN based on permission_type
                    LEFT JOIN user_permissions up ON (v_permission_type = 'userwise' AND up.permission_id = child.permission_id AND up.user_id = v_user_id)
                    LEFT JOIN role_permissions rp ON (v_permission_type = 'rolewise' AND rp.permission_id = child.permission_id AND rp.role_id = v_role_id)
                    LEFT JOIN permissions view_perm ON view_perm.entity_id = child.entity_id AND view_perm.name = 'view'
                    LEFT JOIN user_permissions up_view ON (v_permission_type = 'userwise' AND up_view.permission_id = view_perm.id AND up_view.user_id = v_user_id)
                    LEFT JOIN role_permissions rp_view ON (v_permission_type = 'rolewise' AND rp_view.permission_id = view_perm.id AND rp_view.role_id = v_role_id)
                    WHERE (child.parent_id_ref = p.id OR child.id = p.id) AND (p.link_type != 2 AND p.link_type != 3)
                )
                ELSE NULL -- If parent's entity_id is null, permissions should be NULL
            END AS permissions
        FROM RecursiveMenu p where (p.link_type != 2 AND p.link_type != 3)
    )
    
    -- Final aggregation to collect the results into JSON format
    SELECT json_agg(
        json_build_object(
            'id', am.id,
            'parent_id', am.parent_id,
            'parent_name', am.parent_name,
            'menu_img', am.menu_img,  -- Include menu_img in the final result
            'permissions', am.permissions
        )
    ) INTO result
    FROM AggregatedMenu am;

    -- Raise a notice to show the result
    RAISE NOTICE 'Result: %', result::TEXT;
END;
$BODY$;

=================================================================================================================================

===========***** 7. Procedure for Changing the Ownership ***********=============================

ALTER PROCEDURE public.get_menu_permissions(jsonb, jsonb)
    OWNER TO postgres;


========================================================================================================

==========***** 8. Title: Procedure to Retrieve Attached policies for the Specified Entity and Company in JSONB Format *****=========

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$;

=======================================================================================================

===========****** Types ******======================

===========****** 1. Title: Include Result ******======================

CREATE TYPE IncludeResult AS
(
	select_clause text,
	join_clause text,
	alias_mapping jsonb
);

=======================================================================================================

====================*************** METHODS **********===================


========********* Import Template Users ***********==================================

ALTER TABLE roles
ADD CONSTRAINT unique_name_company_id UNIQUE (name, company_id);

ALTER TABLE departments
ADD CONSTRAINT unique_departmentcode_company_id UNIQUE (code, company_id);

ALTER TABLE designations
ADD CONSTRAINT unique_designationcode_company_id UNIQUE (code, company_id);

ALTER TABLE users
ADD CONSTRAINT unique_username_company_id UNIQUE (username, company_id);

ALTER TABLE user_roles
ADD CONSTRAINT unique_userid_roleid_companyid UNIQUE (user_id, role_id, company_id);


SELECT setval('scheduled_jobs_id_seq', (SELECT MAX(id) FROM scheduled_jobs));
