'use server'

import db  from "@/db/db";
import { 
  evaluationSubmissions, 
  vendors, 
  generalEvaluationResponses,
  esgEvaluationResponses,
  vendorEvaluationAttachments,
  EvaluationSubmission,
  GeneralEvaluationResponse,
  NewGeneralEvaluationResponse,
  generalEvaluations,
  GeneralEvaluation,
  EsgEvaluationItem,
  EsgAnswerOption,
  EsgEvaluationResponse,
  esgEvaluations,
  esgAnswerOptions,
  esgEvaluationItems,
  periodicEvaluations,
  evaluationTargets
} from "@/db/schema";
import { and, asc, desc, eq, ilike, or, SQL, count , sql, avg, inArray} from "drizzle-orm";
import { filterColumns } from "@/lib/filter-columns";
import { GetEvaluationsSubmitSchema } from "./validation";

// 평가 제출 목록 조회용 뷰 타입
export type EvaluationSubmissionWithVendor = EvaluationSubmission & {
  vendor: {
    id: number;
    vendorCode: string;
    vendorName: string;
    countryCode: string;
    contactEmail: string;
  };
  _count: {
    generalResponses: number;
    esgResponses: number;
    attachments: number;
  };
  // 연결된 평가 정보 (조선/해양 구분 표시용)
  linkedEvaluations: Array<{
    id: number;
    division: string;
    evaluationPeriod: string;
  }>;
  // 같은 그룹의 모든 submission IDs (제출 처리용)
  groupSubmissionIds?: number[];
};

/**
 * 평가 제출 목록을 조회합니다
 */
