(대표님) 20250619 1844 KST 작업사항

1 files changed, 601 insertions, 0 deletions

diff --git a/lib/esg-check-list/service.ts b/lib/esg-check-list/service.ts
new file mode 100644
index 00000000..500cd82c
--- /dev/null
+++ b/lib/esg-check-list/service.ts
@@ -0,0 +1,601 @@
+'use server'
+
+import { and, asc, desc, ilike, or } from 'drizzle-orm';
+import db  from '@/db/db';
+import { filterColumns } from "@/lib/filter-columns";
+
+
+import { 
+  esgEvaluations, 
+  esgEvaluationItems, 
+  esgAnswerOptions,
+  NewEsgEvaluation,
+  NewEsgEvaluationItem,
+  NewEsgAnswerOption,
+  EsgEvaluationWithItems,
+  esgEvaluationsView
+} from '@/db/schema';
+import { eq } from 'drizzle-orm';
+import { GetEsgEvaluationsSchema } from './validation';
+import { countEsgEvaluations, getEsgEvaluationWithDetails, selectEsgEvaluations } from './repository';
+
+// ============ 조회 함수들 ============
+
+export async function getEsgEvaluations(input: GetEsgEvaluationsSchema) {
+  try {
+    const offset = (input.page - 1) * input.perPage;
+    
+    // 고급 필터링
+    const advancedWhere = filterColumns({
+      table: esgEvaluationsView,
+      filters: input.filters,
+      joinOperator: input.joinOperator,
+    });
+
+    // 전역 검색
+    let globalWhere;
+    if (input.search) {
+      const s = `%${input.search}%`;
+      globalWhere = or(
+        ilike(esgEvaluationsView.serialNumber, s),
+        ilike(esgEvaluationsView.category, s),
+        ilike(esgEvaluationsView.inspectionItem, s)
+      );
+    }
+
+    const finalWhere = and(advancedWhere, globalWhere);
+
+    // 정렬
+    const orderBy = input.sort.length > 0
+      ? input.sort.map((item) => {
+          return item.desc
+            ? desc(esgEvaluationsView[item.id])
+            : asc(esgEvaluationsView[item.id]);
+        })
+      : [desc(esgEvaluationsView.createdAt)];
+
+    // 데이터 조회
+    const { data, total } = await db.transaction(async (tx) => {
+      const data = await selectEsgEvaluations(tx, {
+        where: finalWhere,
+        orderBy,
+        offset,
+        limit: input.perPage,
+      });
+
+      const total = await countEsgEvaluations(tx, finalWhere);
+      return { data, total };
+    });
+
+    const pageCount = Math.ceil(total / input.perPage);
+    return { data, pageCount };
+  } catch (err) {
+    console.error('Error in getEsgEvaluations:', err);
+    return { data: [], pageCount: 0 };
+  }
+}
+
+// 단일 평가표 상세 조회 (평가항목과 답변 옵션 포함)
+export async function getEsgEvaluationDetails(id: number) {
+  try {
+    return await db.transaction(async (tx) => {
+      return await getEsgEvaluationWithDetails(tx, id);
+    });
+  } catch (err) {
+    console.error('Error in getEsgEvaluationDetails:', err);
+    return null;
+  }
+}
+
+// ============ 생성 함수들 ============
+
+export async function createEsgEvaluation(data: NewEsgEvaluation) {
+  try {
+    return await db.transaction(async (tx) => {
+      const [result] = await tx
+        .insert(esgEvaluations)
+        .values(data)
+        .returning();
+      return result;
+    });
+  } catch (err) {
+    console.error('Error creating ESG evaluation:', err);
+    throw new Error('Failed to create ESG evaluation');
+  }
+}
+
+export async function createEsgEvaluationWithItems(
+  evaluationData: NewEsgEvaluation,
+  items: Array<{
+    evaluationItem: string;
+    orderIndex?: number;
+    answerOptions: Array<{
+      answerText: string;
+      score: number;
+      orderIndex?: number;
+    }>;
+  }>
+) {
+  try {
+    return await db.transaction(async (tx) => {
+      // 1. 평가표 생성
+      const [evaluation] = await tx
+        .insert(esgEvaluations)
+        .values(evaluationData)
+        .returning();
+
+      // 2. 평가항목들 생성
+      for (let i = 0; i < items.length; i++) {
+        const item = items[i];
+        const [evaluationItem] = await tx
+          .insert(esgEvaluationItems)
+          .values({
+            esgEvaluationId: evaluation.id,
+            evaluationItem: item.evaluationItem,
+            orderIndex: item.orderIndex ?? i,
+          })
+          .returning();
+
+        // 3. 답변 옵션들 생성
+        if (item.answerOptions.length > 0) {
+          await tx.insert(esgAnswerOptions).values(
+            item.answerOptions.map((option, optionIndex) => ({
+              esgEvaluationItemId: evaluationItem.id,
+              answerText: option.answerText,
+              score: option.score.toString(),
+              orderIndex: option.orderIndex ?? optionIndex,
+            }))
+          );
+        }
+      }
+
+      return evaluation;
+    });
+  } catch (err) {
+    console.error('Error creating ESG evaluation with items:', err);
+    throw new Error('Failed to create ESG evaluation with items');
+  }
+}
+
+// ============ 수정 함수들 ============
+
+export async function updateEsgEvaluation(
+  id: number,
+  data: Partial<NewEsgEvaluation>
+) {
+  try {
+    return await db.transaction(async (tx) => {
+      const [result] = await tx
+        .update(esgEvaluations)
+        .set({ ...data, updatedAt: new Date() })
+        .where(eq(esgEvaluations.id, id))
+        .returning();
+      return result;
+    });
+  } catch (err) {
+    console.error('Error updating ESG evaluation:', err);
+    throw new Error('Failed to update ESG evaluation');
+  }
+}
+
+export async function updateEsgEvaluationItem(
+  id: number,
+  data: Partial<NewEsgEvaluationItem>
+) {
+  try {
+    return await db.transaction(async (tx) => {
+      const [result] = await tx
+        .update(esgEvaluationItems)
+        .set({ ...data, updatedAt: new Date() })
+        .where(eq(esgEvaluationItems.id, id))
+        .returning();
+      return result;
+    });
+  } catch (err) {
+    console.error('Error updating ESG evaluation item:', err);
+    throw new Error('Failed to update ESG evaluation item');
+  }
+}
+
+export async function updateEsgAnswerOption(
+  id: number,
+  data: Partial<NewEsgAnswerOption>
+) {
+  try {
+    return await db.transaction(async (tx) => {
+      const [result] = await tx
+        .update(esgAnswerOptions)
+        .set({ ...data, updatedAt: new Date() })
+        .where(eq(esgAnswerOptions.id, id))
+        .returning();
+      return result;
+    });
+  } catch (err) {
+    console.error('Error updating ESG answer option:', err);
+    throw new Error('Failed to update ESG answer option');
+  }
+}
+
+// ============ 삭제 함수들 ============
+
+export async function deleteEsgEvaluation(id: number) {
+  try {
+    return await db.transaction(async (tx) => {
+      // Cascade delete가 설정되어 있어서 평가항목과 답변옵션들도 자동 삭제됨
+      const [result] = await tx
+        .delete(esgEvaluations)
+        .where(eq(esgEvaluations.id, id))
+        .returning();
+      return result;
+    });
+  } catch (err) {
+    console.error('Error deleting ESG evaluation:', err);
+    throw new Error('Failed to delete ESG evaluation');
+  }
+}
+
+export async function deleteEsgEvaluationItem(id: number) {
+  try {
+    return await db.transaction(async (tx) => {
+      // Cascade delete가 설정되어 있어서 답변옵션들도 자동 삭제됨
+      const [result] = await tx
+        .delete(esgEvaluationItems)
+        .where(eq(esgEvaluationItems.id, id))
+        .returning();
+      return result;
+    });
+  } catch (err) {
+    console.error('Error deleting ESG evaluation item:', err);
+    throw new Error('Failed to delete ESG evaluation item');
+  }
+}
+
+export async function deleteEsgAnswerOption(id: number) {
+  try {
+    return await db.transaction(async (tx) => {
+      const [result] = await tx
+        .delete(esgAnswerOptions)
+        .where(eq(esgAnswerOptions.id, id))
+        .returning();
+      return result;
+    });
+  } catch (err) {
+    console.error('Error deleting ESG answer option:', err);
+    throw new Error('Failed to delete ESG answer option');
+  }
+}
+
+// ============ 소프트 삭제 함수들 ============
+
+export async function softDeleteEsgEvaluation(id: number) {
+  return updateEsgEvaluation(id, { isActive: false });
+}
+
+export async function softDeleteEsgEvaluationItem(id: number) {
+  return updateEsgEvaluationItem(id, { isActive: false });
+}
+
+export async function softDeleteEsgAnswerOption(id: number) {
+  return updateEsgAnswerOption(id, { isActive: false });
+}
+
+
+
+export async function updateEsgEvaluationWithItems(
+  id: number,
+  evaluationData: {
+    serialNumber: string;
+    category: string;
+    inspectionItem: string;
+  },
+  items: Array<{
+    evaluationItem: string;
+    evaluationItemDescription: string;
+    answerOptions: Array<{
+      answerText: string;
+      score: number;
+    }>;
+  }>
+) {
+  try {
+    return await db.transaction(async (tx) => {
+      // 1. 기본 정보 수정
+      const [updatedEvaluation] = await tx
+        .update(esgEvaluations)
+        .set({
+          ...evaluationData,
+          updatedAt: new Date(),
+        })
+        .where(eq(esgEvaluations.id, id))
+        .returning();
+
+      // 2. 기존 평가항목들과 답변 옵션들 모두 삭제 (cascade delete로 답변옵션도 함께 삭제됨)
+      await tx
+        .delete(esgEvaluationItems)
+        .where(eq(esgEvaluationItems.esgEvaluationId, id));
+
+      // 3. 새로운 평가항목들과 답변 옵션들 생성
+      for (let i = 0; i < items.length; i++) {
+        const item = items[i];
+        
+        const [evaluationItem] = await tx
+          .insert(esgEvaluationItems)
+          .values({
+            esgEvaluationId: id,
+            evaluationItem: item.evaluationItem,
+            orderIndex: i,
+          })
+          .returning();
+
+        // 답변 옵션들 생성
+        if (item.answerOptions.length > 0) {
+          await tx.insert(esgAnswerOptions).values(
+            item.answerOptions.map((option, optionIndex) => ({
+              esgEvaluationItemId: evaluationItem.id,
+              answerText: option.answerText,
+              score: option.score.toString(),
+              orderIndex: optionIndex,
+            }))
+          );
+        }
+      }
+
+      return updatedEvaluation;
+    });
+  } catch (err) {
+    console.error('Error updating ESG evaluation with items:', err);
+    
+    // 시리얼 번호 중복 에러 처리
+    if (err instanceof Error && err.message.includes('unique')) {
+      throw new Error('이미 존재하는 시리얼번호입니다.');
+    }
+    
+    throw new Error('평가표 수정에 실패했습니다.');
+  }
+}
+
+// ============ 소프트 삭제 버전 (데이터 보존) ============
+
+export async function updateEsgEvaluationWithItemsSoft(
+  id: number,
+  evaluationData: {
+    serialNumber: string;
+    category: string;
+    inspectionItem: string;
+  },
+  items: Array<{
+    evaluationItem: string;
+    answerOptions: Array<{
+      answerText: string;
+      score: number;
+    }>;
+  }>
+) {
+  try {
+    return await db.transaction(async (tx) => {
+      // 1. 기본 정보 수정
+      const [updatedEvaluation] = await tx
+        .update(esgEvaluations)
+        .set({
+          ...evaluationData,
+          updatedAt: new Date(),
+        })
+        .where(eq(esgEvaluations.id, id))
+        .returning();
+
+      // 2. 기존 평가항목들 소프트 삭제
+      await tx
+        .update(esgEvaluationItems)
+        .set({ isActive: false, updatedAt: new Date() })
+        .where(eq(esgEvaluationItems.esgEvaluationId, id));
+
+      // 기존 답변 옵션들도 소프트 삭제
+      const existingItems = await tx
+        .select({ id: esgEvaluationItems.id })
+        .from(esgEvaluationItems)
+        .where(eq(esgEvaluationItems.esgEvaluationId, id));
+
+      for (const item of existingItems) {
+        await tx
+          .update(esgAnswerOptions)
+          .set({ isActive: false, updatedAt: new Date() })
+          .where(eq(esgAnswerOptions.esgEvaluationItemId, item.id));
+      }
+
+      // 3. 새로운 평가항목들과 답변 옵션들 생성
+      for (let i = 0; i < items.length; i++) {
+        const item = items[i];
+        
+        const [evaluationItem] = await tx
+          .insert(esgEvaluationItems)
+          .values({
+            esgEvaluationId: id,
+            evaluationItem: item.evaluationItem,
+            orderIndex: i,
+          })
+          .returning();
+
+        // 답변 옵션들 생성
+        if (item.answerOptions.length > 0) {
+          await tx.insert(esgAnswerOptions).values(
+            item.answerOptions.map((option, optionIndex) => ({
+              esgEvaluationItemId: evaluationItem.id,
+              answerText: option.answerText,
+              score: option.score.toString(),
+              orderIndex: optionIndex,
+            }))
+          );
+        }
+      }
+
+      return updatedEvaluation;
+    });
+  } catch (err) {
+    console.error('Error updating ESG evaluation with items (soft):', err);
+    
+    if (err instanceof Error && err.message.includes('unique')) {
+      throw new Error('이미 존재하는 시리얼번호입니다.');
+    }
+    
+    throw new Error('평가표 수정에 실패했습니다.');
+  }
+}
+
+// ============ 생성 함수 개선 (에러 처리 추가) ============
+
+export async function createEsgEvaluationWithItemsEnhanced(
+  evaluationData: {
+    serialNumber: string;
+    category: string;
+    inspectionItem: string;
+  },
+  items: Array<{
+    evaluationItem: string;
+    evaluationItemDescription: string;
+    answerOptions: Array<{
+      answerText: string;
+      score: number;
+    }>;
+  }>
+) {
+  try {
+    return await db.transaction(async (tx) => {
+      // 1. 평가표 생성
+      const [evaluation] = await tx
+        .insert(esgEvaluations)
+        .values(evaluationData)
+        .returning();
+
+      // 2. 평가항목들과 답변 옵션들 생성
+      for (let i = 0; i < items.length; i++) {
+        const item = items[i];
+        
+        const [evaluationItem] = await tx
+          .insert(esgEvaluationItems)
+          .values({
+            esgEvaluationId: evaluation.id,
+            evaluationItem: item.evaluationItem,
+            evaluationItemDescription: item.evaluationItemDescription,
+            orderIndex: i,
+          })
+          .returning();
+
+        // 답변 옵션들 생성
+        if (item.answerOptions.length > 0) {
+          await tx.insert(esgAnswerOptions).values(
+            item.answerOptions.map((option, optionIndex) => ({
+              esgEvaluationItemId: evaluationItem.id,
+              answerText: option.answerText,
+              score: option.score.toString(),
+              orderIndex: optionIndex,
+            }))
+          );
+        }
+      }
+
+      return evaluation;
+    });
+  } catch (err) {
+    console.error('Error creating ESG evaluation with items:', err);
+    
+    // 시리얼 번호 중복 에러 처리
+    if (err instanceof Error && err.message.includes('unique')) {
+      throw new Error('이미 존재하는 시리얼번호입니다.');
+    }
+    
+    throw new Error('평가표 생성에 실패했습니다.');
+  }
+}
+
+export async function deleteEsgEvaluationsBatch(ids: number[]) {
+  try {
+    if (ids.length === 0) {
+      throw new Error('삭제할 평가표가 없습니다.');
+    }
+
+    return await db.transaction(async (tx) => {
+      let deletedCount = 0;
+      
+      for (const id of ids) {
+        try {
+          // 각 평가표 삭제 (cascade delete로 관련 데이터도 함께 삭제됨)
+          await tx
+            .delete(esgEvaluations)
+            .where(eq(esgEvaluations.id, id));
+          
+          deletedCount++;
+        } catch (error) {
+          console.error(`Error deleting evaluation ${id}:`, error);
+          // 개별 삭제 실패는 로그만 남기고 계속 진행
+        }
+      }
+
+      return {
+        total: ids.length,
+        deleted: deletedCount,
+        failed: ids.length - deletedCount
+      };
+    });
+  } catch (err) {
+    console.error('Error in batch delete ESG evaluations:', err);
+    throw new Error('평가표 일괄 삭제에 실패했습니다.');
+  }
+}
+
+export async function softDeleteEsgEvaluationsBatch(ids: number[]) {
+  try {
+    if (ids.length === 0) {
+      throw new Error('삭제할 평가표가 없습니다.');
+    }
+
+    return await db.transaction(async (tx) => {
+      let deletedCount = 0;
+      
+      for (const id of ids) {
+        try {
+          // 평가표 소프트 삭제
+          await tx
+            .update(esgEvaluations)
+            .set({ 
+              isActive: false,
+              updatedAt: new Date(),
+            })
+            .where(eq(esgEvaluations.id, id));
+
+          // 관련 평가항목들 소프트 삭제
+          await tx
+            .update(esgEvaluationItems)
+            .set({ isActive: false, updatedAt: new Date() })
+            .where(eq(esgEvaluationItems.esgEvaluationId, id));
+
+          // 관련 답변 옵션들 소프트 삭제
+          const evaluationItems = await tx
+            .select({ id: esgEvaluationItems.id })
+            .from(esgEvaluationItems)
+            .where(eq(esgEvaluationItems.esgEvaluationId, id));
+
+          for (const item of evaluationItems) {
+            await tx
+              .update(esgAnswerOptions)
+              .set({ isActive: false, updatedAt: new Date() })
+              .where(eq(esgAnswerOptions.esgEvaluationItemId, item.id));
+          }
+          
+          deletedCount++;
+        } catch (error) {
+          console.error(`Error soft deleting evaluation ${id}:`, error);
+          // 개별 삭제 실패는 로그만 남기고 계속 진행
+        }
+      }
+
+      return {
+        total: ids.length,
+        deleted: deletedCount,
+        failed: ids.length - deletedCount
+      };
+    });
+  } catch (err) {
+    console.error('Error in batch soft delete ESG evaluations:', err);
+    throw new Error('평가표 일괄 삭제에 실패했습니다.');
+  }
+}
\ No newline at end of file