CREATE OR REPLACE PROCEDURE public.get_grid_items(
    IN json_input jsonb,
    INOUT result jsonb)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    v_record_offset INT;
    v_record_limit INT;
    v_sort_columns JSONB;
    v_search_all JSONB;
    v_search_any JSONB;
    v_select_columns JSONB;
    v_grid_params JSONB;
    v_entity_table TEXT;
    v_print_query BOOLEAN;
    v_entity_name TEXT;
    v_select_clause TEXT := '';
    v_join_clause TEXT := '';
    v_where_clause TEXT := ' WHERE ';
    v_group_by_clause TEXT := '';
    v_having_clause TEXT := '';
    v_query_str TEXT;
    v_count_query_str TEXT;
    v_total_records_count INT;
    v_records JSONB;
    v_headers JSONB := '[]';
    v_entities JSONB := '{}'::jsonb;
    v_header_entity_id TEXT;
    v_footer_entity_id TEXT;
    v_executed_query TEXT := '';
    v_company_id INT;
    v_group_by JSONB;
    v_having_conditions JSONB;
    v_having_any_conditions JSONB;

    -- Variables for iteration
    v_col JSONB;
    v_col_search JSONB;
    v_sort_col JSONB;
    v_inner_col_search JSONB;
    v_group_col TEXT;
    v_having_cond RECORD;

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

    -- For dynamic JSON construction
    v_json_fields TEXT := '';
    v_json_object TEXT;
    v_header_object TEXT;

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

    v_cte_clause TEXT := '';

    cunique TEXT;

    -- ✅ Security: Define valid operators (UPDATED: added IS_EMPTY / IS_NOT_EMPTY)
    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'
    );

    -- Sorting Variables
    v_sort_condition TEXT DEFAULT '';

    v_query_info_search JSONB;
    v_combined_search_all JSONB;

    v_query_info_search_any JSONB;
    v_combined_search_any JSONB;

    v_query_info_sort JSONB;
    v_final_sort_columns JSONB;

    v_user_id INT := NULL;

    input_json1 JSONB;
    input_json2 JSONB;
    replace_value_result JSONB;
    replaced_query TEXT;

    -- Variables for attached_policies
    v_attached_policies JSONB;
    v_filtered_columns JSONB := '[]'::jsonb;
    v_policy_query_info JSONB;
    v_policy_slug TEXT;
    v_policy_having_conditions JSONB;
    v_policy_filtered_columns JSONB;
    v_filtered_headers JSONB := '[]'::jsonb;
    v_filtered_records JSONB := '[]'::jsonb;
    v_header JSONB;
    v_record JSONB;
    v_filtered_record JSONB;
    v_includes JSONB;

    -- For lcpfunc_* dynamic filter_params transformation
    v_current_select_expr TEXT;
    v_lcp_json_match TEXT[];
    v_lcp_filter_payload JSONB;
    v_lcp_filter_text TEXT;
    v_lcp_where_all TEXT;
    v_lcp_where_any TEXT;
    v_lcp_having_all TEXT;
    v_lcp_having_any TEXT;
    v_lcp_where_final TEXT;
    v_lcp_having_final TEXT;
    v_lcp_map JSONB;
    v_lcp_parent_cond JSONB;
    v_lcp_operator TEXT;
    v_lcp_value_sql TEXT;
    v_lcp_condition TEXT;
    v_lcp_current_col TEXT;
    v_lcp_parent_col TEXT;
    v_lcp_expr_cache JSONB := '{}'::jsonb;
    v_effective_column_name TEXT;