export async function getEvaluationSubmissions(input: GetEvaluationsSubmitSchema, vendorId: number) {
  try {
    const offset = (input.page - 1) * input.perPage;

    // 고급 필터링
    const advancedWhere = filterColumns({
      table: evaluationSubmissions,
      filters: input.filters,
      joinOperator: input.joinOperator,
    });

    // 전역 검색
    let globalWhere: SQL<unknown> | undefined;
    if (input.search) {
      const s = `%${input.search}%`;
      globalWhere = or(
        ilike(evaluationSubmissions.submissionId, s),
        ilike(vendors.vendorName, s),
        ilike(vendors.vendorCode, s),
        ilike(evaluationSubmissions.submissionStatus, s),
        ilike(evaluationSubmissions.evaluationRound, s)
      );
    }

    const finalWhere = and(
      advancedWhere,
      globalWhere,
      eq(evaluationSubmissions.isActive, true),
      eq(evaluationSubmissions.companyId, vendorId),
    );

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

    // 데이터 조회
    const { data, total } = await db.transaction(async (tx) => {

      const totalGeneralItemsResult = await tx
      .select({ count: count() })
      .from(generalEvaluations)
      .where(eq(generalEvaluations.isActive, true));
    
    const totalGeneralItemsCount = totalGeneralItemsResult[0]?.count || 0;

    const totalEsgItemsResult = await tx
    .select({ count: count() })
    .from(esgEvaluationItems)
    .innerJoin(esgEvaluations, eq(esgEvaluationItems.esgEvaluationId, esgEvaluations.id))
    .where(
      and(
        eq(esgEvaluations.isActive, true),
        eq(esgEvaluationItems.isActive, true)
      )
    );
  
  const totalEGSItemsCount = totalEsgItemsResult[0]?.count || 0;

      // 메인 데이터 조회
      const data = await tx
        .select({
          id: evaluationSubmissions.id,
          submissionId: evaluationSubmissions.submissionId,
          periodicEvaluationId: evaluationSubmissions.periodicEvaluationId,
          companyId: evaluationSubmissions.companyId,
          evaluationYear: evaluationSubmissions.evaluationYear,
          evaluationRound: evaluationSubmissions.evaluationRound,
          submissionStatus: evaluationSubmissions.submissionStatus,
          submittedAt: evaluationSubmissions.submittedAt,
          reviewedAt: evaluationSubmissions.reviewedAt,
          reviewedBy: evaluationSubmissions.reviewedBy,
          reviewComments: evaluationSubmissions.reviewComments,
          averageEsgScore: evaluationSubmissions.averageEsgScore,
        
          completedGeneralItems: evaluationSubmissions.completedGeneralItems,
        
          completedEsgItems: evaluationSubmissions.completedEsgItems,
          isActive: evaluationSubmissions.isActive,
          createdAt: evaluationSubmissions.createdAt,
          updatedAt: evaluationSubmissions.updatedAt,
          // Vendor 정보
          vendor: {
            id: vendors.id,
            vendorCode: vendors.vendorCode,
            vendorName: vendors.vendorName,
            countryCode: vendors.country,
            contactEmail: vendors.email,
          },
        })
        .from(evaluationSubmissions)
        .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id))
        .where(finalWhere)
        .orderBy(...orderBy)
        .limit(input.perPage)
        .offset(offset);

      // 각 제출에 대한 응답/첨부파일 수 조회
      const dataWithCounts = await Promise.all(
        data.map(async (submission) => {          
          const [generalCount, esgCount, attachmentCount, linkedEvaluationsResult] = await Promise.all([
            tx
              .select({ count: count() })
              .from(generalEvaluationResponses)
              .where(
                and(
                  eq(generalEvaluationResponses.submissionId, submission.id),
                  eq(generalEvaluationResponses.isActive, true)
                )
              )
              .then(result => result[0]?.count || 0),
            
            tx
              .select({ count: count() })
              .from(esgEvaluationResponses)
              .where(
                and(
                  eq(esgEvaluationResponses.submissionId, submission.id),
                  eq(esgEvaluationResponses.isActive, true)
                )
              )
              .then(result => result[0]?.count || 0),
            
            tx
              .select({ count: count() })
              .from(vendorEvaluationAttachments)
              .where(
                and(
                  eq(vendorEvaluationAttachments.submissionId, submission.id),
                  eq(vendorEvaluationAttachments.isActive, true)
                )
              )
              .then(result => result[0]?.count || 0),
            
              // 해당 submission의 periodicEvaluation 정보만 조회
            submission.periodicEvaluationId
              ? tx
                  .select({
                    id: periodicEvaluations.id,
                    division: evaluationTargets.division,
                    evaluationPeriod: periodicEvaluations.evaluationPeriod,
                  })
                  .from(periodicEvaluations)
                  .innerJoin(evaluationTargets, eq(periodicEvaluations.evaluationTargetId, evaluationTargets.id))
                  .where(eq(periodicEvaluations.id, submission.periodicEvaluationId))
                  .limit(1)
                  .catch(() => [])
              : Promise.resolve([]),
          ]);

          const linkedEvaluations = linkedEvaluationsResult || [];

          return {
            ...submission,
            totalGeneralItems: totalGeneralItemsCount ,
            totalEsgItems: totalEGSItemsCount,
            _count: {
              generalResponses: generalCount,
              esgResponses: esgCount,
              attachments: attachmentCount,
            },
            linkedEvaluations: linkedEvaluations,
          };
        })
      );

      // 같은 그룹의 evaluationSubmission들을 묶어서 하나의 그룹으로 반환
      const groupedData = dataWithCounts.reduce((groups, submission) => {
        const key = `${submission.companyId}_${submission.evaluationYear}_${submission.evaluationRound}`;
        if (!groups[key]) {
          groups[key] = [];
        }
        groups[key].push(submission);
        return groups;
      }, {} as Record<string, typeof dataWithCounts>);

      // 각 그룹의 첫 번째 submission을 대표로 해서 반환
      const finalData = Object.values(groupedData).map(group => {
        const representative = group[0];
        const allLinkedEvaluations = group.flatMap(sub => sub.linkedEvaluations);

        return {
          ...representative,
          linkedEvaluations: allLinkedEvaluations,
          // 그룹 내 모든 submission의 통계 합산
          _count: {
            generalResponses: group.reduce((sum, sub) => sum + sub._count.generalResponses, 0),
            esgResponses: group.reduce((sum, sub) => sum + sub._count.esgResponses, 0),
            attachments: group.reduce((sum, sub) => sum + sub._count.attachments, 0),
          },
          // 그룹 내 submission IDs 저장 (제출 처리용)
          groupSubmissionIds: group.map(sub => sub.id),
        };
      });

      // 총 개수 조회 - grouping 키를 기준으로 고유한 그룹 수를 계산
      // 먼저 모든 레코드를 조회하여 grouping 키를 추출
      const allSubmissions = await tx
        .select({
          companyId: evaluationSubmissions.companyId,
          evaluationYear: evaluationSubmissions.evaluationYear,
          evaluationRound: evaluationSubmissions.evaluationRound,
        })
        .from(evaluationSubmissions)
        .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id))
        .where(finalWhere);

      // 고유한 그룹 키 계산
      const uniqueGroupKeys = new Set(
        allSubmissions.map(sub => `${sub.companyId}_${sub.evaluationYear}_${sub.evaluationRound}`)
      );
      const total = uniqueGroupKeys.size;
      return { data: finalData, total };
    });

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

