import {
  initializeSQLAdapter,
  getConnection,
  disconnectAll,
  query,
  getRow,
  getRows,
  fetchQueryResult,
  getRowCount,
  insertRow,
  updateRow,
  deleteRow,
  withTransaction,
  executeBatch,
} from '../mssql/index'
import sql from 'mssql'

// Mock the mssql module
jest.mock('mssql')

// Mock the error utils
jest.mock('../../../utils/error', () => {
  return {
    ErrorUtils: jest.fn().mockImplementation(() => {
      return {
        errorHandler: jest.fn().mockImplementation(({ error }) => {
          throw error
        }),
      }
    }),
  }
})

describe('MSSQL Adapter', () => {
  // Mock SQL connection pool
  const mockPool = {
    connected: true,
    connect: jest.fn().mockResolvedValue(true),
    close: jest.fn().mockResolvedValue(undefined),
    request: jest.fn(),
  }

  // Mock SQL request
  const mockRequest = {
    input: jest.fn().mockReturnThis(),
    query: jest.fn(),
  }

  // Mock SQL transaction
  const mockTransaction = {
    begin: jest.fn().mockResolvedValue(undefined),
    commit: jest.fn().mockResolvedValue(undefined),
    rollback: jest.fn().mockResolvedValue(undefined),
    request: jest.fn().mockReturnValue(mockRequest),
  }

  beforeEach(() => {
    // Reset all mocks
    jest.clearAllMocks()

    // Setup default mock implementations
    ;(sql.ConnectionPool as unknown as jest.Mock).mockImplementation(
      () => mockPool
    )
    ;(sql.Transaction as unknown as jest.Mock).mockImplementation(
      () => mockTransaction
    )
    mockPool.request.mockReturnValue(mockRequest)
    mockRequest.query.mockResolvedValue({
      recordset: [{ id: 1, name: 'Test' }],
    })
  })

  afterEach(async () => {
    // Clean up any connections
    await disconnectAll()
  })

  describe('initializeSQLAdapter', () => {
    it('should initialize the adapter with configuration', () => {
      const config = {
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
        defaultConfig: {
          server: 'default-server',
          database: 'default_db',
        },
      }

      initializeSQLAdapter(config)

      // No direct way to test this as the config is stored in a private variable
      // We'll test it indirectly through getConnection
      expect(() => initializeSQLAdapter(config)).not.toThrow()
    })
  })

  describe('getConnection', () => {
    it('should create a new connection if one does not exist', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      const connection = await getConnection('users')

      expect(sql.ConnectionPool).toHaveBeenCalledWith({
        server: 'test-server',
        database: 'users_db',
      })
      expect(mockPool.connect).toHaveBeenCalled()
      expect(connection).toBe(mockPool)
    })

    it('should reuse an existing connection if one exists', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      // First call should create a new connection
      await getConnection('users')

      // Reset mocks to verify second call doesn't create a new connection
      jest.clearAllMocks()

      // Second call should reuse the existing connection
      const connection = await getConnection('users')

      expect(sql.ConnectionPool).not.toHaveBeenCalled()
      expect(mockPool.connect).not.toHaveBeenCalled()
      expect(connection).toBe(mockPool)
    })

    it('should use the default config if service type config is not found', async () => {
      initializeSQLAdapter({
        defaultConfig: {
          server: 'default-server',
          database: 'default_db',
        },
      })

      const connection = await getConnection('unknown')

      expect(sql.ConnectionPool).toHaveBeenCalledWith({
        server: 'default-server',
        database: 'default_db',
      })
      expect(connection).toBe(mockPool)
    })

    it('should throw an error if no config is found for the service type', async () => {
      initializeSQLAdapter({})

      await expect(getConnection('unknown')).rejects.toThrow(
        'No database configuration found for service type: unknown'
      )
    })
  })

  describe('disconnectAll', () => {
    it('should close all connections', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
          teams: {
            server: 'test-server',
            database: 'teams_db',
          },
        },
      })

      // Create two connections
      await getConnection('users')
      await getConnection('teams')

      // Reset mocks to verify close is called
      jest.clearAllMocks()

      await disconnectAll()

      expect(mockPool.close).toHaveBeenCalledTimes(2)
    })
  })

  describe('query', () => {
    it('should execute a SQL query with parameters', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      const result = await query({
        serviceType: 'users',
        query: 'SELECT * FROM Users WHERE id = @id',
        params: { id: 1 },
      })

      expect(mockPool.request).toHaveBeenCalled()
      expect(mockRequest.input).toHaveBeenCalledWith('id', 1)
      expect(mockRequest.query).toHaveBeenCalledWith(
        'SELECT * FROM Users WHERE id = @id'
      )
      expect(result).toEqual([{ id: 1, name: 'Test' }])
    })
  })

  describe('getRow', () => {
    it('should get a single row from a table', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      const result = await getRow({
        serviceType: 'users',
        table: 'Users',
        where: { id: 1 },
      })

      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('SELECT TOP 1')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('FROM Users')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('WHERE')
      )
      expect(result).toEqual({ id: 1, name: 'Test' })
    })

    it('should return null if no row is found', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      mockRequest.query.mockResolvedValueOnce({ recordset: [] })

      const result = await getRow({
        serviceType: 'users',
        table: 'Users',
        where: { id: 999 },
      })

      expect(result).toBeNull()
    })
  })

  describe('getRows', () => {
    it('should get multiple rows from a table', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      mockRequest.query.mockResolvedValueOnce({
        recordset: [
          { id: 1, name: 'User 1' },
          { id: 2, name: 'User 2' },
        ],
      })

      const result = await getRows({
        serviceType: 'users',
        table: 'Users',
        where: { active: true },
        orderBy: 'name',
        limit: 10,
        offset: 0,
      })

      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('SELECT')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('FROM Users')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('WHERE')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('ORDER BY')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('OFFSET')
      )
      expect(result).toEqual([
        { id: 1, name: 'User 1' },
        { id: 2, name: 'User 2' },
      ])
    })
  })

  describe('fetchQueryResult', () => {
    it('should get merged tables with using join', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
          userProfile: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      mockRequest.query.mockResolvedValueOnce({
        recordset: [{ id: 1, name: 'User 1' }],
      })
      mockRequest.query.mockResolvedValueOnce({
        recordset: [{ count: 1 }],
      })

      const result = await fetchQueryResult({
        serviceType: 'users',
        baseTable: 'Users',
        fields: {
          Users: ['id', 'name'],
        },
        where: {
          Users: {
            field: 'name',
            operator: 'equals',
            value: 'User 1',
          },
        },
        type: 'INNER',
        orderBy: 'name',
        includes: ['UserProfile'],
        limit: 10,
        offset: 0,
        primaryKey: 'id',
        foreignKey: { UserProfile: 'userId' },
      })

      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('SELECT')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('FROM Users')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('JOIN')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('WHERE')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('ORDER BY')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('OFFSET')
      )
      expect(result).toEqual({ count: 1, rows: [{ id: 1, name: 'User 1' }] })
    })
  })

  describe('insertRow', () => {
    it('should insert a row into a table', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      const result = await insertRow({
        serviceType: 'users',
        table: 'Users',
        data: { name: 'New User', email: 'user@example.com' },
      })

      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('INSERT INTO Users')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('VALUES')
      )
      expect(result).toEqual([{ id: 1, name: 'Test' }])
    })

    it('should return the inserted row when returnInserted is true', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      // Mock the first query to return the ID
      mockRequest.query.mockResolvedValueOnce({
        recordset: [{ id: 5 }],
      })

      // Mock the second query to return the full row
      mockRequest.query.mockResolvedValueOnce({
        recordset: [{ id: 5, name: 'New User', email: 'user@example.com' }],
      })

      const result = await insertRow({
        serviceType: 'users',
        table: 'Users',
        data: { name: 'New User', email: 'user@example.com' },
        returnInserted: true,
      })

      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('INSERT INTO Users')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('SELECT SCOPE_IDENTITY()')
      )
      expect(result).toEqual({
        id: 5,
        name: 'New User',
        email: 'user@example.com',
      })
    })
  })

  describe('updateRow', () => {
    it('should update rows in a table', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      const result = await updateRow({
        serviceType: 'users',
        table: 'Users',
        data: { name: 'Updated User' },
        where: { id: 1 },
      })

      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('UPDATE Users')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('SET')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('WHERE')
      )
      expect(result).toBe(true)
    })

    it('should return the updated rows when returnUpdated is true', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      // Mock the second query to return the updated row
      mockRequest.query.mockResolvedValueOnce({})
      mockRequest.query.mockResolvedValueOnce({
        recordset: [{ id: 1, name: 'Updated User' }],
      })

      const result = await updateRow({
        serviceType: 'users',
        table: 'Users',
        data: { name: 'Updated User' },
        where: { id: 1 },
        returnUpdated: true,
      })

      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('UPDATE Users')
      )
      expect(result).toEqual([{ id: 1, name: 'Updated User' }])
    })
  })

  describe('deleteRow', () => {
    it('should delete rows from a table', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      const result = await deleteRow({
        serviceType: 'users',
        table: 'Users',
        where: { id: 1 },
      })

      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('DELETE FROM Users')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('WHERE')
      )
      expect(result).toBe(true)
    })

    it('should return the deleted rows when returnDeleted is true', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      // Mock the first query to return the row to be deleted
      mockRequest.query.mockResolvedValueOnce({
        recordset: [{ id: 1, name: 'User to Delete' }],
      })

      const result = await deleteRow({
        serviceType: 'users',
        table: 'Users',
        where: { id: 1 },
        returnDeleted: true,
      })

      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('DELETE FROM Users')
      )
      expect(result).toEqual([{ id: 1, name: 'User to Delete' }])
    })
  })

  describe('withTransaction', () => {
    it('should execute a callback within a transaction', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      const callback = jest.fn().mockResolvedValue('result')

      const result = await withTransaction('users', callback)

      expect(mockTransaction.begin).toHaveBeenCalled()
      expect(callback).toHaveBeenCalledWith(mockTransaction)
      expect(mockTransaction.commit).toHaveBeenCalled()
      expect(result).toBe('result')
    })

    it('should rollback the transaction if the callback throws', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      const error = new Error('Transaction error')
      const callback = jest.fn().mockRejectedValue(error)

      await expect(withTransaction('users', callback)).rejects.toThrow(error)

      expect(mockTransaction.begin).toHaveBeenCalled()
      expect(callback).toHaveBeenCalledWith(mockTransaction)
      expect(mockTransaction.rollback).toHaveBeenCalled()
      expect(mockTransaction.commit).not.toHaveBeenCalled()
    })
  })

  describe('executeBatch', () => {
    it('should execute multiple queries in a transaction', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      // Mock the transaction request and query results
      mockTransaction.request.mockReturnValue(mockRequest)
      mockRequest.query.mockResolvedValueOnce({
        recordset: [{ id: 1, name: 'User 1' }],
      })
      mockRequest.query.mockResolvedValueOnce({
        recordset: [{ id: 2, name: 'User 2' }],
      })

      const result = await executeBatch({
        serviceType: 'users',
        queries: [
          {
            query: 'SELECT * FROM Users WHERE id = @id',
            params: { id: 1 },
          },
          {
            query: 'SELECT * FROM Users WHERE id = @id',
            params: { id: 2 },
          },
        ],
      })

      expect(mockTransaction.begin).toHaveBeenCalled()
      expect(mockTransaction.request).toHaveBeenCalledTimes(2)
      expect(mockRequest.query).toHaveBeenCalledTimes(2)
      expect(mockTransaction.commit).toHaveBeenCalled()
      expect(result).toEqual([
        [{ id: 1, name: 'User 1' }],
        [{ id: 2, name: 'User 2' }],
      ])
    })
  })

  describe('getRowCount', () => {
    it('should get the count of rows in a table', async () => {
      initializeSQLAdapter({
        dbConfig: {
          users: {
            server: 'test-server',
            database: 'users_db',
          },
        },
      })

      mockRequest.query.mockResolvedValueOnce({
        recordset: [{ count: 42 }],
      })

      const result = await getRowCount({
        serviceType: 'users',
        table: 'Users',
        where: { active: true },
      })

      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('SELECT COUNT(*)')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('FROM Users')
      )
      expect(mockRequest.query).toHaveBeenCalledWith(
        expect.stringContaining('WHERE')
      )
      expect(result).toBe(42)
    })
  })
})