"use server";

import db from "@/db/db";
import {
  formMetas,
  formEntries,
  tags,
  tagClasses,
  tagClassAttributes
} from "@/db/schema/vendorData";
import { contractItems } from "@/db/schema/contract";
import { contracts } from "@/db/schema/contract";
import { projects } from "@/db/schema/projects";
import { vendors } from "@/db/schema/vendors";
import { eq, and, desc } from "drizzle-orm";
import type { DataTableColumnJSON } from "@/components/form-data/form-data-table-columns";

export interface VendorFormCompletionStats {
  vendorId: number;
  vendorName: string;
  contractItemId: number;
  formCode: string;
  formName: string;
  totalRequiredFields: number;
  totalFilledFields: number;
  totalEmptyFields: number;
  completionPercentage: number;
  tagCount: number;
  detailsByTag: Array<{
    tagNo: string;
    requiredFields: number;
    filledFields: number;
    emptyFields: number;
    completionPercentage: number;
  }>;
}

export interface ProjectVendorCompletionSummary {
  projectId: number;
  projectCode: string;
  projectName: string;
  vendors: VendorFormCompletionStats[];
  totalVendors: number;
  averageCompletionPercentage: number;
}

export interface VendorContractCompletionStats {
  contractId: number;
  contractItemId: number;
  projectId: number;
  projectCode: string;
  projectName: string;
  itemCode: string;
  itemName: string;
  forms: VendorFormCompletionStats[];
  totalForms: number;
  totalRequiredFields: number;
  totalFilledFields: number;
  totalEmptyFields: number;
  averageCompletionPercentage: number;
}

export interface VendorAllContractsCompletionSummary {
  vendorId: number;
  vendorName: string;
  contracts: VendorContractCompletionStats[];
  totalContracts: number;
  totalForms: number;
  totalTags: number;
  totalRequiredFields: number;
  totalFilledFields: number;
  totalEmptyFields: number;
  overallCompletionPercentage: number;
  projectBreakdown: Array<{
    projectId: number;
    projectCode: string;
    projectName: string;
    contractsCount: number;
    formsCount: number;
    completionPercentage: number;
  }>;
}

/**
 * 필드가 벤더에 의해 편집 가능한지 확인
 * SHI 값이 "BOTH" 또는 "IN"인 경우만 벤더가 편집 가능 (대소문자 무관)
 */
function isFieldEditableByVendor(column: DataTableColumnJSON): boolean {
  const shi = column.shi?.toString().toUpperCase();
  const isEditable = shi === "BOTH" || shi === "IN";
  console.log(`isFieldEditableByVendor - Key: ${column.key}, shi: ${column.shi}, upperShi: ${shi}, isEditable: ${isEditable}`);
  return isEditable;
}

/**
 * 특정 태그에 대해 편집 가능한 필드 목록을 가져옴
 */
async function getEditableFieldsForTag(
  tagNo: string,
  contractItemId: number,
  projectId: number
): Promise<string[]> {
  try {
    // 1. 해당 태그 정보 조회
    const tagResult = await db
      .select({
        tagClass: tags.class
      })
      .from(tags)
      .where(
        and(
          eq(tags.tagNo, tagNo),
          eq(tags.contractItemId, contractItemId)
        )
      )
      .limit(1);

    if (tagResult.length === 0) {
      console.log(`getEditableFieldsForTag - No tag found for tagNo: ${tagNo}, contractItemId: ${contractItemId}`);
      return [];
    }

    console.log(`getEditableFieldsForTag - Found tag for tagNo: ${tagNo}, class: ${tagResult[0].tagClass}`);

    // 2. tagClasses에서 해당 class와 projectId로 tagClass 찾기
    const tagClassResult = await db
      .select({ id: tagClasses.id })
      .from(tagClasses)
      .where(
        and(
          eq(tagClasses.label, tagResult[0].tagClass),
          eq(tagClasses.projectId, projectId)
        )
      )
      .limit(1);

    if (tagClassResult.length === 0) {
      console.log(`getEditableFieldsForTag - No tag class found for class: ${tagResult[0].tagClass}, projectId: ${projectId}`);
      return [];
    }

    console.log(`getEditableFieldsForTag - Found tag class: ${tagClassResult[0].id} for class: ${tagResult[0].tagClass}`);

    // 3. tagClassAttributes에서 편집 가능한 필드 목록 조회
    const editableAttributes = await db
      .select({ attId: tagClassAttributes.attId })
      .from(tagClassAttributes)
      .where(eq(tagClassAttributes.tagClassId, tagClassResult[0].id))
      .orderBy(tagClassAttributes.seq);

    console.log(`getEditableFieldsForTag - Found ${editableAttributes.length} editable attributes for tag class ${tagClassResult[0].id}:`, editableAttributes.map(attr => attr.attId));

    return editableAttributes.map(attr => attr.attId);
  } catch (error) {
    console.error(`Error getting editable fields for tag ${tagNo}:`, error);
    return [];
  }
}