/**
 * 특정 평가 제출의 상세 정보를 조회합니다
 */
export async function getEvaluationSubmissionById(id: number) {
  try {
    const result = await db
      .select()
      .from(evaluationSubmissions)
      .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id))
      .where(
        and(
          eq(evaluationSubmissions.id, id),
          eq(evaluationSubmissions.isActive, true)
        )
      )
      .limit(1);

    if (result.length === 0) {
      return null;
    }

    const submission = result[0];

    // 응답 데이터도 함께 조회
    const [generalResponses, esgResponses, attachments] = await Promise.all([
      db
        .select()
        .from(generalEvaluationResponses)
        .where(
          and(
            eq(generalEvaluationResponses.submissionId, id),
            eq(generalEvaluationResponses.isActive, true)
          )
        ),
      
      db
        .select()
        .from(esgEvaluationResponses)
        .where(
          and(
            eq(esgEvaluationResponses.submissionId, id),
            eq(esgEvaluationResponses.isActive, true)
          )
        ),
      
      db
        .select()
        .from(vendorEvaluationAttachments)
        .where(
          and(
            eq(vendorEvaluationAttachments.submissionId, id),
            eq(vendorEvaluationAttachments.isActive, true)
          )
        ),
    ]);

    return {
      ...submission,
      generalResponses,
      esgResponses,
      attachments,
    };
  } catch (err) {
    console.error('Error in getEvaluationSubmissionById:', err);
    return null;
  }
}


/**
 * 평가 제출의 완성도를 확인합니다 (간단 버전)
 */
