// src/services/db/flyer.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 { createMockFlyer, createMockFlyerItem, createMockBrand } from '../../tests/utils/mockFactories';

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

import { FlyerRepository, createFlyerAndItems } from './flyer.db';
import { UniqueConstraintError, ForeignKeyConstraintError, NotFoundError } from './errors.db';
import type { FlyerInsert, FlyerItemInsert, Brand, Flyer, FlyerItem, FlyerDbInsert } from '../../types';

// Mock dependencies
vi.mock('../logger.server', () => ({
  logger: { info: vi.fn(), warn: vi.fn(), 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('Flyer DB Service', () => {
  let flyerRepo: FlyerRepository;

  beforeEach(() => {
    vi.clearAllMocks();

    // In a transaction, `pool.connect()` returns a client. That client has a `release` method.
    // For these tests, we simulate this by having `connect` resolve to the pool instance itself,
    // and we ensure the `release` method is mocked on that instance.
    const mockClient = { ...mockPoolInstance, release: vi.fn() } as unknown as PoolClient;
    vi.mocked(mockPoolInstance.connect).mockResolvedValue(mockClient);

    flyerRepo = new FlyerRepository(mockPoolInstance as unknown as Pool);
  });

  describe('findOrCreateStore', () => {
    it('should find an existing store and return its ID', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [{ store_id: 1 }] });
      const result = await flyerRepo.findOrCreateStore('Existing Store', mockLogger);
      expect(result).toBe(1);
      expect(mockPoolInstance.query).toHaveBeenCalledWith('SELECT store_id FROM public.stores WHERE name = $1', ['Existing Store']);
    });

    it('should create a new store if it does not exist', async () => {
      mockPoolInstance.query
        .mockResolvedValueOnce({ rows: [] }) // First SELECT finds nothing
        .mockResolvedValueOnce({ rows: [{ store_id: 2 }] }); // INSERT returns new ID
      const result = await flyerRepo.findOrCreateStore('New Store', mockLogger);
      expect(result).toBe(2);
      expect(mockPoolInstance.query).toHaveBeenCalledWith('INSERT INTO public.stores (name) VALUES ($1) RETURNING store_id', ['New Store']);
    });

    it('should handle race condition where store is created between SELECT and INSERT', async () => {
      const uniqueConstraintError = new Error('duplicate key value violates unique constraint');
      (uniqueConstraintError as Error & { code: string }).code = '23505';

      mockPoolInstance.query
        .mockResolvedValueOnce({ rows: [] }) // First SELECT finds nothing
        .mockRejectedValueOnce(uniqueConstraintError) // INSERT fails due to race condition
        .mockResolvedValueOnce({ rows: [{ store_id: 3 }] }); // Second SELECT finds the store

      const result = await flyerRepo.findOrCreateStore('Racy Store', mockLogger);
      expect(result).toBe(3);
      expect(mockPoolInstance.query).toHaveBeenCalledTimes(3);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(flyerRepo.findOrCreateStore('Any Store', mockLogger)).rejects.toThrow('Failed to find or create store in database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, storeName: 'Any Store' }, 'Database error in findOrCreateStore');
    });

    it('should throw an error if race condition recovery fails', async () => {
      const uniqueConstraintError = new Error('duplicate key value violates unique constraint');
      (uniqueConstraintError as Error & { code: string }).code = '23505';

      mockPoolInstance.query
        .mockResolvedValueOnce({ rows: [] }) // First SELECT
        .mockRejectedValueOnce(uniqueConstraintError) // INSERT fails
        .mockRejectedValueOnce(new Error('Second select fails')); // Recovery SELECT fails

      await expect(flyerRepo.findOrCreateStore('Racy Store', mockLogger)).rejects.toThrow('Failed to find or create store in database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error), storeName: 'Racy Store' }, 'Database error in findOrCreateStore');
    });
  });

  describe('insertFlyer', () => {
    it('should execute an INSERT query and return the new flyer', async () => {
      const flyerData: FlyerDbInsert = {
        file_name: 'test.jpg',
        image_url: '/images/test.jpg',
        icon_url: '/images/icons/test.jpg',
        checksum: 'checksum123',
        store_id: 1,
        valid_from: '2024-01-01',
        valid_to: '2024-01-07',
        store_address: '123 Test St',
        item_count: 10,
        uploaded_by: 'user-1',
      };
      const mockFlyer = createMockFlyer({ ...flyerData, flyer_id: 1 });
      mockPoolInstance.query.mockResolvedValue({ rows: [mockFlyer] });

      const result = await flyerRepo.insertFlyer(flyerData, mockLogger);

      expect(result).toEqual(mockFlyer);
      expect(mockPoolInstance.query).toHaveBeenCalledTimes(1);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        expect.stringContaining('INSERT INTO flyers'),
        [
          'test.jpg',
          '/images/test.jpg',
          '/images/icons/test.jpg',
          'checksum123',
          1,
          '2024-01-01',
          '2024-01-07',
          '123 Test St',
          10,
          'user-1',
        ]
      );
    });

    it('should throw UniqueConstraintError on duplicate checksum', async () => {
      const flyerData: FlyerDbInsert = { checksum: 'duplicate-checksum' } as FlyerDbInsert;
      const dbError = new Error('duplicate key value violates unique constraint "flyers_checksum_key"');
      (dbError as Error & { code: string }).code = '23505';
      mockPoolInstance.query.mockRejectedValue(dbError);

      await expect(flyerRepo.insertFlyer(flyerData, mockLogger)).rejects.toThrow(UniqueConstraintError);
      await expect(flyerRepo.insertFlyer(flyerData, mockLogger)).rejects.toThrow('A flyer with this checksum already exists.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, flyerData }, 'Database error in insertFlyer');
    });

    it('should throw a generic error if the database query fails', async () => {
      const flyerData: FlyerDbInsert = { checksum: 'fail-checksum' } as FlyerDbInsert;
      mockPoolInstance.query.mockRejectedValue(new Error('DB Connection Error'));
      await expect(flyerRepo.insertFlyer(flyerData, mockLogger)).rejects.toThrow('Failed to insert flyer into database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error), flyerData }, 'Database error in insertFlyer');
    });
  });

  describe('insertFlyerItems', () => {
    it('should build a bulk INSERT query and return the new items', async () => {
      const itemsData: FlyerItemInsert[] = [
        { item: 'Milk', price_display: '$3.99', price_in_cents: 399, quantity: '1L', category_name: 'Dairy', view_count: 0, click_count: 0 },
        { item: 'Bread', price_display: '$2.49', price_in_cents: 249, quantity: '1 loaf', category_name: 'Bakery', view_count: 0, click_count: 0 },
      ];
      const mockItems = itemsData.map((item, i) => createMockFlyerItem({ ...item, flyer_item_id: i + 1, flyer_id: 1 }));
      mockPoolInstance.query.mockResolvedValue({ rows: mockItems });

      const result = await flyerRepo.insertFlyerItems(1, itemsData, mockLogger);

      expect(result).toEqual(mockItems);
      expect(mockPoolInstance.query).toHaveBeenCalledTimes(1);
      // Check that the query string has two value placeholders
      expect(mockPoolInstance.query.mock.calls[0][0]).toContain('VALUES ($1, $2, $3, $4, $5, $6, $7, $8), ($9, $10, $11, $12, $13, $14, $15, $16)');
      // Check that the values array is correctly flattened
      expect(mockPoolInstance.query.mock.calls[0][1]).toEqual([
        1, 'Milk', '$3.99', 399, '1L', 'Dairy', 0, 0,
        1, 'Bread', '$2.49', 249, '1 loaf', 'Bakery', 0, 0,
      ]);
    });

    it('should return an empty array and not query the DB if items array is empty', async () => {
      const result = await flyerRepo.insertFlyerItems(1, [], mockLogger);
      expect(result).toEqual([]);
      expect(mockPoolInstance.query).not.toHaveBeenCalled();
    });

    it('should throw ForeignKeyConstraintError if flyerId is invalid', async () => {
      const itemsData: FlyerItemInsert[] = [{ item: 'Test', price_display: '$1', price_in_cents: 100, quantity: '1', category_name: 'Test', view_count: 0, click_count: 0 }];
      const dbError = new Error('insert or update on table "flyer_items" violates foreign key constraint "flyer_items_flyer_id_fkey"');
      (dbError as Error & { code: string }).code = '23503';
      mockPoolInstance.query.mockRejectedValue(dbError);

      await expect(flyerRepo.insertFlyerItems(999, itemsData, mockLogger)).rejects.toThrow(ForeignKeyConstraintError);
      await expect(flyerRepo.insertFlyerItems(999, itemsData, mockLogger)).rejects.toThrow('The specified flyer does not exist.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, flyerId: 999 }, 'Database error in insertFlyerItems');
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Connection Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      // The implementation now re-throws the original error, so we should expect that.
      await expect(flyerRepo.insertFlyerItems(1, [{ item: 'Test' } as FlyerItemInsert], mockLogger)).rejects.toThrow(dbError);
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, flyerId: 1 }, 'Database error in insertFlyerItems');
    });
  });

  describe('createFlyerAndItems', () => {
    it('should use withTransaction to create a flyer and items', async () => {
      const flyerData: FlyerInsert = { file_name: 'transact.jpg', store_name: 'Transaction Store' } as FlyerInsert;
      const itemsData: FlyerItemInsert[] = [{
        item: 'Transactional Item',
        price_in_cents: 199,
        quantity: 'each',
        view_count: 0,
        click_count: 0,
      } as FlyerItemInsert];
      const mockFlyer = createMockFlyer({ ...flyerData, flyer_id: 99, store_id: 1 });
      const mockItems = [createMockFlyerItem({ ...itemsData[0], flyer_id: 99, flyer_item_id: 101, master_item_id: undefined })];

      // Mock the withTransaction to execute the callback with a mock client
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: vi.fn() };
        // Mock the sequence of calls within the transaction
        mockClient.query
          .mockResolvedValueOnce({ rows: [{ store_id: 1 }] }) // findOrCreateStore
          .mockResolvedValueOnce({ rows: [mockFlyer] }) // insertFlyer
          .mockResolvedValueOnce({ rows: mockItems }); // insertFlyerItems
        return callback(mockClient as unknown as PoolClient);
      });

      const result = await createFlyerAndItems(flyerData, itemsData, mockLogger);

      expect(result).toEqual({
        flyer: mockFlyer,
        items: mockItems
      });
      expect(withTransaction).toHaveBeenCalledTimes(1);

      // Verify the individual functions were called with the client
      const callback = (vi.mocked(withTransaction) as Mock).mock.calls[0][0];
      const mockClient = { query: vi.fn() };
      mockClient.query.mockResolvedValueOnce({ rows: [{ store_id: 1 }] }).mockResolvedValueOnce({ rows: [mockFlyer] }).mockResolvedValueOnce({ rows: mockItems });
      await callback(mockClient as unknown as PoolClient);
      expect(mockClient.query).toHaveBeenCalledWith(expect.stringContaining('SELECT store_id FROM public.stores'), ['Transaction Store']);
      expect(mockClient.query).toHaveBeenCalledWith(expect.stringContaining('INSERT INTO flyers'), expect.any(Array));
      expect(mockClient.query).toHaveBeenCalledWith(expect.stringContaining('INSERT INTO flyer_items'), expect.any(Array));
    });

    it('should ROLLBACK the transaction if an error occurs', async () => {
      const flyerData: FlyerInsert = { file_name: 'fail.jpg', store_name: 'Fail Store' } as FlyerInsert;
      const itemsData: FlyerItemInsert[] = [{ item: 'Failing Item' } as FlyerItemInsert];
      const dbError = new Error('DB connection lost');

      // Mock withTransaction to simulate a failure during the callback
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: vi.fn() };
        mockClient.query
          .mockResolvedValueOnce({ rows: [{ store_id: 1 }] }) // findOrCreateStore
          .mockRejectedValueOnce(dbError); // insertFlyer fails
        // The withTransaction helper will catch this and roll back.
        // Since insertFlyer wraps the DB error, we expect the wrapped error message here.
        await expect(callback(mockClient as unknown as PoolClient)).rejects.toThrow('Failed to insert flyer into database.');
        // re-throw because withTransaction re-throws (simulating the wrapped error propagating up)
        throw new Error('Failed to insert flyer into database.');
      });

      // The transactional function re-throws the original error from the failed step.
      // Since insertFlyer wraps errors, we expect the wrapped error message.
      await expect(createFlyerAndItems(flyerData, itemsData, mockLogger)).rejects.toThrow('Failed to insert flyer into database.');
      // The error object passed to the logger will be the wrapped Error object, not the original dbError
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error) }, 'Database transaction error in createFlyerAndItems');
      expect(withTransaction).toHaveBeenCalledTimes(1);
    });
  });

  describe('getAllBrands', () => {
    it('should execute the correct SELECT query and return brands', async () => {
      const mockBrands: Brand[] = [createMockBrand({ brand_id: 1, name: 'Test Brand' })];
      mockPoolInstance.query.mockResolvedValue({ rows: mockBrands });

      const result = await flyerRepo.getAllBrands(mockLogger);

      expect(result).toEqual(mockBrands);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('FROM public.stores s'));
    });

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

  describe('getFlyerById', () => {
    it('should return a flyer if found', async () => {
      const mockFlyer = createMockFlyer({ flyer_id: 123 });
      mockPoolInstance.query.mockResolvedValue({ rows: [mockFlyer] });

      const result = await flyerRepo.getFlyerById(123);

      expect(result).toEqual(mockFlyer);
      expect(mockPoolInstance.query).toHaveBeenCalledWith('SELECT * FROM public.flyers WHERE flyer_id = $1', [123]);
    });

    it('should throw NotFoundError if flyer is not found', async () => {
      mockPoolInstance.query.mockResolvedValue({ rowCount: 0, rows: [] });
      await expect(flyerRepo.getFlyerById(999)).rejects.toThrow(NotFoundError);
      await expect(flyerRepo.getFlyerById(999)).rejects.toThrow('Flyer with ID 999 not found.');
    });
  });

  describe('getFlyers', () => {
    it('should use default limit and offset when none are provided', async () => {
      const mockFlyers: Flyer[] = [createMockFlyer({ flyer_id: 1 })];
      mockPoolInstance.query.mockResolvedValue({ rows: mockFlyers });

      await flyerRepo.getFlyers(mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        'SELECT * FROM public.flyers ORDER BY created_at DESC LIMIT $1 OFFSET $2',
        [20, 0] // Default values
      );
    });

    it('should use provided limit and offset values', async () => {
      const mockFlyers: Flyer[] = [createMockFlyer({ flyer_id: 1 })];
      mockPoolInstance.query.mockResolvedValue({ rows: mockFlyers });

      await flyerRepo.getFlyers(mockLogger, 10, 5);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        'SELECT * FROM public.flyers ORDER BY created_at DESC LIMIT $1 OFFSET $2',
        [10, 5] // Provided values
      );
    });

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

  describe('getFlyerItems', () => {
    it('should return items for a specific flyer', async () => {
      const mockItems: FlyerItem[] = [createMockFlyerItem({ flyer_id: 456 })];
      mockPoolInstance.query.mockResolvedValue({ rows: mockItems });

      const result = await flyerRepo.getFlyerItems(456, mockLogger);

      expect(result).toEqual(mockItems);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('WHERE flyer_id = $1'), [456]);
    });

    it('should return an empty array if flyer has no items', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      const result = await flyerRepo.getFlyerItems(456, mockLogger);
      expect(result).toEqual([]);
    });

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

  describe('getFlyerItemsForFlyers', () => {
    it('should return items for multiple flyers using ANY', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await flyerRepo.getFlyerItemsForFlyers([1, 2, 3], mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('flyer_id = ANY($1::int[])'), [[1, 2, 3]]);
    });

    it('should return an empty array if no items are found for the given flyer IDs', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      const result = await flyerRepo.getFlyerItemsForFlyers([1, 2, 3], mockLogger);
      expect(result).toEqual([]);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(flyerRepo.getFlyerItemsForFlyers([1, 2, 3], mockLogger)).rejects.toThrow('Failed to retrieve flyer items in batch from database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, flyerIds: [1, 2, 3] }, 'Database error in getFlyerItemsForFlyers');
    });
  });

  describe('countFlyerItemsForFlyers', () => {
    it('should return the total count of items', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [{ count: '42' }] });
      const result = await flyerRepo.countFlyerItemsForFlyers([1, 2], mockLogger);
      expect(result).toBe(42);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('SELECT COUNT(*)'), [[1, 2]]);
    });

    it('should return 0 if the flyerIds array is empty', async () => {
      // The implementation should short-circuit and return 0 without a query.
      const result = await flyerRepo.countFlyerItemsForFlyers([], mockLogger);
      expect(result).toBe(0);
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(flyerRepo.countFlyerItemsForFlyers([1, 2], mockLogger)).rejects.toThrow('Failed to count flyer items in batch from database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, flyerIds: [1, 2] }, 'Database error in countFlyerItemsForFlyers');
    });
  });

  describe('findFlyerByChecksum', () => {
    it('should return a flyer for a given checksum', async () => {
      const mockFlyer = createMockFlyer({ checksum: 'abc' });
      mockPoolInstance.query.mockResolvedValue({ rows: [mockFlyer] });
      const result = await flyerRepo.findFlyerByChecksum('abc', mockLogger);
      expect(result).toEqual(mockFlyer);
      expect(mockPoolInstance.query).toHaveBeenCalledWith('SELECT * FROM public.flyers WHERE checksum = $1', ['abc']);
    });

    it('should return undefined if no flyer is found for the checksum', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      const result = await flyerRepo.findFlyerByChecksum('not-found', mockLogger);
      expect(result).toBeUndefined();
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(flyerRepo.findFlyerByChecksum('abc', mockLogger)).rejects.toThrow('Failed to find flyer by checksum in database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, checksum: 'abc' }, 'Database error in findFlyerByChecksum');
    });
  });

  describe('trackFlyerItemInteraction', () => {
    it('should increment view_count for a "view" interaction', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await flyerRepo.trackFlyerItemInteraction(101, 'view', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('SET view_count = view_count + 1'), [101]);
    });

    it('should increment click_count for a "click" interaction', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await flyerRepo.trackFlyerItemInteraction(102, 'click', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('SET click_count = click_count + 1'), [102]);
    });

    it('should not throw an error if the database query fails (fire-and-forget)', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      // The function is designed to swallow errors, so we expect it to resolve.
      await expect(flyerRepo.trackFlyerItemInteraction(103, 'view', mockLogger)).resolves.toBeUndefined();
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, flyerItemId: 103, interactionType: 'view' }, 'Database error in trackFlyerItemInteraction (non-critical)');
    });
  });

  describe('deleteFlyer', () => {
    it('should use withTransaction to delete a flyer', async () => {
      // Create a mock client that we can reference both inside and outside the transaction mock.
      const mockClientQuery = vi.fn();

      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: mockClientQuery };
        mockClientQuery.mockResolvedValueOnce({ rowCount: 1 });
        return callback(mockClient as unknown as PoolClient);
      });

      await flyerRepo.deleteFlyer(42, mockLogger);

      expect(mockClientQuery).toHaveBeenCalledWith('DELETE FROM public.flyers WHERE flyer_id = $1', [42]);
      expect(withTransaction).toHaveBeenCalledTimes(1);
    });

    it('should throw an error if the flyer to delete is not found', async () => {
      const mockClient = { query: vi.fn().mockResolvedValue({ rowCount: 0 }) };
      vi.mocked(withTransaction).mockImplementation(cb => cb(mockClient as unknown as PoolClient));

      await expect(flyerRepo.deleteFlyer(999, mockLogger)).rejects.toThrow('Failed to delete flyer.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(NotFoundError), flyerId: 999 }, 'Database transaction error in deleteFlyer');
    });

    it('should rollback transaction on generic error', async () => {
      const dbError = new Error('DB Error');
      vi.mocked(withTransaction).mockImplementation(async (_callback) => {
        throw dbError; // Simulate error during transaction
      });

      await expect(flyerRepo.deleteFlyer(42, mockLogger)).rejects.toThrow('Failed to delete flyer.'); // This was a duplicate, fixed.
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, flyerId: 42 }, 'Database transaction error in deleteFlyer');
    });
  });
});