'use server'

import db from "@/db/db"
import {
  evaluationSubmissions,
  generalEvaluations,
  generalEvaluationResponses,
  esgEvaluations,
  esgEvaluationItems,
  esgAnswerOptions,
  esgEvaluationResponses,
  vendorEvaluationAttachments,
  periodicEvaluations,
  evaluationTargets,
  vendors,
} from "@/db/schema"
import { eq, and, asc, desc, sql } from "drizzle-orm"

// 협력업체 제출 상세 정보 타입 정의
export interface VendorSubmissionDetail {
  // 제출 기본 정보
  submissionId: string
  evaluationYear: number
  evaluationRound: string | null
  submissionStatus: string
  submittedAt: Date | null
  reviewedAt: Date | null
  reviewedBy: string | null
  reviewComments: string | null
  averageEsgScore: number | null
  
  // 진행률 통계
  totalGeneralItems: number
  completedGeneralItems: number
  totalEsgItems: number
  completedEsgItems: number
  
  // 협력업체 정보
  vendor: {
    id: number
    vendorCode: string
    vendorName: string
    email: string | null
    country: string | null
  }
  
  // 일반평가 응답
  generalEvaluations: {
    id: number
    serialNumber: string
    category: string
    inspectionItem: string
    remarks: string | null
    response: {
      responseText: string
      hasAttachments: boolean
      reviewComments: string | null
      attachments: {
        id: number
        fileId: string
        originalFileName: string
        storedFileName: string
        filePath: string
        fileSize: number
        mimeType: string | null
        uploadedBy: string
        createdAt: Date
      }[]
    } | null
  }[]
  
  // ESG 평가 응답
  esgEvaluations: {
    id: number
    serialNumber: string
    category: string
    inspectionItem: string
    evaluationItems: {
      id: number
      evaluationItem: string
      evaluationItemDescription: string | null
      orderIndex: number
      response: {
        selectedScore: number
        additionalComments: string | null
        answerOption: {
          id: number
          answerText: string
          score: number
        }
      } | null
    }[]
  }[]
  
  // 첨부파일 통계
  attachmentStats: {
    totalFiles: number
    totalSize: number
    generalEvaluationFiles: number
    esgEvaluationFiles: number
  }
}

/**
 * 특정 정기평가에 대한 협력업체 제출 상세 정보를 조회합니다
 */