BEGIN
    SET LOCAL statement_timeout = '55s';  -- Die before nginx timeout

    -- Parse the JSON input
    v_record_offset := COALESCE((json_input->>'start_index')::INT, 0);
    v_record_limit := COALESCE((json_input->>'limit_range')::INT, 10);
    v_sort_columns := (json_input->'sort_columns')::jsonb;
    v_search_all := (json_input->'search_all')::jsonb;
    v_search_any := (json_input->'search_any')::jsonb;
    v_grid_params := (json_input->'grid_params')::jsonb;
    v_entity_name := json_input->>'entity_name';
    v_company_id := COALESCE((json_input->>'company_id')::INT, 0);
    cunique := COALESCE(json_input->>'unique_id', NULL);

    -- Fetch print_query from master_entities
    SELECT COALESCE(query_information->>'print_query', 'false')::BOOLEAN
    INTO v_print_query
    FROM master_entities
    WHERE entity_name = v_entity_name AND company_id = v_company_id;

    v_group_by := json_input->'group_by';
    v_having_conditions := json_input->'having_conditions';
    v_having_any_conditions := json_input->'having_any_conditions';
    v_user_id := COALESCE((json_input->>'user_id')::INT, NULL);
    v_attached_policies := COALESCE((json_input->'attached_policies')::jsonb, '[]'::jsonb);
    v_filtered_columns := COALESCE((json_input->'filtered_columns')::jsonb, '[]'::jsonb);
    v_includes := json_input->'includes';

    -- Fetch the CTE from master_entities if available
    SELECT COALESCE(query_information->>'cte', NULL)
    INTO v_cte_clause
    FROM master_entities
    WHERE entity_name = v_entity_name AND company_id = v_company_id;

    -- If not found in table, fallback to input JSON
    IF v_cte_clause IS NULL OR TRIM(v_cte_clause) = '' THEN
        IF json_input ? 'cte' AND COALESCE(json_input->>'cte', '') <> '' THEN
            v_cte_clause := json_input->>'cte';
        END IF;
    END IF;

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

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

    SELECT header_entity_id, footer_entity_id
    INTO v_header_entity_id, v_footer_entity_id
    FROM master_entities
    WHERE entity_name = v_entity_name AND company_id = v_company_id;

    v_entities := jsonb_build_object(
        'header_entity_id', v_header_entity_id,
        'footer_entity_id', v_footer_entity_id
    );

    IF v_select_columns IS NULL THEN
        RAISE EXCEPTION 'No columns found for entity %', v_entity_name;
    END IF;
    -- Build the SELECT clause (lcpfunc_* columns always last so parent filter cache is fully populated)
    FOR v_col IN
        SELECT value FROM jsonb_array_elements(v_select_columns)
        ORDER BY CASE WHEN value->>1 LIKE 'lcpfunc_%' THEN 1 ELSE 0 END
    LOOP
        IF v_select_clause <> '' THEN
            v_select_clause := v_select_clause || ', ';
        END IF;

        v_current_select_expr := (v_col->>0);

        -- Auto-inject parent conditions into any column expression that contains filter_params JSON
        IF lower(v_current_select_expr) LIKE '%filter_params%' THEN
            v_lcp_filter_payload := NULL;
            v_lcp_filter_text := '';

            BEGIN
                v_lcp_json_match := regexp_match(v_current_select_expr, '(\{.*filter_params.*\})');
                IF v_lcp_json_match IS NOT NULL AND array_length(v_lcp_json_match, 1) > 0 THEN
                    v_lcp_filter_payload := (v_lcp_json_match[1])::jsonb;
                END IF;
            EXCEPTION WHEN OTHERS THEN
                v_lcp_filter_payload := NULL;
            END;

            IF v_lcp_filter_payload IS NOT NULL THEN
                -- Keep parent conditions under filter_params so function-side logic owns extraction.
                v_lcp_filter_payload := jsonb_set(
                    v_lcp_filter_payload,
                    '{filter_params,parent_conditions}',
                    jsonb_build_object(
                        'search_all', COALESCE(v_search_all, '[]'::jsonb),
                        'search_any', COALESCE(v_search_any, '[]'::jsonb),
                        'having_conditions', COALESCE(v_having_conditions, '[]'::jsonb),
                        'having_any_conditions', COALESCE(v_having_any_conditions, '[]'::jsonb)
                    ),
                    true
                );

                -- Replace exact matched JSON segment to avoid regex edge cases with escaped quotes.
                v_current_select_expr := replace(
                    v_current_select_expr,
                    v_lcp_json_match[1],
                    '$json$' || v_lcp_filter_payload::text || '$json$::jsonb'
                );
            END IF;
        END IF;

        IF (v_col->>0) IS NOT NULL THEN
            v_lcp_expr_cache := v_lcp_expr_cache || jsonb_build_object((v_col->>0), v_current_select_expr);
        END IF;

        v_select_clause := v_select_clause || v_current_select_expr;

        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->>14,
                'is_searchable', v_col->>3,
                'is_grid_column', v_col->>4,
                'is_sortable', v_col->>5,
                'field_value', v_col->>6,
                'clause_type', v_col->>8,
                'link_type', v_col->>9,
                'link_action', v_col->>10,
                'link_mode', v_col->>11,
                'field_type_id', v_col->>7,
                'field_html_content', v_col->>12,
                'enum_values', v_col->>13
            )::TEXT;

            v_headers := v_headers || jsonb_build_array(v_header_object::jsonb);
        END IF;

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

    -- Build the JOIN clauses and GROUP BY clause
    IF cunique IS NOT NULL THEN
        input_json1 := jsonb_build_object('$session_user_id', v_user_id, '$unique_id', cunique);
    ELSE
        input_json1 := jsonb_build_object('$session_user_id', v_user_id);
    END IF;

    FOR v_include_result_row IN
        SELECT * FROM build_includes1(v_entity_name, '', '', v_company_id)
    LOOP
        -- Apply placeholder replacement only on current clause
        replaced_query := replace_placeholder_variables(v_include_result_row.join_clause, input_json1);

        -- Append the replaced clause
        v_join_clause := v_join_clause || ' ' || COALESCE(replaced_query, '');

        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 := COALESCE(v_include_result_row.having_clause, '');
    END LOOP;

    IF v_includes IS NOT NULL AND jsonb_array_length(v_includes) > 0 THEN
        FOR v_include_result_row IN
            SELECT * FROM build_includes(v_includes, '', '')
        LOOP
            -- Append SELECT columns from include
            IF v_include_result_row.select_clause IS NOT NULL 
            AND v_include_result_row.select_clause <> '' THEN
                v_select_clause := v_select_clause || ', ' || COALESCE(v_include_result_row.select_clause, '');
            END IF;

            -- Append JOIN clause
            IF v_include_result_row.join_clause IS NOT NULL THEN
                v_join_clause := v_join_clause || ' ' || COALESCE(v_include_result_row.join_clause, '');
            END IF;

            -- Append alias mapping
            v_alias_mapping := v_alias_mapping || v_include_result_row.alias_mapping;
        END LOOP;
    END IF;

    SELECT
        COALESCE(query_information->>'search_all', '[]'::text)::jsonb,
        COALESCE(query_information->>'search_any', '[]'::text)::jsonb,
        COALESCE(query_information->>'sort_columns', '[]'::text)::jsonb
    INTO v_query_info_search, v_query_info_search_any, v_query_info_sort
    FROM master_entities
    WHERE entity_name = v_entity_name AND company_id = v_company_id;

    -- Note: filtered_columns is now fetched directly from json_input
    -- No need to process attached_policies table

    -- Combine search conditions from input and query_information
    v_combined_search_all := v_search_all || v_query_info_search;
    v_combined_search_any := v_search_any || v_query_info_search_any;

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

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

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

    -- =========================
    -- ✅ search_all (AND)
    -- =========================
    FOR v_col_search IN SELECT * FROM jsonb_array_elements(v_combined_search_all) LOOP
        v_effective_column_name := COALESCE(v_lcp_expr_cache->>(v_col_search->>'column_name'), (v_col_search->>'column_name'));

        -- Security check for column name and operator
        IF v_effective_column_name !~ '^[a-zA-Z0-9_\. ()'',:|\-]+$'
            AND lower(v_effective_column_name) NOT LIKE '%get_invoice_hours(%'
        THEN
            RAISE EXCEPTION 'Invalid characters in column name: %', v_effective_column_name;
        END IF;
        IF NOT EXISTS (
            SELECT 1
            FROM jsonb_array_elements_text(v_valid_operators) AS op
            WHERE op = 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_effective_column_name
                || ' ' || (v_col_search->>'operator')
                || ' (' || array_to_string(
                    ARRAY(
                        SELECT quote_literal(trim(both '''' FROM x))
                        FROM jsonb_array_elements_text(
                            CASE 
                                -- ✅ Already array
                                WHEN jsonb_typeof(v_col_search->'value') = 'array' 
                                THEN v_col_search->'value'

                                -- ✅ Stringified JSON array
                                WHEN jsonb_typeof(v_col_search->'value') = 'string'
                                    AND (v_col_search->>'value') LIKE '[%' 
                                THEN ((v_col_search->>'value')::json)::jsonb

                                -- ✅ Single value → wrap as array
                                ELSE jsonb_build_array(v_col_search->>'value')
                            END
                        ) x
                    ), ', '
                ) || ')';

        ELSIF (v_col_search->>'operator') = 'BETWEEN' THEN
            IF jsonb_typeof(v_col_search->'value') = 'array'
            AND jsonb_array_length(v_col_search->'value') = 2
            AND (v_col_search->'value')->>0 IS NOT NULL
            AND (v_col_search->'value')->>1 IS NOT NULL
            THEN
                v_where_clause := v_where_clause
                    || ' AND ' || v_effective_column_name
                    || ' BETWEEN '
                    || quote_literal((v_col_search->'value')->>0)
                    || ' AND '
                    || quote_literal((v_col_search->'value')->>1);
            ELSE
                RAISE EXCEPTION 'BETWEEN requires array with exactly 2 values';
            END IF;

        ELSE
            -- ✅ UPDATED explicit operator handling
            IF (v_col_search->>'operator') = 'IS NULL' THEN
                v_where_clause := v_where_clause
                    || ' AND ' || v_effective_column_name || ' IS NULL';

            ELSIF (v_col_search->>'operator') = 'IS NOT NULL' THEN
                v_where_clause := v_where_clause
                    || ' AND ' || v_effective_column_name || ' IS NOT NULL';

            ELSIF (v_col_search->>'operator') = 'IS_EMPTY' THEN
                v_where_clause := v_where_clause
                    || ' AND (' || v_effective_column_name || ' IS NULL OR '
                    || v_effective_column_name || ' = '''')';

            ELSIF (v_col_search->>'operator') = 'IS_NOT_EMPTY' THEN
                v_where_clause := v_where_clause
                    || ' AND (' || v_effective_column_name || ' IS NOT NULL AND '
                    || v_effective_column_name || ' <> '''')';

            ELSIF ((v_col_search->>'operator') = 'IS' OR (v_col_search->>'operator') = 'IS NOT') THEN
                v_where_clause := v_where_clause
                    || ' AND ' || v_effective_column_name
                    || ' ' || (v_col_search->>'operator') || ' NULL';

            ELSIF v_col_search ? 'value'
                AND (
                    jsonb_typeof(v_col_search->'value') <> 'string'
                    OR TRIM(v_col_search->>'value') <> ''
                ) THEN
                input_json2 := jsonb_build_object(
                    'placeholder', (v_col_search->>'value'),
                    'value_json', input_json1
                );
                replace_value_result := replace_values(input_json2);

                v_where_clause := v_where_clause
                    || ' AND ' || v_effective_column_name
                    || ' ' || (v_col_search->>'operator')
                    || ' ' || CASE 
                    WHEN jsonb_typeof(replace_value_result->'value') = 'string'
                    THEN quote_literal(replace_value_result->>'value')
                    ELSE (replace_value_result->'value')::text
                    END;
            END IF;
        END IF;
    END LOOP;

    -- =========================
    -- ✅ search_any (OR groups)
    -- =========================
    FOR v_col_search IN SELECT * FROM jsonb_array_elements(v_combined_search_any) LOOP
        IF jsonb_typeof(v_col_search) = 'array' THEN
            -- Multiple sets of conditions
            v_or_group := '';

            FOR v_inner_col_search IN SELECT * FROM jsonb_array_elements(v_col_search) LOOP
                v_effective_column_name := COALESCE(v_lcp_expr_cache->>(v_inner_col_search->>'column_name'), (v_inner_col_search->>'column_name'));

                IF v_effective_column_name !~ '^[a-zA-Z0-9_\. ()'',:|\-]+$'
                    AND lower(v_effective_column_name) NOT LIKE '%get_invoice_hours(%'
                THEN
                    RAISE EXCEPTION 'Invalid characters in column name: %', v_effective_column_name;
                END IF;
                IF NOT EXISTS (
                    SELECT 1
                    FROM jsonb_array_elements_text(v_valid_operators) AS op
                    WHERE op = v_inner_col_search->>'operator'
                ) THEN
                    RAISE EXCEPTION 'Invalid operator: %', (v_inner_col_search->>'operator');
                END IF;

                IF (v_inner_col_search->>'operator') = 'IN' OR (v_inner_col_search->>'operator') = 'NOT IN' THEN
                    v_or_group := v_or_group
                        || v_effective_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
                    IF jsonb_typeof(v_inner_col_search->'value') = 'array'
                    AND jsonb_array_length(v_inner_col_search->'value') = 2
                    AND (v_inner_col_search->'value')->>0 IS NOT NULL
                    AND (v_inner_col_search->'value')->>1 IS NOT NULL THEN

                        v_or_group := v_or_group
                            || v_effective_column_name
                            || ' BETWEEN ' || quote_literal((v_inner_col_search->'value')->>0)
                            || ' AND ' || quote_literal((v_inner_col_search->'value')->>1)
                            || ' OR ';
                    END IF;

                ELSE
                    -- ✅ UPDATED explicit operator handling
                    IF (v_inner_col_search->>'operator') = 'IS NULL' THEN
                        v_or_group := v_or_group
                            || v_effective_column_name || ' IS NULL OR ';

                    ELSIF (v_inner_col_search->>'operator') = 'IS NOT NULL' THEN
                        v_or_group := v_or_group
                            || v_effective_column_name || ' IS NOT NULL OR ';

                    ELSIF (v_inner_col_search->>'operator') = 'IS_EMPTY' THEN
                        v_or_group := v_or_group
                            || '(' || v_effective_column_name || ' IS NULL OR '
                            || v_effective_column_name || ' = '''') OR ';

                    ELSIF (v_inner_col_search->>'operator') = 'IS_NOT_EMPTY' THEN
                        v_or_group := v_or_group
                            || '(' || v_effective_column_name || ' IS NOT NULL AND '
                            || v_effective_column_name || ' <> '''') OR ';

                    ELSIF ((v_inner_col_search->>'operator') = 'IS' OR (v_inner_col_search->>'operator') = 'IS NOT') THEN
                        v_or_group := v_or_group
                            || v_effective_column_name
                            || ' ' || (v_inner_col_search->>'operator') || ' NULL OR ';

                    ELSIF (v_inner_col_search->>'value') IS NOT NULL AND TRIM(v_inner_col_search->>'value') <> '' THEN
                        input_json2 := jsonb_build_object(
                            'placeholder', (v_inner_col_search->>'value'),
                            'value_json', input_json1
                        );
                        replace_value_result := replace_values(input_json2);

                        v_or_group := v_or_group
                            || v_effective_column_name
                            || ' ' || (v_inner_col_search->>'operator')
                            || ' ' || CASE 
                            WHEN jsonb_typeof(replace_value_result->'value') = 'string'
                            THEN quote_literal(replace_value_result->>'value')
                            ELSE (replace_value_result->'value')::text
                            END
                            || ' OR ';
                    END IF;
                END IF;
            END LOOP;

            IF v_or_group <> '' THEN
                v_or_group := LEFT(v_or_group, LENGTH(v_or_group) - 4); -- remove trailing " OR "
                v_or_clause := v_or_clause || '(' || v_or_group || ')' || ' AND ';
            END IF;

        ELSE
            -- Single set of conditions
            v_effective_column_name := COALESCE(v_lcp_expr_cache->>(v_col_search->>'column_name'), (v_col_search->>'column_name'));

            IF v_effective_column_name !~ '^[a-zA-Z0-9_\\. ()'',:|\\-]+$'
                AND lower(v_effective_column_name) NOT LIKE '%get_invoice_hours(%'
            THEN
                RAISE EXCEPTION 'Invalid characters in column name: %', v_effective_column_name;
            END IF;
            IF NOT EXISTS (
                SELECT 1
                FROM jsonb_array_elements_text(v_valid_operators) AS op
                WHERE op = v_col_search->>'operator'
            ) THEN
                RAISE EXCEPTION 'Invalid operator: %', (v_col_search->>'operator');
            END IF;

            IF (v_col_search->>'operator') = 'IN' OR (v_col_search->>'operator') = 'NOT IN' THEN
                v_or_clause := v_or_clause
                    || v_effective_column_name || ' ' || (v_col_search->>'operator')
                    || ' (' || array_to_string(
                        ARRAY(
                            SELECT quote_literal(trim(both '''' FROM x))
                            FROM jsonb_array_elements_text(
                                CASE 
                                    -- ✅ Already array
                                    WHEN jsonb_typeof(v_col_search->'value') = 'array' 
                                    THEN v_col_search->'value'

                                    -- ✅ Stringified JSON array
                                    WHEN jsonb_typeof(v_col_search->'value') = 'string'
                                        AND (v_col_search->>'value') LIKE '[%' 
                                    THEN ((v_col_search->>'value')::json)::jsonb

                                    -- ✅ Single value → wrap as array
                                    ELSE jsonb_build_array(v_col_search->>'value')
                                END
                            ) x
                        ), ', '
                    ) || ') OR ';

            ELSIF (v_col_search->>'operator') = 'BETWEEN' THEN
                IF jsonb_typeof(v_col_search->'value') = 'array'
                AND jsonb_array_length(v_col_search->'value') = 2
                THEN
                    v_or_clause := v_or_clause
                        || v_effective_column_name
                        || ' BETWEEN '
                        || quote_literal((v_col_search->'value')->>0)
                        || ' AND '
                        || quote_literal((v_col_search->'value')->>1)
                        || ' OR ';
                ELSE
                    RAISE EXCEPTION 'BETWEEN requires array with exactly 2 values';
                END IF;

            ELSE
                -- ✅ UPDATED explicit operator handling
                IF (v_col_search->>'operator') = 'IS NULL' THEN
                    v_or_clause := v_or_clause
                        || v_effective_column_name || ' IS NULL OR ';

                ELSIF (v_col_search->>'operator') = 'IS NOT NULL' THEN
                    v_or_clause := v_or_clause
                        || v_effective_column_name || ' IS NOT NULL OR ';

                ELSIF (v_col_search->>'operator') = 'IS_EMPTY' THEN
                    v_or_clause := v_or_clause
                        || '(' || v_effective_column_name || ' IS NULL OR '
                        || v_effective_column_name || ' = '''') OR ';

                ELSIF (v_col_search->>'operator') = 'IS_NOT_EMPTY' THEN
                    v_or_clause := v_or_clause
                        || '(' || v_effective_column_name || ' IS NOT NULL AND '
                        || v_effective_column_name || ' <> '''') OR ';

                ELSIF ((v_col_search->>'operator') = 'IS' OR (v_col_search->>'operator') = 'IS NOT') THEN
                    v_or_clause := v_or_clause
                        || v_effective_column_name
                        || ' ' || (v_col_search->>'operator') || ' NULL OR ';

                 ELSIF v_col_search ? 'value'
                    AND (
                        jsonb_typeof(v_col_search->'value') <> 'string'
                        OR TRIM(v_col_search->>'value') <> ''
                    ) THEN
                    input_json2 := jsonb_build_object(
                        'placeholder', (v_col_search->>'value'),
                        'value_json', input_json1
                    );
                    replace_value_result := replace_values(input_json2);

                    v_or_clause := v_or_clause
                        || v_effective_column_name
                        || ' ' || (v_col_search->>'operator')
                        || ' ' || CASE 
                                WHEN jsonb_typeof(replace_value_result->'value') = 'string'
                                THEN quote_literal(replace_value_result->>'value')
                                ELSE (replace_value_result->'value')::text
                                END
                        || ' 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); -- remove trailing " OR "
        v_where_clause := COALESCE(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;

    -- =========================
    -- ✅ HAVING (AND)
    -- =========================
    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
            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 EXISTS (
                SELECT 1
                FROM jsonb_array_elements_text(v_valid_operators) AS op
                WHERE op = v_having_cond.value->>'operator'
            ) THEN
                RAISE EXCEPTION 'Invalid operator in HAVING: %', (v_having_cond.value->>'operator');
            END IF;

            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
                IF jsonb_typeof(v_having_cond.value->'value') = 'array'
                AND jsonb_array_length(v_having_cond.value->'value') = 2
                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
                    RAISE EXCEPTION 'BETWEEN requires array with exactly 2 values in HAVING';
                END IF;

            ELSE
                -- ✅ UPDATED explicit operator handling
                IF (v_having_cond.value->>'operator') = 'IS NULL' THEN
                    v_having_clause := v_having_clause
                        || (v_having_cond.value->>'column_name') || ' IS NULL AND ';

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

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

                ELSIF (v_having_cond.value->>'operator') = 'IS_NOT_EMPTY' THEN
                    v_having_clause := v_having_clause
                        || '(' || (v_having_cond.value->>'column_name') || ' IS NOT NULL AND '
                        || (v_having_cond.value->>'column_name') || ' <> '''') 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 ';

                ELSIF (v_having_cond.value->>'value') IS NOT NULL AND TRIM(v_having_cond.value->>'value') <> '' THEN
                    input_json2 := jsonb_build_object(
                        'placeholder', (v_having_cond.value->>'value'),
                        'value_json', input_json1
                    );
                    replace_value_result := replace_values(input_json2);

                    v_having_clause := v_having_clause
                        || (v_having_cond.value->>'column_name')
                        || ' ' || (v_having_cond.value->>'operator')
                        || ' ' || CASE 
                                    WHEN jsonb_typeof(replace_value_result->'value') = 'string'
                                    THEN quote_literal(replace_value_result->>'value')
                                    ELSE (replace_value_result->'value')::text
                                    END
                        || ' AND ';
                END IF;
            END IF;
        END LOOP;

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

    -- =========================
    -- ✅ HAVING ANY (OR)
    -- =========================
    IF v_having_any_conditions IS NOT NULL THEN
        IF v_having_clause = '' THEN
            v_having_clause := ' HAVING ';
        ELSE
            v_having_clause := v_having_clause || ' AND (';
        END IF;

        FOR v_having_cond IN SELECT * FROM jsonb_array_elements(v_having_any_conditions) LOOP
            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 EXISTS (
                SELECT 1
                FROM jsonb_array_elements_text(v_valid_operators) AS op
                WHERE op = v_having_cond.value->>'operator'
            ) THEN
                RAISE EXCEPTION 'Invalid operator in HAVING: %', (v_having_cond.value->>'operator');
            END IF;

            IF (v_having_cond.value->>'operator') = 'IN' OR (v_having_cond.value->>'operator') = 'NOT IN' THEN
                v_having_clause := v_having_clause
                    || (v_having_cond.value->>'column_name') || ' ' || (v_having_cond.value->>'operator')
                    || ' (' || array_to_string(
                        ARRAY(
                            SELECT quote_literal(trim(both '''' FROM x))
                            FROM jsonb_array_elements_text(v_having_cond.value->'value') x
                        ), ', '
                    ) || ') OR ';

            ELSIF (v_having_cond.value->>'operator') = 'BETWEEN' THEN
                IF jsonb_typeof(v_having_cond.value->'value') = 'array'
                AND jsonb_array_length(v_having_cond.value->'value') = 2
                THEN
                    v_having_clause := v_having_clause
                        || (v_having_cond.value->>'column_name')
                        || ' BETWEEN '
                        || quote_literal((v_having_cond.value->'value')->>0)
                        || ' AND '
                        || quote_literal((v_having_cond.value->'value')->>1)
                        || ' OR ';
                ELSE
                    RAISE EXCEPTION 'BETWEEN requires array with exactly 2 values in HAVING ANY';
                END IF;

            ELSE
                -- ✅ UPDATED explicit operator handling
                IF (v_having_cond.value->>'operator') = 'IS NULL' THEN
                    v_having_clause := v_having_clause
                        || (v_having_cond.value->>'column_name') || ' IS NULL OR ';

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

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

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

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

                ELSIF (v_having_cond.value->>'value') IS NOT NULL AND TRIM(v_having_cond.value->>'value') <> '' THEN
                    input_json2 := jsonb_build_object(
                        'placeholder', (v_having_cond.value->>'value'),
                        'value_json', input_json1
                    );
                    replace_value_result := replace_values(input_json2);

                    v_having_clause := v_having_clause
                        || (v_having_cond.value->>'column_name')
                        || ' ' || (v_having_cond.value->>'operator')
                        || ' ' || CASE 
                                    WHEN jsonb_typeof(replace_value_result->'value') = 'string'
                                    THEN quote_literal(replace_value_result->>'value')
                                    ELSE (replace_value_result->'value')::text
                                    END
                        || ' OR ';
                END IF;
            END IF;
        END LOOP;

        IF v_having_clause <> '' THEN
            v_having_clause := LEFT(v_having_clause, LENGTH(v_having_clause) - 4); -- remove trailing " OR "
        END IF;

        IF v_having_conditions IS NOT NULL THEN
            v_having_clause := v_having_clause || ')';
        END IF;
    END IF;

    -- Handle CTE if present in input (kept as-is)
    IF json_input ? 'cte' AND COALESCE(json_input->>'cte', '') <> '' THEN
        v_cte_clause := json_input->>'cte';
    END IF;

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

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

    v_count_query_str := COALESCE(replace_placeholder_variables(v_count_query_str, v_grid_params), v_count_query_str);

    v_count_query_str := COALESCE(replace_placeholder_variables(v_count_query_str, input_json1), v_count_query_str);
    v_executed_query := v_count_query_str;
    IF v_count_query_str IS NULL OR TRIM(v_count_query_str) = '' THEN
        RAISE EXCEPTION 'Count query is NULL or empty';
    END IF;
    BEGIN
        EXECUTE v_count_query_str INTO v_total_records_count;
    EXCEPTION WHEN OTHERS THEN
        v_total_records_count := 0;
    END;

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

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

            IF v_sort_condition = '' THEN
                v_sort_condition := (v_sort_col->>0) || ' ' || (v_sort_col->>1);
            ELSE
                v_sort_condition := v_sort_condition || ', ' || (v_sort_col->>0) || ' ' || (v_sort_col->>1);
            END IF;
        END LOOP;

        IF v_sort_condition <> '' THEN
            v_query_str := v_query_str || ' ORDER BY ' || v_sort_condition;
        END IF;
    END IF;

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

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

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

    IF cunique IS NOT NULL THEN
        v_query_str := replace_placeholder_variables(
            v_query_str,
            jsonb_build_object('$session_user_id', v_user_id, '$unique_id', cunique)
        );
    ELSE
        v_query_str := COALESCE(
                        replace_placeholder_variables(
                            v_query_str,
                            jsonb_build_object('$session_user_id', v_user_id)
                        ),
                        v_query_str
                    );
    END IF;


    -- Always keep the latest executable SQL for error reporting
    v_executed_query := v_query_str;

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

    -- Return empty records array since we want plain SELECT
    v_records := '[]'::jsonb;
    IF v_query_str IS NULL OR TRIM(v_query_str) = '' THEN
        RAISE EXCEPTION 'Final query is NULL or empty';
    END IF;
    -- Build the records JSON manually
    EXECUTE 'SELECT jsonb_agg(t) FROM (' || v_query_str || ') t'
    INTO v_records;

    v_records := COALESCE(v_records, '[]'::jsonb);

    IF v_print_query THEN
        v_executed_query := v_query_str;
    END IF;

    -- Filter headers and records based on filtered_columns if exists
    IF jsonb_array_length(v_filtered_columns) > 0 THEN
        -- Filter headers
        FOR v_header IN SELECT * FROM jsonb_array_elements(v_headers) LOOP
            IF v_header->>'header' IN (
                SELECT jsonb_array_elements_text(v_filtered_columns)
            ) THEN
                v_filtered_headers := v_filtered_headers || v_header;
            END IF;
        END LOOP;

        -- Filter records
        FOR v_record IN SELECT * FROM jsonb_array_elements(v_records) LOOP
            v_filtered_record := '{}'::jsonb;

            -- Only include columns that are in filtered_columns
            FOR v_policy_slug IN SELECT jsonb_array_elements_text(v_filtered_columns) LOOP
                IF v_record ? v_policy_slug THEN
                    v_filtered_record := v_filtered_record || jsonb_build_object(v_policy_slug, v_record->v_policy_slug);
                END IF;
            END LOOP;

            v_filtered_records := v_filtered_records || v_filtered_record;
        END LOOP;

        -- Use filtered data
        v_headers := v_filtered_headers;
        v_records := v_filtered_records;
    END IF;

    -- Set the output result
    IF v_print_query THEN
        result := jsonb_build_object(
            'total_records_count', v_total_records_count,
            'records', v_records,
            'headers', v_headers,
            'entities', v_entities,
            'query_executed', v_executed_query,
            'error_type', '',
            'error_message', '',
            'execution_status', TRUE
        );
    ELSE
        result := jsonb_build_object(
            'total_records_count', v_total_records_count,
            'records', v_records,
            'headers', v_headers,
            'entities', v_entities,
            'error_type', '',
            'error_message', '',
            'execution_status', TRUE
        );
    END IF;

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

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