path: root/lib/forms-plant/stat.ts

"use server"

import db from "@/db/db"
import { vendors, contracts, contractItems, forms,formsPlant,formEntriesPlant, formEntries, formMetas, tags,tagsPlant, tagClasses, tagClassAttributes, projects } from "@/db/schema"
import { eq, and, inArray } from "drizzle-orm"
import { getEditableFieldsByTag } from "./services"
import { getServerSession } from "next-auth/next"
import { authOptions } from "@/app/api/auth/[...nextauth]/route"

interface VendorFormStatus {
  vendorId: number
  vendorName: string
  formCount: number // 벤더가 가진 form 개수
  tagCount: number // 벤더가 가진 tag 개수
  totalFields: number // 입력해야 하는 총 필드 개수
  completedFields: number // 입력 완료된 필드 개수
  completionRate: number // 완료율 (%)
}

export interface FormStatusByVendor {
  tagCount: number;
  totalFields: number;
  completedFields: number;
  completionRate: number;
  upcomingCount: number;  // 7일 이내 임박한  개수
  overdueCount: number;   // 지연된 개수
}

export async function getProjectsWithContracts() {
  try {
    const projectList = await db
      .selectDistinct({
        id: projects.id,
        projectCode: projects.code,
        projectName: projects.name,
      })
      .from(projects)
      .innerJoin(contracts, eq(contracts.projectId, projects.id))
      .orderBy(projects.code)

    return projectList
  } catch (error) {
    console.error('Error getting projects with contracts:', error)
    throw new Error('계약이 있는 프로젝트 조회 중 오류가 발생했습니다.')
  }
}



