LOAD 'chdb_hook';

\set ECHO errors
\set oid8 OID8
SELECT current_setting('server_version_num')::int < 190000 AS pg18 \gset
\if :pg18
-- Postgres 19 added oid8; xid8 stands in for it before then.
\set oid8 XID8
\endif
\set ECHO all

/****************************************************************************/
-- Numbers.
CREATE TABLE numbers (
    u   UUID           NOT NULL,
    i2  INT2           NOT NULL,
    i4  INT4           NOT NULL,
    i8  INT8               NULL,
    num numeric            NULL,
    np  numeric(32)        NULL,
    nps numeric(12, 6)     NULL,
    f4  float4         NOT NULL,
    f8  float8             NULL,
    b   bool           NOT NULL,
    o   OID            NOT NULL,
    o8  :oid8          NOT NULL
);

INSERT INTO numbers
VALUES ('00000000-0000-0000-0000-000000000000', 0, 0, 0, 0, 0, 0, 0, 0, false, 0, '0')
     , ('6ba7b810-9dad-11d1-80b4-00c04fd430c8', 0, 0, NULL, NULL, NULL, NULL, 0, NULL, false, 0, '0')
     , ('B82601F4-2FF0-4F05-99E4-2CAAC200BA0F', -32768, -2147483648, -9223372036854775808, -987654321.123456789, -987654321, -123456.545675, -1.1, -98.68, false, 0, '0')
     , ('A411B3DC-76c7-4D5D-92B2-3ab802504f1f', 32767, 2147483647, 9223372036854775807, 987654321.123456789, 987654321, 123456.545675, 1.1, 98.68, true, 0, '18446744073709551615')
     , ('00000000-0000-0000-0000-000000000000', 42, 42, 42, NULL, NULL, NULL, 'nan', 'nan', true, 0, '0')
     , ('00000000-0000-0000-0000-000000000000', -42, -42, -42, NULL, NULL, NULL, '-infinity', '-infinity', true, 0, '0')
     , ('00000000-0000-0000-0000-000000000000', 42, 42, 42, NULL, NULL, NULL, 'infinity', 'infinity', true, 0, '0')
;

-- Execute round-trip to all supported formats. Protobuf reads a Nullable
-- field holding zero back as NULL, so the zeroed int8 and float8 do not
-- survive: https://github.com/chdb-io/chdb-core/issues/152
CREATE TABLE numbers2 (LIKE numbers INCLUDING ALL);
\set from_table numbers
\set to_table numbers2
\set output_file numbers.tmp
\i test/utils/round-trip-formats.sql

/****************************************************************************/
-- Number Arrays.
CREATE TABLE number_arrays (
    u   UUID[]           NOT NULL,
    i2  INT2[]           NOT NULL,
    i4  INT4[]           NOT NULL,
    i8  INT8[]           NOT NULL,
    num numeric[]        NOT NULL,
    np  numeric(32)[]    NOT NULL,
    nps numeric(12, 6)[] NOT NULL,
    f4  float4[]         NOT NULL,
    f8  float8[]         NOT NULL,
    b   bool[]           NOT NULL,
    o   OID[]            NOT NULL,
    o8  :oid8[]          NOT NULL
);

INSERT INTO number_arrays
VALUES ('{00000000-0000-0000-0000-000000000000}', '{0}', '{0}', '{0}', '{0}', '{0}', '{0}', '{0}', '{0}', '{false}', '{0}', '{0}')
     , ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')
     , ('{6ba7b810-9dad-11d1-80b4-00c04fd430c8, NULL}', '{-32768,32767}', '{-2147483648,2147483647}', '{-9223372036854775808,9223372036854775807}', '{-987654321.123456789,987654321.123456789}', '{-987654321,987654321}', '{-123456.545675,123456.545675}', '{-1.1,1.1}', '{-98.68,98.68}', '{false,true}', '{0, 0}', '{0,18446744073709551615}')
     , ('{B82601F4-2FF0-4F05-99E4-2CAAC200BA0F, A411B3DC-76c7-4D5D-92B2-3ab802504f1f}', '{0, NULL}', '{NULL, 0}', '{NULL}', '{0, NULL}', '{NULL, 0}', '{NULL}', '{0,NULL}', '{NULL,0}', '{false,NULL}', '{NULL}', '{0,NULL}')
;

-- Execute round-trip to all supported formats. Protobuf has no null in a
-- repeated field, so the arrays carrying one come back short.
CREATE TABLE number_arrays2 (LIKE number_arrays INCLUDING ALL);
\set from_table number_arrays
\set to_table number_arrays2
\i test/utils/round-trip-formats.sql

/****************************************************************************/
-- Numeric for integers wider than bigint, UInt64 among them.
CREATE TABLE wide_ints (
    i128 numeric NOT NULL,
    u128 numeric NOT NULL,
    i256 numeric NOT NULL,
    u256 numeric NOT NULL,
    u64  numeric NOT NULL
);

\set wide_structure 'i128 Int128, u128 UInt128, i256 Int256, u256 UInt256, u64 UInt64'

INSERT INTO wide_ints
VALUES (0, 0, 0, 0, 0)
     , (170141183460469231731687303715884105727, 340282366920938463463374607431768211455, 57896044618658097711785492504343953926634992332820282019728792003956564819967, 115792089237316195423570985008687907853269984665640564039457584007913129639935, 18446744073709551615)
     , (-170141183460469231731687303715884105728, 42, -57896044618658097711785492504343953926634992332820282019728792003956564819968, 42, 42)
;

CREATE TABLE wide_ints2 (LIKE wide_ints INCLUDING ALL);
COPY wide_ints TO 'file:///tmp/numbers.tmp' (format 'TabSeparated', structure :'wide_structure');
COPY wide_ints2 FROM 'file:///tmp/numbers.tmp' (format 'TabSeparated', structure :'wide_structure');

SELECT * FROM wide_ints2 ORDER BY i128;

/****************************************************************************/
-- BFloat16 keeps the leading 16 bits of a float4, so a write drops the low
-- mantissa bits.
CREATE TABLE bfloats (f FLOAT4 NOT NULL);

INSERT INTO bfloats
VALUES (0), (1.5), (-2.25), (1.1), (3.4028235e38), ('infinity'), ('-infinity'), ('nan');

CREATE TABLE bfloats2 (LIKE bfloats INCLUDING ALL);
COPY bfloats TO 'file:///tmp/numbers.tmp' (format 'TabSeparated', structure 'f BFloat16');
COPY bfloats2 FROM 'file:///tmp/numbers.tmp' (format 'TabSeparated', structure 'f BFloat16');

SELECT f FROM bfloats2 ORDER BY f;

\set ECHO errors
\! rm -rf /tmp/numbers.tmp 2> /dev/null || true
