/**
 * Tests for TanStack Query Adapter
 *
 * Tests the query key generation, SQL utilities, and QueryAdapter class.
 */

import { describe, it, expect, vi, beforeEach, afterEach } from 'vitest'
import {
  normalizeSQL,
  createQueryKey,
  extractTablesFromSQL,
  getMutationType,
  isReadOnlyQuery,
  createQueryAdapter,
  createCollectionQueryOptions,
  createCollectionMutationOptions,
} from './adapter'
import type { BaseRecord, Collection } from './types'

// ============================================================================
// SQL Utility Tests
// ============================================================================

describe('normalizeSQL', () => {
  it('should trim whitespace', () => {
    expect(normalizeSQL('  SELECT * FROM users  ')).toBe('SELECT * FROM users')
  })

  it('should collapse multiple spaces', () => {
    expect(normalizeSQL('SELECT  *   FROM    users')).toBe('SELECT * FROM users')
  })

  it('should normalize parentheses spacing', () => {
    expect(normalizeSQL('INSERT INTO users ( name, email )')).toBe('INSERT INTO users (name, email)')
  })

  it('should normalize comma spacing', () => {
    expect(normalizeSQL('SELECT id,   name,email')).toBe('SELECT id, name, email')
  })

  it('should handle newlines', () => {
    const sql = `
      SELECT *
      FROM users
      WHERE id = 1
    `
    expect(normalizeSQL(sql)).toBe('SELECT * FROM users WHERE id = 1')
  })

  it('should handle tabs', () => {
    expect(normalizeSQL('SELECT\t*\tFROM\tusers')).toBe('SELECT * FROM users')
  })
})

describe('createQueryKey', () => {
  it('should create a basic query key', () => {
    const key = createQueryKey('mydb', 'SELECT * FROM users')
    expect(key).toEqual(['postgres', 'mydb', 'SELECT * FROM users'])
  })

  it('should include params in the key', () => {
    const key = createQueryKey('mydb', 'SELECT * FROM users WHERE id = $1', [123])
    expect(key).toEqual(['postgres', 'mydb', 'SELECT * FROM users WHERE id = $1', 123])
  })

  it('should include multiple params', () => {
    const key = createQueryKey('mydb', 'SELECT * FROM users WHERE name = $1 AND age = $2', ['Alice', 30])
    expect(key).toEqual(['postgres', 'mydb', 'SELECT * FROM users WHERE name = $1 AND age = $2', 'Alice', 30])
  })

  it('should normalize SQL in the key', () => {
    const key = createQueryKey('mydb', '  SELECT  *  FROM  users  ')
    expect(key).toEqual(['postgres', 'mydb', 'SELECT * FROM users'])
  })

  it('should handle empty params array', () => {
    const key = createQueryKey('mydb', 'SELECT * FROM users', [])
    expect(key).toEqual(['postgres', 'mydb', 'SELECT * FROM users'])
  })
})

describe('extractTablesFromSQL', () => {
  it('should extract table from SELECT query', () => {
    expect(extractTablesFromSQL('SELECT * FROM users')).toEqual(['users'])
  })

  it('should extract table from INSERT query', () => {
    expect(extractTablesFromSQL('INSERT INTO users (name) VALUES ($1)')).toEqual(['users'])
  })

  it('should extract table from UPDATE query', () => {
    expect(extractTablesFromSQL('UPDATE users SET name = $1')).toEqual(['users'])
  })

  it('should extract table from DELETE query', () => {
    expect(extractTablesFromSQL('DELETE FROM users WHERE id = $1')).toEqual(['users'])
  })

  it('should extract multiple tables from JOIN', () => {
    const tables = extractTablesFromSQL('SELECT * FROM users JOIN orders ON users.id = orders.user_id')
    expect(tables).toContain('users')
    expect(tables).toContain('orders')
  })

  it('should handle multiple JOINs', () => {
    const sql = `
      SELECT * FROM users
      JOIN orders ON users.id = orders.user_id
      JOIN products ON orders.product_id = products.id
    `
    const tables = extractTablesFromSQL(sql)
    expect(tables).toContain('users')
    expect(tables).toContain('orders')
    expect(tables).toContain('products')
  })

  it('should deduplicate table names', () => {
    const tables = extractTablesFromSQL('SELECT * FROM users WHERE id IN (SELECT user_id FROM users)')
    expect(tables).toEqual(['users'])
  })

  it('should handle case insensitivity', () => {
    expect(extractTablesFromSQL('select * from USERS')).toEqual(['users'])
  })
})

