\c pgtui_tests

drop table blog cascade;
drop table character_types cascade;
drop table integer_types cascade;
drop table float32_types cascade;
drop table float64_types cascade;
drop table big_decimal_types cascade;
drop table date_types cascade;
drop table time_types_no_tz cascade;
drop table time_types_with_tz cascade;
drop table datetime_types cascade;

create extension citext;

create table blog (
    id integer primary key generated always as identity,
    title text not null,
    date date not null,
    content text not null,
    temperature float,
    tags text[],
    published bool not null default true
);

insert into blog (title, date, content, temperature, tags) values
    ('one', '2026-01-01', 'Some content here', '32.0', '{"tag1","tag2"}'),
    ('two', '2026-01-02', 'Some more content', '30.5', '{"tag1","tag3"}'),
    ('three', '2026-01-03', 'Some more content', '31', '{"tag1","tag3"}')
;

update blog set published = 'false' where title = 'three';

-- create tables for each DataType, with fields for every kind of Postgres type that it converts from
-- <https://www.postgresql.org/docs/current/datatype.html>
-- <https://docs.rs/sqlx/latest/sqlx/postgres/types/index.html>

create table character_types (
    one character varying(5),
    two varchar(5),
    three character(5),
    four char(5),
    five bpchar(5),
    six bpchar,
    seven text,
    eight citext
);

insert into character_types values (
    'val1', 'val2', 'val3', 'val4', 'val5', 'val6', 'val7', 'VAL8'
);

create table integer_types (
    one smallint,
    two smallserial,
    three int2,
    four int,
    five serial,
    six int4,
    seven bigint,
    eight bigserial,
    nine int8,
    ten integer
);

insert into integer_types values (
    1, 2, 3, 4, 5, 6, 7, 8, 9, 10
);

create table float32_types (
    one real,
    two float4
);

insert into float32_types values (
    1.1, 2.2
);

create table float64_types (
    one double precision,
    two float8
);

insert into float64_types values (
    1.1, 2.1
);

create table big_decimal_types (
    one decimal,
    two numeric
);

insert into big_decimal_types values (
    3.1, 4.1
);

create table date_types (
    one date
);

insert into date_types values (
    '1814-05-30'
);

create table time_types_no_tz (
    one time,
    two time without time zone,
    three time
);

insert into time_types_no_tz values (
    '04:05', '04:59 PM', '23:10:10.789'
);

-- NOTE: Although included for testing purposes, it will be treated as DataType::Other.
create table time_types_with_tz (
    one timetz,
    two time with time zone
);

insert into time_types_with_tz values (
    '04:05:06-08:00', '04:05:06 PST'
);

create table datetime_types (
    one timestamp,
    two timestamp without time zone,
    three timestamptz,
    four timestamp with time zone
);

insert into datetime_types values (
    '1999-01-08 04:05:06',
    '1999-01-09 04:05:06',
    '1999-01-10 04:05:06 -5:00', -- U.S. Eastern
    '1999-01-11 04:05:06 +6:00'
);

-- byte/bytea
-- interval
-- arrays of various types
