path: root/lib/risk-management/service.ts

/* eslint-disable @typescript-eslint/no-explicit-any */

'use server';

/* IMPORT */
import {
  and,
  asc,
  desc,
  eq,
  gte,
  ilike,
  isNotNull,
  lte,
  or,
} from 'drizzle-orm';
import { authOptions } from '@/app/api/auth/[...nextauth]/route';
import {
  countRisksView,
  insertRiskEvents,
  selectRiskEvents,
  selectRisksView,
  updateRiskEvents,
} from './repository';
import db from '@/db/db';
import ExcelJS from 'exceljs';
import { filterColumns } from '@/lib/filter-columns';
import { getServerSession } from 'next-auth';
import { decryptWithServerAction } from '@/components/drm/drmUtils';
import { RISK_EVENT_TYPE_LIST, RISK_PROVIDER_LIST } from '@/config/risksConfig';
import { 
  riskEvents,
  risksView,
  type RiskEvents,
  type RisksView,
  users,
  vendors,
} from '@/db/schema';
import { selectUsers } from '../admin-users/repository';
import { selectVendors } from '../vendors/repository';
import { sendEmail } from '../mail/sendEmail';
import { type GetRisksSchema } from './validations';

// ----------------------------------------------------------------------------------------------------

/* HELPER FUNCTION FOR GETTING CURRENT USER ID */
async function getCurrentUserId(): Promise<number> {
  try {
    const session = await getServerSession(authOptions);
    return session?.user?.id ? Number(session.user.id) : 3; // 기본값 3, 실제 환경에서는 적절한 기본값 설정
  } catch (error) {
    console.error('Error in Getting Current Session User ID:', error);
    return 3; // 기본값 3
  }
}

// ----------------------------------------------------------------------------------------------------

/* FUNCTION FOR GETTING RISKS */
async function getRisksView(input: GetRisksSchema) {
  try {
    const offset = (input.page - 1) * input.perPage;

    const dateWhere = and(
      gte(risksView.occuredAt, new Date(input.from)),
      lte(risksView.occuredAt, new Date(input.to)),
    );

    const advancedWhere = filterColumns({
      table: risksView,
      filters: input.filters,
      joinOperator: input.joinOperator,
    });

    // Filtering
    let globalWhere;
    if (input.search) {
      const s = `%${input.search}%`;
      globalWhere = or(
        ilike(risksView.eventType, s),
        ilike(risksView.vendorCode, s),
        ilike(risksView.vendorName, s),
        ilike(risksView.businessNumber, s),
        ilike(risksView.provider, s),
      );
    }
    const finalWhere = and(dateWhere, advancedWhere, globalWhere);

    // Sorting
    const orderBy = input.sort.length > 0
      ? input.sort.map((item) => {
          return item.desc
            ? desc(risksView[item.id])
            : asc(risksView[item.id]);
        })
      : [asc(risksView.id)];

    // Getting Data
    const { data, total } = await db.transaction(async (tx) => {
      const data = await selectRisksView(tx, {
        where: finalWhere,
        orderBy,
        offset,
        limit: input.perPage,
      });
      const total = await countRisksView(tx, finalWhere);

      return { data, total };
    });
    const pageCount = Math.ceil(total / input.perPage);
    return { data, pageCount };
  } catch (err) {
    console.error('Error in Getting Risk Data: ', err);
    return { data: [], pageCount: 0 };
  }
}

/* FUNCTION FOR GETTING RISKS VIEW BY ID */
async function getRisksViewById(id: number) {
  try {
    return await db.transaction(async (tx) => {
      return await selectRisksView(tx, {
        where: eq(risksView.id, id),
      });
    });
  } catch (err) {
    console.error('Error in Getting Risk Events by ID: ', err);
    return null;
  }
}

/* FUNCTION FOR GETTING RISK EVENTS BY ID */
async function getRiskEventsById(id: number) {
  try {
    return await db.transaction(async (tx) => {
      return await selectRiskEvents(tx, {
        where: eq(riskEvents.id, id),
      });
    });
  } catch (err) {
    console.error('Error in Getting Risk Events by ID: ', err);
    return null;
  }
}