describe('getMutationType', () => {
  it('should detect INSERT', () => {
    expect(getMutationType('INSERT INTO users (name) VALUES ($1)')).toBe('insert')
  })

  it('should detect UPDATE', () => {
    expect(getMutationType('UPDATE users SET name = $1')).toBe('update')
  })

  it('should detect DELETE', () => {
    expect(getMutationType('DELETE FROM users WHERE id = $1')).toBe('delete')
  })

  it('should handle lowercase', () => {
    expect(getMutationType('insert into users (name) values ($1)')).toBe('insert')
    expect(getMutationType('update users set name = $1')).toBe('update')
    expect(getMutationType('delete from users where id = $1')).toBe('delete')
  })

  it('should return unknown for SELECT', () => {
    expect(getMutationType('SELECT * FROM users')).toBe('unknown')
  })

  it('should handle leading whitespace', () => {
    expect(getMutationType('  INSERT INTO users (name) VALUES ($1)')).toBe('insert')
  })
})

describe('isReadOnlyQuery', () => {
  it('should return true for SELECT queries', () => {
    expect(isReadOnlyQuery('SELECT * FROM users')).toBe(true)
    expect(isReadOnlyQuery('select * from users')).toBe(true)
  })

  it('should return true for WITH queries (CTEs)', () => {
    expect(isReadOnlyQuery('WITH active_users AS (SELECT * FROM users) SELECT * FROM active_users')).toBe(true)
  })

  it('should return false for INSERT', () => {
    expect(isReadOnlyQuery('INSERT INTO users (name) VALUES ($1)')).toBe(false)
  })

  it('should return false for UPDATE', () => {
    expect(isReadOnlyQuery('UPDATE users SET name = $1')).toBe(false)
  })

  it('should return false for DELETE', () => {
    expect(isReadOnlyQuery('DELETE FROM users WHERE id = $1')).toBe(false)
  })

  it('should handle leading whitespace', () => {
    expect(isReadOnlyQuery('  SELECT * FROM users')).toBe(true)
  })
})

// ============================================================================
// QueryAdapter Tests
// ============================================================================