export async function getEvaluationSubmissionCompleteness(submissionId: number) {
  const result = await db.transaction(async (tx) => {
    // 제출 정보 조회
    const submissionInfo = await tx
      .select({
        submissionId: evaluationSubmissions.id,
        countryCode: vendors.country,
        averageEsgScore: evaluationSubmissions.averageEsgScore,
        totalGeneralItems: evaluationSubmissions.totalGeneralItems,
        completedGeneralItems: evaluationSubmissions.completedGeneralItems,
        totalEsgItems: evaluationSubmissions.totalEsgItems,
        completedEsgItems: evaluationSubmissions.completedEsgItems,
      })
      .from(evaluationSubmissions)
      .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id))
      .where(eq(evaluationSubmissions.id, submissionId))
      .limit(1);

    if (submissionInfo.length === 0) {
      throw new Error("Submission not found");
    }

    const info = submissionInfo[0];
    const isKorean = info.countryCode === 'KR';

    // 🔄 실제 평가 항목 수 조회 (전체 시스템에 등록된 평가 항목 수)
    const [actualGeneralTotal, actualEsgTotal] = await Promise.all([
      // 활성화된 일반평가 항목 수
      tx
        .select({ count: count() })
        .from(generalEvaluations)
        .where(eq(generalEvaluations.isActive, true))
        .then(result => result[0]?.count || 0),
      
      // 활성화된 ESG 평가 항목 수 (한국 업체인 경우에만)
      isKorean ? tx
        .select({ count: count() })
        .from(esgEvaluationItems)
        .innerJoin(esgEvaluations, eq(esgEvaluationItems.esgEvaluationId, esgEvaluations.id))
        .where(
          and(
            eq(esgEvaluationItems.isActive, true),
            eq(esgEvaluations.isActive, true)
          )
        )
        .then(result => result[0]?.count || 0) : Promise.resolve(0)
    ]);

    // 실시간 완성도 계산 (실제 응답된 것만)
    const [generalStats, esgStats] = await Promise.all([
      tx
        .select({
          total: count(),
          completed: sql<number>`COUNT(CASE WHEN ${generalEvaluationResponses.responseText} IS NOT NULL AND ${generalEvaluationResponses.responseText} != '' THEN 1 END)`,
        })
        .from(generalEvaluationResponses)
        .where(
          and(
            eq(generalEvaluationResponses.submissionId, submissionId),
            eq(generalEvaluationResponses.isActive, true)
          )
        ),
      
      isKorean ? tx
        .select({
          total: count(),
          completed: count(esgEvaluationResponses.selectedScore),
          averageScore: avg(esgEvaluationResponses.selectedScore),
        })
        .from(esgEvaluationResponses)
        .where(
          and(
            eq(esgEvaluationResponses.submissionId, submissionId),
            eq(esgEvaluationResponses.isActive, true)
          )
        ) : Promise.resolve([{ total: 0, completed: 0, averageScore: null }])
    ]);

    // 실제 완료된 항목 수 (숫자로 변환 0707 최겸 수정)
    const generalCompleted = Number(generalStats[0]?.completed || 0);
    const esgCompleted = Number(esgStats[0]?.completed || 0);
    const esgAverage = parseFloat(esgStats[0]?.averageScore?.toString() || '0');

    // 🎯 실제 평가 항목 수를 기준으로 완성도 계산
    return {
      general: {
        total: actualGeneralTotal,
        completed: generalCompleted,
        percentage: actualGeneralTotal > 0 ? (generalCompleted / actualGeneralTotal) * 100 : 0,
        isComplete: actualGeneralTotal > 0 && generalCompleted === actualGeneralTotal,
      },
      esg: {
        total: actualEsgTotal,
        completed: esgCompleted,
        percentage: actualEsgTotal > 0 ? (esgCompleted / actualEsgTotal) * 100 : 0,
        averageScore: esgAverage,
        isComplete: actualEsgTotal === 0 || esgCompleted === actualEsgTotal,
      },
      overall: {
        isComplete: 
          (actualGeneralTotal > 0 && generalCompleted === actualGeneralTotal) &&
          (actualEsgTotal === 0 || esgCompleted === actualEsgTotal),
        totalItems: actualGeneralTotal + actualEsgTotal,
        completedItems: generalCompleted + esgCompleted,
      },
    };
  });

  return result;
}

/**
 * 평가 제출 상태를 업데이트합니다 (완성도 검증 포함)
 */
