erika/lib/form/submission-write.ts

import "server-only";

import { db } from "@/db";
import { answers, submissions } from "@/db/schema";
import { and, count, eq, lt, lte, or } from "drizzle-orm";
import type {
  NormalizedSubmissionAnswer,
  StoredAnswerSnapshot,
} from "@/lib/form/submission-intake";

export type SubmissionWriteResult = {
  submissionId: string;
  isUpdate: boolean;
  oldAnswersDict: Record<string, StoredAnswerSnapshot>;
  totalAnswers: number;
  queueNumber: number;
};

export async function writeSubmission({
  formId,
  discordId,
  userName,
  allowMultipleSubmissions,
  normalizedAnswers,
  targetSubmissionId,
}: {
  formId: string;
  discordId: string;
  userName: string | null;
  allowMultipleSubmissions: boolean;
  normalizedAnswers: NormalizedSubmissionAnswer[];
  targetSubmissionId?: string | null;
}): Promise<SubmissionWriteResult> {
  return db.transaction(async (tx) => {
    const existingSubmission = allowMultipleSubmissions
      ? targetSubmissionId
        ? await tx.query.submissions.findFirst({
            where: (submission, { eq, and }) =>
              and(
                eq(submission.id, targetSubmissionId),
                eq(submission.formId, formId),
                eq(submission.userDiscordId, discordId),
              ),
          })
        : null
      : await tx.query.submissions.findFirst({
          where: (submission, { eq, and }) =>
            and(eq(submission.formId, formId), eq(submission.userDiscordId, discordId)),
        });

    if (allowMultipleSubmissions && targetSubmissionId && !existingSubmission) {
      throw new Error("ไม่พบคำตอบที่ต้องการแก้ไข");
    }

    let submissionId = "";
    let submittedAt = new Date();
    const isUpdate = Boolean(existingSubmission);
    let oldAnswersDict: Record<string, StoredAnswerSnapshot> = {};

    if (existingSubmission) {
      submissionId = existingSubmission.id;
      submittedAt = existingSubmission.submittedAt;

      const oldAnswersList = await tx.query.answers.findMany({
        where: (answer, { eq }) => eq(answer.submissionId, submissionId),
      });

      oldAnswersDict = oldAnswersList.reduce<Record<string, StoredAnswerSnapshot>>(
        (acc, curr) => {
          acc[curr.questionId] = {
            value: curr.value,
            imageCdnId: curr.imageCdnId ?? null,
          };
          return acc;
        },
        {},
      );

      const history = Array.isArray(existingSubmission.editHistory)
        ? [...existingSubmission.editHistory]
        : [];
      history.push({
        editedAt: new Date().toISOString(),
        oldAnswers: oldAnswersDict,
      });

      await tx
        .update(submissions)
        .set({ editHistory: history })
        .where(eq(submissions.id, submissionId));

      await tx.delete(answers).where(eq(answers.submissionId, submissionId));
    } else {
      const [inserted] = await tx
        .insert(submissions)
        .values({ formId, userDiscordId: discordId, userName })
        .returning();
      submissionId = inserted.id;
      submittedAt = inserted.submittedAt;
    }

    if (normalizedAnswers.length > 0) {
      await tx.insert(answers).values(
        normalizedAnswers.map((answer) => ({
          submissionId,
          questionId: answer.questionId,
          value: answer.value,
          imageCdnId: answer.imageCdnId,
        })),
      );
    }

    const [submissionStats] = await tx
      .select({ totalAnswers: count() })
      .from(submissions)
      .where(eq(submissions.formId, formId));
    const [queueStats] = await tx
      .select({ queueNumber: count() })
      .from(submissions)
      .where(
        and(
          eq(submissions.formId, formId),
          or(
            lt(submissions.submittedAt, submittedAt),
            and(
              eq(submissions.submittedAt, submittedAt),
              lte(submissions.id, submissionId),
            ),
          ),
        ),
      );

    return {
      submissionId,
      isUpdate,
      oldAnswersDict,
      totalAnswers: submissionStats.totalAnswers,
      queueNumber: queueStats.queueNumber,
    };
  });
}