/* FUNCTION FOR GETTING RISKS VIEW DATA COUNT */
async function getRisksViewCount(input: GetRisksSchema) {
  try {
    const dateWhere = and(
      gte(risksView.occuredAt, new Date(input.from)),
      lte(risksView.occuredAt, new Date(input.to)),
    );

    const advancedWhere = filterColumns({
      table: risksView,
      filters: input.filters,
      joinOperator: input.joinOperator,
    });

    let globalWhere;
    if (input.search) {
      const s = `%${input.search}%`;
      globalWhere = or(
        ilike(risksView.eventType, s),
        ilike(risksView.vendorCode, s),
        ilike(risksView.vendorName, s),
        ilike(risksView.businessNumber, s),
        ilike(risksView.provider, s),
      );
    }
    const statusWhere = eq(risksView.eventStatus, true);
    const finalWhere = and(dateWhere, advancedWhere, globalWhere, statusWhere);

    const { total } = await db.transaction(async (tx) => {
      const total = await countRisksView(tx, finalWhere);

      return { total };
    });

    return { count: total };
  } catch (err) {
    console.error('Error in Counting Risks: ', err);
    return { count: 0 };
  }
}

/* FUNCTION FOR GETTING PROCUREMENT MANAGER LIST */
async function getProcurementManagerList() {
  try {
    return await db.transaction(async (tx) => {
      const managerData = await selectUsers(tx, {
        where: and(eq(users.isActive, true), isNotNull(users.userCode)),
        orderBy: [asc(users.name)],
      });

      return managerData.map((user) => ({
        id: user.id,
        name: user.name,
        email: user.email,
        deptName: user.deptName,
      }));
    });
  } catch (err) {
    console.error('Error in Getting Procurement Manager List: ', err);
    return [];
  }
}

// ----------------------------------------------------------------------------------------------------

/* FUNCTION FOR MODIFYING RISK EVENTS */
async function modifyRiskEvents(
  id: number,
  riskData: Partial<RiskEvents>,
) {
  try {
    const currentUserId = await getCurrentUserId();
    
    return await db.transaction(async (tx) => {
      const modifiedRiskEvent = await updateRiskEvents(tx, id, {
        ...riskData,
        updatedAt: new Date(),
        updatedBy: currentUserId,
      });

      return { ...modifiedRiskEvent };
    });
  } catch (error) {
    console.error('Error in Modifying Risk Events: ', error);
    throw new Error('Failed to Modify Risk Events');
  }
}

// ----------------------------------------------------------------------------------------------------