export async function getVendorSubmissionDetails(periodicEvaluationId: number): Promise<VendorSubmissionDetail | null> {
  try {
    // 1. 제출 정보 조회
    const submissionResult = await db
      .select({
        // 제출 기본 정보
        id: evaluationSubmissions.id,
        submissionId: evaluationSubmissions.submissionId,
        evaluationYear: evaluationSubmissions.evaluationYear,
        evaluationRound: evaluationSubmissions.evaluationRound,
        submissionStatus: evaluationSubmissions.submissionStatus,
        submittedAt: evaluationSubmissions.submittedAt,
        reviewedAt: evaluationSubmissions.reviewedAt,
        reviewedBy: evaluationSubmissions.reviewedBy,
        reviewComments: evaluationSubmissions.reviewComments,
        averageEsgScore: evaluationSubmissions.averageEsgScore,
        
        // 진행률 통계
        totalGeneralItems: evaluationSubmissions.totalGeneralItems,
        completedGeneralItems: evaluationSubmissions.completedGeneralItems,
        totalEsgItems: evaluationSubmissions.totalEsgItems,
        completedEsgItems: evaluationSubmissions.completedEsgItems,
        
        // 협력업체 정보
        vendorId: vendors.id,
        companyId: evaluationSubmissions.companyId,
        vendorCode: vendors.vendorCode,
        vendorName: vendors.vendorName,
        vendorEmail: vendors.email,
        vendorCountry: vendors.country,
      })
      .from(evaluationSubmissions)
      .innerJoin(vendors, eq(evaluationSubmissions.companyId, vendors.id))
      .where(
        and(
          eq(evaluationSubmissions.periodicEvaluationId, periodicEvaluationId),
          eq(evaluationSubmissions.isActive, true)
        )
      )
      .limit(1)

    if (submissionResult.length === 0) {
      return null // 제출 내용이 없음
    }

    const submission = submissionResult[0]
    let submissionId = submission.id // evaluationSubmissions.id (integer)
    const submissionUuid = submission.submissionId // evaluationSubmissions.submissionId (UUID)

    console.log("=== 협력업체 제출 상세 조회 시작 ===")
    console.log("submissionId (integer):", submissionId)
    console.log("submissionUuid:", submissionUuid)
    console.log("submission:", submission)

    // 🔍 조선/해양 동시 제출 케이스 처리: 현재 submission에 응답이 없으면 같은 그룹에서 응답이 있는 submission을 찾아서 사용
    const hasResponses = await db
      .select({ count: sql<number>`COUNT(*)::int` })
      .from(generalEvaluationResponses)
      .where(
        and(
          eq(generalEvaluationResponses.submissionId, submissionId),
          eq(generalEvaluationResponses.isActive, true)
        )
      )
      .then(result => (result[0]?.count || 0) > 0)

    if (!hasResponses) {
      console.log("현재 submission에 응답 없음. 같은 그룹에서 응답이 있는 submission 찾기...")
      
      // 같은 companyId, evaluationYear, evaluationRound를 가진 다른 submission 중 응답이 있는 것을 찾음
      const siblingSubmissions = await db
        .select({
          id: evaluationSubmissions.id,
          submissionId: evaluationSubmissions.submissionId,
        })
        .from(evaluationSubmissions)
        .where(
          and(
            eq(evaluationSubmissions.companyId, submission.companyId),
            eq(evaluationSubmissions.evaluationYear, submission.evaluationYear),
            eq(evaluationSubmissions.evaluationRound, submission.evaluationRound || ""),
            eq(evaluationSubmissions.isActive, true)
          )
        )

      // 각 sibling submission에 응답이 있는지 확인
      for (const sibling of siblingSubmissions) {
        const siblingHasResponses = await db
          .select({ count: sql<number>`COUNT(*)::int` })
          .from(generalEvaluationResponses)
          .where(
            and(
              eq(generalEvaluationResponses.submissionId, sibling.id),
              eq(generalEvaluationResponses.isActive, true)
            )
          )
          .then(result => (result[0]?.count || 0) > 0)

        if (siblingHasResponses) {
          console.log(`응답이 있는 submission 발견: ${sibling.id}`)
          submissionId = sibling.id
          break
        }
      }
    }

    // 2. 일반평가 항목과 응답 조회
    const generalEvaluationsResult = await db
      .select({
        // 일반평가 항목 정보
        generalEvaluationId: generalEvaluations.id,
        serialNumber: generalEvaluations.serialNumber,
        category: generalEvaluations.category,
        inspectionItem: generalEvaluations.inspectionItem,
        remarks: generalEvaluations.remarks,
        
        // 응답 정보
        responseId: generalEvaluationResponses.id,
        responseText: generalEvaluationResponses.responseText,
        hasAttachments: generalEvaluationResponses.hasAttachments,
        reviewComments: generalEvaluationResponses.reviewComments,
        
        // 첨부파일 정보
        attachmentId: vendorEvaluationAttachments.id,
        fileId: vendorEvaluationAttachments.fileId,
        originalFileName: vendorEvaluationAttachments.originalFileName,
        storedFileName: vendorEvaluationAttachments.storedFileName,
        filePath: vendorEvaluationAttachments.filePath,
        fileSize: vendorEvaluationAttachments.fileSize,
        mimeType: vendorEvaluationAttachments.mimeType,
        uploadedBy: vendorEvaluationAttachments.uploadedBy,
        attachmentCreatedAt: vendorEvaluationAttachments.createdAt,
      })
      .from(generalEvaluations)
      .leftJoin(
        generalEvaluationResponses,
        and(
          eq(generalEvaluationResponses.generalEvaluationId, generalEvaluations.id),
          eq(generalEvaluationResponses.submissionId, submissionId),
          eq(generalEvaluationResponses.isActive, true)
        )
      )
      .leftJoin(
        vendorEvaluationAttachments,
        eq(vendorEvaluationAttachments.generalEvaluationResponseId, generalEvaluationResponses.id)
      )
      .where(eq(generalEvaluations.isActive, true))
      .orderBy(asc(generalEvaluations.serialNumber))

    // 3. ESG 평가 항목과 응답 조회
    const esgEvaluationsResult = await db
      .select({
        // ESG 평가표 정보
        esgEvaluationId: esgEvaluations.id,
        esgSerialNumber: esgEvaluations.serialNumber,
        esgCategory: esgEvaluations.category,
        esgInspectionItem: esgEvaluations.inspectionItem,
        
        // ESG 평가항목 정보
        esgEvaluationItemId: esgEvaluationItems.id,
        evaluationItem: esgEvaluationItems.evaluationItem,
        evaluationItemDescription: esgEvaluationItems.evaluationItemDescription,
        orderIndex: esgEvaluationItems.orderIndex,
        
        // ESG 응답 정보
        esgResponseId: esgEvaluationResponses.id,
        selectedScore: esgEvaluationResponses.selectedScore,
        additionalComments: esgEvaluationResponses.additionalComments,
        
        // ESG 답변 옵션 정보
        answerOptionId: esgAnswerOptions.id,
        answerText: esgAnswerOptions.answerText,
        answerScore: esgAnswerOptions.score,
      })
      .from(esgEvaluations)
      .innerJoin(esgEvaluationItems, eq(esgEvaluationItems.esgEvaluationId, esgEvaluations.id))
      .leftJoin(
        esgEvaluationResponses,
        and(
          eq(esgEvaluationResponses.esgEvaluationItemId, esgEvaluationItems.id),
          eq(esgEvaluationResponses.submissionId, submissionId),
          eq(esgEvaluationResponses.isActive, true)
        )
      )
      .leftJoin(
        esgAnswerOptions,
        eq(esgAnswerOptions.id, esgEvaluationResponses.esgAnswerOptionId)
      )
      .where(
        and(
          eq(esgEvaluations.isActive, true),
          eq(esgEvaluationItems.isActive, true)
        )
      )
      .orderBy(
        asc(esgEvaluations.serialNumber),
        asc(esgEvaluationItems.orderIndex)
      )

    // 4. 데이터 가공
    // 일반평가 데이터 그룹화
    const generalEvaluationsMap = new Map<number, any>()
    generalEvaluationsResult.forEach(row => {
      if (!generalEvaluationsMap.has(row.generalEvaluationId)) {
        generalEvaluationsMap.set(row.generalEvaluationId, {
          id: row.generalEvaluationId,
          serialNumber: row.serialNumber,
          category: row.category,
          inspectionItem: row.inspectionItem,
          remarks: row.remarks,
          response: row.responseId ? {
            responseText: row.responseText,
            hasAttachments: row.hasAttachments,
            reviewComments: row.reviewComments,
            attachments: []
          } : null
        })
      }

      // 첨부파일 추가
      if (row.attachmentId && generalEvaluationsMap.get(row.generalEvaluationId)?.response) {
        generalEvaluationsMap.get(row.generalEvaluationId).response.attachments.push({
          id: row.attachmentId,
          fileId: row.fileId,
          originalFileName: row.originalFileName,
          storedFileName: row.storedFileName,
          filePath: row.filePath,
          fileSize: row.fileSize,
          mimeType: row.mimeType,
          uploadedBy: row.uploadedBy,
          createdAt: row.attachmentCreatedAt ? new Date(row.attachmentCreatedAt) : new Date()
        })
      }
    })

    // ESG 평가 데이터 그룹화
    const esgEvaluationsMap = new Map<number, any>()
    esgEvaluationsResult.forEach(row => {
      if (!esgEvaluationsMap.has(row.esgEvaluationId)) {
        esgEvaluationsMap.set(row.esgEvaluationId, {
          id: row.esgEvaluationId,
          serialNumber: row.esgSerialNumber,
          category: row.esgCategory,
          inspectionItem: row.esgInspectionItem,
          evaluationItems: []
        })
      }

      const esgEvaluation = esgEvaluationsMap.get(row.esgEvaluationId)
      
      // 평가항목 추가 (중복 방지)
      const existingItem = esgEvaluation.evaluationItems.find((item: any) => item.id === row.esgEvaluationItemId)
      if (!existingItem) {
        esgEvaluation.evaluationItems.push({
          id: row.esgEvaluationItemId,
          evaluationItem: row.evaluationItem,
          evaluationItemDescription: row.evaluationItemDescription,
          orderIndex: row.orderIndex,
          response: row.esgResponseId ? {
            selectedScore: Number(row.selectedScore),
            additionalComments: row.additionalComments,
            answerOption: {
              id: row.answerOptionId,
              answerText: row.answerText,
              score: Number(row.answerScore)
            }
          } : null
        })
      }
    })

    // 5. 첨부파일 통계 계산
    const allAttachments = generalEvaluationsResult
      .filter(row => row.attachmentId && row.fileSize !== null)
      .map(row => ({
        id: row.attachmentId,
        fileSize: row.fileSize || 0
      }))

    const attachmentStats = {
      totalFiles: allAttachments.length,
      totalSize: allAttachments.reduce((sum, att) => sum + att.fileSize, 0),
      generalEvaluationFiles: allAttachments.length,
      esgEvaluationFiles: 0 // ESG는 첨부파일 없음
    }

    return {
      submissionId: submission.submissionId,
      evaluationYear: submission.evaluationYear,
      evaluationRound: submission.evaluationRound,
      submissionStatus: submission.submissionStatus,
      submittedAt: submission.submittedAt,
      reviewedAt: submission.reviewedAt,
      reviewedBy: submission.reviewedBy,
      reviewComments: submission.reviewComments,
      averageEsgScore: submission.averageEsgScore ? Number(submission.averageEsgScore) : null,
      
      // 진행률 통계
      totalGeneralItems: submission.totalGeneralItems,
      completedGeneralItems: submission.completedGeneralItems,
      totalEsgItems: submission.totalEsgItems,
      completedEsgItems: submission.completedEsgItems,

      vendor: {
        id: submission.vendorId,
        vendorCode: submission.vendorCode || "",
        vendorName: submission.vendorName,
        email: submission.vendorEmail,
        country: submission.vendorCountry,
      },
      
      generalEvaluations: Array.from(generalEvaluationsMap.values()),
      esgEvaluations: Array.from(esgEvaluationsMap.values()),
      attachmentStats
    }

  } catch (error) {
    console.error("Error fetching vendor submission details:", error)
    throw new Error("협력업체 제출 상세 정보 조회 중 오류가 발생했습니다")
  }
}