// src/services/db/expiry.db.test.ts
import { describe, it, expect, vi, beforeEach, afterEach } from 'vitest';
import type { Logger } from 'pino';
import { createMockLogger } from '../../tests/utils/mockLogger';
import { ExpiryRepository } from './expiry.db';
import { NotFoundError } from './errors.db';
import type { InventorySource, ExpirySource, ExpiryRangeSource } from '../../types/expiry';

// Create mock pool
const mockQuery = vi.fn();
const mockPool = {
  query: mockQuery,
};

describe('ExpiryRepository', () => {
  let repo: ExpiryRepository;
  let mockLogger: Logger;

  beforeEach(() => {
    vi.clearAllMocks();
    mockLogger = createMockLogger();
    repo = new ExpiryRepository(mockPool);
  });

  afterEach(() => {
    vi.resetAllMocks();
  });

  // ============================================================================
  // INVENTORY ITEMS (pantry_items)
  // ============================================================================

  describe('addInventoryItem', () => {
    it('should add inventory item with master item lookup', async () => {
      // Master item lookup query (only called when item_name is NOT provided)
      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
        rows: [{ name: 'Milk' }],
      });

      // Location upsert query
      mockQuery.mockResolvedValueOnce({
        rows: [{ pantry_location_id: 1 }],
      });

      // Insert pantry item query
      const pantryItemRow = {
        pantry_item_id: 1,
        user_id: 'user-1',
        master_item_id: 100,
        quantity: 2,
        unit: 'liters',
        best_before_date: '2024-02-15',
        pantry_location_id: 1,
        notification_sent_at: null,
        updated_at: new Date().toISOString(),
        purchase_date: '2024-01-10',
        source: 'manual' as InventorySource,
        receipt_item_id: null,
        product_id: null,
        expiry_source: 'manual' as ExpirySource,
        is_consumed: false,
        consumed_at: null,
      };

      mockQuery.mockResolvedValueOnce({
        rows: [pantryItemRow],
      });

      // When item_name is NOT provided but master_item_id IS provided,
      // the function looks up the item name from master_grocery_items
      const result = await repo.addInventoryItem(
        'user-1',
        {
          // item_name is required by type but will be overwritten by master item lookup
          item_name: '',
          master_item_id: 100,
          quantity: 2,
          unit: 'liters',
          expiry_date: '2024-02-15',
          purchase_date: '2024-01-10',
          source: 'manual',
          location: 'fridge',
        },
        mockLogger,
      );

      expect(result.inventory_id).toBe(1);
      expect(result.item_name).toBe('Milk');
      expect(result.quantity).toBe(2);
    });

    it('should add inventory item with provided item name', async () => {
      const pantryItemRow = {
        pantry_item_id: 2,
        user_id: 'user-1',
        master_item_id: null,
        quantity: 1,
        unit: null,
        best_before_date: null,
        pantry_location_id: null,
        notification_sent_at: null,
        updated_at: new Date().toISOString(),
        purchase_date: null,
        source: 'manual' as InventorySource,
        receipt_item_id: null,
        product_id: null,
        expiry_source: null,
        is_consumed: false,
        consumed_at: null,
      };

      mockQuery.mockResolvedValueOnce({
        rows: [pantryItemRow],
      });

      const result = await repo.addInventoryItem(
        'user-1',
        {
          item_name: 'Custom Item',
          source: 'manual',
        },
        mockLogger,
      );

      expect(result.inventory_id).toBe(2);
      expect(result.item_name).toBe('Custom Item');
    });

    it('should throw on database error', async () => {
      mockQuery.mockRejectedValueOnce(new Error('DB connection failed'));

      await expect(
        repo.addInventoryItem('user-1', { source: 'manual', item_name: 'Test' }, mockLogger),
      ).rejects.toThrow();
    });
  });

  describe('updateInventoryItem', () => {
    it('should update inventory item successfully', async () => {
      const updatedRow = {
        pantry_item_id: 1,
        user_id: 'user-1',
        master_item_id: 100,
        quantity: 3,
        unit: 'liters',
        best_before_date: '2024-02-20',
        pantry_location_id: 1,
        notification_sent_at: null,
        updated_at: new Date().toISOString(),
        purchase_date: '2024-01-10',
        source: 'manual' as InventorySource,
        receipt_item_id: null,
        product_id: null,
        expiry_source: 'manual' as ExpirySource,
        is_consumed: false,
        consumed_at: null,
        item_name: 'Milk',
        category_name: 'Dairy',
        location_name: 'fridge',
      };

      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
        rows: [updatedRow],
      });

      const result = await repo.updateInventoryItem(
        1,
        'user-1',
        {
          quantity: 3,
          expiry_date: '2024-02-20',
        },
        mockLogger,
      );

      expect(result.quantity).toBe(3);
      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('UPDATE public.pantry_items'),
        expect.any(Array),
      );
    });

    it('should update with location change', async () => {
      // Location upsert query
      mockQuery.mockResolvedValueOnce({
        rows: [{ pantry_location_id: 2 }],
      });

      const updatedRow = {
        pantry_item_id: 1,
        user_id: 'user-1',
        master_item_id: 100,
        quantity: 1,
        unit: null,
        best_before_date: null,
        pantry_location_id: 2,
        notification_sent_at: null,
        updated_at: new Date().toISOString(),
        purchase_date: null,
        source: 'manual' as InventorySource,
        receipt_item_id: null,
        product_id: null,
        expiry_source: null,
        is_consumed: false,
        consumed_at: null,
        item_name: 'Milk',
        category_name: 'Dairy',
        location_name: 'freezer',
      };

      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
        rows: [updatedRow],
      });

      const result = await repo.updateInventoryItem(
        1,
        'user-1',
        { location: 'freezer' },
        mockLogger,
      );

      expect(result.location).toBe('freezer');
    });

    it('should throw NotFoundError when item not found', async () => {
      mockQuery.mockResolvedValueOnce({
        rowCount: 0,
        rows: [],
      });

      await expect(
        repo.updateInventoryItem(999, 'user-1', { quantity: 1 }, mockLogger),
      ).rejects.toThrow(NotFoundError);
    });
  });

  describe('markAsConsumed', () => {
    it('should mark item as consumed', async () => {
      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
      });

      await repo.markAsConsumed(1, 'user-1', mockLogger);

      expect(mockQuery).toHaveBeenCalledWith(expect.stringContaining('is_consumed = true'), [
        1,
        'user-1',
      ]);
    });

    it('should throw NotFoundError when item not found', async () => {
      mockQuery.mockResolvedValueOnce({
        rowCount: 0,
      });

      await expect(repo.markAsConsumed(999, 'user-1', mockLogger)).rejects.toThrow(NotFoundError);
    });
  });

  describe('deleteInventoryItem', () => {
    it('should delete inventory item', async () => {
      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
      });

      await repo.deleteInventoryItem(1, 'user-1', mockLogger);

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('DELETE FROM public.pantry_items'),
        [1, 'user-1'],
      );
    });

    it('should throw NotFoundError when item not found', async () => {
      mockQuery.mockResolvedValueOnce({
        rowCount: 0,
      });

      await expect(repo.deleteInventoryItem(999, 'user-1', mockLogger)).rejects.toThrow(
        NotFoundError,
      );
    });
  });

  describe('getInventoryItemById', () => {
    it('should return inventory item with details', async () => {
      const itemRow = {
        pantry_item_id: 1,
        user_id: 'user-1',
        master_item_id: 100,
        quantity: 2,
        unit: 'liters',
        best_before_date: '2024-02-15',
        pantry_location_id: 1,
        notification_sent_at: null,
        updated_at: new Date().toISOString(),
        purchase_date: '2024-01-10',
        source: 'manual' as InventorySource,
        receipt_item_id: null,
        product_id: null,
        expiry_source: 'manual' as ExpirySource,
        is_consumed: false,
        consumed_at: null,
        item_name: 'Milk',
        category_name: 'Dairy',
        location_name: 'fridge',
      };

      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
        rows: [itemRow],
      });

      const result = await repo.getInventoryItemById(1, 'user-1', mockLogger);

      expect(result.inventory_id).toBe(1);
      expect(result.item_name).toBe('Milk');
      expect(result.category_name).toBe('Dairy');
    });

    it('should throw NotFoundError when item not found', async () => {
      mockQuery.mockResolvedValueOnce({
        rowCount: 0,
        rows: [],
      });

      await expect(repo.getInventoryItemById(999, 'user-1', mockLogger)).rejects.toThrow(
        NotFoundError,
      );
    });
  });

  describe('getInventory', () => {
    it('should return paginated inventory', async () => {
      // Count query
      mockQuery.mockResolvedValueOnce({
        rows: [{ count: '10' }],
      });

      // Data query
      mockQuery.mockResolvedValueOnce({
        rows: [
          {
            pantry_item_id: 1,
            user_id: 'user-1',
            master_item_id: 100,
            quantity: 1,
            unit: null,
            best_before_date: '2024-02-15',
            pantry_location_id: 1,
            notification_sent_at: null,
            updated_at: new Date().toISOString(),
            purchase_date: null,
            source: 'manual' as InventorySource,
            receipt_item_id: null,
            product_id: null,
            expiry_source: 'manual' as ExpirySource,
            is_consumed: false,
            consumed_at: null,
            item_name: 'Milk',
            category_name: 'Dairy',
            location_name: 'fridge',
          },
        ],
      });

      const result = await repo.getInventory(
        { user_id: 'user-1', limit: 10, offset: 0 },
        mockLogger,
      );

      expect(result.total).toBe(10);
      expect(result.items).toHaveLength(1);
    });

    it('should filter by location', async () => {
      mockQuery.mockResolvedValueOnce({ rows: [{ count: '5' }] });
      mockQuery.mockResolvedValueOnce({ rows: [] });

      await repo.getInventory({ user_id: 'user-1', location: 'fridge' }, mockLogger);

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('pl.name = $'),
        expect.any(Array),
      );
    });

    it('should filter by expiring within days', async () => {
      mockQuery.mockResolvedValueOnce({ rows: [{ count: '3' }] });
      mockQuery.mockResolvedValueOnce({ rows: [] });

      await repo.getInventory({ user_id: 'user-1', expiring_within_days: 7 }, mockLogger);

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('best_before_date <= CURRENT_DATE + $'),
        expect.any(Array),
      );
    });

    it('should filter by category', async () => {
      mockQuery.mockResolvedValueOnce({ rows: [{ count: '4' }] });
      mockQuery.mockResolvedValueOnce({ rows: [] });

      await repo.getInventory({ user_id: 'user-1', category_id: 5 }, mockLogger);

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('mgi.category_id = $'),
        expect.any(Array),
      );
    });

    it('should search by item name', async () => {
      mockQuery.mockResolvedValueOnce({ rows: [{ count: '2' }] });
      mockQuery.mockResolvedValueOnce({ rows: [] });

      await repo.getInventory({ user_id: 'user-1', search: 'milk' }, mockLogger);

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('mgi.name ILIKE $'),
        expect.any(Array),
      );
    });
  });

  describe('getExpiringItems', () => {
    it('should return items expiring within specified days', async () => {
      const items = [
        {
          pantry_item_id: 1,
          user_id: 'user-1',
          master_item_id: 100,
          quantity: 1,
          unit: null,
          best_before_date: '2024-01-20',
          pantry_location_id: 1,
          notification_sent_at: null,
          updated_at: new Date().toISOString(),
          purchase_date: null,
          source: 'manual' as InventorySource,
          receipt_item_id: null,
          product_id: null,
          expiry_source: 'manual' as ExpirySource,
          is_consumed: false,
          consumed_at: null,
          item_name: 'Milk',
          category_name: 'Dairy',
          location_name: 'fridge',
        },
      ];

      mockQuery.mockResolvedValueOnce({
        rows: items,
      });

      const result = await repo.getExpiringItems('user-1', 7, mockLogger);

      expect(result).toHaveLength(1);
      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('best_before_date <= CURRENT_DATE + $2'),
        ['user-1', 7],
      );
    });
  });

  describe('getExpiredItems', () => {
    it('should return already expired items', async () => {
      const items = [
        {
          pantry_item_id: 1,
          user_id: 'user-1',
          master_item_id: 100,
          quantity: 1,
          unit: null,
          best_before_date: '2024-01-01',
          pantry_location_id: 1,
          notification_sent_at: null,
          updated_at: new Date().toISOString(),
          purchase_date: null,
          source: 'manual' as InventorySource,
          receipt_item_id: null,
          product_id: null,
          expiry_source: 'manual' as ExpirySource,
          is_consumed: false,
          consumed_at: null,
          item_name: 'Old Milk',
          category_name: 'Dairy',
          location_name: 'fridge',
        },
      ];

      mockQuery.mockResolvedValueOnce({
        rows: items,
      });

      const result = await repo.getExpiredItems('user-1', mockLogger);

      expect(result).toHaveLength(1);
      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('best_before_date < CURRENT_DATE'),
        ['user-1'],
      );
    });
  });

  // ============================================================================
  // EXPIRY DATE RANGES
  // ============================================================================

  describe('getExpiryRangeForItem', () => {
    it('should find expiry range by master item id', async () => {
      const range = {
        expiry_range_id: 1,
        master_item_id: 100,
        category_id: null,
        item_pattern: null,
        storage_location: 'fridge',
        min_days: 5,
        max_days: 10,
        typical_days: 7,
        notes: 'Keep refrigerated',
        source: 'usda' as ExpiryRangeSource,
      };

      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
        rows: [range],
      });

      const result = await repo.getExpiryRangeForItem('fridge', mockLogger, {
        masterItemId: 100,
      });

      expect(result).not.toBeNull();
      expect(result?.typical_days).toBe(7);
    });

    it('should find expiry range by category if master item not found', async () => {
      // Master item lookup - not found
      mockQuery.mockResolvedValueOnce({
        rowCount: 0,
        rows: [],
      });

      // Category lookup - found
      const range = {
        expiry_range_id: 2,
        master_item_id: null,
        category_id: 5,
        item_pattern: null,
        storage_location: 'fridge',
        min_days: 7,
        max_days: 14,
        typical_days: 10,
        notes: null,
        source: null,
      };

      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
        rows: [range],
      });

      const result = await repo.getExpiryRangeForItem('fridge', mockLogger, {
        masterItemId: 100,
        categoryId: 5,
      });

      expect(result).not.toBeNull();
      expect(result?.category_id).toBe(5);
    });

    it('should find expiry range by pattern matching', async () => {
      const range = {
        expiry_range_id: 3,
        master_item_id: null,
        category_id: null,
        item_pattern: '.*cheese.*',
        storage_location: 'fridge',
        min_days: 14,
        max_days: 30,
        typical_days: 21,
        notes: null,
        source: null,
      };

      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
        rows: [range],
      });

      const result = await repo.getExpiryRangeForItem('fridge', mockLogger, {
        itemName: 'Cheddar Cheese',
      });

      expect(result).not.toBeNull();
      expect(result?.item_pattern).toBe('.*cheese.*');
    });

    it('should return null when no range found', async () => {
      const result = await repo.getExpiryRangeForItem('fridge', mockLogger, {});

      expect(result).toBeNull();
    });
  });

  describe('addExpiryRange', () => {
    it('should add new expiry range', async () => {
      const range = {
        expiry_range_id: 1,
        master_item_id: 100,
        category_id: null,
        item_pattern: null,
        storage_location: 'fridge',
        min_days: 5,
        max_days: 10,
        typical_days: 7,
        notes: 'Keep refrigerated',
        source: 'usda' as ExpiryRangeSource,
      };

      mockQuery.mockResolvedValueOnce({
        rows: [range],
      });

      const result = await repo.addExpiryRange(
        {
          master_item_id: 100,
          storage_location: 'fridge',
          min_days: 5,
          max_days: 10,
          typical_days: 7,
          notes: 'Keep refrigerated',
          source: 'usda',
        },
        mockLogger,
      );

      expect(result.typical_days).toBe(7);
      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('INSERT INTO public.expiry_date_ranges'),
        expect.any(Array),
      );
    });
  });

  describe('getExpiryRanges', () => {
    it('should return paginated expiry ranges', async () => {
      // Count query
      mockQuery.mockResolvedValueOnce({
        rows: [{ count: '20' }],
      });

      // Data query
      mockQuery.mockResolvedValueOnce({
        rows: [
          {
            expiry_range_id: 1,
            master_item_id: 100,
            category_id: null,
            item_pattern: null,
            storage_location: 'fridge',
            min_days: 5,
            max_days: 10,
            typical_days: 7,
            notes: null,
            source: null,
          },
        ],
      });

      const result = await repo.getExpiryRanges({ limit: 10, offset: 0 }, mockLogger);

      expect(result.total).toBe(20);
      expect(result.ranges).toHaveLength(1);
    });

    it('should filter by storage location', async () => {
      mockQuery.mockResolvedValueOnce({ rows: [{ count: '10' }] });
      mockQuery.mockResolvedValueOnce({ rows: [] });

      await repo.getExpiryRanges({ storage_location: 'freezer' }, mockLogger);

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('storage_location = $1'),
        expect.any(Array),
      );
    });
  });

  // ============================================================================
  // EXPIRY ALERTS
  // ============================================================================

  describe('getUserAlertSettings', () => {
    it('should return user alert settings', async () => {
      const settings = [
        {
          alert_id: 1,
          user_id: 'user-1',
          alert_method: 'email',
          days_before_expiry: 3,
          is_enabled: true,
          last_alert_sent_at: null,
          created_at: new Date().toISOString(),
          updated_at: new Date().toISOString(),
        },
        {
          alert_id: 2,
          user_id: 'user-1',
          alert_method: 'push',
          days_before_expiry: 1,
          is_enabled: false,
          last_alert_sent_at: null,
          created_at: new Date().toISOString(),
          updated_at: new Date().toISOString(),
        },
      ];

      mockQuery.mockResolvedValueOnce({
        rows: settings,
      });

      const result = await repo.getUserAlertSettings('user-1', mockLogger);

      expect(result).toHaveLength(2);
      expect(result[0].alert_method).toBe('email');
    });
  });

  describe('upsertAlertSettings', () => {
    it('should create new alert settings', async () => {
      const settings = {
        alert_id: 1,
        user_id: 'user-1',
        alert_method: 'email',
        days_before_expiry: 3,
        is_enabled: true,
        last_alert_sent_at: null,
        created_at: new Date().toISOString(),
        updated_at: new Date().toISOString(),
      };

      mockQuery.mockResolvedValueOnce({
        rows: [settings],
      });

      const result = await repo.upsertAlertSettings(
        'user-1',
        'email',
        { days_before_expiry: 3, is_enabled: true },
        mockLogger,
      );

      expect(result.alert_method).toBe('email');
      expect(result.days_before_expiry).toBe(3);
    });

    it('should update existing alert settings', async () => {
      const settings = {
        alert_id: 1,
        user_id: 'user-1',
        alert_method: 'email',
        days_before_expiry: 5,
        is_enabled: false,
        last_alert_sent_at: null,
        created_at: new Date().toISOString(),
        updated_at: new Date().toISOString(),
      };

      mockQuery.mockResolvedValueOnce({
        rows: [settings],
      });

      const result = await repo.upsertAlertSettings(
        'user-1',
        'email',
        { days_before_expiry: 5, is_enabled: false },
        mockLogger,
      );

      expect(result.days_before_expiry).toBe(5);
      expect(result.is_enabled).toBe(false);
    });
  });

  describe('logAlert', () => {
    it('should log expiry alert', async () => {
      const logRecord = {
        log_id: 1,
        user_id: 'user-1',
        pantry_item_id: 1,
        alert_type: 'expiring_soon',
        alert_method: 'email',
        item_name: 'Milk',
        expiry_date: '2024-01-20',
        days_until_expiry: 3,
        created_at: new Date().toISOString(),
      };

      mockQuery.mockResolvedValueOnce({
        rows: [logRecord],
      });

      const result = await repo.logAlert('user-1', 'expiring_soon', 'email', 'Milk', mockLogger, {
        pantryItemId: 1,
        expiryDate: '2024-01-20',
        daysUntilExpiry: 3,
      });

      expect(result.alert_type).toBe('expiring_soon');
      expect(result.item_name).toBe('Milk');
    });
  });

  describe('getUsersWithExpiringItems', () => {
    it('should return users with expiring items who need alerts', async () => {
      const users = [
        {
          user_id: 'user-1',
          email: 'user1@example.com',
          alert_method: 'email',
          days_before_expiry: 3,
        },
        {
          user_id: 'user-2',
          email: 'user2@example.com',
          alert_method: 'push',
          days_before_expiry: 1,
        },
      ];

      mockQuery.mockResolvedValueOnce({
        rows: users,
      });

      const result = await repo.getUsersWithExpiringItems(mockLogger);

      expect(result).toHaveLength(2);
      expect(mockQuery).toHaveBeenCalledWith(expect.stringContaining('ea.is_enabled = true'));
    });
  });

  describe('markAlertSent', () => {
    it('should update last_alert_sent_at timestamp', async () => {
      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
      });

      await repo.markAlertSent('user-1', 'email', mockLogger);

      expect(mockQuery).toHaveBeenCalledWith(
        expect.stringContaining('last_alert_sent_at = NOW()'),
        ['user-1', 'email'],
      );
    });
  });

  // ============================================================================
  // RECIPE SUGGESTIONS
  // ============================================================================

  describe('getRecipesForExpiringItems', () => {
    it('should return recipes using expiring items', async () => {
      // Expiring items query
      mockQuery.mockResolvedValueOnce({
        rows: [{ master_item_id: 100 }, { master_item_id: 101 }],
      });

      // Count query
      mockQuery.mockResolvedValueOnce({
        rows: [{ total: '5' }],
      });

      // Recipes query
      mockQuery.mockResolvedValueOnce({
        rows: [
          {
            recipe_id: 1,
            recipe_name: 'Milk Smoothie',
            description: 'A healthy smoothie',
            prep_time_minutes: 5,
            cook_time_minutes: 0,
            servings: 2,
            photo_url: null,
            matching_master_item_ids: [100],
            match_count: '1',
          },
          {
            recipe_id: 2,
            recipe_name: 'French Toast',
            description: 'Classic breakfast',
            prep_time_minutes: 10,
            cook_time_minutes: 15,
            servings: 4,
            photo_url: null,
            matching_master_item_ids: [100, 101],
            match_count: '2',
          },
        ],
      });

      const result = await repo.getRecipesForExpiringItems('user-1', 7, 10, 0, mockLogger);

      expect(result.total).toBe(5);
      expect(result.recipes).toHaveLength(2);
      expect(result.recipes[0].match_count).toBe(1);
      expect(result.recipes[1].match_count).toBe(2);
    });

    it('should return empty when no expiring items', async () => {
      mockQuery.mockResolvedValueOnce({
        rows: [],
      });

      const result = await repo.getRecipesForExpiringItems('user-1', 7, 10, 0, mockLogger);

      expect(result.total).toBe(0);
      expect(result.recipes).toHaveLength(0);
    });
  });

  // ============================================================================
  // HELPER METHODS (tested indirectly)
  // ============================================================================

  describe('expiry status calculation', () => {
    it('should calculate expiry status correctly for fresh items', async () => {
      // Create an item with expiry date 10 days in the future
      const futureDate = new Date();
      futureDate.setDate(futureDate.getDate() + 10);

      const itemRow = {
        pantry_item_id: 1,
        user_id: 'user-1',
        master_item_id: 100,
        quantity: 1,
        unit: null,
        best_before_date: futureDate.toISOString().split('T')[0],
        pantry_location_id: null,
        notification_sent_at: null,
        updated_at: new Date().toISOString(),
        purchase_date: null,
        source: 'manual' as InventorySource,
        receipt_item_id: null,
        product_id: null,
        expiry_source: 'manual' as ExpirySource,
        is_consumed: false,
        consumed_at: null,
        item_name: 'Fresh Milk',
        category_name: 'Dairy',
        location_name: null,
      };

      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
        rows: [itemRow],
      });

      const result = await repo.getInventoryItemById(1, 'user-1', mockLogger);

      expect(result.expiry_status).toBe('fresh');
      expect(result.days_until_expiry).toBeGreaterThan(7);
    });

    it('should calculate expiry status correctly for expiring soon items', async () => {
      // Create an item with expiry date 3 days in the future
      const soonDate = new Date();
      soonDate.setDate(soonDate.getDate() + 3);

      const itemRow = {
        pantry_item_id: 1,
        user_id: 'user-1',
        master_item_id: 100,
        quantity: 1,
        unit: null,
        best_before_date: soonDate.toISOString().split('T')[0],
        pantry_location_id: null,
        notification_sent_at: null,
        updated_at: new Date().toISOString(),
        purchase_date: null,
        source: 'manual' as InventorySource,
        receipt_item_id: null,
        product_id: null,
        expiry_source: 'manual' as ExpirySource,
        is_consumed: false,
        consumed_at: null,
        item_name: 'Expiring Milk',
        category_name: 'Dairy',
        location_name: null,
      };

      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
        rows: [itemRow],
      });

      const result = await repo.getInventoryItemById(1, 'user-1', mockLogger);

      expect(result.expiry_status).toBe('expiring_soon');
      expect(result.days_until_expiry).toBeLessThanOrEqual(7);
      expect(result.days_until_expiry).toBeGreaterThanOrEqual(0);
    });

    it('should calculate expiry status correctly for expired items', async () => {
      // Create an item with expiry date 3 days in the past
      const pastDate = new Date();
      pastDate.setDate(pastDate.getDate() - 3);

      const itemRow = {
        pantry_item_id: 1,
        user_id: 'user-1',
        master_item_id: 100,
        quantity: 1,
        unit: null,
        best_before_date: pastDate.toISOString().split('T')[0],
        pantry_location_id: null,
        notification_sent_at: null,
        updated_at: new Date().toISOString(),
        purchase_date: null,
        source: 'manual' as InventorySource,
        receipt_item_id: null,
        product_id: null,
        expiry_source: 'manual' as ExpirySource,
        is_consumed: false,
        consumed_at: null,
        item_name: 'Expired Milk',
        category_name: 'Dairy',
        location_name: null,
      };

      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
        rows: [itemRow],
      });

      const result = await repo.getInventoryItemById(1, 'user-1', mockLogger);

      expect(result.expiry_status).toBe('expired');
      expect(result.days_until_expiry).toBeLessThan(0);
    });

    it('should return unknown status when no expiry date', async () => {
      const itemRow = {
        pantry_item_id: 1,
        user_id: 'user-1',
        master_item_id: 100,
        quantity: 1,
        unit: null,
        best_before_date: null,
        pantry_location_id: null,
        notification_sent_at: null,
        updated_at: new Date().toISOString(),
        purchase_date: null,
        source: 'manual' as InventorySource,
        receipt_item_id: null,
        product_id: null,
        expiry_source: null,
        is_consumed: false,
        consumed_at: null,
        item_name: 'No Expiry Item',
        category_name: 'Dry Goods',
        location_name: null,
      };

      mockQuery.mockResolvedValueOnce({
        rowCount: 1,
        rows: [itemRow],
      });

      const result = await repo.getInventoryItemById(1, 'user-1', mockLogger);

      expect(result.expiry_status).toBe('unknown');
      expect(result.days_until_expiry).toBeNull();
    });
  });
});
