dbcli 0.1.0

/// Create a comprehensive test table covering all PostgreSQL types supported by dbcli.
///
/// The returned SQL string contains two parts separated by a semicolon:
///   1. A `DO $$ ... $$` block that idempotently creates the `test_status` ENUM type.
///   2. A `CREATE TABLE IF NOT EXISTS db_json_type_test` statement with one column per
///      supported type category.
///
/// # Prerequisites
/// - pgvector extension must be installed for the `col_vector` column:
///   `CREATE EXTENSION IF NOT EXISTS vector;`
/// - The ENUM creation block handles the "duplicate_object" error gracefully, so the
///   function is safe to call multiple times.
pub fn create_pg_test_table() -> String {
    [
        // -- Step 1: create the test ENUM type idempotently --
        concat!(
            "DO $$ BEGIN\n",
            "    CREATE TYPE test_status AS ENUM ('active', 'pending', 'inactive');\n",
            "EXCEPTION WHEN duplicate_object THEN NULL;\n",
            "END $$",
        ),
        // -- Step 2: create the main test table --
        concat!(
            "CREATE TABLE IF NOT EXISTS db_json_type_test (\n",

            // --- Integer types ---
            // SERIAL acts as auto-increment primary key; maps to INT in dbcli.
            "    id              SERIAL          PRIMARY KEY,\n",
            // SMALLINT (int2) — minimum integer width.
            "    col_smallint    SMALLINT,\n",
            // INTEGER (int4) — standard 32-bit integer.
            "    col_integer     INTEGER,\n",
            // BIGINT (int8) — 64-bit integer for large numbers.
            "    col_bigint      BIGINT,\n",

            // --- Float types ---
            // REAL (float4) — single-precision floating-point.
            "    col_real        REAL,\n",
            // DOUBLE PRECISION (float8) — double-precision floating-point.
            "    col_double      DOUBLE PRECISION,\n",

            // --- Exact numeric ---
            // NUMERIC(12,4) — fixed-precision decimal; dbcli returns it as a JSON String
            // to preserve precision without floating-point rounding.
            "    col_numeric     NUMERIC(12, 4),\n",

            // --- Boolean ---
            "    col_bool        BOOLEAN,\n",

            // --- Text types ---
            // Plain TEXT — unlimited length string.
            "    col_text        TEXT,\n",
            // VARCHAR(255) — variable-length with limit.
            "    col_varchar     VARCHAR(255),\n",
            // CHAR(10) — fixed-length, right-padded with spaces.
            "    col_char        CHAR(10),\n",

            // --- Date / Time types ---
            // DATE — calendar date without time component.
            "    col_date        DATE,\n",
            // TIMESTAMP — local timestamp without time zone.
            "    col_timestamp   TIMESTAMP,\n",
            // TIMESTAMPTZ — timestamp with time zone (stored in UTC).
            "    col_timestamptz TIMESTAMPTZ,\n",
            // TIME — time of day without date.
            "    col_time        TIME,\n",
            // INTERVAL — duration / time span; dbcli returns it as a JSON String.
            "    col_interval    INTERVAL,\n",

            // --- JSON types ---
            // JSON — stored as plain text, parsed on read.
            "    col_json        JSON,\n",
            // JSONB — stored in binary form, supports indexing.
            "    col_jsonb       JSONB,\n",

            // --- Binary ---
            // BYTEA — raw byte sequence; dbcli auto-detects text vs binary and
            // returns a UTF-8 String or a Base64-encoded String respectively.
            "    col_bytea       BYTEA,\n",

            // --- UUID ---
            "    col_uuid        UUID,\n",

            // --- Array types ---
            // All array columns map to the ARRAY handler in dbcli, which returns a JSON Array.
            "    col_int_array   INTEGER[],\n",
            "    col_text_array  TEXT[],\n",
            "    col_float_array FLOAT8[],\n",

            // --- Network types ---
            // INET — host IP address (IPv4 or IPv6) with optional prefix length.
            "    col_inet        INET,\n",
            // CIDR — network address block; dbcli returns it as a JSON String.
            "    col_cidr        CIDR,\n",
            // MACADDR — 6-byte MAC address.
            "    col_macaddr     MACADDR,\n",

            // --- Money ---
            // MONEY — fixed-precision currency; dbcli returns it as a JSON String.
            "    col_money       MONEY,\n",

            // --- XML ---
            // XML — well-formed XML document; dbcli returns it as a JSON String.
            "    col_xml         XML,\n",

            // --- ENUM ---
            // Uses the test_status ENUM type created above.
            // dbcli detects PgTypeKind::Enum and returns the label as a JSON String.
            "    col_enum        test_status,\n",

            // --- Range types ---
            // INT4RANGE — integer range; dbcli parses it into
            // {lower, upper, lower_inc, upper_inc}.
            "    col_int4range   INT4RANGE,\n",
            // TSRANGE — timestamp-without-timezone range.
            "    col_tsrange     TSRANGE,\n",

            // --- Bit-string types ---
            // BIT(8) — fixed-length bit string; dbcli returns it as a binary-digit String.
            "    col_bit         BIT(8),\n",
            // BIT VARYING(16) — variable-length bit string.
            "    col_varbit      BIT VARYING(16),\n",

            // --- Vector (pgvector extension) ---
            // Requires `CREATE EXTENSION IF NOT EXISTS vector;`
            // dbcli parses VECTOR as a JSON Array of f64 values.
            "    col_vector      VECTOR(3)\n",

            ")",
        ),
    ]
    .join(";\n\n")
}

