// src/services/db/admin.db.test.ts
import { describe, it, expect, vi, beforeEach, Mock } from 'vitest';
import { mockPoolInstance } from '../../tests/setup/tests-setup-unit';
import type { Pool, PoolClient } from 'pg';
import { ForeignKeyConstraintError, NotFoundError } from './errors.db';
import { AdminRepository } from './admin.db';
import type { SuggestedCorrection, AdminUserView, Profile } from '../../types';
import { createMockSuggestedCorrection, createMockAdminUserView, createMockProfile } from '../../tests/utils/mockFactories';
// Un-mock the module we are testing
vi.unmock('./admin.db');

// Mock the logger to prevent console output during tests
vi.mock('../logger.server', () => ({
  logger: {
    info: vi.fn(),
    warn: vi.fn(), // Keep warn for other tests that might use it
    error: vi.fn(),
    debug: vi.fn(),
  },
}));
import { logger as mockLogger } from '../logger.server';

// Mock the withTransaction helper
vi.mock('./connection.db', async (importOriginal) => {
  const actual = await importOriginal<typeof import('./connection.db')>();
  return { ...actual, withTransaction: vi.fn() };
});
import { withTransaction } from './connection.db';

describe('Admin DB Service', () => {
  let adminRepo: AdminRepository;

  beforeEach(() => {
    // Reset the global mock's call history before each test.
    vi.clearAllMocks();

    // Reset the withTransaction mock before each test
    vi.mocked(withTransaction).mockImplementation(async (callback) => {
      const mockClient = { query: vi.fn() };
      return callback(mockClient as unknown as PoolClient);
    });
    // Instantiate the repository with the mock pool for each test
    adminRepo = new AdminRepository(mockPoolInstance as unknown as Pool);
  });

  describe('getSuggestedCorrections', () => {
    it('should execute the correct query and return corrections', async () => {
      const mockCorrections: SuggestedCorrection[] = [createMockSuggestedCorrection({ suggested_correction_id: 1 })];
      mockPoolInstance.query.mockResolvedValue({ rows: mockCorrections });

      const result = await adminRepo.getSuggestedCorrections(mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining("FROM public.suggested_corrections sc"));
      expect(result).toEqual(mockCorrections);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.getSuggestedCorrections(mockLogger)).rejects.toThrow('Failed to retrieve suggested corrections.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError }, 'Database error in getSuggestedCorrections');
    });
  });

  describe('approveCorrection', () => {
    it('should call the approve_correction database function', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] }); // Mock the function call
      await adminRepo.approveCorrection(123, mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith('SELECT public.approve_correction($1)', [123]);
    });

    it('should throw an error if the database function fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.approveCorrection(123, mockLogger)).rejects.toThrow('Failed to approve correction.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, correctionId: 123 }, 'Database transaction error in approveCorrection');
    });
  });

  describe('rejectCorrection', () => {
    it('should update the correction status to rejected', async () => {
      mockPoolInstance.query.mockResolvedValue({ rowCount: 1 });
      await adminRepo.rejectCorrection(123, mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        expect.stringContaining("UPDATE public.suggested_corrections SET status = 'rejected'"),
        [123]
      );
    });

    it('should throw NotFoundError if the correction is not found or not pending', async () => {
      mockPoolInstance.query.mockResolvedValue({ rowCount: 0 });
      await expect(adminRepo.rejectCorrection(123, mockLogger)).rejects.toThrow(NotFoundError);
      await expect(adminRepo.rejectCorrection(123, mockLogger)).rejects.toThrow('Correction with ID 123 not found or not in \'pending\' state.');
    });

    it('should throw an error if the database query fails', async () => {
      mockPoolInstance.query.mockRejectedValue(new Error('DB Error'));
      await expect(adminRepo.rejectCorrection(123, mockLogger)).rejects.toThrow('Failed to reject correction.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error), correctionId: 123 }, 'Database error in rejectCorrection');
    });
  });

  describe('updateSuggestedCorrection', () => {
    it('should update the suggested value and return the updated correction', async () => {
      const mockCorrection = createMockSuggestedCorrection({ suggested_correction_id: 1, suggested_value: '300' });
      mockPoolInstance.query.mockResolvedValue({ rows: [mockCorrection], rowCount: 1 });

      const result = await adminRepo.updateSuggestedCorrection(1, '300', mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        expect.stringContaining("UPDATE public.suggested_corrections SET suggested_value = $1"),
        ['300', 1]
      );
      expect(result).toEqual(mockCorrection);
    });

    it('should throw an error if the correction is not found (rowCount is 0)', async () => {
      mockPoolInstance.query.mockResolvedValue({ rowCount: 0, rows: [] });
      await expect(adminRepo.updateSuggestedCorrection(999, 'new value', mockLogger)).rejects.toThrow(NotFoundError);
      await expect(adminRepo.updateSuggestedCorrection(999, 'new value', mockLogger)).rejects.toThrow('Correction with ID 999 not found or is not in \'pending\' state.');
    });

    it('should throw a generic error if the database query fails', async () => {
      mockPoolInstance.query.mockRejectedValue(new Error('DB Error'));
      await expect(adminRepo.updateSuggestedCorrection(1, 'new value', mockLogger)).rejects.toThrow('Failed to update suggested correction.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error), correctionId: 1 }, 'Database error in updateSuggestedCorrection');
    });
  });

  describe('getApplicationStats', () => {
    it('should execute 5 parallel count queries and return the aggregated stats', async () => {
      // Mock responses for each of the 5 parallel queries
      mockPoolInstance.query
        .mockResolvedValueOnce({ rows: [{ count: '10' }] }) // flyerCount
        .mockResolvedValueOnce({ rows: [{ count: '20' }] }) // userCount
        .mockResolvedValueOnce({ rows: [{ count: '300' }] }) // flyerItemCount
        .mockResolvedValueOnce({ rows: [{ count: '5' }] }) // storeCount
        .mockResolvedValueOnce({ rows: [{ count: '2' }] }); // pendingCorrectionCount

      const stats = await adminRepo.getApplicationStats(mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledTimes(5);
      expect(stats).toEqual({
        flyerCount: 10,
        userCount: 20,
        flyerItemCount: 300,
        storeCount: 5,
        pendingCorrectionCount: 2,
      });
    });

    it('should throw an error if one of the parallel queries fails', async () => {
      // Mock one query to succeed and another to fail
      mockPoolInstance.query
        .mockResolvedValueOnce({ rows: [{ count: '10' }] })
        .mockRejectedValueOnce(new Error('DB Read Error'));

      // The Promise.all should reject, and the function should re-throw the error
      await expect(adminRepo.getApplicationStats(mockLogger)).rejects.toThrow('DB Read Error');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error) }, 'Database error in getApplicationStats');
    });
  });

  describe('getDailyStatsForLast30Days', () => {
    it('should execute the correct query to get daily stats', async () => {
      const mockStats = [{ date: '2023-01-01', new_users: 5, new_flyers: 2 }];
      mockPoolInstance.query.mockResolvedValue({ rows: mockStats });

      const result = await adminRepo.getDailyStatsForLast30Days(mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining("WITH date_series AS"));
      expect(result).toEqual(mockStats);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.getDailyStatsForLast30Days(mockLogger)).rejects.toThrow('Failed to retrieve daily statistics.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError }, 'Database error in getDailyStatsForLast30Days');
    });
  });

  describe('logActivity', () => {
    it('should insert a new activity log entry', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      const logData = { userId: 'user-123', action: 'test_action', displayText: 'Test activity' };
      await adminRepo.logActivity(logData, mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        expect.stringContaining("INSERT INTO public.activity_log"),
        [logData.userId, logData.action, logData.displayText, null, null]
      );
    });

    it('should not throw an error if the database query fails (non-critical)', async () => {
      mockPoolInstance.query.mockRejectedValue(new Error('DB Error'));
      const logData = { action: 'test_action', displayText: 'Test activity' };
      await expect(adminRepo.logActivity(logData, mockLogger)).resolves.toBeUndefined();
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error), logData }, 'Database error in logActivity');
    });
  });

  describe('getMostFrequentSaleItems', () => {
    it('should call the correct database function', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await adminRepo.getMostFrequentSaleItems(30, 10, mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('FROM public.flyer_items fi'), [30, 10]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.getMostFrequentSaleItems(30, 10, mockLogger)).rejects.toThrow('Failed to get most frequent sale items.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError }, 'Database error in getMostFrequentSaleItems');
    });
  });

  describe('updateRecipeCommentStatus', () => {
    it('should update the comment status and return the updated comment', async () => {
      const mockComment = { comment_id: 1, status: 'hidden' };
      mockPoolInstance.query.mockResolvedValue({ rows: [mockComment], rowCount: 1 });
      const result = await adminRepo.updateRecipeCommentStatus(1, 'hidden', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('UPDATE public.recipe_comments'), ['hidden', 1]);
      expect(result).toEqual(mockComment);
    });

    it('should throw an error if the comment is not found (rowCount is 0)', async () => {
      mockPoolInstance.query.mockResolvedValue({ rowCount: 0, rows: [] });
      await expect(adminRepo.updateRecipeCommentStatus(999, 'hidden', mockLogger)).rejects.toThrow('Recipe comment with ID 999 not found.');
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.updateRecipeCommentStatus(1, 'hidden', mockLogger)).rejects.toThrow('Failed to update recipe comment status.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, commentId: 1, status: 'hidden' }, 'Database error in updateRecipeCommentStatus');
    });
  });

  describe('getUnmatchedFlyerItems', () => {
    it('should execute the correct query to get unmatched items', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await adminRepo.getUnmatchedFlyerItems(mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('FROM public.unmatched_flyer_items ufi'));
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.getUnmatchedFlyerItems(mockLogger)).rejects.toThrow('Failed to retrieve unmatched flyer items.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError }, 'Database error in getUnmatchedFlyerItems');
    });
  });

  describe('updateRecipeStatus', () => {
    it('should update the recipe status and return the updated recipe', async () => {
      const mockRecipe = { recipe_id: 1, status: 'public' };
      mockPoolInstance.query.mockResolvedValue({ rows: [mockRecipe], rowCount: 1 });
      const result = await adminRepo.updateRecipeStatus(1, 'public', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('UPDATE public.recipes'), ['public', 1]);
      expect(result).toEqual(mockRecipe);
    });

    it('should throw an error if the recipe is not found (rowCount is 0)', async () => {
      mockPoolInstance.query.mockResolvedValue({ rowCount: 0, rows: [] });
      await expect(adminRepo.updateRecipeStatus(999, 'public', mockLogger)).rejects.toThrow(NotFoundError);
      await expect(adminRepo.updateRecipeStatus(999, 'public', mockLogger)).rejects.toThrow('Recipe with ID 999 not found.');
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.updateRecipeStatus(1, 'public', mockLogger)).rejects.toThrow('Failed to update recipe status.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, recipeId: 1, status: 'public' }, 'Database error in updateRecipeStatus');
    });
  });

  describe('resolveUnmatchedFlyerItem', () => {
    it('should execute a transaction to resolve an unmatched item', async () => {
      // Create a mock client that we can reference both inside and outside the transaction mock.
      const mockClient = { query: vi.fn() };
      (mockClient.query as Mock)
        .mockResolvedValueOnce({ rows: [{ flyer_item_id: 55 }] }) // SELECT flyer_item_id from unmatched_flyer_items
        .mockResolvedValueOnce({ rowCount: 1 }) // UPDATE flyer_items table
        .mockResolvedValueOnce({ rowCount: 1 }); // UPDATE unmatched_flyer_items table

      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        return callback(mockClient as unknown as PoolClient);
      });
  
      await adminRepo.resolveUnmatchedFlyerItem(1, 101, mockLogger);

      expect(mockClient.query).toHaveBeenCalledWith(expect.stringContaining('SELECT flyer_item_id FROM public.unmatched_flyer_items'), [1]);
      expect(mockClient.query).toHaveBeenCalledWith(expect.stringContaining('UPDATE public.flyer_items'), [101, 55]);
      expect(mockClient.query).toHaveBeenCalledWith(expect.stringContaining("UPDATE public.unmatched_flyer_items SET status = 'resolved'"), [1]);
    });

    it('should throw NotFoundError if the unmatched item is not found', async () => {
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: vi.fn() };
        (mockClient.query as Mock).mockResolvedValueOnce({ rowCount: 0, rows: [] }); // SELECT finds nothing
        await expect(callback(mockClient as unknown as PoolClient)).rejects.toThrow(NotFoundError);
        throw new NotFoundError(`Unmatched flyer item with ID 999 not found.`); // Re-throw for the outer expect
      });

      await expect(adminRepo.resolveUnmatchedFlyerItem(999, 101, mockLogger)).rejects.toThrow(NotFoundError);
      await expect(adminRepo.resolveUnmatchedFlyerItem(999, 101, mockLogger)).rejects.toThrow('Unmatched flyer item with ID 999 not found.');
    });

    it('should rollback transaction on generic error', async () => {
      const dbError = new Error('DB Error');
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: vi.fn() };
        (mockClient.query as Mock)
          .mockResolvedValueOnce({ rows: [{ flyer_item_id: 55 }] }) // SELECT flyer_item_id
          .mockRejectedValueOnce(dbError); // UPDATE flyer_items fails
        await expect(callback(mockClient as unknown as PoolClient)).rejects.toThrow(dbError);
        throw dbError; // Re-throw for the outer expect
      });

      await expect(adminRepo.resolveUnmatchedFlyerItem(1, 101, mockLogger)).rejects.toThrow('Failed to resolve unmatched flyer item.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, unmatchedFlyerItemId: 1, masterItemId: 101 }, 'Database transaction error in resolveUnmatchedFlyerItem');
    });
  });

  describe('ignoreUnmatchedFlyerItem', () => {
    it('should update the status of an unmatched item to "ignored"', async () => {
      mockPoolInstance.query.mockResolvedValue({ rowCount: 1 });
      await adminRepo.ignoreUnmatchedFlyerItem(1, mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith("UPDATE public.unmatched_flyer_items SET status = 'ignored' WHERE unmatched_flyer_item_id = $1 AND status = 'pending'", [1]);
    });

    it('should throw NotFoundError if the unmatched item is not found or not pending', async () => {
      mockPoolInstance.query.mockResolvedValue({ rowCount: 0 });
      await expect(adminRepo.ignoreUnmatchedFlyerItem(999, mockLogger)).rejects.toThrow(NotFoundError);
      await expect(adminRepo.ignoreUnmatchedFlyerItem(999, mockLogger)).rejects.toThrow('Unmatched flyer item with ID 999 not found or not in \'pending\' state.');
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.ignoreUnmatchedFlyerItem(1, mockLogger)).rejects.toThrow('Failed to ignore unmatched flyer item.');
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining("UPDATE public.unmatched_flyer_items SET status = 'ignored'"), [1]);
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, unmatchedFlyerItemId: 1 }, 'Database error in ignoreUnmatchedFlyerItem');
    });
  });

  describe('resetFailedLoginAttempts', () => {
    it('should execute an UPDATE query to reset failed attempts', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await adminRepo.resetFailedLoginAttempts('user-123', '127.0.0.1', mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        expect.stringContaining('UPDATE public.users'),
        ['user-123', '127.0.0.1']
      );
    });

    it('should not throw an error if the database query fails (non-critical)', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.resetFailedLoginAttempts('user-123', '127.0.0.1', mockLogger)).resolves.toBeUndefined();
      const { logger } = await import('../logger.server');
      expect(logger.error).toHaveBeenCalledWith({ err: dbError, userId: 'user-123' }, 'Database error in resetFailedLoginAttempts');
    });
  });

  describe('incrementFailedLoginAttempts', () => {
    it('should execute an UPDATE query to increment failed attempts', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await adminRepo.incrementFailedLoginAttempts('user-123', mockLogger);
      
      // Fix: Use regex to match query with variable whitespace
      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        expect.stringContaining('UPDATE public.users'), 
        ['user-123']
      );
    });

    it('should not throw an error if the database query fails (non-critical)', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.incrementFailedLoginAttempts('user-123', mockLogger)).resolves.toBeUndefined();
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: 'user-123' }, 'Database error in incrementFailedLoginAttempts');
    });
  });

  describe('updateBrandLogo', () => {
    it('should execute an UPDATE query for the brand logo', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await adminRepo.updateBrandLogo(1, '/logo.png', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith('UPDATE public.brands SET logo_url = $1 WHERE brand_id = $2', ['/logo.png', 1]);
    });

    it('should throw NotFoundError if the brand is not found', async () => {
      mockPoolInstance.query.mockResolvedValue({ rowCount: 0 });
      await expect(adminRepo.updateBrandLogo(999, '/logo.png', mockLogger)).rejects.toThrow(NotFoundError);
      await expect(adminRepo.updateBrandLogo(999, '/logo.png', mockLogger)).rejects.toThrow('Brand with ID 999 not found.');
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.updateBrandLogo(1, '/logo.png', mockLogger)).rejects.toThrow('Failed to update brand logo in database.');
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('UPDATE public.brands SET logo_url'), ['/logo.png', 1]);
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, brandId: 1 }, 'Database error in updateBrandLogo');
    });
  });

  describe('updateReceiptStatus', () => {
    it('should update the receipt status and return the updated receipt', async () => {
      const mockReceipt = { receipt_id: 1, status: 'completed' };
      mockPoolInstance.query.mockResolvedValue({ rows: [mockReceipt], rowCount: 1 });
      const result = await adminRepo.updateReceiptStatus(1, 'completed', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('UPDATE public.receipts'), ['completed', 1]);
      expect(result).toEqual(mockReceipt);
    });

    it('should throw an error if the receipt is not found (rowCount is 0)', async () => {
      mockPoolInstance.query.mockResolvedValue({ rowCount: 0, rows: [] });
      await expect(adminRepo.updateReceiptStatus(999, 'completed', mockLogger)).rejects.toThrow(NotFoundError);
      await expect(adminRepo.updateReceiptStatus(999, 'completed', mockLogger)).rejects.toThrow('Receipt with ID 999 not found.');
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.updateReceiptStatus(1, 'completed', mockLogger)).rejects.toThrow('Failed to update receipt status.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, receiptId: 1, status: 'completed' }, 'Database error in updateReceiptStatus');
    });
  });

  describe('getActivityLog', () => {
    it('should call the get_activity_log database function', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await adminRepo.getActivityLog(50, 0, mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith('SELECT * FROM public.get_activity_log($1, $2)', [50, 0]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.getActivityLog(50, 0, mockLogger)).rejects.toThrow('Failed to retrieve activity log.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, limit: 50, offset: 0 }, 'Database error in getActivityLog');
    });
  });

  describe('getAllUsers', () => {
    it('should return a list of all users for the admin view', async () => {
      const mockUsers: AdminUserView[] = [createMockAdminUserView({ user_id: '1', email: 'test@test.com' })];
      mockPoolInstance.query.mockResolvedValue({ rows: mockUsers });
      const result = await adminRepo.getAllUsers(mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('FROM public.users u JOIN public.profiles p'));
      expect(result).toEqual(mockUsers);
    });
  });

  describe('updateUserRole', () => {
    it('should update the user role and return the updated user', async () => {
      const mockProfile: Profile = createMockProfile({ user_id: '1', role: 'admin' });
      mockPoolInstance.query.mockResolvedValue({ rows: [mockProfile], rowCount: 1 });
      const result = await adminRepo.updateUserRole('1', 'admin', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith('UPDATE public.profiles SET role = $1 WHERE user_id = $2 RETURNING *', ['admin', '1']);
      expect(result).toEqual(mockProfile);
    });

    it('should throw an error if the user is not found (rowCount is 0)', async () => {
      mockPoolInstance.query.mockResolvedValue({ rowCount: 0, rows: [] });
      await expect(adminRepo.updateUserRole('999', 'admin', mockLogger)).rejects.toThrow('User with ID 999 not found.');
    });

    it('should re-throw a generic error if the database query fails for other reasons', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(adminRepo.updateUserRole('1', 'admin', mockLogger)).rejects.toThrow('DB Error');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: '1', role: 'admin' }, 'Database error in updateUserRole');
    });
  });

  it('should throw ForeignKeyConstraintError if the user does not exist on update', async () => {
    const dbError = new Error('violates foreign key constraint');
    // Create a more specific type for the error object to avoid using 'any'
    (dbError as Error & { code: string }).code = '23503';
    mockPoolInstance.query.mockRejectedValue(dbError);

    await expect(adminRepo.updateUserRole('non-existent-user', 'admin', mockLogger)).rejects.toThrow(ForeignKeyConstraintError);
    await expect(adminRepo.updateUserRole('non-existent-user', 'admin', mockLogger)).rejects.toThrow('The specified user does not exist.');
    expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: 'non-existent-user', role: 'admin' }, 'Database error in updateUserRole');
  });
});