export async function updateEvaluationSubmissionStatus(
  submissionId: number,
  newStatus: string,
  reviewData?: {
    reviewedBy: string;
    reviewComments?: string;
  }
) {
  return await db.transaction(async (tx) => {
    // 제출 시에는 완성도 검증
    if (newStatus === 'submitted') {
      const completeness = await getEvaluationSubmissionCompleteness(submissionId);
      
      if (!completeness.overall.isComplete) {
        throw new Error(
          `평가가 완료되지 않았습니다. ` +
          `일반평가: ${completeness.general.completed}/${completeness.general.total}, ` +
          `ESG평가: ${completeness.esg.completed}/${completeness.esg.total}`
        );
      }
    }

    // 상태 업데이트
    const updateData: any = {
      submissionStatus: newStatus,
      updatedAt: new Date(),
    };

    if (newStatus === 'submitted') {
      updateData.submittedAt = new Date();
    }

    if (reviewData) {
      updateData.reviewedAt = new Date();
      updateData.reviewedBy = reviewData.reviewedBy;
      updateData.reviewComments = reviewData.reviewComments;
    }

    const [updatedSubmission] = await tx
      .update(evaluationSubmissions)
      .set(updateData)
      .where(eq(evaluationSubmissions.id, submissionId))
      .returning();

    // newStatus === 'submitted'일 때 periodicEvaluations 테이블도 업데이트
    if (newStatus === 'submitted' && updatedSubmission) {
      // 그룹 내 모든 submission IDs를 DB 기준으로 계산 (companyId/evaluationYear/evaluationRound)
      const siblingSubmissions = await tx
      .select({ id: evaluationSubmissions.id })
      .from(evaluationSubmissions)
      .where(
        and(
          eq(evaluationSubmissions.companyId, updatedSubmission.companyId),
          eq(evaluationSubmissions.evaluationYear, updatedSubmission.evaluationYear),
          eq(evaluationSubmissions.evaluationRound, updatedSubmission.evaluationRound),
          eq(evaluationSubmissions.isActive, true),
        )
      );

      const submissionIdsToUpdate = siblingSubmissions.map(s => s.id);

     // 그룹 내 모든 submission들의 상태를 submitted로 업데이트
      await tx
        .update(evaluationSubmissions)
        .set({
          submissionStatus: 'submitted',
          submittedAt: new Date(),
          updatedAt: new Date(),
        })
        .where(inArray(evaluationSubmissions.id, submissionIdsToUpdate));

      // 그룹 내 모든 periodicEvaluation들도 함께 업데이트
      const periodicEvaluationIds = await tx
        .select({ periodicEvaluationId: evaluationSubmissions.periodicEvaluationId })
        .from(evaluationSubmissions)
        .where(inArray(evaluationSubmissions.id, submissionIdsToUpdate));

      await Promise.all(
        periodicEvaluationIds.map(({ periodicEvaluationId }) =>
          tx
            .update(periodicEvaluations)
            .set({
              documentsSubmitted: true,
              status: 'SUBMITTED',
              submissionDate: new Date(),
              updatedAt: new Date(),
            })
            .where(eq(periodicEvaluations.id, periodicEvaluationId))
        )
      );
    }

    return updatedSubmission;
  });
}

export type GeneralEvaluationFormData = {
  submission: {
    id: number;
    submissionId: string;
    vendorName: string;
    submissionStatus: string;
  };
  evaluations: Array<{
    evaluation: GeneralEvaluation;
    response: GeneralEvaluationResponse | null;
    attachments: Array<{
      id: number;
      fileId: string;
      originalFileName: string;
      fileSize: number;
      mimeType: string | null;
      createdAt: Date;
    }>;
  }>;
};

/**
 * 일반평가 폼 데이터를 조회하고, 응답 레코드가 없으면 생성합니다
 */
