pn-new-crm/tests/integration/backfill-document-folders.test.ts

/**
 * Task 11 - backfill-document-folders integration tests.
 *
 * Five cases:
 *   1. Creates system roots and entity subfolders.
 *   2. Sets files.folder_id from entity FKs.
 *   3. Copies entity FKs from completed workflows onto signed files.
 *   4. Idempotent - second run produces the same result.
 *   5. Port isolation - does not touch other ports.
 */

import { describe, it, expect, beforeAll, beforeEach } from 'vitest';
import { and, eq } from 'drizzle-orm';

import { db } from '@/lib/db';
import { documentFolders, files, documents } from '@/lib/db/schema/documents';
import { user } from '@/lib/db/schema/users';
import { runBackfill } from '../../scripts/backfill-document-folders';
import { makePort, makeClient } from '../helpers/factories';

let TEST_USER_ID = '';

beforeAll(async () => {
  const [u] = await db.select({ id: user.id }).from(user).limit(1);
  if (!u) throw new Error('No user available; run pnpm db:seed first');
  TEST_USER_ID = u.id;
});

describe('backfill-document-folders · runBackfill', () => {
  let portId: string;

  beforeEach(async () => {
    const port = await makePort();
    portId = port.id;
    // Clean up any folders left by a prior test on this port.
    await db.delete(documentFolders).where(eq(documentFolders.portId, portId));
  });

  // ── Test 1: Creates system roots and entity subfolders ─────────────────────

  it('creates the three system roots and a client entity subfolder', async () => {
    const client = await makeClient({ portId });

    // Insert a file linked to the client so the backfill creates the subfolder.
    await db.insert(files).values({
      portId,
      clientId: client.id,
      filename: 'test.pdf',
      originalName: 'test.pdf',
      storagePath: `${portId}/test.pdf`,
      storageBucket: 'crm-files',
      uploadedBy: TEST_USER_ID,
    });

    await runBackfill({ portId, systemUserId: TEST_USER_ID });

    const roots = await db
      .select()
      .from(documentFolders)
      .where(and(eq(documentFolders.portId, portId), eq(documentFolders.entityType, 'root')));

    expect(roots).toHaveLength(3);
    const rootNames = roots.map((r) => r.name).sort();
    expect(rootNames).toEqual(['Clients', 'Companies', 'Yachts']);

    const entityFolder = await db.query.documentFolders.findFirst({
      where: and(
        eq(documentFolders.portId, portId),
        eq(documentFolders.entityType, 'client'),
        eq(documentFolders.entityId, client.id),
      ),
    });
    expect(entityFolder).toBeDefined();
    expect(entityFolder?.entityType).toBe('client');
    expect(entityFolder?.entityId).toBe(client.id);
  });

  // ── Test 2: Sets files.folder_id from entity FKs ────────────────────────────

  it('sets files.folder_id for files that have entity FKs but no folder_id', async () => {
    const client = await makeClient({ portId });

    const [fileRow] = await db
      .insert(files)
      .values({
        portId,
        clientId: client.id,
        filename: 'contract.pdf',
        originalName: 'contract.pdf',
        storagePath: `${portId}/contract.pdf`,
        storageBucket: 'crm-files',
        uploadedBy: TEST_USER_ID,
        // folderId intentionally omitted → null
      })
      .returning();

    expect(fileRow!.folderId).toBeNull();

    await runBackfill({ portId, systemUserId: TEST_USER_ID });

    const updated = await db.query.files.findFirst({
      where: and(eq(files.id, fileRow!.id), eq(files.portId, portId)),
    });

    expect(updated!.folderId).not.toBeNull();

    // The assigned folder must be the client's entity subfolder.
    const entityFolder = await db.query.documentFolders.findFirst({
      where: and(
        eq(documentFolders.portId, portId),
        eq(documentFolders.entityType, 'client'),
        eq(documentFolders.entityId, client.id),
      ),
    });
    expect(updated!.folderId).toBe(entityFolder!.id);
  });

  // ── Test 3: Copies entity FKs from completed workflows onto signed files ────

  it('propagates entity FKs from completed workflow onto signed file and sets folder_id', async () => {
    const client = await makeClient({ portId });

    // File row with no entity FK (simulating a legacy completed-before-auto-deposit).
    const [signedFile] = await db
      .insert(files)
      .values({
        portId,
        // No clientId set - simulates legacy completion before entity FK auto-propagation.
        filename: 'signed-eoi.pdf',
        originalName: 'signed-eoi.pdf',
        storagePath: `${portId}/signed-eoi.pdf`,
        storageBucket: 'crm-files',
        uploadedBy: TEST_USER_ID,
      })
      .returning();

    expect(signedFile!.clientId).toBeNull();

    // Completed workflow document pointing at the orphaned file.
    await db.insert(documents).values({
      portId,
      documentType: 'eoi',
      title: 'EOI for client',
      status: 'completed',
      signedFileId: signedFile!.id,
      clientId: client.id,
      createdBy: TEST_USER_ID,
    });

    await runBackfill({ portId, systemUserId: TEST_USER_ID });

    const updatedFile = await db.query.files.findFirst({
      where: and(eq(files.id, signedFile!.id), eq(files.portId, portId)),
    });

    // The backfill should have set the clientId on the signed file.
    expect(updatedFile!.clientId).toBe(client.id);
    // And then assigned it to the client's entity subfolder.
    expect(updatedFile!.folderId).not.toBeNull();
  });

  // ── Test 4: Idempotent ────────────────────────────────────────────────────────

  it('is idempotent - running twice produces the same number of folder rows', async () => {
    const client = await makeClient({ portId });

    await db.insert(files).values({
      portId,
      clientId: client.id,
      filename: 'idempotent.pdf',
      originalName: 'idempotent.pdf',
      storagePath: `${portId}/idempotent.pdf`,
      storageBucket: 'crm-files',
      uploadedBy: TEST_USER_ID,
    });

    await runBackfill({ portId, systemUserId: TEST_USER_ID });

    const countAfterFirst = await db
      .select()
      .from(documentFolders)
      .where(eq(documentFolders.portId, portId));

    await runBackfill({ portId, systemUserId: TEST_USER_ID });

    const countAfterSecond = await db
      .select()
      .from(documentFolders)
      .where(eq(documentFolders.portId, portId));

    expect(countAfterSecond).toHaveLength(countAfterFirst.length);
  });

  // ── Test 5: Port isolation ────────────────────────────────────────────────────

  it('does not create folders for a different port when only portId is supplied', async () => {
    const otherPort = await makePort();
    await db.delete(documentFolders).where(eq(documentFolders.portId, otherPort.id));

    const otherClient = await makeClient({ portId: otherPort.id });

    await db.insert(files).values({
      portId: otherPort.id,
      clientId: otherClient.id,
      filename: 'other-port.pdf',
      originalName: 'other-port.pdf',
      storagePath: `${otherPort.id}/other-port.pdf`,
      storageBucket: 'crm-files',
      uploadedBy: TEST_USER_ID,
    });

    // Run backfill only for the main portId, NOT the otherPort.
    await runBackfill({ portId, systemUserId: TEST_USER_ID });

    const otherPortFolders = await db
      .select()
      .from(documentFolders)
      .where(eq(documentFolders.portId, otherPort.id));

    // The other port should have zero folders - the backfill was not run for it.
    expect(otherPortFolders).toHaveLength(0);
  });
});