export async function getVendorFormStatus(projectId?: number): Promise<VendorFormStatus[]> {
  try {
    // 1. 벤더 조회 쿼리 수정
    const vendorList = projectId 
      ? await db
          .selectDistinct({
            vendorId: vendors.id,
            vendorName: vendors.vendorName,
          })
          .from(vendors)
          .innerJoin(contracts, eq(contracts.vendorId, vendors.id))
          .where(eq(contracts.projectId, projectId))
      : await db
          .selectDistinct({
            vendorId: vendors.id,
            vendorName: vendors.vendorName,
          })
          .from(vendors)
          .innerJoin(contracts, eq(contracts.vendorId, vendors.id))
    

    const vendorStatusList: VendorFormStatus[] = []

    for (const vendor of vendorList) {
      let vendorFormCount = 0
      let vendorTagCount = 0
      let vendorTotalFields = 0
      let vendorCompletedFields = 0
      const uniqueTags = new Set<string>()

      // 2. 계약 조회 시 projectId 필터 추가
      const vendorContracts = projectId
      ? await db
          .select({
            id: contracts.id,
            projectId: contracts.projectId
          })
          .from(contracts)
          .where(
            and(
              eq(contracts.vendorId, vendor.vendorId),
              eq(contracts.projectId, projectId)
            )
          )
      : await db
          .select({
            id: contracts.id,
            projectId: contracts.projectId
          })
          .from(contracts)
          .where(eq(contracts.vendorId, vendor.vendorId))
  
          
      for (const contract of vendorContracts) {
        // 3. 계약별 contractItems 조회
        const contractItemsList = await db
          .select({
            id: contractItems.id
          })
          .from(contractItems)
          .where(eq(contractItems.contractId, contract.id))

        for (const contractItem of contractItemsList) {
          // 4. contractItem별 forms 조회
          const formsList = await db
            .select({
              id: forms.id,
              formCode: forms.formCode,
              contractItemId: forms.contractItemId
            })
            .from(forms)
            .where(eq(forms.contractItemId, contractItem.id))

          vendorFormCount += formsList.length

          // 5. formEntries 조회
          const entriesList = await db
            .select({
              id: formEntries.id,
              formCode: formEntries.formCode,
              data: formEntries.data
            })
            .from(formEntries)
            .where(eq(formEntries.contractItemId, contractItem.id))

          // 6. TAG별 편집 가능 필드 조회
          const editableFieldsByTag = await getEditableFieldsByTag(contractItem.id, contract.projectId)

          for (const entry of entriesList) {
            // formMetas에서 해당 formCode의 columns 조회
            const metaResult = await db
              .select({
                columns: formMetas.columns
              })
              .from(formMetas)
              .where(
                and(
                  eq(formMetas.formCode, entry.formCode),
                  eq(formMetas.projectId, contract.projectId)
                )
              )
              .limit(1)

            if (metaResult.length === 0) continue

            const metaColumns = metaResult[0].columns as any[]
            
            // shi가 'IN' 또는 'BOTH'인 필드 찾기
            const inputRequiredFields = metaColumns
              .filter(col => col.shi === 'IN' || col.shi === 'BOTH')
              .map(col => col.key)

            // entry.data 분석 (배열로 가정)
            const dataArray = Array.isArray(entry.data) ? entry.data : []
            
            for (const dataItem of dataArray) {
              if (typeof dataItem !== 'object' || !dataItem) continue
              
              const tagNo = dataItem.TAG_NO
              if (tagNo) {
                uniqueTags.add(tagNo)
                
                // TAG별 편집 가능 필드 가져오기
                const tagEditableFields = editableFieldsByTag.get(tagNo) || []
                
                // 최종 입력 필요 필드 = shi 기반 필드 + TAG 기반 편집 가능 필드
                const allRequiredFields = inputRequiredFields.filter(field => 
                    tagEditableFields.includes(field)
                  )                
                // 각 필드별 입력 상태 체크
                for (const fieldKey of allRequiredFields) {
                  vendorTotalFields++
                  
                  const fieldValue = dataItem[fieldKey]
                  // 값이 있고, 빈 문자열이 아니고, null이 아니면 입력 완료
                  if (fieldValue !== undefined && fieldValue !== null && fieldValue !== '') {
                    vendorCompletedFields++
                  }
                }
              }
            }
          }
        }
      }

      // 완료율 계산
      const completionRate = vendorTotalFields > 0 
        ? Math.round((vendorCompletedFields / vendorTotalFields) * 100 * 10) / 10 
        : 0

      vendorStatusList.push({
        vendorId: vendor.vendorId,
        vendorName: vendor.vendorName || '이름 없음',
        formCount: vendorFormCount,
        tagCount: uniqueTags.size,
        totalFields: vendorTotalFields,
        completedFields: vendorCompletedFields,
        completionRate
      })
    }

    return vendorStatusList

  } catch (error) {
    console.error('Error getting vendor form status:', error)
    throw new Error('벤더별 Form 입력 현황 조회 중 오류가 발생했습니다.')
  }
}



