CREATE OR REPLACE PROCEDURE check_for_related_records(
    IN json_input JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql AS $$
DECLARE
    entity_name_var TEXT;
    record_id INT;
    associated_tables JSONB;
    table_entry JSONB;
    table_name TEXT;
    where_clause TEXT;
    count_result INT;
BEGIN
    -- Extract values from JSON input
    entity_name_var := json_input->>'entity_name';
    record_id := (json_input->>'record_id')::INT;

    -- Initialize the result as an empty JSON object if it's not provided
    IF result IS NULL THEN
        result := '{}'::JSONB;
    END IF;

    -- Get associated tables from master_entities
    SELECT m.associated_tables
    INTO associated_tables
    FROM master_entities m
    WHERE m.entity_name = entity_name_var;

    -- Ensure associated_tables is a valid JSON array
    IF associated_tables IS NULL OR jsonb_typeof(associated_tables) <> 'array' THEN
        RAISE NOTICE 'No related records found or associated_tables is invalid.';
        RETURN;
    END IF;

    -- Loop through each entry in the associated_tables array
    FOR table_entry IN SELECT * FROM jsonb_array_elements(associated_tables) LOOP
        table_name := table_entry->>'table';
        where_clause := table_entry->>'where_clause';

        -- Validate table_name to prevent SQL injection
        IF table_name !~ '^[a-zA-Z0-9_]+$' THEN
            RAISE EXCEPTION 'Invalid table name: %. Only alphanumeric and underscore allowed.', table_name;
        END IF;

        -- Validate where_clause pattern - must contain $1 placeholder
        IF where_clause !~ '\$1' THEN
            RAISE EXCEPTION 'Invalid where_clause for table %: must contain $1 placeholder', table_name;
        END IF;

        -- Additional security: validate where_clause doesn't contain dangerous keywords
        IF where_clause ~* '(DROP|ALTER|CREATE|TRUNCATE|GRANT|REVOKE|;)' THEN
            RAISE EXCEPTION 'Dangerous SQL keywords detected in where_clause for table %', table_name;
        END IF;

        RAISE NOTICE 'Checking table: %, Where Clause: %', table_name, where_clause;

        -- Dynamically execute the query with the where_clause and bind the record_id
        BEGIN
            EXECUTE format('SELECT COUNT(*) FROM %I WHERE %s', table_name, where_clause)
            INTO count_result
            USING record_id;

            RAISE NOTICE 'Count result for table %: %', table_name, count_result;

            IF count_result > 0 THEN
                -- Add the associated table and count to the result JSONB object
                result := jsonb_set(
                    result,
                    ARRAY[table_name],
                    to_jsonb(count_result),
                    TRUE
                );
            END IF;

        EXCEPTION
            WHEN undefined_table THEN
                RAISE WARNING 'Table % does not exist, skipping', table_name;
            WHEN syntax_error THEN
                RAISE EXCEPTION 'Syntax error in where_clause for table %: %. Clause: %',
                    table_name, SQLERRM, where_clause;
            WHEN OTHERS THEN
                RAISE EXCEPTION 'Error querying table % with where_clause "%": %',
                    table_name, where_clause, SQLERRM;
        END;
    END LOOP;

    -- Check if the result JSONB object is empty
    IF jsonb_typeof(result) = 'object' AND NOT EXISTS (SELECT 1 FROM jsonb_each(result)) THEN
        RAISE NOTICE 'No related records found.';
    ELSE
        RAISE NOTICE 'Related records: %', result::TEXT;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error in check_for_related_records for entity % (record_id %): %',
            entity_name_var, record_id, SQLERRM;
END;
$$;