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$

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

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.width as column_width, 
	    mgl.order_no, is_searchable, is_grid_column, mgl.is_sortable, mgl.field_name, mgl.field_type_id
        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 
		ORDER BY mgl.order_no ASC
    LOOP
        select_columns := select_columns || jsonb_build_array(
			jsonb_build_array(
				field.field_name, field.display_name, field.column_width, field.order_no, field.is_searchable, 
				field.is_grid_column, field.is_sortable, field.field_name, field.field_type_id
			)
		);
    END LOOP;
    RETURN select_columns;
END;
$function$

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

CREATE OR REPLACE PROCEDURE public.get_grid_items(
	IN json_input jsonb,
	INOUT result jsonb)
LANGUAGE 'plpgsql'
AS $procedure$
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_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_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;
    
    -- 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 := '';

    -- 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_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_entity_name := json_input->>'entity_name';
    v_company_id := COALESCE((json_input->>'company_id')::INT, 0);
    v_print_query := COALESCE((json_input->>'print_query')::BOOLEAN, FALSE);
    v_group_by := json_input->'group_by';
    v_having_conditions := json_input->'having_conditions';

    -- 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->>3,
                'is_searchable', v_col->>4,
                'is_grid_column', v_col->>5,
                'is_sortable', v_col->>6,
                'field_value', 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));

        -- Construct the dynamic JSON field list
        IF v_json_fields <> '' THEN
            v_json_fields := v_json_fields || ', ';
        END IF;
        v_json_fields := v_json_fields || quote_literal(v_col->>1) || ', ' || (v_col->>1);
    END LOOP;

    -- Build the JOIN clauses and GROUP BY clause
    FOR v_include_result_row IN SELECT * FROM build_includes1(v_entity_name, '', '', v_company_id) LOOP
        v_join_clause := v_join_clause || ' ' || v_include_result_row.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;

    -- 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_search_all) LOOP
        -- Security check for column name and operator
        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 
                v_where_clause := v_where_clause || ' AND ' || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' ' || quote_literal((v_col_search->>'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_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
                    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 
                        v_or_group := v_or_group || (v_inner_col_search->>'column_name') || ' ' || (v_inner_col_search->>'operator') || ' ' || quote_literal((v_inner_col_search->>'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
                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 
                    v_or_clause := v_or_clause || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' ' || quote_literal((v_col_search->>'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
                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_entity_table || v_join_clause || v_where_clause || v_group_by_clause || v_having_clause;

    -- Total records count
    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;

    -- 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->>0) !~ '^[a-zA-Z0-9_\. ()'',=]+$' THEN
                RAISE EXCEPTION 'Invalid characters in column name in sort: %', (v_sort_col->>0);
            END IF;
            IF v_query_str NOT LIKE '%ORDER BY%' THEN
                v_query_str := v_query_str || ' ORDER BY ';
            ELSE
                v_query_str := v_query_str || ', ';
            END IF;
            v_query_str := v_query_str || (v_sort_col->>0) || ' ' || (v_sort_col->>1);
        END LOOP;
    END IF;
    v_query_str := v_query_str || ' LIMIT ' || v_record_limit || ' OFFSET ' || v_record_offset;

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

    -- Construct the JSON object manually to maintain the column order
    v_query_str := 'SELECT jsonb_agg(jsonb_build_object(' || v_json_fields || ')) FROM (' || v_query_str || ') t';

    -- Get the records
    EXECUTE v_query_str INTO v_records;

    -- Set the output result
    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
    );
EXCEPTION
    WHEN OTHERS THEN
        -- Handle exceptions and set the result with the error message
        result := jsonb_build_object(
            'total_records_count', 0,
            'records', jsonb '[]',
            'query_executed', v_executed_query,
            'error_type', SQLSTATE,
            'error_message', SQLERRM,
            'execution_status', FALSE
        );
END;
$procedure$

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

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

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_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_query_str TEXT;
    v_total_records_count INT;
    v_records JSONB;
    v_executed_query 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 := '';

    -- 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_record_limit := COALESCE((json_input->>'limit_range')::INT, 10);
    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);

    -- 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
            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'));
            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
                    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
                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;

    -- Total records count
    EXECUTE 'SELECT COUNT(*) FROM (SELECT 1 FROM ' || v_primary_table || v_join_clause || v_where_clause || v_group_by_clause || v_having_clause || ') AS subquery' 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;

    IF v_record_limit IS NOT NULL THEN
        v_query_str := v_query_str || ' LIMIT ' || v_record_limit || ' OFFSET ' || v_record_offset;
    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
    EXECUTE 'SELECT COALESCE(jsonb_agg(t), ''[]'') FROM (' || v_query_str || ') t' INTO v_records;

    -- Set the output result
    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
    );
EXCEPTION
    WHEN OTHERS THEN
        -- Handle exceptions and set the result with the error message
        result := jsonb_build_object(
            'total_records_count', 0,
            'records', jsonb '[]',
            'query_executed', v_executed_query,
            'error_type', SQLSTATE,
            'error_message', SQLERRM,
            'execution_status', FALSE
        );
END;
$procedure$

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

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$

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

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

    -- Check if associated tables exist for the entity
    IF associated_tables IS NULL OR associated_tables = '[]' THEN
        RAISE NOTICE 'No related records found.';
        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;
$$;

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


CREATE OR REPLACE PROCEDURE get_configurations_values(
    IN category_ids JSONB, -- JSONB object containing category IDs with numerical keys
    INOUT result JSONB     -- Output JSONB object
)
LANGUAGE plpgsql AS $$
DECLARE
    rec RECORD;
    current_category_id TEXT;
BEGIN
    -- Initialize the result as an empty JSON object if it's not provided
    IF result IS NULL THEN
        result := '{}'::JSONB;
    END IF;

    -- Loop through each key-value pair in the category_ids object
    FOR current_category_id IN SELECT value::TEXT 
                                FROM jsonb_each_text(category_ids)
    LOOP
        -- Debug: Output the current category ID
        RAISE NOTICE 'Current category ID: %', current_category_id;

        -- Loop through records matching the criteria
        FOR rec IN SELECT config_key, config_value 
                    FROM app_configurations 
                    WHERE category_id = current_category_id 
                    ORDER BY order_no ASC
        LOOP
            -- Debug: Output the config_key and config_value
            RAISE NOTICE 'Found config_key: %, config_value: %', rec.config_key, rec.config_value;

            -- Add each record to the result JSONB object
            result := jsonb_set(
                result,
                ARRAY[rec.config_key],
                COALESCE(to_jsonb(rec.config_value), 'null'::JSONB),  -- Handle NULL values
                TRUE
            );
        END LOOP;

        -- Debug: Check if any records were found for the current category ID
        IF NOT FOUND THEN
            RAISE NOTICE 'No records found for category ID: %', current_category_id;
        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 configurations found for the provided category_ids.';
    ELSE
        RAISE NOTICE 'Configurations retrieved: %', result::TEXT;
    END IF;
END;
$$;


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

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$;
ALTER PROCEDURE public.get_menu_permissions(jsonb, jsonb)
    OWNER TO postgres;


=============================================================================================================================
CREATE UNIQUE INDEX unique_item_uom ON item_uom(item_id, uom_id);

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

CREATE OR REPLACE PROCEDURE public.update_and_insert_stock_take(
    IN json_input jsonb,
    INOUT result jsonb)
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock_take_id INTEGER;  
    v_company_id INTEGER;      
    v_created_by INTEGER;      
    v_highest_version INTEGER;  
BEGIN
    -- Extract the input values from the JSON
    v_stock_take_id := (json_input ->> 'stock_take_id')::INTEGER;
    v_company_id := (json_input ->> 'company_id')::INTEGER;
    v_created_by := (json_input ->> 'created_by')::INTEGER;

    RAISE NOTICE 'Received stock_take_id: %, company_id: %, created_by: %', v_stock_take_id, v_company_id, v_created_by;

    -- Initialize the result
    result := jsonb_build_object('status', 'success', 'details', '[]');

    -- Fetch the highest version number for the given stock_take_id
    SELECT MAX(version_number) INTO v_highest_version
    FROM stock_audit_entry_header
    WHERE stock_take_id = v_stock_take_id;

    RAISE NOTICE 'Highest version number found: %', v_highest_version;

    IF v_highest_version IS NOT NULL THEN
        -- Update the record with the highest version number
        UPDATE stock_audit_entry_header
        SET status_id = 2,
            updated_at = CURRENT_TIMESTAMP,
            updated_by = v_created_by
        WHERE stock_take_id = v_stock_take_id
        AND version_number = v_highest_version;

        RAISE NOTICE 'Record updated for stock_take_id: %', v_stock_take_id;

        -- Insert new record with incremented version number
        INSERT INTO stock_audit_entry_header (stock_take_id, version_number, status_id, company_id, created_by)
        VALUES (
            v_stock_take_id,
            v_highest_version + 1,
            1, 
            v_company_id,
            v_created_by
        );

        RAISE NOTICE 'New entry inserted for stock_take_id: %', v_stock_take_id;

        -- Add result details for the operation
        result := jsonb_set(
            result,
            '{details}',
            (result->'details') || jsonb_build_object('stock_take_id', v_stock_take_id, 'status', 'processed')
        );

    ELSE
        result := jsonb_build_object('status', 'failure', 'error_message', 'stock_take_id not found');
    END IF;

    RAISE NOTICE 'Result: %', result;
    RETURN;
END;
$$;
====================================================================================================================

CREATE UNIQUE INDEX unique_stock_audit_entry_line_items_companyid_and_uuid ON stock_audit_entry_line_items(company_id, uuid);

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

CREATE UNIQUE INDEX unique_onhand_stocks_stocktakeid_and_itemid_and_uomid ON onhand_stocks(stock_take_id, item_id, uom_id);

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

CREATE OR REPLACE PROCEDURE consolidate_stock_take(
    IN json_input JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql
AS $$
DECLARE
    stock_record JSONB;
    rec_company_id INTEGER;
    rec_item_id INTEGER;
    rec_uom_id INTEGER;
    rec_item_status_id INTEGER;
    rec_onhand_quantity INTEGER;
    rec_identified_quantity INTEGER;
    rec_is_audit_completed BOOLEAN;
    rec_version_number INTEGER;
    input_type TEXT;
    input_stock_take_id INTEGER;
    input_user_id INTEGER;
    input_stock_take_status_id INTEGER;
    input_company_id INTEGER;
BEGIN
    -- Initialize result if it is null
    IF result IS NULL THEN
        result := jsonb_build_object('status', 'success', 'details', '[]');
    END IF;

    -- Extract the "type" and other input values from the input JSON
    input_type := json_input ->> 'type';
    input_stock_take_id := (json_input ->> 'stock_take_id')::INTEGER;
    input_user_id := (json_input ->> 'user_id')::INTEGER;
    input_company_id := (json_input ->> 'company_id')::INTEGER;
    input_stock_take_status_id := (json_input ->> 'stock_take_status_id')::INTEGER;
    
    -- Update stock_take status if the type is "stock_audit_close"
    IF input_type = 'stock_audit_close' THEN
        UPDATE stock_take
        SET stock_take_status_id = input_stock_take_status_id,
            updated_by = input_user_id,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = input_stock_take_id AND company_id = input_company_id;
    END IF;

    -- Process each record in the "records" array
    FOR stock_record IN SELECT * FROM jsonb_array_elements(json_input -> 'records')
    LOOP
        -- Extract values from each JSON object
        rec_company_id := (stock_record ->> 'company_id')::INTEGER;
        rec_item_id := (stock_record ->> 'item_id')::INTEGER;
        rec_uom_id := (stock_record ->> 'uom_id')::INTEGER;
        rec_item_status_id := (stock_record ->> 'item_status_id')::INTEGER;
        rec_onhand_quantity := (stock_record ->> 'onhand_quantity')::INTEGER;
        rec_identified_quantity := (stock_record ->> 'identified_quantity')::INTEGER;
        rec_is_audit_completed := (stock_record ->> 'is_audit_completed')::BOOLEAN;
        rec_version_number := (stock_record ->> 'version_number')::INTEGER;

        -- Insert or update based on conflict
        INSERT INTO onhand_stocks (
            company_id, stock_take_id, item_id, uom_id, item_status_id, 
            onhand_quantity, identified_quantity, is_audit_completed, version_number, created_by
        )
        VALUES (
            rec_company_id, input_stock_take_id, rec_item_id, rec_uom_id, rec_item_status_id,
            rec_onhand_quantity, rec_identified_quantity, rec_is_audit_completed, rec_version_number, input_user_id
        )
        ON CONFLICT (company_id, stock_take_id, item_id, uom_id, item_status_id)
        DO UPDATE SET
            identified_quantity = EXCLUDED.identified_quantity,
            version_number = EXCLUDED.version_number,
            is_audit_completed = EXCLUDED.is_audit_completed
        WHERE onhand_stocks.is_audit_completed = false;

        -- Append details to the result
        result := jsonb_set(
            result,
            '{details}',
            (result->'details') || jsonb_build_object('company_id', rec_company_id, 'item_id', rec_item_id, 'status', 'processed')
        );
    END LOOP;

    -- Completion message
    RAISE NOTICE 'Procedure executed successfully.';

EXCEPTION
    -- Handle errors and update the result with error information
    WHEN OTHERS THEN
        result := jsonb_build_object('status', 'failure', 'error_message', SQLERRM);
        RAISE NOTICE 'An error occurred: %', SQLERRM;
END;
$$;

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

CREATE OR REPLACE PROCEDURE update_and_insert_stock_take(
    IN stock_take_data JSON
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock_take_id INTEGER;
    v_company_id INTEGER;
    v_created_by INTEGER;    
    highest_version INTEGER;
    result JSONB;  -- Local variable to hold the result
BEGIN
    -- Extract stock_take_id, company_id, and created_by from the JSON input
    v_stock_take_id := (stock_take_data ->> 'stock_take_id')::INTEGER;
    v_company_id := (stock_take_data ->> 'company_id')::INTEGER;
    v_created_by := (stock_take_data ->> 'created_by')::INTEGER;

    -- Initialize the result as an empty JSON object
    result := '{}'::JSONB;

    -- Step 1: Find the highest version_number for the given stock_take_id
    SELECT MAX(version_number) INTO highest_version
    FROM stock_audit_entry_header
    WHERE stock_take_id = v_stock_take_id;

    -- Step 2: Check if stock_take_id exists and if the highest version is found
    IF highest_version IS NOT NULL THEN
        -- Step 3: Update the status of the highest version to 2
        UPDATE stock_audit_entry_header
        SET status_id = 2
        WHERE stock_take_id = v_stock_take_id
        AND version_number = highest_version;

        -- Step 4: Insert a new entry with version_number = highest_version + 1, status = 1
        INSERT INTO stock_audit_entry_header (
            stock_take_id, 
            version_number, 
            status_id, 
            company_id, 
            created_by 
        )
        VALUES (
            v_stock_take_id,
            highest_version + 1,
            1,                -- New status
            v_company_id,
            v_created_by 
        );

        -- Prepare the output JSON with the new entry details
        result := jsonb_build_object(
            'stock_take_id', v_stock_take_id,
            'new_version', highest_version + 1,
            'status', 1
        );
    ELSE
        RAISE NOTICE 'stock_take_id % not found in stock_audit_entry_header', v_stock_take_id;
        result := jsonb_build_object('error', 'stock_take_id not found');
    END IF;

    -- Output result as a notice
    RAISE NOTICE 'Result: %', result::TEXT;
END;
$$;

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

ALTER TABLE onhand_stocks
ADD CONSTRAINT unique_onhand_stock UNIQUE (company_id, stock_take_id, item_id, uom_id, item_status_id);
