CREATE OR REPLACE PROCEDURE public.get_multiple_records(
	IN json_input jsonb,
	INOUT result jsonb)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    v_record_offset INT;
    v_limit_range 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_cte_clause TEXT := ''; -- Added missing variable declaration
    v_query_str TEXT;
    v_total_records_count INT;
    v_records JSONB;
    v_executed_query TEXT := '';
	v_count_query_str 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 RECORD;

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

	v_grid_params JSONB;
    v_current_select_expr TEXT;
    v_lcp_filter_payload JSONB;
    v_lcp_filter_text TEXT;
    v_lcp_json_match 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_limit_range := COALESCE((json_input->>'limit_range')::INT, 0);
    
    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);
	v_grid_params := (json_input->'grid_params')::jsonb;

    -- 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_conditions, '[]'::jsonb)
                    ),
                    true
                );

                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->>1) IS NOT NULL AND (v_col->>1) <> '' THEN
            v_select_clause := v_select_clause || v_current_select_expr || ' 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_current_select_expr;
        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;

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

    -- 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
		--IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',=<>!:-|]+$' THEN
		--IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_. ()'',=!<>-]+$' THEN
		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'));
                        IF upper(trim(v_col_search.value->>'value')) IN ('CURRENT_DATE', 'NOW()', 'CURRENT_TIMESTAMP') THEN
                            v_where_clause := v_where_clause || ' AND ' || (v_col_search.value->>'column_name') || ' ' || (v_col_search.value->>'operator') || ' ' || (v_col_search.value->>'value');
                        ELSE
                            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'));
                        END IF;
            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
				--IF (v_inner_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_. ()'',=!<>-]+$' THEN
				--IF (v_inner_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_. ()'',=!<>-]+$' THEN
				IF (v_inner_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_.: ()'',=!<>-]+$' THEN
                --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
			--IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',=<>!:-|]+$' THEN
			--IF (v_col_search.value->>'column_name') !~ '^[a-zA-Z0-9_. ()'',=!<>-]+$' THEN
			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;

    -- Handle CTE if present
    IF json_input ? 'cte' THEN
        v_cte_clause := json_input->>'cte';
    END IF;
    IF v_cte_clause IS NOT NULL AND TRIM(v_cte_clause) <> '' THEN
        v_query_str := v_cte_clause || ' ' || v_query_str;
    END IF;

    -- Total records count
    IF v_cte_clause IS NOT NULL AND TRIM(v_cte_clause) <> '' THEN
        v_count_query_str := v_cte_clause || ' SELECT COUNT(*) FROM (SELECT 1 FROM ' || v_primary_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_primary_table || v_join_clause || v_where_clause || v_group_by_clause || v_having_clause || ') AS subquery';
    END IF;

	-- Only replace if grid_params is not null
    IF v_grid_params IS NOT NULL THEN
		v_count_query_str := replace_placeholder_variables(v_count_query_str, v_grid_params);
	END IF;

	EXECUTE v_count_query_str 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);
        END LOOP;
        
        v_query_str := v_query_str || v_order_by_clause;
    END IF;

    -- Apply LIMIT and OFFSET
    IF v_limit_range > 0 THEN
        v_query_str := v_query_str || ' LIMIT ' || v_limit_range;
    END IF;

    IF v_record_offset > 0 THEN
        v_query_str := v_query_str || ' OFFSET ' || v_record_offset;
    END IF;

    -- Final JSON aggregation query
    --v_query_str := 'SELECT COALESCE(jsonb_agg(t), ''[]'') FROM (' || v_query_str || ') t';
    --IF v_cte_clause IS NOT NULL AND TRIM(v_cte_clause) <> '' THEN
      --  v_query_str := v_cte_clause || ' ' || v_query_str;
    --END IF;

	
		-- Only replace if grid_params is not null
    IF v_grid_params IS NOT NULL THEN
		-- Replace all variables with its respective values here (For example $gparam_1,$gparam_2..etc.)
		v_query_str := replace_placeholder_variables(v_query_str, v_grid_params);
	END IF;

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

    -- Build records using efficient jsonb_agg aggregation
    EXECUTE 'SELECT COALESCE(jsonb_agg(to_jsonb(t)), ''[]''::jsonb) FROM (' || v_query_str || ') t'
    INTO v_records;

    -- Set the output result
    IF v_print_query THEN
        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
        );
    ELSE
        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
        );
    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 '[]',
                'error_type', SQLSTATE,
                'error_message', SQLERRM,
                'execution_status', FALSE
            );
        END IF;
END;
$BODY$;