/**
 * 값이 "빈" 값인지 확인
 */
function isEmptyValue(value: unknown): boolean {
  if (value === null || value === undefined) return true;
  if (typeof value === 'string') return value.trim() === '';
  if (typeof value === 'number') return isNaN(value);
  return false;
}

/**
 * 특정 contract item의 form에 대한 벤더 입력 완성도 계산
 */
export async function calculateVendorFormCompletion(
  contractItemId: number,
  formCode: string
): Promise<VendorFormCompletionStats | null> {
  try {
    // 1. Contract Item 정보 및 Vendor 정보 조회
    const contractInfo = await db
      .select({
        projectId: projects.id,
        projectCode: projects.code,
        projectName: projects.name,
        vendorId: vendors.id,
        vendorName: vendors.vendorName,
        contractId: contracts.id
      })
      .from(contractItems)
      .innerJoin(contracts, eq(contractItems.contractId, contracts.id))
      .innerJoin(projects, eq(contracts.projectId, projects.id))
      .innerJoin(vendors, eq(contracts.vendorId, vendors.id))
      .where(eq(contractItems.id, contractItemId))
      .limit(1);

    if (contractInfo.length === 0) {
      console.warn(`No contract item found with ID: ${contractItemId}`);
      return null;
    }

    const { projectId, vendorId, vendorName } = contractInfo[0];

    // 2. Form 메타데이터 조회 (컬럼 정의)
    const metaRows = await db
      .select()
      .from(formMetas)
      .where(eq(formMetas.formCode, formCode))
      .orderBy(desc(formMetas.updatedAt))
      .limit(1);

    const meta = metaRows[0];
    if (!meta) {
      console.warn(`No form meta found for formCode: ${formCode} and projectId: ${projectId}`);
      return null;
    }

    console.log(`calculateVendorFormCompletion - Found form meta for formCode: ${formCode}, projectId: ${projectId}, columns type: ${typeof meta.columns}, isArray: ${Array.isArray(meta.columns)}`);

    // 3. Form 실제 데이터 조회
    const entryRows = await db
      .select()
      .from(formEntries)
      .where(
        and(
          eq(formEntries.formCode, formCode),
          eq(formEntries.contractItemId, contractItemId)
        )
      )
      .orderBy(desc(formEntries.updatedAt))
      .limit(1);

    const entry = entryRows[0];
    if (!entry || !Array.isArray(entry.data)) {
      console.warn(`No form data found for formCode: ${formCode} and contractItemId: ${contractItemId}`);
      return null;
    }

    // 4. 컬럼 정의에서 벤더가 편집 가능한 필드 필터링
    const columns = meta.columns as DataTableColumnJSON[];
    const excludeKeys = ['BF_TAG_NO', 'TAG_TYPE_ID', 'PIC_NO', 'status'];
    const editableColumns = columns.filter(col =>
      !excludeKeys.includes(col.key) && isFieldEditableByVendor(col)
    );

    console.log(`calculateVendorFormCompletion - Total columns: ${columns.length}, Editable columns: ${editableColumns.length}`);
    console.log(`calculateVendorFormCompletion - Editable column keys:`, editableColumns.map(col => col.key));
    console.log(`calculateVendorFormCompletion - All column keys:`, columns.map(col => col.key));
    console.log(`calculateVendorFormCompletion - All column shi values:`, columns.map(col => col.shi));

    // 5. 각 태그별로 완성도 계산
    const detailsByTag: VendorFormCompletionStats['detailsByTag'] = [];
    let totalRequiredFields = 0;
    let totalFilledFields = 0;

    const formData = entry.data as Array<Record<string, unknown>>;

    for (const rowData of formData) {
      const tagNo = rowData.TAG_NO as string;
      if (!tagNo) continue;

      // Debug 페이지와 동일하게 직접 editableColumns 사용 (getEditableFieldsForTag 대신)
      const actualEditableFields = editableColumns;

      const requiredFieldsCount = actualEditableFields.length;
      let filledFieldsCount = 0;

      // 각 편집 가능한 필드의 값 확인
      for (const column of actualEditableFields) {
        const value = rowData[column.key];
        if (!isEmptyValue(value)) {
          filledFieldsCount++;
        }
      }

      const emptyFieldsCount = requiredFieldsCount - filledFieldsCount;
      const completionPercentage = requiredFieldsCount > 0
        ? Math.round((filledFieldsCount / requiredFieldsCount) * 100)
        : 100;

      detailsByTag.push({
        tagNo: tagNo as string,
        requiredFields: requiredFieldsCount,
        filledFields: filledFieldsCount,
        emptyFields: emptyFieldsCount,
        completionPercentage
      });

      totalRequiredFields += requiredFieldsCount;
      totalFilledFields += filledFieldsCount;
    }

    const totalEmptyFields = totalRequiredFields - totalFilledFields;
    const overallCompletionPercentage = totalRequiredFields > 0
      ? Math.round((totalFilledFields / totalRequiredFields) * 100)
      : 0; // Changed from 100 to 0 for no EDP data case

    return {
      vendorId,
      vendorName,
      contractItemId,
      formCode,
      formName: meta.formName,
      totalRequiredFields,
      totalFilledFields,
      totalEmptyFields,
      completionPercentage: overallCompletionPercentage,
      tagCount: formData.length,
      detailsByTag
    };

  } catch (error) {
    console.error(`Error calculating vendor form completion:`, error);
    return null;
  }
}