export async function getGeneralEvaluationFormData(submissionId: number): Promise<GeneralEvaluationFormData> {
  return await db.transaction(async (tx) => {
    // 1. 제출 정보 조회
    const submissionResult = await tx
      .select({
        id: evaluationSubmissions.id,
        submissionId: evaluationSubmissions.submissionId,
        vendorName: vendors.vendorName,
        submissionStatus: evaluationSubmissions.submissionStatus,
      })
      .from(evaluationSubmissions)
      .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id))
      .where(eq(evaluationSubmissions.id, submissionId))
      .limit(1);

    if (submissionResult.length === 0) {
      throw new Error("제출 정보를 찾을 수 없습니다.");
    }

    const submission = submissionResult[0];

    // 2. 활성화된 일반평가 항목들 조회
    const activeEvaluations = await tx
      .select()
      .from(generalEvaluations)
      .where(eq(generalEvaluations.isActive, true))
      .orderBy(asc(generalEvaluations.serialNumber));

    // 3. 기존 응답들 조회
    const existingResponses = await tx
      .select()
      .from(generalEvaluationResponses)
      .where(
        and(
          eq(generalEvaluationResponses.submissionId, submissionId),
          eq(generalEvaluationResponses.isActive, true)
        )
      );

    // 4. 응답이 없는 평가 항목들에 대해 빈 응답 레코드 생성
    const responseMap = new Map(existingResponses.map(r => [r.generalEvaluationId, r]));
    const missingResponses: NewGeneralEvaluationResponse[] = [];

    for (const evaluation of activeEvaluations) {
      if (!responseMap.has(evaluation.id)) {
        missingResponses.push({
          submissionId,
          generalEvaluationId: evaluation.id,
          responseText: '',
          hasAttachments: false,
        });
      }
    }

    // 5. 누락된 응답 레코드들 생성
    let newResponses: GeneralEvaluationResponse[] = [];
    if (missingResponses.length > 0) {
      newResponses = await tx
        .insert(generalEvaluationResponses)
        .values(missingResponses)
        .returning();
    }

    // 6. 응답 맵 업데이트
    newResponses.forEach(response => {
      responseMap.set(response.generalEvaluationId, response);
    });

    // 7. 각 응답의 첨부파일들 조회
    const evaluationData = await Promise.all(
      activeEvaluations.map(async (evaluation) => {
        const response = responseMap.get(evaluation.id) || null;
        
        let attachments: any[] = [];
        if (response) {
          attachments = await tx
            .select({
              id: vendorEvaluationAttachments.id,
              fileId: vendorEvaluationAttachments.fileId,
              originalFileName: vendorEvaluationAttachments.originalFileName,
              fileSize: vendorEvaluationAttachments.fileSize,
              mimeType: vendorEvaluationAttachments.mimeType,
              createdAt: vendorEvaluationAttachments.createdAt,
            })
            .from(vendorEvaluationAttachments)
            .where(
              and(
                eq(vendorEvaluationAttachments.generalEvaluationResponseId, response.id),
                eq(vendorEvaluationAttachments.isActive, true)
              )
            )
            .orderBy(desc(vendorEvaluationAttachments.createdAt));
        }

        return {
          evaluation,
          response,
          attachments,
        };
      })
    );

    return {
      submission,
      evaluations: evaluationData,
    };
  });
}

/**
 * 일반평가 응답을 저장합니다
 */
export async function saveGeneralEvaluationResponse(data: {
  responseId: number;
  responseText: string;
  hasAttachments?: boolean;
}) {
  try {
    const [updatedResponse] = await db
      .update(generalEvaluationResponses)
      .set({
        responseText: data.responseText,
        hasAttachments: data.hasAttachments || false,
        updatedAt: new Date(),
      })
      .where(eq(generalEvaluationResponses.id, data.responseId))
      .returning();

    return updatedResponse;
  } catch (error) {
    console.error('Error saving general evaluation response:', error);
    throw error;
  }
}

/**
 * 평가 제출의 진행률과 ESG 평균점수를 계산합니다
 */
