CREATE OR REPLACE FUNCTION get_transformed_filter_conditions(
    p_filter_input JSONB
)
RETURNS TEXT
LANGUAGE plpgsql
AS $function$
DECLARE
    v_result_where_all TEXT := '';
    v_result_where_any TEXT := '';
    v_result_having_all TEXT := '';
    v_result_having_any TEXT := '';
    v_final_where TEXT := '';
    v_final_having TEXT := '';

    v_map JSONB;
    v_parent_cond JSONB;
    v_parent_cond_inner JSONB;
    v_effective_operator TEXT;
    v_condition TEXT;
    v_current_col TEXT;
    v_parent_col TEXT;
    v_effective_value JSONB;
    v_value_sql TEXT;
BEGIN
    IF p_filter_input IS NULL OR p_filter_input = '{}'::jsonb THEN
        RETURN '';
    END IF;

    -- search_all (AND)
    FOR v_map IN
        SELECT * FROM jsonb_array_elements(COALESCE(p_filter_input->'filter_params'->'search_all', '[]'::jsonb))
    LOOP
        IF COALESCE((v_map->>'apply_based_on_parent')::boolean, false) THEN
            v_current_col := v_map->>'current_column_name';
            v_parent_col := v_map->>'parent_column_name';
            v_effective_operator := NULL;
            v_effective_value := NULL;

            IF v_map ? 'operator' THEN
                v_effective_operator := NULLIF(TRIM(v_map->>'operator'), '');
            END IF;
            IF v_map ? 'value' THEN
                v_effective_value := v_map->'value';
            END IF;

            IF v_effective_operator IS NULL THEN
                FOR v_parent_cond IN
                    SELECT *
                    FROM jsonb_array_elements(
                        COALESCE(
                            p_filter_input->'filter_params'->'parent_conditions'->'search_all',
                            p_filter_input->'search_all',
                            '[]'::jsonb
                        )
                    )
                LOOP
                    IF v_parent_cond->>'column_name' = v_parent_col THEN
                        v_effective_operator := v_parent_cond->>'operator';
                        IF v_effective_value IS NULL THEN
                            v_effective_value := v_parent_cond->'value';
                        END IF;
                        EXIT;
                    END IF;
                END LOOP;
            END IF;

            IF v_current_col IS NOT NULL AND v_effective_operator IS NOT NULL THEN
                IF v_effective_operator IN ('IS NULL', 'IS NOT NULL') THEN
                    v_condition := v_current_col || ' ' || v_effective_operator;
                ELSIF v_effective_operator IN ('IS', 'IS NOT') THEN
                    v_condition := v_current_col || ' ' || v_effective_operator || ' NULL';
                ELSIF v_effective_operator = 'IS_EMPTY' THEN
                    v_condition := '(' || v_current_col || ' IS NULL OR ' || v_current_col || ' = '''')';
                ELSIF v_effective_operator = 'IS_NOT_EMPTY' THEN
                    v_condition := '(' || v_current_col || ' IS NOT NULL AND ' || v_current_col || ' <> '''')';
                ELSIF v_effective_operator IN ('IN', 'NOT IN') THEN
                    v_condition := v_current_col || ' ' || v_effective_operator
                        || ' (' || array_to_string(
                            ARRAY(
                                SELECT quote_literal(trim(both '''' FROM x))
                                FROM jsonb_array_elements_text(
                                    CASE
                                        WHEN jsonb_typeof(v_effective_value) = 'array' THEN v_effective_value
                                        WHEN jsonb_typeof(v_effective_value) = 'string' AND (v_effective_value::text) LIKE '"[%"' THEN ((trim(both '"' FROM v_effective_value::text))::json)::jsonb
                                        ELSE jsonb_build_array(v_effective_value)
                                    END
                                ) x
                            ), ', '
                        ) || ')';
                ELSIF v_effective_operator = 'BETWEEN'
                    AND jsonb_typeof(v_effective_value) = 'array'
                    AND jsonb_array_length(v_effective_value) = 2 THEN
                    v_condition := v_current_col || ' BETWEEN '
                        || quote_literal(v_effective_value->>0)
                        || ' AND '
                        || quote_literal(v_effective_value->>1);
                ELSE
                    v_value_sql := CASE
                        WHEN jsonb_typeof(v_effective_value) = 'string' THEN quote_literal(v_effective_value#>>'{}')
                        WHEN v_effective_value IS NULL THEN 'NULL'
                        ELSE v_effective_value::text
                    END;
                    v_condition := v_current_col || ' ' || v_effective_operator || ' ' || v_value_sql;
                END IF;

                IF COALESCE(v_condition, '') <> '' THEN
                    IF v_result_where_all <> '' THEN
                        v_result_where_all := v_result_where_all || ' AND ';
                    END IF;
                    v_result_where_all := v_result_where_all || v_condition;
                END IF;
            END IF;
        END IF;
    END LOOP;

    -- search_any (OR)
    FOR v_map IN
        SELECT * FROM jsonb_array_elements(COALESCE(p_filter_input->'filter_params'->'search_any', '[]'::jsonb))
    LOOP
        IF COALESCE((v_map->>'apply_based_on_parent')::boolean, false) THEN
            v_current_col := v_map->>'current_column_name';
            v_parent_col := v_map->>'parent_column_name';
            v_effective_operator := NULL;
            v_effective_value := NULL;

            IF v_map ? 'operator' THEN
                v_effective_operator := NULLIF(TRIM(v_map->>'operator'), '');
            END IF;
            IF v_map ? 'value' THEN
                v_effective_value := v_map->'value';
            END IF;

            IF v_effective_operator IS NULL THEN
                FOR v_parent_cond IN
                    SELECT *
                    FROM jsonb_array_elements(
                        COALESCE(
                            p_filter_input->'filter_params'->'parent_conditions'->'search_any',
                            p_filter_input->'search_any',
                            '[]'::jsonb
                        )
                    )
                LOOP
                    IF jsonb_typeof(v_parent_cond) = 'array' THEN
                        FOR v_parent_cond_inner IN SELECT * FROM jsonb_array_elements(v_parent_cond) LOOP
                            IF v_parent_cond_inner->>'column_name' = v_parent_col THEN
                                v_effective_operator := v_parent_cond_inner->>'operator';
                                IF v_effective_value IS NULL THEN
                                    v_effective_value := v_parent_cond_inner->'value';
                                END IF;
                                EXIT;
                            END IF;
                        END LOOP;
                    ELSIF v_parent_cond->>'column_name' = v_parent_col THEN
                        v_effective_operator := v_parent_cond->>'operator';
                        IF v_effective_value IS NULL THEN
                            v_effective_value := v_parent_cond->'value';
                        END IF;
                        EXIT;
                    END IF;
                END LOOP;
            END IF;

            IF v_current_col IS NOT NULL AND v_effective_operator IS NOT NULL THEN
                IF v_effective_operator IN ('IS NULL', 'IS NOT NULL') THEN
                    v_condition := v_current_col || ' ' || v_effective_operator;
                ELSIF v_effective_operator IN ('IS', 'IS NOT') THEN
                    v_condition := v_current_col || ' ' || v_effective_operator || ' NULL';
                ELSIF v_effective_operator = 'IS_EMPTY' THEN
                    v_condition := '(' || v_current_col || ' IS NULL OR ' || v_current_col || ' = '''')';
                ELSIF v_effective_operator = 'IS_NOT_EMPTY' THEN
                    v_condition := '(' || v_current_col || ' IS NOT NULL AND ' || v_current_col || ' <> '''')';
                ELSIF v_effective_operator = 'BETWEEN'
                    AND jsonb_typeof(v_effective_value) = 'array'
                    AND jsonb_array_length(v_effective_value) = 2 THEN
                    v_condition := v_current_col || ' BETWEEN '
                        || quote_literal(v_effective_value->>0)
                        || ' AND '
                        || quote_literal(v_effective_value->>1);
                ELSE
                    v_value_sql := CASE
                        WHEN jsonb_typeof(v_effective_value) = 'string' THEN quote_literal(v_effective_value#>>'{}')
                        WHEN v_effective_value IS NULL THEN 'NULL'
                        ELSE v_effective_value::text
                    END;
                    v_condition := v_current_col || ' ' || v_effective_operator || ' ' || v_value_sql;
                END IF;

                IF COALESCE(v_condition, '') <> '' THEN
                    IF v_result_where_any <> '' THEN
                        v_result_where_any := v_result_where_any || ' OR ';
                    END IF;
                    v_result_where_any := v_result_where_any || v_condition;
                END IF;
            END IF;
        END IF;
    END LOOP;

    -- having_conditions (AND)
    FOR v_map IN
        SELECT * FROM jsonb_array_elements(COALESCE(p_filter_input->'filter_params'->'having_conditions', '[]'::jsonb))
    LOOP
        IF COALESCE((v_map->>'apply_based_on_parent')::boolean, false) THEN
            v_current_col := v_map->>'current_column_name';
            v_parent_col := v_map->>'parent_column_name';
            v_effective_operator := NULL;
            v_effective_value := NULL;

            IF v_map ? 'operator' THEN
                v_effective_operator := NULLIF(TRIM(v_map->>'operator'), '');
            END IF;
            IF v_map ? 'value' THEN
                v_effective_value := v_map->'value';
            END IF;

            IF v_effective_operator IS NULL THEN
                FOR v_parent_cond IN
                    SELECT value
                    FROM jsonb_array_elements(
                        COALESCE(
                            p_filter_input->'filter_params'->'parent_conditions'->'having_conditions',
                            p_filter_input->'having_conditions',
                            '[]'::jsonb
                        )
                    )
                LOOP
                    IF v_parent_cond->>'column_name' = v_parent_col THEN
                        v_effective_operator := v_parent_cond->>'operator';
                        IF v_effective_value IS NULL THEN
                            v_effective_value := v_parent_cond->'value';
                        END IF;
                        EXIT;
                    END IF;
                END LOOP;
            END IF;

            IF v_current_col IS NOT NULL AND v_effective_operator IS NOT NULL THEN
                v_value_sql := CASE
                    WHEN jsonb_typeof(v_effective_value) = 'string' THEN quote_literal(v_effective_value#>>'{}')
                    WHEN v_effective_value IS NULL THEN 'NULL'
                    ELSE v_effective_value::text
                END;
                v_condition := v_current_col || ' ' || v_effective_operator || ' ' || v_value_sql;

                IF COALESCE(v_condition, '') <> '' THEN
                    IF v_result_having_all <> '' THEN
                        v_result_having_all := v_result_having_all || ' AND ';
                    END IF;
                    v_result_having_all := v_result_having_all || v_condition;
                END IF;
            END IF;
        END IF;
    END LOOP;

    -- having_any_conditions (OR)
    FOR v_map IN
        SELECT * FROM jsonb_array_elements(COALESCE(p_filter_input->'filter_params'->'having_any_conditions', '[]'::jsonb))
    LOOP
        IF COALESCE((v_map->>'apply_based_on_parent')::boolean, false) THEN
            v_current_col := v_map->>'current_column_name';
            v_parent_col := v_map->>'parent_column_name';
            v_effective_operator := NULL;
            v_effective_value := NULL;

            IF v_map ? 'operator' THEN
                v_effective_operator := NULLIF(TRIM(v_map->>'operator'), '');
            END IF;
            IF v_map ? 'value' THEN
                v_effective_value := v_map->'value';
            END IF;

            IF v_effective_operator IS NULL THEN
                FOR v_parent_cond IN
                    SELECT value
                    FROM jsonb_array_elements(
                        COALESCE(
                            p_filter_input->'filter_params'->'parent_conditions'->'having_any_conditions',
                            p_filter_input->'having_any_conditions',
                            '[]'::jsonb
                        )
                    )
                LOOP
                    IF v_parent_cond->>'column_name' = v_parent_col THEN
                        v_effective_operator := v_parent_cond->>'operator';
                        IF v_effective_value IS NULL THEN
                            v_effective_value := v_parent_cond->'value';
                        END IF;
                        EXIT;
                    END IF;
                END LOOP;
            END IF;

            IF v_current_col IS NOT NULL AND v_effective_operator IS NOT NULL THEN
                v_value_sql := CASE
                    WHEN jsonb_typeof(v_effective_value) = 'string' THEN quote_literal(v_effective_value#>>'{}')
                    WHEN v_effective_value IS NULL THEN 'NULL'
                    ELSE v_effective_value::text
                END;
                v_condition := v_current_col || ' ' || v_effective_operator || ' ' || v_value_sql;

                IF COALESCE(v_condition, '') <> '' THEN
                    IF v_result_having_any <> '' THEN
                        v_result_having_any := v_result_having_any || ' OR ';
                    END IF;
                    v_result_having_any := v_result_having_any || v_condition;
                END IF;
            END IF;
        END IF;
    END LOOP;

    IF v_result_where_all <> '' AND v_result_where_any <> '' THEN
        v_final_where := '(' || v_result_where_all || ') OR (' || v_result_where_any || ')';
    ELSIF v_result_where_all <> '' THEN
        v_final_where := v_result_where_all;
    ELSIF v_result_where_any <> '' THEN
        v_final_where := v_result_where_any;
    END IF;

    IF v_result_having_all <> '' AND v_result_having_any <> '' THEN
        v_final_having := '(' || v_result_having_all || ') OR (' || v_result_having_any || ')';
    ELSIF v_result_having_all <> '' THEN
        v_final_having := v_result_having_all;
    ELSIF v_result_having_any <> '' THEN
        v_final_having := v_result_having_any;
    END IF;

    IF v_final_where <> '' AND v_final_having <> '' THEN
        RETURN '(' || v_final_where || ') AND (' || v_final_having || ')';
    ELSIF v_final_where <> '' THEN
        RETURN v_final_where;
    ELSE
        RETURN v_final_having;
    END IF;
END;
$function$;