audio_codebuddy/tests/integration/db/prisma-crud.test.ts

/**
 * 数据库集成测试 - Prisma CRUD 操作
 * 测试真实的数据库操作，包括事务和回滚
 */

import { describe, it, expect, beforeAll, afterAll, beforeEach, afterEach } from 'vitest';
import { PrismaClient } from '@prisma/client';

const TEST_DATABASE_URL = process.env.TEST_DATABASE_URL || 'mysql://root:@localhost:3307/audio_book_test';

const db = new PrismaClient({
  datasources: {
    db: { url: TEST_DATABASE_URL }
  }
});

describe('Prisma CRUD 集成测试', () => {
  beforeAll(async () => {
    await db.$connect();
  });

  afterAll(async () => {
    await db.$disconnect();
  });

  beforeEach(async () => {
    await db.$executeRawUnsafe('BEGIN');
  });

  afterEach(async () => {
    await db.$executeRawUnsafe('ROLLBACK');
  });

  describe('User CRUD', () => {
    it('创建用户并查询', async () => {
      const phone = `138${Date.now()}`.slice(0, 11);
      const user = await db.user.create({
        data: {
          phone,
          nickname: `测试用户_${Date.now()}`,
          memberLevel: 0,
          dailyUsage: 0,
        }
      });

      expect(user.id).toBeDefined();
      expect(user.phone).toBe(phone);
      expect(user.memberLevel).toBe(0);

      const found = await db.user.findUnique({ where: { id: user.id } });
      expect(found).not.toBeNull();
      expect(found?.phone).toBe(phone);
    });

    it('更新用户会员等级', async () => {
      const phone = `138${Date.now()}`.slice(0, 11);
      const user = await db.user.create({
        data: {
          phone,
          nickname: `测试用户_${Date.now()}`,
          memberLevel: 0,
          dailyUsage: 0,
        }
      });

      const updated = await db.user.update({
        where: { id: user.id },
        data: { memberLevel: 2 }
      });

      expect(updated.memberLevel).toBe(2);
    });

    it('软删除用户', async () => {
      const phone = `138${Date.now()}`.slice(0, 11);
      const user = await db.user.create({
        data: {
          phone,
          nickname: `测试用户_${Date.now()}`,
          memberLevel: 0,
          dailyUsage: 0,
          deletedAt: null,
        }
      });

      await db.user.update({
        where: { id: user.id },
        data: { deletedAt: new Date() }
      });

      const found = await db.user.findUnique({ where: { id: user.id } });
      expect(found?.deletedAt).not.toBeNull();
    });

    it('唯一索引冲突 - 重复手机号', async () => {
      const phone = `138${Date.now()}`.slice(0, 11);
      await db.user.create({
        data: {
          phone,
          nickname: '用户1',
          memberLevel: 0,
          dailyUsage: 0,
        }
      });

      await expect(
        db.user.create({
          data: {
            phone,
            nickname: '用户2',
            memberLevel: 0,
            dailyUsage: 0,
          }
        })
      ).rejects.toThrow();
    });
  });

  describe('Book + Chapter 关联查询', () => {
    it('创建书籍时级联创建章节', async () => {
      const phone = `138${Date.now()}`.slice(0, 11);
      const user = await db.user.create({
        data: {
          phone,
          nickname: `测试用户_${Date.now()}`,
          memberLevel: 0,
          dailyUsage: 0,
        }
      });

      const book = await db.book.create({
        data: {
          userId: user.id,
          title: `测试书籍_${Date.now()}`,
          description: '测试描述',
          totalChapters: 3,
          estimatedWords: 30000,
          status: 'draft',
        }
      });

      const chapter1 = await db.bookChapter.create({
        data: {
          bookId: book.id,
          chapterIndex: 1,
          title: '第一章',
          genStage: 'idle',
        }
      });

      const chapter2 = await db.bookChapter.create({
        data: {
          bookId: book.id,
          chapterIndex: 2,
          title: '第二章',
          genStage: 'idle',
        }
      });

      expect(chapter1.id).toBeDefined();
      expect(chapter2.id).toBeDefined();
      expect(chapter1.bookId).toBe(book.id);
    });

    it('查询书籍时包含所有章节', async () => {
      const phone = `138${Date.now()}`.slice(0, 11);
      const user = await db.user.create({
        data: {
          phone,
          nickname: `测试用户_${Date.now()}`,
          memberLevel: 0,
          dailyUsage: 0,
        }
      });

      const book = await db.book.create({
        data: {
          userId: user.id,
          title: `测试书籍_${Date.now()}`,
          description: '测试描述',
          totalChapters: 2,
          estimatedWords: 20000,
          status: 'draft',
        }
      });

      await db.bookChapter.createMany({
        data: [
          { bookId: book.id, chapterIndex: 1, title: '第一章', genStage: 'idle' },
          { bookId: book.id, chapterIndex: 2, title: '第二章', genStage: 'idle' },
        ]
      });

      const found = await db.book.findUnique({
        where: { id: book.id },
        include: { chapters: true }
      });

      expect(found).not.toBeNull();
      expect(found?.chapters).toHaveLength(2);
    });

    it('删除书籍时级联删除章节', async () => {
      const phone = `138${Date.now()}`.slice(0, 11);
      const user = await db.user.create({
        data: {
          phone,
          nickname: `测试用户_${Date.now()}`,
          memberLevel: 0,
          dailyUsage: 0,
        }
      });

      const book = await db.book.create({
        data: {
          userId: user.id,
          title: `测试书籍_${Date.now()}`,
          description: '测试描述',
          totalChapters: 2,
          estimatedWords: 20000,
          status: 'draft',
        }
      });

      await db.bookChapter.createMany({
        data: [
          { bookId: book.id, chapterIndex: 1, title: '第一章', genStage: 'idle' },
          { bookId: book.id, chapterIndex: 2, title: '第二章', genStage: 'idle' },
        ]
      });

      // 删除书籍（不级联删除，需要手动删除章节）
      await db.book.delete({ where: { id: book.id } });

      const chapters = await db.bookChapter.findMany({ where: { bookId: book.id } });
      expect(chapters).toHaveLength(0);
    });
  });

  describe('事务', () => {
    it('事务失败时全部回滚', async () => {
      const phone1 = `138${Date.now()}`.slice(0, 11);
      const phone2 = `138${Date.now() + 1}`.slice(0, 11);

      const user1 = await db.user.create({
        data: {
          phone: phone1,
          nickname: `用户1_${Date.now()}`,
          memberLevel: 0,
          dailyUsage: 0,
        }
      });

      try {
        await db.$transaction([
          db.user.update({
            where: { id: user1.id },
            data: { memberLevel: 1 }
          }),
          db.user.create({
            data: {
              phone: phone2,
              nickname: `用户2_${Date.now()}`,
              memberLevel: 0,
              dailyUsage: 0,
            }
          }),
          db.user.create({
            data: {
              phone: phone2, // 重复手机号，应该失败
              nickname: `用户2重复_${Date.now()}`,
              memberLevel: 0,
              dailyUsage: 0,
            }
          }),
        ]);
      } catch (e) {
        // 预期失败
      }

      // 检查第一个用户的等级没有被修改（事务回滚）
      const found = await db.user.findUnique({ where: { id: user1.id } });
      expect(found?.memberLevel).toBe(0);
    });

    it('并发事务不互相干扰', async () => {
      const phone = `138${Date.now()}`.slice(0, 11);

      const user = await db.user.create({
        data: {
          phone,
          nickname: `测试用户_${Date.now()}`,
          memberLevel: 0,
          dailyUsage: 0,
        }
      });

      // 并发更新
      const [result1, result2] = await Promise.all([
        db.user.update({
          where: { id: user.id },
          data: { dailyUsage: { increment: 1 } }
        }),
        db.user.update({
          where: { id: user.id },
          data: { dailyUsage: { increment: 2 } }
        }),
      ]);

      // 最终值应该是 3（1 + 2）
      const final = await db.user.findUnique({ where: { id: user.id } });
      expect(final?.dailyUsage).toBeGreaterThanOrEqual(3);
    });
  });

  describe('Token 余额操作', () => {
    it('创建 TokenBalance', async () => {
      const phone = `138${Date.now()}`.slice(0, 11);
      const user = await db.user.create({
        data: {
          phone,
          nickname: `测试用户_${Date.now()}`,
          memberLevel: 0,
          dailyUsage: 0,
        }
      });

      const balance = await db.tokenBalance.create({
        data: {
          userId: user.id,
          totalTokens: 10000,
          usedTokens: 0,
        }
      });

      expect(balance.id).toBeDefined();
      expect(balance.totalTokens).toBe(10000);
      expect(balance.usedTokens).toBe(0);
    });

    it('扣除 Token', async () => {
      const phone = `138${Date.now()}`.slice(0, 11);
      const user = await db.user.create({
        data: {
          phone,
          nickname: `测试用户_${Date.now()}`,
          memberLevel: 0,
          dailyUsage: 0,
        }
      });

      const balance = await db.tokenBalance.create({
        data: {
          userId: user.id,
          totalTokens: 10000,
          usedTokens: 0,
        }
      });

      await db.tokenBalance.update({
        where: { id: balance.id },
        data: { usedTokens: { increment: 500 } }
      });

      const updated = await db.tokenBalance.findUnique({ where: { id: balance.id } });
      expect(updated?.usedTokens).toBe(500);
      expect(updated?.totalTokens - updated!.usedTokens).toBe(9500);
    });
  });
});