import type { QueryCatalog, QueryColumn, QueryForeignKey, QueryFunction } from '../supabase/functions/_backend/private/console_query_sql'
import { PgDialect } from 'drizzle-orm/pg-core'
import { describe, expect, it } from 'vitest'
import { drizzleFrom, finishConsoleQuery, readSqlError, statusForSqlState } from '../supabase/functions/_backend/private/console_query_run'
import { compileConsoleQuery, functionFromCatalogRow, shapeConsoleQueryResult } from '../supabase/functions/_backend/private/console_query_sql'

function column(name: string, udt: string): QueryColumn {
  return { name, udt }
}

function key(name: string, sourceTable: string, sourceColumn: string, targetTable: string, targetColumn: string, unique = false): QueryForeignKey {
  return { name, sourceTable, sourceColumn, targetTable, targetColumn, unique }
}

function fn(name: string, argNames: string[], argTypes: string[], returnsSet = false, returnsComposite = false, defaultCount = 0): QueryFunction {
  return { name, argNames, argTypes, returnsSet, returnsComposite, defaultCount }
}

const catalog: QueryCatalog = {
  columns: new Map([
    ['users', [column('id', 'uuid'), column('email', 'text'), column('onboarding', 'jsonb')]],
    ['orgs', [column('id', 'uuid'), column('name', 'text'), column('customer_id', 'text'), column('created_at', 'timestamptz')]],
    ['stripe_info', [column('customer_id', 'text'), column('paid_at', 'timestamptz'), column('trial_at', 'timestamptz')]],
    ['org_users', [column('user_id', 'uuid'), column('org_id', 'uuid')]],
    ['deploy_history', [column('id', 'int8'), column('app_id', 'text'), column('channel_id', 'int8'), column('version_id', 'int8'), column('created_at', 'timestamptz')]],
    ['app_versions', [column('id', 'int8'), column('name', 'text'), column('app_id', 'text'), column('created_at', 'timestamptz'), column('deleted', 'bool'), column('native_packages', 'jsonb')]],
    ['channels', [column('id', 'int8'), column('name', 'text'), column('app_id', 'text'), column('version', 'int8')]],
    ['audit_logs', [column('org_id', 'uuid'), column('operation', 'text'), column('table_name', 'text'), column('changed_fields', '_text'), column('old_record', 'jsonb'), column('created_at', 'timestamptz'), column('record_id', 'text')]],
    ['role_bindings', [column('principal_id', 'text'), column('role_id', 'uuid'), column('org_id', 'uuid')]],
    ['roles', [column('id', 'uuid'), column('name', 'text')]],
    ['apps', [column('id', 'uuid'), column('app_id', 'text'), column('owner_org', 'uuid'), column('created_by', 'uuid'), column('other_user', 'uuid')]],
    ['group_members', [column('group_id', 'uuid'), column('user_id', 'uuid')]],
  ]),
  foreignKeys: [
    key('orgs_customer_id_fkey', 'orgs', 'customer_id', 'stripe_info', 'customer_id'),
    key('org_users_user_id_fkey', 'org_users', 'user_id', 'users', 'id'),
    key('deploy_history_version_id_fkey', 'deploy_history', 'version_id', 'app_versions', 'id'),
    key('deploy_history_channel_id_fkey', 'deploy_history', 'channel_id', 'channels', 'id'),
    key('channels_version_fkey', 'channels', 'version', 'app_versions', 'id'),
    key('role_bindings_role_id_fkey', 'role_bindings', 'role_id', 'roles', 'id'),
    key('apps_owner_org_fkey', 'apps', 'owner_org', 'orgs', 'id'),
    key('apps_created_by_fkey', 'apps', 'created_by', 'users', 'id'),
    key('apps_other_user_fkey', 'apps', 'other_user', 'users', 'id'),
  ],
  functions: [
    fn('is_platform_admin', [], []),
    fn('is_platform_admin', ['userid'], ['uuid']),
    fn('is_not_deleted', ['email_check'], ['varchar']),
    fn('get_org_members', ['guild_id'], ['uuid'], true, true),
    fn('get_org_members', ['guild_id', 'user_id'], ['uuid', 'uuid'], true, true),
  ],
}

function flat(text: string) {
  return text.replace(/\s+/g, ' ').trim()
}

function compile(request: Parameters<typeof compileConsoleQuery>[1]) {
  const compiled = compileConsoleQuery(catalog, request)
  return { ...compiled, text: flat(compiled.text) }
}