describe('QueryAdapter', () => {
  let mockFetch: ReturnType<typeof vi.fn>

  beforeEach(() => {
    mockFetch = vi.fn()
  })

  afterEach(() => {
    vi.restoreAllMocks()
  })

  const createAdapter = (overrides?: Record<string, unknown>) => {
    return createQueryAdapter({
      database: 'testdb',
      fetch: mockFetch,
      ...overrides,
    })
  }

  describe('constructor', () => {
    it('should create an adapter with required config', () => {
      const adapter = createAdapter()
      expect(adapter.database).toBe('testdb')
    })

    it('should use default baseUrl', () => {
      const adapter = createAdapter()
      // Verify by checking a query call
      mockFetch.mockResolvedValue({
        ok: true,
        json: () => Promise.resolve({ rows: [] }),
      })

      adapter.query('SELECT 1')

      expect(mockFetch).toHaveBeenCalledWith(
        expect.stringContaining('https://db.postgres.do/'),
        expect.any(Object)
      )
    })

    it('should use custom baseUrl', () => {
      const adapter = createQueryAdapter({
        database: 'testdb',
        baseUrl: 'https://custom.postgres.do',
        fetch: mockFetch,
      })

      mockFetch.mockResolvedValue({
        ok: true,
        json: () => Promise.resolve({ rows: [] }),
      })

      adapter.query('SELECT 1')

      expect(mockFetch).toHaveBeenCalledWith(
        expect.stringContaining('https://custom.postgres.do/'),
        expect.any(Object)
      )
    })
  })

  describe('query()', () => {
    it('should execute a query', async () => {
      const adapter = createAdapter()
      const mockResponse = { rows: [{ id: 1, name: 'Alice' }] }

      mockFetch.mockResolvedValue({
        ok: true,
        json: () => Promise.resolve(mockResponse),
      })

      const result = await adapter.query('SELECT * FROM users')

      expect(result).toEqual(mockResponse)
      expect(mockFetch).toHaveBeenCalledWith(
        expect.stringContaining('/testdb/query'),
        expect.objectContaining({
          method: 'POST',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({ sql: 'SELECT * FROM users', params: undefined }),
        })
      )
    })

    it('should pass params to the query', async () => {
      const adapter = createAdapter()

      mockFetch.mockResolvedValue({
        ok: true,
        json: () => Promise.resolve({ rows: [] }),
      })

      await adapter.query('SELECT * FROM users WHERE id = $1', [123])

      expect(mockFetch).toHaveBeenCalledWith(
        expect.any(String),
        expect.objectContaining({
          body: JSON.stringify({ sql: 'SELECT * FROM users WHERE id = $1', params: [123] }),
        })
      )
    })

    it('should throw on error response', async () => {
      const adapter = createAdapter()

      mockFetch.mockResolvedValue({
        ok: false,
        json: () => Promise.resolve({ message: 'Database error' }),
      })

      await expect(adapter.query('SELECT * FROM invalid')).rejects.toThrow('Database error')
    })

    it('should handle non-JSON error response', async () => {
      const adapter = createAdapter()

      mockFetch.mockResolvedValue({
        ok: false,
        json: () => Promise.reject(new Error('Not JSON')),
      })

      await expect(adapter.query('SELECT * FROM invalid')).rejects.toThrow('Query failed')
    })
  })

  describe('queryOptions()', () => {
    it('should return query options object', () => {
      const adapter = createAdapter()
      const options = adapter.queryOptions('SELECT * FROM users')

      expect(options).toHaveProperty('queryKey')
      expect(options).toHaveProperty('queryFn')
      expect(options.queryKey).toEqual(['postgres', 'testdb', 'SELECT * FROM users'])
    })

    it('should accept string parameter', () => {
      const adapter = createAdapter()
      const options = adapter.queryOptions('SELECT * FROM users')

      expect(options.queryKey).toEqual(['postgres', 'testdb', 'SELECT * FROM users'])
    })

    it('should accept object parameter with params', () => {
      const adapter = createAdapter()
      const options = adapter.queryOptions({
        sql: 'SELECT * FROM users WHERE id = $1',
        params: [123],
      })

      expect(options.queryKey).toEqual(['postgres', 'testdb', 'SELECT * FROM users WHERE id = $1', 123])
    })

    it('should include staleTime from options', () => {
      const adapter = createAdapter()
      const options = adapter.queryOptions({
        sql: 'SELECT * FROM users',
        staleTime: 30000,
      })

      expect(options.staleTime).toBe(30000)
    })

    it('should use default staleTime', () => {
      const adapter = createQueryAdapter({
        database: 'testdb',
        defaultStaleTime: 10000,
        fetch: mockFetch,
      })
      const options = adapter.queryOptions('SELECT * FROM users')

      expect(options.staleTime).toBe(10000)
    })

    it('should include gcTime from options', () => {
      const adapter = createAdapter()
      const options = adapter.queryOptions({
        sql: 'SELECT * FROM users',
        gcTime: 60000,
      })

      expect(options.gcTime).toBe(60000)
    })

    it('should include refetchInterval from options', () => {
      const adapter = createAdapter()
      const options = adapter.queryOptions({
        sql: 'SELECT * FROM users',
        refetchInterval: 5000,
      })

      expect(options.refetchInterval).toBe(5000)
    })

    it('should include enabled from options', () => {
      const adapter = createAdapter()
      const options = adapter.queryOptions({
        sql: 'SELECT * FROM users',
        enabled: false,
      })

      expect(options.enabled).toBe(false)
    })

    it('queryFn should execute the query', async () => {
      const adapter = createAdapter()
      const mockRows = [{ id: 1, name: 'Alice' }]

      mockFetch.mockResolvedValue({
        ok: true,
        json: () => Promise.resolve({ rows: mockRows }),
      })

      const options = adapter.queryOptions('SELECT * FROM users')
      const result = await options.queryFn()

      expect(result).toEqual(mockRows)
    })

    it('should warn for non-SELECT queries', () => {
      const adapter = createAdapter()
      const warnSpy = vi.spyOn(console, 'warn').mockImplementation(() => {})

      adapter.queryOptions('INSERT INTO users (name) VALUES ($1)')

      expect(warnSpy).toHaveBeenCalledWith(
        expect.stringContaining('queryOptions should be used for SELECT queries')
      )

      warnSpy.mockRestore()
    })
  })

  describe('mutationOptions()', () => {
    it('should return mutation options object', () => {
      const adapter = createAdapter()
      const options = adapter.mutationOptions()

      expect(options).toHaveProperty('mutationKey')
      expect(options).toHaveProperty('mutationFn')
      expect(options.mutationKey).toEqual(['postgres', 'testdb', 'mutation'])
    })

    it('mutationFn should execute the mutation', async () => {
      const adapter = createAdapter()
      const mockResult = { rows: [{ id: 1 }], rowCount: 1 }

      mockFetch.mockResolvedValue({
        ok: true,
        json: () => Promise.resolve(mockResult),
      })

      const options = adapter.mutationOptions()
      const result = await options.mutationFn({
        sql: 'INSERT INTO users (name) VALUES ($1)',
        params: ['Alice'],
      })

      expect(result).toEqual(mockResult)
    })

    it('should call onMutate callback', async () => {
      const adapter = createAdapter()
      const onMutate = vi.fn().mockReturnValue({ previousData: [] })

      mockFetch.mockResolvedValue({
        ok: true,
        json: () => Promise.resolve({ rows: [] }),
      })

      const options = adapter.mutationOptions({ onMutate })

      expect(options.onMutate).toBe(onMutate)
    })

    it('should call onSuccess callback', () => {
      const adapter = createAdapter()
      const onSuccess = vi.fn()

      const options = adapter.mutationOptions({ onSuccess })

      expect(options.onSuccess).toBe(onSuccess)
    })

    it('should call onError callback', () => {
      const adapter = createAdapter()
      const onError = vi.fn()

      const options = adapter.mutationOptions({ onError })

      expect(options.onError).toBe(onError)
    })

    it('should call onSettled callback', () => {
      const adapter = createAdapter()
      const onSettled = vi.fn()

      const options = adapter.mutationOptions({ onSettled })

      expect(options.onSettled).toBe(onSettled)
    })
  })

  describe('getQueryKey()', () => {
    it('should return a query key', () => {
      const adapter = createAdapter()
      const key = adapter.getQueryKey('SELECT * FROM users')

      expect(key).toEqual(['postgres', 'testdb', 'SELECT * FROM users'])
    })

    it('should include params in the key', () => {
      const adapter = createAdapter()
      const key = adapter.getQueryKey('SELECT * FROM users WHERE id = $1', [123])

      expect(key).toEqual(['postgres', 'testdb', 'SELECT * FROM users WHERE id = $1', 123])
    })
  })

  describe('getInvalidationKeysForTables()', () => {
    it('should return database-level invalidation key', () => {
      const adapter = createAdapter()
      const keys = adapter.getInvalidationKeysForTables(['users', 'orders'])

      expect(keys).toEqual([['postgres', 'testdb']])
    })
  })
})

