-- 1. The type's comment defines the outbound request.
create type books_api as (
    body text,
    status_code int,
    success boolean,
    error_message text
);

comment on type books_api is 'GET https://books.toscrape.com/
Accept: text/html
@timeout 30s';

-- 2. Use the type as a parameter; NpgsqlRest fills it before the function runs.
create function average_book_price(_response books_api default null)
returns numeric
language plpgsql
as $$
begin
    if not (_response).success then
        raise exception 'fetch failed: %',
            coalesce((_response).error_message, 'status ' || (_response).status_code);
    end if;
    -- parse (_response).body … (XPath/regex) and compute the average
    return round(avg(substring(p::text from '([0-9]+(?:\.[0-9]+)?)')::numeric), 2)
    from unnest(
        xpath('//p[@class="price_color"]/text()',
              xmlparse(document (_response).body))
    ) as p;
end;
$$;

comment on function average_book_price(books_api) is '
HTTP GET /average-book-price
@allow_anonymous
@single';

-- The type is created once (in a migration or schema file):
--   create type books_api as (body text, status_code int, success boolean, error_message text);
--   comment on type books_api is 'GET https://books.toscrape.com/
--   Accept: text/html
--   @timeout 30s';

-- sql/average-book-price.sql
/*
HTTP GET /average-book-price
@allow_anonymous
@single
@param $1 _response books_api
*/
select round(avg(substring(p::text from '([0-9]+(?:\.[0-9]+)?)')::numeric), 2) as avg_price
from unnest(
    xpath('//p[@class="price_color"]/text()',
          xmlparse(document ($1).body))
) as p;

create type weather_api as (body jsonb, success boolean, error_message text);

create type exchange_rate_api as (body jsonb, status_code int, success boolean, error_message text);

comment on type exchange_rate_api is 'GET https://open.er-api.com/v6/latest/{_base_currency}
Accept: application/json
@timeout 10s';

create function get_rates(_base_currency text, _response exchange_rate_api default null)
returns jsonb language sql as $$
  select case when (_response).success then (_response).body
              else jsonb_build_object('error', (_response).error_message) end;
$$;

comment on function get_rates(text, exchange_rate_api) is '
HTTP GET /rates
@allow_anonymous';

comment on type my_api is '@timeout 10s
@retry_delay 1s, 2s, 5s on 429, 503
@cache 5m
GET https://api.example.com/data
Accept: application/json';

create type exchange_rate_api as (body jsonb, status_code int, success boolean, error_message text);
create type crypto_price_api  as (body jsonb, status_code int, success boolean, error_message text);

comment on type exchange_rate_api is 'GET https://open.er-api.com/v6/latest/{_base_currency}
Accept: application/json
@timeout 10s';

comment on type crypto_price_api is 'GET https://api.coingecko.com/api/v3/simple/price?ids={_crypto_ids_csv}&vs_currencies={_vs_currencies_csv}
Accept: application/json
@timeout 10s';

create function get_financial_dashboard(
    _base_currency text,
    _crypto_ids_csv text,
    _vs_currencies_csv text,
    _exchange_rate_response exchange_rate_api,  -- both HTTP calls
    _crypto_response crypto_price_api           -- run in parallel
)
returns json language plpgsql as $$
begin
    return json_build_object(
        'rates',  case when (_exchange_rate_response).success then (_exchange_rate_response).body end,
        'crypto', case when (_crypto_response).success       then (_crypto_response).body end
    );
end;
$$;

comment on function get_financial_dashboard(text, text, text, exchange_rate_api, crypto_price_api) is '
HTTP GET /financial-dashboard
@authorize';

create type api_users  as (body json);
create type api_orders as (body json);
comment on type api_users  is 'GET /api/users';
comment on type api_orders is 'GET /api/orders';

create function dashboard(_users api_users, _orders api_orders)
returns json language sql as $$
  select json_build_object('users', ($1).body, 'orders', ($2).body);
$$;

comment on function dashboard(api_users, api_orders) is '
HTTP GET /dashboard
@authorize';

comment on type weather_api is 'GET https://api.example.com/v1/current?city={_city}
Authorization: Bearer {WEATHER_API_KEY}';

comment on type my_api is 'GET https://api.example.com/data
Authorization: Bearer {_token}';

comment on function get_secure_data(_user_id int, _req my_api, _token text) is '
HTTP GET /secure-data
@authorize
_token = select api_token from user_tokens where user_id = {_user_id}';