/* FUNCTION FOR SENDING RISK EMAIL */
async function sendRiskEmail(
  vendorId: number,
  managerId: number,
  adminComment: string,
  selectedEventTypeMap: Record<string, RisksView[]>,
  attachment?: { filename: string; content: Buffer },
) {
  try {
    // Getting Vendor Information
    const { vendorList } = await db.transaction(async (tx) => {
      const selectRes = await selectVendors(tx, {
        where: eq(vendors.id, vendorId),
      });

      return { vendorList: selectRes };
    });
    if (vendorList.length === 0) {
      throw new Error('존재하지 않는 협력업체입니다. 다시 한 번 확인해주십시오.');
    }
    const { vendorName, vendorCode, taxId } = vendorList[0];
    const businessNumber = /^\d{10}$/.test(taxId)
      ? `${taxId.slice(0, 3)}-${taxId.slice(3, 5)}-${taxId.slice(5)}`
      : taxId;

    // Getting Procurement Manager Information
    const procurementManagerList = await db.transaction(async (tx) => {
      const managerData = await selectUsers(tx, {
        where: eq(users.id, managerId),
      });

      return managerData.map((user) => ({
        id: user.id,
        name: user.name,
        email: user.email,
        deptName: user.deptName,
      }));
    });
    if (!procurementManagerList || procurementManagerList.length === 0) {
      throw new Error('해당하는 구매 담당자가 존재하지 않습니다.');
    }
    const procurementManager = procurementManagerList[0];

    // Getting Risk Manager Information
    const riskManagerId = await getCurrentUserId();
    const riskManagerList = await db.transaction(async (tx) => {
      const managerData = await selectUsers(tx, {
        where: eq(users.id, riskManagerId),
      });

      return managerData.map((user) => ({
        id: user.id,
        name: user.name,
        email: user.email,
        deptName: user.deptName,
      }));
    });
    if (!riskManagerList || riskManagerList.length === 0) {
      throw new Error('해당하는 리스크 관리 담당자가 존재하지 않습니다.');
    }
    const riskManager = riskManagerList[0];

    // Getting Ranking Information
    const ratingList = ['종합등급', '신용등급', '현금흐름등급', 'WATCH등급'];
    const { ratingMap } = await db.transaction(async (tx) => {
      const selectResList: Record<string, string> = {};

      for (const rating of ratingList) {
        const selectRes = await selectRiskEvents(tx, {
          where: and(
            eq(riskEvents.vendorId, vendorId),
            eq(riskEvents.eventType, rating),
          ),
          orderBy: [desc(riskEvents.occuredAt)],
          limit: 1,
        });

        selectResList[rating] = selectRes.length > 0 && selectRes[0].content !== null ? selectRes[0].content : '-';
      }

      return { ratingMap: selectResList };
    });

    const riskItems = Object.entries(selectedEventTypeMap).map(([eventType, items]) => {
      const contents = items.map(item => item.content).join(', ');
      return { eventType, content: contents };
    });

    const attachments = attachment
    ? [
        {
          filename: attachment.filename,
          content: attachment.content,
        },
      ]
    : [];

    await sendEmail({
      to: procurementManager.email,
      // from: `"${riskManager.name}" <${riskManager.email}>`, // 추후 수정 필요
      from: `"${riskManager.name}" <dujin.kim@dtsolution.co.kr>`,
      subject: `[eVCP] 협력업체 리스크 알림 및 관리 요청 - ${vendorName}(${vendorCode}, 사업자등록번호: ${businessNumber})`,
      template: 'risks-notification',
      context: {
        adminComment,
        vendorName,
        vendorCode,
        businessNumber,
        ratingTotal: ratingMap['종합등급'],
        ratingCredit: ratingMap['신용등급'],
        ratingCashflow: ratingMap['현금흐름등급'],
        ratingWatch: ratingMap['WATCH등급'],
        riskItems,
        senderName: riskManager.name,
        senderEmail: riskManager.email,
        currentYear: new Date().getFullYear(),
      },
      attachments,
    });
  } catch (error) {
    console.error('Error in Sending Risk Email: ', error);
    throw new Error('Failed to Send Risk Email');
  }
}

// ----------------------------------------------------------------------------------------------------

/* FUNCTION FOR GENERATING EXCEL TEMPLATE */
async function generateRiskEventsTemplate(): Promise<ArrayBuffer> {
  try {
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('협력업체 리스크 입력 템플릿');
    workbook.creator = 'eVCP System';
    workbook.created = new Date();

    const templateHeaders = [
      { key: 'eventType', header: '항목', width: 30 },
      { key: 'vendorName', header: '협력업체명', width: 30 },
      { key: 'businessNumber', header: '사업자등록번호', width: 30 },
      { key: 'provider', header: '신용평가사', width: 30 },
      { key: 'content', header: '상세 내용', width: 50 },
      { key: 'occuredAt', header: '발생일자', width: 50 },
    ];

    worksheet.columns = templateHeaders;

    const headerRow = worksheet.getRow(1);
    headerRow.font = { bold: true };
    headerRow.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFCCCCCC' },
    };
    headerRow.alignment = { horizontal: 'center', vertical: 'middle' };

    const exampleData = [
      {
        eventType: '리스크 항목을 입력하십시오.',
        vendorName: '협력업체명을 입력하십시오.',
        businessNumber: '사업자등록번호를 입력하십시오.',
        provider: '신용평가사를 입력하십시오.',
        content: '상세 내용을 입력하십시오.',
        occuredAt: '발생일자를 YYYY-MM-DD형식으로 입력하십시오.'
      },
    ];

    exampleData.forEach((row) => {
      worksheet.addRow(row);
    });

    const validationSheet = workbook.addWorksheet('ValidationData');
    validationSheet.state = 'hidden';
    validationSheet.getColumn(1).values = ['리스크 항목', ...RISK_EVENT_TYPE_LIST];
    validationSheet.getColumn(2).values = ['신용평가사', ...RISK_PROVIDER_LIST];

    const eventTypeColIndex = templateHeaders.findIndex(col => col.key === 'eventType') + 1;
    const providerColIndex = templateHeaders.findIndex(col => col.key === 'provider') + 1;

    if (eventTypeColIndex > 0) {
      (worksheet as any).dataValidations.add(`${worksheet.getColumn(eventTypeColIndex).letter}2:${worksheet.getColumn(eventTypeColIndex).letter}1000`, {
        type: 'list',
        allowBlank: false,
        formulae: [`ValidationData!$A$2:$A$${RISK_EVENT_TYPE_LIST.length + 1}`],
      });
    }

    if (providerColIndex > 0) {
      (worksheet as any).dataValidations.add(`${worksheet.getColumn(providerColIndex).letter}2:${worksheet.getColumn(providerColIndex).letter}1000`, {
        type: 'list',
        allowBlank: false,
        formulae: [`ValidationData!$B$2:$B$${RISK_PROVIDER_LIST.length + 1}`],
      });
    }

    worksheet.eachRow((row) => {
      row.eachCell((cell) => {
        cell.border = {
          top: { style: 'thin' },
          left: { style: 'thin' },
          bottom: { style: 'thin' },
          right: { style: 'thin' },
        };
      });
    });

    return await workbook.xlsx.writeBuffer() as ArrayBuffer;
  } catch (error) {
    console.error('Error in generating Excel template:', error);
    throw new Error('Failed to generate Excel template');
  }
}

