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

    -- RAISE NOTICE 'param_values: %, new_row: %, query_text: %', param_values, new_row, query_text;

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

	-- SELECT ARRAY(
 --        SELECT DISTINCT regexp_matches(query_text, '\@[a-zA-Z0-9_\.]+', 'g')::TEXT
 --    ) INTO regex_value2;
	
	RAISE NOTICE 'regex_value1: %', regex_value1;
	
    IF array_length(regex_value1, 1) > 0 THEN
        FOREACH placeholder IN ARRAY regex_value1
        LOOP
            placeholder := replace(placeholder, '{', '');
            placeholder := replace(placeholder, '}', '');
            extracted_value := NULL;

            -- Handle $prefill_data.*
            IF placeholder LIKE '$prefill_data.%' THEN
                placeholder_substr := substring(placeholder FROM '\$prefill_data\.(.+)');
                extracted_value := param_values #>> string_to_array(placeholder_substr, '.');

                IF extracted_value IS NULL THEN
                    RAISE EXCEPTION 'Missing JSONB key: %', placeholder_substr;
                END IF;
            
            -- Handle $session_user_id.*
            ELSIF placeholder = '$session_user_id' THEN
                extracted_value := new_row #>> '{user_id}';

                IF extracted_value IS NULL THEN
                    RAISE EXCEPTION 'Missing JSONB key: %', '$session_user_id';
                END IF;


            -- Handle direct fields from new_row
            ELSIF placeholder LIKE '$%' AND new_row IS NOT NULL THEN
                placeholder_substr := substring(placeholder FROM '\$(.+)');
                IF new_row ? placeholder_substr THEN
                    extracted_value := new_row ->> placeholder_substr;
                ELSE
                    RAISE EXCEPTION 'Field % not found in new_row', placeholder_substr;
                END IF;
            END IF;

            RAISE NOTICE 'placeholder: %, replacing with: %', placeholder, extracted_value;
            query_text := replace(query_text, placeholder, quote_literal(extracted_value));
        END LOOP;
    END IF;
	
   --  IF array_length(regex_value2, 1) > 0 THEN
   --      FOREACH placeholder IN ARRAY regex_value2
   --      LOOP
   --          placeholder := replace(placeholder, '{', '');
   --          placeholder := replace(placeholder, '}', '');
   --          extracted_value := NULL;

			-- RAISE NOTICE 'regex_value2: %', placeholder;
   --          -- Handle @queryN
   --          IF placeholder LIKE '@query' THEN
   --              SELECT
			-- 	  substring(placeholder FROM '@([^[]+)') AS query_key,     -- query1
			-- 	  substring(placeholder FROM '\[([^\]]+)\]') AS field_key;  -- id
			-- 	RAISE EXCEPTION 'query: %', query_key, field_key;
   --              -- extracted_value := param_values #>> string_to_array(placeholder_substr, '.');

   --              -- IF extracted_value IS NULL THEN
   --              --     RAISE EXCEPTION 'Missing JSONB key: %', placeholder_substr;
   --              -- END IF;
   --          END IF;

   --          RAISE NOTICE 'placeholder: %, replacing with: %', placeholder, extracted_value;
   --          query_text := replace(query_text, placeholder, quote_literal(extracted_value));
   --      END LOOP;
   --  END IF;
    RETURN query_text;

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