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;
    table_name TEXT;
    join_type TEXT;
    join_condition TEXT;
    alias TEXT;
    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',
        'IS NULL', 'IS NOT NULL',
        'IS_EMPTY', 'IS_NOT_EMPTY',
        'IN', 'NOT IN',
        'LIKE', 'NOT LIKE', 'ILIKE', 'NOT ILIKE',
        'BETWEEN'
    );

BEGIN
    -- Fetch query_information
    SELECT query_information
    INTO query_information1
    FROM master_entities
    WHERE entity_name = entity_name1
      AND company_id = var_company_id;

    includes := query_information1->'includes';
    group_by := query_information1->'group_by';
    v_having_conditions := query_information1->'having_conditions';

    -- =========================
    -- JOIN CLAUSE
    -- =========================
    IF includes IS NOT NULL THEN
        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';

            local_join_clause := local_join_clause || ' '
                || join_type || ' JOIN ' || table_name
                || ' ON ' || join_condition;

            -- Nested includes
            IF include ? 'includes' THEN
                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;

                    IF nested_result.having_clause IS NOT NULL AND nested_result.having_clause <> '' THEN
                        v_having_clause := v_having_clause || ' ' || nested_result.having_clause;
                    END IF;
                END LOOP;
            END IF;
        END LOOP;
    END IF;

    -- =========================
    -- GROUP BY
    -- =========================
    IF group_by IS NOT NULL AND jsonb_array_length(group_by) > 0 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;

    -- =========================
    -- HAVING CLAUSE (FIXED)
    -- =========================
    IF v_having_conditions IS NOT NULL AND jsonb_array_length(v_having_conditions) > 0 THEN
        v_having_clause := ' HAVING ';

        FOR v_having_cond IN
            SELECT * FROM jsonb_array_elements(v_having_conditions)
        LOOP
            -- Column validation
            IF (v_having_cond->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',:|\\-]+$' THEN
                RAISE EXCEPTION 'Invalid column name in HAVING: %',
                    (v_having_cond->>'column_name');
            END IF;

            -- Operator validation
            IF NOT EXISTS (
                SELECT 1
                FROM jsonb_array_elements_text(v_valid_operators) op
                WHERE op = v_having_cond->>'operator'
            ) THEN
                RAISE EXCEPTION 'Invalid operator in HAVING: %',
                    (v_having_cond->>'operator');
            END IF;

            -- =========================
            -- OPERATOR HANDLING
            -- =========================

            IF (v_having_cond->>'operator') IN ('IN', '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(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 ';

            ELSIF (v_having_cond->>'operator') = 'IS NULL' THEN
                v_having_clause := v_having_clause
                    || (v_having_cond->>'column_name') || ' IS NULL AND ';

            ELSIF (v_having_cond->>'operator') = 'IS NOT NULL' THEN
                v_having_clause := v_having_clause
                    || (v_having_cond->>'column_name') || ' IS NOT NULL AND ';

            ELSIF (v_having_cond->>'operator') = 'IS_EMPTY' THEN
                v_having_clause := v_having_clause
                    || '(' || (v_having_cond->>'column_name')
                    || ' IS NULL OR '
                    || (v_having_cond->>'column_name') || ' = '''') AND ';

            ELSIF (v_having_cond->>'operator') = 'IS_NOT_EMPTY' THEN
                v_having_clause := v_having_clause
                    || '(' || (v_having_cond->>'column_name')
                    || ' IS NOT NULL AND '
                    || (v_having_cond->>'column_name') || ' <> '''') AND ';

            ELSIF (v_having_cond->>'operator') IN ('IS', 'IS NOT') THEN
                v_having_clause := v_having_clause
                    || (v_having_cond->>'column_name')
                    || ' ' || (v_having_cond->>'operator')
                    || ' NULL AND ';

            ELSE
                IF (v_having_cond->>'value') IS NOT NULL
                   AND TRIM(v_having_cond->>'value') <> '' THEN
                    v_having_clause := v_having_clause
                        || (v_having_cond->>'column_name')
                        || ' ' || (v_having_cond->>'operator')
                        || ' ' || quote_literal(v_having_cond->>'value')
                        || ' AND ';
                END IF;
            END IF;
        END LOOP;

        -- Remove trailing AND
        IF v_having_clause <> '' THEN
            v_having_clause := LEFT(v_having_clause, LENGTH(v_having_clause) - 5);
        END IF;
    END IF;

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