CREATE OR REPLACE PROCEDURE public.get_multiple_records(
    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_primary_table TEXT;
    v_includes JSONB;
    v_group_by JSONB;
    v_print_query BOOLEAN;
    v_select_clause TEXT := '';
    v_join_clause TEXT := '';
    v_where_clause TEXT := ' WHERE ';
    v_group_by_clause TEXT := '';
    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;

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

    -- Initialize query string
    v_query_str := 'SELECT ' || v_select_clause || ' FROM ' || v_primary_table || v_join_clause || v_where_clause || v_group_by_clause;

    -- Total records count
    EXECUTE 'SELECT COUNT(*) FROM ' || v_primary_table || v_join_clause || v_where_clause 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_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.value->>0) || ' ' || (v_sort_col.value->>1);
        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;
$BODY$;
