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';
import { getComplianceResponseByBasicContractId } from '@/lib/compliance/services';

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

  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];
  const contractRequestorEmail = ownerRecord?.requestedByEmail;

  // 구매 담당자가 없으면 발송하지 않음
  if (!purchasingEmail) {
    return;
  }

  // 준법설문 응답 정보 조회하여 링크 생성
  const complianceResponse = await getComplianceResponseByBasicContractId(params.contractId);
  let responseLink: string | null = null;
  
  if (complianceResponse && complianceResponse.templateId && complianceResponse.id) {
    const baseUrl = process.env.NEXT_PUBLIC_APP_URL || process.env.NEXT_PUBLIC_BASE_URL || '';
    responseLink = `${baseUrl}/ko/evcp/compliance/${complianceResponse.templateId}/responses/${complianceResponse.id}`;
  }

  const context = {
    contractId: params.contractId,
    templateId: params.templateId ?? null,
    vendorName: params.vendorName ?? '협력업체',
    triggeredCount: params.triggeredQuestions.length,
    responseLink: responseLink,
  };

  // CC 이메일 배열 구성
  const ccEmails: string[] = [];
  if (complianceEmail) {
    ccEmails.push(complianceEmail);
  }
  if (contractRequestorEmail) {
    ccEmails.push(contractRequestorEmail);
  }

  // 구매 담당자에게 To로, 준법 담당자와 계약 요청자는 CC로 발송
  await sendEmail({
    to: purchasingEmail,
    cc: ccEmails.length > 0 ? ccEmails : undefined,
    subject: '[eVCP] 컴플라이언스 레드플래그 알림',
    template: 'compliance-red-flag-alert',
    context,
  });
}