1 files changed, 899 insertions, 0 deletions
diff --git a/lib/compliance/services.ts b/lib/compliance/services.ts
new file mode 100644
index 00000000..03fae071
--- /dev/null
+++ b/lib/compliance/services.ts
@@ -0,0 +1,899 @@
+'use server'
+
+import db from "@/db/db";
+import { eq, desc, count, and, ne, or, ilike, asc } from "drizzle-orm";
+import { revalidatePath } from "next/cache";
+import {
+  complianceSurveyTemplates,
+  complianceQuestions,
+  complianceQuestionOptions,
+  complianceResponses,
+  complianceResponseAnswers,
+  complianceResponseFiles,
+} from "@/db/schema/compliance";
+import { users } from "@/db/schema";
+import { basicContract, basicContractTemplates } from "@/db/schema/basicContractDocumnet";
+import { vendors } from "@/db/schema/vendors";
+
+// 설문조사 템플릿 목록 조회 (페이지네이션 포함)
+export async function getComplianceSurveyTemplatesWithPagination() {
+  try {
+
+    
+    const templates = await db
+      .select()
+      .from(complianceSurveyTemplates)
+      .where(eq(complianceSurveyTemplates.isActive, true))
+      .orderBy(desc(complianceSurveyTemplates.createdAt));
+
+  
+
+    return {
+      data: templates,
+      pageCount: Math.ceil(templates.length / 10)
+    };
+  } catch (error) {
+    console.error("Error fetching compliance survey templates:", error);
+    throw error;
+  }
+}
+
+// 정렬 기능이 포함된 설문조사 템플릿 목록 조회
+export async function getComplianceSurveyTemplatesWithSorting(sort?: { id: string; desc: boolean }[]) {
+  try {
+  
+    
+    // 정렬 설정
+    let orderBy = [desc(complianceSurveyTemplates.createdAt)];
+    
+    if (sort && sort.length > 0) {
+      const validSortFields = ['id', 'name', 'description', 'version', 'isActive', 'createdAt', 'updatedAt'];
+      const validSorts = sort.filter(item => validSortFields.includes(item.id));
+      
+      if (validSorts.length > 0) {
+        orderBy = validSorts.map((item) => {
+          switch (item.id) {
+            case 'id':
+              return item.desc ? desc(complianceSurveyTemplates.id) : asc(complianceSurveyTemplates.id);
+            case 'name':
+              return item.desc ? desc(complianceSurveyTemplates.name) : asc(complianceSurveyTemplates.name);
+            case 'description':
+              return item.desc ? desc(complianceSurveyTemplates.description) : asc(complianceSurveyTemplates.description);
+            case 'version':
+              return item.desc ? desc(complianceSurveyTemplates.version) : asc(complianceSurveyTemplates.version);
+            case 'isActive':
+              return item.desc ? desc(complianceSurveyTemplates.isActive) : asc(complianceSurveyTemplates.isActive);
+            case 'createdAt':
+              return item.desc ? desc(complianceSurveyTemplates.createdAt) : asc(complianceSurveyTemplates.createdAt);
+            case 'updatedAt':
+              return item.desc ? desc(complianceSurveyTemplates.updatedAt) : asc(complianceSurveyTemplates.updatedAt);
+            default:
+              return desc(complianceSurveyTemplates.createdAt);
+          }
+        });
+      }
+    }
+    
+    const templates = await db
+      .select()
+      .from(complianceSurveyTemplates)
+      .where(eq(complianceSurveyTemplates.isActive, true))
+      .orderBy(...orderBy);
+    return {
+      data: templates,
+      pageCount: Math.ceil(templates.length / 10)
+    };
+  } catch (error) {
+    console.error("Error fetching compliance survey templates with sorting:", error);
+    throw error;
+  }
+}
+
+// items 서비스와 동일한 구조의 함수 추가
+export async function getComplianceSurveyTemplates(input: {
+  page: number;
+  perPage: number;
+  search?: string;
+  filters?: Array<{ id: string; value: string }>;
+  joinOperator?: 'and' | 'or';
+  sort?: { id: string; desc: boolean }[];
+}) {
+  try {
+    const safePerPage = Math.min(input.perPage, 100);
+    const offset = (input.page - 1) * safePerPage;
+    
+    let whereClause = eq(complianceSurveyTemplates.isActive, true);
+    
+    // 검색 기능
+    if (input.search) {
+      const searchTerm = `%${input.search}%`;
+      whereClause = and(
+        eq(complianceSurveyTemplates.isActive, true),
+        or(
+          ilike(complianceSurveyTemplates.name, searchTerm),
+          ilike(complianceSurveyTemplates.description, searchTerm),
+          ilike(complianceSurveyTemplates.version, searchTerm)
+        )
+      )!;
+    }
+    
+    // 정렬 - 안전한 방식으로 처리
+    let orderBy = [desc(complianceSurveyTemplates.createdAt)];
+    
+    if (input.sort && input.sort.length > 0) {
+      const validSortFields = ['id', 'name', 'description', 'version', 'isActive', 'createdAt', 'updatedAt'];
+      const validSorts = input.sort.filter(item => validSortFields.includes(item.id));
+      
+      if (validSorts.length > 0) {
+        orderBy = validSorts.map((item) => {
+          switch (item.id) {
+            case 'id':
+              return item.desc ? desc(complianceSurveyTemplates.id) : asc(complianceSurveyTemplates.id);
+            case 'name':
+              return item.desc ? desc(complianceSurveyTemplates.name) : asc(complianceSurveyTemplates.name);
+            case 'description':
+              return item.desc ? desc(complianceSurveyTemplates.description) : asc(complianceSurveyTemplates.description);
+            case 'version':
+              return item.desc ? desc(complianceSurveyTemplates.version) : asc(complianceSurveyTemplates.version);
+            case 'isActive':
+              return item.desc ? desc(complianceSurveyTemplates.isActive) : asc(complianceSurveyTemplates.isActive);
+            case 'createdAt':
+              return item.desc ? desc(complianceSurveyTemplates.createdAt) : asc(complianceSurveyTemplates.createdAt);
+            case 'updatedAt':
+              return item.desc ? desc(complianceSurveyTemplates.updatedAt) : asc(complianceSurveyTemplates.updatedAt);
+            default:
+              return desc(complianceSurveyTemplates.createdAt);
+          }
+        });
+      }
+    }
+
+    const templates = await db
+      .select()
+      .from(complianceSurveyTemplates)
+      .where(whereClause)
+      .orderBy(...orderBy)
+      .limit(safePerPage)
+      .offset(offset);
+
+    const totalCount = await db
+      .select({ count: count() })
+      .from(complianceSurveyTemplates)
+      .where(whereClause);
+
+    const total = totalCount[0]?.count || 0;
+    const pageCount = Math.ceil(total / safePerPage);
+
+    return { data: templates, pageCount };
+  } catch (error) {
+    console.error("Error fetching compliance survey templates:", error);
+    return { data: [], pageCount: 0 };
+  }
+}
+
+// 특정 템플릿 조회
+export async function getComplianceSurveyTemplate(templateId: number) {
+  try {
+    const [template] = await db
+      .select()
+      .from(complianceSurveyTemplates)
+      .where(eq(complianceSurveyTemplates.id, templateId));
+
+    return template;
+  } catch (error) {
+    console.error("Error fetching compliance survey template:", error);
+    throw error;
+  }
+}
+
+// 템플릿 수정
+export async function updateComplianceSurveyTemplate(templateId: number, data: {
+  name?: string;
+  description?: string;
+  version?: string;
+  isActive?: boolean;
+}) {
+  try {
+    const [template] = await db
+      .update(complianceSurveyTemplates)
+      .set({
+        ...data,
+        updatedAt: new Date(),
+      })
+      .where(eq(complianceSurveyTemplates.id, templateId))
+      .returning();
+
+    revalidatePath('/evcp/compliance');
+    return template;
+  } catch (error) {
+    console.error("Error updating compliance survey template:", error);
+    throw error;
+  }
+}
+
+// 템플릿의 질문들 조회
+export async function getComplianceQuestions(templateId: number) {
+  try {
+    const questions = await db
+      .select()
+      .from(complianceQuestions)
+      .where(eq(complianceQuestions.templateId, templateId))
+      .orderBy(complianceQuestions.displayOrder);
+
+    return questions;
+  } catch (error) {
+    console.error("Error fetching compliance questions:", error);
+    throw error;
+  }
+}
+
+// 질문의 옵션들 조회
+export async function getComplianceQuestionOptions(questionId: number) {
+  try {
+    const options = await db
+      .select()
+      .from(complianceQuestionOptions)
+      .where(eq(complianceQuestionOptions.questionId, questionId))
+      .orderBy(complianceQuestionOptions.displayOrder);
+
+    return options;
+  } catch (error) {
+    console.error("Error fetching compliance question options:", error);
+    throw error;
+  }
+}
+
+// 선택 가능한 부모 질문들 조회 (조건부 질문용)
+export async function getSelectableParentQuestions(templateId: number, excludeQuestionId?: number) {
+  try {
+    const questions = await db
+      .select({
+        id: complianceQuestions.id,
+        questionNumber: complianceQuestions.questionNumber,
+        questionText: complianceQuestions.questionText,
+        questionType: complianceQuestions.questionType,
+      })
+      .from(complianceQuestions)
+      .where(
+        and(
+          eq(complianceQuestions.templateId, templateId),
+          or(
+            eq(complianceQuestions.questionType, 'RADIO'),
+            eq(complianceQuestions.questionType, 'CHECKBOX'),
+            eq(complianceQuestions.questionType, 'DROPDOWN')
+          ),
+          excludeQuestionId ? ne(complianceQuestions.id, excludeQuestionId) : undefined
+        )
+      )
+      .orderBy(complianceQuestions.displayOrder);
+
+    return questions;
+  } catch (error) {
+    console.error("Error fetching selectable parent questions:", error);
+    throw error;
+  }
+}
+
+// 템플릿의 질문 개수 조회
+export async function getComplianceQuestionsCount(templateId: number) {
+  try {
+    const [result] = await db
+      .select({ count: count() })
+      .from(complianceQuestions)
+      .where(eq(complianceQuestions.templateId, templateId));
+
+    return Number(result?.count || 0);
+  } catch (error) {
+    console.error("Error fetching compliance questions count:", error);
+    return 0;
+  }
+}
+
+// 질문번호 중복 여부 확인 (템플릿 내 고유)
+export async function isQuestionNumberDuplicated(
+  templateId: number,
+  questionNumber: string,
+  excludeQuestionId?: number
+) {
+  const whereClause = excludeQuestionId
+    ? and(
+        eq(complianceQuestions.templateId, templateId),
+        eq(complianceQuestions.questionNumber, questionNumber),
+        ne(complianceQuestions.id, excludeQuestionId)
+      )
+    : and(
+        eq(complianceQuestions.templateId, templateId),
+        eq(complianceQuestions.questionNumber, questionNumber)
+      );
+
+  const [row] = await db
+    .select({ count: count() })
+    .from(complianceQuestions)
+    .where(whereClause);
+
+  return Number(row?.count ?? 0) > 0;
+}
+
+// 새로운 질문 생성
+export async function createComplianceQuestion(data: {
+  templateId: number;
+  questionNumber: string;
+  questionText: string;
+  questionType: string;
+  isRequired: boolean;
+  hasDetailText: boolean;
+  hasFileUpload: boolean;
+  displayOrder: number;
+  parentQuestionId?: number | null;
+  conditionalValue?: string;
+}) {
+  try {
+    // 중복 검사 (템플릿 내 질문번호 고유)
+    const duplicated = await isQuestionNumberDuplicated(
+      data.templateId,
+      data.questionNumber
+    );
+    if (duplicated) {
+      const error = new Error("DUPLICATE_QUESTION_NUMBER");
+      throw error;
+    }
+
+    const [question] = await db
+      .insert(complianceQuestions)
+      .values(data)
+      .returning();
+
+    return question;
+  } catch (error) {
+    console.error("Error creating compliance question:", error);
+    throw error;
+  }
+}
+
+// 질문 수정
+export async function updateComplianceQuestion(questionId: number, data: {
+  questionNumber?: string;
+  questionText?: string;
+  questionType?: string;
+  isRequired?: boolean;
+  hasDetailText?: boolean;
+  hasFileUpload?: boolean;
+  displayOrder?: number;
+  isConditional?: boolean;
+  parentQuestionId?: number | null;
+  conditionalValue?: string;
+}) {
+  try {
+    // 질문번호 변경 시 중복 검사
+    if (typeof data.questionNumber === 'string' && data.questionNumber.trim().length > 0) {
+      // 현재 질문의 템플릿 ID 조회
+      const [current] = await db
+        .select({ templateId: complianceQuestions.templateId })
+        .from(complianceQuestions)
+        .where(eq(complianceQuestions.id, questionId));
+
+      if (current) {
+        const duplicated = await isQuestionNumberDuplicated(
+          current.templateId,
+          data.questionNumber,
+          questionId
+        );
+        if (duplicated) {
+          const error = new Error("DUPLICATE_QUESTION_NUMBER");
+          throw error;
+        }
+      }
+    }
+
+    const [question] = await db
+      .update(complianceQuestions)
+      .set(data)
+      .where(eq(complianceQuestions.id, questionId))
+      .returning();
+
+    return question;
+  } catch (error) {
+    console.error("Error updating compliance question:", error);
+    throw error;
+  }
+}
+
+// 질문 삭제
+export async function deleteComplianceQuestion(questionId: number) {
+  try {
+    // 먼저 관련된 옵션들을 삭제
+    await db
+      .delete(complianceQuestionOptions)
+      .where(eq(complianceQuestionOptions.questionId, questionId));
+
+    // 그 다음 질문을 삭제
+    const [question] = await db
+      .delete(complianceQuestions)
+      .where(eq(complianceQuestions.id, questionId))
+      .returning();
+
+    return question;
+  } catch (error) {
+    console.error("Error deleting compliance question:", error);
+    throw error;
+  }
+}
+
+// 질문 옵션 생성
+export async function createComplianceQuestionOption(data: {
+  questionId: number;
+  optionText: string;
+  optionValue: string;
+  displayOrder: number;
+  allowsOtherInput?: boolean;
+  isCorrect?: boolean;
+}) {
+  try {
+    const [option] = await db
+      .insert(complianceQuestionOptions)
+      .values(data)
+      .returning();
+
+    return option;
+  } catch (error) {
+    console.error("Error creating compliance question option:", error);
+    throw error;
+  }
+}
+
+// 질문 옵션 수정
+export async function updateComplianceQuestionOption(optionId: number, data: {
+  optionText?: string;
+  optionValue?: string;
+  displayOrder?: number;
+  allowsOtherInput?: boolean;
+  isCorrect?: boolean;
+}) {
+  try {
+    const [option] = await db
+      .update(complianceQuestionOptions)
+      .set(data)
+      .where(eq(complianceQuestionOptions.id, optionId))
+      .returning();
+
+    return option;
+  } catch (error) {
+    console.error("Error updating compliance question option:", error);
+    throw error;
+  }
+}
+
+// 질문 옵션 삭제
+export async function deleteComplianceQuestionOption(optionId: number) {
+  try {
+    const [option] = await db
+      .delete(complianceQuestionOptions)
+      .where(eq(complianceQuestionOptions.id, optionId))
+      .returning();
+
+    return option;
+  } catch (error) {
+    console.error("Error deleting compliance question option:", error);
+    throw error;
+  }
+}
+
+// 템플릿의 응답들 조회
+export async function getComplianceResponses(templateId: number) {
+  try {
+    const responses = await db
+      .select()
+      .from(complianceResponses)
+      .where(eq(complianceResponses.templateId, templateId))
+      .orderBy(desc(complianceResponses.createdAt));
+
+    return responses;
+  } catch (error) {
+    console.error("Error fetching compliance responses:", error);
+    throw error;
+  }
+}
+
+// 템플릿의 응답들과 답변들을 함께 조회 (페이지네이션 포함)
+export async function getComplianceResponsesWithPagination(templateId: number) {
+  try {
+    const responses = await db
+      .select({
+        id: complianceResponses.id,
+        basicContractId: complianceResponses.basicContractId,
+        templateId: complianceResponses.templateId,
+        status: complianceResponses.status,
+        completedAt: complianceResponses.completedAt,
+        reviewedBy: complianceResponses.reviewedBy,
+        reviewedAt: complianceResponses.reviewedAt,
+        reviewNotes: complianceResponses.reviewNotes,
+        createdAt: complianceResponses.createdAt,
+        updatedAt: complianceResponses.updatedAt,
+        answersCount: count(complianceResponseAnswers.id),
+        reviewerName: users.name,
+        templateName: complianceSurveyTemplates.name,
+        // Vendor 정보 추가
+        vendorId: vendors.id,
+        vendorName: vendors.vendorName,
+        vendorCode: vendors.vendorCode,
+        // Basic Contract 정보 추가
+        contractName: basicContractTemplates.templateName,
+      })
+      .from(complianceResponses)
+      .leftJoin(complianceResponseAnswers, eq(complianceResponses.id, complianceResponseAnswers.responseId))
+      .leftJoin(users, eq(complianceResponses.reviewedBy, users.id))
+      .leftJoin(complianceSurveyTemplates, eq(complianceResponses.templateId, complianceSurveyTemplates.id))
+      // Basic Contract와 Vendor 정보를 위한 JOIN 추가
+      .leftJoin(basicContract, eq(complianceResponses.basicContractId, basicContract.id))
+      .leftJoin(vendors, eq(basicContract.vendorId, vendors.id))
+      .leftJoin(basicContractTemplates, eq(basicContract.templateId, basicContractTemplates.id))
+      .where(eq(complianceResponses.templateId, templateId))
+      .groupBy(complianceResponses.id, users.name, complianceSurveyTemplates.name, vendors.id, vendors.vendorName, vendors.vendorCode, basicContractTemplates.templateName)
+      .orderBy(desc(complianceResponses.createdAt));
+
+    return {
+      data: responses,
+      pageCount: Math.ceil(responses.length / 10)
+    };
+  } catch (error) {
+    console.error("Error fetching compliance responses with answers:", error);
+    throw error;
+  }
+}
+
+// 템플릿별 응답 통계 조회
+export async function getComplianceResponseStats(templateId: number) {
+  try {
+    const responses = await db
+      .select({
+        status: complianceResponses.status,
+        count: count()
+      })
+      .from(complianceResponses)
+      .where(eq(complianceResponses.templateId, templateId))
+      .groupBy(complianceResponses.status);
+
+    const stats = {
+      inProgress: 0,
+      completed: 0,
+      reviewed: 0,
+      total: 0
+    };
+
+    responses.forEach(response => {
+      const count = Number(response.count);
+      stats.total += count;
+      
+      switch (response.status) {
+        case 'IN_PROGRESS':
+          stats.inProgress = count;
+          break;
+        case 'COMPLETED':
+          stats.completed = count;
+          break;
+        case 'REVIEWED':
+          stats.reviewed = count;
+          break;
+      }
+    });
+
+    return stats;
+  } catch (error) {
+    console.error("Error fetching compliance response stats:", error);
+    return { inProgress: 0, completed: 0, reviewed: 0, total: 0 };
+  }
+}
+
+// 특정 응답 조회
+export async function getComplianceResponse(responseId: number) {
+  try {
+    const [response] = await db
+      .select({
+        id: complianceResponses.id,
+        basicContractId: complianceResponses.basicContractId,
+        templateId: complianceResponses.templateId,
+        status: complianceResponses.status,
+        completedAt: complianceResponses.completedAt,
+        reviewedBy: complianceResponses.reviewedBy,
+        reviewedAt: complianceResponses.reviewedAt,
+        reviewNotes: complianceResponses.reviewNotes,
+        createdAt: complianceResponses.createdAt,
+        updatedAt: complianceResponses.updatedAt,
+        // 검토자 정보 추가
+        reviewerName: users.name,
+        reviewerEmail: users.email,
+        // Vendor 정보 추가
+        vendorId: vendors.id,
+        vendorName: vendors.vendorName,
+        vendorCode: vendors.vendorCode,
+      })
+      .from(complianceResponses)
+      .leftJoin(users, eq(complianceResponses.reviewedBy, users.id))
+      .leftJoin(basicContract, eq(complianceResponses.basicContractId, basicContract.id))
+      .leftJoin(vendors, eq(basicContract.vendorId, vendors.id))
+      .where(eq(complianceResponses.id, responseId));
+
+    return response;
+  } catch (error) {
+    console.error("Error fetching compliance response:", error);
+    throw error;
+  }
+}
+
+// 응답의 답변들 조회
+export async function getComplianceResponseAnswers(responseId: number) {
+  try {
+    const answers = await db
+      .select()
+      .from(complianceResponseAnswers)
+      .where(eq(complianceResponseAnswers.responseId, responseId));
+
+    return answers;
+  } catch (error) {
+    console.error("Error fetching compliance response answers:", error);
+    throw error;
+  }
+}
+
+// 답변의 첨부파일들 조회
+export async function getComplianceResponseFiles(answerId: number) {
+  try {
+    const files = await db
+      .select()
+      .from(complianceResponseFiles)
+      .where(eq(complianceResponseFiles.answerId, answerId));
+
+    return files;
+  } catch (error) {
+    console.error("Error fetching compliance response files:", error);
+    throw error;
+  }
+}
+
+// 응답의 모든 첨부파일들 조회 (responseId로)
+export async function getComplianceResponseFilesByResponseId(responseId: number) {
+  try {
+    const files = await db
+      .select({
+        id: complianceResponseFiles.id,
+        answerId: complianceResponseFiles.answerId,
+        fileName: complianceResponseFiles.fileName,
+        filePath: complianceResponseFiles.filePath,
+        fileSize: complianceResponseFiles.fileSize,
+        mimeType: complianceResponseFiles.mimeType,
+        uploadedAt: complianceResponseFiles.uploadedAt,
+      })
+      .from(complianceResponseFiles)
+      .innerJoin(complianceResponseAnswers, eq(complianceResponseFiles.answerId, complianceResponseAnswers.id))
+      .where(eq(complianceResponseAnswers.responseId, responseId));
+
+    return files;
+  } catch (error) {
+    console.error("Error fetching compliance response files by responseId:", error);
+    throw error;
+  }
+}
+
+// 기본계약별 응답 조회
+export async function getComplianceResponseByContract(basicContractId: number) {
+  try {
+    const [response] = await db
+      .select()
+      .from(complianceResponses)
+      .where(eq(complianceResponses.basicContractId, basicContractId));
+
+    return response;
+  } catch (error) {
+    console.error("Error fetching compliance response by contract:", error);
+    throw error;
+  }
+}
+
+// 설문조사 응답 생성
+export async function createComplianceResponse(data: {
+  basicContractId: number;
+  templateId: number;
+  status?: string;
+}) {
+  try {
+    const [response] = await db
+      .insert(complianceResponses)
+      .values({
+        basicContractId: data.basicContractId,
+        templateId: data.templateId,
+        status: data.status || 'IN_PROGRESS',
+      })
+      .returning();
+
+    return response;
+  } catch (error) {
+    console.error("Error creating compliance response:", error);
+    throw error;
+  }
+}
+
+// 답변 저장
+export async function saveComplianceResponseAnswer(data: {
+  responseId: number;
+  questionId: number;
+  answerValue?: string;
+  detailText?: string;
+  otherText?: string;
+  percentageValue?: string;
+}) {
+  try {
+    const [answer] = await db
+      .insert(complianceResponseAnswers)
+      .values(data)
+      .returning();
+
+    return answer;
+  } catch (error) {
+    console.error("Error saving compliance response answer:", error);
+    throw error;
+  }
+}
+
+// 응답 상태 업데이트
+export async function updateComplianceResponseStatus(responseId: number, status: string) {
+  try {
+    const [response] = await db
+      .update(complianceResponses)
+      .set({ status })
+      .where(eq(complianceResponses.id, responseId))
+      .returning();
+
+    return response;
+  } catch (error) {
+    console.error("Error updating compliance response status:", error);
+    throw error;
+  }
+}
+
+// 설문조사 템플릿 생성
+export async function createComplianceSurveyTemplate(data: {
+  name: string;
+  description: string;
+  version: string;
+  isActive?: boolean;
+}) {
+  try {
+    const [template] = await db
+      .insert(complianceSurveyTemplates)
+      .values({
+        name: data.name,
+        description: data.description,
+        version: data.version,
+        isActive: data.isActive ?? true,
+      })
+      .returning();
+
+    return template;
+  } catch (error) {
+    console.error("Error creating compliance survey template:", error);
+    throw error;
+  }
+}
+
+// 서버 액션: 템플릿 생성
+export async function createTemplateAction(formData: FormData) {
+  try {
+  
+
+
+  
+
+    const name = formData.get("name") as string
+    const description = formData.get("description") as string
+    const version = formData.get("version") as string
+    const isActive = formData.get("isActive") === "true"
+
+    
+
+    // 필수 필드 검증
+    if (!name || !description || !version) {
+      return { error: "필수 필드가 누락되었습니다." }
+    }
+
+    // 템플릿 생성
+    await createComplianceSurveyTemplate({
+      name,
+      description,
+      version,
+      isActive,
+    })
+
+    // 페이지 캐시 무효화
+    revalidatePath("/evcp/compliance")
+
+    return { success: true }
+  } catch (error) {
+    console.error("Error creating template:", error)
+    return { error: "템플릿 생성 중 오류가 발생했습니다." }
+  }
+}
+
+// 설문조사 템플릿 삭제 (비활성화)
+export async function deleteComplianceSurveyTemplate(templateId: number) {
+  try {
+  
+    const [template] = await db
+      .update(complianceSurveyTemplates)
+      .set({ 
+        isActive: false,
+        updatedAt: new Date()
+      })
+      .where(eq(complianceSurveyTemplates.id, templateId))
+      .returning();
+
+    console.log(`✅ 템플릿 ${templateId} 삭제 완료:`, template);
+    
+    // 캐시 무효화
+    revalidatePath("/evcp/compliance");
+    
+    return template;
+  } catch (error) {
+    console.error("Error deleting compliance survey template:", error);
+    throw error;
+  }
+}
+
+// 서버 액션: 템플릿 삭제
+export async function deleteTemplateAction(templateId: number) {
+  try {
+    await deleteComplianceSurveyTemplate(templateId);
+    revalidatePath("/evcp/compliance");
+    return { success: true };
+  } catch (error) {
+    console.error("Error deleting template:", error);
+    return { error: "템플릿 삭제 중 오류가 발생했습니다." };
+  }
+}
+
+// 템플릿의 연결된 데이터 개수 조회
+export async function getTemplateRelatedDataCount(templateId: number) {
+  try {
+    const [questionsCount, responsesCount] = await Promise.all([
+      db
+        .select({ count: count() })
+        .from(complianceQuestions)
+        .where(eq(complianceQuestions.templateId, templateId)),
+      db
+        .select({ count: count() })
+        .from(complianceResponses)
+        .where(eq(complianceResponses.templateId, templateId)),
+    ]);
+
+    return {
+      questionsCount: questionsCount[0]?.count || 0,
+      responsesCount: responsesCount[0]?.count || 0,
+    };
+  } catch (error) {
+    console.error("Error getting template related data count:", error);
+    return { questionsCount: 0, responsesCount: 0 };
+  }
+}
+
+// 여러 템플릿의 연결된 데이터 개수 조회
+export async function getTemplatesRelatedDataCount(templateIds: number[]) {
+  try {
+    const results = await Promise.all(
+      templateIds.map(async (templateId) => {
+        const data = await getTemplateRelatedDataCount(templateId);
+        return { templateId, ...data };
+      })
+    );
+
+    const totalQuestions = results.reduce((sum, result) => sum + result.questionsCount, 0);
+    const totalResponses = results.reduce((sum, result) => sum + result.responsesCount, 0);
+
+    return {
+      totalQuestions,
+      totalResponses,
+      details: results,
+    };
+  } catch (error) {
+    console.error("Error getting templates related data count:", error);
+    return { totalQuestions: 0, totalResponses: 0, details: [] };
+  }
+}