/* FUNCTION FOR IMPORTING EXCEL FILE */
async function importRiskEventsExcel(file: File): Promise<{
  errorFile?: Blob;
  errorMessage?: string;
  successMessage?: string;
}> {
  try {
    const arrayBuffer = await decryptWithServerAction(file);
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.load(arrayBuffer);
    
    const worksheet = workbook.getWorksheet(1);
    if (!worksheet) {
      return { errorMessage: '워크시트를 찾을 수 없습니다.' };
    }
    const errors: string[] = [];
    const importDataList: {
      eventType: string;
      vendorId: number;
      vendorName: string;
      businessNumber: string;
      provider: string;
      content: string;
      occuredAt: string;
    }[] = [];

    const rows = worksheet.getRows(2, worksheet.rowCount - 1);

    if (!rows) {
      return { errorMessage: '새로 추가할 리스크 데이터가 존재하지 않습니다.' };
    }

    for (const [index, row] of rows.entries()) {
      const rowIndex = index + 2;
      try {
        const rowData = {
          eventType: row.getCell(1).value?.toString()?.trim() || '',
          vendorId: 0,
          vendorName: row.getCell(2).value?.toString()?.trim() || '',
          businessNumber: row.getCell(3).value?.toString()?.trim() || '',
          provider: row.getCell(4).value?.toString()?.trim() || '',
          content: row.getCell(5).value?.toString()?.trim() || '',
          occuredAt : '',
        };

        let occuredAtRaw = row.getCell(6).value;
        let occuredAt = '';

        if (occuredAtRaw instanceof Date) {
          const year = occuredAtRaw.getFullYear();
          const month = String(occuredAtRaw.getMonth() + 1).padStart(2, '0');
          const day = String(occuredAtRaw.getDate()).padStart(2, '0');
          occuredAt = `${year}-${month}-${day}`;
        } else if (typeof occuredAtRaw === 'number') {
          const excelEpoch = new Date(Date.UTC(1899, 11, 30));
          const dateObj = new Date(excelEpoch.getTime() + occuredAtRaw * 86400 * 1000);
          const year = dateObj.getUTCFullYear();
          const month = String(dateObj.getUTCMonth() + 1).padStart(2, '0');
          const day = String(dateObj.getUTCDate()).padStart(2, '0');
          occuredAt = `${year}-${month}-${day}`;
        } else if (typeof occuredAtRaw === 'string') {
          occuredAt = occuredAtRaw.trim();
        }

        rowData.occuredAt = occuredAt || '';

        if (!rowData.eventType && !rowData.vendorName && !rowData.businessNumber && !rowData.provider && !rowData.occuredAt) {
          continue;
        }

        if (!rowData.eventType || !rowData.vendorName || !rowData.businessNumber || !rowData.provider || !rowData.occuredAt) {
          errors.push(`행 ${rowIndex}: 필수 필드(항목, 협력업체명, 사업자등록번호, 신용평가사, 발생일자)가 누락되었습니다.`);
          continue;
        }

        rowData.businessNumber = rowData.businessNumber.replace(/\D/g, '');

        if (rowData.businessNumber.length !== 10) {
          errors.push(`행 ${rowIndex}: 사업자등록번호는 숫자 10자리여야 합니다.`);
          continue;
        }

        const datePattern = /^\d{4}-\d{2}-\d{2}$/;
        if (!datePattern.test(rowData.occuredAt)) {
          errors.push(`행 ${rowIndex}: 발생일자는 YYYY-MM-DD 형식이어야 합니다.`);
          continue;
        }

        const dateObj = new Date(rowData.occuredAt);
        const [year, month, day] = rowData.occuredAt.split('-').map(Number);
        if (
          dateObj.getFullYear() !== year ||
          dateObj.getMonth() + 1 !== month ||
          dateObj.getDate() !== day
        ) {
          errors.push(`행 ${rowIndex}: 발생일자가 올바른 날짜가 아닙니다.`);
          continue;
        }

        const { vendorList } = await db.transaction(async (tx) => {
          const selectRes = await selectVendors(tx, {
            where: eq(vendors.taxId, rowData.businessNumber),
          });

          return { vendorList: selectRes };
        });

        if (vendorList.length === 0) {
          errors.push(`행 ${rowIndex}: 협력업체로 등록되지 않은 사업자등록번호입니다. 다시 한 번 확인해주십시오.`);
          continue;
        }

        rowData.vendorId = vendorList[0].id;
        rowData.vendorName = vendorList[0].vendorName;
        importDataList.push(rowData);

      } catch (error) {
        errors.push(`행 ${rowIndex}: 데이터 처리 중 오류가 발생했습니다 - ${error}`);
      }
    }

    if (errors.length > 0) {
      const errorWorkbook = new ExcelJS.Workbook();
      const errorWorksheet = errorWorkbook.addWorksheet('Import Errors');
      
      errorWorksheet.columns = [
        { header: '오류 내용', key: 'error', width: 80 },
      ];

      errorWorksheet.getRow(1).font = { bold: true };
      errorWorksheet.getRow(1).fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFFF0000' }
      };

      errors.forEach(error => {
        errorWorksheet.addRow({ error });
      });

      const buffer = await errorWorkbook.xlsx.writeBuffer();
      const errorFile = new Blob([buffer], { 
        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' 
      });
      
      return { 
        errorFile,
        errorMessage: `${errors.length}개의 오류가 발견되었습니다. 오류 파일을 확인하십시오.` 
      };
    }

    if (importDataList.length === 0) {
      return { errorMessage: '새로 추가할 리스크 데이터가 존재하지 않습니다. 파일을 다시 한 번 확인해주십시오.' };
    }

    const currentUserId = await getCurrentUserId();
    
    for (const importData of importDataList) {    
      const { vendorId, eventType, provider, content, occuredAt } = importData;
      await db.transaction(async (tx) => {
        await insertRiskEvents(tx, {
          vendorId,
          provider,
          eventType,
          content,
          occuredAt: new Date(occuredAt),
          createdBy: currentUserId,
          updatedBy: currentUserId,
        });
      });
    }

    return { successMessage: `Excel 파일이 성공적으로 업로드되었습니다. ${importDataList.length}개의 리스크 이벤트가 추가되었습니다.` };
  } catch (error) {
    console.error('Error in Importing Regular Evaluation Criteria from Excel:', error);
    return { errorMessage: 'Excel 파일 처리 중 오류가 발생했습니다.' };
  }
}

// ----------------------------------------------------------------------------------------------------

/* EXPORT */
export {
  getProcurementManagerList,
  getRisksView,
  getRisksViewById,
  getRisksViewCount,
  getRiskEventsById,
  generateRiskEventsTemplate,
  importRiskEventsExcel,
  modifyRiskEvents,
  sendRiskEmail,
};