export async function recalculateEvaluationProgress(submissionId: number) {
  try {
    return await db.transaction(async (tx) => {
      // 1. 일반평가 진행률 계산 (점수 계산 제거)
      const generalProgressResult = await tx
        .select({
          totalItems: count(),
          completedItems: sql<number>`COUNT(CASE WHEN ${generalEvaluationResponses.responseText} IS NOT NULL AND ${generalEvaluationResponses.responseText} != '' THEN 1 END)`,
        })
        .from(generalEvaluationResponses)
        .where(
          and(
            eq(generalEvaluationResponses.submissionId, submissionId),
            eq(generalEvaluationResponses.isActive, true)
          )
        );

      const generalStats = generalProgressResult[0];
      const totalGeneralItems = generalStats.totalItems || 0;
      const completedGeneralItems = generalStats.completedItems || 0;

      // 2. ESG 평가 평균 점수 계산
      const esgScoreResult = await tx
        .select({
          averageScore: avg(esgEvaluationResponses.selectedScore),
          totalItems: count(),
          completedItems: count(esgEvaluationResponses.selectedScore),
        })
        .from(esgEvaluationResponses)
        .where(
          and(
            eq(esgEvaluationResponses.submissionId, submissionId),
            eq(esgEvaluationResponses.isActive, true)
          )
        );

      const esgStats = esgScoreResult[0];
      const averageEsgScore = parseFloat(esgStats.averageScore?.toString() || '0');
      const totalEsgItems = esgStats.totalItems || 0;
      const completedEsgItems = esgStats.completedItems || 0;

      // 3. submission 테이블 업데이트
      const [updatedSubmission] = await tx
        .update(evaluationSubmissions)
        .set({
          // ❌ averageGeneralScore 제거
          averageEsgScore: averageEsgScore > 0 ? averageEsgScore.toString() : null,
          totalGeneralItems,
          completedGeneralItems,
          totalEsgItems,
          completedEsgItems,
          updatedAt: new Date(),
        })
        .where(eq(evaluationSubmissions.id, submissionId))
        .returning();

      return {
        submission: updatedSubmission,
        stats: {
          general: {
            total: totalGeneralItems,
            completed: completedGeneralItems,
            percentage: totalGeneralItems > 0 ? (completedGeneralItems / totalGeneralItems) * 100 : 0,
          },
          esg: {
            average: averageEsgScore,
            total: totalEsgItems,
            completed: completedEsgItems,
            percentage: totalEsgItems > 0 ? (completedEsgItems / totalEsgItems) * 100 : 0,
          },
        },
      };
    });
  } catch (error) {
    console.error('Error recalculating evaluation progress:', error);
    throw error;
  }
}


// ================================================================
// ESG평가 관련 서버 액션들
// ================================================================

export type EsgEvaluationFormData = {
  submission: {
    id: number;
    submissionId: string;
    vendorName: string;
    submissionStatus: string;
  };
  evaluations: Array<{
    evaluation: {
      id: number;
      serialNumber: string;
      category: string;
      inspectionItem: string;
    };
    items: Array<{
      item: EsgEvaluationItem;
      answerOptions: EsgAnswerOption[];
      response: EsgEvaluationResponse | null;
    }>;
  }>;
};

/**
 * ESG평가 폼 데이터를 조회합니다 (응답은 실시간 생성)
 */
export async function getEsgEvaluationFormData(submissionId: number): Promise<EsgEvaluationFormData> {
  return await db.transaction(async (tx) => {
    // 1. 제출 정보 조회
    const submissionResult = await tx
      .select({
        id: evaluationSubmissions.id,
        submissionId: evaluationSubmissions.submissionId,
        vendorName: vendors.vendorName,
        submissionStatus: evaluationSubmissions.submissionStatus,
      })
      .from(evaluationSubmissions)
      .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id))
      .where(eq(evaluationSubmissions.id, submissionId))
      .limit(1);

    if (submissionResult.length === 0) {
      throw new Error("제출 정보를 찾을 수 없습니다.");
    }

    const submission = submissionResult[0];

    // 2. 활성화된 ESG 평가표들 조회
    const activeEsgEvaluations = await tx
      .select({
        id: esgEvaluations.id,
        serialNumber: esgEvaluations.serialNumber,
        category: esgEvaluations.category,
        inspectionItem: esgEvaluations.inspectionItem,
      })
      .from(esgEvaluations)
      .where(eq(esgEvaluations.isActive, true))
      .orderBy(asc(esgEvaluations.serialNumber));

    // 3. 각 ESG 평가표의 항목들과 답변 옵션들 조회
    const evaluationData = await Promise.all(
      activeEsgEvaluations.map(async (evaluation) => {
        // 평가 항목들 조회
        const items = await tx
          .select()
          .from(esgEvaluationItems)
          .where(
            and(
              eq(esgEvaluationItems.esgEvaluationId, evaluation.id),
              eq(esgEvaluationItems.isActive, true)
            )
          )
          .orderBy(asc(esgEvaluationItems.orderIndex));

        // 각 항목의 답변 옵션들과 기존 응답 조회
        const itemsWithOptions = await Promise.all(
          items.map(async (item) => {
            // 답변 옵션들 조회
            const answerOptions = await tx
              .select()
              .from(esgAnswerOptions)
              .where(
                and(
                  eq(esgAnswerOptions.esgEvaluationItemId, item.id),
                  eq(esgAnswerOptions.isActive, true)
                )
              )
              .orderBy(asc(esgAnswerOptions.orderIndex));

            // 기존 응답 조회
            const existingResponse = await tx
              .select()
              .from(esgEvaluationResponses)
              .where(
                and(
                  eq(esgEvaluationResponses.submissionId, submissionId),
                  eq(esgEvaluationResponses.esgEvaluationItemId, item.id),
                  eq(esgEvaluationResponses.isActive, true)
                )
              )
              .limit(1);

            return {
              item,
              answerOptions,
              response: existingResponse[0] || null,
            };
          })
        );

        return {
          evaluation,
          items: itemsWithOptions,
        };
      })
    );

    return {
      submission,
      evaluations: evaluationData,
    };
  });
}

