chatbot/erp-bridge/sql/chatbot_api_catalog_images.sql

-- chatbot_api.catalog_images
-- Returns product image URLs for a given model pattern.
-- base_url is read from public.config where item = 'File_Url'.
-- full_url = base_url || apppicture_path

CREATE OR REPLACE FUNCTION chatbot_api.catalog_images(
    p_model TEXT,
    p_limit INT DEFAULT 10
)
RETURNS JSON
LANGUAGE sql
STABLE
AS $$
    SELECT json_build_object(
        'base_url', (
            SELECT item_value FROM public.config WHERE item = 'File_Url' LIMIT 1
        ),
        'images', COALESCE(
            (
                SELECT json_agg(row)
                FROM (
                    SELECT json_build_object(
                        'model',           c.model,
                        'apppicture_path', spp.apppicture_path,
                        'full_url',        (
                            SELECT item_value FROM public.config WHERE item = 'File_Url' LIMIT 1
                        ) || spp.apppicture_path
                    ) AS row
                    FROM shop.shop_product_picture spp
                    JOIN shop.shop_product sp ON spp.product_id = sp.product_id
                    JOIN public.catalog c ON sp.caf_serial_no = c.serial_no
                    WHERE c.model ILIKE '%' || p_model || '%'
                      AND spp.apppicture_path IS NOT NULL
                    LIMIT p_limit
                ) sub
            ),
            '[]'::json
        )
    );
$$;

GRANT EXECUTE ON FUNCTION chatbot_api.catalog_images(TEXT, INT) TO chatbot_readonly;