SET intervalstyle = 'postgres';
CREATE SERVER binary_interval_int_loopback FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'interval_int_test', driver 'binary');
CREATE SERVER http_interval_int_loopback FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'interval_int_test', driver 'http');
CREATE USER MAPPING FOR CURRENT_USER SERVER binary_interval_int_loopback;
CREATE USER MAPPING FOR CURRENT_USER SERVER http_interval_int_loopback;

CREATE SERVER interval_int_admin FOREIGN DATA WRAPPER clickhouse_fdw;
CREATE USER MAPPING FOR CURRENT_USER SERVER interval_int_admin;

\set ECHO errors
SELECT clickhouse_server_version('interval_int_admin') AS ch_version \gset
SELECT (split_part(:'ch_version', '.', 1)::int,
        split_part(:'ch_version', '.', 2)::int) < (23, 5) AS no_ch235 \gset
\if :no_ch235
\echo 'SKIP: Interval columns unsupported prior to ClickHouse 23.5'
\quit
\endif
\set ECHO all

CALL clickhouse_perform('interval_int_admin', 'DROP DATABASE IF EXISTS interval_int_test');
CALL clickhouse_perform('interval_int_admin', 'CREATE DATABASE interval_int_test');
CALL clickhouse_perform('interval_int_admin', $$
    CREATE TABLE interval_int_test.intervals (
        id      Int32              NOT NULL,
        nanos   IntervalNanosecond NOT NULL,
        seconds IntervalSecond     NOT NULL,
        days    IntervalDay        NOT NULL,
        months  IntervalMonth      NOT NULL
    ) ENGINE = MergeTree ORDER BY (id);
$$);

CREATE SCHEMA ival_int_bin;
CREATE SCHEMA ival_int_http;
IMPORT FOREIGN SCHEMA "interval_int_test" FROM SERVER binary_interval_int_loopback INTO ival_int_bin;
IMPORT FOREIGN SCHEMA "interval_int_test" FROM SERVER http_interval_int_loopback INTO ival_int_http;

CREATE FOREIGN TABLE ival_int_bin.ints (
    id      INT    NOT NULL,
    nanos   BIGINT NOT NULL,
    seconds BIGINT NOT NULL,
    days    INT    NOT NULL,
    months  INT    NOT NULL
) SERVER binary_interval_int_loopback OPTIONS (table_name 'intervals');

CREATE FOREIGN TABLE ival_int_http.ints (
    id      INT    NOT NULL,
    nanos   BIGINT NOT NULL,
    seconds BIGINT NOT NULL,
    days    INT    NOT NULL,
    months  INT    NOT NULL
) SERVER http_interval_int_loopback OPTIONS (table_name 'intervals');

-- Integers insert as unit counts
INSERT INTO ival_int_bin.ints VALUES (1, 1500, 90, 3, 14);
INSERT INTO ival_int_http.ints VALUES (2, 2500, 45, 4, 15);

-- Interval inserts as unit count
INSERT INTO ival_int_bin.intervals VALUES (3, '1 microsecond', '1 minute', '1 week', '1 year');
INSERT INTO ival_int_http.intervals VALUES (4, '2 microseconds', '2 minutes', '2 weeks', '2 years');

-- Match remote column names and insert column order.
ALTER FOREIGN TABLE ival_int_http.intervals RENAME nanos TO nanoseconds;
ALTER FOREIGN TABLE ival_int_http.intervals ALTER COLUMN nanoseconds OPTIONS (ADD column_name 'nanos');
INSERT INTO ival_int_http.intervals (months, days, seconds, nanoseconds, id)
VALUES ('-1 year', '-1 week', '-1 minute', '-1 microsecond', 5);

-- Reject values that cannot be represented in destination units.
INSERT INTO ival_int_http.intervals (id, nanoseconds) VALUES (6, '1 month');
INSERT INTO ival_int_http.intervals (id, nanoseconds) VALUES (6, '106752 days');
INSERT INTO ival_int_http.intervals (id, nanoseconds, seconds) VALUES (6, '0', '1 microsecond');

SELECT * FROM ival_int_bin.ints ORDER BY id;
SELECT * FROM ival_int_http.ints ORDER BY id;
SELECT * FROM ival_int_bin.intervals ORDER BY id;
SELECT * FROM ival_int_http.intervals ORDER BY id;

CALL clickhouse_perform('interval_int_admin', 'DROP DATABASE interval_int_test');
DROP USER MAPPING FOR CURRENT_USER SERVER binary_interval_int_loopback;
DROP USER MAPPING FOR CURRENT_USER SERVER http_interval_int_loopback;
DROP USER MAPPING FOR CURRENT_USER SERVER interval_int_admin;
DROP SERVER binary_interval_int_loopback CASCADE;
DROP SERVER http_interval_int_loopback CASCADE;
DROP SERVER interval_int_admin;