'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 {
    // templateId 유효성 검사 추가
    if (!templateId || isNaN(templateId) || templateId <= 0) {
      console.error(`Invalid templateId: ${templateId}`);
      return null;
    }
    
    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 {
    // templateId 유효성 검사 추가
    if (!templateId || isNaN(templateId) || templateId <= 0) {
      console.error(`Invalid templateId: ${templateId}`);
      return [];
    }
    
    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 {
    // templateId 유효성 검사 추가
    if (!templateId || isNaN(templateId) || templateId <= 0) {
      console.error(`Invalid templateId: ${templateId}`);
      return [];
    }
    
    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 {
    // templateId 유효성 검사 추가
    if (!templateId || isNaN(templateId) || templateId <= 0) {
      console.error(`Invalid templateId: ${templateId}`);
      return { data: [], pageCount: 0 };
    }
    
    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 {
    // templateId 유효성 검사 추가
    if (!templateId || isNaN(templateId) || templateId <= 0) {
      console.error(`Invalid templateId: ${templateId}`);
      return {
        inProgress: 0,
        completed: 0,
        reviewed: 0,
        total: 0
      };
    }
    
    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: [] };
  }
}