--
-- Test json foreign data wrapper.
--

-- Settings to make the result deterministic
SET datestyle = "ISO, YMD";


-- Install json_fdw
CREATE EXTENSION json_fdw;

CREATE SERVER json_server FOREIGN DATA WRAPPER json_fdw;


-- validator tests
CREATE FOREIGN TABLE test_validator_filename_missing () 
	SERVER json_server; -- ERROR

CREATE FOREIGN TABLE test_validator_invalid_option () 
	SERVER json_server 
	OPTIONS(filename 'data.json', bad_option_name '1'); -- ERROR


-- data conversion tests
CREATE FOREIGN TABLE json_data (id int8, type char(20), name text, 
	birthdate date, actions int[], "position.lat" float, "position.lon" float, 
	"position.address.country" varchar(50), last_update timestamp,
	last_update_tz timestamp with time zone
	) SERVER json_server OPTIONS(filename '@abs_srcdir@/data/data.json');

SELECT id, type, name FROM json_data ORDER BY id;

SELECT id, name, birthdate FROM json_data WHERE type = 'person' ORDER BY id;

SELECT id, "position.lat" AS lat, "position.lon" AS lon, 
	"position.address.country" AS country, last_update 
	FROM json_data WHERE type = 'resturaunt' ORDER BY id;

SELECT id, type, birthdate, last_update, "position.lon" as lon 
	FROM json_data WHERE type = 'invalid_record' ORDER BY id;

SELECT last_update_tz AT TIME ZONE 'UTC' FROM json_data 
	WHERE last_update_tz IS NOT NULL;


-- max error count test
CREATE FOREIGN TABLE test_skip_broken_on (a integer, b integer) 
	SERVER json_server 
	OPTIONS (filename '@abs_srcdir@/data/data_broken.json', max_error_count '1');

SELECT * FROM test_skip_broken_on ORDER BY a;

CREATE FOREIGN TABLE test_skip_broken_off (a integer, b integer) 
	SERVER json_server 
	OPTIONS (filename '@abs_srcdir@/data/data_broken.json', max_error_count '0');

SELECT * FROM test_skip_broken_off; -- ERROR


-- error scenarios
CREATE FOREIGN TABLE test_missing_file () SERVER json_server 
	OPTIONS (filename '@abs_srcdir@/data/missing_file.json');

SELECT * FROM test_missing_file; -- ERROR

CREATE FOREIGN TABLE test_string_length_check (type CHAR(6)) SERVER json_server
	OPTIONS (filename '@abs_srcdir@/data/data.json');

SELECT * FROM test_string_length_check; -- ERROR

CREATE FOREIGN TABLE test_int_range_check (id int4) SERVER json_server
	OPTIONS (filename '@abs_srcdir@/data/data.json');

SELECT * FROM test_int_range_check; -- ERROR

CREATE FOREIGN TABLE test_decimal_range_check ("position.lat" decimal(3, 2))
	SERVER json_server OPTIONS (filename '@abs_srcdir@/data/data.json');

SELECT * FROM test_decimal_range_check; -- ERROR