DROP FUNCTION IF EXISTS public.replace_placeholders(JSONB);
CREATE OR REPLACE FUNCTION public.replace_placeholders(input JSONB)
RETURNS TEXT
LANGUAGE plpgsql
AS $function$
DECLARE
    query_text TEXT;
    param_values JSONB;
    new_row JSONB;
    old_row JSONB;
    query_results JSONB;
    placeholder TEXT;
    placeholder_substr TEXT;
    extracted_value TEXT;
    regex_value1 TEXT[];
    regex_value2 TEXT[];
    query_key TEXT;
    field_key TEXT;
BEGIN
    -- Extract query_text and param_values from the JSON input
    query_text := input ->> 'query_text';
    param_values := input -> 'param_values';
    new_row := input -> 'new_row'; -- optional

    -- Validate input
    IF query_text IS NULL THEN
        RAISE EXCEPTION 'query_text is required';
    END IF;

    -- Extract placeholders matching pattern $[a-zA-Z0-9_\.]+
    SELECT ARRAY(
        SELECT DISTINCT regexp_matches(query_text, '\$[a-zA-Z0-9_\.]+', 'g')::TEXT
    ) INTO regex_value1;

    RAISE NOTICE 'Found placeholders: %', regex_value1;

    IF array_length(regex_value1, 1) > 0 THEN
        FOREACH placeholder IN ARRAY regex_value1
        LOOP
            -- Clean up placeholder
            placeholder := replace(placeholder, '{', '');
            placeholder := replace(placeholder, '}', '');
            extracted_value := NULL;

            -- Validate placeholder format (security check)
            IF placeholder !~ '^\$[a-zA-Z0-9_\.]+$' THEN
                RAISE EXCEPTION 'Invalid placeholder format: %. Only alphanumeric, underscore, and dot allowed.', placeholder;
            END IF;

            -- Handle $prefill_data.*
            IF placeholder LIKE '$prefill_data.%' THEN
                placeholder_substr := substring(placeholder FROM '\$prefill_data\.(.+)');

                -- Validate nested key format
                IF placeholder_substr !~ '^[a-zA-Z0-9_\.]+$' THEN
                    RAISE EXCEPTION 'Invalid key format in $prefill_data: %', placeholder_substr;
                END IF;

                IF param_values IS NULL THEN
                    RAISE EXCEPTION 'param_values is required for placeholder: %', placeholder;
                END IF;

                extracted_value := param_values #>> string_to_array(placeholder_substr, '.');

                IF extracted_value IS NULL THEN
                    RAISE WARNING 'Missing value for placeholder: %, defaulting to NULL', placeholder;
                    extracted_value := 'NULL';
                ELSE
                    -- Validate extracted value doesn't contain SQL injection patterns
                    IF extracted_value ~* '(;|--|\bDROP\b|\bALTER\b|\bCREATE\b|\bTRUNCATE\b|\bEXEC\b|\bEXECUTE\b)' THEN
                        RAISE EXCEPTION 'Potentially dangerous value detected for placeholder %: %', placeholder, extracted_value;
                    END IF;
                END IF;

            -- Handle $session_user_id
            ELSIF placeholder = '$session_user_id' THEN
                IF new_row IS NULL THEN
                    RAISE EXCEPTION 'new_row is required for placeholder: %', placeholder;
                END IF;

                extracted_value := new_row #>> '{user_id}';

                IF extracted_value IS NULL THEN
                    RAISE WARNING 'Missing user_id in new_row, defaulting to NULL';
                    extracted_value := 'NULL';
                ELSE
                    -- Validate it's a number (user_id should be numeric)
                    IF extracted_value !~ '^\d+$' THEN
                        RAISE EXCEPTION 'Invalid user_id value: %. Must be numeric.', extracted_value;
                    END IF;
                END IF;

            -- Handle direct fields from new_row
            ELSIF placeholder LIKE '$%' AND new_row IS NOT NULL THEN
                placeholder_substr := substring(placeholder FROM '\$(.+)');

                -- Validate field key format
                IF placeholder_substr !~ '^[a-zA-Z0-9_\.]+$' THEN
                    RAISE EXCEPTION 'Invalid field key format: %', placeholder_substr;
                END IF;

                IF new_row ? placeholder_substr THEN
                    extracted_value := new_row ->> placeholder_substr;

                    IF extracted_value IS NULL THEN
                        extracted_value := 'NULL';
                    ELSE
                        -- Validate extracted value
                        IF extracted_value ~* '(;|--|\bDROP\b|\bALTER\b|\bCREATE\b|\bTRUNCATE\b|\bEXEC\b|\bEXECUTE\b)' THEN
                            RAISE EXCEPTION 'Potentially dangerous value detected for field %: %', placeholder_substr, extracted_value;
                        END IF;
                    END IF;
                ELSE
                    RAISE WARNING 'Field % not found in new_row, defaulting to NULL', placeholder_substr;
                    extracted_value := 'NULL';
                END IF;

            -- Unrecognized placeholder
            ELSE
                RAISE EXCEPTION 'Unrecognized placeholder pattern: %', placeholder;
            END IF;

            RAISE NOTICE 'Replacing placeholder: % with value: %', placeholder, extracted_value;

            -- Use quote_literal for proper SQL escaping, but handle NULL specially
            IF extracted_value = 'NULL' THEN
                query_text := replace(query_text, placeholder, 'NULL');
            ELSE
                query_text := replace(query_text, placeholder, quote_literal(extracted_value));
            END IF;
        END LOOP;
    END IF;

    RETURN query_text;

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error in replace_placeholders: %', SQLERRM;
END;
$function$;
