import { describe, it, expect, beforeEach, afterEach } from 'vitest'
import { getDb, setDbPathForTest, withTransaction, healthCheck } from '../../server/utils/db.js'

describe('db', () => {
  beforeEach(() => {
    setDbPathForTest(':memory:')
  })

  afterEach(() => {
    setDbPathForTest(null)
  })

  it('creates tables and returns db', async () => {
    const conn = await getDb()
    expect(conn).toBeDefined()
    expect(conn.run).toBeDefined()
    expect(conn.all).toBeDefined()
    expect(conn.get).toBeDefined()
  })

  it('inserts and reads user', async () => {
    const { run, get } = await getDb()
    const id = 'test-user-id'
    const now = new Date().toISOString()
    await run(
      'INSERT INTO users (id, identifier, password_hash, role, created_at, auth_provider, oidc_issuer, oidc_sub) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
      [id, 'test@test.com', 'salt:hash', 'member', now, 'local', null, null],
    )
    const row = await get('SELECT id, identifier, role FROM users WHERE id = ?', [id])
    expect(row).toEqual({ id, identifier: 'test@test.com', role: 'member' })
  })

  it('inserts and reads poi', async () => {
    const { run, all } = await getDb()
    const id = 'test-poi-id'
    await run(
      'INSERT INTO pois (id, lat, lng, label, icon_type) VALUES (?, ?, ?, ?, ?)',
      [id, 37.7, -122.4, 'Test', 'pin'],
    )
    const rows = await all('SELECT id, lat, lng, label, icon_type FROM pois')
    expect(rows).toHaveLength(1)
    expect(rows[0]).toMatchObject({ id, lat: 37.7, lng: -122.4, label: 'Test', icon_type: 'pin' })
  })

  it('inserts and reads device', async () => {
    const { run, all } = await getDb()
    const id = 'test-device-id'
    await run(
      'INSERT INTO devices (id, name, device_type, vendor, lat, lng, stream_url, source_type, config) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [id, 'Traffic Cam', 'traffic', null, 37.7, -122.4, 'https://example.com/stream', 'mjpeg', null],
    )
    const rows = await all('SELECT id, name, device_type, lat, lng, stream_url, source_type FROM devices')
    expect(rows).toHaveLength(1)
    expect(rows[0]).toMatchObject({ id, name: 'Traffic Cam', device_type: 'traffic', lat: 37.7, lng: -122.4, stream_url: 'https://example.com/stream', source_type: 'mjpeg' })
  })

  describe('withTransaction', () => {
    it('commits on success', async () => {
      const db = await getDb()
      const id = 'test-transaction-id'
      await withTransaction(db, async ({ run, get }) => {
        await run(
          'INSERT INTO users (id, identifier, password_hash, role, created_at, auth_provider, oidc_issuer, oidc_sub) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
          [id, 'transaction@test.com', 'salt:hash', 'member', new Date().toISOString(), 'local', null, null],
        )
        return await get('SELECT id FROM users WHERE id = ?', [id])
      })
      const { get } = await getDb()
      const user = await get('SELECT id FROM users WHERE id = ?', [id])
      expect(user).toBeDefined()
      expect(user.id).toBe(id)
    })

    it('rolls back on error', async () => {
      const db = await getDb()
      const id = 'test-rollback-id'
      try {
        await withTransaction(db, async ({ run }) => {
          await run(
            'INSERT INTO users (id, identifier, password_hash, role, created_at, auth_provider, oidc_issuer, oidc_sub) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
            [id, 'rollback@test.com', 'salt:hash', 'member', new Date().toISOString(), 'local', null, null],
          )
          throw new Error('Test error')
        })
      }
      catch (error) {
        expect(error.message).toBe('Test error')
      }
      const { get } = await getDb()
      const user = await get('SELECT id FROM users WHERE id = ?', [id])
      expect(user).toBeUndefined()
    })

    it('returns callback result', async () => {
      const db = await getDb()
      const result = await withTransaction(db, async () => {
        return { success: true, value: 42 }
      })
      expect(result).toEqual({ success: true, value: 42 })
    })
  })

  describe('healthCheck', () => {
    it('returns healthy when database is accessible', async () => {
      const health = await healthCheck()
      expect(health.healthy).toBe(true)
      expect(health.error).toBeUndefined()
    })

    it('returns unhealthy when database is closed', async () => {
      const db = await getDb()
      await new Promise((resolve, reject) => {
        db.db.close((err) => {
          if (err) reject(err)
          else resolve()
        })
      })
      setDbPathForTest(':memory:')
      const health = await healthCheck()
      expect(health.healthy).toBe(true)
    })
  })
})