create extension if not exists "uuid-ossp";

-- Main performance test function with all parameter types
create function public.perf_test(
    _records int,
    _text text,
    _int int,
    _bigint bigint,
    _numeric numeric,
    _real real,
    _double double precision,
    _bool bool,
    _date date,
    _timestamp timestamp,
    _timestamptz timestamptz,
    _uuid uuid,
    _json json,
    _jsonb jsonb,
    _int_array int[],
    _text_array text[]
)
returns table(
    row_num int,
    -- text types
    text_val text,
    varchar_val varchar(100),
    char_val char(10),
    -- integer types
    smallint_val smallint,
    int_val int,
    bigint_val bigint,
    -- floating point
    numeric_val numeric(12,4),
    real_val real,
    double_val double precision,
    -- boolean
    bool_val bool,
    -- date/time
    date_val date,
    time_val time,
    timestamp_val timestamp,
    timestamptz_val timestamptz,
    interval_val interval,
    -- special types
    uuid_val uuid,
    json_val json,
    jsonb_val jsonb,
    -- arrays
    int_array_val int[],
    text_array_val text[],
    -- nullable
    nullable_text text,
    nullable_int int
)
stable
language sql
as
$$
select
    i as row_num,
    -- text types
    _text || '_' || i::text as text_val,
    left(_text || '_' || i::text, 100)::varchar(100) as varchar_val,
    lpad(i::text, 10, '0')::char(10) as char_val,
    -- integer types
    (i % 32767)::smallint as smallint_val,
    _int + i as int_val,
    _bigint + i as bigint_val,
    -- floating point
    (_numeric + i + 0.1234)::numeric(12,4) as numeric_val,
    (_real + i * 0.1)::real as real_val,
    (_double + i * 0.001)::double precision as double_val,
    -- boolean
    case when i % 2 = 0 then _bool else not _bool end as bool_val,
    -- date/time
    _date + i as date_val,
    ('12:00:00'::time + (i || ' minutes')::interval)::time as time_val,
    _timestamp + (i || ' hours')::interval as timestamp_val,
    _timestamptz + (i || ' hours')::interval as timestamptz_val,
    (i || ' days')::interval as interval_val,
    -- special types
    case when i = 1 then _uuid
         else uuid_generate_v5(_uuid, i::text) end as uuid_val,
    (_json::jsonb || jsonb_build_object('i', i))::json as json_val,
    _jsonb || jsonb_build_object('row', i) as jsonb_val,
    -- arrays
    _int_array || array[i] as int_array_val,
    _text_array || array[_text || i::text] as text_array_val,
    -- nullable (nulls on even rows)
    case when i % 2 = 1 then _text || '_' || i::text else null end as nullable_text,
    case when i % 2 = 1 then i else null end as nullable_int
from
    generate_series(1, _records) as i
$$;

comment on function public.perf_test(int,text,int,bigint,numeric,real,double precision,bool,date,timestamp,timestamptz,uuid,json,jsonb,int[],text[]) is 'HTTP GET /api/perf-test';

-- ============================================
-- ADDITIONAL BENCHMARK FUNCTIONS
-- All use generate_series() or in-memory computation only
-- No table reads/writes to ensure constant DB response time
-- ============================================

-- SCENARIO 1: POST with JSON body (tests request body parsing)
create function public.perf_post(_records int, _payload jsonb)
returns table(row_num int, echo jsonb, computed text)
stable
language sql
as
$$
select
    i as row_num,
    _payload as echo,
    'row_' || i::text as computed
from generate_series(1, _records) as i
$$;

comment on function public.perf_post(int, jsonb) is 'HTTP POST /api/perf-post';

-- SCENARIO 2: Nested JSON (tests nested object serialization)
create function public.perf_nested(_records int, _depth int)
returns table(row_num int, nested jsonb)
stable
language sql
as
$$
select
    i as row_num,
    case _depth
        when 1 then jsonb_build_object('a', i)
        when 2 then jsonb_build_object('a', jsonb_build_object('b', i))
        when 3 then jsonb_build_object('a', jsonb_build_object('b', jsonb_build_object('c', i)))
        else jsonb_build_object('a', i)
    end as nested
from generate_series(1, _records) as i
$$;

comment on function public.perf_nested(int, int) is 'HTTP GET /api/perf-nested';

-- SCENARIO 3: Minimal overhead baseline
create function public.perf_minimal()
returns table(status text, ts timestamptz)
stable
language sql
as
$$
select 'ok'::text as status, now() as ts
$$;

comment on function public.perf_minimal() is 'HTTP GET /api/perf-minimal';

-- SCENARIO 4: Large payload (tests chunked transfer, buffer handling)
create function public.perf_large_payload(_size_kb int)
returns table(data text)
stable
language sql
as
$$
select repeat('x', _size_kb * 1024) as data
$$;

comment on function public.perf_large_payload(int) is 'HTTP GET /api/perf-large-payload';

-- SCENARIO 5: Many parameters (tests query string parsing)
create function public.perf_many_params(
    _p1 text, _p2 int, _p3 bool, _p4 numeric, _p5 text,
    _p6 text, _p7 int, _p8 bool, _p9 numeric, _p10 text,
    _p11 text, _p12 int, _p13 bool, _p14 numeric, _p15 text,
    _p16 text, _p17 int, _p18 bool, _p19 numeric, _p20 text
)
returns table(param_count int, checksum text)
stable
language sql
as
$$
select 20 as param_count, md5(_p1 || coalesce(_p10, '') || coalesce(_p20, '')) as checksum
$$;

comment on function public.perf_many_params(text,int,bool,numeric,text,text,int,bool,numeric,text,text,int,bool,numeric,text,text,int,bool,numeric,text) is 'HTTP GET /api/perf-many-params';

show max_connections;