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,
      }),
    ),
  );
}