/// Insert one row of representative test data covering every column in `db_json_type_test`.
///
/// Each value is chosen to exercise the corresponding dbcli parser:
/// - Integer maximums to verify int2 / int4 / int8 handling.
/// - NaN-safe floats with enough significant digits.
/// - A NUMERIC value that would lose precision if stored as f64.
/// - `true` for the BOOLEAN column.
/// - A multi-key JSON object for JSON/JSONB.
/// - A printable ASCII byte sequence for BYTEA (auto-decoded as UTF-8 String).
/// - A canonical UUID.
/// - Heterogeneous arrays (int, text, float).
/// - Concrete network addresses for INET / CIDR / MACADDR.
/// - A MONEY literal.
/// - A minimal XML document.
/// - An ENUM label.
/// - Half-open integer and timestamp ranges.
/// - Fixed and variable bit-string literals.
/// - A 3-element pgvector.
pub fn insert_pg_test_data() -> String {
    concat!(
        "INSERT INTO db_json_type_test (\n",
        "    col_smallint, col_integer, col_bigint,\n",
        "    col_real, col_double,\n",
        "    col_numeric,\n",
        "    col_bool,\n",
        "    col_text, col_varchar, col_char,\n",
        "    col_date, col_timestamp, col_timestamptz, col_time, col_interval,\n",
        "    col_json, col_jsonb,\n",
        "    col_bytea,\n",
        "    col_uuid,\n",
        "    col_int_array, col_text_array, col_float_array,\n",
        "    col_inet, col_cidr, col_macaddr,\n",
        "    col_money,\n",
        "    col_xml,\n",
        "    col_enum,\n",
        "    col_int4range, col_tsrange,\n",
        "    col_bit, col_varbit,\n",
        "    col_vector\n",
        ") VALUES (\n",

        // Integer types: SMALLINT max, INTEGER max, BIGINT max
        "    32767, 2147483647, 9223372036854775807,\n",

        // Float types: REAL with 6 significant digits, DOUBLE with full precision
        "    3.14159, 2.718281828459045,\n",

        // NUMERIC: value that cannot be represented exactly in float64
        "    12345.6789,\n",

        // BOOLEAN
        "    true,\n",

        // Text types: TEXT, VARCHAR, CHAR (10-char padded value)
        "    'Hello dbcli!', 'VARCHAR test value', 'CHAR_PAD  ',\n",

        // Date/Time types
        "    '2025-06-15',\n",
        "    '2025-06-15 14:30:00',\n",
        "    '2025-06-15 14:30:00+08:00',\n",
        "    '14:30:00',\n",
        "    '2 hours 30 minutes',\n",

        // JSON / JSONB: objects with mixed value types
        "    '{\"key\": \"value\", \"number\": 42}',\n",
        "    '{\"tags\": [\"rust\", \"dbcli\"], \"active\": true}',\n",

        // BYTEA: ASCII bytes for \"Hello\" — dbcli should decode this as a UTF-8 String
        "    E'\\\\x48656C6C6F',\n",

        // UUID: canonical lowercase form
        "    'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',\n",

        // Array types: integer array, text array, float8 array
        "    ARRAY[1, 2, 3, 4, 5],\n",
        "    ARRAY['hello', 'world', 'test'],\n",
        "    ARRAY[1.1, 2.2, 3.3],\n",

        // Network types: host address, network block, MAC address
        "    '192.168.1.1',\n",
        "    '10.0.0.0/8',\n",
        "    '08:00:2b:01:02:03',\n",

        // MONEY: currency literal (locale-dependent formatting)
        "    '$1234.56',\n",

        // XML: minimal well-formed document
        "    '<root><item>test</item></root>',\n",

        // ENUM: must match one of 'active', 'pending', 'inactive'
        "    'active',\n",

        // Range types: half-open integer range [1,100), half-open ts range
        "    '[1, 100)',\n",
        "    '[2025-01-01 00:00:00, 2025-12-31 23:59:59)',\n",

        // Bit-string types: fixed 8-bit and variable up-to-16-bit literals
        "    B'10101010',\n",
        "    B'1100110011',\n",

        // VECTOR(3): pgvector literal — three-element float vector
        "    '[0.1, 0.2, 0.3]'\n",

        ")",
    )
    .to_string()
}