Briven/packages/studio/pg-meta/test/query/table-row-query.test.ts

import { afterAll, beforeAll, describe, expect, test } from 'vitest'

import pgMeta from '../../src/index'
import { Filter, Sort } from '../../src/query'
import { getDefaultOrderByColumns, getTableRowsSql } from '../../src/query/table-row-query'
import { cleanupRoot, createTestDatabase } from '../db/utils'

beforeAll(async () => {
  // Any global setup if needed
})

afterAll(async () => {
  await cleanupRoot()
})

type TestDb = Awaited<ReturnType<typeof createTestDatabase>>

const withTestDatabase = (name: string, fn: (db: TestDb) => Promise<void>) => {
  test(name, async () => {
    const db = await createTestDatabase()
    try {
      await fn(db)
    } finally {
      await db.cleanup()
    }
  })
}

describe('Table Row Query', () => {
  describe('getDefaultOrderByColumns', () => {
    test('should return empty array when no primary keys and no columns exist', () => {
      const result = getDefaultOrderByColumns({
        primary_keys: [],
        columns: [],
      })
      expect(result).toEqual([])
    })

    test('should exclude specified columns when determining default sort', () => {
      const table = {
        primary_keys: [{ name: 'id' }],
        columns: [
          {
            name: 'id',
            data_type: 'integer',
            format: 'int4',
            ordinal_position: 1,
          },
          {
            name: 'name',
            data_type: 'text',
            format: 'text',
            ordinal_position: 2,
          },
        ],
      } as any

      const result = getDefaultOrderByColumns(table, { excludedColumns: ['id'] })
      expect(result).toEqual(['name'])
    })
  })

  describe('getTableRowsSql', () => {
    withTestDatabase('should handle array of enums correctly', async (db) => {
      // Create an enum type and a table with an array of that enum
      await db.executeQuery(`
        -- Create enum type
        CREATE TYPE status_type AS ENUM ('pending', 'active', 'completed', 'canceled');

        -- Create table with array of enums
        CREATE TABLE test_enum_array (
          id SERIAL PRIMARY KEY,
          name TEXT,
          status status_type, -- Regular enum column
          history status_type[] -- Array of enums
        );

        -- Insert test data with various enum array values
        INSERT INTO test_enum_array (name, status, history) VALUES
          ('Item 1', 'active', ARRAY['pending', 'active']::status_type[]),
          ('Item 2', 'completed', ARRAY['pending', 'active', 'completed']::status_type[]),
          ('Item 3', 'canceled', ARRAY['active', 'canceled']::status_type[]),
          ('Item 4', 'pending', NULL);
      `)

      // Get table metadata
      const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
      const tables = tablesZod.parse(await db.executeQuery(tablesSql))
      const testTable = tables.find((table) => table.name === 'test_enum_array')

      expect(testTable).toBeDefined()

      // Generate SQL
      const sql = getTableRowsSql({
        table: testTable!,
        page: 1,
        limit: 10,
      })

      // Verify SQL generation with snapshot
      expect(sql).toMatchInlineSnapshot(`
        "with _base_query as (select * from public.test_enum_array order by test_enum_array.id asc nulls last limit 10 offset 0)
          select id,case
                when octet_length(name::text) > 10240 
                then left(name::text, 10240) || '...'
                else name::text
              end as name,status,
                case 
                  when octet_length(history::text) > 10240 
                  then
                    case
                      when array_ndims(history) = 1
                      then
                        (select array_cat(history[1:50]::text[], array['...']::text[]))::text[]
                      else
                        history[1:50]::text[]
                    end
                  else history::text[]
                end
               from _base_query;"
      `)

      // Execute the generated SQL and verify the results
      const queryResult = await db.executeQuery(sql)
      expect(queryResult).toMatchInlineSnapshot(`
        [
          {
            "history": [
              "pending",
              "active",
            ],
            "id": 1,
            "name": "Item 1",
            "status": "active",
          },
          {
            "history": [
              "pending",
              "active",
              "completed",
            ],
            "id": 2,
            "name": "Item 2",
            "status": "completed",
          },
          {
            "history": [
              "active",
              "canceled",
            ],
            "id": 3,
            "name": "Item 3",
            "status": "canceled",
          },
          {
            "history": null,
            "id": 4,
            "name": "Item 4",
            "status": "pending",
          },
        ]
      `)

      // Test filtering on enum array values
      const filtersStatusSQL = getTableRowsSql({
        table: testTable!,
        filters: [
          { column: 'status', operator: '=', value: `active` }, // Contains 'active' using text pattern matching
        ],
        page: 1,
        limit: 10,
      })

      expect(filtersStatusSQL).toMatchInlineSnapshot(`
        "with _base_query as (select * from public.test_enum_array where status = 'active' order by test_enum_array.id asc nulls last limit 10 offset 0)
          select id,case
                when octet_length(name::text) > 10240 
                then left(name::text, 10240) || '...'
                else name::text
              end as name,status,
                case 
                  when octet_length(history::text) > 10240 
                  then
                    case
                      when array_ndims(history) = 1
                      then
                        (select array_cat(history[1:50]::text[], array['...']::text[]))::text[]
                      else
                        history[1:50]::text[]
                    end
                  else history::text[]
                end
               from _base_query;"
      `)
      // Execute the filtered query
      const filtersStatusResult = await db.executeQuery(filtersStatusSQL)
      expect(filtersStatusResult).toMatchInlineSnapshot(`
        [
          {
            "history": [
              "pending",
              "active",
            ],
            "id": 1,
            "name": "Item 1",
            "status": "active",
          },
        ]
      `)

      const filtersHistorySQL = getTableRowsSql({
        table: testTable!,
        filters: [
          { column: 'history', operator: '=', value: `ARRAY['active']::status_type[]` }, // Contains 'active' using text pattern matching
        ],
        page: 1,
        limit: 10,
      })

      expect(filtersHistorySQL).toMatchInlineSnapshot(`
        "with _base_query as (select * from public.test_enum_array where history = ARRAY['active']::status_type[] order by test_enum_array.id asc nulls last limit 10 offset 0)
          select id,case
                when octet_length(name::text) > 10240 
                then left(name::text, 10240) || '...'
                else name::text
              end as name,status,
                case 
                  when octet_length(history::text) > 10240 
                  then
                    case
                      when array_ndims(history) = 1
                      then
                        (select array_cat(history[1:50]::text[], array['...']::text[]))::text[]
                      else
                        history[1:50]::text[]
                    end
                  else history::text[]
                end
               from _base_query;"
      `)
      const filtersHistoryResult = await db.executeQuery(filtersHistorySQL)
      expect(filtersHistoryResult).toMatchInlineSnapshot(`[]`)
    })

    withTestDatabase('should handle array columns correctly', async (db) => {
      await db.executeQuery(`
        CREATE TABLE test_array_table (
          id SERIAL PRIMARY KEY,
          name TEXT,
          tags TEXT[] -- Array of text
        );

        -- Insert test data with array values
        INSERT INTO test_array_table (name, tags) VALUES
          ('Item 1', ARRAY['tag1', 'tag2']),
          ('Item 2', ARRAY['tag3']),
          ('Item 3', ARRAY['tag1', 'tag4']);
      `)

      // Get table metadata
      const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
      const tables = tablesZod.parse(await db.executeQuery(tablesSql))
      const testTable = tables.find((table) => table.name === 'test_array_table')

      expect(testTable).toBeDefined()

      // Generate SQL
      const sql = getTableRowsSql({
        table: testTable!,
        page: 1,
        limit: 10,
      })
      expect(sql).toMatchInlineSnapshot(`
        "with _base_query as (select * from public.test_array_table order by test_array_table.id asc nulls last limit 10 offset 0)
          select id,case
                when octet_length(name::text) > 10240 
                then left(name::text, 10240) || '...'
                else name::text
              end as name,
                case 
                  when octet_length(tags::text) > 10240 
                  then
                    case
                      when array_ndims(tags) = 1
                      then
                        (select array_cat(tags[1:50]::text[], array['...']::text[]))::text[]
                      else
                        tags[1:50]::text[]
                    end
                  else tags::text[]
                end
               from _base_query;"
      `)

      const queryResult = await db.executeQuery(sql)
      expect(queryResult.length).toBe(3)
      expect(queryResult).toMatchInlineSnapshot(`
        [
          {
            "id": 1,
            "name": "Item 1",
            "tags": [
              "tag1",
              "tag2",
            ],
          },
          {
            "id": 2,
            "name": "Item 2",
            "tags": [
              "tag3",
            ],
          },
          {
            "id": 3,
            "name": "Item 3",
            "tags": [
              "tag1",
              "tag4",
            ],
          },
        ]
      `)
    })

    withTestDatabase('should generate basic SELECT SQL for a table', async (db) => {
      // Create test table and insert data
      await db.executeQuery(`
        CREATE TABLE test_sql_gen (
          id SERIAL PRIMARY KEY,
          name TEXT,
          description TEXT,
          created_at TIMESTAMP DEFAULT NOW()
        );

        -- Insert test data
        INSERT INTO test_sql_gen (name, description) VALUES
          ('Row 1', 'Description 1'),
          ('Row 2', 'Description 2'),
          ('Row 3', 'Description 3');
      `)

      // Get table metadata
      const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
      const tables = tablesZod.parse(await db.executeQuery(tablesSql))
      const testTable = tables.find((table) => table.name === 'test_sql_gen')

      expect(testTable).toBeDefined()

      // Generate SQL
      const sql = getTableRowsSql({
        table: testTable!,
        page: 1,
        limit: 10,
      })

      // Verify SQL generation with snapshot
      expect(sql).toMatchInlineSnapshot(
        `
        "with _base_query as (select * from public.test_sql_gen order by test_sql_gen.id asc nulls last limit 10 offset 0)
          select id,case
                when octet_length(name::text) > 10240 
                then left(name::text, 10240) || '...'
                else name::text
              end as name,case
                when octet_length(description::text) > 10240 
                then left(description::text, 10240) || '...'
                else description::text
              end as description,created_at from _base_query;"
      `
      )

      // E2E Test: Execute the SQL and verify results
      const queryResult = await db.executeQuery(sql)
      expect(queryResult.length).toBe(3)
      expect(queryResult.map((row: any) => row.name)).toEqual(['Row 1', 'Row 2', 'Row 3'])
      expect(queryResult.map((row: any) => row.description)).toEqual([
        'Description 1',
        'Description 2',
        'Description 3',
      ])
    })

    withTestDatabase(
      'should truncate large arrays to maxArraySize elements if their size is > maxCharacters',
      async (db) => {
        // Create test table with array column
        await db.executeQuery(`
        CREATE TABLE test_large_array_table (
          id SERIAL PRIMARY KEY,
          name TEXT,
          large_array TEXT[] -- Will hold a very large array
        );

        -- Insert test data with a large array (>10KB)
        -- Create an array with 1000 elements to ensure it exceeds 10KB
        INSERT INTO test_large_array_table (name, large_array) VALUES
          ('Large Array Item', (SELECT array_agg('element_' || i) FROM generate_series(1, 1000) i)),
          ('Large Array Small items', (SELECT array_agg('' || i) FROM generate_series(1, 100) i)),
          ('Normal Array Item', ARRAY['tag1', 'tag2', 'tag3']);
      `)

        // Get table metadata
        const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
        const tables = tablesZod.parse(await db.executeQuery(tablesSql))
        const testTable = tables.find((table) => table.name === 'test_large_array_table')

        expect(testTable).toBeDefined()

        // Generate SQL with lower maxCharacters and maxArraySize limits
        const sql = getTableRowsSql({
          table: testTable!,
          page: 1,
          limit: 10,
          maxCharacters: 2048,
          maxArraySize: 10,
        })

        // Verify the SQL contains the array truncation logic
        expect(sql).toMatchInlineSnapshot(`
          "with _base_query as (select * from public.test_large_array_table order by test_large_array_table.id asc nulls last limit 10 offset 0)
            select id,case
                  when octet_length(name::text) > 2048 
                  then left(name::text, 2048) || '...'
                  else name::text
                end as name,
                  case 
                    when octet_length(large_array::text) > 2048 
                    then
                      case
                        when array_ndims(large_array) = 1
                        then
                          (select array_cat(large_array[1:10]::text[], array['...']::text[]))::text[]
                        else
                          large_array[1:10]::text[]
                      end
                    else large_array::text[]
                  end
                 from _base_query;"
        `)

        // Execute the SQL and verify results
        const queryResult = await db.executeQuery(sql)
        expect(queryResult).toMatchInlineSnapshot(`
          [
            {
              "id": 1,
              "large_array": [
                "element_1",
                "element_2",
                "element_3",
                "element_4",
                "element_5",
                "element_6",
                "element_7",
                "element_8",
                "element_9",
                "element_10",
                "...",
              ],
              "name": "Large Array Item",
            },
            {
              "id": 2,
              "large_array": [
                "1",
                "2",
                "3",
                "4",
                "5",
                "6",
                "7",
                "8",
                "9",
                "10",
                "11",
                "12",
                "13",
                "14",
                "15",
                "16",
                "17",
                "18",
                "19",
                "20",
                "21",
                "22",
                "23",
                "24",
                "25",
                "26",
                "27",
                "28",
                "29",
                "30",
                "31",
                "32",
                "33",
                "34",
                "35",
                "36",
                "37",
                "38",
                "39",
                "40",
                "41",
                "42",
                "43",
                "44",
                "45",
                "46",
                "47",
                "48",
                "49",
                "50",
                "51",
                "52",
                "53",
                "54",
                "55",
                "56",
                "57",
                "58",
                "59",
                "60",
                "61",
                "62",
                "63",
                "64",
                "65",
                "66",
                "67",
                "68",
                "69",
                "70",
                "71",
                "72",
                "73",
                "74",
                "75",
                "76",
                "77",
                "78",
                "79",
                "80",
                "81",
                "82",
                "83",
                "84",
                "85",
                "86",
                "87",
                "88",
                "89",
                "90",
                "91",
                "92",
                "93",
                "94",
                "95",
                "96",
                "97",
                "98",
                "99",
                "100",
              ],
              "name": "Large Array Small items",
            },
            {
              "id": 3,
              "large_array": [
                "tag1",
                "tag2",
                "tag3",
              ],
              "name": "Normal Array Item",
            },
          ]
        `)
      }
    )

    withTestDatabase(
      'should truncate large arrays of jsonb and json to maxArraySize elements if their size is > maxCharacters',
      async (db) => {
        // Create test table with array column
        await db.executeQuery(`
        CREATE TABLE test_large_array_table (
          id SERIAL PRIMARY KEY,
          name TEXT,
          large_array_jsonb jsonb[],
          large_array_json json[]
        );

        -- Insert test data with a large array (>10KB)
        -- Create arrays with JSON objects
        INSERT INTO test_large_array_table (name, large_array_jsonb, large_array_json) VALUES
          (
            'Large Array Item',
            (SELECT array_agg(jsonb_build_object(
              'id', i,
              'name', 'element_' || i,
              'data', jsonb_build_object('value', i * 10, 'active', true)
            )) FROM generate_series(1, 1000) i),
            (SELECT array_agg(json_build_object(
              'id', i,
              'name', 'element_' || i,
              'data', json_build_object('value', i * 10, 'active', true)
            )) FROM generate_series(1, 1000) i)
          ),
          (
            'Large Array Small items',
            (SELECT array_agg(jsonb_build_object(
              'id', i,
              'value', i
            )) FROM generate_series(1, 100) i),
            (SELECT array_agg(json_build_object(
              'id', i,
              'value', i
            )) FROM generate_series(1, 100) i)
          ),
          (
            'Normal Array Item',
            ARRAY[
              '{"id": 1, "tag": "tag1"}'::jsonb,
              '{"id": 2, "tag": "tag2"}'::jsonb,
              '{"id": 3, "tag": "tag3"}'::jsonb
            ],
            ARRAY[
              '{"id": 1, "tag": "tag1"}'::json,
              '{"id": 2, "tag": "tag2"}'::json,
              '{"id": 3, "tag": "tag3"}'::json
            ]
          );
      `)

        // Get table metadata
        const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
        const tables = tablesZod.parse(await db.executeQuery(tablesSql))
        const testTable = tables.find((table) => table.name === 'test_large_array_table')

        expect(testTable).toBeDefined()

        // Generate SQL with lower maxCharacters and maxArraySize limits
        const sql = getTableRowsSql({
          table: testTable!,
          page: 1,
          limit: 10,
          maxCharacters: 2048,
          maxArraySize: 10,
        })

        // Verify the SQL contains the array truncation logic
        expect(sql).toMatchInlineSnapshot(`
          "with _base_query as (select * from public.test_large_array_table order by test_large_array_table.id asc nulls last limit 10 offset 0)
            select id,case
                  when octet_length(name::text) > 2048 
                  then left(name::text, 2048) || '...'
                  else name::text
                end as name,
                  case 
                    when octet_length(large_array_jsonb::text) > 2048 
                    then
                      case
                        when array_ndims(large_array_jsonb) = 1
                        then
                          (select array_cat(large_array_jsonb[1:10]::jsonb[], array['{"truncated": true}'::json]::jsonb[]))::jsonb[]
                        else
                          large_array_jsonb[1:10]::jsonb[]
                      end
                    else large_array_jsonb::jsonb[]
                  end
                ,
                  case 
                    when octet_length(large_array_json::text) > 2048 
                    then
                      case
                        when array_ndims(large_array_json) = 1
                        then
                          (select array_cat(large_array_json[1:10]::json[], array['{"truncated": true}'::json]::json[]))::json[]
                        else
                          large_array_json[1:10]::json[]
                      end
                    else large_array_json::json[]
                  end
                 from _base_query;"
        `)

        // Execute the SQL and verify results
        const queryResult = await db.executeQuery(sql)
        expect(queryResult).toMatchInlineSnapshot(`
      [
        {
          "id": 1,
          "large_array_json": [
            {
              "data": {
                "active": true,
                "value": 10,
              },
              "id": 1,
              "name": "element_1",
            },
            {
              "data": {
                "active": true,
                "value": 20,
              },
              "id": 2,
              "name": "element_2",
            },
            {
              "data": {
                "active": true,
                "value": 30,
              },
              "id": 3,
              "name": "element_3",
            },
            {
              "data": {
                "active": true,
                "value": 40,
              },
              "id": 4,
              "name": "element_4",
            },
            {
              "data": {
                "active": true,
                "value": 50,
              },
              "id": 5,
              "name": "element_5",
            },
            {
              "data": {
                "active": true,
                "value": 60,
              },
              "id": 6,
              "name": "element_6",
            },
            {
              "data": {
                "active": true,
                "value": 70,
              },
              "id": 7,
              "name": "element_7",
            },
            {
              "data": {
                "active": true,
                "value": 80,
              },
              "id": 8,
              "name": "element_8",
            },
            {
              "data": {
                "active": true,
                "value": 90,
              },
              "id": 9,
              "name": "element_9",
            },
            {
              "data": {
                "active": true,
                "value": 100,
              },
              "id": 10,
              "name": "element_10",
            },
            {
              "truncated": true,
            },
          ],
          "large_array_jsonb": [
            {
              "data": {
                "active": true,
                "value": 10,
              },
              "id": 1,
              "name": "element_1",
            },
            {
              "data": {
                "active": true,
                "value": 20,
              },
              "id": 2,
              "name": "element_2",
            },
            {
              "data": {
                "active": true,
                "value": 30,
              },
              "id": 3,
              "name": "element_3",
            },
            {
              "data": {
                "active": true,
                "value": 40,
              },
              "id": 4,
              "name": "element_4",
            },
            {
              "data": {
                "active": true,
                "value": 50,
              },
              "id": 5,
              "name": "element_5",
            },
            {
              "data": {
                "active": true,
                "value": 60,
              },
              "id": 6,
              "name": "element_6",
            },
            {
              "data": {
                "active": true,
                "value": 70,
              },
              "id": 7,
              "name": "element_7",
            },
            {
              "data": {
                "active": true,
                "value": 80,
              },
              "id": 8,
              "name": "element_8",
            },
            {
              "data": {
                "active": true,
                "value": 90,
              },
              "id": 9,
              "name": "element_9",
            },
            {
              "data": {
                "active": true,
                "value": 100,
              },
              "id": 10,
              "name": "element_10",
            },
            {
              "truncated": true,
            },
          ],
          "name": "Large Array Item",
        },
        {
          "id": 2,
          "large_array_json": [
            {
              "id": 1,
              "value": 1,
            },
            {
              "id": 2,
              "value": 2,
            },
            {
              "id": 3,
              "value": 3,
            },
            {
              "id": 4,
              "value": 4,
            },
            {
              "id": 5,
              "value": 5,
            },
            {
              "id": 6,
              "value": 6,
            },
            {
              "id": 7,
              "value": 7,
            },
            {
              "id": 8,
              "value": 8,
            },
            {
              "id": 9,
              "value": 9,
            },
            {
              "id": 10,
              "value": 10,
            },
            {
              "truncated": true,
            },
          ],
          "large_array_jsonb": [
            {
              "id": 1,
              "value": 1,
            },
            {
              "id": 2,
              "value": 2,
            },
            {
              "id": 3,
              "value": 3,
            },
            {
              "id": 4,
              "value": 4,
            },
            {
              "id": 5,
              "value": 5,
            },
            {
              "id": 6,
              "value": 6,
            },
            {
              "id": 7,
              "value": 7,
            },
            {
              "id": 8,
              "value": 8,
            },
            {
              "id": 9,
              "value": 9,
            },
            {
              "id": 10,
              "value": 10,
            },
            {
              "truncated": true,
            },
          ],
          "name": "Large Array Small items",
        },
        {
          "id": 3,
          "large_array_json": [
            {
              "id": 1,
              "tag": "tag1",
            },
            {
              "id": 2,
              "tag": "tag2",
            },
            {
              "id": 3,
              "tag": "tag3",
            },
          ],
          "large_array_jsonb": [
            {
              "id": 1,
              "tag": "tag1",
            },
            {
              "id": 2,
              "tag": "tag2",
            },
            {
              "id": 3,
              "tag": "tag3",
            },
          ],
          "name": "Normal Array Item",
        },
      ]
    `)
      }
    )

    withTestDatabase('should truncate fields to maxCharacters avoid', async (db) => {
      // Create test table with array column
      await db.executeQuery(`
        CREATE TABLE test_large_array_table (
          id SERIAL PRIMARY KEY,
          name TEXT,
          large_array TEXT[] -- Will hold a very large array
        );

        -- Insert test data with a large array (>10KB)
        -- Create an array with 1000 elements to ensure it exceeds 10KB
        INSERT INTO test_large_array_table (name, large_array) VALUES
          ('Normal Array Item', ARRAY['tag1', 'tag2', 'tag3']),
          -- Locally testing with up to 700 Mo in size should work and not raise a JS string alloc size error
          (repeat('A', 5 * 1024 * 1024), ARRAY['tag1', 'tag2', 'tag3']);
      `)

      // Get table metadata
      const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
      const tables = tablesZod.parse(await db.executeQuery(tablesSql))
      const testTable = tables.find((table) => table.name === 'test_large_array_table')

      expect(testTable).toBeDefined()

      // Generate SQL with lower maxCharacters and maxArraySize limits
      const sql = getTableRowsSql({
        table: testTable!,
        page: 1,
        limit: 10,
        maxCharacters: 256,
      })

      // Verify the SQL contains the array truncation logic
      expect(sql).toMatchInlineSnapshot(`
        "with _base_query as (select * from public.test_large_array_table order by test_large_array_table.id asc nulls last limit 10 offset 0)
          select id,case
                when octet_length(name::text) > 256 
                then left(name::text, 256) || '...'
                else name::text
              end as name,
                case 
                  when octet_length(large_array::text) > 256 
                  then
                    case
                      when array_ndims(large_array) = 1
                      then
                        (select array_cat(large_array[1:50]::text[], array['...']::text[]))::text[]
                      else
                        large_array[1:50]::text[]
                    end
                  else large_array::text[]
                end
               from _base_query;"
      `)

      // Execute the SQL and verify results
      const start = performance.now()
      const queryResult = await db.executeQuery(sql)
      const end = performance.now()
      expect(end - start).lessThan(1000)

      expect(queryResult).toMatchInlineSnapshot(`
        [
          {
            "id": 1,
            "large_array": [
              "tag1",
              "tag2",
              "tag3",
            ],
            "name": "Normal Array Item",
          },
          {
            "id": 2,
            "large_array": [
              "tag1",
              "tag2",
              "tag3",
            ],
            "name": "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA...",
          },
        ]
      `)
    })

    withTestDatabase('should generate SQL with filtering', async (db) => {
      // Create test table and insert data
      await db.executeQuery(`
        CREATE TABLE test_sql_filter (
          id SERIAL PRIMARY KEY,
          name TEXT,
          category TEXT
        );

        -- Insert test data with different categories
        INSERT INTO test_sql_filter (name, category) VALUES
          ('Test Item 1', 'A'),
          ('Test Item 2', 'B'),
          ('Test Item 3', 'A'),
          ('Another Item', 'A'),
          ('Different Item', 'C');
      `)

      // Get table metadata
      const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
      const tables = tablesZod.parse(await db.executeQuery(tablesSql))
      const testTable = tables.find((table) => table.name === 'test_sql_filter')

      expect(testTable).toBeDefined()

      // Define filters
      const filters: Filter[] = [
        { column: 'name', operator: '~~', value: 'Test%' },
        { column: 'category', operator: '=', value: 'A' },
      ]

      // Generate SQL with filters
      const sql = getTableRowsSql({
        table: testTable!,
        filters,
        page: 1,
        limit: 10,
      })

      // Verify SQL generation with snapshot
      expect(sql).toMatchInlineSnapshot(
        `
        "with _base_query as (select * from public.test_sql_filter where name::text ~~ 'Test%' and category = 'A' order by test_sql_filter.id asc nulls last limit 10 offset 0)
          select id,case
                when octet_length(name::text) > 10240 
                then left(name::text, 10240) || '...'
                else name::text
              end as name,case
                when octet_length(category::text) > 10240 
                then left(category::text, 10240) || '...'
                else category::text
              end as category from _base_query;"
      `
      )

      // E2E Test: Execute the SQL and verify results
      const queryResult = await db.executeQuery(sql)
      expect(queryResult.length).toBe(2) // Should only get items that match both filters
      expect(queryResult.map((row: any) => row.name)).toEqual(['Test Item 1', 'Test Item 3'])
      expect(queryResult.every((row: any) => row.category === 'A')).toBe(true)
    })

    withTestDatabase('should generate SQL with sorting', async (db) => {
      // Create test table and insert data
      await db.executeQuery(`
        CREATE TABLE test_sql_sort (
          id SERIAL PRIMARY KEY,
          name TEXT,
          value INTEGER
        );

        -- Insert test data with varying values
        INSERT INTO test_sql_sort (name, value) VALUES
          ('Z Item', 10),
          ('A Item', 30),
          ('M Item', 20),
          ('X Item', null);
      `)

      // Get table metadata
      const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
      const tables = tablesZod.parse(await db.executeQuery(tablesSql))
      const testTable = tables.find((table) => table.name === 'test_sql_sort')

      expect(testTable).toBeDefined()

      // Define sorts
      const sorts: Sort[] = [
        { column: 'name', table: 'test_sql_sort', ascending: true, nullsFirst: false },
        { column: 'value', table: 'test_sql_sort', ascending: false, nullsFirst: true },
      ]

      // Generate SQL with sorting
      const sql = getTableRowsSql({
        table: testTable!,
        sorts,
        page: 1,
        limit: 10,
      })

      // Verify SQL generation with snapshot
      expect(sql).toMatchInlineSnapshot(
        `
        "with _base_query as (select * from public.test_sql_sort order by test_sql_sort.name asc nulls last, test_sql_sort.value desc nulls first limit 10 offset 0)
          select id,case
                when octet_length(name::text) > 10240 
                then left(name::text, 10240) || '...'
                else name::text
              end as name,value from _base_query;"
      `
      )

      // E2E Test: Execute the SQL and verify results
      const queryResult = await db.executeQuery(sql)
      expect(queryResult.length).toBe(4)

      // Should be sorted by name (asc) first, then by value (desc, nulls first)
      expect(queryResult.map((row: any) => row.name)).toEqual([
        'A Item',
        'M Item',
        'X Item',
        'Z Item',
      ])

      // The first item (A Item) should have value 30
      expect(queryResult[0].value).toBe(30)

      // Check if the X Item (with null value) is before Z Item (non-null value)
      // due to nullsFirst: true for the value sort
      const xItemIndex = queryResult.findIndex((row: any) => row.name === 'X Item')
      const zItemIndex = queryResult.findIndex((row: any) => row.name === 'Z Item')
      expect(xItemIndex).toBeLessThan(zItemIndex)
    })

    withTestDatabase('should generate SQL for special/quoted column names', async (db) => {
      // Create test table with quoted names and insert data
      await db.executeQuery(`
        CREATE TABLE "test sql spaces" (
          id SERIAL PRIMARY KEY,
          "user name" TEXT,
          "column-with-dashes" TEXT,
          "quoted""column" TEXT
        );

        -- Insert test data
        INSERT INTO "test sql spaces" ("user name", "column-with-dashes", "quoted""column") VALUES
          ('User 1', 'Value 1', 'Quoted 1'),
          ('User 2', 'Value 2', 'Quoted 2');
      `)

      // Get table metadata
      const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
      const tables = tablesZod.parse(await db.executeQuery(tablesSql))
      const testTable = tables.find((table) => table.name === 'test sql spaces')

      expect(testTable).toBeDefined()

      // Generate SQL
      const sql = getTableRowsSql({
        table: testTable!,
        page: 1,
        limit: 10,
      })

      // Verify SQL generation with snapshot
      expect(sql).toMatchInlineSnapshot(
        `
        "with _base_query as (select * from public."test sql spaces" order by "test sql spaces".id asc nulls last limit 10 offset 0)
          select id,case
                when octet_length("user name"::text) > 10240 
                then left("user name"::text, 10240) || '...'
                else "user name"::text
              end as "user name",case
                when octet_length("column-with-dashes"::text) > 10240 
                then left("column-with-dashes"::text, 10240) || '...'
                else "column-with-dashes"::text
              end as "column-with-dashes",case
                when octet_length("quoted""column"::text) > 10240 
                then left("quoted""column"::text, 10240) || '...'
                else "quoted""column"::text
              end as "quoted""column" from _base_query;"
      `
      )

      // E2E Test: Execute the SQL and verify results
      const queryResult = await db.executeQuery(sql)
      expect(queryResult.length).toBe(2)
      expect(queryResult.map((row: any) => row['user name'])).toEqual(['User 1', 'User 2'])
      expect(queryResult.map((row: any) => row['column-with-dashes'])).toEqual([
        'Value 1',
        'Value 2',
      ])
      expect(queryResult.map((row: any) => row['quoted"column'])).toEqual(['Quoted 1', 'Quoted 2'])
    })

    withTestDatabase('should generate SQL for tables with large text fields', async (db) => {
      // Create test table with large text fields
      await db.executeQuery(`
        CREATE TABLE test_large_text (
          id SERIAL PRIMARY KEY,
          small_text VARCHAR(100),
          large_text TEXT,
          json_data JSONB
        );

        -- Insert test data including a large text field
        INSERT INTO test_large_text (small_text, large_text, json_data) VALUES
          ('Small text', repeat('Lorem ipsum ', 100), '{"key": "value", "nested": {"data": true}}'),
          ('Another small text', repeat('Dolor sit amet ', 100), '{"array": [1, 2, 3], "bool": false}');
      `)

      // Get table metadata
      const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
      const tables = tablesZod.parse(await db.executeQuery(tablesSql))
      const testTable = tables.find((table) => table.name === 'test_large_text')

      expect(testTable).toBeDefined()

      // Generate SQL
      const sql = getTableRowsSql({
        table: testTable!,
        page: 1,
        limit: 10,
      })

      // Verify SQL generation with snapshot
      expect(sql).toMatchInlineSnapshot(
        `
        "with _base_query as (select * from public.test_large_text order by test_large_text.id asc nulls last limit 10 offset 0)
          select id,case
                when octet_length(small_text::text) > 10240 
                then left(small_text::text, 10240) || '...'
                else small_text::text
              end as small_text,case
                when octet_length(large_text::text) > 10240 
                then left(large_text::text, 10240) || '...'
                else large_text::text
              end as large_text,case
                when octet_length(json_data::text) > 10240 
                then left(json_data::text, 10240) || '...'
                else json_data::text
              end as json_data from _base_query;"
      `
      )

      // E2E Test: Execute the SQL and verify results
      const queryResult = await db.executeQuery(sql)
      expect(queryResult.length).toBe(2)
      expect(queryResult.map((row: any) => row.small_text)).toEqual([
        'Small text',
        'Another small text',
      ])

      expect(queryResult[0].large_text.startsWith('Lorem ipsum')).toBe(true)
      expect(queryResult[1].large_text.startsWith('Dolor sit amet')).toBe(true)

      expect(JSON.parse(queryResult[0].json_data)).toHaveProperty('key', 'value')
      expect(JSON.parse(queryResult[1].json_data)).toHaveProperty('array')
    })

    withTestDatabase('should generate SQL with pagination', async (db) => {
      // Create test table and insert multiple rows for pagination
      await db.executeQuery(`
        CREATE TABLE test_pagination (
          id SERIAL PRIMARY KEY,
          name TEXT
        );

        -- Insert 15 rows for pagination testing
        INSERT INTO test_pagination (name)
        SELECT 'Item ' || i FROM generate_series(1, 15) i;
      `)

      // Get table metadata
      const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
      const tables = tablesZod.parse(await db.executeQuery(tablesSql))
      const testTable = tables.find((table) => table.name === 'test_pagination')

      expect(testTable).toBeDefined()

      // Generate SQL for page 1 (5 items)
      const sql1 = getTableRowsSql({
        table: testTable!,
        page: 1,
        limit: 5,
      })

      expect(sql1).toMatchInlineSnapshot(
        `
        "with _base_query as (select * from public.test_pagination order by test_pagination.id asc nulls last limit 5 offset 0)
          select id,case
                when octet_length(name::text) > 10240 
                then left(name::text, 10240) || '...'
                else name::text
              end as name from _base_query;"
      `
      )

      const page1Result = await db.executeQuery(sql1)
      expect(page1Result.length).toBe(5)
      expect(page1Result.map((row: any) => row.name)).toEqual([
        'Item 1',
        'Item 2',
        'Item 3',
        'Item 4',
        'Item 5',
      ])

      const sql2 = getTableRowsSql({
        table: testTable!,
        page: 2,
        limit: 5,
      })

      // Verify SQL generation for page 2
      expect(sql2).toMatchInlineSnapshot(
        `
        "with _base_query as (select * from public.test_pagination order by test_pagination.id asc nulls last limit 5 offset 5)
          select id,case
                when octet_length(name::text) > 10240 
                then left(name::text, 10240) || '...'
                else name::text
              end as name from _base_query;"
      `
      )

      const page2Result = await db.executeQuery(sql2)
      expect(page2Result.length).toBe(5)
      expect(page2Result.map((row: any) => row.name)).toEqual([
        'Item 6',
        'Item 7',
        'Item 8',
        'Item 9',
        'Item 10',
      ])
    })

    withTestDatabase('should generate SQL for view', async (db) => {
      // Create table and view
      await db.executeQuery(`
        CREATE TABLE test_view_source (
          id SERIAL PRIMARY KEY,
          name TEXT,
          active BOOLEAN
        );

        -- Insert test data
        INSERT INTO test_view_source (name, active) VALUES
          ('Active Item 1', true),
          ('Inactive Item', false),
          ('Active Item 2', true);

        -- Create view that only shows active items
        CREATE VIEW test_sql_view AS
        SELECT id, name, active FROM test_view_source WHERE active = true;
      `)

      // Get view metadata
      const { sql: viewsSql, zod: viewsZod } = pgMeta.views.list()
      const views = viewsZod.parse(await db.executeQuery(viewsSql))
      const testView = views.find((view) => view.name === 'test_sql_view')

      expect(testView).toBeDefined()

      // Generate SQL
      const sql = getTableRowsSql({
        table: testView!,
        page: 1,
        limit: 10,
      })

      // Verify SQL generation with snapshot
      expect(sql).toMatchInlineSnapshot(
        `
        "with _base_query as (select * from public.test_sql_view order by test_sql_view.id asc nulls last limit 10 offset 0)
          select id,case
                when octet_length(name::text) > 10240 
                then left(name::text, 10240) || '...'
                else name::text
              end as name,active from _base_query;"
      `
      )

      // E2E Test: Execute the SQL and verify results
      const queryResult = await db.executeQuery(sql)
      expect(queryResult.length).toBe(2) // Only active items should be in the view
      expect(queryResult.map((row: any) => row.name)).toEqual(['Active Item 1', 'Active Item 2'])
      expect(queryResult.every((row: any) => row.active === true)).toBe(true)
    })

    withTestDatabase('should generate SQL for materialized view', async (db) => {
      // Create table and materialized view
      await db.executeQuery(`
        CREATE TABLE test_mv_source (
          id SERIAL PRIMARY KEY,
          name TEXT,
          value NUMERIC
        );

        -- Insert test data
        INSERT INTO test_mv_source (name, value) VALUES
          ('Item 1', 10.5),
          ('Item 2', -5.25),
          ('Item 3', 20);

        -- Create materialized view that only includes positive values
        CREATE MATERIALIZED VIEW test_sql_mv AS
        SELECT id, name, value FROM test_mv_source WHERE value > 0;
      `)

      // Get materialized view metadata
      const { sql: mvSql, zod: mvZod } = pgMeta.materializedViews.list()
      const materializedViews = mvZod.parse(await db.executeQuery(mvSql))
      const testMv = materializedViews.find((mv) => mv.name === 'test_sql_mv')

      expect(testMv).toBeDefined()

      // Generate SQL
      const sql = getTableRowsSql({
        table: testMv!,
        page: 1,
        limit: 10,
      })

      // Verify SQL generation with snapshot
      expect(sql).toMatchInlineSnapshot(
        `
        "with _base_query as (select * from public.test_sql_mv order by test_sql_mv.id asc nulls last limit 10 offset 0)
          select id,case
                when octet_length(name::text) > 10240 
                then left(name::text, 10240) || '...'
                else name::text
              end as name,value from _base_query;"
      `
      )

      // E2E Test: Execute the SQL and verify results
      const queryResult = await db.executeQuery(sql)
      expect(queryResult.length).toBe(2) // Only items with positive values
      expect(queryResult.map((row: any) => row.name)).toEqual(['Item 1', 'Item 3'])
      expect(queryResult.every((row: any) => row.value > 0)).toBe(true)
    })

    withTestDatabase('should generate SQL for foreign table', async (db) => {
      // Set up a foreign table with the file_fdw extension
      await db.executeQuery(`
        -- Create the extension if it doesn't exist
        CREATE EXTENSION IF NOT EXISTS file_fdw;

        -- Create a foreign server
        DROP SERVER IF EXISTS file_server2 CASCADE;
        CREATE SERVER file_server2 FOREIGN DATA WRAPPER file_fdw;

        -- Create a table to export data from
        CREATE TABLE source_for_foreign_test (
          id SERIAL PRIMARY KEY,
          name TEXT,
          description TEXT
        );

        -- Insert test data
        INSERT INTO source_for_foreign_test (name, description) VALUES
          ('Foreign Item 1', 'Description 1'),
          ('Foreign Item 2', 'Description 2');

        -- Export to CSV for the foreign table
        COPY source_for_foreign_test TO '/tmp/foreign_test2.csv' WITH (FORMAT csv, HEADER);

        -- Create the foreign table
        CREATE FOREIGN TABLE test_sql_foreign (
          id INT,
          name TEXT,
          description TEXT
        ) SERVER file_server2
        OPTIONS (filename '/tmp/foreign_test2.csv', format 'csv', header 'true');
      `)

      // Get foreign table metadata
      const { sql: ftSql, zod: ftZod } = pgMeta.foreignTables.list()
      const foreignTables = ftZod.parse(await db.executeQuery(ftSql))
      const testFt = foreignTables.find((ft) => ft.name === 'test_sql_foreign')

      expect(testFt).toBeDefined()

      // Generate SQL
      const sql = getTableRowsSql({
        table: testFt!,
        page: 1,
        limit: 10,
      })

      // Verify SQL generation with snapshot
      expect(sql).toMatchInlineSnapshot(
        `
        "with _base_query as (select * from public.test_sql_foreign order by test_sql_foreign.id asc nulls last limit 10 offset 0)
          select id,case
                when octet_length(name::text) > 10240 
                then left(name::text, 10240) || '...'
                else name::text
              end as name,case
                when octet_length(description::text) > 10240 
                then left(description::text, 10240) || '...'
                else description::text
              end as description from _base_query;"
      `
      )

      // E2E Test: Execute the SQL and verify results
      const queryResult = await db.executeQuery(sql)
      expect(queryResult).toMatchInlineSnapshot(`
        [
          {
            "description": "Description 1",
            "id": 1,
            "name": "Foreign Item 1",
          },
          {
            "description": "Description 2",
            "id": 2,
            "name": "Foreign Item 2",
          },
        ]
      `)
    })
  })
  withTestDatabase('should handle large multi-dimensional arrays correctly', async (db) => {
    // Create test table with multi-dimensional arrays
    await db.executeQuery(`
      CREATE TABLE public.monitor_data (
        subject_id TEXT,
        "timestamp" TIMESTAMP[],
        "PPG" FLOAT8[][],
        "ACC" FLOAT8[][]
      );
  
      INSERT INTO public.monitor_data (subject_id, "timestamp", "PPG", "ACC")
      VALUES (
        'subject-1',
        ARRAY['2024-01-01 00:00:00'::timestamp, '2024-01-02 00:00:00'::timestamp],
        ARRAY[
          [1.1, 1.2, 1.3, 1.4, 1.5, 1.6],
          [2.1, 2.2, 2.3, 2.4, 2.5, 2.6],
          [3.1, 3.2, 3.3, 3.4, 3.5, 3.6]
        ]::FLOAT8[][],
        ARRAY[
          [4.1, 4.2, 4.3, 4.4, 4.5, 4.6],
          [5.1, 5.2, 5.3, 5.4, 5.5, 5.6],
          [6.1, 6.2, 6.3, 6.4, 6.5, 6.6]
        ]::FLOAT8[][]
      );
  
      INSERT INTO public.monitor_data (subject_id, "timestamp", "PPG", "ACC")
      VALUES (
        'subject-large',
        -- large 1D timestamp array (e.g., 1000 timestamps)
        ARRAY(
          SELECT generate_series('2024-01-01'::timestamp, '2024-01-01'::timestamp + interval '999 minutes', '1 minute')
        ),
        -- large 2D float8 arrays (e.g., 1000 x 6)
        ARRAY(
          SELECT ARRAY[
            random(), random(), random(), random(), random(), random()
          ]::float8[]
          FROM generate_series(1, 1000)
        )::float8[][],
        ARRAY(
          SELECT ARRAY[
            random(), random(), random(), random(), random(), random()
          ]::float8[]
          FROM generate_series(1, 1000)
        )::float8[][]
      );
    `)

    // Get table metadata
    const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
    const tables = tablesZod.parse(await db.executeQuery(tablesSql))
    const testTable = tables.find((table) => table.name === 'monitor_data')

    expect(testTable).toBeDefined()

    // Generate SQL with default settings
    const sql = getTableRowsSql({
      table: testTable!,
      page: 1,
      limit: 10,
    })

    // Verify SQL generation with snapshot
    expect(sql).toMatchInlineSnapshot(`
      "with _base_query as (select * from public.monitor_data order by monitor_data.subject_id asc nulls last limit 10 offset 0)
        select case
              when octet_length(subject_id::text) > 10240 
              then left(subject_id::text, 10240) || '...'
              else subject_id::text
            end as subject_id,
              case 
                when octet_length("timestamp"::text) > 10240 
                then
                  case
                    when array_ndims("timestamp") = 1
                    then
                      (select array_cat("timestamp"[1:50]::text[], array['...']::text[]))::text[]
                    else
                      "timestamp"[1:50]::text[]
                  end
                else "timestamp"::text[]
              end
            ,
              case 
                when octet_length("PPG"::text) > 10240 
                then
                  case
                    when array_ndims("PPG") = 1
                    then
                      (select array_cat("PPG"[1:50]::text[], array['...']::text[]))::text[]
                    else
                      "PPG"[1:50]::text[]
                  end
                else "PPG"::text[]
              end
            ,
              case 
                when octet_length("ACC"::text) > 10240 
                then
                  case
                    when array_ndims("ACC") = 1
                    then
                      (select array_cat("ACC"[1:50]::text[], array['...']::text[]))::text[]
                    else
                      "ACC"[1:50]::text[]
                  end
                else "ACC"::text[]
              end
             from _base_query;"
    `)

    // Execute the SQL and verify results
    const queryResult = await db.executeQuery(sql)
    expect(queryResult.length).toBe(2)

    // Verify the first row (small arrays)
    const smallRow = queryResult.find((row: any) => row.subject_id === 'subject-1')
    expect(smallRow).toBeDefined()
    expect(smallRow.timestamp).toHaveLength(2)
    expect(smallRow.PPG).toHaveLength(3)
    expect(smallRow.PPG[0]).toHaveLength(6)
    expect(smallRow.ACC).toHaveLength(3)
    expect(smallRow.ACC[0]).toHaveLength(6)

    // Verify the second row (large arrays)
    const largeRow = queryResult.find((row: any) => row.subject_id === 'subject-large')
    expect(largeRow).toBeDefined()
    expect(largeRow.timestamp).toHaveLength(51) // Has the extra '...' element
    expect(largeRow.PPG).toHaveLength(50)
    expect(largeRow.PPG[0]).toHaveLength(6)
    expect(largeRow.ACC).toHaveLength(50)
    expect(largeRow.ACC[0]).toHaveLength(6)

    // Test with custom maxArraySize
    const sqlWithCustomSize = getTableRowsSql({
      table: testTable!,
      page: 1,
      limit: 10,
      maxArraySize: 10,
    })

    const customSizeResult = await db.executeQuery(sqlWithCustomSize)
    const largeRowCustom = customSizeResult.find((row: any) => row.subject_id === 'subject-large')
    expect(largeRowCustom.timestamp).toHaveLength(11) // Has the extra '...' element
    expect(largeRowCustom.PPG).toHaveLength(10) // multi-dimentional array are truncated
    expect(largeRowCustom.ACC).toHaveLength(10)
  })

  withTestDatabase('should handle reserved keyword "collation" as column name', async (db) => {
    // Create a table with a column named "collation" (a PostgreSQL reserved keyword)
    await db.executeQuery(`
      CREATE TABLE IF NOT EXISTS "public"."test" (
        id SERIAL PRIMARY KEY,
        "collation" TEXT
      );
      
      DELETE FROM "public"."test";
      INSERT INTO "public"."test" ("collation") 
      VALUES 
        ('value1'),
        ('value2'),
        ('value3');
    `)

    // Get table metadata
    const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
    const tables = tablesZod.parse(await db.executeQuery(tablesSql))
    const testTable = tables.find((table) => table.name === 'test')

    expect(testTable).toBeDefined()

    // Generate SQL - this should properly quote the "collation" column name
    const sql = getTableRowsSql({
      table: testTable!,
      page: 1,
      limit: 100,
    })

    // Verify SQL generation - the "collation" column should be properly quoted
    expect(sql).toMatchInlineSnapshot(`
      "with _base_query as (select * from public.test order by test.id asc nulls last limit 100 offset 0)
        select id,case
              when octet_length("collation"::text) > 10240 
              then left("collation"::text, 10240) || '...'
              else "collation"::text
            end as "collation" from _base_query;"
    `)

    // Execute the generated SQL and verify the results
    const queryResult = await db.executeQuery(sql)
    expect(queryResult.length).toBe(3)
    expect(queryResult[0].collation).toBe('value1')
    expect(queryResult[1].collation).toBe('value2')
    expect(queryResult[2].collation).toBe('value3')

    // Test with ORDER BY on the collation column
    const sqlWithOrder = getTableRowsSql({
      table: testTable!,
      page: 1,
      limit: 100,
      sorts: [{ table: 'test', column: 'collation', ascending: true, nullsFirst: false }],
    })

    // Verify the ORDER BY clause properly quotes the collation column
    expect(sqlWithOrder).toMatchInlineSnapshot(`
      "with _base_query as (select * from public.test order by test."collation" asc nulls last limit 100 offset 0)
        select id,case
              when octet_length("collation"::text) > 10240 
              then left("collation"::text, 10240) || '...'
              else "collation"::text
            end as "collation" from _base_query;"
    `)

    const queryResultWithOrder = await db.executeQuery(sqlWithOrder)
    expect(queryResultWithOrder.length).toBe(3)
    expect(queryResultWithOrder[0].collation).toBe('value1')
    expect(queryResultWithOrder[1].collation).toBe('value2')
    expect(queryResultWithOrder[2].collation).toBe('value3')

    // Test with FILTER on the collation column
    const sqlWithFilter = getTableRowsSql({
      table: testTable!,
      page: 1,
      limit: 100,
      filters: [{ column: 'collation', operator: '=', value: 'value2' }],
    })

    // Verify the WHERE clause properly quotes the collation column
    expect(sqlWithFilter).toMatchInlineSnapshot(`
      "with _base_query as (select * from public.test where "collation" = 'value2' order by test.id asc nulls last limit 100 offset 0)
        select id,case
              when octet_length("collation"::text) > 10240 
              then left("collation"::text, 10240) || '...'
              else "collation"::text
            end as "collation" from _base_query;"
    `)

    const queryResultWithFilter = await db.executeQuery(sqlWithFilter)
    expect(queryResultWithFilter.length).toBe(1)
    expect(queryResultWithFilter[0].collation).toBe('value2')
  })
})