1 files changed, 142 insertions, 0 deletions
diff --git a/lib/compliance/red-flag-notifier.ts b/lib/compliance/red-flag-notifier.ts
new file mode 100644
index 00000000..ad55de54
--- /dev/null
+++ b/lib/compliance/red-flag-notifier.ts
@@ -0,0 +1,142 @@
+import db from '@/db/db';
+import {
+  basicContract,
+  complianceQuestions,
+  complianceResponseAnswers,
+  complianceResponses,
+  redFlagManagers,
+  users,
+} from '@/db/schema';
+import { and, desc, eq } from 'drizzle-orm';
+import { sendEmail } from '@/lib/mail/sendEmail';
+
+export type TriggeredRedFlagInfo = {
+  questionId: number;
+  questionNumber: string;
+  questionText: string;
+  answerValue: string;
+};
+
+export async function getTriggeredRedFlagQuestions(
+  contractId: number,
+): Promise<TriggeredRedFlagInfo[]> {
+  const rows = await db
+    .select({
+      questionId: complianceResponseAnswers.questionId,
+      answerValue: complianceResponseAnswers.answerValue,
+      questionNumber: complianceQuestions.questionNumber,
+      questionText: complianceQuestions.questionText,
+    })
+    .from(complianceResponses)
+    .innerJoin(
+      complianceResponseAnswers,
+      eq(complianceResponseAnswers.responseId, complianceResponses.id),
+    )
+    .innerJoin(
+      complianceQuestions,
+      eq(complianceQuestions.id, complianceResponseAnswers.questionId),
+    )
+    .where(
+      and(
+        eq(complianceResponses.basicContractId, contractId),
+        eq(complianceQuestions.isRedFlag, true),
+      ),
+    );
+
+  return rows
+    .filter(
+      (row) => (row.answerValue ?? '').toString().trim().toUpperCase() === 'YES',
+    )
+    .map((row) => ({
+      questionId: row.questionId,
+      questionNumber: row.questionNumber ?? '',
+      questionText: row.questionText ?? '',
+      answerValue: 'YES',
+    }));
+}
+
+export async function notifyComplianceRedFlagManagers(params: {
+  contractId: number;
+  templateId?: number | null;
+  vendorName?: string | null;
+  triggeredQuestions: TriggeredRedFlagInfo[];
+}) {
+  if (!params.triggeredQuestions.length) {
+    return;
+  }
+
+  const recipientEmails = new Set<string>();
+
+  const managerRow = await db
+    .select({
+      purchasingManagerId: redFlagManagers.purchasingManagerId,
+      complianceManagerId: redFlagManagers.complianceManagerId,
+    })
+    .from(redFlagManagers)
+    .orderBy(desc(redFlagManagers.createdAt))
+    .limit(1);
+
+  const managerIds = managerRow[0];
+
+  const fetchUserEmail = async (userId?: number | null) => {
+    if (!userId) {
+      return null;
+    }
+    const rows = await db
+      .select({ email: users.email })
+      .from(users)
+      .where(eq(users.id, userId))
+      .limit(1);
+    return rows[0]?.email ?? null;
+  };
+
+  const [purchasingEmail, complianceEmail] = await Promise.all([
+    fetchUserEmail(managerIds?.purchasingManagerId),
+    fetchUserEmail(managerIds?.complianceManagerId),
+  ]);
+
+  if (purchasingEmail) {
+    recipientEmails.add(purchasingEmail);
+  }
+
+  if (complianceEmail) {
+    recipientEmails.add(complianceEmail);
+  }
+
+  const contractOwner = await db
+    .select({
+      requestedBy: basicContract.requestedBy,
+      requestedByEmail: users.email,
+    })
+    .from(basicContract)
+    .leftJoin(users, eq(basicContract.requestedBy, users.id))
+    .where(eq(basicContract.id, params.contractId))
+    .limit(1);
+
+  const ownerRecord = contractOwner[0];
+  if (ownerRecord?.requestedByEmail) {
+    recipientEmails.add(ownerRecord.requestedByEmail);
+  }
+
+  if (recipientEmails.size === 0) {
+    return;
+  }
+
+  const context = {
+    contractId: params.contractId,
+    templateId: params.templateId ?? null,
+    vendorName: params.vendorName ?? '협력업체',
+    triggeredCount: params.triggeredQuestions.length,
+  };
+
+  await Promise.all(
+    Array.from(recipientEmails).map((email) =>
+      sendEmail({
+        to: email,
+        subject: '[eVCP] 컴플라이언스 레드플래그 알림',
+        template: 'compliance-red-flag-alert',
+        context,
+      }),
+    ),
+  );
+}