// src/services/db/connection.db.test.ts
import { describe, it, expect, vi, beforeEach, type Mock } from 'vitest';
import type { PoolClient } from 'pg';

// Define mocks first using vi.hoisted to allow usage in vi.mock factory
const mocks = vi.hoisted(() => {
  const mockPoolInstance = {
    connect: vi.fn(),
    query: vi.fn(),
    end: vi.fn(),
    on: vi.fn(),
    totalCount: 10,
    idleCount: 5,
    waitingCount: 0,
  };

  // FIX: Use a standard function expression so it can be called with 'new'
  const MockPool = vi.fn(function () {
    return mockPoolInstance;
  });

  return {
    mockPoolInstance,
    MockPool,
  };
});

// Mock the 'pg' module
vi.mock('pg', () => {
  return {
    Pool: mocks.MockPool,
    types: { setTypeParser: vi.fn(), builtins: { NUMERIC: 'NUMERIC', INT8: 'INT8' } },
  };
});

// Mock the logger. This is a server-side DB test.
vi.mock('../logger.server', () => ({
  logger: {
    info: vi.fn(),
    error: vi.fn(),
  },
}));
import { logger } from '../logger.server';

describe('DB Connection Service', () => {
  beforeEach(async () => {
    vi.clearAllMocks();

    // We reset modules to ensure 'connection.db.ts' re-evaluates and calls the (mocked) Pool constructor again
    // Reset implementations to default for shared mocks
    // This is crucial because 'should throw an error' test overrides implementation
    mocks.MockPool.mockImplementation(function () {
      return mocks.mockPoolInstance;
    });

    // Reset specific method behaviors
    mocks.mockPoolInstance.query.mockReset();

    // Mock pool.on to capture the error handler
    let capturedErrorHandler: ((err: Error, client: PoolClient) => void) | undefined;
    vi.mocked(mocks.mockPoolInstance.on).mockImplementation((event, handler) => {
      if (event === 'error') {
        capturedErrorHandler = handler as (err: Error, client: PoolClient) => void;
      }
      return mocks.mockPoolInstance; // Return the mock instance for chaining
    });

    vi.resetModules();
  });

  describe('getPool', () => {
    it('should create a new pool instance on the first call', async () => {
      // Re-import to ensure fresh state after resetModules
      const { getPool } = await import('./connection.db');
      const { Pool } = await import('pg'); // Get the current mock

      const pool = getPool();

      // Verify Pool constructor was called
      expect(Pool).toHaveBeenCalledTimes(1);
      expect(pool).toBeDefined();
      // Check basic shape to confirm it's our mock
      expect(pool).toHaveProperty('connect');
      expect(pool).toHaveProperty('query');
    });

    it('should return the same pool instance on subsequent calls', async () => {
      const { getPool } = await import('./connection.db');

      const pool1 = getPool();
      const pool2 = getPool();

      // Since getPool() caches the instance, and resetModules() was called in beforeEach,
      // this test starts fresh.
      // pool1 triggers new Pool(). pool2 returns cached pool1.
      expect(pool1).toBe(pool2);
    });

    it('should log an error if an unexpected error occurs on an idle client', async () => {
      const { getPool } = await import('./connection.db');
      getPool(); // Ensure the pool is initialized and the 'on' handler is registered

      const unexpectedError = new Error('Simulated unexpected pool error');
      const mockClient = { query: vi.fn(), release: vi.fn() } as unknown as PoolClient;

      // Manually invoke the captured error handler
      const errorHandler = vi
        .mocked(mocks.mockPoolInstance.on)
        .mock.calls.find((call) => call[0] === 'error')?.[1] as (
        err: Error,
        client: PoolClient,
      ) => void;
      errorHandler(unexpectedError, mockClient);

      expect(logger.error).toHaveBeenCalledWith(
        { err: unexpectedError, client: mockClient },
        'Unexpected error on idle client in pool',
      );
    });
  });

  it('should throw an error if the Pool constructor fails', async () => {
    // Arrange: Mock the Pool constructor to throw an error
    const { Pool } = await import('pg');
    const constructorError = new Error('Invalid credentials');

    // FIX: Use standard function for mock implementation
    vi.mocked(Pool).mockImplementation(function () {
      throw constructorError;
    });

    const { getPool } = await import('./connection.db');

    // Act & Assert: Expect getPool to throw the error from the constructor
    expect(() => getPool()).toThrow(constructorError);
  });

  describe('checkTablesExist', () => {
    it('should return an empty array if all tables exist', async () => {
      const { checkTablesExist } = await import('./connection.db');
      const pool = mocks.mockPoolInstance;

      // Use vi.mocked() to get a type-safe mock of the query function
      (pool.query as Mock).mockResolvedValue({
        rows: [{ table_name: 'users' }, { table_name: 'flyers' }],
      });

      const tableNames = ['users', 'flyers'];
      const missingTables = await checkTablesExist(tableNames);

      expect(pool.query).toHaveBeenCalledWith(expect.any(String), [tableNames]);
      expect(missingTables).toEqual([]);
    });

    it('should throw an error if the database query fails', async () => {
      const { checkTablesExist } = await import('./connection.db');
      const pool = mocks.mockPoolInstance;
      const dbError = new Error('DB Connection Failed');
      (pool.query as Mock).mockRejectedValue(dbError);

      const tableNames = ['users'];

      await expect(checkTablesExist(tableNames)).rejects.toThrow(
        'Failed to check for tables in database.',
      );
      expect(logger.error).toHaveBeenCalledWith(
        { err: dbError },
        'Database error in checkTablesExist',
      );
    });

    it('should return an array of missing tables', async () => {
      const { checkTablesExist } = await import('./connection.db');
      const pool = mocks.mockPoolInstance;

      (pool.query as Mock).mockResolvedValue({ rows: [{ table_name: 'users' }] });

      const tableNames = ['users', 'flyers', 'products'];
      const missingTables = await checkTablesExist(tableNames);

      expect(missingTables).toEqual(['flyers', 'products']);
    });
  });

  describe('getPoolStatus', () => {
    it('should return the status counts from the pool instance', async () => {
      const { getPoolStatus } = await import('./connection.db');
      const status = getPoolStatus();

      expect(status).toEqual({
        totalCount: 10,
        idleCount: 5,
        waitingCount: 0,
      });
    });
  });

  describe('withTransaction', () => {
    it('should commit the transaction on success', async () => {
      const { withTransaction } = await import('./connection.db');
      const mockClient = {
        query: vi.fn().mockResolvedValue({}),
        release: vi.fn(),
      };
      mocks.mockPoolInstance.connect.mockResolvedValue(mockClient);

      const callback = vi.fn().mockResolvedValue('success');
      const result = await withTransaction(callback);

      expect(result).toBe('success');
      expect(mockClient.query).toHaveBeenCalledWith('BEGIN');
      expect(callback).toHaveBeenCalledWith(mockClient);
      expect(mockClient.query).toHaveBeenCalledWith('COMMIT');
      expect(mockClient.release).toHaveBeenCalledTimes(1);
    });

    it('should rollback the transaction and log on error', async () => {
      const { withTransaction } = await import('./connection.db');
      const mockClient = {
        query: vi.fn().mockResolvedValue({}),
        release: vi.fn(),
      };
      mocks.mockPoolInstance.connect.mockResolvedValue(mockClient);

      const dbError = new Error('Callback failed');
      const callback = vi.fn().mockRejectedValue(dbError);

      await expect(withTransaction(callback)).rejects.toThrow(dbError);

      expect(mockClient.query).toHaveBeenCalledWith('BEGIN');
      expect(callback).toHaveBeenCalledWith(mockClient);
      expect(mockClient.query).toHaveBeenCalledWith('ROLLBACK');
      expect(mockClient.query).not.toHaveBeenCalledWith('COMMIT');
      expect(logger.error).toHaveBeenCalledWith(
        { err: dbError },
        'Transaction failed, rolling back.',
      );
      expect(mockClient.release).toHaveBeenCalledTimes(1);
    });
  });
});