/**
 * 프로젝트의 모든 벤더들에 대한 특정 form의 입력 완성도 요약
 */
export async function getProjectVendorCompletionSummary(
  projectId: number,
  formCode: string
): Promise<ProjectVendorCompletionSummary | null> {
  try {
    // 1. 프로젝트 정보 조회
    const projectInfo = await db
      .select({
        id: projects.id,
        code: projects.code,
        name: projects.name
      })
      .from(projects)
      .where(eq(projects.id, projectId))
      .limit(1);

    if (projectInfo.length === 0) {
      console.warn(`No project found with ID: ${projectId}`);
      return null;
    }

    const project = projectInfo[0];

    // 2. 해당 프로젝트의 모든 contract items 조회 (formCode와 연관된)
    const contractItemsInfo = await db
      .select({
        contractItemId: contractItems.id,
        vendorId: vendors.id,
        vendorName: vendors.vendorName
      })
      .from(contractItems)
      .innerJoin(contracts, eq(contractItems.contractId, contracts.id))
      .innerJoin(vendors, eq(contracts.vendorId, vendors.id))
      .innerJoin(formEntries, and(
        eq(formEntries.contractItemId, contractItems.id),
        eq(formEntries.formCode, formCode)
      ))
      .where(eq(contracts.projectId, projectId));

    // 3. 각 contract item별로 완성도 계산
    const vendorStats: VendorFormCompletionStats[] = [];

    for (const item of contractItemsInfo) {
      const stats = await calculateVendorFormCompletion(
        item.contractItemId,
        formCode
      );

      if (stats) {
        vendorStats.push(stats);
      }
    }

    // 4. 전체 평균 완성도 계산
    const averageCompletionPercentage = vendorStats.length > 0
      ? Math.round(
        vendorStats.reduce((sum, stat) => sum + stat.completionPercentage, 0) / vendorStats.length
      )
      : 0;

    return {
      projectId: project.id,
      projectCode: project.code,
      projectName: project.name,
      vendors: vendorStats,
      totalVendors: vendorStats.length,
      averageCompletionPercentage
    };

  } catch (error) {
    console.error(`Error getting project vendor completion summary:`, error);
    return null;
  }
}

