// src/services/db/personalization.db.test.ts
import { describe, it, expect, vi, beforeEach } from 'vitest';
import { mockPoolInstance } from '../../tests/setup/tests-setup-unit';
import type { Pool, PoolClient } from 'pg';
import { withTransaction } from './connection.db';
import {
  PersonalizationRepository} from './personalization.db';
import type { MasterGroceryItem, UserAppliance, DietaryRestriction, Appliance } from '../../types';

// Un-mock the module we are testing to ensure we use the real implementation.
vi.unmock('./personalization.db');

const mockQuery = mockPoolInstance.query;

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

import { ForeignKeyConstraintError } from './errors.db';
// Mock the logger to prevent console output during tests. This is a server-side DB test.
vi.mock('../logger.server', () => ({
  logger: {
    info: vi.fn(),
    warn: vi.fn(),
    error: vi.fn(),
    debug: vi.fn(),
  },
}));
import { logger as mockLogger } from '../logger.server';

describe('Personalization DB Service', () => {
  let personalizationRepo: PersonalizationRepository;

  beforeEach(() => {
    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
    personalizationRepo = new PersonalizationRepository(mockPoolInstance as unknown as Pool);
  });

  describe('getAllMasterItems', () => {
    it('should execute the correct query and return master items', async () => {
      const mockItems: MasterGroceryItem[] = [{ master_grocery_item_id: 1, name: 'Apples', created_at: '' }];
      mockQuery.mockResolvedValue({ rows: mockItems });
 
      const result = await personalizationRepo.getAllMasterItems(mockLogger);

      expect(mockQuery).toHaveBeenCalledWith('SELECT * FROM public.master_grocery_items ORDER BY name ASC');
      expect(result).toEqual(mockItems);
    });

    it('should return an empty array if no master items exist', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      const result = await personalizationRepo.getAllMasterItems(mockLogger);
      expect(result).toEqual([]);
    });

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

  describe('getWatchedItems', () => {
    it('should execute the correct query and return watched items', async () => {
      const mockItems: MasterGroceryItem[] = [{ master_grocery_item_id: 1, name: 'Apples', created_at: '' }];
      mockQuery.mockResolvedValue({ rows: mockItems });

      const result = await personalizationRepo.getWatchedItems('user-123', mockLogger);
 
      expect(mockQuery).toHaveBeenCalledWith(expect.stringContaining('FROM public.master_grocery_items mgi'), ['user-123']);
      expect(result).toEqual(mockItems);
    });

    it('should return an empty array if the user has no watched items', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      const result = await personalizationRepo.getWatchedItems('user-123', mockLogger);
      expect(result).toEqual([]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockQuery.mockRejectedValue(dbError);
      await expect(personalizationRepo.getWatchedItems('user-123', mockLogger)).rejects.toThrow('Failed to retrieve watched items.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: 'user-123' }, 'Database error in getWatchedItems');
    });
  });

  describe('addWatchedItem', () => {
    it('should execute a transaction to add a watched item', async () => {
      const mockClientQuery = vi.fn();
      const mockItem: MasterGroceryItem = { master_grocery_item_id: 1, name: 'New Item', created_at: '' };
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: mockClientQuery };
        mockClientQuery
          .mockResolvedValueOnce({ rows: [{ category_id: 1 }] }) // Find category
          .mockResolvedValueOnce({ rows: [mockItem] }) // Find master item
          .mockResolvedValueOnce({ rows: [] }); // Insert into watchlist
        return callback(mockClient as unknown as PoolClient);
      });

      await personalizationRepo.addWatchedItem('user-123', 'New Item', 'Produce', mockLogger);

      expect(withTransaction).toHaveBeenCalledTimes(1);
      expect(mockClientQuery).toHaveBeenCalledWith(expect.stringContaining('SELECT category_id FROM public.categories'), ['Produce']);
      expect(mockClientQuery).toHaveBeenCalledWith(expect.stringContaining('SELECT * FROM public.master_grocery_items'), ['New Item']);
      expect(mockClientQuery).toHaveBeenCalledWith(expect.stringContaining('INSERT INTO public.user_watched_items'), ['user-123', 1]);
    });

    it('should create a new master item if it does not exist', async () => {
      const mockClientQuery = vi.fn();
      const mockNewItem: MasterGroceryItem = { master_grocery_item_id: 2, name: 'Brand New Item', created_at: '' };
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: mockClientQuery };
        mockClientQuery
          .mockResolvedValueOnce({ rows: [{ category_id: 1 }] }) // Find category
          .mockResolvedValueOnce({ rows: [] }) // Find master item (not found)
          .mockResolvedValueOnce({ rows: [mockNewItem] }) // INSERT new master item
          .mockResolvedValueOnce({ rows: [] }); // Insert into watchlist
        return callback(mockClient as unknown as PoolClient);
      });

      const result = await personalizationRepo.addWatchedItem('user-123', 'Brand New Item', 'Produce', mockLogger);

      expect(mockClientQuery).toHaveBeenCalledWith(expect.stringContaining('INSERT INTO public.master_grocery_items'), ['Brand New Item', 1]);
      expect(result).toEqual(mockNewItem);
    });

    it('should not throw an error if the item is already in the watchlist', async () => {
      const mockClientQuery = vi.fn();
      const mockExistingItem: MasterGroceryItem = { master_grocery_item_id: 1, name: 'Existing Item', created_at: '' };
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: mockClientQuery };
        mockClientQuery
          .mockResolvedValueOnce({ rows: [{ category_id: 1 }] }) // Find category
          .mockResolvedValueOnce({ rows: [mockExistingItem] }) // Find master item
          .mockResolvedValueOnce({ rows: [] }); // INSERT...ON CONFLICT
        return callback(mockClient as unknown as PoolClient);
      });

      // The function should resolve successfully without throwing an error.
      await expect(personalizationRepo.addWatchedItem('user-123', 'Existing Item', 'Produce', mockLogger)).resolves.toEqual(mockExistingItem);
      expect(mockClientQuery).toHaveBeenCalledWith(expect.stringContaining('ON CONFLICT (user_id, master_item_id) DO NOTHING'), ['user-123', 1]);
    });

    it('should throw an error if the category is not found', async () => {
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: vi.fn().mockResolvedValue({ rows: [] }) };
        await expect(callback(mockClient as unknown as PoolClient)).rejects.toThrow("Category 'Fake Category' not found.");
        throw new Error("Category 'Fake Category' not found.");
      });

      await expect(personalizationRepo.addWatchedItem('user-123', 'Some Item', 'Fake Category', mockLogger)).rejects.toThrow("Failed to add item to watchlist.");
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error), userId: 'user-123', itemName: 'Some Item', categoryName: 'Fake Category' }, 'Transaction error in addWatchedItem');
    });

    it('should throw a generic error on failure', async () => {
      const dbError = new Error('DB Error');
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: vi.fn() };
        mockClient.query.mockResolvedValueOnce({ rows: [{ category_id: 1 }] }).mockRejectedValueOnce(dbError);
        await expect(callback(mockClient as unknown as PoolClient)).rejects.toThrow(dbError);
        throw dbError;
      });

      await expect(personalizationRepo.addWatchedItem('user-123', 'Failing Item', 'Produce', mockLogger)).rejects.toThrow('Failed to add item to watchlist.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: 'user-123', itemName: 'Failing Item', categoryName: 'Produce' }, 'Transaction error in addWatchedItem');
    });

    it('should throw ForeignKeyConstraintError on invalid user or category', async () => {
      const dbError = new Error('violates foreign key constraint');
      (dbError as Error & { code: string }).code = '23503';
      vi.mocked(withTransaction).mockRejectedValue(dbError);

      await expect(personalizationRepo.addWatchedItem('non-existent-user', 'Some Item', 'Produce', mockLogger)).rejects.toThrow('The specified user or category does not exist.');
    });
  });

  describe('removeWatchedItem', () => {
    it('should execute a DELETE query', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      await personalizationRepo.removeWatchedItem('user-123', 1, mockLogger);
      expect(mockQuery).toHaveBeenCalledWith('DELETE FROM public.user_watched_items WHERE user_id = $1 AND master_item_id = $2', ['user-123', 1]
      );
    });

    it('should complete without error if the item to remove is not in the watchlist', async () => {
      // Simulate the DB returning 0 rows affected
      mockQuery.mockResolvedValue({ rowCount: 0 });
      await expect(personalizationRepo.removeWatchedItem('user-123', 999, mockLogger)).resolves.toBeUndefined();
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockQuery.mockRejectedValue(dbError);
      await expect(personalizationRepo.removeWatchedItem('user-123', 1, mockLogger)).rejects.toThrow('Failed to remove item from watchlist.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: 'user-123', masterItemId: 1 }, 'Database error in removeWatchedItem');
    });
  });

  describe('findRecipesFromPantry', () => {
    it('should call the correct database function', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      await personalizationRepo.findRecipesFromPantry('user-123', mockLogger);
      expect(mockQuery).toHaveBeenCalledWith('SELECT * FROM public.find_recipes_from_pantry($1)', ['user-123']);
    });

    it('should return an empty array if no recipes are found', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      const result = await personalizationRepo.findRecipesFromPantry('user-123', mockLogger);
      expect(result).toEqual([]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockQuery.mockRejectedValue(dbError);
      await expect(personalizationRepo.findRecipesFromPantry('user-123', mockLogger)).rejects.toThrow('Failed to find recipes from pantry.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: 'user-123' }, 'Database error in findRecipesFromPantry');
    });
  });

  describe('recommendRecipesForUser', () => {
    it('should call the correct database function', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      await personalizationRepo.recommendRecipesForUser('user-123', 5, mockLogger);
      expect(mockQuery).toHaveBeenCalledWith('SELECT * FROM public.recommend_recipes_for_user($1, $2)', ['user-123', 5]);
    });

    it('should return an empty array if no recipes are recommended', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      const result = await personalizationRepo.recommendRecipesForUser('user-123', 5, mockLogger);
      expect(result).toEqual([]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockQuery.mockRejectedValue(dbError);
      await expect(personalizationRepo.recommendRecipesForUser('user-123', 5, mockLogger)).rejects.toThrow('Failed to recommend recipes.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: 'user-123', limit: 5 }, 'Database error in recommendRecipesForUser');
    });
  });

  describe('getBestSalePricesForUser', () => {
    it('should call the correct database function', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      await personalizationRepo.getBestSalePricesForUser('user-123', mockLogger);
      expect(mockQuery).toHaveBeenCalledWith('SELECT * FROM public.get_best_sale_prices_for_user($1)', ['user-123']);
    });

    it('should return an empty array if no deals are found for the user', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      const result = await personalizationRepo.getBestSalePricesForUser('user-123', mockLogger);
      expect(result).toEqual([]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockQuery.mockRejectedValue(dbError);
      await expect(personalizationRepo.getBestSalePricesForUser('user-123', mockLogger)).rejects.toThrow('Failed to get best sale prices.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: 'user-123' }, 'Database error in getBestSalePricesForUser');
    });
  });

  describe('getBestSalePricesForAllUsers', () => {
    it('should call the correct database function', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      await personalizationRepo.getBestSalePricesForAllUsers(mockLogger);
      expect(mockQuery).toHaveBeenCalledWith('SELECT * FROM public.get_best_sale_prices_for_all_users()');
    });

    it('should return an empty array if no deals are found for any user', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      const result = await personalizationRepo.getBestSalePricesForAllUsers(mockLogger);
      expect(result).toEqual([]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockQuery.mockRejectedValue(dbError);
      await expect(personalizationRepo.getBestSalePricesForAllUsers(mockLogger)).rejects.toThrow('Failed to get best sale prices for all users.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError }, 'Database error in getBestSalePricesForAllUsers');
    });
  });

  describe('suggestPantryItemConversions', () => {
    it('should call the correct database function', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      await personalizationRepo.suggestPantryItemConversions(1, mockLogger);
      expect(mockQuery).toHaveBeenCalledWith('SELECT * FROM public.suggest_pantry_item_conversions($1)', [1]);
    });

    it('should return an empty array if no conversions are suggested', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      const result = await personalizationRepo.suggestPantryItemConversions(1, mockLogger);
      expect(result).toEqual([]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockQuery.mockRejectedValue(dbError);
      await expect(personalizationRepo.suggestPantryItemConversions(1, mockLogger)).rejects.toThrow('Failed to suggest pantry item conversions.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, pantryItemId: 1 }, 'Database error in suggestPantryItemConversions');
    });
  });

  describe('findPantryItemOwner', () => {
    it('should execute a SELECT query to find the owner', async () => {
      mockQuery.mockResolvedValue({ rows: [{ user_id: 'user-123' }] });
      const result = await personalizationRepo.findPantryItemOwner(1, mockLogger);
      expect(mockQuery).toHaveBeenCalledWith('SELECT user_id FROM public.pantry_items WHERE pantry_item_id = $1', [1]);
      expect(result?.user_id).toBe('user-123');
    });

    it('should return undefined if the pantry item is not found', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      const result = await personalizationRepo.findPantryItemOwner(999, mockLogger);
      expect(result).toBeUndefined();
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockQuery.mockRejectedValue(dbError);
      await expect(personalizationRepo.findPantryItemOwner(1, mockLogger)).rejects.toThrow('Failed to retrieve pantry item owner from database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, pantryItemId: 1 }, 'Database error in findPantryItemOwner');
    });
  });

  describe('getDietaryRestrictions', () => {
    it('should execute a SELECT query to get all restrictions', async () => {
      mockQuery.mockResolvedValue({ rows: [] as DietaryRestriction[] });
      await personalizationRepo.getDietaryRestrictions(mockLogger);
      expect(mockQuery).toHaveBeenCalledWith('SELECT * FROM public.dietary_restrictions ORDER BY type, name');
    });

    it('should return an empty array if no restrictions exist', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      const result = await personalizationRepo.getDietaryRestrictions(mockLogger);
      expect(result).toEqual([]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockQuery.mockRejectedValue(dbError);
      await expect(personalizationRepo.getDietaryRestrictions(mockLogger)).rejects.toThrow('Failed to get dietary restrictions.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError }, 'Database error in getDietaryRestrictions');
    });
  });

  describe('setUserDietaryRestrictions', () => {
    it('should execute a transaction to set restrictions', async () => {
      const mockClientQuery = vi.fn().mockResolvedValue({ rows: [] });
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: mockClientQuery };
        return callback(mockClient as unknown as PoolClient);
      });

      await personalizationRepo.setUserDietaryRestrictions('user-123', [1, 2], mockLogger);

      expect(withTransaction).toHaveBeenCalledTimes(1);
      expect(mockClientQuery).toHaveBeenCalledWith('DELETE FROM public.user_dietary_restrictions WHERE user_id = $1', ['user-123']);
      expect(mockClientQuery).toHaveBeenCalledWith(expect.stringContaining('INSERT INTO public.user_dietary_restrictions'), ['user-123', [1, 2]]);
    });

    it('should throw ForeignKeyConstraintError if a restriction ID is invalid', async () => {
      const dbError = new Error('violates foreign key constraint');
      (dbError as Error & { code: string }).code = '23503';
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: vi.fn() };
        mockClient.query.mockResolvedValueOnce({ rows: [] }).mockRejectedValueOnce(dbError); // DELETE ok, INSERT fail
        await expect(callback(mockClient as unknown as PoolClient)).rejects.toThrow(dbError);
        throw dbError;
      });

      await expect(personalizationRepo.setUserDietaryRestrictions('user-123', [999], mockLogger)).rejects.toThrow('One or more of the specified restriction IDs are invalid.');
    });

    it('should handle an empty array of restriction IDs', async () => {
      const mockClientQuery = vi.fn().mockResolvedValue({ rows: [] });
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: mockClientQuery };
        return callback(mockClient as unknown as PoolClient);
      });

      await personalizationRepo.setUserDietaryRestrictions('user-123', [], mockLogger);

      expect(withTransaction).toHaveBeenCalledTimes(1);
      expect(mockClientQuery).toHaveBeenCalledWith('DELETE FROM public.user_dietary_restrictions WHERE user_id = $1', ['user-123']);
      expect(mockClientQuery).not.toHaveBeenCalledWith(expect.stringContaining('INSERT INTO'));
    });

    it('should throw a generic error if the database query fails', async () => {
      vi.mocked(withTransaction).mockRejectedValue(new Error('DB Error'));
      await expect(personalizationRepo.setUserDietaryRestrictions('user-123', [1], mockLogger)).rejects.toThrow('Failed to set user dietary restrictions.');
    });
  });

  describe('getAppliances', () => {
    it('should execute a SELECT query to get all appliances', async () => {
      mockQuery.mockResolvedValue({ rows: [] as Appliance[] });
      await personalizationRepo.getAppliances(mockLogger);
      expect(mockQuery).toHaveBeenCalledWith('SELECT * FROM public.appliances ORDER BY name');
    });

    it('should return an empty array if no appliances exist', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      const result = await personalizationRepo.getAppliances(mockLogger);
      expect(result).toEqual([]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockQuery.mockRejectedValue(dbError);
      await expect(personalizationRepo.getAppliances(mockLogger)).rejects.toThrow('Failed to get appliances.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError }, 'Database error in getAppliances');
    });
  });

  describe('getUserAppliances', () => {
    it('should execute a SELECT query with a JOIN', async () => {
      mockQuery.mockResolvedValue({ rows: [] as Appliance[] });
      await personalizationRepo.getUserAppliances('user-123', mockLogger);
      expect(mockQuery).toHaveBeenCalledWith(expect.stringContaining('FROM public.appliances a'), ['user-123']);
    });

    it('should return an empty array if the user has no appliances', async () => {
      mockQuery.mockResolvedValue({ rows: [] as Appliance[] });
      const result = await personalizationRepo.getUserAppliances('user-123', mockLogger);
      expect(result).toEqual([]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockQuery.mockRejectedValue(dbError);
      await expect(personalizationRepo.getUserAppliances('user-123', mockLogger)).rejects.toThrow('Failed to get user appliances.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: 'user-123' }, 'Database error in getUserAppliances');
    });
  });

  describe('setUserAppliances', () => {
    it('should execute a transaction to set appliances', async () => {
      const mockNewAppliances: UserAppliance[] = [
        { user_id: 'user-123', appliance_id: 1 },
        { user_id: 'user-123', appliance_id: 2 },
      ];
      const mockClientQuery = vi.fn();
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: mockClientQuery };
        mockClientQuery
          .mockResolvedValueOnce({ rows: [] }) // DELETE
          .mockResolvedValueOnce({ rows: mockNewAppliances }); // INSERT
        return callback(mockClient as unknown as PoolClient);
      });

      const result = await personalizationRepo.setUserAppliances('user-123', [1, 2], mockLogger);

      expect(withTransaction).toHaveBeenCalledTimes(1);
      expect(mockClientQuery).toHaveBeenCalledWith('DELETE FROM public.user_appliances WHERE user_id = $1', ['user-123']);
      expect(mockClientQuery).toHaveBeenCalledWith(expect.stringContaining('INSERT INTO public.user_appliances'), ['user-123', [1, 2]]);
      expect(result).toEqual(mockNewAppliances);
    });

    it('should throw ForeignKeyConstraintError if an appliance ID is invalid', async () => {
      const dbError = new Error('violates foreign key constraint');
      (dbError as Error & { code: string }).code = '23503';
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: vi.fn() };
        mockClient.query.mockResolvedValueOnce({ rows: [] }).mockRejectedValueOnce(dbError); // DELETE ok, INSERT fail
        await expect(callback(mockClient as unknown as PoolClient)).rejects.toThrow(dbError);
        throw dbError;
      });

      await expect(personalizationRepo.setUserAppliances('user-123', [999], mockLogger)).rejects.toThrow(ForeignKeyConstraintError);
    });

    it('should handle an empty array of appliance IDs', async () => {
      const mockClientQuery = vi.fn().mockResolvedValue({ rows: [] });
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: mockClientQuery };
        return callback(mockClient as unknown as PoolClient);
      });

      const result = await personalizationRepo.setUserAppliances('user-123', [], mockLogger);

      expect(withTransaction).toHaveBeenCalledTimes(1);
      expect(mockClientQuery).toHaveBeenCalledWith('DELETE FROM public.user_appliances WHERE user_id = $1', ['user-123']);
      // The INSERT query should NOT be called
      expect(mockClientQuery).not.toHaveBeenCalledWith(expect.stringContaining('INSERT INTO'));
      expect(result).toEqual([]);
    });

    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().mockRejectedValue(dbError) };
        await expect(callback(mockClient as unknown as PoolClient)).rejects.toThrow(dbError);
        throw dbError;
      });

      await expect(personalizationRepo.setUserAppliances('user-123', [1], mockLogger)).rejects.toThrow('Failed to set user appliances.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: 'user-123', applianceIds: [1] }, 'Database error in setUserAppliances');
    });
  });

  describe('getRecipesForUserDiets', () => {
    it('should call the correct database function', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      await personalizationRepo.getRecipesForUserDiets('user-123', mockLogger);
      expect(mockQuery).toHaveBeenCalledWith('SELECT * FROM public.get_recipes_for_user_diets($1)', ['user-123']);
    });

    it('should return an empty array if no recipes match the diet', async () => {
      mockQuery.mockResolvedValue({ rows: [] });
      const result = await personalizationRepo.getRecipesForUserDiets('user-123', mockLogger);
      expect(result).toEqual([]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockQuery.mockRejectedValue(dbError);
      await expect(personalizationRepo.getRecipesForUserDiets('user-123', mockLogger)).rejects.toThrow('Failed to get recipes compatible with user diet.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: 'user-123' }, 'Database error in getRecipesForUserDiets');
    });
  });
});