// src/services/db/user.db.test.ts
import { describe, it, expect, vi, beforeEach, afterEach, Mock } from 'vitest';
import { PoolClient } from 'pg';

// Mock the logger to prevent stderr noise during tests
vi.mock('../logger.server', () => ({
  logger: {
    debug: vi.fn(),
    info: vi.fn(),
    warn: vi.fn(),
    error: vi.fn(),
  },
}));
import { logger as mockLogger } from '../logger.server';

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

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

import { mockPoolInstance } from '../../tests/setup/tests-setup-unit';
import { UniqueConstraintError, ForeignKeyConstraintError, NotFoundError } from './errors.db';
import type { Profile, ActivityLogItem, SearchQuery, UserProfile } from '../../types';

// Mock other db services that are used by functions in user.db.ts
// Update mocks to put methods on prototype so spyOn works in exportUserData tests
vi.mock('./shopping.db', () => ({
  ShoppingRepository: class {
    getShoppingLists() { return Promise.resolve([]); }
    createShoppingList() { return Promise.resolve({}); }
  },
}));
vi.mock('./personalization.db', () => ({
  PersonalizationRepository: class {
    getWatchedItems() { return Promise.resolve([]); }
  },
}));

describe('User DB Service', () => {
  // Instantiate the repository with the mock pool for each test
  let userRepo: UserRepository;

  // Restore mocks after each test to ensure spies on prototypes (like in exportUserData) don't leak
  afterEach(() => {
    vi.restoreAllMocks();
  });

  beforeEach(() => {
    vi.clearAllMocks();
    userRepo = new UserRepository(mockPoolInstance as unknown as PoolClient);
    // Provide a default mock implementation for withTransaction for all tests.
    vi.mocked(withTransaction).mockImplementation(async (callback: (client: PoolClient) => Promise<unknown>) => callback(mockPoolInstance as unknown as PoolClient));
  });

  describe('findUserByEmail', () => {
    it('should execute the correct query and return a user', async () => {
      const mockUser = { user_id: '123', email: 'test@example.com' };
      mockPoolInstance.query.mockResolvedValue({ rows: [mockUser] });

      const result = await userRepo.findUserByEmail('test@example.com', mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('FROM public.users WHERE email = $1'), ['test@example.com']);
      expect(result).toEqual(mockUser);
    });

    it('should return undefined if user is not found', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      const result = await userRepo.findUserByEmail('notfound@example.com', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('FROM public.users WHERE email = $1'), ['notfound@example.com']);
      expect(result).toBeUndefined();
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Connection Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(userRepo.findUserByEmail('test@example.com', mockLogger)).rejects.toThrow('Failed to retrieve user from database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, email: 'test@example.com' }, 'Database error in findUserByEmail');
    });
  });

  describe('createUser', () => {
    it('should execute a transaction to create a user and profile', async () => {
      const mockUser = { user_id: 'new-user-id', email: 'new@example.com' };
      // This is the flat structure returned by the DB query inside createUser
      const mockDbProfile = { user_id: 'new-user-id', email: 'new@example.com', role: 'user', full_name: 'New User', avatar_url: null, points: 0, preferences: null };
      // This is the nested structure the function is expected to return
      const expectedProfile: UserProfile = {
        user: { user_id: 'new-user-id', email: 'new@example.com' },
        user_id: 'new-user-id',
        full_name: 'New User',
        avatar_url: null,
        role: 'user',
        points: 0,
        preferences: null,
      };

      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: vi.fn() };
        mockClient.query
          .mockResolvedValueOnce({ rows: [] }) // set_config
          .mockResolvedValueOnce({ rows: [mockUser] }) // INSERT user
          .mockResolvedValueOnce({ rows: [mockDbProfile] }); // SELECT profile
        return callback(mockClient as unknown as PoolClient);
      });

      const result = await userRepo.createUser('new@example.com', 'hashedpass', { full_name: 'New User' }, mockLogger);

      expect(result).toEqual(expectedProfile);
      expect(withTransaction).toHaveBeenCalledTimes(1);
    });    

    it('should rollback the transaction if creating the user fails', async () => {
      const dbError = new Error('User insert failed');
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: vi.fn() };
        mockClient.query.mockRejectedValueOnce(dbError); // set_config or INSERT fails
        await expect(callback(mockClient as unknown as PoolClient)).rejects.toThrow(dbError);
        throw dbError;
      });

      await expect(userRepo.createUser('fail@example.com', 'badpass', {}, mockLogger)).rejects.toThrow('Failed to create user in database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, email: 'fail@example.com' }, 'Error during createUser transaction');
    });

    it('should rollback the transaction if fetching the final profile fails', async () => {
      const mockUser = { user_id: 'new-user-id', email: 'new@example.com' };
      const dbError = new Error('Profile fetch failed');
      vi.mocked(withTransaction).mockImplementation(async (callback) => {
        const mockClient = { query: vi.fn() };
        mockClient.query
          .mockResolvedValueOnce({ rows: [] }) // set_config
          .mockResolvedValueOnce({ rows: [mockUser] }) // INSERT user
          .mockRejectedValueOnce(dbError); // SELECT profile fails
        await expect(callback(mockClient as unknown as PoolClient)).rejects.toThrow(dbError);
        throw dbError;
      });

      await expect(userRepo.createUser('fail@example.com', 'pass', {}, mockLogger)).rejects.toThrow('Failed to create user in database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, email: 'fail@example.com' }, 'Error during createUser transaction');
    });

    it('should throw UniqueConstraintError if the email already exists', async () => {
      const dbError = new Error('duplicate key value violates unique constraint');
      (dbError as Error & { code: string }).code = '23505';

      vi.mocked(withTransaction).mockRejectedValue(dbError);

      try {
        await userRepo.createUser('exists@example.com', 'pass', {}, mockLogger);
        expect.fail('Expected createUser to throw UniqueConstraintError');
      } catch (error: unknown) {
        expect(error).toBeInstanceOf(UniqueConstraintError);
        // After confirming the error type, we can safely access its properties.
        // This satisfies TypeScript's type checker for the 'unknown' type.
        if (error instanceof Error) {
          expect(error.message).toBe('A user with this email address already exists.');
        }
      }

      expect(withTransaction).toHaveBeenCalledTimes(1);
      expect(mockLogger.warn).toHaveBeenCalledWith(`Attempted to create a user with an existing email: exists@example.com`);
    });
  });

  describe('findUserWithProfileByEmail', () => {
    it('should query for a user and their profile by email', async () => {
      const mockUserWithProfile = { user_id: '123', email: 'test@example.com', full_name: 'Test User', role: 'user' };
      mockPoolInstance.query.mockResolvedValue({ rows: [mockUserWithProfile] });

      const result = await userRepo.findUserWithProfileByEmail('test@example.com', mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('JOIN public.profiles'), ['test@example.com']);
      expect(result).toEqual(mockUserWithProfile);
    });

    it('should return undefined if user is not found', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      const result = await userRepo.findUserWithProfileByEmail('notfound@example.com', mockLogger);
      expect(result).toBeUndefined();
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Connection Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(userRepo.findUserWithProfileByEmail('test@example.com', mockLogger)).rejects.toThrow('Failed to retrieve user with profile from database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, email: 'test@example.com' }, 'Database error in findUserWithProfileByEmail');
    });
  });

  describe('findUserById', () => {
    it('should query for a user by their ID', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [{ user_id: '123' }], rowCount: 1 });
      await userRepo.findUserById('123', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('FROM public.users WHERE user_id = $1'), ['123']);
    });

    it('should throw NotFoundError if user is not found', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [], rowCount: 0 });
      await expect(userRepo.findUserById('not-found-id', mockLogger)).rejects.toThrow('User with ID not-found-id not found.');
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Connection Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(userRepo.findUserById('123', mockLogger)).rejects.toThrow('Failed to retrieve user by ID from database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: '123' }, 'Database error in findUserById');
    });
  });
  
  describe('findUserWithPasswordHashById', () => {
    it('should query for a user and their password hash by ID', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [{ user_id: '123', password_hash: 'hash' }], rowCount: 1 });
      await userRepo.findUserWithPasswordHashById('123', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('SELECT user_id, email, password_hash'), ['123']);
    });

    it('should throw NotFoundError if user is not found', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [], rowCount: 0 });
      await expect(userRepo.findUserWithPasswordHashById('not-found-id', mockLogger)).rejects.toThrow(NotFoundError);
      await expect(userRepo.findUserWithPasswordHashById('not-found-id', mockLogger)).rejects.toThrow('User with ID not-found-id not found.');
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Connection Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(userRepo.findUserWithPasswordHashById('123', mockLogger)).rejects.toThrow('Failed to retrieve user with sensitive data by ID from database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: '123' }, 'Database error in findUserWithPasswordHashById');
    });
  });

  describe('findUserProfileById', () => {
    it('should query for a user profile by user ID', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [{ user_id: '123' }] });
      await userRepo.findUserProfileById('123', mockLogger);
      // The actual query uses 'p.user_id' due to the join alias
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('WHERE p.user_id = $1'), ['123']);
    });

    it('should throw NotFoundError if user profile is not found', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [], rowCount: 0 });
      await expect(userRepo.findUserProfileById('not-found-id', mockLogger)).rejects.toThrow('Profile not found for this user.');
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Connection Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(userRepo.findUserProfileById('123', mockLogger)).rejects.toThrow('Failed to retrieve user profile from database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: '123' }, 'Database error in findUserProfileById');
    });
  });

  describe('updateUserProfile', () => {
    it('should execute an UPDATE query for the user profile', async () => {
      const mockProfile: Profile = { user_id: '123', full_name: 'Updated Name', role: 'user', points: 0 };
      mockPoolInstance.query.mockResolvedValue({ rows: [mockProfile] });

      await userRepo.updateUserProfile('123', { full_name: 'Updated Name' }, mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('UPDATE public.profiles'), expect.any(Array));
    });

    it('should execute an UPDATE query for avatar_url', async () => {
      const mockProfile: Profile = { user_id: '123', avatar_url: 'new-avatar.png', role: 'user', points: 0 };
      mockPoolInstance.query.mockResolvedValue({ rows: [mockProfile] });

      await userRepo.updateUserProfile('123', { avatar_url: 'new-avatar.png' }, mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('avatar_url = $1'), ['new-avatar.png', '123']);
    });

    it('should execute an UPDATE query for address_id', async () => {
      const mockProfile: Profile = { user_id: '123', address_id: 99, role: 'user', points: 0 };
      mockPoolInstance.query.mockResolvedValue({ rows: [mockProfile] });

      await userRepo.updateUserProfile('123', { address_id: 99 }, mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('address_id = $1'), [99, '123']);
    });

    it('should fetch the current profile if no update fields are provided', async () => {
      const mockProfile: Profile = { user_id: '123', full_name: 'Current Name', role: 'user', points: 0 };
      // FIX: Instead of mocking `mockResolvedValue` on the instance method which might fail if not spied correctly,
      // we mock the underlying `db.query` call that `findUserProfileById` makes.
      mockPoolInstance.query.mockResolvedValue({ rows: [mockProfile] });

      const result = await userRepo.updateUserProfile('123', { full_name: undefined }, mockLogger);

      // Check that it calls query for finding profile (since no updates were made)
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('SELECT'), expect.any(Array));
      expect(result).toEqual(mockProfile);
    });

    it('should throw an error if the user to update is not found', async () => {
      // Simulate the DB returning 0 rows affected
      mockPoolInstance.query.mockResolvedValue({ rowCount: 0, rows: [] });
      await expect(userRepo.updateUserProfile('999', { full_name: 'Fail' }, mockLogger)).rejects.toThrow('User not found or user does not have permission to update.');
    });

    it('should throw a generic error if the database query fails', async () => {
      mockPoolInstance.query.mockRejectedValue(new Error('DB Error'));
      await expect(userRepo.updateUserProfile('123', { full_name: 'Fail' }, mockLogger)).rejects.toThrow('Failed to update user profile in database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error), userId: '123', profileData: { full_name: 'Fail' } }, 'Database error in updateUserProfile');
    });
  });

  describe('updateUserPreferences', () => {
    it('should execute an UPDATE query for user preferences', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [{}] });
      await userRepo.updateUserPreferences('123', { darkMode: true }, mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining("SET preferences = COALESCE(preferences, '{}'::jsonb) || $1"), [{ darkMode: true }, '123']);
    });

    it('should throw an error if the user to update is not found', async () => {
      // Simulate the DB returning 0 rows affected
      mockPoolInstance.query.mockResolvedValue({ rowCount: 0, rows: [] });
      await expect(userRepo.updateUserPreferences('999', { darkMode: true }, mockLogger)).rejects.toThrow('User not found or user does not have permission to update.');
    });

    it('should throw a generic error if the database query fails', async () => {
      mockPoolInstance.query.mockRejectedValue(new Error('DB Error'));
      await expect(userRepo.updateUserPreferences('123', { darkMode: true }, mockLogger)).rejects.toThrow('Failed to update user preferences in database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error), userId: '123', preferences: { darkMode: true } }, 'Database error in updateUserPreferences');
    });
  });

  describe('updateUserPassword', () => {
    it('should execute an UPDATE query for the user password', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await userRepo.updateUserPassword('123', 'newhash', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith('UPDATE public.users SET password_hash = $1 WHERE user_id = $2', ['newhash', '123']);
    });

    it('should throw a generic error if the database query fails', async () => {
      mockPoolInstance.query.mockRejectedValue(new Error('DB Error'));
      await expect(userRepo.updateUserPassword('123', 'newhash', mockLogger)).rejects.toThrow('Failed to update user password in database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error), userId: '123' }, 'Database error in updateUserPassword');
    });
  });

  describe('deleteUserById', () => {
    it('should execute a DELETE query for the user', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await userRepo.deleteUserById('123', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith('DELETE FROM public.users WHERE user_id = $1', ['123']);
    });

    it('should throw a generic error if the database query fails', async () => {
      mockPoolInstance.query.mockRejectedValue(new Error('DB Error'));
      await expect(userRepo.deleteUserById('123', mockLogger)).rejects.toThrow('Failed to delete user from database.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error), userId: '123' }, 'Database error in deleteUserById');
    });
  });

  describe('saveRefreshToken', () => {
    it('should execute an UPDATE query to save the refresh token', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await userRepo.saveRefreshToken('123', 'new-token', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith('UPDATE public.users SET refresh_token = $1 WHERE user_id = $2', ['new-token', '123']);
    });

    it('should throw a generic error if the database query fails', async () => {
      mockPoolInstance.query.mockRejectedValue(new Error('DB Error'));
      await expect(userRepo.saveRefreshToken('123', 'new-token', mockLogger)).rejects.toThrow('Failed to save refresh token.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error), userId: '123' }, 'Database error in saveRefreshToken');
    });
  });

  describe('findUserByRefreshToken', () => {
    it('should query for a user by their refresh token', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [{ user_id: '123' }], rowCount: 1 });
      await userRepo.findUserByRefreshToken('a-token', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('WHERE refresh_token = $1'), ['a-token']);
    });

    it('should throw NotFoundError if token is not found', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [], rowCount: 0 });
      await expect(userRepo.findUserByRefreshToken('a-token', mockLogger)).rejects.toThrow(NotFoundError);
      await expect(userRepo.findUserByRefreshToken('a-token', mockLogger)).rejects.toThrow('User not found for the given refresh token.');
    });
  });

  describe('deleteRefreshToken', () => {
    it('should execute an UPDATE query to set the refresh token to NULL', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await userRepo.deleteRefreshToken('a-token', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        'UPDATE public.users SET refresh_token = NULL WHERE refresh_token = $1', ['a-token']
      );
    });

    it('should not throw an error if the database query fails', async () => {
      mockPoolInstance.query.mockRejectedValue(new Error('DB Error'));
      // The function is designed to swallow errors, so we expect it to resolve.
      await expect(userRepo.deleteRefreshToken('a-token', mockLogger)).resolves.toBeUndefined();
      // We can still check that the query was attempted.
      expect(mockPoolInstance.query).toHaveBeenCalled();
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error) }, 'Database error in deleteRefreshToken');
    });
  });

  describe('createPasswordResetToken', () => {
    it('should execute DELETE and INSERT queries', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      const expires = new Date();
      await userRepo.createPasswordResetToken('123', 'token-hash', expires, mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith('DELETE FROM public.password_reset_tokens WHERE user_id = $1', ['123']);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('INSERT INTO public.password_reset_tokens'), ['123', 'token-hash', expires]);
    });

    it('should throw ForeignKeyConstraintError if user does not exist', async () => {
      const dbError = new Error('violates foreign key constraint');
      (dbError as Error & { code: string }).code = '23503';
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(userRepo.createPasswordResetToken('non-existent-user', 'hash', new Date(), mockLogger)).rejects.toThrow(ForeignKeyConstraintError);
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      const expires = new Date();
      await expect(userRepo.createPasswordResetToken('123', 'token-hash', expires, mockLogger)).rejects.toThrow('Failed to create password reset token.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: '123' }, 'Database error in createPasswordResetToken');
    });
  });

  describe('getValidResetTokens', () => {
    it('should query for tokens where expires_at > NOW()', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await userRepo.getValidResetTokens(mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.stringContaining('WHERE expires_at > NOW()'));
    });

    it('should throw a generic error if the database query fails', async () => {
      mockPoolInstance.query.mockRejectedValue(new Error('DB Error'));
      await expect(userRepo.getValidResetTokens(mockLogger)).rejects.toThrow('Failed to retrieve valid reset tokens.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error) }, 'Database error in getValidResetTokens');
    });
  });

  describe('deleteResetToken', () => {
    it('should execute a DELETE query for the token hash', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await userRepo.deleteResetToken('token-hash', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith('DELETE FROM public.password_reset_tokens WHERE token_hash = $1', ['token-hash']);
    });

    it('should log an error if the database query fails', async () => {
      mockPoolInstance.query.mockRejectedValue(new Error('DB Error'));
      await userRepo.deleteResetToken('token-hash', mockLogger);
      expect(mockLogger.error).toHaveBeenCalledWith({ err: expect.any(Error), tokenHash: 'token-hash' }, 'Database error in deleteResetToken');
    });
  });

  describe('exportUserData', () => {
    // Import the mocked withTransaction helper
    let withTransaction: Mock;
    beforeEach(async () => {
      const connDb = await import('./connection.db');
      // Cast to Mock for type-safe access to mock properties
      withTransaction = connDb.withTransaction as Mock;
    });

    it('should call profile, watched items, and shopping list functions', async () => {
      const { ShoppingRepository } = await import('./shopping.db');
      const { PersonalizationRepository } = await import('./personalization.db');

      const findProfileSpy = vi.spyOn(UserRepository.prototype, 'findUserProfileById');
      findProfileSpy.mockResolvedValue({ user_id: '123' } as Profile);
      const getWatchedItemsSpy = vi.spyOn(PersonalizationRepository.prototype, 'getWatchedItems');
      getWatchedItemsSpy.mockResolvedValue([]);
      const getShoppingListsSpy = vi.spyOn(ShoppingRepository.prototype, 'getShoppingLists');
      getShoppingListsSpy.mockResolvedValue([]);

      await exportUserData('123', mockLogger);

      // Verify that withTransaction was called
      expect(withTransaction).toHaveBeenCalledTimes(1);

      // Verify the repository methods were called inside the transaction
      expect(findProfileSpy).toHaveBeenCalledWith('123', expect.any(Object));
      expect(getWatchedItemsSpy).toHaveBeenCalledWith('123', expect.any(Object));
      expect(getShoppingListsSpy).toHaveBeenCalledWith('123', expect.any(Object));
    });

    it('should throw an error if the user profile is not found', async () => {
      // Arrange: Mock findUserProfileById to throw a NotFoundError, as per its contract (ADR-001).
      // The exportUserData function will catch this and re-throw a generic error.
      const { NotFoundError } = await import('./errors.db');
      vi.spyOn(UserRepository.prototype, 'findUserProfileById').mockRejectedValue(new NotFoundError('Profile not found'));

      // Act & Assert: The outer function catches the NotFoundError and re-throws a generic one.
      await expect(exportUserData('123', mockLogger)).rejects.toThrow('Failed to export user data.');
      expect(withTransaction).toHaveBeenCalledTimes(1);
    });

    it('should throw an error if the database query fails', async () => {
       // Arrange: Force a failure in one of the parallel calls
       vi.spyOn(UserRepository.prototype, 'findUserProfileById').mockRejectedValue(new Error('DB Error'));

      // Act & Assert
      await expect(exportUserData('123', mockLogger)).rejects.toThrow('Failed to export user data.');
      expect(withTransaction).toHaveBeenCalledTimes(1);
    });
  });

  describe('followUser', () => {
    it('should execute an INSERT query to create a follow relationship', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await userRepo.followUser('follower-1', 'following-1', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        'INSERT INTO public.user_follows (follower_id, following_id) VALUES ($1, $2) ON CONFLICT (follower_id, following_id) DO NOTHING',
        ['follower-1', 'following-1']
      );
    });

    it('should throw ForeignKeyConstraintError if a user does not exist', async () => {
      const dbError = new Error('violates foreign key constraint');
      (dbError as Error & { code: string }).code = '23503';
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(userRepo.followUser('follower-1', 'non-existent-user', mockLogger)).rejects.toThrow(ForeignKeyConstraintError);
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(userRepo.followUser('follower-1', 'following-1', mockLogger)).rejects.toThrow('Failed to follow user.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, followerId: 'follower-1', followingId: 'following-1' }, 'Database error in followUser');
    });
  });

  describe('unfollowUser', () => {
    it('should execute a DELETE query to remove a follow relationship', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      await userRepo.unfollowUser('follower-1', 'following-1', mockLogger);
      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        'DELETE FROM public.user_follows WHERE follower_id = $1 AND following_id = $2',
        ['follower-1', 'following-1']
      );
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(userRepo.unfollowUser('follower-1', 'following-1', mockLogger)).rejects.toThrow('Failed to unfollow user.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, followerId: 'follower-1', followingId: 'following-1' }, 'Database error in unfollowUser');
    });
  });

  describe('getUserFeed', () => {
    it('should execute a SELECT query to get a user feed', async () => {
      const mockFeedItems: ActivityLogItem[] = [
        {
          activity_log_id: 1,
          user_id: 'following-1',
          action: 'recipe_created',
          display_text: 'Created a new recipe',
          created_at: new Date().toISOString(),
          details: { recipe_id: 1, recipe_name: 'Test Recipe' },
        },
      ];
      mockPoolInstance.query.mockResolvedValue({ rows: mockFeedItems });

      const result = await userRepo.getUserFeed('user-123', 10, 0, mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        expect.stringContaining('FROM public.activity_log al'),
        ['user-123', 10, 0]
      );
      expect(result).toEqual(mockFeedItems);
    });

    it('should return an empty array if the user feed is empty', async () => {
      mockPoolInstance.query.mockResolvedValue({ rows: [] });
      const result = await userRepo.getUserFeed('user-123', 10, 0, mockLogger);
      expect(result).toEqual([]);
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(userRepo.getUserFeed('user-123', 10, 0, mockLogger)).rejects.toThrow('Failed to retrieve user feed.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, userId: 'user-123', limit: 10, offset: 0 }, 'Database error in getUserFeed');
    });
  });

  describe('logSearchQuery', () => {
    it('should execute an INSERT query and return the new search query log', async () => {
      const queryData: Omit<SearchQuery, 'search_query_id' | 'created_at'> = {
        user_id: 'user-123',
        query_text: 'best chicken recipes',
        result_count: 5,
        was_successful: true,
      };
      const mockLoggedQuery: SearchQuery = {
        search_query_id: 1,
        created_at: new Date().toISOString(),
        ...queryData,
      };
      mockPoolInstance.query.mockResolvedValue({ rows: [mockLoggedQuery] });

      const result = await userRepo.logSearchQuery(queryData, mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(
        'INSERT INTO public.search_queries (user_id, query_text, result_count, was_successful) VALUES ($1, $2, $3, $4) RETURNING *',
        [
          queryData.user_id,
          queryData.query_text,
          queryData.result_count,
          queryData.was_successful,
        ]
      );
      expect(result).toEqual(mockLoggedQuery);
    });

    it('should handle logging a search for an anonymous user', async () => {
      const queryData = {
        user_id: null,
        query_text: 'anonymous search',
        result_count: 10,
        was_successful: true,
      };
      const mockLoggedQuery: SearchQuery = {
        search_query_id: 2,
        created_at: new Date().toISOString(),
        ...queryData,
      };
      mockPoolInstance.query.mockResolvedValue({ rows: [mockLoggedQuery] });

      await userRepo.logSearchQuery(queryData, mockLogger);

      expect(mockPoolInstance.query).toHaveBeenCalledWith(expect.any(String), [null, 'anonymous search', 10, true]);
    });

    it('should throw a generic error if the database query fails', async () => {
      const dbError = new Error('DB Error');
      mockPoolInstance.query.mockRejectedValue(dbError);
      await expect(userRepo.logSearchQuery({ query_text: 'fail' }, mockLogger)).rejects.toThrow('Failed to log search query.');
      expect(mockLogger.error).toHaveBeenCalledWith({ err: dbError, queryData: { query_text: 'fail' } }, 'Database error in logSearchQuery');
    });
  });
});