/**
 * 특정 벤더의 특정 계약(contract item)에 대한 모든 form 완성도 계산
 */
export async function calculateVendorContractCompletion(
  vendorId: number,
  contractItemId: number
): Promise<VendorContractCompletionStats | null> {
  try {
    // 1. Contract Item 정보 조회
    const contractItemInfo = await db
      .select({
        contractId: contracts.id,
        contractItemId: contractItems.id,
        projectId: projects.id,
        projectCode: projects.code,
        projectName: projects.name,
        itemId: contractItems.itemId,
        description: contractItems.description,
        vendorId: vendors.id,
        vendorName: vendors.vendorName
      })
      .from(contractItems)
      .innerJoin(contracts, eq(contractItems.contractId, contracts.id))
      .innerJoin(projects, eq(contracts.projectId, projects.id))
      .innerJoin(vendors, eq(contracts.vendorId, vendors.id))
      .where(
        and(
          eq(contractItems.id, contractItemId),
          eq(vendors.id, vendorId)
        )
      )
      .limit(1);

    if (contractItemInfo.length === 0) {
      console.warn(`No contract item found for vendorId: ${vendorId}, contractItemId: ${contractItemId}`);
      return null;
    }

    const contractInfo = contractItemInfo[0];

    // 2. 해당 contract item과 연관된 모든 form codes 조회
    const formCodes = await db
      .selectDistinct({
        formCode: formEntries.formCode
      })
      .from(formEntries)
      .where(eq(formEntries.contractItemId, contractItemId));

    // 3. 각 form에 대한 완성도 계산
    const formStats: VendorFormCompletionStats[] = [];
    let totalRequiredFields = 0;
    let totalFilledFields = 0;

    for (const { formCode } of formCodes) {
      const formCompletion = await calculateVendorFormCompletion(contractItemId, formCode);
      if (formCompletion) {
        formStats.push(formCompletion);
        totalRequiredFields += formCompletion.totalRequiredFields;
        totalFilledFields += formCompletion.totalFilledFields;
      }
    }

    const totalEmptyFields = totalRequiredFields - totalFilledFields;
    const averageCompletionPercentage = totalRequiredFields > 0
      ? Math.round((totalFilledFields / totalRequiredFields) * 100)
      : 0; // Changed to 0 for no EDP data case

    return {
      contractId: contractInfo.contractId,
      contractItemId: contractInfo.contractItemId,
      projectId: contractInfo.projectId,
      projectCode: contractInfo.projectCode,
      projectName: contractInfo.projectName,
      itemCode: contractInfo.itemId?.toString() || '',
      itemName: contractInfo.description || '',
      forms: formStats,
      totalForms: formStats.length,
      totalRequiredFields,
      totalFilledFields,
      totalEmptyFields,
      averageCompletionPercentage
    };

  } catch (error) {
    console.error(`Error calculating vendor contract completion:`, error);
    return null;
  }
}

/**
 * 특정 벤더가 보유한 모든 계약에 대한 입력 완성도 요약
 */