// ============================================================================
// Collection Adapter Tests
// ============================================================================

describe('createCollectionQueryOptions', () => {
  const createMockCollection = <T extends BaseRecord>(items: T[]): Collection<T> => ({
    id: 'test-collection',
    getAll: () => items,
    get: (id) => items.find(item => item.id === id),
    insert: vi.fn().mockImplementation(async (data) => ({ ...data, id: data.id ?? Date.now() } as T)),
    update: vi.fn().mockImplementation(async (id, data) => {
      const existing = items.find(item => item.id === id)
      return { ...existing, ...data } as T
    }),
    delete: vi.fn(),
    subscribe: vi.fn().mockReturnValue(() => {}),
    getSyncState: () => ({
      connected: false,
      initialized: true,
      pendingCount: 0,
    }),
  })

  it('should create query options from collection', () => {
    const items = [{ id: 1, name: 'Alice' }]
    const collection = createMockCollection(items)

    const options = createCollectionQueryOptions({ collection })

    expect(options.queryKey).toEqual(['postgres', 'collection', 'test-collection'])
    expect(options.staleTime).toBe(0)
    expect(options.enabled).toBe(true)
  })

  it('queryFn should return collection items', async () => {
    const items = [{ id: 1, name: 'Alice' }, { id: 2, name: 'Bob' }]
    const collection = createMockCollection(items)

    const options = createCollectionQueryOptions({ collection })
    const result = await options.queryFn()

    expect(result).toEqual(items)
  })

  it('should accept custom staleTime', () => {
    const collection = createMockCollection([])
    const options = createCollectionQueryOptions({ collection, staleTime: 30000 })

    expect(options.staleTime).toBe(30000)
  })

  it('should accept custom gcTime', () => {
    const collection = createMockCollection([])
    const options = createCollectionQueryOptions({ collection, gcTime: 60000 })

    expect(options.gcTime).toBe(60000)
  })

  it('should accept enabled option', () => {
    const collection = createMockCollection([])
    const options = createCollectionQueryOptions({ collection, enabled: false })

    expect(options.enabled).toBe(false)
  })
})