/**
 * ESG평가 응답을 저장합니다
 */
export async function saveEsgEvaluationResponse(data: {
  submissionId: number;
  esgEvaluationItemId: number;
  esgAnswerOptionId: number;
  selectedScore: number;
  additionalComments?: string;
}) {
  try {
    return await db.transaction(async (tx) => {
      // 기존 응답이 있는지 확인
      const existingResponse = await tx
        .select()
        .from(esgEvaluationResponses)
        .where(
          and(
            eq(esgEvaluationResponses.submissionId, data.submissionId),
            eq(esgEvaluationResponses.esgEvaluationItemId, data.esgEvaluationItemId),
            eq(esgEvaluationResponses.isActive, true)
          )
        )
        .limit(1);

      if (existingResponse.length > 0) {
        // 기존 응답 업데이트
        const [updatedResponse] = await tx
          .update(esgEvaluationResponses)
          .set({
            esgAnswerOptionId: data.esgAnswerOptionId,
            selectedScore: data.selectedScore.toString(),
            additionalComments: data.additionalComments || null,
            updatedAt: new Date(),
          })
          .where(eq(esgEvaluationResponses.id, existingResponse[0].id))
          .returning();

        return updatedResponse;
      } else {
        // 새 응답 생성
        const [newResponse] = await tx
          .insert(esgEvaluationResponses)
          .values({
            submissionId: data.submissionId,
            esgEvaluationItemId: data.esgEvaluationItemId,
            esgAnswerOptionId: data.esgAnswerOptionId,
            selectedScore: data.selectedScore.toString(),
            additionalComments: data.additionalComments || null,
          })
          .returning();

        return newResponse;
      }
    });
  } catch (error) {
    console.error('Error saving ESG evaluation response:', error);
    throw error;
  }
}

export async function updateAttachmentStatus(responseId: number) {
  try {
    // 활성 첨부파일 개수 확인
    const attachmentCount = await db
      .select({ count: vendorEvaluationAttachments.id })
      .from(vendorEvaluationAttachments)
      .where(
        and(
          eq(vendorEvaluationAttachments.generalEvaluationResponseId, responseId),
          eq(vendorEvaluationAttachments.isActive, true)
        )
      )

    const hasAttachments = attachmentCount.length > 0

    // 응답 테이블의 hasAttachments 필드 업데이트
    await db
      .update(generalEvaluationResponses)
      .set({ 
        hasAttachments,
        updatedAt: new Date()
      })
      .where(eq(generalEvaluationResponses.id, responseId))

    return { hasAttachments, count: attachmentCount.length }
  } catch (error) {
    console.error('Error updating attachment status:', error)
    throw error
  }
}