export async function getVendorAllContractsCompletionSummary(
  vendorId: number
): Promise<VendorAllContractsCompletionSummary | null> {
  try {
    // 1. 벤더 정보 조회
    const vendorInfo = await db
      .select({
        id: vendors.id,
        vendorName: vendors.vendorName
      })
      .from(vendors)
      .where(eq(vendors.id, vendorId))
      .limit(1);

    if (vendorInfo.length === 0) {
      console.warn(`No vendor found with ID: ${vendorId}`);
      return null;
    }

    const vendor = vendorInfo[0];

    // 2. 해당 벤더의 모든 contract items 조회
    const contractItemsInfo = await db
      .select({
        contractId: contracts.id,
        contractItemId: contractItems.id,
        projectId: projects.id,
        projectCode: projects.code,
        projectName: projects.name,
        itemId: contractItems.itemId,
        description: contractItems.description
      })
      .from(contractItems)
      .innerJoin(contracts, eq(contractItems.contractId, contracts.id))
      .innerJoin(projects, eq(contracts.projectId, projects.id))
      .where(eq(contracts.vendorId, vendorId));

    // 3. 각 contract item별로 완성도 계산
    const contractStats: VendorContractCompletionStats[] = [];

    for (const item of contractItemsInfo) {
      console.log(`getVendorAllContractsCompletionSummary - Processing contract item: ${item.contractItemId} for vendor: ${vendorId}`);
      const contractCompletion = await calculateVendorContractCompletion(
        vendorId,
        item.contractItemId
      );

      if (contractCompletion) {
        console.log(`getVendorAllContractsCompletionSummary - Contract completion for item ${item.contractItemId}:`, {
          totalRequiredFields: contractCompletion.totalRequiredFields,
          totalFilledFields: contractCompletion.totalFilledFields,
          totalForms: contractCompletion.totalForms
        });
        contractStats.push(contractCompletion);
      } else {
        console.log(`getVendorAllContractsCompletionSummary - No contract completion for item: ${item.contractItemId}`);
      }
    }

    // 4. 전체 통계 계산
    const totalRequiredFields = contractStats.reduce((sum, stat) => sum + stat.totalRequiredFields, 0);
    const totalFilledFields = contractStats.reduce((sum, stat) => sum + stat.totalFilledFields, 0);
    const totalEmptyFields = totalRequiredFields - totalFilledFields;
    const totalForms = contractStats.reduce((sum, stat) => sum + stat.totalForms, 0);
    const totalTags = contractStats.reduce((sum, stat) =>
      sum + stat.forms.reduce((formSum, form) => formSum + form.tagCount, 0), 0
    );

    const overallCompletionPercentage = totalRequiredFields > 0
      ? Math.round((totalFilledFields / totalRequiredFields) * 100)
      : 0; // Changed from 100 to 0 for no EDP data case

    // 5. 프로젝트별 요약 계산
    const projectMap = new Map<number, {
      projectId: number;
      projectCode: string;
      projectName: string;
      contracts: VendorContractCompletionStats[];
    }>();

    contractStats.forEach(contract => {
      const key = contract.projectId;
      if (!projectMap.has(key)) {
        projectMap.set(key, {
          projectId: contract.projectId,
          projectCode: contract.projectCode,
          projectName: contract.projectName,
          contracts: []
        });
      }
      projectMap.get(key)!.contracts.push(contract);
    });

    const projectBreakdown = Array.from(projectMap.values()).map(project => {
      const projectTotalRequired = project.contracts.reduce((sum, c) => sum + c.totalRequiredFields, 0);
      const projectTotalFilled = project.contracts.reduce((sum, c) => sum + c.totalFilledFields, 0);
      const projectCompletionPercentage = projectTotalRequired > 0
        ? Math.round((projectTotalFilled / projectTotalRequired) * 100)
        : 0; // Changed from 100 to 0 for no EDP data case

      return {
        projectId: project.projectId,
        projectCode: project.projectCode,
        projectName: project.projectName,
        contractsCount: project.contracts.length,
        formsCount: project.contracts.reduce((sum, c) => sum + c.totalForms, 0),
        completionPercentage: projectCompletionPercentage
      };
    });

    return {
      vendorId: vendor.id,
      vendorName: vendor.vendorName,
      contracts: contractStats,
      totalContracts: contractStats.length,
      totalForms,
      totalTags,
      totalRequiredFields,
      totalFilledFields,
      totalEmptyFields,
      overallCompletionPercentage,
      projectBreakdown
    };

  } catch (error) {
    console.error(`Error getting vendor all contracts completion summary:`, error);
    return null;
  }
}

/**
 * 모든 프로젝트의 모든 form에 대한 벤더 완성도 요약 (관리자용)
 */
