// src/services/db/flyer.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 {
  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,
  CheckConstraintError,
} from './errors.db';
import { DatabaseError } from '../processingErrors';
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 cacheService to bypass caching logic during tests
vi.mock('../cacheService.server', () => ({
  cacheService: {
    getOrSet: vi.fn(async (_key, callback) => callback()),
    invalidateFlyer: vi.fn(),
  },
  CACHE_TTL: { BRANDS: 3600, FLYERS: 300, FLYER_ITEMS: 600 },
  CACHE_PREFIX: { BRANDS: 'brands', FLYERS: 'flyers', FLYER_ITEMS: 'flyer_items' },
}));

// 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();
    mockPoolInstance.query.mockReset();
    //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 () => {
      // 1. INSERT...ON CONFLICT does nothing. 2. SELECT finds the store.
      mockPoolInstance.query
        .mockResolvedValueOnce({ rows: [], rowCount: 0 })
        .mockResolvedValueOnce({ rows: [{ store_id: 1 }] });

      const result = await flyerRepo.findOrCreateStore('Existing Store', mockLogger);
      expect(result).toBe(1);
      expect(mockPoolInstance.query).toHaveBeenCalledTimes(2);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        'INSERT INTO public.stores (name) VALUES ($1) ON CONFLICT (name) DO NOTHING',
        ['Existing Store'],
      );
      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 and return its ID', async () => {
      // 1. INSERT...ON CONFLICT creates the store. 2. SELECT finds it.
      mockPoolInstance.query
        .mockResolvedValueOnce({ rows: [], rowCount: 1 }) // INSERT affects 1 row
        .mockResolvedValueOnce({ rows: [{ store_id: 2 }] }); // SELECT finds the new store

      const result = await flyerRepo.findOrCreateStore('New Store', mockLogger);
      expect(result).toBe(2);
      expect(mockPoolInstance.query).toHaveBeenCalledTimes(2);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        'INSERT INTO public.stores (name) VALUES ($1) ON CONFLICT (name) DO NOTHING',
        ['New Store'],
      );
      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        'SELECT store_id FROM public.stores WHERE name = $1',
        ['New Store'],
      );
    });

    it('should throw an error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      // The new implementation uses handleDbError, which will throw a generic Error with the default message.
      await expect(flyerRepo.findOrCreateStore('Any Store', mockLogger)).rejects.toThrow(
        'Failed to find or create store in database.',
      );
      // handleDbError also logs the error.
      expect(mockLogger.error).toHaveBeenCalledWith(
        { err: dbError, storeName: 'Any Store' },
        'Database error in findOrCreateStore',
      );
    });

    it('should throw an error if store is not found after upsert (edge case)', async () => {
      // This simulates a very unlikely scenario where the store is deleted between the
      // INSERT...ON CONFLICT and the subsequent SELECT.
      mockPoolInstance.query
        .mockResolvedValueOnce({ rows: [], rowCount: 1 }) // INSERT succeeds
        .mockResolvedValueOnce({ rows: [] }); // SELECT finds nothing

      await expect(flyerRepo.findOrCreateStore('Weird Store', mockLogger)).rejects.toThrow(
        'Failed to find or create store in database.',
      );
      expect(mockLogger.error).toHaveBeenCalledWith(
        {
          err: new Error('Failed to find store immediately after upsert operation.'),
          storeName: 'Weird 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: 'https://example.com/images/test.jpg',
        icon_url: 'https://example.com/images/icons/test.jpg',
        checksum: 'checksum123',
        store_id: 1,
        valid_from: '2024-01-01',
        valid_to: '2024-01-07',
        store_address: '123 Test St',
        status: 'processed',
        item_count: 10,
        // Use a valid UUID format for the foreign key.
        uploaded_by: 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
      };
      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',
          'https://example.com/images/test.jpg',
          'https://example.com/images/icons/test.jpg',
          'checksum123',
          1,
          '2024-01-01',
          '2024-01-07',
          '123 Test St',
          'processed',
          10,
          'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
        ],
      );
    });

    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,
          code: '23505',
          constraint: undefined,
          detail: undefined,
        },
        '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',
      );
    });

    it('should throw CheckConstraintError for invalid checksum format', async () => {
      const flyerData: FlyerDbInsert = { checksum: 'short' } as FlyerDbInsert;
      const dbError = new Error('violates check constraint "flyers_checksum_check"');
      (dbError as Error & { code: string }).code = '23514'; // Check constraint violation
      mockPoolInstance.query.mockRejectedValue(dbError);

      await expect(flyerRepo.insertFlyer(flyerData, mockLogger)).rejects.toThrow(
        CheckConstraintError,
      );
      await expect(flyerRepo.insertFlyer(flyerData, mockLogger)).rejects.toThrow(
        'The provided checksum is invalid or does not meet format requirements (e.g., must be a 64-character SHA-256 hash).',
      );
    });

    it('should throw CheckConstraintError for invalid status', async () => {
      const flyerData: FlyerDbInsert = { status: 'invalid_status' } as any;
      const dbError = new Error('violates check constraint "flyers_status_check"');
      (dbError as Error & { code: string }).code = '23514';
      mockPoolInstance.query.mockRejectedValue(dbError);

      await expect(flyerRepo.insertFlyer(flyerData, mockLogger)).rejects.toThrow(
        CheckConstraintError,
      );
      await expect(flyerRepo.insertFlyer(flyerData, mockLogger)).rejects.toThrow(
        'Invalid status provided for flyer.',
      );
    });

    it('should throw CheckConstraintError for invalid URL format', async () => {
      const flyerData: FlyerDbInsert = { image_url: 'not-a-url' } as FlyerDbInsert;
      const dbError = new Error('violates check constraint "url_check"');
      (dbError as Error & { code: string }).code = '23514';
      mockPoolInstance.query.mockRejectedValue(dbError);

      await expect(flyerRepo.insertFlyer(flyerData, mockLogger)).rejects.toThrow(
        CheckConstraintError,
      );
      // The implementation generates a detailed error message with the actual URLs.
      // The base URL depends on FRONTEND_URL env var, so we match the pattern instead of exact string.
      await expect(flyerRepo.insertFlyer(flyerData, mockLogger)).rejects.toThrow(
        /\[URL_CHECK_FAIL\] Invalid URL format\. Image: 'https?:\/\/[^']+\/not-a-url', Icon: 'null'/,
      );
    });

    it('should transform relative icon_url to absolute URL with leading slash', async () => {
      const flyerData: FlyerDbInsert = {
        file_name: 'test.jpg',
        image_url: 'https://example.com/images/test.jpg',
        icon_url: '/uploads/icons/test-icon.jpg', // relative path with leading slash
        checksum: 'checksum-with-relative-icon',
        store_id: 1,
        valid_from: '2024-01-01',
        valid_to: '2024-01-07',
        store_address: '123 Test St',
        status: 'processed',
        item_count: 10,
        uploaded_by: null,
      };
      const mockFlyer = createMockFlyer({ ...flyerData, flyer_id: 1 });
      mockPoolInstance.query.mockResolvedValue({ rows: [mockFlyer] });

      await flyerRepo.insertFlyer(flyerData, mockLogger);

      // The icon_url should have been transformed to an absolute URL
      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        expect.stringContaining('INSERT INTO flyers'),
        expect.arrayContaining([
          expect.stringMatching(/^https?:\/\/.*\/uploads\/icons\/test-icon\.jpg$/),
        ]),
      );
    });

    it('should transform relative icon_url to absolute URL without leading slash', async () => {
      const flyerData: FlyerDbInsert = {
        file_name: 'test.jpg',
        image_url: 'https://example.com/images/test.jpg',
        icon_url: 'uploads/icons/test-icon.jpg', // relative path without leading slash
        checksum: 'checksum-with-relative-icon2',
        store_id: 1,
        valid_from: '2024-01-01',
        valid_to: '2024-01-07',
        store_address: '123 Test St',
        status: 'processed',
        item_count: 10,
        uploaded_by: null,
      };
      const mockFlyer = createMockFlyer({ ...flyerData, flyer_id: 1 });
      mockPoolInstance.query.mockResolvedValue({ rows: [mockFlyer] });

      await flyerRepo.insertFlyer(flyerData, mockLogger);

      // The icon_url should have been transformed to an absolute URL
      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        expect.stringContaining('INSERT INTO flyers'),
        expect.arrayContaining([
          expect.stringMatching(/^https?:\/\/.*\/uploads\/icons\/test-icon\.jpg$/),
        ]),
      );
    });
  });

  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, category, master item, or product does not exist.',
      );
      expect(mockLogger.error).toHaveBeenCalledWith(
        {
          err: dbError,
          flyerId: 999,
          code: '23503',
          constraint: undefined,
          detail: undefined,
        },
        '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 wraps the error using handleDbError
      await expect(
        flyerRepo.insertFlyerItems(1, [{ item: 'Test' } as FlyerItemInsert], mockLogger),
      ).rejects.toThrow('An unknown error occurred while inserting flyer items.');
      expect(mockLogger.error).toHaveBeenCalledWith(
        { err: dbError, flyerId: 1 },
        'Database error in insertFlyerItems',
      );
    });

    it('should sanitize empty or whitespace-only price_display to "N/A"', async () => {
      const itemsData: FlyerItemInsert[] = [
        {
          item: 'Free Item',
          price_display: '', // Empty string
          price_in_cents: 0,
          quantity: '1',
          category_name: 'Promo',
          view_count: 0,
          click_count: 0,
        },
        {
          item: 'Whitespace Item',
          price_display: '   ', // Whitespace only
          price_in_cents: null,
          quantity: '1',
          category_name: 'Promo',
          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 });

      await flyerRepo.insertFlyerItems(1, itemsData, mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledTimes(1);

      // Check that the values array passed to the query has null for price_display
      const queryValues = mockPoolInstance.query.mock.calls[0][1];
      expect(queryValues).toEqual([
        1, // flyerId for item 1
        'Free Item',
        'N/A', // Sanitized price_display for item 1
        0,
        '1',
        'Promo',
        0,
        0,
        1, // flyerId for item 2
        'Whitespace Item',
        'N/A', // Sanitized price_display for item 2
        null,
        '1',
        'Promo',
        0,
        0,
      ]);
    });
  });

  describe('createFlyerAndItems', () => {
    it('should execute find/create store, insert flyer, and insert items using the provided client', async () => {
      const flyerData: FlyerInsert = {
        // This was a duplicate, fixed.
        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 sequence of 4 calls on the client
      const mockClient = { query: vi.fn() };
      mockClient.query
        // 1. findOrCreateStore: INSERT ... ON CONFLICT
        .mockResolvedValueOnce({ rows: [], rowCount: 0 })
        // 2. findOrCreateStore: SELECT store_id
        .mockResolvedValueOnce({ rows: [{ store_id: 1 }] })
        // 3. insertFlyer
        .mockResolvedValueOnce({ rows: [mockFlyer] })
        // 4. insertFlyerItems
        .mockResolvedValueOnce({ rows: mockItems });

      const result = await createFlyerAndItems(
        flyerData,
        itemsData,
        mockLogger,
        mockClient as unknown as PoolClient,
      );

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

      // Verify the individual functions were called with the client
      // findOrCreateStore assertions
      expect(mockClient.query).toHaveBeenCalledWith(
        'INSERT INTO public.stores (name) VALUES ($1) ON CONFLICT (name) DO NOTHING',
        ['Transaction Store'],
      );
      expect(mockClient.query).toHaveBeenCalledWith(
        'SELECT store_id FROM public.stores WHERE name = $1',
        ['Transaction Store'],
      );
      // insertFlyer assertion
      expect(mockClient.query).toHaveBeenCalledWith(
        expect.stringContaining('INSERT INTO flyers'),
        expect.any(Array),
      );
      // insertFlyerItems assertion
      expect(mockClient.query).toHaveBeenCalledWith(
        expect.stringContaining('INSERT INTO flyer_items'),
        expect.any(Array),
      );
    });

    it('should create a flyer with no items if items array is empty', async () => {
      const flyerData: FlyerInsert = {
        file_name: 'empty.jpg',
        store_name: 'Empty Store',
      } as FlyerInsert;
      const itemsData: FlyerItemInsert[] = [];
      const mockFlyer = createMockFlyer({ ...flyerData, flyer_id: 100, store_id: 2 });

      const mockClient = { query: vi.fn() };
      mockClient.query
        .mockResolvedValueOnce({ rows: [], rowCount: 0 }) // findOrCreateStore (insert)
        .mockResolvedValueOnce({ rows: [{ store_id: 2 }] }) // findOrCreateStore (select)
        .mockResolvedValueOnce({ rows: [mockFlyer] }); // insertFlyer

      const result = await createFlyerAndItems(
        flyerData,
        itemsData,
        mockLogger,
        mockClient as unknown as PoolClient,
      );

      expect(result).toEqual({
        flyer: mockFlyer,
        items: [],
      });
      expect(mockClient.query).toHaveBeenCalledTimes(3);
    });

    it('should propagate an error if any step fails', 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('Underlying DB call failed');

      // Mock the client to fail on the insertFlyer step
      const mockClient = { query: vi.fn() };
      mockClient.query
        .mockResolvedValueOnce({ rows: [], rowCount: 0 })
        .mockResolvedValueOnce({ rows: [{ store_id: 1 }] })
        .mockRejectedValueOnce(dbError); // insertFlyer fails

      // The calling service's withTransaction would catch this.
      // Here, we just expect it to be thrown.
      await expect(
        createFlyerAndItems(flyerData, itemsData, mockLogger, mockClient as unknown as PoolClient),
        // The error is wrapped by handleDbError, so we check for the wrapped error.
      ).rejects.toThrow(new DatabaseError('Failed to insert flyer into database.'));
    });
  });

  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 () => {
      console.log('[TEST DEBUG] Running test: getFlyers > should use default limit and offset');
      const mockFlyers: Flyer[] = [createMockFlyer({ flyer_id: 1 })];
      mockPoolInstance.query.mockResolvedValue({ rows: mockFlyers });

      await flyerRepo.getFlyers(mockLogger);

      console.log(
        '[TEST DEBUG] mockPoolInstance.query calls:',
        JSON.stringify(mockPoolInstance.query.mock.calls, null, 2),
      );

      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        expect.stringContaining('FROM public.flyers f'),
        [20, 0], // Default values
      );
    });

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

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

      console.log(
        '[TEST DEBUG] mockPoolInstance.query calls:',
        JSON.stringify(mockPoolInstance.query.mock.calls, null, 2),
      );

      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        expect.stringContaining('FROM public.flyers f'),
        [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(
        'Flyer 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) => {
        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',
      );
    });
  });
});
