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$