describe('createCollectionMutationOptions', () => {
  const createMockCollection = <T extends BaseRecord>(): Collection<T> => ({
    id: 'test-collection',
    getAll: () => [],
    get: () => undefined,
    insert: vi.fn().mockImplementation(async (data) => ({ ...data, id: data.id ?? 1 } as T)),
    update: vi.fn().mockImplementation(async (id, data) => ({ id, ...data } as T)),
    delete: vi.fn(),
    subscribe: vi.fn().mockReturnValue(() => {}),
    getSyncState: () => ({
      connected: false,
      initialized: true,
      pendingCount: 0,
    }),
  })

  it('should create mutation options from collection', () => {
    const collection = createMockCollection<{ id: number; name: string }>()
    const options = createCollectionMutationOptions(collection)

    expect(options.mutationKey).toEqual(['postgres', 'collection', 'test-collection', 'mutation'])
    expect(options.mutationFn).toBeDefined()
  })

  it('mutationFn should handle insert', async () => {
    const collection = createMockCollection<{ id: number; name: string }>()
    const options = createCollectionMutationOptions(collection)

    await options.mutationFn({ type: 'insert', data: { name: 'Alice' } })

    expect(collection.insert).toHaveBeenCalledWith({ name: 'Alice' })
  })

  it('mutationFn should handle update', async () => {
    const collection = createMockCollection<{ id: number; name: string }>()
    const options = createCollectionMutationOptions(collection)

    await options.mutationFn({ type: 'update', id: 1, data: { name: 'Alice Updated' } })

    expect(collection.update).toHaveBeenCalledWith(1, { name: 'Alice Updated' })
  })

  it('mutationFn should handle delete', async () => {
    const collection = createMockCollection<{ id: number; name: string }>()
    const options = createCollectionMutationOptions(collection)

    await options.mutationFn({ type: 'delete', id: 1, data: {} })

    expect(collection.delete).toHaveBeenCalledWith(1)
  })

  it('mutationFn should throw for update without id', async () => {
    const collection = createMockCollection<{ id: number; name: string }>()
    const options = createCollectionMutationOptions(collection)

    await expect(options.mutationFn({ type: 'update', data: { name: 'Alice' } }))
      .rejects.toThrow('ID is required for update')
  })

  it('mutationFn should throw for delete without id', async () => {
    const collection = createMockCollection<{ id: number; name: string }>()
    const options = createCollectionMutationOptions(collection)

    await expect(options.mutationFn({ type: 'delete', data: {} }))
      .rejects.toThrow('ID is required for delete')
  })

  it('should call onMutate callback', () => {
    const collection = createMockCollection<{ id: number; name: string }>()
    const onMutate = vi.fn()
    const options = createCollectionMutationOptions(collection, { onMutate })

    expect(options.onMutate).toBe(onMutate)
  })

  it('should call onSuccess callback', () => {
    const collection = createMockCollection<{ id: number; name: string }>()
    const onSuccess = vi.fn()
    const options = createCollectionMutationOptions(collection, { onSuccess })

    expect(options.onSuccess).toBeDefined()
  })

  it('should call onError callback', () => {
    const collection = createMockCollection<{ id: number; name: string }>()
    const onError = vi.fn()
    const options = createCollectionMutationOptions(collection, { onError })

    expect(options.onError).toBeDefined()
  })
})