describe('console query sql', () => {
  it('keeps an apostrophe literal in an or filter', () => {
    const compiled = compile({
      kind: 'table',
      name: 'channels',
      args: [],
      operations: [
        { method: 'select', args: ['id, name'] },
        { method: 'or', args: ['name.ilike.%O\'Reilly%,id.in.(42)'] },
      ],
    })
    expect(compiled.text).toContain('t."name" ILIKE $1')
    expect(compiled.text).toContain('OR t."id" = ANY($2::bigint[])')
    expect(compiled.values).toEqual(['%O\'Reilly%', ['42']])
  })

  it('compiles an equality filter and keeps the value out of the SQL text', () => {
    const compiled = compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'select', args: ['id, email'] }, { method: 'eq', args: ['id', 'user-1'] }, { method: 'single', args: [] }],
    })
    expect(compiled.text).toBe('SELECT jsonb_build_object(\'id\', t."id", \'email\', t."email") AS row FROM "public"."users" AS t WHERE t."id" = $1::uuid LIMIT 2')
    expect(compiled.values).toEqual(['user-1'])
    expect(compiled.text).not.toContain('user-1')
    expect(compiled.cardinality).toBe('one')
  })

  it('treats eq and neq null as null checks', () => {
    const missing = compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'select', args: ['*'] }, { method: 'eq', args: ['email', null] }],
    })
    expect(missing.text).toContain('WHERE t."email" IS NULL')
    const present = compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'not', args: ['email', 'is', null] }],
    })
    expect(present.text).toContain('WHERE NOT (t."email" IS NULL)')
  })

  it('compiles in, jsonb equality, and a json path as text', () => {
    const listed = compile({
      kind: 'table',
      name: 'role_bindings',
      args: [],
      operations: [{ method: 'select', args: ['principal_id'] }, { method: 'in', args: ['principal_id', ['a', 'b']] }],
    })
    expect(listed.text).toContain('t."principal_id" = ANY($1::text[])')
    expect(listed.values).toEqual([['a', 'b']])
    const dialect = new PgDialect()
    const bound = dialect.sqlToQuery(drizzleFrom(listed.text, listed.values))
    expect(bound.sql).toContain('= ANY($1::text[])')
    expect(bound.params).toEqual([['a', 'b']])

    const orgs = compile({
      kind: 'table',
      name: 'apps',
      args: [],
      operations: [{ method: 'select', args: ['app_id'] }, { method: 'in', args: ['owner_org', ['11111111-1111-4111-8111-111111111111']] }],
    })
    const oneOrg = dialect.sqlToQuery(drizzleFrom(orgs.text, orgs.values))
    expect(oneOrg.sql).toContain('= ANY($1::uuid[])')
    expect(oneOrg.params).toEqual([['11111111-1111-4111-8111-111111111111']])

    const onboarding = compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'filter', args: ['onboarding', 'eq', '{"step":1}'] }],
    })
    expect(onboarding.text).toContain('t."onboarding" = $1::jsonb')
    expect(onboarding.values).toEqual(['{"step":1}'])

    const path = compile({
      kind: 'table',
      name: 'audit_logs',
      args: [],
      operations: [{ method: 'eq', args: ['old_record->>version', '1.2.3'] }],
    })
    expect(path.text).toContain('(t."old_record"->>\'version\') = $1::text')
    expect(path.values).toEqual(['1.2.3'])
  })

  it('compiles or, and, and not.cd array literals', () => {
    const compiled = compile({
      kind: 'table',
      name: 'audit_logs',
      args: [],
      operations: [{ method: 'or', args: ['operation.eq.INSERT,and(operation.eq.UPDATE,changed_fields.not.cd.{updated_at})'] }],
    })
    expect(compiled.text).toContain('WHERE (t."operation" = $1::text OR (t."operation" = $2::text AND NOT (t."changed_fields" <@ $3::text[])))')
    expect(compiled.values).toEqual(['INSERT', 'UPDATE', '{updated_at}'])
  })

  it('embeds a foreign key column, an inner target table, and stripe_info', () => {
    const history = compile({
      kind: 'table',
      name: 'deploy_history',
      args: [],
      operations: [
        { method: 'select', args: ['*, version:version_id (id, name)'] },
        { method: 'eq', args: ['app_id', 'com.demo.app'] },
        { method: 'like', args: ['version.name', '%demo%'] },
        { method: 'order', args: ['created_at', { ascending: false, nullsFirst: true }] },
        { method: 'range', args: [10, 19] },
      ],
    })
    expect(history.text).toContain('to_jsonb(t) || jsonb_build_object(\'version\', (SELECT jsonb_build_object(\'id\', e1."id", \'name\', e1."name") FROM "public"."app_versions" AS e1 WHERE e1."id" = t."version_id"))')
    expect(history.text).toContain('EXISTS (SELECT 1 FROM "public"."app_versions" AS ef WHERE ef."id" = t."version_id" AND ef."name" LIKE $2::text)')
    expect(history.text).toContain('ORDER BY t."created_at" DESC NULLS FIRST LIMIT 10 OFFSET 10')
    expect(history.values).toEqual(['com.demo.app', '%demo%'])

    const members = compile({
      kind: 'table',
      name: 'org_users',
      args: [],
      operations: [{ method: 'select', args: ['user_id, users!inner(email)'] }, { method: 'eq', args: ['org_id', 'org-1'] }],
    })
    expect(members.text).toContain('\'users\', (SELECT jsonb_build_object(\'email\', e1."email") FROM "public"."users" AS e1 WHERE e1."id" = t."user_id")')
    expect(members.text).toContain('EXISTS (SELECT 1 FROM "public"."users" AS ei WHERE ei."id" = t."user_id")')

    const billing = compile({
      kind: 'table',
      name: 'orgs',
      args: [],
      operations: [{ method: 'select', args: ['created_at, stripe_info(paid_at, trial_at)'] }, { method: 'maybeSingle', args: [] }],
    })
    expect(billing.text).toContain('\'stripe_info\', (SELECT jsonb_build_object(\'paid_at\', e1."paid_at", \'trial_at\', e1."trial_at") FROM "public"."stripe_info" AS e1 WHERE e1."customer_id" = t."customer_id")')
    expect(billing.cardinality).toBe('maybe')

    const channel = compile({
      kind: 'table',
      name: 'channels',
      args: [],
      operations: [{ method: 'select', args: ['version:app_versions!channels_version_fkey(native_packages)'] }],
    })
    expect(channel.text).toContain('FROM "public"."app_versions" AS e1 WHERE e1."id" = t."version"')
  })

  it('keeps an exact count when the page is empty and caps the page size', () => {
    const compiled = compile({
      kind: 'table',
      name: 'audit_logs',
      args: [],
      operations: [{ method: 'select', args: ['*', { count: 'estimated', head: false }] }, { method: 'limit', args: [5000] }],
    })
    expect(compiled.count).toBe(true)
    expect(compiled.text).toContain('SELECT count(*)::bigint AS total_count FROM "public"."audit_logs" AS t')
    expect(compiled.text).toContain('LEFT JOIN LATERAL')
    expect(compiled.text).toContain('LIMIT 1000')
    expect(compiled.text).not.toContain('LIMIT 5000')

    const head = compile({
      kind: 'table',
      name: 'apps',
      args: [],
      operations: [{ method: 'select', args: ['id', { count: 'exact', head: true }] }],
    })
    expect(head.head).toBe(true)
    expect(head.text).toBe('SELECT count(*)::bigint AS total_count FROM "public"."apps" AS t')
  })

  it('compiles upsert, ignore-duplicates, and a filtered jsonb update', () => {
    const upsert = compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'upsert', args: [{ id: 'user-1', email: 'person@example.com' }, { onConflict: 'id' }] }, { method: 'select', args: [] }, { method: 'single', args: [] }],
    })
    expect(upsert.text).toContain('INSERT INTO "public"."users" ("id", "email") VALUES ($1::uuid, $2::text) ON CONFLICT ("id") DO UPDATE SET "email" = EXCLUDED."email" RETURNING *')
    expect(upsert.values).toEqual(['user-1', 'person@example.com'])

    const ignored = compile({
      kind: 'table',
      name: 'group_members',
      args: [],
      operations: [{ method: 'upsert', args: [[{ group_id: 'g', user_id: 'u' }], { onConflict: 'group_id,user_id', ignoreDuplicates: true }] }],
    })
    expect(ignored.text).toContain('ON CONFLICT ("group_id", "user_id") DO NOTHING')
    expect(ignored.text).not.toContain('RETURNING')
    expect(ignored.minimal).toBe(true)
    expect(shapeConsoleQueryResult(ignored, [{ row: { group_id: 'g' } }])).toEqual({ data: null, error: null, count: null, status: 200 })

    const update = compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'update', args: [{ onboarding: { step: 1 } }] }, { method: 'eq', args: ['id', 'user-1'] }, { method: 'filter', args: ['onboarding', 'eq', '{"step":1}'] }],
    })
    expect(update.text).toContain('SET "onboarding" = $3::jsonb')
    expect(update.text).toContain('t."id" = $1::uuid')
    expect(update.text).toContain('t."onboarding" = $2::jsonb')
    expect(update.text).not.toContain('RETURNING')
    expect(update.values).toEqual(['user-1', '{"step":1}', '{"step":1}'])
  })

  it('rejects identifiers and ambiguous relationships', () => {
    expect(() => compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'eq', args: ['id;drop', 'x'] }],
    })).toThrow(/Invalid identifier/)
    expect(() => compile({
      kind: 'table',
      name: 'apps',
      args: [],
      operations: [{ method: 'select', args: ['users(id)'] }],
    })).toThrow(/more than one relationship/)
    expect(() => compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'order', args: ['missing'] }],
    })).toThrow(/does not exist/)
  })

  it('picks the RPC overload from the provided argument names', () => {
    const admin = compile({ kind: 'rpc', name: 'is_platform_admin', args: [], operations: [] })
    expect(admin.text).toBe('SELECT to_jsonb(public."is_platform_admin"()) AS data')
    const probe = compile({ kind: 'rpc', name: 'is_not_deleted', args: [{ email_check: 'nobody@example.com' }], operations: [] })
    expect(probe.text).toBe('SELECT to_jsonb(public."is_not_deleted"("email_check" => $1::varchar)) AS data')
    expect(probe.values).toEqual(['nobody@example.com'])
    const members = compile({ kind: 'rpc', name: 'get_org_members', args: [{ guild_id: 'org-1' }], operations: [] })
    expect(members.text).toContain('jsonb_agg(to_jsonb(q)) FROM (SELECT * FROM public."get_org_members"("guild_id" => $1::uuid) AS q LIMIT 1000) AS q')
    expect(members.returnsSet).toBe(true)
  })

  it('shapes pages, cardinality, and scalar RPC results', () => {
    const many = compile({ kind: 'table', name: 'users', args: [], operations: [{ method: 'select', args: ['id'] }] })
    expect(shapeConsoleQueryResult(many, [{ row: { id: 'a' } }, { row: { id: 'b' } }]).data).toEqual([{ id: 'a' }, { id: 'b' }])

    const single = compile({ kind: 'table', name: 'users', args: [], operations: [{ method: 'single', args: [] }] })
    expect(shapeConsoleQueryResult(single, []).error?.code).toBe('PGRST116')
    expect(shapeConsoleQueryResult(single, []).status).toBe(406)
    expect(shapeConsoleQueryResult(single, [{ row: { id: 'a' } }, { row: { id: 'b' } }]).error?.code).toBe('PGRST116')

    const maybe = compile({ kind: 'table', name: 'users', args: [], operations: [{ method: 'maybeSingle', args: [] }] })
    expect(shapeConsoleQueryResult(maybe, []).data).toBeNull()
    expect(shapeConsoleQueryResult(maybe, []).error).toBeNull()

    const counted = compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'select', args: ['id', { count: 'exact' }] }],
    })
    const page = shapeConsoleQueryResult(counted, [{ row: null, total_count: '4' }])
    expect(page.data).toEqual([])
    expect(page.count).toBe(4)

    const head = compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'select', args: ['id', { count: 'exact', head: true }] }],
    })
    expect(shapeConsoleQueryResult(head, [{ total_count: '8' }])).toEqual({ data: null, error: null, count: 8, status: 200 })

    const rpc = compile({ kind: 'rpc', name: 'is_platform_admin', args: [], operations: [] })
    expect(shapeConsoleQueryResult(rpc, [{ data: false }]).data).toBe(false)
    expect(shapeConsoleQueryResult(rpc, [{ data: '{"step":1}' }]).data).toBe('{"step":1}')
    expect(shapeConsoleQueryResult(rpc, [{ data: { step: 1 } }]).data).toEqual({ step: 1 })
  })

  it('reads function signatures from the array text node-pg leaves unparsed', () => {
    const zero = functionFromCatalogRow({
      name: 'is_platform_admin',
      returns_set: false,
      arg_count: 0,
      arg_names: null,
      arg_modes: null,
      return_kind: 'b',
      arg_types: '{}',
    })
    const named = functionFromCatalogRow({
      name: 'is_not_deleted',
      returns_set: false,
      arg_count: 1,
      arg_names: ['email_check'],
      arg_modes: '{i}',
      return_kind: 'b',
      arg_types: '{varchar}',
    })
    const withOut = functionFromCatalogRow({
      name: 'example',
      returns_set: false,
      arg_count: 1,
      arg_names: '{email_check,result}',
      arg_modes: '{i,o}',
      return_kind: 'b',
      arg_types: '{varchar}',
    })
    expect(zero?.argNames).toEqual([])
    expect(named?.argNames).toEqual(['email_check'])
    expect(named?.argTypes).toEqual(['varchar'])
    expect(withOut?.argNames).toEqual(['email_check'])

    const parsed: QueryCatalog = {
      columns: new Map(),
      foreignKeys: [],
      functions: [zero, named].filter(item => item != null),
    }
    expect(compileConsoleQuery(parsed, { kind: 'rpc', name: 'is_platform_admin', args: [], operations: [] }).text).toContain('public."is_platform_admin"()')
    const deleted = compileConsoleQuery(parsed, { kind: 'rpc', name: 'is_not_deleted', args: [{ email_check: 'nobody@example.com' }], operations: [] })
    expect(deleted.text).toContain('public."is_not_deleted"("email_check" => $1::varchar)')
    expect(deleted.values).toEqual(['nobody@example.com'])
  })

  it('returns every column of a table function and omits defaulted arguments', () => {
    const orgs = functionFromCatalogRow({
      name: 'get_orgs_v7',
      returns_set: true,
      arg_count: 0,
      arg_names: '{gid,name}',
      arg_modes: '{t,t}',
      return_kind: 'p',
      arg_types: '{}',
      default_count: 0,
    })
    const orgsForUser = functionFromCatalogRow({
      name: 'get_orgs_v7',
      returns_set: true,
      arg_count: 1,
      arg_names: '{userid,gid,name}',
      arg_modes: '{i,t,t}',
      return_kind: 'p',
      arg_types: '{uuid}',
      default_count: 0,
    })
    const scalarSet = functionFromCatalogRow({
      name: 'list_ids',
      returns_set: true,
      arg_count: 0,
      arg_names: null,
      arg_modes: null,
      return_kind: 'b',
      arg_types: '{}',
    })
    const permission = functionFromCatalogRow({
      name: 'rbac_check_permission',
      returns_set: false,
      arg_count: 4,
      arg_names: '{p_permission_key,p_org_id,p_app_id,p_channel_id}',
      arg_modes: null,
      return_kind: 'b',
      arg_types: '{text,uuid,varchar,int8}',
      default_count: '3',
    })
    expect(orgs?.argNames).toEqual([])
    expect(orgs?.returnsComposite).toBe(true)
    expect(orgsForUser?.argNames).toEqual(['userid'])
    expect(scalarSet?.returnsComposite).toBe(false)
    expect(permission?.defaultCount).toBe(3)

    const parsed: QueryCatalog = {
      columns: new Map(),
      foreignKeys: [],
      functions: [orgs, orgsForUser, scalarSet, permission].filter(item => item != null),
    }
    const listed = compileConsoleQuery(parsed, { kind: 'rpc', name: 'get_orgs_v7', args: [], operations: [] })
    expect(listed.text).toContain('jsonb_agg(to_jsonb(q)) FROM (SELECT * FROM public."get_orgs_v7"() AS q LIMIT 1000) AS q')
    expect(listed.text).not.toContain('q(val)')
    const ids = compileConsoleQuery(parsed, { kind: 'rpc', name: 'list_ids', args: [], operations: [] })
    expect(ids.text).toContain('jsonb_agg(to_jsonb(q.val)) FROM (SELECT val FROM public."list_ids"() AS q(val) LIMIT 1000) AS q')

    const check = compileConsoleQuery(parsed, {
      kind: 'rpc',
      name: 'rbac_check_permission',
      args: [{ p_permission_key: 'org.read_billing', p_org_id: 'org-1' }],
      operations: [],
    })
    expect(check.text).toContain('public."rbac_check_permission"("p_permission_key" => $1::text, "p_org_id" => $2::uuid)')
    expect(check.text).not.toContain('p_app_id')
    expect(check.text).not.toContain('p_channel_id')
    expect(check.values).toEqual(['org.read_billing', 'org-1'])
    expect(() => compileConsoleQuery(parsed, {
      kind: 'rpc',
      name: 'rbac_check_permission',
      args: [{ p_org_id: 'org-1' }],
      operations: [],
    })).toThrow(/Could not find the function/)
    expect(() => compileConsoleQuery({
      columns: new Map(),
      foreignKeys: [],
      functions: [
        fn('ambiguous', ['a'], ['text']),
        fn('ambiguous', ['a', 'b'], ['text', 'text'], false, false, 1),
      ],
    }, { kind: 'rpc', name: 'ambiguous', args: [{ a: 'x' }], operations: [] })).toThrow(/Could not find the function/)
  })

  it('preserves RPC argument types and pages set-returning functions', () => {
    const apps = fn(
      'get_org_apps_with_last_upload',
      ['p_org_id', 'p_search', 'p_sort_by', 'p_sort_desc', 'p_limit', 'p_offset'],
      ['uuid', 'text', 'text', 'bool', 'int4', 'int4'],
      true,
      true,
      5,
    )
    const uploads = fn(
      'get_bundle_upload_daily_counts',
      ['p_app_ids', 'p_start', 'p_end'],
      ['_varchar', 'timestamptz', 'timestamptz'],
      true,
      true,
    )
    const parsed: QueryCatalog = {
      columns: new Map(),
      foreignKeys: [],
      functions: [apps, uploads],
    }
    const listed = compileConsoleQuery(parsed, {
      kind: 'rpc',
      name: 'get_org_apps_with_last_upload',
      args: [{ p_org_id: 'org-1', p_limit: 10, p_offset: 20 }],
      operations: [],
    })
    expect(listed.text).toContain('"p_limit" => $2::integer')
    expect(listed.text).toContain('"p_offset" => $3::integer')
    expect(listed.text).not.toContain('bigint')
    expect(listed.values).toEqual(['org-1', 10, 20])

    const page = compileConsoleQuery(parsed, {
      kind: 'rpc',
      name: 'get_bundle_upload_daily_counts',
      args: [{ p_app_ids: ['com.demo.app'], p_start: '2026-01-01T00:00:00.000Z', p_end: '2026-01-02T00:00:00.000Z' }],
      operations: [{ method: 'range', args: [0, 999] }],
    })
    expect(flat(page.text)).toContain('jsonb_agg(to_jsonb(q)) FROM (SELECT * FROM public."get_bundle_upload_daily_counts"("p_app_ids" => $1::varchar[], "p_start" => $2::timestamptz, "p_end" => $3::timestamptz) AS q LIMIT 1000) AS q')
    expect(page.text).not.toContain('text[]')
    expect(page.text).not.toContain('OFFSET')
    expect(page.values).toEqual([['com.demo.app'], '2026-01-01T00:00:00.000Z', '2026-01-02T00:00:00.000Z'])

    const next = compileConsoleQuery(parsed, {
      kind: 'rpc',
      name: 'get_bundle_upload_daily_counts',
      args: [{ p_app_ids: ['com.demo.app'], p_start: '2026-01-01T00:00:00.000Z', p_end: '2026-01-02T00:00:00.000Z' }],
      operations: [{ method: 'range', args: [1000, 2999] }],
    })
    expect(next.text).toContain('LIMIT 1000 OFFSET 1000')

    const channel = compile({
      kind: 'table',
      name: 'channels',
      args: [],
      operations: [{ method: 'eq', args: ['id', 1] }],
    })
    expect(channel.text).toContain('t."id" = $1::bigint')
  })

  it('keeps a json string on writes and parses filter in lists', () => {
    const update = compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'update', args: [{ onboarding: '1.0' }] }, { method: 'eq', args: ['id', 'user-1'] }, { method: 'select', args: [] }],
    })
    expect(update.text).toContain('RETURNING')
    expect(update.values).toEqual(['user-1', '"1.0"'])

    const filter = compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'filter', args: ['onboarding', 'eq', '1.0'] }],
    })
    expect(filter.values).toEqual(['1.0'])

    const listed = compile({
      kind: 'table',
      name: 'channels',
      args: [],
      operations: [{ method: 'filter', args: ['id', 'in', '(5,6,7)'] }],
    })
    expect(listed.text).toContain('t."id" = ANY($1::bigint[])')
    expect(listed.values).toEqual([['5', '6', '7']])

    const excluded = compile({
      kind: 'table',
      name: 'channels',
      args: [],
      operations: [{ method: 'not', args: ['id', 'in', '(5,6)'] }],
    })
    expect(excluded.text).toContain('NOT (t."id" = ANY($1::bigint[]))')
    expect(excluded.values).toEqual([['5', '6']])

    const noted = compileConsoleQuery({
      ...catalog,
      functions: [...catalog.functions, fn('save_note', ['payload'], ['jsonb'])],
    }, {
      kind: 'rpc',
      name: 'save_note',
      args: [{ payload: '1.0' }],
      operations: [],
    })
    expect(noted.values).toEqual(['"1.0"'])

    const counted = compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [{ method: 'delete', args: [undefined, { count: 'exact', head: true }] }, { method: 'eq', args: ['id', 'user-1'] }],
    })
    expect(counted.text).toContain('DELETE FROM "public"."users" AS t WHERE t."id" = $1::uuid RETURNING *')
    expect(counted.head).toBe(true)

    const ordered = compile({
      kind: 'table',
      name: 'users',
      args: [],
      operations: [
        { method: 'update', args: [{ email: 'person@example.com' }] },
        { method: 'eq', args: ['id', 'user-1'] },
        { method: 'select', args: [] },
        { method: 'order', args: ['email', { ascending: true }] },
      ],
    })
    expect(ordered.text).toContain('ORDER BY w."email" ASC')
    expect(ordered.text).not.toContain('ORDER BY t.')
  })

  it('does not treat a quoted placeholder as a bind', () => {
    const dialect = new PgDialect()
    const quoted = dialect.sqlToQuery(drizzleFrom('select \'$1\', "$1", $1::uuid', ['real-id']))
    expect(quoted.sql).toContain('\'$1\'')
    expect(quoted.sql).toContain('"$1"')
    expect(quoted.params).toEqual(['real-id'])

    const repeated = dialect.sqlToQuery(drizzleFrom('select $1::text, $1::text', ['a']))
    expect(repeated.params).toEqual(['a', 'a'])

    const dollar = dialect.sqlToQuery(drizzleFrom('select $body$ $1 $body$, $1::text', ['kept']))
    expect(dollar.sql).toContain('$body$ $1 $body$')
    expect(dollar.params).toEqual(['kept'])
  })

  it('maps permission failures by session and rethrows connection failures', () => {
    expect(statusForSqlState('42501', 'authenticated')).toBe(403)
    expect(statusForSqlState('42501', 'anon')).toBe(401)
    expect(statusForSqlState('23505', 'authenticated')).toBe(409)
    expect(statusForSqlState('42P01', 'authenticated')).toBe(400)

    const denied = new Error('Failed query: update users params: person@example.com')
    Object.assign(denied, {
      cause: Object.assign(new Error('permission denied for table users'), {
        name: 'DatabaseError',
        code: '42501',
        severity: 'ERROR',
      }),
    })
    expect(readSqlError(denied)).toEqual({ code: '42501', message: 'permission denied for table users' })

    expect(readSqlError(Object.assign(new Error('write EPIPE'), { code: 'EPIPE' }))).toBeNull()
    for (const code of ['08006', '53300', '57P01']) {
      expect(readSqlError(Object.assign(new Error('connection failed'), { name: 'DatabaseError', code, severity: 'FATAL' }))).toBeNull()
    }
    expect(readSqlError(Object.assign(new Error('canceling statement due to statement timeout'), { name: 'DatabaseError', code: '57014', severity: 'ERROR' }))).toEqual({
      code: '57014',
      message: 'canceling statement due to statement timeout',
    })
  })

  it('throws a rollback signal when singular cardinality fails', () => {
    const ok = { data: [{ id: 'a' }], error: null, count: null, status: 200 }
    expect(finishConsoleQuery(ok)).toBe(ok)

    const failed = {
      data: null,
      error: {
        code: 'PGRST116',
        message: 'JSON object requested, multiple (or no) rows returned',
        details: 'The result contains 2 rows',
      },
      count: null,
      status: 406,
    }
    expect(() => finishConsoleQuery(failed)).toThrowError(expect.objectContaining({
      name: 'ConsoleQueryRollback',
      result: failed,
    }))
  })
})