export async function getFormStatusByVendor(projectId: number, projectCode: string, packageCode: string, formCode: string): Promise<FormStatusByVendor[]> {
  try {
    const session = await getServerSession(authOptions)
    if (!session?.user?.id) {
      throw new Error("인증이 필요합니다.")
    }


    let vendorFormCount = 0
    let vendorTagCount = 0
    let vendorTotalFields = 0
    let vendorCompletedFields = 0
    let vendorUpcomingCount = 0  // 7일 이내 임박한 개수
    let vendorOverdueCount = 0    // 지연된 개수
    const uniqueTags = new Set<string>()
    const processedTags = new Set<string>()  // 중복 처리 방지용

    // 현재 날짜와 7일 후 날짜 계산
    const today = new Date()
    today.setHours(0, 0, 0, 0)  // 시간 부분 제거
    const sevenDaysLater = new Date(today)
    sevenDaysLater.setDate(sevenDaysLater.getDate() + 7)

    // 4. contractItem별 forms 조회
    const formsList = await db
      .select({
        id: formsPlant.id,
        formCode: formsPlant.formCode,
        contractItemId: formsPlant.contractItemId
      })
      .from(formsPlant)
      .where(
        and(
          eq(formsPlant.projectCode, projectCode),
          eq(formsPlant.packageCode, packageCode),
          eq(formsPlant.formCode, formCode)
        )
      )

    vendorFormCount += formsList.length

    // 5. formEntries 조회
    const entriesList = await db
      .select({
        id: formEntriesPlant.id,
        formCode: formEntriesPlant.formCode,
        data: formEntriesPlant.data
      })
      .from(formEntriesPlant)
      .where(
        and(
          eq(formEntriesPlant.packageCode, packageCode),
          eq(formEntriesPlant.projectCode, projectCode),
          eq(formEntriesPlant.formCode, formCode)
        )
      )

    // 6. TAG별 편집 가능 필드 조회
    const editableFieldsByTag = await getEditableFieldsByTag(projectCode,packageCode, projectId)

    const vendorStatusList: VendorFormStatus[] = []
    
    for (const entry of entriesList) {
      const metaResult = await db
        .select({
          columns: formMetas.columns
        })
        .from(formMetas)
        .where(
          and(
            eq(formMetas.formCode, entry.formCode),
            eq(formMetas.projectId, projectId)
          )
        )
        .limit(1)

      if (metaResult.length === 0) continue

      const metaColumns = metaResult[0].columns as any[]
      
      const inputRequiredFields = metaColumns
        .filter(col => col.shi === 'IN' || col.shi === 'BOTH')
        .map(col => col.key)

      const dataArray = Array.isArray(entry.data) ? entry.data : []
      
      for (const dataItem of dataArray) {
        if (typeof dataItem !== 'object' || !dataItem) continue
        
        const tagNo = dataItem.TAG_NO
        if (tagNo) {
          uniqueTags.add(tagNo)
          
          // TAG별 편집 가능 필드 가져오기
          const tagEditableFields = editableFieldsByTag.get(tagNo) || []
          
          const allRequiredFields = inputRequiredFields.filter(field => 
            tagEditableFields.includes(field)
          )
          
          // 해당 TAG의 필드 완료 상태 체크
          let tagHasIncompleteFields = false
          
          for (const fieldKey of allRequiredFields) {
            vendorTotalFields++
            
            const fieldValue = dataItem[fieldKey]
            if (fieldValue !== undefined && fieldValue !== null && fieldValue !== '') {
              vendorCompletedFields++
            } else {
              tagHasIncompleteFields = true
            }
          }
          
          // 미완료 TAG에 대해서만 날짜 체크 (TAG당 한 번만 처리)
          if (!processedTags.has(tagNo) && tagHasIncompleteFields) {
            processedTags.add(tagNo)
            
            const targetDate = dataItem.DUE_DATE
            if (targetDate) {
              const target = new Date(targetDate)
              target.setHours(0, 0, 0, 0)  // 시간 부분 제거
              
              if (target < today) {
                // 미완료이면서 지연된 경우 (오늘보다 이전)
                vendorOverdueCount++
              } else if (target >= today && target <= sevenDaysLater) {
                // 미완료이면서 7일 이내 임박한 경우
                vendorUpcomingCount++
              }
            }
          }
        }
      }
    }

    // 완료율 계산
    const completionRate = vendorTotalFields > 0 
      ? Math.round((vendorCompletedFields / vendorTotalFields) * 100 * 10) / 10 
      : 0

    vendorStatusList.push({
      tagCount: uniqueTags.size,
      totalFields: vendorTotalFields,
      completedFields: vendorCompletedFields,
      completionRate,
      upcomingCount: vendorUpcomingCount,
      overdueCount: vendorOverdueCount
    })

    return vendorStatusList

  } catch (error) {
    console.log('Error getting vendor form status:', error)
    throw new Error('벤더별 Form 입력 현황 조회 중 오류가 발생했습니다.')
  }
}