export async function getAllProjectsVendorCompletionSummary(): Promise<{
  projects: ProjectVendorCompletionSummary[];
  totalProjects: number;
  overallAverageCompletion: number;
}> {
  try {
    // 1. 모든 프로젝트 조회
    const allProjects = await db
      .select({
        id: projects.id,
        code: projects.code,
        name: projects.name
      })
      .from(projects);

    // 2. 각 프로젝트별로 form들의 완성도 조회
    const projectSummaries: ProjectVendorCompletionSummary[] = [];

    for (const project of allProjects) {
      // 해당 프로젝트의 모든 form codes 조회
      const formCodes = await db
        .selectDistinct({
          formCode: formMetas.formCode
        })
        .from(formMetas)
        .where(eq(formMetas.projectId, project.id));

      // 각 form에 대한 완성도 조회 후 통합
      const allVendorStats: VendorFormCompletionStats[] = [];

      for (const { formCode } of formCodes) {
        const summary = await getProjectVendorCompletionSummary(project.id, formCode);
        if (summary) {
          allVendorStats.push(...summary.vendors);
        }
      }

      if (allVendorStats.length > 0) {
        const averageCompletion = Math.round(
          allVendorStats.reduce((sum, stat) => sum + stat.completionPercentage, 0) / allVendorStats.length
        );

        projectSummaries.push({
          projectId: project.id,
          projectCode: project.code,
          projectName: project.name,
          vendors: allVendorStats,
          totalVendors: allVendorStats.length,
          averageCompletionPercentage: averageCompletion
        });
      }
    }

    // 3. 전체 평균 계산
    const overallAverageCompletion = projectSummaries.length > 0
      ? Math.round(
        projectSummaries.reduce((sum, proj) => sum + proj.averageCompletionPercentage, 0) / projectSummaries.length
      )
      : 0;

    return {
      projects: projectSummaries,
      totalProjects: projectSummaries.length,
      overallAverageCompletion
    };

  } catch (error) {
    console.error(`Error getting all projects vendor completion summary:`, error);
    return {
      projects: [],
      totalProjects: 0,
      overallAverageCompletion: 0
    };
  }
}

/**
 * 특정 벤더의 필드 계산 상세 정보를 디버깅용으로 반환
 */
