// @vitest-environment node
import { FilesTabs, SortType } from '@lobechat/types';
import { eq, inArray } from 'drizzle-orm';
import { afterEach, beforeEach, describe, expect, it, vi } from 'vitest';

import { getTestDB } from '../../core/getTestDB';
import {
  asyncTasks,
  chunks,
  documents,
  embeddings,
  fileChunks,
  files,
  filesToSessions,
  globalFiles,
  knowledgeBaseFiles,
  knowledgeBases,
  messages,
  messagesFiles,
  sessions,
  topics,
  users,
  workspaces,
} from '../../schemas';
import type { LobeChatDatabase } from '../../type';
import { FileModel } from '../file';

const serverDB: LobeChatDatabase = await getTestDB();

const userId = 'file-model-test-user-id';
const fileModel = new FileModel(serverDB, userId);

const knowledgeBase = { id: 'kb1', userId, name: 'knowledgeBase' };
beforeEach(async () => {
  await serverDB.delete(users);
  await serverDB.insert(users).values([{ id: userId }, { id: 'user2' }]);
  await serverDB.insert(knowledgeBases).values(knowledgeBase);
});

afterEach(async () => {
  await serverDB.delete(users);
  await serverDB.delete(files);
  await serverDB.delete(globalFiles);
});

describe('FileModel', () => {
  describe('create', () => {
    it('should create a new file', async () => {
      const params = {
        name: 'test-file.txt',
        url: 'https://example.com/test-file.txt',
        size: 100,
        fileType: 'text/plain',
      };

      const { id } = await fileModel.create(params);
      expect(id).toBeDefined();

      const file = await serverDB.query.files.findFirst({ where: eq(files.id, id) });
      expect(file).toMatchObject({ ...params, userId });
    });

    it('should create a file with knowledgeBaseId', async () => {
      const params = {
        name: 'test-file.txt',
        url: 'https://example.com/test-file.txt',
        size: 100,
        fileType: 'text/plain',
        knowledgeBaseId: 'kb1',
      };

      const { id } = await fileModel.create(params);

      const kbFile = await serverDB.query.knowledgeBaseFiles.findFirst({
        where: eq(knowledgeBaseFiles.fileId, id),
      });
      expect(kbFile).toMatchObject({ fileId: id, knowledgeBaseId: 'kb1' });
    });

    it('should create a new file with hash', async () => {
      const params = {
        name: 'test-file.txt',
        url: 'https://example.com/test-file.txt',
        size: 100,
        fileHash: 'abc',
        fileType: 'text/plain',
      };

      const { id } = await fileModel.create(params, true);
      expect(id).toBeDefined();

      const file = await serverDB.query.files.findFirst({ where: eq(files.id, id) });
      expect(file).toMatchObject({ ...params, userId });

      const globalFile = await serverDB.query.globalFiles.findFirst({
        where: eq(globalFiles.hashId, params.fileHash),
      });
      expect(globalFile).toMatchObject({
        url: 'https://example.com/test-file.txt',
        size: 100,
        hashId: 'abc',
        fileType: 'text/plain',
      });
    });
  });

  describe('createGlobalFile', () => {
    it('should create a global file', async () => {
      const globalFile = {
        hashId: 'test-hash',
        fileType: 'text/plain',
        size: 100,
        url: 'https://example.com/global-file.txt',
        metadata: { key: 'value' },
        creator: userId,
      };

      const result = await fileModel.createGlobalFile(globalFile);
      expect(result[0]).toMatchObject(globalFile);
    });
  });

  describe('checkHash', () => {
    it('should return isExist: false for non-existent hash', async () => {
      const result = await fileModel.checkHash('non-existent-hash');
      expect(result).toEqual({ isExist: false });
    });

    it('should return file info for existing hash', async () => {
      const globalFile = {
        hashId: 'existing-hash',
        fileType: 'text/plain',
        size: 100,
        url: 'https://example.com/existing-file.txt',
        metadata: { key: 'value' },
        creator: userId,
      };

      await serverDB.insert(globalFiles).values(globalFile);

      const result = await fileModel.checkHash('existing-hash');
      expect(result).toEqual({
        isExist: true,
        fileType: 'text/plain',
        size: 100,
        url: 'https://example.com/existing-file.txt',
        metadata: { key: 'value' },
      });
    });
  });

  describe('delete', () => {
    it('should delete a file by id', async () => {
      await fileModel.createGlobalFile({
        hashId: '1',
        url: 'https://example.com/file1.txt',
        size: 100,
        fileType: 'text/plain',
        creator: userId,
      });

      const { id } = await fileModel.create({
        name: 'test-file.txt',
        url: 'https://example.com/test-file.txt',
        size: 100,
        fileType: 'text/plain',
        fileHash: '1',
      });

      await fileModel.delete(id);

      const file = await serverDB.query.files.findFirst({ where: eq(files.id, id) });
      const globalFile = await serverDB.query.globalFiles.findFirst({
        where: eq(globalFiles.hashId, '1'),
      });

      expect(file).toBeUndefined();
      expect(globalFile).toBeUndefined();
    });
    it('should delete a file by id but global file not removed ', async () => {
      await fileModel.createGlobalFile({
        hashId: '1',
        url: 'https://example.com/file1.txt',
        size: 100,
        fileType: 'text/plain',
        creator: userId,
      });

      const { id } = await fileModel.create({
        name: 'test-file.txt',
        url: 'https://example.com/test-file.txt',
        size: 100,
        fileType: 'text/plain',
        fileHash: '1',
      });

      await fileModel.delete(id, false);

      const file = await serverDB.query.files.findFirst({ where: eq(files.id, id) });
      const globalFile = await serverDB.query.globalFiles.findFirst({
        where: eq(globalFiles.hashId, '1'),
      });

      expect(file).toBeUndefined();
      expect(globalFile).toBeDefined();
    });

    it('should delete mirror documents (sourceType=file) tied to the file', async () => {
      const { id: fileId } = await fileModel.create({
        name: 'mirror.pdf',
        url: 'https://example.com/mirror.pdf',
        size: 100,
        fileType: 'application/pdf',
      });

      await serverDB.insert(documents).values({
        userId,
        fileId,
        sourceType: 'file',
        source: 'mirror.pdf',
        fileType: 'application/pdf',
        totalCharCount: 0,
        totalLineCount: 0,
      });

      await fileModel.delete(fileId);

      const remainingDocs = await serverDB.query.documents.findMany({
        where: eq(documents.userId, userId),
      });
      expect(remainingDocs).toHaveLength(0);
    });

    it('should NOT delete non-mirror documents, only null out their fileId', async () => {
      const { id: fileId } = await fileModel.create({
        name: 'shared.pdf',
        url: 'https://example.com/shared.pdf',
        size: 100,
        fileType: 'application/pdf',
      });

      const inserted = await serverDB
        .insert(documents)
        .values({
          userId,
          fileId,
          // not a mirror — created from a topic, just happens to reference this file
          sourceType: 'topic',
          source: 'topic-source',
          fileType: 'application/pdf',
          totalCharCount: 0,
          totalLineCount: 0,
        })
        .returning();
      const docId = inserted[0]!.id;

      await fileModel.delete(fileId);

      const doc = await serverDB.query.documents.findFirst({
        where: eq(documents.id, docId),
      });
      expect(doc).toBeDefined();
      expect(doc?.fileId).toBeNull();
    });

    it('should delete asyncTasks attached to the file', async () => {
      const [chunkTask] = await serverDB
        .insert(asyncTasks)
        .values({ userId, type: 'chunk', status: 'success' })
        .returning();
      const [embeddingTask] = await serverDB
        .insert(asyncTasks)
        .values({ userId, type: 'embedding', status: 'success' })
        .returning();

      const { id: fileId } = await fileModel.create({
        name: 'tasked.pdf',
        url: 'https://example.com/tasked.pdf',
        size: 100,
        fileType: 'application/pdf',
        chunkTaskId: chunkTask!.id,
        embeddingTaskId: embeddingTask!.id,
      });

      await fileModel.delete(fileId);

      const remainingTasks = await serverDB.query.asyncTasks.findMany({
        where: inArray(asyncTasks.id, [chunkTask!.id, embeddingTask!.id]),
      });
      expect(remainingTasks).toHaveLength(0);
    });
  });

  describe('deleteMany', () => {
    it('should delete multiple files', async () => {
      await fileModel.createGlobalFile({
        hashId: '1',
        url: 'https://example.com/file1.txt',
        size: 100,
        fileType: 'text/plain',
        creator: userId,
      });
      await fileModel.createGlobalFile({
        hashId: '2',
        url: 'https://example.com/file2.txt',
        size: 200,
        fileType: 'text/plain',
        creator: userId,
      });

      const file1 = await fileModel.create({
        name: 'file1.txt',
        url: 'https://example.com/file1.txt',
        size: 100,
        fileHash: '1',
        fileType: 'text/plain',
      });
      const file2 = await fileModel.create({
        name: 'file2.txt',
        url: 'https://example.com/file2.txt',
        size: 200,
        fileType: 'text/plain',
        fileHash: '2',
      });
      const globalFilesResult = await serverDB.query.globalFiles.findMany({
        where: inArray(globalFiles.hashId, ['1', '2']),
      });
      expect(globalFilesResult).toHaveLength(2);

      const deletedFiles = await fileModel.deleteMany([file1.id, file2.id]);

      const remainingFiles = await serverDB.query.files.findMany({
        where: eq(files.userId, userId),
      });
      const globalFilesResult2 = await serverDB.query.globalFiles.findMany({
        where: inArray(
          globalFiles.hashId,
          remainingFiles.map((i) => i.fileHash as string),
        ),
      });

      expect(remainingFiles).toHaveLength(0);
      expect(globalFilesResult2).toHaveLength(0);
      expect(deletedFiles.map((file) => file.id).sort()).toEqual([file1.id, file2.id].sort());
    });
    it('should delete multiple files but not remove global files if DISABLE_REMOVE_GLOBAL_FILE=true', async () => {
      await fileModel.createGlobalFile({
        hashId: '1',
        url: 'https://example.com/file1.txt',
        size: 100,
        fileType: 'text/plain',
        creator: userId,
      });
      await fileModel.createGlobalFile({
        hashId: '2',
        url: 'https://example.com/file2.txt',
        size: 200,
        fileType: 'text/plain',
        creator: userId,
      });

      const file1 = await fileModel.create({
        name: 'file1.txt',
        url: 'https://example.com/file1.txt',
        size: 100,
        fileType: 'text/plain',
        fileHash: '1',
      });
      const file2 = await fileModel.create({
        name: 'file2.txt',
        url: 'https://example.com/file2.txt',
        size: 200,
        fileType: 'text/plain',
        fileHash: '2',
      });

      const globalFilesResult = await serverDB.query.globalFiles.findMany({
        where: inArray(globalFiles.hashId, ['1', '2']),
      });

      expect(globalFilesResult).toHaveLength(2);

      const deletedFiles = await fileModel.deleteMany([file1.id, file2.id], false);

      const remainingFiles = await serverDB.query.files.findMany({
        where: eq(files.userId, userId),
      });
      const globalFilesResult2 = await serverDB.query.globalFiles.findMany({
        where: inArray(globalFiles.hashId, ['1', '2']),
      });

      expect(remainingFiles).toHaveLength(0);
      expect(globalFilesResult2).toHaveLength(2);
      expect(deletedFiles).toEqual([]);
    });

    it('should return only files whose backing global file is no longer referenced', async () => {
      await fileModel.createGlobalFile({
        hashId: 'shared-hash',
        url: 'https://example.com/shared.txt',
        size: 100,
        fileType: 'text/plain',
        creator: userId,
      });
      await fileModel.createGlobalFile({
        hashId: 'exclusive-hash',
        url: 'https://example.com/exclusive.txt',
        size: 100,
        fileType: 'text/plain',
        creator: userId,
      });

      const sharedFileA = await fileModel.create({
        name: 'shared-a.txt',
        url: 'https://example.com/shared.txt',
        size: 100,
        fileHash: 'shared-hash',
        fileType: 'text/plain',
      });
      await fileModel.create({
        name: 'shared-b.txt',
        url: 'https://example.com/shared.txt',
        size: 100,
        fileHash: 'shared-hash',
        fileType: 'text/plain',
      });
      const exclusiveFile = await fileModel.create({
        name: 'exclusive.txt',
        url: 'https://example.com/exclusive.txt',
        size: 100,
        fileHash: 'exclusive-hash',
        fileType: 'text/plain',
      });

      const deletedFiles = await fileModel.deleteMany([sharedFileA.id, exclusiveFile.id]);

      expect(deletedFiles.map((file) => file.id)).toEqual([exclusiveFile.id]);

      const sharedGlobalFile = await serverDB.query.globalFiles.findFirst({
        where: eq(globalFiles.hashId, 'shared-hash'),
      });
      const exclusiveGlobalFile = await serverDB.query.globalFiles.findFirst({
        where: eq(globalFiles.hashId, 'exclusive-hash'),
      });
      expect(sharedGlobalFile).toBeDefined();
      expect(exclusiveGlobalFile).toBeUndefined();
    });

    it('should delete mirror documents and asyncTasks for all files in batch', async () => {
      const [chunkTask1] = await serverDB
        .insert(asyncTasks)
        .values({ userId, type: 'chunk', status: 'success' })
        .returning();
      const [chunkTask2] = await serverDB
        .insert(asyncTasks)
        .values({ userId, type: 'chunk', status: 'success' })
        .returning();

      const file1 = await fileModel.create({
        name: 'a.pdf',
        url: 'https://example.com/a.pdf',
        size: 100,
        fileType: 'application/pdf',
        chunkTaskId: chunkTask1!.id,
      });
      const file2 = await fileModel.create({
        name: 'b.pdf',
        url: 'https://example.com/b.pdf',
        size: 100,
        fileType: 'application/pdf',
        chunkTaskId: chunkTask2!.id,
      });

      await serverDB.insert(documents).values([
        {
          userId,
          fileId: file1.id,
          sourceType: 'file',
          source: 'a.pdf',
          fileType: 'application/pdf',
          totalCharCount: 0,
          totalLineCount: 0,
        },
        {
          userId,
          fileId: file2.id,
          sourceType: 'file',
          source: 'b.pdf',
          fileType: 'application/pdf',
          totalCharCount: 0,
          totalLineCount: 0,
        },
      ]);

      await fileModel.deleteMany([file1.id, file2.id]);

      const remainingDocs = await serverDB.query.documents.findMany({
        where: eq(documents.userId, userId),
      });
      const remainingTasks = await serverDB.query.asyncTasks.findMany({
        where: inArray(asyncTasks.id, [chunkTask1!.id, chunkTask2!.id]),
      });
      expect(remainingDocs).toHaveLength(0);
      expect(remainingTasks).toHaveLength(0);
    });
  });

  describe('clear', () => {
    it('should clear all files for the user', async () => {
      await fileModel.create({
        name: 'test-file-1.txt',
        url: 'https://example.com/test-file-1.txt',
        size: 100,
        fileType: 'text/plain',
      });
      await fileModel.create({
        name: 'test-file-2.txt',
        url: 'https://example.com/test-file-2.txt',
        size: 200,
        fileType: 'text/plain',
      });

      await fileModel.clear();

      const userFiles = await serverDB.query.files.findMany({ where: eq(files.userId, userId) });
      expect(userFiles).toHaveLength(0);
    });
  });

  describe('Query', () => {
    const sharedFileList = [
      {
        name: 'document.pdf',
        url: 'https://example.com/document.pdf',
        size: 1000,
        fileType: 'application/pdf',
        userId,
      },
      {
        name: 'image.jpg',
        url: 'https://example.com/image.jpg',
        size: 500,
        fileType: 'image/jpeg',
        userId,
      },
      {
        name: 'audio.mp3',
        url: 'https://example.com/audio.mp3',
        size: 2000,
        fileType: 'audio/mpeg',
        userId,
      },
    ];

    it('should query files for the user', async () => {
      const file1 = await fileModel.create({
        name: 'test-file-1.txt',
        url: 'https://example.com/test-file-1.txt',
        size: 100,
        fileType: 'text/plain',
      });
      // Add a small delay to ensure different timestamps
      await new Promise((resolve) => setTimeout(resolve, 10));
      const file2 = await fileModel.create({
        name: 'test-file-2.txt',
        url: 'https://example.com/test-file-2.txt',
        size: 200,
        fileType: 'text/plain',
      });
      await serverDB.insert(files).values({
        name: 'audio.mp3',
        url: 'https://example.com/audio.mp3',
        size: 2000,
        fileType: 'audio/mpeg',
        userId: 'user2',
      });

      const userFiles = await fileModel.query();
      expect(userFiles).toHaveLength(2);
      // file2 should be first since it was created more recently
      expect(userFiles[0].id).toBe(file2.id);
      expect(userFiles[1].id).toBe(file1.id);
    });

    it('should filter files by name', async () => {
      await serverDB.insert(files).values(sharedFileList);
      const filteredFiles = await fileModel.query({ q: 'DOC' });
      expect(filteredFiles).toHaveLength(1);
      expect(filteredFiles[0].name).toBe('document.pdf');
    });

    it('should filter files by category', async () => {
      await serverDB.insert(files).values(sharedFileList);

      const imageFiles = await fileModel.query({ category: FilesTabs.Images });
      expect(imageFiles).toHaveLength(1);
      expect(imageFiles[0].name).toBe('image.jpg');
    });

    it('should filter audio files by category', async () => {
      await serverDB.insert(files).values(sharedFileList);

      const audioFiles = await fileModel.query({ category: FilesTabs.Audios });
      expect(audioFiles).toHaveLength(1);
      expect(audioFiles[0].name).toBe('audio.mp3');
    });

    it('should sort files by name in ascending order', async () => {
      await serverDB.insert(files).values(sharedFileList);

      const sortedFiles = await fileModel.query({ sortType: SortType.Asc, sorter: 'name' });
      expect(sortedFiles[0].name).toBe('audio.mp3');
      expect(sortedFiles[2].name).toBe('image.jpg');
    });

    it('should sort files by size in descending order', async () => {
      await serverDB.insert(files).values(sharedFileList);

      const sortedFiles = await fileModel.query({ sortType: SortType.Desc, sorter: 'size' });
      expect(sortedFiles[0].name).toBe('audio.mp3');
      expect(sortedFiles[2].name).toBe('image.jpg');
    });

    it('should combine filtering and sorting', async () => {
      await serverDB.insert(files).values([
        ...sharedFileList,
        {
          name: 'big_document.pdf',
          url: 'https://example.com/big_document.pdf',
          size: 5000,
          fileType: 'application/pdf',
          userId,
        },
      ]);

      const filteredAndSortedFiles = await fileModel.query({
        category: FilesTabs.Documents,
        sortType: SortType.Desc,
        sorter: 'size',
      });

      expect(filteredAndSortedFiles).toHaveLength(2);
      expect(filteredAndSortedFiles[0].name).toBe('big_document.pdf');
      expect(filteredAndSortedFiles[1].name).toBe('document.pdf');
    });

    it('should return an empty array when no files match the query', async () => {
      await serverDB.insert(files).values(sharedFileList);
      const noFiles = await fileModel.query({ q: 'nonexistent' });
      expect(noFiles).toHaveLength(0);
    });

    it('should handle invalid sort field gracefully', async () => {
      await serverDB.insert(files).values(sharedFileList);

      const result = await fileModel.query({
        sortType: SortType.Asc,
        sorter: 'invalidField' as any,
      });
      expect(result).toHaveLength(3);
      // Should default to sorting by createdAt in descending order
    });

    describe('Query with knowledge base', () => {
      beforeEach(async () => {
        await serverDB.insert(files).values([
          {
            id: 'file1',
            name: 'file1.txt',
            userId,
            fileType: 'text/plain',
            size: 100,
            url: 'url1',
          },
          {
            id: 'file2',
            name: 'file2.txt',
            userId,
            fileType: 'text/plain',
            size: 200,
            url: 'url2',
          },
        ]);
        await serverDB
          .insert(knowledgeBaseFiles)
          .values([{ fileId: 'file1', knowledgeBaseId: 'kb1', userId }]);
      });

      it('should query files in a specific knowledge base', async () => {
        const result = await fileModel.query({ knowledgeBaseId: 'kb1' });
        expect(result).toHaveLength(1);
        expect(result[0].id).toBe('file1');
      });

      it('should exclude files in knowledge bases when showFilesInKnowledgeBase is false', async () => {
        const result = await fileModel.query({ showFilesInKnowledgeBase: false });
        expect(result).toHaveLength(1);
        expect(result[0].id).toBe('file2');
      });

      it('should include all files when showFilesInKnowledgeBase is true', async () => {
        const result = await fileModel.query({ showFilesInKnowledgeBase: true });
        expect(result).toHaveLength(2);
      });
    });
  });

  describe('findById', () => {
    it('should find a file by id', async () => {
      const { id } = await fileModel.create({
        name: 'test-file.txt',
        url: 'https://example.com/test-file.txt',
        size: 100,
        fileType: 'text/plain',
      });

      const file = await fileModel.findById(id);
      expect(file).toMatchObject({
        id,
        name: 'test-file.txt',
        url: 'https://example.com/test-file.txt',
        size: 100,
        fileType: 'text/plain',
        userId,
      });
    });
  });

  it('should update a file', async () => {
    const { id } = await fileModel.create({
      name: 'test-file.txt',
      url: 'https://example.com/test-file.txt',
      size: 100,
      fileType: 'text/plain',
    });

    await fileModel.update(id, { name: 'updated-test-file.txt', size: 200 });

    const updatedFile = await serverDB.query.files.findFirst({ where: eq(files.id, id) });
    expect(updatedFile).toMatchObject({
      id,
      name: 'updated-test-file.txt',
      url: 'https://example.com/test-file.txt',
      size: 200,
      fileType: 'text/plain',
      userId,
    });
  });

  it('should countFilesByHash', async () => {
    const fileList = [
      {
        id: '1',
        name: 'document.pdf',
        url: 'https://example.com/document.pdf',
        fileHash: 'hash1',
        size: 1000,
        fileType: 'application/pdf',
        userId,
      },
      {
        id: '2',
        name: 'image.jpg',
        url: 'https://example.com/image.jpg',
        fileHash: 'hash2',
        size: 500,
        fileType: 'image/jpeg',
        userId,
      },
      {
        id: '5',
        name: 'document.pdf',
        url: 'https://example.com/document.pdf',
        fileHash: 'hash1',
        size: 1000,
        fileType: 'application/pdf',
        userId: 'user2',
      },
    ];

    await serverDB.insert(globalFiles).values([
      {
        hashId: 'hash1',
        url: 'https://example.com/document.pdf',
        size: 1000,
        fileType: 'application/pdf',
        creator: userId,
      },
      {
        hashId: 'hash2',
        url: 'https://example.com/image.jpg',
        size: 500,
        fileType: 'image/jpeg',
        creator: userId,
      },
    ]);

    await serverDB.insert(files).values(fileList);

    const data = await fileModel.countFilesByHash('hash1');
    expect(data).toEqual(2);
  });

  describe('countUsage', () => {
    const sharedFileList = [
      {
        name: 'document.pdf',
        url: 'https://example.com/document.pdf',
        size: 1000,
        fileType: 'application/pdf',
        userId,
      },
      {
        name: 'image.jpg',
        url: 'https://example.com/image.jpg',
        size: 500,
        fileType: 'image/jpeg',
        userId,
      },
      {
        name: 'audio.mp3',
        url: 'https://example.com/audio.mp3',
        size: 2000,
        fileType: 'audio/mpeg',
        userId,
      },
    ];

    it('should get total size of files for the user', async () => {
      await serverDB.insert(files).values(sharedFileList);
      const size = await fileModel.countUsage();

      expect(size).toBe(3500);
    });
  });

  describe('findByNames', () => {
    it('should find files by names', async () => {
      // Prepare test data
      const fileList = [
        {
          name: 'test1.txt',
          url: 'https://example.com/test1.txt',
          size: 100,
          fileType: 'text/plain',
          userId,
        },
        {
          name: 'test2.txt',
          url: 'https://example.com/test2.txt',
          size: 200,
          fileType: 'text/plain',
          userId,
        },
        {
          name: 'other.txt',
          url: 'https://example.com/other.txt',
          size: 300,
          fileType: 'text/plain',
          userId,
        },
      ];

      await serverDB.insert(files).values(fileList);

      // Test finding files
      const result = await fileModel.findByNames(['test1', 'test2']);
      expect(result).toHaveLength(2);
      expect(result.map((f) => f.name)).toContain('test1.txt');
      expect(result.map((f) => f.name)).toContain('test2.txt');
    });

    it('should return empty array when no files match names', async () => {
      const result = await fileModel.findByNames(['nonexistent']);
      expect(result).toHaveLength(0);
    });

    it('should only find files belonging to current user', async () => {
      // Prepare test data
      await serverDB.insert(files).values([
        {
          name: 'test1.txt',
          url: 'https://example.com/test1.txt',
          size: 100,
          fileType: 'text/plain',
          userId,
        },
        {
          name: 'test2.txt',
          url: 'https://example.com/test2.txt',
          size: 200,
          fileType: 'text/plain',
          userId: 'user2', // file from a different user
        },
      ]);

      const result = await fileModel.findByNames(['test']);
      expect(result).toHaveLength(1);
      expect(result[0].name).toBe('test1.txt');
    });
  });

  describe('deleteGlobalFile', () => {
    it('should delete global file by hashId', async () => {
      // Prepare test data
      const globalFile = {
        hashId: 'test-hash',
        fileType: 'text/plain',
        size: 100,
        url: 'https://example.com/global-file.txt',
        metadata: { key: 'value' },
        creator: userId,
      };

      await serverDB.insert(globalFiles).values(globalFile);

      // Execute delete operation
      await fileModel.deleteGlobalFile('test-hash');

      // Verify file has been deleted
      const result = await serverDB.query.globalFiles.findFirst({
        where: eq(globalFiles.hashId, 'test-hash'),
      });
      expect(result).toBeUndefined();
    });

    it('should not throw error when deleting non-existent global file', async () => {
      // Deleting a non-existent file should not throw an error
      await expect(fileModel.deleteGlobalFile('non-existent-hash')).resolves.not.toThrow();
    });

    it('should only delete specified global file', async () => {
      // Prepare test data
      const globalFiles1 = {
        hashId: 'hash1',
        fileType: 'text/plain',
        size: 100,
        url: 'https://example.com/file1.txt',
        creator: userId,
      };
      const globalFiles2 = {
        hashId: 'hash2',
        fileType: 'text/plain',
        size: 200,
        url: 'https://example.com/file2.txt',
        creator: userId,
      };

      await serverDB.insert(globalFiles).values([globalFiles1, globalFiles2]);

      // Delete one file
      await fileModel.deleteGlobalFile('hash1');

      // Verify only the specified file was deleted
      const remainingFiles = await serverDB.query.globalFiles.findMany();
      expect(remainingFiles).toHaveLength(1);
      expect(remainingFiles[0].hashId).toBe('hash2');
    });
  });

  describe('Transaction Support', () => {
    describe('create with transaction', () => {
      it('should create file within provided transaction', async () => {
        const params = {
          name: 'test-file-txn.txt',
          url: 'https://example.com/test-file-txn.txt',
          size: 100,
          fileType: 'text/plain',
          fileHash: 'test-hash-txn',
        };

        // Create file in transaction
        const result = await serverDB.transaction(async (trx) => {
          const { id } = await fileModel.create(params, true, trx);

          // Verify file was created inside the transaction
          const file = await trx.query.files.findFirst({ where: eq(files.id, id) });
          expect(file).toMatchObject({ ...params, userId });

          return { id };
        });

        // After transaction commit, verify file still exists
        const file = await serverDB.query.files.findFirst({ where: eq(files.id, result.id) });
        expect(file).toMatchObject({ ...params, userId });

        // Verify global file was also created
        const globalFile = await serverDB.query.globalFiles.findFirst({
          where: eq(globalFiles.hashId, params.fileHash),
        });
        expect(globalFile).toBeDefined();
      });

      it('should rollback file creation when transaction fails', async () => {
        const params = {
          name: 'test-file-rollback.txt',
          url: 'https://example.com/test-file-rollback.txt',
          size: 100,
          fileType: 'text/plain',
          fileHash: 'test-hash-rollback',
        };

        let createdFileId: string | undefined;

        // Intentionally fail the transaction
        await expect(
          serverDB.transaction(async (trx) => {
            const { id } = await fileModel.create(params, true, trx);
            createdFileId = id;

            // Verify file was created inside the transaction
            const file = await trx.query.files.findFirst({ where: eq(files.id, id) });
            expect(file).toMatchObject({ ...params, userId });

            // Throw an error to cause transaction rollback
            throw new Error('Intentional rollback');
          }),
        ).rejects.toThrow('Intentional rollback');

        // Verify file creation was rolled back
        if (createdFileId) {
          const file = await serverDB.query.files.findFirst({
            where: eq(files.id, createdFileId),
          });
          expect(file).toBeUndefined();
        }

        // Verify global file creation was also rolled back
        const globalFile = await serverDB.query.globalFiles.findFirst({
          where: eq(globalFiles.hashId, params.fileHash),
        });
        expect(globalFile).toBeUndefined();
      });

      it('should create file with knowledgeBase within transaction', async () => {
        const params = {
          name: 'test-kb-file.txt',
          url: 'https://example.com/test-kb-file.txt',
          size: 100,
          fileType: 'text/plain',
          knowledgeBaseId: 'kb1',
        };

        const result = await serverDB.transaction(async (trx) => {
          const { id } = await fileModel.create(params, false, trx);

          // Verify knowledge base file association was created
          const kbFile = await trx.query.knowledgeBaseFiles.findFirst({
            where: eq(knowledgeBaseFiles.fileId, id),
          });
          expect(kbFile).toMatchObject({ fileId: id, knowledgeBaseId: 'kb1', userId });

          return { id };
        });

        // Verify after transaction commit
        const kbFile = await serverDB.query.knowledgeBaseFiles.findFirst({
          where: eq(knowledgeBaseFiles.fileId, result.id),
        });
        expect(kbFile).toMatchObject({
          fileId: result.id,
          knowledgeBaseId: 'kb1',
          userId,
        });
      });
    });

    describe('delete with transaction', () => {
      it('should delete file within provided transaction', async () => {
        // First create the file and global file
        await fileModel.createGlobalFile({
          hashId: 'delete-txn-hash',
          url: 'https://example.com/delete-txn.txt',
          size: 100,
          fileType: 'text/plain',
          creator: userId,
        });

        const { id } = await fileModel.create({
          name: 'delete-txn-file.txt',
          url: 'https://example.com/delete-txn.txt',
          size: 100,
          fileType: 'text/plain',
          fileHash: 'delete-txn-hash',
        });

        // Delete file in transaction
        await serverDB.transaction(async (trx) => {
          await fileModel.delete(id, true, trx);

          // Verify file was deleted inside the transaction
          const file = await trx.query.files.findFirst({ where: eq(files.id, id) });
          expect(file).toBeUndefined();
        });

        // After transaction commit, verify file is still deleted
        const file = await serverDB.query.files.findFirst({ where: eq(files.id, id) });
        expect(file).toBeUndefined();

        // Verify global file was also deleted (no other references)
        const globalFile = await serverDB.query.globalFiles.findFirst({
          where: eq(globalFiles.hashId, 'delete-txn-hash'),
        });
        expect(globalFile).toBeUndefined();
      });

      it('should rollback file deletion when transaction fails', async () => {
        // First create the file and global file
        await fileModel.createGlobalFile({
          hashId: 'rollback-delete-hash',
          url: 'https://example.com/rollback-delete.txt',
          size: 100,
          fileType: 'text/plain',
          creator: userId,
        });

        const { id } = await fileModel.create({
          name: 'rollback-delete-file.txt',
          url: 'https://example.com/rollback-delete.txt',
          size: 100,
          fileType: 'text/plain',
          fileHash: 'rollback-delete-hash',
        });

        // Intentionally fail the transaction
        await expect(
          serverDB.transaction(async (trx) => {
            await fileModel.delete(id, true, trx);

            // Verify file was deleted inside the transaction
            const file = await trx.query.files.findFirst({ where: eq(files.id, id) });
            expect(file).toBeUndefined();

            // Throw an error to cause transaction rollback
            throw new Error('Intentional rollback for delete');
          }),
        ).rejects.toThrow('Intentional rollback for delete');

        // Verify file deletion was rolled back, file still exists
        const file = await serverDB.query.files.findFirst({ where: eq(files.id, id) });
        expect(file).toBeDefined();
        expect(file?.name).toBe('rollback-delete-file.txt');

        // Verify global file was also rolled back, still exists
        const globalFile = await serverDB.query.globalFiles.findFirst({
          where: eq(globalFiles.hashId, 'rollback-delete-hash'),
        });
        expect(globalFile).toBeDefined();
      });

      it('should delete file but preserve global file when removeGlobalFile=false in transaction', async () => {
        // First create the file and global file
        await fileModel.createGlobalFile({
          hashId: 'preserve-global-hash',
          url: 'https://example.com/preserve-global.txt',
          size: 100,
          fileType: 'text/plain',
          creator: userId,
        });

        const { id } = await fileModel.create({
          name: 'preserve-global-file.txt',
          url: 'https://example.com/preserve-global.txt',
          size: 100,
          fileType: 'text/plain',
          fileHash: 'preserve-global-hash',
        });

        // Delete file in transaction, but keep global file
        await serverDB.transaction(async (trx) => {
          await fileModel.delete(id, false, trx);
        });

        // Verify file was deleted
        const file = await serverDB.query.files.findFirst({ where: eq(files.id, id) });
        expect(file).toBeUndefined();

        // Verify global file was retained
        const globalFile = await serverDB.query.globalFiles.findFirst({
          where: eq(globalFiles.hashId, 'preserve-global-hash'),
        });
        expect(globalFile).toBeDefined();
      });
    });

    describe('mixed operations in transaction', () => {
      it('should support create and delete operations in same transaction', async () => {
        // First create a file to be deleted
        await fileModel.createGlobalFile({
          hashId: 'mixed-delete-hash',
          url: 'https://example.com/mixed-delete.txt',
          size: 100,
          fileType: 'text/plain',
          creator: userId,
        });

        const { id: deleteFileId } = await fileModel.create({
          name: 'mixed-delete-file.txt',
          url: 'https://example.com/mixed-delete.txt',
          size: 100,
          fileType: 'text/plain',
          fileHash: 'mixed-delete-hash',
        });

        // Delete old file and create new file in the same transaction
        const result = await serverDB.transaction(async (trx) => {
          // Delete old file
          await fileModel.delete(deleteFileId, true, trx);

          // Create new file
          const { id: newFileId } = await fileModel.create(
            {
              name: 'mixed-create-file.txt',
              url: 'https://example.com/mixed-create.txt',
              size: 200,
              fileType: 'text/plain',
              fileHash: 'mixed-create-hash',
            },
            true,
            trx,
          );

          return { newFileId };
        });

        // Verify old file was deleted
        const deletedFile = await serverDB.query.files.findFirst({
          where: eq(files.id, deleteFileId),
        });
        expect(deletedFile).toBeUndefined();

        // Verify new file was created
        const newFile = await serverDB.query.files.findFirst({
          where: eq(files.id, result.newFileId),
        });
        expect(newFile).toBeDefined();
        expect(newFile?.name).toBe('mixed-create-file.txt');

        // Verify new global file was created
        const newGlobalFile = await serverDB.query.globalFiles.findFirst({
          where: eq(globalFiles.hashId, 'mixed-create-hash'),
        });
        expect(newGlobalFile).toBeDefined();
      });
    });
  });

  describe('private getFileTypePrefix method', () => {
    beforeEach(async () => {
      // Create test files for all categories
      await serverDB.insert(files).values([
        {
          id: 'video-file',
          name: 'video.mp4',
          url: 'https://example.com/video.mp4',
          size: 1000,
          fileType: 'video/mp4',
          userId,
        },
        {
          id: 'page-file',
          name: 'page.html',
          url: 'https://example.com/page.html',
          size: 500,
          fileType: 'text/html',
          userId,
        },
        {
          id: 'unknown-file',
          name: 'unknown.xyz',
          url: 'https://example.com/unknown.xyz',
          size: 200,
          fileType: 'application/xyz',
          userId,
        },
      ]);
    });

    it('should filter video files correctly', async () => {
      const result = await fileModel.query({ category: FilesTabs.Videos });
      expect(result).toHaveLength(1);
      expect(result[0].id).toBe('video-file');
    });

    it('should filter website/page files correctly', async () => {
      const result = await fileModel.query({ category: FilesTabs.Websites });
      expect(result).toHaveLength(1);
      expect(result[0].id).toBe('page-file');
    });

    it('should handle Pages category (should use text/html like Websites)', async () => {
      // FilesTabs.Pages is not explicitly handled in switch, falls to default
      // which returns empty string, so it won't filter by file type
      const result = await fileModel.query({ category: FilesTabs.Pages });
      // Should return all files since default case returns empty string
      expect(result.length).toBeGreaterThan(0);
    });

    it('should handle unknown file category', async () => {
      // This tests the default case in switch statement
      const unknownCategory = 'unknown' as FilesTabs;

      // We need to access the private method indirectly by testing the query method
      // that uses getFileTypePrefix internally
      const params = {
        category: unknownCategory,
        current: 1,
        pageSize: 10,
      };

      // This should not throw an error and should handle the unknown category gracefully
      const result = await fileModel.query(params);
      expect(result).toBeDefined();
      expect(Array.isArray(result)).toBe(true);
    });
  });

  describe('large batch operations', () => {
    it('should handle large number of chunks deletion in batches', async () => {
      // This tests the batch processing code (lines 351-381)
      // First create a file with many chunks to test the batch deletion logic
      const testFile = {
        name: 'large-file.txt',
        url: 'https://example.com/large-file.txt',
        size: 100000,
        fileType: 'text/plain',
        fileHash: 'large-file-hash',
      };

      const { id: fileId } = await fileModel.create(testFile, true);

      // Create many chunks for this file to trigger batch processing
      // Note: This is a simplified test since we can't easily create 3000+ chunks
      // But it will still exercise the batch deletion code path

      // Insert chunks (this might need to be done through proper API)
      // For testing purposes, we'll delete the file which should trigger the batch deletion
      await fileModel.delete(fileId, true);

      // Verify the file is deleted
      const deletedFile = await serverDB.query.files.findFirst({
        where: eq(files.id, fileId),
      });
      expect(deletedFile).toBeUndefined();
    });
  });

  describe('deleteFileChunks error handling', () => {
    let consoleWarnSpy: any;

    beforeEach(() => {
      consoleWarnSpy = vi.spyOn(console, 'warn').mockImplementation(() => {});
    });

    afterEach(() => {
      consoleWarnSpy.mockRestore();
    });

    it('should delete file even when chunks deletion fails', async () => {
      // Create test file
      const testFile = {
        name: 'error-test-file.txt',
        url: 'https://example.com/error-test-file.txt',
        size: 100,
        fileType: 'text/plain',
        fileHash: 'error-test-hash',
      };

      const { id: fileId } = await fileModel.create(testFile, true);

      // Create some test data to simulate chunk associations
      const chunkId1 = '550e8400-e29b-41d4-a716-446655440001';
      const chunkId2 = '550e8400-e29b-41d4-a716-446655440002';

      // Insert chunks
      await serverDB.insert(chunks).values([
        { id: chunkId1, text: 'chunk 1', userId, type: 'text' },
        { id: chunkId2, text: 'chunk 2', userId, type: 'text' },
      ]);

      // Insert fileChunks associations
      await serverDB.insert(fileChunks).values([
        { fileId, chunkId: chunkId1, userId },
        { fileId, chunkId: chunkId2, userId },
      ]);

      // Insert embeddings (1024-dimensional vectors)
      const testEmbedding = Array.from({ length: 1024 }).fill(0.1) as number[];
      await serverDB
        .insert(embeddings)
        .values([{ chunkId: chunkId1, embeddings: testEmbedding, model: 'test-model', userId }]);

      // Skip documentChunks test, requires creating documents records first

      // Delete file, should clean up all related data
      const result = await fileModel.delete(fileId, true);

      // Verify file was deleted
      const deletedFile = await serverDB.query.files.findFirst({
        where: eq(files.id, fileId),
      });
      expect(deletedFile).toBeUndefined();

      // Verify chunks were deleted
      const remainingChunks = await serverDB.query.chunks.findMany({
        where: inArray(chunks.id, [chunkId1, chunkId2]),
      });
      expect(remainingChunks).toHaveLength(0);

      // Verify embeddings were deleted
      const remainingEmbeddings = await serverDB.query.embeddings.findMany({
        where: inArray(embeddings.chunkId, [chunkId1, chunkId2]),
      });
      expect(remainingEmbeddings).toHaveLength(0);

      // Verify fileChunks were deleted
      const remainingFileChunks = await serverDB.query.fileChunks.findMany({
        where: eq(fileChunks.fileId, fileId),
      });
      expect(remainingFileChunks).toHaveLength(0);

      expect(result).toBeDefined();
    });

    it('should successfully delete file with all related chunks and embeddings', async () => {
      // Simplified test: only verify the normal full deletion flow (after removing knowledge base protection)
      const testFile = {
        name: 'complete-deletion-test.txt',
        url: 'https://example.com/complete-deletion-test.txt',
        size: 100,
        fileType: 'text/plain',
        fileHash: 'complete-deletion-hash',
      };

      const { id: fileId } = await fileModel.create(testFile, true);

      const chunkId = '550e8400-e29b-41d4-a716-446655440003';

      // Insert chunk
      await serverDB
        .insert(chunks)
        .values([{ id: chunkId, text: 'complete test chunk', userId, type: 'text' }]);

      // Insert fileChunks associations
      await serverDB.insert(fileChunks).values([{ fileId, chunkId, userId }]);

      // Insert embeddings
      const testEmbedding = Array.from({ length: 1024 }).fill(0.1) as number[];
      await serverDB
        .insert(embeddings)
        .values([{ chunkId, embeddings: testEmbedding, model: 'test-model', userId }]);

      // Delete file
      await fileModel.delete(fileId, true);

      // Verify file was deleted
      const deletedFile = await serverDB.query.files.findFirst({
        where: eq(files.id, fileId),
      });
      expect(deletedFile).toBeUndefined();

      // Verify chunks were deleted
      const remainingChunks = await serverDB.query.chunks.findMany({
        where: eq(chunks.id, chunkId),
      });
      expect(remainingChunks).toHaveLength(0);

      // Verify embeddings were deleted
      const remainingEmbeddings = await serverDB.query.embeddings.findMany({
        where: eq(embeddings.chunkId, chunkId),
      });
      expect(remainingEmbeddings).toHaveLength(0);

      // Verify fileChunks were deleted
      const remainingFileChunks = await serverDB.query.fileChunks.findMany({
        where: eq(fileChunks.fileId, fileId),
      });
      expect(remainingFileChunks).toHaveLength(0);
    });

    it('should delete files that are in knowledge bases (removed protection)', async () => {
      // Test the fixed logic: files in knowledge bases should also be deleted
      const testFile = {
        name: 'knowledge-base-file.txt',
        url: 'https://example.com/knowledge-base-file.txt',
        size: 100,
        fileType: 'text/plain',
        fileHash: 'kb-file-hash',
        knowledgeBaseId: 'kb1',
      };

      const { id: fileId } = await fileModel.create(testFile, true);

      const chunkId = '550e8400-e29b-41d4-a716-446655440007';

      // Insert chunk and association data
      await serverDB
        .insert(chunks)
        .values([{ id: chunkId, text: 'knowledge base chunk', userId, type: 'text' }]);

      await serverDB.insert(fileChunks).values([{ fileId, chunkId, userId }]);

      // Insert embeddings (1024-dimensional vectors)
      const testEmbedding = Array.from({ length: 1024 }).fill(0.1) as number[];
      await serverDB
        .insert(embeddings)
        .values([{ chunkId, embeddings: testEmbedding, model: 'test-model', userId }]);

      // Verify file is indeed in the knowledge base
      const kbFile = await serverDB.query.knowledgeBaseFiles.findFirst({
        where: eq(knowledgeBaseFiles.fileId, fileId),
      });
      expect(kbFile).toBeDefined();

      // Delete file
      await fileModel.delete(fileId, true);

      // Verify files in knowledge base were also completely deleted
      const deletedFile = await serverDB.query.files.findFirst({
        where: eq(files.id, fileId),
      });
      expect(deletedFile).toBeUndefined();

      // Verify chunks were deleted (this is the core of the fix: previously chunks of knowledge base files would not be deleted)
      const remainingChunks = await serverDB.query.chunks.findMany({
        where: eq(chunks.id, chunkId),
      });
      expect(remainingChunks).toHaveLength(0);

      // Verify embeddings were deleted
      const remainingEmbeddings = await serverDB.query.embeddings.findMany({
        where: eq(embeddings.chunkId, chunkId),
      });
      expect(remainingEmbeddings).toHaveLength(0);

      // Verify fileChunks were deleted
      const remainingFileChunks = await serverDB.query.fileChunks.findMany({
        where: eq(fileChunks.fileId, fileId),
      });
      expect(remainingFileChunks).toHaveLength(0);
    });
  });

  describe('static getFileById', () => {
    it('should return a file by id', async () => {
      const [file] = await serverDB
        .insert(files)
        .values({
          id: 'static-file-id',
          userId,
          name: 'static-file.txt',
          url: 'https://example.com/file.txt',
          fileType: 'text/plain',
          size: 100,
        })
        .returning();

      const result = await FileModel.getFileById(serverDB, file.id);
      expect(result).toBeDefined();
      expect(result?.id).toBe(file.id);
      expect(result?.name).toBe('static-file.txt');
    });

    it('should return undefined for non-existent file', async () => {
      const result = await FileModel.getFileById(serverDB, 'non-existent');
      expect(result).toBeUndefined();
    });
  });

  describe('findByIds', () => {
    it('should find multiple files by ids', async () => {
      await serverDB.insert(files).values([
        {
          id: 'find-id-1',
          userId,
          name: 'file1.txt',
          url: 'url1',
          fileType: 'text/plain',
          size: 100,
        },
        {
          id: 'find-id-2',
          userId,
          name: 'file2.txt',
          url: 'url2',
          fileType: 'text/plain',
          size: 200,
        },
      ]);

      const result = await fileModel.findByIds(['find-id-1', 'find-id-2']);
      expect(result).toHaveLength(2);
    });

    it('should only return files belonging to current user', async () => {
      const otherUserId = 'other-file-user';
      await serverDB.insert(users).values({ id: otherUserId });
      await serverDB.insert(files).values({
        id: 'other-file-id',
        userId: otherUserId,
        name: 'other.txt',
        url: 'url',
        fileType: 'text/plain',
        size: 100,
      });

      const result = await fileModel.findByIds(['other-file-id']);
      expect(result).toHaveLength(0);
    });
  });

  describe('findFilesToInitInSandbox', () => {
    const sessionId = 'sandbox-session-1';
    const topicId = 'sandbox-topic-1';

    beforeEach(async () => {
      await serverDB.insert(sessions).values({ id: sessionId, userId });
      await serverDB.insert(topics).values([
        { id: topicId, sessionId, userId },
        { id: 'sandbox-topic-2', sessionId, userId },
      ]);
      await serverDB.insert(messages).values([
        { id: 'sandbox-msg-1', role: 'user', topicId, userId },
        { id: 'sandbox-msg-2', role: 'user', topicId: 'sandbox-topic-2', userId },
      ]);
      await serverDB.insert(files).values([
        { fileType: 'text/csv', id: 'sf-msg', name: 'msg.csv', size: 1, url: 'k-msg', userId },
        {
          fileType: 'application/pdf',
          id: 'sf-sess',
          name: 's.pdf',
          size: 2,
          url: 'k-sess',
          userId,
        },
        { fileType: 'text/plain', id: 'sf-both', name: 'both.txt', size: 3, url: 'k-both', userId },
        { fileType: 'text/plain', id: 'sf-other', name: 'o.txt', size: 4, url: 'k-other', userId },
      ]);
    });

    it('merges topic message files and session files, de-duped by id', async () => {
      await serverDB.insert(messagesFiles).values([
        { fileId: 'sf-msg', messageId: 'sandbox-msg-1', userId },
        { fileId: 'sf-both', messageId: 'sandbox-msg-1', userId },
        // attached to a different topic → must be excluded
        { fileId: 'sf-other', messageId: 'sandbox-msg-2', userId },
      ]);
      await serverDB.insert(filesToSessions).values([
        { fileId: 'sf-sess', sessionId, userId },
        // also referenced via message → must be de-duped
        { fileId: 'sf-both', sessionId, userId },
      ]);

      const result = await fileModel.findFilesToInitInSandbox(topicId);

      expect(result.map((file) => file.id).sort()).toEqual(['sf-both', 'sf-msg', 'sf-sess']);
      expect(result.find((file) => file.id === 'sf-both')).toEqual({
        fileType: 'text/plain',
        id: 'sf-both',
        name: 'both.txt',
        size: 3,
        url: 'k-both',
      });
    });

    it('returns an empty array when the topic has no associated files', async () => {
      const result = await fileModel.findFilesToInitInSandbox(topicId);
      expect(result).toEqual([]);
    });

    it('does not return files belonging to another user', async () => {
      await serverDB.insert(messages).values({
        id: 'sandbox-msg-other',
        role: 'user',
        topicId,
        userId: 'user2',
      });
      await serverDB.insert(files).values({
        fileType: 'text/plain',
        id: 'sf-user2',
        name: 'u2.txt',
        size: 5,
        url: 'k-u2',
        userId: 'user2',
      });
      await serverDB
        .insert(messagesFiles)
        .values({ fileId: 'sf-user2', messageId: 'sandbox-msg-other', userId: 'user2' });

      const result = await fileModel.findFilesToInitInSandbox(topicId);
      expect(result).toEqual([]);
    });
  });

  describe('updateGlobalFile', () => {
    it('should update url and metadata of a global file by hashId', async () => {
      await fileModel.createGlobalFile({
        hashId: 'update-hash',
        fileType: 'text/plain',
        size: 100,
        url: 'https://example.com/old.txt',
        metadata: { version: 1 },
        creator: userId,
      });

      await fileModel.updateGlobalFile('update-hash', {
        url: 'https://example.com/new.txt',
        metadata: { version: 2 },
      });

      const updated = await serverDB.query.globalFiles.findFirst({
        where: eq(globalFiles.hashId, 'update-hash'),
      });

      expect(updated?.url).toBe('https://example.com/new.txt');
      expect(updated?.metadata).toEqual({ version: 2 });
    });

    it('should support running inside a provided transaction', async () => {
      await fileModel.createGlobalFile({
        hashId: 'trx-update-hash',
        fileType: 'text/plain',
        size: 100,
        url: 'https://example.com/old.txt',
        metadata: { version: 1 },
        creator: userId,
      });

      await serverDB.transaction(async (trx) => {
        await fileModel.updateGlobalFile(
          'trx-update-hash',
          { url: 'https://example.com/trx.txt' },
          trx,
        );
      });

      const updated = await serverDB.query.globalFiles.findFirst({
        where: eq(globalFiles.hashId, 'trx-update-hash'),
      });

      expect(updated?.url).toBe('https://example.com/trx.txt');
    });
  });

  describe('transferTo', () => {
    const targetWorkspaceId = 'transfer-target-ws';

    beforeEach(async () => {
      await serverDB.insert(workspaces).values({
        id: targetWorkspaceId,
        name: 'Target WS',
        slug: 'transfer-target-ws',
        primaryOwnerId: userId,
      });
    });

    it('should transfer ownership of a file and re-point knowledge base links', async () => {
      await serverDB.insert(files).values({
        id: 'transfer-file-1',
        name: 'transfer.txt',
        fileType: 'text/plain',
        size: 10,
        url: 'k-transfer',
        userId,
      });
      await serverDB.insert(knowledgeBaseFiles).values({
        fileId: 'transfer-file-1',
        knowledgeBaseId: knowledgeBase.id,
        userId,
      });

      const result = await fileModel.transferTo('transfer-file-1', targetWorkspaceId, 'user2');

      expect(result).toEqual({ fileId: 'transfer-file-1' });

      const file = await serverDB.query.files.findFirst({
        where: eq(files.id, 'transfer-file-1'),
      });
      expect(file?.userId).toBe('user2');
      expect(file?.workspaceId).toBe(targetWorkspaceId);

      const kbLink = await serverDB.query.knowledgeBaseFiles.findFirst({
        where: eq(knowledgeBaseFiles.fileId, 'transfer-file-1'),
      });
      expect(kbLink?.userId).toBe('user2');
    });

    it('should support transferring to a null (personal) workspace', async () => {
      await serverDB.insert(files).values({
        id: 'transfer-file-2',
        name: 'transfer2.txt',
        fileType: 'text/plain',
        size: 10,
        url: 'k-transfer2',
        userId,
      });

      await fileModel.transferTo('transfer-file-2', null, 'user2');

      const file = await serverDB.query.files.findFirst({
        where: eq(files.id, 'transfer-file-2'),
      });
      expect(file?.userId).toBe('user2');
      expect(file?.workspaceId).toBeNull();
    });

    it('should throw when the file does not exist or is not owned', async () => {
      await expect(
        fileModel.transferTo('non-existent-file', targetWorkspaceId, 'user2'),
      ).rejects.toThrow('File not found');
    });
  });

  describe('copyToWorkspace', () => {
    const targetWorkspaceId = 'copy-target-ws';

    beforeEach(async () => {
      await serverDB.insert(workspaces).values({
        id: targetWorkspaceId,
        name: 'Copy Target WS',
        slug: 'copy-target-ws',
        primaryOwnerId: userId,
      });
    });

    it('should clone a file row into the target scope and reset index task ids', async () => {
      await fileModel.createGlobalFile({
        hashId: 'copy-hash',
        fileType: 'text/plain',
        size: 42,
        url: 'k-copy',
        creator: userId,
      });
      await serverDB.insert(files).values({
        id: 'copy-file-1',
        name: 'copy.txt',
        fileType: 'text/plain',
        fileHash: 'copy-hash',
        size: 42,
        url: 'k-copy',
        metadata: { original: true },
        chunkTaskId: null,
        embeddingTaskId: null,
        userId,
      });

      const result = await fileModel.copyToWorkspace('copy-file-1', targetWorkspaceId, 'user2');

      expect(result.fileId).toBeDefined();
      expect(result.fileId).not.toBe('copy-file-1');

      const copied = await serverDB.query.files.findFirst({
        where: eq(files.id, result.fileId),
      });
      expect(copied?.userId).toBe('user2');
      expect(copied?.workspaceId).toBe(targetWorkspaceId);
      expect(copied?.fileHash).toBe('copy-hash');
      expect(copied?.name).toBe('copy.txt');
      expect(copied?.size).toBe(42);
      expect(copied?.chunkTaskId).toBeNull();
      expect(copied?.embeddingTaskId).toBeNull();
      expect(copied?.parentId).toBeNull();
      expect((copied?.metadata as Record<string, unknown>).duplicatedFrom).toBe('copy-file-1');
      expect((copied?.metadata as Record<string, unknown>).original).toBe(true);

      // Original file remains untouched
      const original = await serverDB.query.files.findFirst({
        where: eq(files.id, 'copy-file-1'),
      });
      expect(original?.userId).toBe(userId);
    });

    it('should support copying to a null (personal) workspace', async () => {
      await serverDB.insert(files).values({
        id: 'copy-file-2',
        name: 'copy2.txt',
        fileType: 'text/plain',
        size: 1,
        url: 'k-copy2',
        userId,
      });

      const result = await fileModel.copyToWorkspace('copy-file-2', null, 'user2');

      const copied = await serverDB.query.files.findFirst({
        where: eq(files.id, result.fileId),
      });
      expect(copied?.workspaceId).toBeNull();
      expect(copied?.userId).toBe('user2');
    });

    it('should throw when the source file does not exist or is not owned', async () => {
      await expect(
        fileModel.copyToWorkspace('non-existent-file', targetWorkspaceId, 'user2'),
      ).rejects.toThrow('File not found');
    });
  });
});
