delivery-desk/scripts/test-sqlite-review-rounds.ts

process.env.NODE_ENV = 'test';
delete process.env.DATABASE_URL;

const { db, repairMultiSchemeRounds } = await import('../api/db.js');
const { database, closeDatabase } = await import('../api/database.js');
const { decideRoundInTransaction, ReviewDecisionError } = await import('../api/services/reviewService.js');
const { addFeedbackReply, findFeedbackTarget, withdrawFeedback } = await import('../api/services/feedbackService.js');

function assert(condition: unknown, message: string): asserts condition { if (!condition) throw new Error(message); }

db.exec('BEGIN IMMEDIATE');
try {
  const group = await database.one<{ id: number }>('SELECT id FROM operation_groups ORDER BY id LIMIT 1');
  assert(group, 'SQLite 测试需要至少一个运营组');
  const project = { id: await database.insertId("INSERT INTO projects (group_id,name,slug) VALUES (?,?,?)", [group.id, '单轮验收测试', `round-test-${Date.now()}`]) };
  const collectionId = await database.insertId("INSERT INTO collections (project_id,name,status) VALUES (?,?,'reviewing')", [project.id, `轮次测试 ${Date.now()}`]);
  const noteId = await database.insertId("INSERT INTO notes (project_id,collection_id,title,review_status) VALUES (?,?,?,'pending')", [project.id, collectionId, '第 1 轮']);
  const round1 = await database.insertId("INSERT INTO review_rounds (note_id,round_number,status) VALUES (?,1,'reviewing')", [noteId]);
  await database.execute('UPDATE notes SET active_round_id=? WHERE id=?', [round1, noteId]);
  await database.execute("INSERT INTO work_versions (note_id,version_number,title,review_status,review_round_id,candidate_name,candidate_status) VALUES (?,1,?,'pending',?,'','pending')", [noteId, '第 1 轮', round1]);

  db.exec('DROP INDEX idx_work_versions_one_per_round');
  await database.execute("INSERT INTO work_versions (note_id,version_number,title,review_status,review_round_id) VALUES (?,99,?,'pending',?)", [noteId, '旧多方案数据', round1]);
  repairMultiSchemeRounds();
  const migrated = await database.one<{ review_round_id: number; round_status: string }>('SELECT v.review_round_id,r.status AS round_status FROM work_versions v JOIN review_rounds r ON r.id=v.review_round_id WHERE v.note_id=? AND v.version_number=99', [noteId]);
  assert(migrated && Number(migrated.review_round_id) !== Number(round1) && migrated.round_status === 'completed', '旧多方案轮次未拆分为单方案历史轮次');
  await database.execute('DELETE FROM work_versions WHERE note_id=? AND version_number=99', [noteId]);
  await database.execute('DELETE FROM review_rounds WHERE id=?', [migrated.review_round_id]);
  db.exec('CREATE UNIQUE INDEX idx_work_versions_one_per_round ON work_versions(review_round_id) WHERE review_round_id IS NOT NULL');

  let duplicateRejected = false;
  try { await database.execute("INSERT INTO work_versions (note_id,version_number,title,review_status,review_round_id) VALUES (?,2,?,'pending',?)", [noteId, '非法第二方案', round1]); }
  catch { duplicateRejected = true; }
  assert(duplicateRejected, '同一验收轮次必须拒绝第二个方案');

  await decideRoundInTransaction(database, { noteId, versionNumber: 1, projectId: project.id, decision: 'changes_requested', reason: '需要调整', actorName: '测试客户', actorRole: 'client' });
  assert((await database.one<{ review_status: string }>('SELECT review_status FROM notes WHERE id=?', [noteId]))?.review_status === 'changes_requested', '要求修改后作品状态错误');
  assert((await database.one<{ completion_reason: string }>('SELECT completion_reason FROM review_rounds WHERE id=?', [round1]))?.completion_reason === 'changes_requested', '退修轮次未完成');

  const round2 = await database.insertId("INSERT INTO review_rounds (note_id,round_number,status) VALUES (?,2,'reviewing')", [noteId]);
  await database.execute("INSERT INTO work_versions (note_id,version_number,title,review_status,review_round_id,candidate_name,candidate_status) VALUES (?,2,?,'pending',?,'','pending')", [noteId, '第 2 轮', round2]);
  await database.execute("UPDATE notes SET active_round_id=?,version_number=2,title=?,review_status='pending' WHERE id=?", [round2, '第 2 轮', noteId]);
  const imageId = await database.insertId("INSERT INTO images (note_id,url,version_number) VALUES (?, '/test.jpg', 2)", [noteId]);
  const annotationId = await database.insertId("INSERT INTO annotations (image_id,x,y,content,author_name,author_role) VALUES (?,.5,.5,'待处理','测试运营','operator')", [imageId]);
  const commentId = await database.insertId("INSERT INTO work_comments (note_id,version_number,content) VALUES (?,2,'总体意见')", [noteId]);

  const feedbackTarget = await findFeedbackTarget(noteId, 'image_annotation', annotationId);
  assert(feedbackTarget, '无法按作品找到图片批注');
  await addFeedbackReply(feedbackTarget, 'image_annotation', annotationId, '已收到，正在处理', '测试客户', 'client');
  assert((await database.one<{ count: number }>('SELECT COUNT(*) AS count FROM feedback_replies WHERE note_id=? AND feedback_id=?', [noteId, annotationId]))?.count === 1, '批注回复未保存');
  await withdrawFeedback('image_annotation', annotationId);
  assert(Boolean((await database.one<{ withdrawn_at: string | null }>('SELECT withdrawn_at FROM annotations WHERE id=?', [annotationId]))?.withdrawn_at), '撤回没有保留时间记录');

  await decideRoundInTransaction(database, { noteId, versionNumber: 2, projectId: project.id, decision: 'approved', reason: '', actorName: '测试客户', actorRole: 'client' });
  const note = await database.one<{ version_number: number; review_status: string }>('SELECT version_number,review_status FROM notes WHERE id=?', [noteId]);
  assert(note?.version_number === 2 && note.review_status === 'approved', '第 2 轮通过后作品状态错误');
  assert((await database.one<{ review_status: string }>('SELECT review_status FROM projects WHERE id=?', [project.id]))?.review_status === 'completed', '全部作品通过后项目未完成');
  assert((await database.one<{ closure_reason: string }>('SELECT closure_reason FROM work_comments WHERE id=?', [commentId]))?.closure_reason === 'approved_with_round', '未处理反馈没有随轮关闭');

  let historicalRejected = false;
  try { await decideRoundInTransaction(database, { noteId, versionNumber: 1, projectId: project.id, decision: 'approved', reason: '', actorName: '测试客户', actorRole: 'client' }); }
  catch (error) { historicalRejected = error instanceof ReviewDecisionError && error.statusCode === 409; }
  assert(historicalRejected, '历史轮次不应被重复验收');
  process.stdout.write('SQLite 单方案轮次验证通过：一轮一稿、退修、回复、留痕撤回、通过、项目完成与历史只读\n');
} finally {
  db.exec('ROLLBACK');
  await closeDatabase();
}