export async function debugVendorFieldCalculation(vendorId: number): Promise<{
  vendorId: number;
  vendorName: string;
  debugInfo: {
    contracts: Array<{
      contractId: number;
      contractItemId: number;
      projectName: string;
      forms: Array<{
        formCode: string;
        formName: string;
        tags: Array<{
          tagNo: string;
          editableFields: string[];
          requiredFieldsCount: number;
          filledFieldsCount: number;
          fieldDetails: Array<{
            fieldKey: string;
            fieldValue: unknown;
            isEmpty: boolean;
          }>;
        }>;
        totalRequiredFields: number;
        totalFilledFields: number;
      }>;
      totalRequiredFields: number;
      totalFilledFields: number;
    }>;
    grandTotal: {
      totalRequiredFields: number;
      totalFilledFields: number;
      totalEmptyFields: number;
      completionPercentage: number;
    };
  };
} | null> {
  try {
    // 1. 벤더 정보 조회
    const vendorInfo = await db
      .select({
        id: vendors.id,
        vendorName: vendors.vendorName
      })
      .from(vendors)
      .where(eq(vendors.id, vendorId))
      .limit(1);

    if (vendorInfo.length === 0) {
      console.warn(`No vendor found with ID: ${vendorId}`);
      return null;
    }

    const vendor = vendorInfo[0];

    // 2. 해당 벤더의 모든 contract items 조회
    const contractItemsInfo = await db
      .select({
        contractId: contracts.id,
        contractItemId: contractItems.id,
        projectId: projects.id,
        projectCode: projects.code,
        projectName: projects.name,
        itemId: contractItems.itemId,
        description: contractItems.description
      })
      .from(contractItems)
      .innerJoin(contracts, eq(contractItems.contractId, contracts.id))
      .innerJoin(projects, eq(contracts.projectId, projects.id))
      .where(eq(contracts.vendorId, vendorId));

    const debugContracts = [];

    for (const item of contractItemsInfo) {
      // 3. 해당 contract item과 연관된 모든 form codes 조회
      const formCodes = await db
        .selectDistinct({
          formCode: formEntries.formCode
        })
        .from(formEntries)
        .where(eq(formEntries.contractItemId, item.contractItemId));

      const debugForms = [];
      let contractTotalRequired = 0;
      let contractTotalFilled = 0;

      for (const { formCode } of formCodes) {
        // 4. Form 메타데이터 조회
        const metaRows = await db
          .select()
          .from(formMetas)
          .where(eq(formMetas.formCode, formCode))
          .orderBy(desc(formMetas.updatedAt))
          .limit(1);

        const meta = metaRows[0];
        if (!meta) {
          console.log(`No form meta found for formCode: ${formCode}, projectId: ${item.projectId}`);
          continue;
        }

        console.log(`Found form meta for formCode: ${formCode}, projectId: ${item.projectId}, columns type: ${typeof meta.columns}, isArray: ${Array.isArray(meta.columns)}`);

        // 5. Form 실제 데이터 조회
        const entryRows = await db
          .select()
          .from(formEntries)
          .where(
            and(
              eq(formEntries.formCode, formCode),
              eq(formEntries.contractItemId, item.contractItemId)
            )
          )
          .orderBy(desc(formEntries.updatedAt))
          .limit(1);

        const entry = entryRows[0];
        if (!entry || !Array.isArray(entry.data)) continue;

        // 6. 컬럼 정의에서 벤더가 편집 가능한 필드 필터링
        const columns = meta.columns as DataTableColumnJSON[];
        const excludeKeys = ['BF_TAG_NO', 'TAG_TYPE_ID', 'PIC_NO', 'status'];
        const editableColumns = columns.filter(col =>
          !excludeKeys.includes(col.key) && isFieldEditableByVendor(col)
        );

        const debugTags = [];
        let formTotalRequired = 0;
        let formTotalFilled = 0;

        const formData = entry.data as Array<Record<string, unknown>>;

        for (const rowData of formData) {
          const tagNo = rowData.TAG_NO as string;
          if (!tagNo) continue;

          // 직접 editableColumns 사용 (getEditableFieldsForTag 대신)
          const actualEditableFields = editableColumns;

          const requiredFieldsCount = actualEditableFields.length;
          let filledFieldsCount = 0;

          const fieldDetails = [];
          // 각 편집 가능한 필드의 값 확인
          for (const column of actualEditableFields) {
            const value = rowData[column.key];
            const isEmpty = isEmptyValue(value);
            if (!isEmpty) {
              filledFieldsCount++;
            }
            fieldDetails.push({
              fieldKey: column.key,
              fieldValue: value,
              isEmpty
            });
          }

          debugTags.push({
            tagNo,
            editableFields: actualEditableFields.map(col => col.key),
            requiredFieldsCount,
            filledFieldsCount,
            fieldDetails
          });

          formTotalRequired += requiredFieldsCount;
          formTotalFilled += filledFieldsCount;
        }

        debugForms.push({
          formCode,
          formName: meta.formName,
          tags: debugTags,
          totalRequiredFields: formTotalRequired,
          totalFilledFields: formTotalFilled
        });

        contractTotalRequired += formTotalRequired;
        contractTotalFilled += formTotalFilled;
      }

      debugContracts.push({
        contractId: item.contractId,
        contractItemId: item.contractItemId,
        projectName: item.projectName,
        forms: debugForms,
        totalRequiredFields: contractTotalRequired,
        totalFilledFields: contractTotalFilled
      });
    }

    // 전체 합계 계산
    const grandTotalRequired = debugContracts.reduce((sum, contract) => sum + contract.totalRequiredFields, 0);
    const grandTotalFilled = debugContracts.reduce((sum, contract) => sum + contract.totalFilledFields, 0);
    const grandTotalEmpty = grandTotalRequired - grandTotalFilled;
    const grandCompletionPercentage = grandTotalRequired > 0
      ? Math.round((grandTotalFilled / grandTotalRequired) * 100)
      : 0; // Changed from 100 to 0 for no EDP data case

    return {
      vendorId: vendor.id,
      vendorName: vendor.vendorName,
      debugInfo: {
        contracts: debugContracts,
        grandTotal: {
          totalRequiredFields: grandTotalRequired,
          totalFilledFields: grandTotalFilled,
          totalEmptyFields: grandTotalEmpty,
          completionPercentage: grandCompletionPercentage
        }
      }
    };

  } catch (error) {
    console.error(`Error debugging vendor field calculation:`, error);
    return null;
  }
}

