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 PROCEDURE public.update_record(IN json_input jsonb, INOUT result jsonb)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_table_name TEXT;
    v_update_values JSONB;
    v_where_all JSONB;
    v_where_any JSONB;
    v_print_query BOOLEAN;
    v_set_clause TEXT := '';
    v_where_clause_str TEXT := ' WHERE ';
    v_query_str TEXT;
    v_executed_query TEXT := '';
    v_execution_status BOOLEAN := FALSE;
    v_company_id INT;
    v_col_name TEXT;
    v_col_value TEXT;
    v_updated_record RECORD;
    v_updated_ids JSONB := '[]';
    v_updated_by INT;
    v_updated_at TIMESTAMPTZ;
    v_deleted_by INT;
    v_deleted_at TIMESTAMPTZ;
    v_old_record RECORD;
    v_old_value TEXT;
    v_new_value TEXT;
    v_old_record_json JSONB;
    v_audit_log_id INT;

    -- 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');
    -- Loop variables
    v_col_search RECORD;
    v_inner_col_search RECORD;
BEGIN
    -- Create a temporary table to store updated IDs
    CREATE TEMP TABLE temp_updated_ids (id INT PRIMARY KEY);

    -- Parse the JSON input
    v_table_name := json_input->>'table_name';
    v_update_values := json_input->'update_values';
    v_where_all := json_input->'where_all';
    v_where_any := json_input->'where_any';
    v_company_id := COALESCE((json_input->>'company_id')::INT, 0);
    v_print_query := COALESCE((json_input->>'print_query')::BOOLEAN, FALSE);

    -- Optional fields
    v_updated_by := COALESCE((json_input->>'updated_by')::INT, NULL);
    v_updated_at := COALESCE((json_input->>'updated_at')::TIMESTAMPTZ, NULL);
    v_deleted_by := COALESCE((json_input->>'deleted_by')::INT, NULL);
    v_deleted_at := COALESCE((json_input->>'deleted_at')::TIMESTAMPTZ, NULL);

    -- Validate the required fields
    IF v_table_name IS NULL OR v_update_values IS NULL OR (v_where_all IS NULL AND v_where_any IS NULL) OR v_company_id IS NULL THEN
        RAISE EXCEPTION 'Invalid input: table_name, update_values, where_all/where_any, and company_id are required';
    END IF;

    -- Insert a single record into audit_logs
    INSERT INTO public.audit_logs (action_type, entity_name, user_id, company_id, created_at)
    VALUES (1, v_table_name, v_updated_by, v_company_id, CURRENT_TIMESTAMP)
    RETURNING id INTO v_audit_log_id;

    -- Build the WHERE clause
    v_where_clause_str := v_where_clause_str || format('%I.company_id = %L', v_table_name, v_company_id);

    -- Handle the "where_all" conditions with AND
    FOR i IN 0 .. jsonb_array_length(v_where_all) - 1 LOOP
        -- Security check for column name and operator
        IF (v_where_all->i->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',]+$' THEN
            RAISE EXCEPTION 'Invalid characters in column name: %', (v_where_all->i->>'column_name');
        END IF;
        IF NOT v_valid_operators ? (v_where_all->i->>'operator') THEN
            RAISE EXCEPTION 'Invalid operator: %', (v_where_all->i->>'operator');
        END IF;
        -- Construct the condition
        IF (v_where_all->i->>'operator') = 'IN' OR (v_where_all->i->>'operator') = 'NOT IN' THEN
            v_where_clause_str := v_where_clause_str || format(' AND %I %s (%s)',
                (v_where_all->i->>'column_name'),
                (v_where_all->i->>'operator'),
                array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_where_all->i->'value') x), ', ')
            );
        ELSIF (v_where_all->i->>'operator') = 'BETWEEN' THEN
            v_where_clause_str := v_where_clause_str || format(' AND %I BETWEEN %L AND %L',
                (v_where_all->i->>'column_name'),
                (v_where_all->i->'value')->>0,
                (v_where_all->i->'value')->>1
            );
        ELSE
            IF (v_where_all->i->>'value') IS NOT NULL THEN 
                v_where_clause_str := v_where_clause_str || format(' AND %I %s %L',
                    (v_where_all->i->>'column_name'),
                    (v_where_all->i->>'operator'),
                    (v_where_all->i->>'value')
                );
            ELSIF (v_where_all->i->>'operator' = 'IS' OR (v_where_all->i->>'operator') = 'IS NOT') THEN
                v_where_clause_str := v_where_clause_str || format(' AND %I %s NULL',
                    (v_where_all->i->>'column_name'),
                    (v_where_all->i->>'operator')
                );
            END IF;
        END IF;
    END LOOP;

    -- Handle the "where_any" conditions with nested OR
    FOR v_col_search IN SELECT * FROM jsonb_array_elements(v_where_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_str := v_where_clause_str || ' AND (' || v_or_clause || ')';
    END IF;

    -- Fetch the old records before updating
    FOR v_old_record IN EXECUTE format('SELECT * FROM %I %s', v_table_name, v_where_clause_str) LOOP
        -- Convert the old record to JSONB for dynamic access
        v_old_record_json := to_jsonb(v_old_record);

        -- Build the SET clause for the update query
        v_set_clause := '';
        FOR v_col_name, v_col_value IN SELECT * FROM jsonb_each_text(v_update_values) LOOP
            v_old_value := COALESCE(v_old_record_json->>v_col_name, '');
            v_new_value := COALESCE(v_col_value, '');

            -- Compare old and new values
            IF v_old_value IS DISTINCT FROM v_new_value THEN
                -- Insert into audit_log_line_items
                INSERT INTO public.audit_log_line_items (
                    audit_log_id, entity_id, field_name, old_value, new_value, company_id, created_at
                ) VALUES (
                    v_audit_log_id, v_old_record.id, v_col_name, v_old_value, v_new_value, v_company_id, CURRENT_TIMESTAMP
                );

                -- Add to SET clause
                v_set_clause := v_set_clause || format('%I = %L, ', v_col_name, v_col_value);
            END IF;
        END LOOP;

        -- Add updated_by, updated_at, deleted_by, and deleted_at to the SET clause if provided
        IF v_updated_by IS NOT NULL THEN
            v_set_clause := v_set_clause || format('updated_by = %L, ', v_updated_by);
        END IF;
        IF v_updated_at IS NOT NULL THEN
            v_set_clause := v_set_clause || format('updated_at = %L, ', v_updated_at);
        END IF;
        IF v_deleted_by IS NOT NULL THEN
            v_set_clause := v_set_clause || format('deleted_by = %L, ', v_deleted_by);
        END IF;
        IF v_deleted_at IS NOT NULL THEN
            v_set_clause := v_set_clause || format('deleted_at = %L, ', v_deleted_at);
        END IF;

        -- Remove the trailing comma and space
        v_set_clause := left(v_set_clause, length(v_set_clause) - 2);

        -- Build the update query
        v_query_str := format('UPDATE %I SET %s %s RETURNING *', v_table_name, v_set_clause, v_where_clause_str);

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

        -- Execute the update query and fetch updated records
        FOR v_updated_record IN EXECUTE v_query_str LOOP
            BEGIN
                INSERT INTO temp_updated_ids (id) VALUES (v_updated_record.id);
            EXCEPTION WHEN unique_violation THEN
                -- Ignore duplicate inserts
            END;
        END LOOP;

    END LOOP;

    -- Collect distinct updated IDs
    SELECT jsonb_agg(id) INTO v_updated_ids FROM temp_updated_ids;

    -- Drop the temporary table
    DROP TABLE temp_updated_ids;

    -- Set execution status to true if query executed successfully
    v_execution_status := TRUE;

    -- Set the output result
    result := jsonb_build_object(
        'query_executed', v_executed_query,
        'updated_ids', v_updated_ids,
        'error_type', '',
        'error_message', '',
        'execution_status', v_execution_status
    );
EXCEPTION
    WHEN OTHERS THEN
        -- Handle exceptions and set the result with the error message
        result := jsonb_build_object(
            'query_executed', v_executed_query,
            'updated_ids', '[]'::jsonb,
            'error_type', SQLSTATE,
            'error_message', SQLERRM,
            'execution_status', FALSE
        );
END;
$procedure$

CREATE OR REPLACE PROCEDURE public.create_record(IN json_input jsonb, INOUT result jsonb)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_table_name TEXT;
    v_insert_values JSONB;
    v_print_query BOOLEAN;
    v_columns TEXT := '';
    v_values TEXT := '';
    v_query_str TEXT;
    v_executed_query TEXT := '';
    v_execution_status BOOLEAN := FALSE;
    v_company_id INT;
    v_created_by INT;
    v_updated_by INT;
    v_updated_at TIMESTAMPTZ;
    v_col_name TEXT;
    v_col_value TEXT;
    v_is_array BOOLEAN;
    v_inserted_count INT := 0;
    v_inserted_records JSONB := '[]';
    v_record JSONB;
BEGIN
    -- Parse the JSON input
    v_table_name := json_input->>'table_name';
    v_insert_values := json_input->'insert_values';
    v_company_id := (json_input->>'company_id')::INT;
    v_print_query := COALESCE((json_input->>'print_query')::BOOLEAN, FALSE);

    -- Optional fields
    v_created_by := COALESCE((json_input->>'created_by')::INT, NULL);
    v_updated_by := COALESCE((json_input->>'updated_by')::INT, NULL);
    v_updated_at := COALESCE((json_input->>'updated_at')::TIMESTAMPTZ, NULL);

    -- Validate the required fields
    IF v_table_name IS NULL OR v_insert_values IS NULL OR v_company_id IS NULL THEN
        RAISE EXCEPTION 'Invalid input: table_name, insert_values, and company_id are required';
    END IF;

    -- Check if insert_values is an array or not
    v_is_array := jsonb_typeof(v_insert_values) = 'array';

    -- Build the columns and values for the insert query
    IF v_is_array THEN
        -- For array of insert values
        FOR i IN 0 .. jsonb_array_length(v_insert_values) - 1 LOOP
            v_columns := '';
            v_values := '';
            v_record := v_insert_values->i;

            FOR v_col_name, v_col_value IN SELECT * FROM jsonb_each_text(v_record) LOOP
                v_columns := v_columns || format('%I, ', v_col_name);
                v_values := v_values || format('%L, ', v_col_value);
            END LOOP;

            -- Add company_id to the columns and values
            v_columns := v_columns || 'company_id';
            v_values := v_values || quote_literal(v_company_id);

            -- Update created_by, updated_by, updated_at fields if provided
            IF v_created_by IS NOT NULL THEN
                v_columns := v_columns || ', created_by';
                v_values := v_values || ', ' || quote_literal(v_created_by);
            END IF;
            IF v_updated_by IS NOT NULL THEN
                v_columns := v_columns || ', updated_by';
                v_values := v_values || ', ' || quote_literal(v_updated_by);
            END IF;
            IF v_updated_at IS NOT NULL THEN
                v_columns := v_columns || ', updated_at';
                v_values := v_values || ', ' || quote_literal(v_updated_at);
            END IF;

            -- Initialize query string
            v_query_str := format('INSERT INTO %I (%s) VALUES (%s) RETURNING *', v_table_name, v_columns, v_values);

            -- Execute the insert query
            EXECUTE v_query_str INTO v_record;
            v_inserted_records := v_inserted_records || jsonb_build_array(v_record);
            v_inserted_count := v_inserted_count + 1;
        END LOOP;
    ELSE
        -- For single insert value
        v_columns := '';
        v_values := '';
        FOR v_col_name, v_col_value IN SELECT * FROM jsonb_each_text(v_insert_values) LOOP
            v_columns := v_columns || format('%I, ', v_col_name);
            v_values := v_values || format('%L, ', v_col_value);
        END LOOP;

        -- Add company_id to the columns and values
        v_columns := v_columns || 'company_id';
        v_values := v_values || quote_literal(v_company_id);

        -- Update created_by, updated_by, updated_at fields if provided
        IF v_created_by IS NOT NULL THEN
            v_columns := v_columns || ', created_by';
            v_values := v_values || ', ' || quote_literal(v_created_by);
        END IF;
        IF v_updated_by IS NOT NULL THEN
            v_columns := v_columns || ', updated_by';
            v_values := v_values || ', ' || quote_literal(v_updated_by);
        END IF;
        IF v_updated_at IS NOT NULL THEN
            v_columns := v_columns || ', updated_at';
            v_values := v_values || ', ' || quote_literal(v_updated_at);
        END IF;

        -- Initialize query string
        v_query_str := format('INSERT INTO %I (%s) VALUES (%s) RETURNING *', v_table_name, v_columns, v_values);

        -- Execute the insert query
        EXECUTE v_query_str INTO v_record;
        v_inserted_records := jsonb_build_array(v_record);
        v_inserted_count := 1;
    END IF;

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

    -- Set execution status to true if query executed successfully
    v_execution_status := TRUE;

    -- Set the output result
    result := jsonb_build_object(
        'query_executed', v_executed_query,
        'inserted_count', v_inserted_count,
        'inserted_records', v_inserted_records,
        'error_type', '',
        'error_message', '',
        'execution_status', v_execution_status
    );
EXCEPTION
    WHEN OTHERS THEN
        -- Handle exceptions and set the result with the error message
        result := jsonb_build_object(
            'query_executed', v_executed_query,
            'inserted_count', v_inserted_count,
            'inserted_records', v_inserted_records,
            'error_type', SQLSTATE,
            'error_message', SQLERRM,
            'execution_status', v_execution_status
        );
END;
$procedure$

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=======================================================================================================
After TRUNCATE tables SEQUENCE start from 1
 
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(r.tablename) || ' CASCADE;';
    END LOOP;
 
    FOR r IN (SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public') LOOP
        EXECUTE 'ALTER SEQUENCE ' || quote_ident(r.sequence_name) || ' RESTART WITH 1;';
    END LOOP;
END $$;

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