/**
 * 모든 벤더들의 전체 계약 완성도 요약 (관리자용)
 */
export async function getAllVendorsContractsCompletionSummary(): Promise<{
  vendors: VendorAllContractsCompletionSummary[];
  totalVendors: number;
  overallAverageCompletion: number;
  topPerformingVendors: Array<{
    vendorId: number;
    vendorName: string;
    completionPercentage: number;
  }>;
  lowPerformingVendors: Array<{
    vendorId: number;
    vendorName: string;
    completionPercentage: number;
  }>;
}> {
  try {
    // 1. 계약이 있는 모든 벤더 조회
    const vendorsWithContracts = await db
      .selectDistinct({
        vendorId: vendors.id,
        vendorName: vendors.vendorName
      })
      .from(vendors)
      .innerJoin(contracts, eq(contracts.vendorId, vendors.id))
      .innerJoin(contractItems, eq(contractItems.contractId, contracts.id));

    // 2. 각 벤더별로 완성도 계산
    const vendorSummaries: VendorAllContractsCompletionSummary[] = [];

    for (const vendor of vendorsWithContracts) {
      console.log(`getAllVendorsContractsCompletionSummary - Processing vendor: ${vendor.vendorId} (${vendor.vendorName})`);
      const summary = await getVendorAllContractsCompletionSummary(vendor.vendorId);
      if (summary && summary.totalRequiredFields > 0) { // Only include vendors with EDP data
        console.log(`getAllVendorsContractsCompletionSummary - Vendor ${vendor.vendorId} summary:`, {
          totalRequiredFields: summary.totalRequiredFields,
          totalFilledFields: summary.totalFilledFields,
          totalTags: summary.totalTags,
          totalForms: summary.totalForms
        });
        vendorSummaries.push(summary);
      } else {
        console.log(`getAllVendorsContractsCompletionSummary - No EDP data for vendor: ${vendor.vendorId}`);
      }
    }

    // 3. 전체 평균 계산
    const overallAverageCompletion = vendorSummaries.length > 0
      ? Math.round(
        vendorSummaries.reduce((sum, vendor) => sum + vendor.overallCompletionPercentage, 0) / vendorSummaries.length
      )
      : 0;

    // 4. 상위/하위 성과 벤더 추출 (상위 5개, 하위 5개)
    const sortedVendors = [...vendorSummaries].sort((a, b) => b.overallCompletionPercentage - a.overallCompletionPercentage);

    const topPerformingVendors = sortedVendors.slice(0, 5).map(vendor => ({
      vendorId: vendor.vendorId,
      vendorName: vendor.vendorName,
      completionPercentage: vendor.overallCompletionPercentage
    }));

    const lowPerformingVendors = sortedVendors.slice(-5).reverse().map(vendor => ({
      vendorId: vendor.vendorId,
      vendorName: vendor.vendorName,
      completionPercentage: vendor.overallCompletionPercentage
    }));

    return {
      vendors: vendorSummaries,
      totalVendors: vendorSummaries.length,
      overallAverageCompletion,
      topPerformingVendors,
      lowPerformingVendors
    };

  } catch (error) {
    console.error(`Error getting all vendors contracts completion summary:`, error);
    return {
      vendors: [],
      totalVendors: 0,
      overallAverageCompletion: 0,
      topPerformingVendors: [],
      lowPerformingVendors: []
    };
  }
}