(대표님) EDP 해양 관련 개발 사항들

3 files changed, 2673 insertions, 0 deletions

diff --git a/lib/forms-plant/sedp-actions.ts b/lib/forms-plant/sedp-actions.ts
new file mode 100644
index 00000000..4883a33f
--- /dev/null
+++ b/lib/forms-plant/sedp-actions.ts
@@ -0,0 +1,222 @@
+"use server";
+
+import { getSEDPToken } from "@/lib/sedp/sedp-token";
+
+interface SEDPTagData {
+  [tableName: string]: Array<{
+    TAG_NO: string;
+    TAG_DESC: string;
+    ATTRIBUTES: Array<{
+      ATT_ID: string;
+      VALUE: string;
+    }>;
+  }>;
+}
+
+interface SEDPTemplateData {
+  templateId: string;
+  content: string;
+  projectNo: string;
+  regTypeId: string;
+  [key: string]: any;
+}
+
+// 🔍 실제 SEDP API 응답 구조 (대문자)
+interface SEDPTemplateResponse {
+  TMPL_ID: string;
+  NAME: string;
+  TMPL_TYPE: string;
+  SPR_LST_SETUP?: {
+    ACT_SHEET: string;
+    HIDN_SHEETS: string[];
+    CONTENT?: string;
+    DATA_SHEETS: Array<{
+      SHEET_NAME: string;
+      REG_TYPE_ID: string;
+      MAP_CELL_ATT: Array<{
+        ATT_ID: string;
+        IN: string;
+      }>;
+    }>;
+  };
+  GRD_LST_SETUP?: {
+    REG_TYPE_ID: string;
+    SPR_ITM_IDS: string[];
+    ATTS: any[];
+  };
+  SPR_ITM_LST_SETUP?: {
+    ACT_SHEET: string;
+    HIDN_SHEETS: string[];
+    CONTENT?: string;
+    DATA_SHEETS: Array<{
+      SHEET_NAME: string;
+      REG_TYPE_ID: string;
+      MAP_CELL_ATT: Array<{
+        ATT_ID: string;
+        IN: string;
+      }>;
+    }>;
+  };
+  [key: string]: any;
+}
+
+/**
+ * SEDP에서 태그 데이터를 가져오는 서버 액션
+ */
+export async function fetchTagDataFromSEDP(
+  projectCode: string, 
+  formCode: string
+): Promise<SEDPTagData> {
+  try {
+    // Get the token
+    const apiKey = await getSEDPToken();
+
+    // Define the API base URL
+    const SEDP_API_BASE_URL = process.env.SEDP_API_BASE_URL || 'http://sedpwebapi.ship.samsung.co.kr/api';
+
+    // Make the API call
+    const response = await fetch(
+      `${SEDP_API_BASE_URL}/Data/GetPubData`,
+      {
+        method: 'POST',
+        headers: {
+          'Content-Type': 'application/json',
+          'accept': '*/*',
+          'ApiKey': apiKey,
+          'ProjectNo': projectCode
+        },
+        body: JSON.stringify({
+          ProjectNo: projectCode,
+          REG_TYPE_ID: formCode,
+          ContainDeleted: false
+        })
+      }
+    );
+
+    if (!response.ok) {
+      const errorText = await response.text();
+      throw new Error(`SEDP API request failed: ${response.status} ${response.statusText} - ${errorText}`);
+    }
+
+    const data = await response.json();
+    return data as SEDPTagData;
+  } catch (error: unknown) {
+    console.error('Error calling SEDP API:', error);
+    const errorMessage = error instanceof Error ? error.message : 'Unknown error';
+    throw new Error(`Failed to fetch data from SEDP API: ${errorMessage}`);
+  }
+}
+
+/**
+ * SEDP에서 템플릿 데이터를 가져오는 서버 액션
+ */
+export async function fetchTemplateFromSEDP(
+  projectCode: string, 
+  formCode: string
+): Promise<SEDPTemplateResponse[]> {
+  try {
+    // Get the token
+    const apiKey = await getSEDPToken();
+
+    // Define the API base URL
+    const SEDP_API_BASE_URL = process.env.SEDP_API_BASE_URL || 'http://sedpwebapi.ship.samsung.co.kr/api';
+
+    const responseAdapter = await fetch(
+      `${SEDP_API_BASE_URL}/AdapterDataMapping/GetByToolID`,
+      {
+        method: 'POST',
+        headers: {
+          'Content-Type': 'application/json',
+          'accept': '*/*',
+          'ApiKey': apiKey,
+          'ProjectNo': projectCode
+        },
+        body: JSON.stringify({
+          ProjectNo: projectCode,
+          "TOOL_ID": "eVCP"
+        })
+      }
+    );
+
+    if (!responseAdapter.ok) {
+      throw new Error(`새 레지스터 요청 실패: ${responseAdapter.status} ${responseAdapter.statusText}`);
+    }
+
+    const dataAdapter = await responseAdapter.json();
+    const templateList = dataAdapter.find(v => v.REG_TYPE_ID === formCode)?.MAP_TMPLS || [];
+
+    // 각 TMPL_ID에 대해 API 호출
+    const templatePromises = templateList.map(async (tmplId: string) => {
+      const response = await fetch(
+        `${SEDP_API_BASE_URL}/Template/GetByID`,
+        {
+          method: 'POST',
+          headers: {
+            'Content-Type': 'application/json',
+            'accept': '*/*',
+            'ApiKey': apiKey,
+            'ProjectNo': projectCode
+          },
+          body: JSON.stringify({
+            WithContent: true,
+            ProjectNo: projectCode,
+            TMPL_ID: tmplId
+          })
+        }
+      );
+
+      if (!response.ok) {
+        const errorText = await response.text();
+        throw new Error(`SEDP Template API request failed for TMPL_ID ${tmplId}: ${response.status} ${response.statusText} - ${errorText}`);
+      }
+
+      const data = await response.json();
+      
+      // 🔍 API 응답 데이터 구조 확인 및 로깅
+      console.log('🔍 SEDP Template API Response for', tmplId, ':', {
+        hasTMPL_ID: !!data.TMPL_ID,
+        hasTemplateId: !!(data as any).templateId,
+        keys: Object.keys(data),
+        sample: data
+      });
+      
+      // 🔍 TMPL_ID 필드 검증
+      if (!data.TMPL_ID) {
+        console.error('❌ Missing TMPL_ID in API response:', data);
+        // templateId가 있다면 변환 시도
+        if ((data as any).templateId) {
+          console.warn('⚠️ Found templateId instead of TMPL_ID, converting...');
+          data.TMPL_ID = (data as any).templateId;
+        }
+      }
+      
+      return data as SEDPTemplateResponse;
+    });
+
+    // 모든 API 호출을 병렬로 실행하고 결과를 수집
+    const templates = await Promise.all(templatePromises);
+    
+    // 🔍 null이나 undefined가 아닌 값들만 필터링하고 TMPL_ID 검증
+    const validTemplates = templates.filter(template => {
+      if (!template) {
+        console.warn('⚠️ Null or undefined template received');
+        return false;
+      }
+      if (!template.TMPL_ID) {
+        console.error('❌ Template missing TMPL_ID:', template);
+        return false;
+      }
+      return true;
+    });
+    
+    console.log(`✅ fetchTemplateFromSEDP completed: ${validTemplates.length} valid templates`);
+    validTemplates.forEach(t => console.log(`  - ${t.TMPL_ID}: ${t.NAME} (${t.TMPL_TYPE})`));
+    
+    return validTemplates;
+    
+  } catch (error: unknown) {
+    console.error('Error calling SEDP Template API:', error);
+    const errorMessage = error instanceof Error ? error.message : 'Unknown error';
+    throw new Error(`Failed to fetch template from SEDP API: ${errorMessage}`);
+  }
+}
\ No newline at end of file
diff --git a/lib/forms-plant/services.ts b/lib/forms-plant/services.ts
new file mode 100644
index 00000000..99e7c35b
--- /dev/null
+++ b/lib/forms-plant/services.ts
@@ -0,0 +1,2076 @@
+// lib/forms/services.ts
+"use server";
+
+import { headers } from "next/headers";
+import path from "path";
+import fs from "fs/promises";
+import { v4 as uuidv4 } from "uuid";
+import db from "@/db/db";
+import {
+  formEntries,
+  formMetas,
+  forms,
+  tagClassAttributes,
+  tagClasses,
+  tags,
+  tagSubfieldOptions,
+  tagSubfields,
+  tagTypeClassFormMappings,
+  tagTypes,
+  vendorDataReportTemps,
+  VendorDataReportTemps,
+} from "@/db/schema/vendorData";
+import { eq, and, desc, sql, DrizzleError, inArray, or, type SQL, type InferSelectModel } from "drizzle-orm";
+import { unstable_cache } from "next/cache";
+import { revalidateTag } from "next/cache";
+import { getErrorMessage } from "../handle-error";
+import { DataTableColumnJSON } from "@/components/form-data/form-data-table-columns";
+import { contractItems, contracts, items, projects } from "@/db/schema";
+import { getSEDPToken } from "../sedp/sedp-token";
+import { decryptWithServerAction } from "@/components/drm/drmUtils";
+import { deleteFile, saveFile } from "@/lib/file-stroage";
+
+
+export type FormInfo = InferSelectModel<typeof forms>;
+
+export async function getFormsByContractItemId(
+  contractItemId: number | null,
+  mode: "ENG" | "IM" | "ALL" = "ALL"
+): Promise<{ forms: FormInfo[] }> {
+  // 유효성 검사
+  if (!contractItemId || contractItemId <= 0) {
+    console.warn(`Invalid contractItemId: ${contractItemId}`);
+    return { forms: [] };
+  }
+
+  // 고유 캐시 키 (모드 포함)
+  const cacheKey = `forms-${contractItemId}-${mode}`;
+
+  try {
+    // return unstable_cache(
+    //   async () => {
+    //     console.log(
+    //       `[Forms Service] Fetching forms for contractItemId: ${contractItemId}, mode: ${mode}`
+    //     );
+
+    try {
+      // 쿼리 생성
+      let query = db.select().from(forms).where(eq(forms.contractItemId, contractItemId));
+
+      // 모드에 따른 추가 필터
+      if (mode === "ENG") {
+        query = db.select().from(forms).where(
+          and(
+            eq(forms.contractItemId, contractItemId),
+            eq(forms.eng, true)
+          )
+        );
+      } else if (mode === "IM") {
+        query = db.select().from(forms).where(
+          and(
+            eq(forms.contractItemId, contractItemId),
+            eq(forms.im, true)
+          )
+        );
+      }
+
+      // 쿼리 실행
+      const formRecords = await query;
+
+      console.log(
+        `[Forms Service] Found ${formRecords.length} forms for contractItemId: ${contractItemId}, mode: ${mode}`
+      );
+
+      return { forms: formRecords };
+    } catch (error) {
+      getErrorMessage(
+        `Database error for contractItemId ${contractItemId}, mode: ${mode}: ${error}`
+      );
+      throw error; // 캐시 함수에서 에러를 던져 캐싱이 발생하지 않도록 함
+    }
+    //   },
+    //   [cacheKey],
+    //   {
+    //     // 캐시 시간 단축
+    //     revalidate: 60, // 1분으로 줄임
+    //     tags: [cacheKey],
+    //   }
+    // )();
+  } catch (error) {
+    getErrorMessage(
+      `Cache operation failed for contractItemId ${contractItemId}, mode: ${mode}: ${error}`
+    );
+
+    // 캐시 문제 시 직접 쿼리 시도
+    try {
+      console.log(
+        `[Forms Service] Fallback: Direct query for contractItemId: ${contractItemId}, mode: ${mode}`
+      );
+
+      // 쿼리 생성
+      let query = db.select().from(forms).where(eq(forms.contractItemId, contractItemId));
+
+      // 모드에 따른 추가 필터
+      if (mode === "ENG") {
+        query = db.select().from(forms).where(
+          and(
+            eq(forms.contractItemId, contractItemId),
+            eq(forms.eng, true)
+          )
+        );
+      } else if (mode === "IM") {
+        query = db.select().from(forms).where(
+          and(
+            eq(forms.contractItemId, contractItemId),
+            eq(forms.im, true)
+          )
+        );
+      }
+
+      // 쿼리 실행
+      const formRecords = await query;
+
+      return { forms: formRecords };
+    } catch (dbError) {
+      getErrorMessage(
+        `Fallback query failed for contractItemId ${contractItemId}, mode: ${mode}: ${dbError}`
+      );
+      return { forms: [] };
+    }
+  }
+}
+
+/**
+ * 폼 캐시를 갱신하는 서버 액션
+ */
+export async function revalidateForms(contractItemId: number) {
+  if (!contractItemId) return;
+
+  const cacheKey = `forms-${contractItemId}`;
+  console.log(`[Forms Service] Invalidating cache for ${cacheKey}`);
+
+  try {
+    revalidateTag(cacheKey);
+    console.log(`[Forms Service] Cache invalidated for ${cacheKey}`);
+  } catch (error) {
+    getErrorMessage(`Failed to invalidate cache for ${cacheKey}: ${error}`);
+  }
+}
+
+export interface EditableFieldsInfo {
+  tagNo: string;
+  editableFields: string[]; // 편집 가능한 필드 키 목록
+}
+
+// TAG별 편집 가능 필드 조회 함수
+export async function getEditableFieldsByTag(
+  contractItemId: number,
+  projectId: number
+): Promise<Map<string, string[]>> {
+  try {
+    // 1. 해당 contractItemId의 모든 태그 조회
+    const tagList = await db
+      .select({
+        tagNo: tags.tagNo,
+        tagClass: tags.class
+      })
+      .from(tags)
+      .where(eq(tags.contractItemId, contractItemId));
+
+    const editableFieldsMap = new Map<string, string[]>();
+
+    // 2. 각 태그별로 편집 가능 필드 계산
+    for (const tag of tagList) {
+      try {
+        // 2-1. tagClasses에서 해당 class(label)와 projectId로 tagClass 찾기
+        const tagClassResult = await db
+          .select({ id: tagClasses.id })
+          .from(tagClasses)
+          .where(
+            and(
+              eq(tagClasses.label, tag.tagClass),
+              eq(tagClasses.projectId, projectId)
+            )
+          )
+          .limit(1);
+
+        if (tagClassResult.length === 0) {
+          console.warn(`No tagClass found for class: ${tag.tagClass}, projectId: ${projectId}`);
+          editableFieldsMap.set(tag.tagNo, []); // 편집 불가능
+          continue;
+        }
+
+        // 2-2. tagClassAttributes에서 편집 가능한 필드 목록 조회
+        const editableAttributes = await db
+          .select({ attId: tagClassAttributes.attId })
+          .from(tagClassAttributes)
+          .where(eq(tagClassAttributes.tagClassId, tagClassResult[0].id))
+          .orderBy(tagClassAttributes.seq);
+
+        // 2-3. attId 목록 저장
+        const editableFields = editableAttributes.map(attr => attr.attId);
+        editableFieldsMap.set(tag.tagNo, editableFields);
+
+      } catch (error) {
+        console.error(`Error processing tag ${tag.tagNo}:`, error);
+        editableFieldsMap.set(tag.tagNo, []); // 에러 시 편집 불가능
+      }
+    }
+
+    return editableFieldsMap;
+  } catch (error) {
+    console.error('Error getting editable fields by tag:', error);
+    return new Map();
+  }
+}
+/**
+ * "가장 최신 1개 row"를 가져오고,
+ * data가 배열이면 그 배열을 반환,
+ * 그리고 이 로직 전체를 unstable_cache로 감싸 캐싱.
+ */
+export async function getFormData(formCode: string, contractItemId: number) {
+  try {
+
+    // 기존 로직으로 projectId, columns, data 가져오기
+    const contractItemResult = await db
+      .select({
+        projectId: projects.id
+      })
+      .from(contractItems)
+      .innerJoin(contracts, eq(contractItems.contractId, contracts.id))
+      .innerJoin(projects, eq(contracts.projectId, projects.id))
+      .where(eq(contractItems.id, contractItemId))
+      .limit(1);
+
+    if (contractItemResult.length === 0) {
+      console.warn(`[getFormData] No contract item found with ID: ${contractItemId}`);
+      return { columns: null, data: [], editableFieldsMap: new Map() };
+    }
+
+    const projectId = contractItemResult[0].projectId;
+
+    const metaRows = await db
+      .select()
+      .from(formMetas)
+      .where(
+        and(
+          eq(formMetas.formCode, formCode),
+          eq(formMetas.projectId, projectId)
+        )
+      )
+      .orderBy(desc(formMetas.updatedAt))
+      .limit(1);
+
+    const meta = metaRows[0] ?? null;
+    if (!meta) {
+      console.warn(`[getFormData] No form meta found for formCode: ${formCode} and projectId: ${projectId}`);
+      return { columns: null, data: [], editableFieldsMap: new Map() };
+    }
+
+    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] ?? null;
+
+    let columns = meta.columns as DataTableColumnJSON[];
+    const excludeKeys = ['BF_TAG_NO', 'TAG_TYPE_ID', 'PIC_NO'];
+    columns = columns.filter(col => !excludeKeys.includes(col.key));
+
+
+
+    columns.forEach((col) => {
+      if (!col.displayLabel) {
+        if (col.uom) {
+          col.displayLabel = `${col.label} (${col.uom})`;
+        } else {
+          col.displayLabel = col.label;
+        }
+      }
+    });
+
+    columns.push({
+      key: "status",
+      label: "status",
+      displayLabel: "Status",
+      type: "STRING"
+    })
+
+    let data: Array<Record<string, any>> = [];
+    if (entry) {
+      if (Array.isArray(entry.data)) {
+        data = entry.data;
+
+        data.sort((a, b) => {
+          const statusA = a.status || '';
+          const statusB = b.status || '';
+          return statusB.localeCompare(statusA)
+        })
+
+      } else {
+        console.warn("formEntries data was not an array. Using empty array.");
+      }
+    }
+
+    // *** 새로 추가: 편집 가능 필드 정보 계산 ***
+    const editableFieldsMap = await getEditableFieldsByTag(contractItemId, projectId);
+
+    return { columns, data, editableFieldsMap };
+
+
+  } catch (cacheError) {
+    console.error(`[getFormData] Cache operation failed:`, cacheError);
+
+    // Fallback logic (기존과 동일하게 editableFieldsMap 추가)
+    try {
+      console.log(`[getFormData] Fallback DB query for (${formCode}, ${contractItemId})`);
+
+      const contractItemResult = await db
+        .select({
+          projectId: projects.id
+        })
+        .from(contractItems)
+        .innerJoin(contracts, eq(contractItems.contractId, contracts.id))
+        .innerJoin(projects, eq(contracts.projectId, projects.id))
+        .where(eq(contractItems.id, contractItemId))
+        .limit(1);
+
+      if (contractItemResult.length === 0) {
+        console.warn(`[getFormData] Fallback: No contract item found with ID: ${contractItemId}`);
+        return { columns: null, data: [], editableFieldsMap: new Map() };
+      }
+
+      const projectId = contractItemResult[0].projectId;
+
+      const metaRows = await db
+        .select()
+        .from(formMetas)
+        .where(
+          and(
+            eq(formMetas.formCode, formCode),
+            eq(formMetas.projectId, projectId)
+          )
+        )
+        .orderBy(desc(formMetas.updatedAt))
+        .limit(1);
+
+      const meta = metaRows[0] ?? null;
+      if (!meta) {
+        console.warn(`[getFormData] Fallback: No form meta found for formCode: ${formCode} and projectId: ${projectId}`);
+        return { columns: null, data: [], editableFieldsMap: new Map() };
+      }
+
+      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] ?? null;
+
+      let columns = meta.columns as DataTableColumnJSON[];
+      const excludeKeys = ['BF_TAG_NO', 'TAG_TYPE_ID', 'PIC_NO'];
+      columns = columns.filter(col => !excludeKeys.includes(col.key));
+
+      columns.forEach((col) => {
+        if (!col.displayLabel) {
+          if (col.uom) {
+            col.displayLabel = `${col.label} (${col.uom})`;
+          } else {
+            col.displayLabel = col.label;
+          }
+        }
+      });
+
+      let data: Array<Record<string, any>> = [];
+      if (entry) {
+        if (Array.isArray(entry.data)) {
+          data = entry.data;
+        } else {
+          console.warn("formEntries data was not an array. Using empty array (fallback).");
+        }
+      }
+
+      // Fallback에서도 편집 가능 필드 정보 계산
+      const editableFieldsMap = await getEditableFieldsByTag(contractItemId, projectId);
+
+      return { columns, data, projectId, editableFieldsMap };
+    } catch (dbError) {
+      console.error(`[getFormData] Fallback DB query failed:`, dbError);
+      return { columns: null, data: [], editableFieldsMap: new Map() };
+    }
+  }
+}
+/**1
+ * contractId와 formCode(itemCode)를 사용하여 contractItemId를 찾는 서버 액션
+ * 
+ * @param contractId - 계약 ID
+ * @param formCode - 폼 코드 (itemCode와 동일)
+ * @returns 찾은 contractItemId 또는 null
+ */
+export async function findContractItemId(contractId: number, formCode: string): Promise<number | null> {
+  try {
+    console.log(`[findContractItemId] 계약 ID ${contractId}와 formCode ${formCode}에 대한 contractItem 조회 시작`);
+
+    // 1. forms 테이블에서 formCode에 해당하는 모든 레코드 조회
+    const formsResult = await db
+      .select({
+        contractItemId: forms.contractItemId
+      })
+      .from(forms)
+      .where(eq(forms.formCode, formCode));
+
+    if (formsResult.length === 0) {
+      console.warn(`[findContractItemId] formCode ${formCode}에 해당하는 form을 찾을 수 없습니다.`);
+      return null;
+    }
+
+    // 모든 contractItemId 추출
+    const contractItemIds = formsResult.map(form => form.contractItemId);
+    console.log(`[findContractItemId] formCode ${formCode}에 해당하는 ${contractItemIds.length}개의 contractItemId 발견`);
+
+    // 2. contractItems 테이블에서 추출한 contractItemId 중에서 
+    // contractId가 일치하는 항목 찾기
+    const contractItemResult = await db
+      .select({
+        id: contractItems.id
+      })
+      .from(contractItems)
+      .where(
+        and(
+          inArray(contractItems.id, contractItemIds),
+          eq(contractItems.contractId, contractId)
+        )
+      )
+      .limit(1);
+
+    if (contractItemResult.length === 0) {
+      console.warn(`[findContractItemId] 계약 ID ${contractId}와 일치하는 contractItemId를 찾을 수 없습니다.`);
+      return null;
+    }
+
+    const contractItemId = contractItemResult[0].id;
+    console.log(`[findContractItemId] 계약 아이템 ID ${contractItemId} 발견`);
+
+    return contractItemId;
+  } catch (error) {
+    console.error(`[findContractItemId] contractItem 조회 중 오류 발생:`, error);
+    return null;
+  }
+}
+
+export async function getPackageCodeById(contractItemId: number): Promise<string | null> {
+  try {
+
+    // 1. forms 테이블에서 formCode에 해당하는 모든 레코드 조회
+    const contractItemsResult = await db
+      .select({
+        itemId: contractItems.itemId
+      })
+      .from(contractItems)
+      .where(eq(contractItems.id, contractItemId))
+      .limit(1)
+      ;
+
+    if (contractItemsResult.length === 0) {
+      console.warn(`[contractItemId]에 해당하는 item을 찾을 수 없습니다.`);
+      return null;
+    }
+
+    const itemId = contractItemsResult[0].itemId
+
+    const packageCodeResult = await db
+      .select({
+        packageCode: items.packageCode
+      })
+      .from(items)
+      .where(eq(items.id, itemId))
+      .limit(1);
+
+    if (packageCodeResult.length === 0) {
+      console.warn(`${itemId}와 일치하는 패키지 코드를 찾을 수 없습니다.`);
+      return null;
+    }
+
+    const packageCode = packageCodeResult[0].packageCode;
+
+    return packageCode;
+  } catch (error) {
+    console.error(`패키지 코드 조회 중 오류 발생:`, error);
+    return null;
+  }
+}
+
+
+export async function syncMissingTags(
+  contractItemId: number,
+  formCode: string
+) {
+  // (1) Ensure there's a row in `forms` matching (contractItemId, formCode).
+  const [formRow] = await db
+    .select()
+    .from(forms)
+    .where(
+      and(
+        eq(forms.contractItemId, contractItemId),
+        eq(forms.formCode, formCode)
+      )
+    )
+    .limit(1);
+
+  if (!formRow) {
+    throw new Error(
+      `Form not found for contractItemId=${contractItemId}, formCode=${formCode}`
+    );
+  }
+
+  // (2) Get all mappings from `tagTypeClassFormMappings` for this formCode.
+  const formMappings = await db
+    .select()
+    .from(tagTypeClassFormMappings)
+    .where(eq(tagTypeClassFormMappings.formCode, formCode));
+
+  // If no mappings are found, there's nothing to sync.
+  if (formMappings.length === 0) {
+    console.log(`No mappings found for formCode=${formCode}`);
+    return { createdCount: 0, updatedCount: 0, deletedCount: 0 };
+  }
+
+  // Build a dynamic OR clause to match (tagType, class) pairs from the mappings.
+  const orConditions = formMappings.map((m) =>
+    and(eq(tags.tagType, m.tagTypeLabel), eq(tags.class, m.classLabel))
+  );
+
+  // (3) Fetch all matching `tags` for the contractItemId + any of the (tagType, class) pairs.
+  const tagRows = await db
+    .select()
+    .from(tags)
+    .where(and(eq(tags.contractItemId, contractItemId), or(...orConditions)));
+
+  // (4) Fetch (or create) a single `formEntries` row for (contractItemId, formCode).
+  let [entry] = await db
+    .select()
+    .from(formEntries)
+    .where(
+      and(
+        eq(formEntries.contractItemId, contractItemId),
+        eq(formEntries.formCode, formCode)
+      )
+    )
+    .limit(1);
+
+  if (!entry) {
+    const [inserted] = await db
+      .insert(formEntries)
+      .values({
+        contractItemId,
+        formCode,
+        data: [], // Initialize with empty array
+      })
+      .returning();
+    entry = inserted;
+  }
+
+  // entry.data는 [{ TAG_NO: string, TAG_DESC?: string }, ...] 형태라고 가정
+  const existingData = entry.data as Array<{
+    TAG_NO: string;
+    TAG_DESC?: string;
+  }>;
+
+  // Create a Set of valid tagNumbers from tagRows for efficient lookup
+  const validTagNumbers = new Set(tagRows.map((tag) => tag.tagNo));
+
+  // Copy existing data to work with
+  let updatedData: Array<{
+    TAG_NO: string;
+    TAG_DESC?: string;
+  }> = [];
+
+  let createdCount = 0;
+  let updatedCount = 0;
+  let deletedCount = 0;
+
+  // First, filter out items that should be deleted (not in validTagNumbers)
+  for (const item of existingData) {
+    if (validTagNumbers.has(item.TAG_NO)) {
+      updatedData.push(item);
+    } else {
+      deletedCount++;
+    }
+  }
+
+  // (5) For each tagRow, if it's missing in updatedData, push it in.
+  //     이미 있는 경우에도 description이 달라지면 업데이트할 수 있음.
+  for (const tagRow of tagRows) {
+    const { tagNo, description } = tagRow;
+
+    // 5-1. 기존 데이터에서 TAG_NO 매칭
+    const existingIndex = updatedData.findIndex(
+      (item) => item.TAG_NO === tagNo
+    );
+
+    // 5-2. 없다면 새로 추가
+    if (existingIndex === -1) {
+      updatedData.push({
+        TAG_NO: tagNo,
+        TAG_DESC: description ?? "",
+      });
+      createdCount++;
+    } else {
+      // 5-3. 이미 있으면, description이 다를 때만 업데이트(선택 사항)
+      const existingItem = updatedData[existingIndex];
+      if (existingItem.TAG_DESC !== description) {
+        updatedData[existingIndex] = {
+          ...existingItem,
+          TAG_DESC: description ?? "",
+        };
+        updatedCount++;
+      }
+    }
+  }
+
+  // (6) 실제로 추가되거나 수정되거나 삭제된 게 있다면 DB에 반영
+  if (createdCount > 0 || updatedCount > 0 || deletedCount > 0) {
+    await db
+      .update(formEntries)
+      .set({ data: updatedData })
+      .where(eq(formEntries.id, entry.id));
+  }
+
+  // 캐시 무효화 등 후처리
+  revalidateTag(`form-data-${formCode}-${contractItemId}`);
+
+  return { createdCount, updatedCount, deletedCount };
+}
+
+/**
+ * updateFormDataInDB:
+ * (formCode, contractItemId)에 해당하는 "단 하나의" formEntries row를 가져와,
+ * data: [{ TAG_NO, ...}, ...] 배열에서 TAG_NO 매칭되는 항목을 업데이트
+ * 업데이트 후, revalidateTag()로 캐시 무효화.
+ */
+export interface UpdateResponse {
+  success: boolean;
+  message: string;
+  data?: {
+    updatedCount?: number;
+    failedCount?: number;
+    updatedTags?: string[];
+    notFoundTags?: string[];
+    updateTimestamp?: string;
+    error?: any;
+    invalidRows?: any[];
+    TAG_NO?: string;
+    updatedFields?: string[];
+  };
+}
+
+export async function updateFormDataInDB(
+  formCode: string,
+  contractItemId: number,
+  newData: Record<string, any>
+): Promise<UpdateResponse> {
+  try {
+    // 1) tagNumber로 식별
+    const TAG_NO = newData.TAG_NO;
+    if (!TAG_NO) {
+      return {
+        success: false,
+        message: "tagNumber는 필수 항목입니다.",
+      };
+    }
+
+    // 2) row 찾기 (단 하나)
+    const entries = await db
+      .select()
+      .from(formEntries)
+      .where(
+        and(
+          eq(formEntries.formCode, formCode),
+          eq(formEntries.contractItemId, contractItemId)
+        )
+      )
+      .limit(1);
+
+    if (!entries || entries.length === 0) {
+      return {
+        success: false,
+        message: `폼 데이터를 찾을 수 없습니다. (formCode=${formCode}, contractItemId=${contractItemId})`,
+      };
+    }
+
+    const entry = entries[0];
+
+    // 3) data가 배열인지 확인
+    if (!entry.data) {
+      return {
+        success: false,
+        message: "폼 데이터가 없습니다.",
+      };
+    }
+
+    const dataArray = entry.data as Array<Record<string, any>>;
+    if (!Array.isArray(dataArray)) {
+      return {
+        success: false,
+        message: "폼 데이터가 올바른 형식이 아닙니다. 배열 형식이어야 합니다.",
+      };
+    }
+
+    // 4) TAG_NO = newData.TAG_NO 항목 찾기
+    const idx = dataArray.findIndex((item) => item.TAG_NO === TAG_NO);
+    if (idx < 0) {
+      return {
+        success: false,
+        message: `태그 번호 "${TAG_NO}"를 가진 항목을 찾을 수 없습니다.`,
+      };
+    }
+
+    // 5) 병합 (status 필드 추가)
+    const oldItem = dataArray[idx];
+    const updatedItem = {
+      ...oldItem,
+      ...newData,
+      TAG_NO: oldItem.TAG_NO, // TAG_NO 변경 불가 시 유지
+      status: "Updated" // Excel에서 가져온 데이터임을 표시
+    };
+
+    const updatedArray = [...dataArray];
+    updatedArray[idx] = updatedItem;
+
+    // 6) DB UPDATE
+    try {
+      await db
+        .update(formEntries)
+        .set({
+          data: updatedArray,
+          updatedAt: new Date(), // 업데이트 시간도 갱신
+        })
+        .where(eq(formEntries.id, entry.id));
+    } catch (dbError) {
+      console.error("Database update error:", dbError);
+
+      if (dbError instanceof DrizzleError) {
+        return {
+          success: false,
+          message: `데이터베이스 업데이트 오류: ${dbError.message}`,
+        };
+      }
+
+      return {
+        success: false,
+        message: "데이터베이스 업데이트 중 오류가 발생했습니다.",
+      };
+    }
+
+    // 7) Cache 무효화
+    try {
+      // 캐시 태그를 form-data-${formCode}-${contractItemId} 형태로 가정
+      const cacheTag = `form-data-${formCode}-${contractItemId}`;
+      console.log(cacheTag, "update")
+      revalidateTag(cacheTag);
+    } catch (cacheError) {
+      console.warn("Cache revalidation warning:", cacheError);
+      // 캐시 무효화는 실패해도 업데이트 자체는 성공했으므로 경고만 로그로 남김
+    }
+
+    return {
+      success: true,
+      message: "데이터가 성공적으로 업데이트되었습니다.",
+      data: {
+        TAG_NO,
+        updatedFields: Object.keys(newData).filter(
+          (key) => key !== "TAG_NO"
+        ),
+      },
+    };
+  } catch (error) {
+    // 예상치 못한 오류 처리
+    console.error("Unexpected error in updateFormDataInDB:", error);
+    return {
+      success: false,
+      message:
+        error instanceof Error
+          ? `예상치 못한 오류가 발생했습니다: ${error.message}`
+          : "알 수 없는 오류가 발생했습니다.",
+    };
+  }
+}
+
+export async function updateFormDataBatchInDB(
+  formCode: string,
+  contractItemId: number,
+  newDataArray: Record<string, any>[]
+): Promise<UpdateResponse> {
+  try {
+    // 입력 유효성 검사
+    if (!newDataArray || newDataArray.length === 0) {
+      return {
+        success: false,
+        message: "업데이트할 데이터가 없습니다.",
+      };
+    }
+
+    // TAG_NO 유효성 검사
+    const invalidRows = newDataArray.filter(row => !row.TAG_NO);
+    if (invalidRows.length > 0) {
+      return {
+        success: false,
+        message: `${invalidRows.length}개 행에 TAG_NO가 없습니다.`,
+        data: { invalidRows }
+      };
+    }
+
+    // 1) DB에서 현재 데이터 가져오기
+    const entries = await db
+      .select()
+      .from(formEntries)
+      .where(
+        and(
+          eq(formEntries.formCode, formCode),
+          eq(formEntries.contractItemId, contractItemId)
+        )
+      )
+      .limit(1);
+
+    if (!entries || entries.length === 0) {
+      return {
+        success: false,
+        message: `폼 데이터를 찾을 수 없습니다. (formCode=${formCode}, contractItemId=${contractItemId})`,
+      };
+    }
+
+    const entry = entries[0];
+
+    // 데이터 형식 검증
+    if (!entry.data) {
+      return {
+        success: false,
+        message: "폼 데이터가 없습니다.",
+      };
+    }
+
+    const dataArray = entry.data as Array<Record<string, any>>;
+    if (!Array.isArray(dataArray)) {
+      return {
+        success: false,
+        message: "폼 데이터가 올바른 형식이 아닙니다. 배열 형식이어야 합니다.",
+      };
+    }
+
+    // 2) 모든 변경사항을 한번에 적용
+    const updatedArray = [...dataArray];
+    const updatedTags: string[] = [];
+    const notFoundTags: string[] = [];
+    const updateTimestamp = new Date().toISOString();
+    
+    // 각 import row에 대해 업데이트 수행
+    for (const newData of newDataArray) {
+      const TAG_NO = newData.TAG_NO;
+      const idx = updatedArray.findIndex(item => item.TAG_NO === TAG_NO);
+      
+      if (idx >= 0) {
+        // 기존 데이터와 병합
+        const oldItem = updatedArray[idx];
+        updatedArray[idx] = {
+          ...oldItem,
+          ...newData,
+          TAG_NO: oldItem.TAG_NO, // TAG_NO는 변경 불가
+          TAG_DESC: oldItem.TAG_DESC, // TAG_DESC도 보존
+          status: "Updated", // Excel import 표시
+          lastUpdated: updateTimestamp // 업데이트 시각 추가
+        };
+        updatedTags.push(TAG_NO);
+      } else {
+        // TAG를 찾을 수 없는 경우
+        notFoundTags.push(TAG_NO);
+      }
+    }
+
+    // 하나도 업데이트할 항목이 없는 경우
+    if (updatedTags.length === 0) {
+      return {
+        success: false,
+        message: `업데이트할 수 있는 TAG를 찾을 수 없습니다. 모든 ${notFoundTags.length}개 TAG가 데이터베이스에 없습니다.`,
+        data: {
+          updatedCount: 0,
+          failedCount: notFoundTags.length,
+          notFoundTags
+        }
+      };
+    }
+
+    // 3) DB에 한 번만 저장
+    try {
+      await db
+        .update(formEntries)
+        .set({
+          data: updatedArray,
+          updatedAt: new Date(),
+        })
+        .where(eq(formEntries.id, entry.id));
+        
+    } catch (dbError) {
+      console.error("Database update error:", dbError);
+
+      if (dbError instanceof DrizzleError) {
+        return {
+          success: false,
+          message: `데이터베이스 업데이트 오류: ${dbError.message}`,
+          data: {
+            updatedCount: 0,
+            failedCount: newDataArray.length,
+            error: dbError
+          }
+        };
+      }
+
+      return {
+        success: false,
+        message: "데이터베이스 업데이트 중 오류가 발생했습니다.",
+        data: {
+          updatedCount: 0,
+          failedCount: newDataArray.length
+        }
+      };
+    }
+
+    // 4) 캐시 무효화
+    try {
+      const cacheTag = `form-data-${formCode}-${contractItemId}`;
+      console.log(`Cache invalidated: ${cacheTag}`);
+      revalidateTag(cacheTag);
+    } catch (cacheError) {
+      // 캐시 무효화 실패는 경고만
+      console.warn("Cache revalidation warning:", cacheError);
+    }
+
+    // 5) 성공 응답
+    const message = notFoundTags.length > 0
+      ? `${updatedTags.length}개 항목이 업데이트되었습니다. (${notFoundTags.length}개 TAG는 찾을 수 없음)`
+      : `${updatedTags.length}개 항목이 성공적으로 업데이트되었습니다.`;
+
+    return {
+      success: true,
+      message: message,
+      data: {
+        updatedCount: updatedTags.length,
+        updatedTags,
+        notFoundTags: notFoundTags.length > 0 ? notFoundTags : undefined,
+        failedCount: notFoundTags.length,
+        updateTimestamp
+      },
+    };
+    
+  } catch (error) {
+    // 예상치 못한 오류 처리
+    console.error("Unexpected error in updateFormDataBatchInDB:", error);
+    
+    return {
+      success: false,
+      message: error instanceof Error
+        ? `예상치 못한 오류가 발생했습니다: ${error.message}`
+        : "알 수 없는 오류가 발생했습니다.",
+      data: {
+        updatedCount: 0,
+        failedCount: newDataArray.length,
+        error: error
+      }
+    };
+  }
+}
+
+// FormColumn Type (동일)
+export interface FormColumn {
+  key: string;
+  type: string;
+  label: string;
+  options?: string[];
+}
+
+interface MetadataResult {
+  formName: string;
+  formCode: string;
+  columns: FormColumn[];
+}
+
+/**
+ * 서버 액션:
+ * 주어진 formCode에 해당하는 form_metas 레코드 1개를 찾아서
+ * { formName, formCode, columns } 형태로 반환.
+ * 없으면 null.
+ */
+export async function fetchFormMetadata(
+  formCode: string,
+  projectId: number
+): Promise<MetadataResult | null> {
+  try {
+    // 기존 방식: select().from().where()
+    const rows = await db
+      .select()
+      .from(formMetas)
+      .where(and(eq(formMetas.formCode, formCode), eq(formMetas.projectId, projectId)))
+      .limit(1);
+
+    // rows는 배열
+    const metaData = rows[0];
+    if (!metaData) return null;
+
+    return {
+      formCode: metaData.formCode,
+      formName: metaData.formName,
+      columns: metaData.columns as FormColumn[],
+    };
+  } catch (err) {
+    console.error("Error in fetchFormMetadata:", err);
+    return null;
+  }
+}
+
+type GetReportFileList = (
+  packageId: string,
+  formCode: string
+) => Promise<{
+  formId: number;
+}>;
+
+export const getFormId: GetReportFileList = async (packageId, formCode) => {
+  const result: { formId: number } = {
+    formId: 0,
+  };
+  try {
+    const [targetForm] = await db
+      .select()
+      .from(forms)
+      .where(
+        and(
+          eq(forms.formCode, formCode),
+          eq(forms.contractItemId, Number(packageId))
+        )
+      );
+
+    if (!targetForm) {
+      throw new Error("Not Found Target Form");
+    }
+
+    const { id: formId } = targetForm;
+
+    result.formId = formId;
+  } catch (err) {
+  } finally {
+    return result;
+  }
+};
+
+type getReportTempList = (
+  packageId: number,
+  formId: number
+) => Promise<VendorDataReportTemps[]>;
+
+export const getReportTempList: getReportTempList = async (
+  packageId,
+  formId
+) => {
+  let result: VendorDataReportTemps[] = [];
+
+  try {
+    result = await db
+      .select()
+      .from(vendorDataReportTemps)
+      .where(
+        and(
+          eq(vendorDataReportTemps.contractItemId, packageId),
+          eq(vendorDataReportTemps.formId, formId)
+        )
+      );
+  } catch (err) {
+  } finally {
+    return result;
+  }
+};
+
+export async function uploadReportTemp(
+  packageId: number,
+  formId: number,
+  formData: FormData
+) {
+  const file = formData.get("file") as File | null;
+  const customFileName = formData.get("customFileName") as string;
+  const uploaderType = (formData.get("uploaderType") as string) || "vendor";
+
+  if (!["vendor", "client", "shi"].includes(uploaderType)) {
+    throw new Error(
+      `Invalid uploaderType: ${uploaderType}. Must be one of: vendor, client, shi`
+    );
+  }
+  if (file && file.size > 0) {
+
+    const saveResult = await saveFile({ file, directory: "vendorFormData", originalName: customFileName });
+    if (!saveResult.success) {
+      return { success: false, error: saveResult.error };
+    }
+
+    return db.transaction(async (tx) => {
+      // 파일 정보를 테이블에 저장
+      await tx
+        .insert(vendorDataReportTemps)
+        .values({
+          contractItemId: packageId,
+          formId: formId,
+          fileName: customFileName,
+          filePath: saveResult.publicPath!,
+        })
+        .returning();
+    });
+  }
+}
+
+export const getOrigin = async (): Promise<string> => {
+  const headersList = await headers();
+  const host = headersList.get("host");
+  const proto = headersList.get("x-forwarded-proto") || "http"; // 기본값은 http
+  const origin = `${proto}://${host}`;
+
+  return origin;
+};
+
+
+type deleteReportTempFile = (id: number) => Promise<{
+  result: boolean;
+  error?: any;
+}>;
+
+export const deleteReportTempFile: deleteReportTempFile = async (id) => {
+  try {
+    return db.transaction(async (tx) => {
+      const [targetTempFile] = await tx
+        .select()
+        .from(vendorDataReportTemps)
+        .where(eq(vendorDataReportTemps.id, id));
+
+      if (!targetTempFile) {
+        throw new Error("해당 Template File을 찾을 수 없습니다.");
+      }
+
+      await tx
+        .delete(vendorDataReportTemps)
+        .where(eq(vendorDataReportTemps.id, id));
+
+      const { filePath } = targetTempFile;
+
+      await deleteFile(filePath);
+
+      return { result: true };
+    });
+  } catch (err) {
+    return { result: false, error: (err as Error).message };
+  }
+};
+
+
+/**
+ * Get tag type mappings specific to a form
+ * @param formCode The form code to filter mappings
+ * @param projectId The project ID
+ * @returns Array of tag type-class mappings for the form
+ */
+export async function getFormTagTypeMappings(formCode: string, projectId: number) {
+
+  try {
+    const mappings = await db.query.tagTypeClassFormMappings.findMany({
+      where: and(
+        eq(tagTypeClassFormMappings.formCode, formCode),
+        eq(tagTypeClassFormMappings.projectId, projectId)
+      )
+    });
+
+    return mappings;
+  } catch (error) {
+    console.error("Error fetching form tag type mappings:", error);
+    throw new Error("Failed to load form tag type mappings");
+  }
+}
+
+/**
+ * Get tag type by its description
+ * @param description The tag type description (used as tagTypeLabel in mappings)
+ * @param projectId The project ID
+ * @returns The tag type object
+ */
+export async function getTagTypeByDescription(description: string, projectId: number) {
+  try {
+    const tagType = await db.query.tagTypes.findFirst({
+      where: and(
+        eq(tagTypes.description, description),
+        eq(tagTypes.projectId, projectId)
+      )
+    });
+
+    return tagType;
+  } catch (error) {
+    console.error("Error fetching tag type by description:", error);
+    throw new Error("Failed to load tag type");
+  }
+}
+
+/**
+ * Get subfields for a specific tag type
+ * @param tagTypeCode The tag type code
+ * @param projectId The project ID
+ * @returns Object containing subfields with their options
+ */
+export async function getSubfieldsByTagTypeForForm(tagTypeCode: string, projectId: number) {
+  try {
+    const subfields = await db.query.tagSubfields.findMany({
+      where: and(
+        eq(tagSubfields.tagTypeCode, tagTypeCode),
+        eq(tagSubfields.projectId, projectId)
+      ),
+      orderBy: tagSubfields.sortOrder
+    });
+
+    const subfieldsWithOptions = await Promise.all(
+      subfields.map(async (subfield) => {
+        const options = await db.query.tagSubfieldOptions.findMany({
+          where: and(
+            eq(tagSubfieldOptions.attributesId, subfield.attributesId),
+            eq(tagSubfieldOptions.projectId, projectId)
+          )
+        });
+
+        return {
+          name: subfield.attributesId,
+          label: subfield.attributesDescription,
+          type: options.length > 0 ? "select" : "text",
+          options: options.map(opt => ({ value: opt.code, label: opt.label })),
+          expression: subfield.expression || undefined,
+          delimiter: subfield.delimiter || undefined
+        };
+      })
+    );
+
+    return { subFields: subfieldsWithOptions };
+  } catch (error) {
+    console.error("Error fetching subfields for form:", error);
+    throw new Error("Failed to load subfields");
+  }
+}
+
+interface GenericData {
+  [key: string]: any;
+}
+
+interface SEDPAttribute {
+  NAME: string;
+  VALUE: any;
+  UOM: string;
+  UOM_ID?: string;
+  CLS_ID?:string;
+}
+
+interface SEDPDataItem {
+  TAG_NO: string;
+  TAG_DESC: string;
+  CLS_ID: string;
+  ATTRIBUTES: SEDPAttribute[];
+  SCOPE: string;
+  TOOLID: string;
+  ITM_NO: string;
+  OP_DELETE: boolean;
+  MAIN_YN: boolean;
+  LAST_REV_YN: boolean;
+  CRTER_NO: string;
+  CHGER_NO: string;
+  TYPE: string;
+  PROJ_NO: string;
+  REV_NO: string;
+  CRTE_DTM?: string;
+  CHGE_DTM?: string;
+  _id?: string;
+}
+
+async function transformDataToSEDPFormat(
+  tableData: GenericData[],
+  columnsJSON: DataTableColumnJSON[],
+  formCode: string,
+  objectCode: string,
+  projectNo: string,
+  contractItemId: number, // Add contractItemId parameter
+  designerNo: string = "253213"
+): Promise<SEDPDataItem[]> {
+  // Create a map for quick column lookup
+  const columnsMap = new Map<string, DataTableColumnJSON>();
+  columnsJSON.forEach(col => {
+    columnsMap.set(col.key, col);
+  });
+
+  // Current timestamp for CRTE_DTM and CHGE_DTM
+  const currentTimestamp = new Date().toISOString();
+
+  // Define the API base URL
+  const SEDP_API_BASE_URL = process.env.SEDP_API_BASE_URL || 'http://sedpwebapi.ship.samsung.co.kr/api';
+
+  // Get the token
+  const apiKey = await getSEDPToken();
+
+  // Cache for UOM factors to avoid duplicate API calls
+  const uomFactorCache = new Map<string, number>();
+
+  // Cache for packageCode to avoid duplicate DB queries for same tag
+  const packageCodeCache = new Map<string, string>();
+
+  // Cache for tagClass code to avoid duplicate DB queries for same tag
+  const tagClassCodeCache = new Map<string, string>();
+
+  // Transform each row
+  const transformedItems = [];
+
+  for (const row of tableData) {
+
+    const cotractItem = await db.query.contractItems.findFirst({
+      where: 
+        eq(contractItems.id, contractItemId),
+    });
+
+    const item = await db.query.items.findFirst({
+      where: 
+        eq(items.id, cotractItem.itemId),
+    });
+
+    // Get packageCode for this specific tag
+    let packageCode = item.packageCode; // fallback to formCode
+    let tagClassCode = ""; // for CLS_ID
+
+    if (row.TAG_NO && contractItemId) {
+      // Check cache first
+      const cacheKey = `${contractItemId}-${row.TAG_NO}`;
+
+      if (packageCodeCache.has(cacheKey)) {
+        packageCode = packageCodeCache.get(cacheKey)!;
+      } else {
+        try {
+          // Query to get packageCode for this specific tag
+          const tagResult = await db.query.tags.findFirst({
+            where: and(
+              eq(tags.contractItemId, contractItemId),
+              eq(tags.tagNo, row.TAG_NO)
+            )
+          });
+
+          if (tagResult) {
+            // Get tagClass code if tagClassId exists
+            if (tagResult.tagClassId) {
+              // Check tagClass cache first
+              if (tagClassCodeCache.has(cacheKey)) {
+                tagClassCode = tagClassCodeCache.get(cacheKey)!;
+              } else {
+                const tagClassResult = await db.query.tagClasses.findFirst({
+                  where: eq(tagClasses.id, tagResult.tagClassId)
+                });
+
+                if (tagClassResult) {
+                  tagClassCode = tagClassResult.code;
+                  console.log(`Found tagClass code for tag ${row.TAG_NO}: ${tagClassCode}`);
+                } else {
+                  console.warn(`No tagClass found for tagClassId: ${tagResult.tagClassId}`);
+                }
+
+                // Cache the tagClass code result
+                tagClassCodeCache.set(cacheKey, tagClassCode);
+              }
+            }
+
+            // Get the contract item
+            const contractItemResult = await db.query.contractItems.findFirst({
+              where: eq(contractItems.id, tagResult.contractItemId)
+            });
+
+            if (contractItemResult) {
+              // Get the first item with this itemId
+              const itemResult = await db.query.items.findFirst({
+                where: eq(items.id, contractItemResult.itemId)
+              });
+
+              if (itemResult && itemResult.packageCode) {
+                packageCode = itemResult.packageCode;
+                console.log(`Found packageCode for tag ${row.TAG_NO}: ${packageCode}`);
+              } else {
+                console.warn(`No item found for contractItem.itemId: ${contractItemResult.itemId}, using fallback`);
+              }
+            } else {
+              console.warn(`No contractItem found for tag ${row.TAG_NO}, using fallback`);
+            }
+          } else {
+            console.warn(`No tag found for contractItemId: ${contractItemId}, tagNo: ${row.TAG_NO}, using fallback`);
+          }
+
+          // Cache the result (even if it's the fallback value)
+          packageCodeCache.set(cacheKey, packageCode);
+        } catch (error) {
+          console.error(`Error fetching packageCode for tag ${row.TAG_NO}:`, error);
+          // Use fallback value and cache it
+          packageCodeCache.set(cacheKey, packageCode);
+        }
+      }
+
+      // Get tagClass code if not already retrieved above
+      if (!tagClassCode && tagClassCodeCache.has(cacheKey)) {
+        tagClassCode = tagClassCodeCache.get(cacheKey)!;
+      } else if (!tagClassCode) {
+        try {
+          const tagResult = await db.query.tags.findFirst({
+            where: and(
+              eq(tags.contractItemId, contractItemId),
+              eq(tags.tagNo, row.TAG_NO)
+            )
+          });
+
+          if (tagResult && tagResult.tagClassId) {
+            const tagClassResult = await db.query.tagClasses.findFirst({
+              where: eq(tagClasses.id, tagResult.tagClassId)
+            });
+
+            if (tagClassResult) {
+              tagClassCode = tagClassResult.code;
+              console.log(`Found tagClass code for tag ${row.TAG_NO}: ${tagClassCode}`);
+            }
+          }
+
+          // Cache the tagClass code result
+          tagClassCodeCache.set(cacheKey, tagClassCode);
+        } catch (error) {
+          console.error(`Error fetching tagClass code for tag ${row.TAG_NO}:`, error);
+          // Cache empty string as fallback
+          tagClassCodeCache.set(cacheKey, "");
+        }
+      }
+    }
+
+    // Create base SEDP item with required fields
+    const sedpItem: SEDPDataItem = {
+      TAG_NO: row.TAG_NO || "",
+      TAG_DESC: row.TAG_DESC || "",
+      ATTRIBUTES: [],
+      // SCOPE: objectCode,
+      SCOPE: packageCode,
+      TOOLID: "eVCP", // Changed from VDCS
+      ITM_NO: row.TAG_NO || "",
+      OP_DELETE: false,
+      MAIN_YN: true,
+      LAST_REV_YN: true,
+      CRTER_NO: designerNo,
+      CHGER_NO: designerNo,
+      TYPE: formCode, // Use packageCode instead of formCode
+      CLS_ID: tagClassCode, // Add CLS_ID with tagClass code
+      PROJ_NO: projectNo,
+      REV_NO: "00",
+      CRTE_DTM: currentTimestamp,
+      CHGE_DTM: currentTimestamp,
+      _id: ""
+    };
+
+    // Convert all other fields (except TAG_NO and TAG_DESC) to ATTRIBUTES
+    for (const key in row) {
+      if (key !== "TAG_NO" && key !== "TAG_DESC") {
+        const column = columnsMap.get(key);
+        let value = row[key];
+
+        // Only process non-empty values
+        if (value !== undefined && value !== null && value !== "") {
+          // Check if we need to apply UOM conversion
+          if (column?.uomId) {
+            // First check cache to avoid duplicate API calls
+            let factor = uomFactorCache.get(column.uomId);
+
+            // If not in cache, make API call to get the factor
+            if (factor === undefined) {
+              try {
+                const response = await fetch(
+                  `${SEDP_API_BASE_URL}/UOM/GetByID`,
+                  {
+                    method: 'POST',
+                    headers: {
+                      'Content-Type': 'application/json',
+                      'accept': '*/*',
+                      'ApiKey': apiKey,
+                      'ProjectNo': projectNo
+                    },
+                    body: JSON.stringify({
+                      'ProjectNo': projectNo,
+                      'UOMID': column.uomId,
+                      'ContainDeleted': false
+                    })
+                  }
+                );
+
+                if (response.ok) {
+                  const uomData = await response.json();
+                  if (uomData && uomData.FACTOR !== undefined && uomData.FACTOR !== null) {
+                    factor = Number(uomData.FACTOR);
+                    // Store in cache for future use (type assertion to ensure it's a number)
+                    uomFactorCache.set(column.uomId, factor);
+                  }
+                } else {
+                  console.warn(`Failed to get UOM data for ${column.uomId}: ${response.statusText}`);
+                }
+              } catch (error) {
+                console.error(`Error fetching UOM data for ${column.uomId}:`, error);
+              }
+            }
+
+            // Apply the factor if we got one
+            // if (factor !== undefined && typeof value === 'number') {
+            //   value = value * factor;
+            // }
+          }
+
+          const attribute: SEDPAttribute = {
+            NAME: key,
+            VALUE: String(value),  // 모든 값을 문자열로 변환
+            UOM: column?.uom || "",
+            CLS_ID: tagClassCode || "",
+          };
+
+          // Add UOM_ID if present in column definition
+          if (column?.uomId) {
+            attribute.UOM_ID = column.uomId;
+          }
+
+          sedpItem.ATTRIBUTES.push(attribute);
+        }
+      }
+    }
+
+    transformedItems.push(sedpItem);
+  }
+
+  return transformedItems;
+}
+
+// Server Action wrapper (async)
+export async function transformFormDataToSEDP(
+  tableData: GenericData[],
+  columnsJSON: DataTableColumnJSON[],
+  formCode: string,
+  objectCode: string,
+  projectNo: string,
+  contractItemId: number, // Add contractItemId parameter
+  designerNo: string = "253213"
+): Promise<SEDPDataItem[]> {
+  return transformDataToSEDPFormat(
+    tableData,
+    columnsJSON,
+    formCode,
+    objectCode,
+    projectNo,
+    contractItemId, // Pass contractItemId
+    designerNo
+  );
+}
+/**
+ * Get project code by project ID
+ */
+export async function getProjectCodeById(projectId: number): Promise<string> {
+  const projectRecord = await db
+    .select({ code: projects.code })
+    .from(projects)
+    .where(eq(projects.id, projectId))
+    .limit(1);
+
+  if (!projectRecord || projectRecord.length === 0) {
+    throw new Error(`Project not found with ID: ${projectId}`);
+  }
+
+  return projectRecord[0].code;
+}
+
+export async function getProjectById(projectId: number): Promise<{ code: string; type: string;  }> {
+  const projectRecord = await db
+    .select({ code: projects.code , type:projects.type})
+    .from(projects)
+    .where(eq(projects.id, projectId))
+    .limit(1);
+
+  if (!projectRecord || projectRecord.length === 0) {
+    throw new Error(`Project not found with ID: ${projectId}`);
+  }
+
+  return projectRecord[0];
+}
+
+
+/**
+ * Send data to SEDP
+ */
+export async function sendDataToSEDP(
+  projectCode: string,
+  sedpData: SEDPDataItem[]
+): Promise<any> {
+  try {
+    // Get the token
+    const apiKey = await getSEDPToken();
+
+    // Define the API base URL
+    const SEDP_API_BASE_URL = process.env.SEDP_API_BASE_URL || 'http://sedpwebapi.ship.samsung.co.kr/api';
+
+    console.log("Sending data to SEDP:", JSON.stringify(sedpData, null, 2));
+
+    // Make the API call
+    const response = await fetch(
+      `${SEDP_API_BASE_URL}/AdapterData/Overwrite`,
+      {
+        method: 'POST',
+        headers: {
+          'Content-Type': 'application/json',
+          'accept': '*/*',
+          'ApiKey': apiKey,
+          'ProjectNo': projectCode
+        },
+        body: JSON.stringify(sedpData)
+      }
+    );
+
+    if (!response.ok) {
+      const errorText = await response.text();
+      throw new Error(`SEDP API request failed: ${response.status} ${response.statusText} - ${errorText}`);
+    }
+
+    const data = await response.json();
+    return data;
+  } catch (error: any) {
+    console.error('Error calling SEDP API:', error);
+    throw new Error(`Failed to send data to SEDP API: ${error.message || 'Unknown error'}`);
+  }
+}
+
+/**
+ * Server action to send form data to SEDP
+ */
+export async function sendFormDataToSEDP(
+  formCode: string,
+  projectId: number,
+  contractItemId: number, // contractItemId 파라미터 추가
+  formData: GenericData[],
+  columns: DataTableColumnJSON[]
+): Promise<{ success: boolean; message: string; data?: any }> {
+  try {
+    // 1. Get project code
+    const projectCode = await getProjectCodeById(projectId);
+
+    // 2. Get class mapping
+    const mappingsResult = await db.query.tagTypeClassFormMappings.findFirst({
+      where: and(
+        eq(tagTypeClassFormMappings.formCode, formCode),
+        eq(tagTypeClassFormMappings.projectId, projectId)
+      )
+    });
+
+    // Check if mappings is an array or a single object and handle accordingly
+    const mappings = Array.isArray(mappingsResult) ? mappingsResult[0] : mappingsResult;
+
+    // Default object code to fallback value if we can't find it
+    let objectCode = ""; // Default fallback
+
+    if (mappings && mappings.classLabel) {
+      const objectCodeResult = await db.query.tagClasses.findFirst({
+        where: and(
+          eq(tagClasses.label, mappings.classLabel),
+          eq(tagClasses.projectId, projectId)
+        )
+      });
+
+      // Check if result is an array or a single object
+      const objectCodeRecord = Array.isArray(objectCodeResult) ? objectCodeResult[0] : objectCodeResult;
+
+      if (objectCodeRecord && objectCodeRecord.code) {
+        objectCode = objectCodeRecord.code;
+      } else {
+        console.warn(`No tag class found for label ${mappings.classLabel} in project ${projectId}, using default`);
+      }
+    } else {
+      console.warn(`No mapping found for formCode ${formCode} in project ${projectId}, using default object code`);
+    }
+
+    // 3. Transform data to SEDP format
+    const sedpData = await transformFormDataToSEDP(
+      formData,
+      columns,
+      formCode,
+      objectCode,
+      projectCode,
+      contractItemId // Add contractItemId parameter
+    );
+
+    // 4. Send to SEDP API
+    const result = await sendDataToSEDP(projectCode, sedpData);
+
+    // 5. SEDP 전송 성공 후 formEntries에 status 업데이트
+    try {
+      // Get the current formEntries data
+      const entries = await db
+        .select()
+        .from(formEntries)
+        .where(
+          and(
+            eq(formEntries.formCode, formCode),
+            eq(formEntries.contractItemId, contractItemId)
+          )
+        )
+        .limit(1);
+
+      if (entries && entries.length > 0) {
+        const entry = entries[0];
+        const dataArray = entry.data as Array<Record<string, any>>;
+
+        if (Array.isArray(dataArray)) {
+          // Extract TAG_NO list from formData
+          const sentTagNumbers = new Set(
+            formData
+              .map(item => item.TAG_NO)
+              .filter(tagNo => tagNo) // Remove null/undefined values
+          );
+
+          // Update status for sent tags
+          const updatedDataArray = dataArray.map(item => {
+            if (item.TAG_NO && sentTagNumbers.has(item.TAG_NO)) {
+              return {
+                ...item,
+                status: "Sent to S-EDP" // SEDP로 전송된 데이터임을 표시
+              };
+            }
+            return item;
+          });
+
+          // Update the database
+          await db
+            .update(formEntries)
+            .set({
+              data: updatedDataArray,
+              updatedAt: new Date()
+            })
+            .where(eq(formEntries.id, entry.id));
+
+          console.log(`Updated status for ${sentTagNumbers.size} tags to "Sent to S-EDP"`);
+        }
+      } else {
+        console.warn(`No formEntries found for formCode: ${formCode}, contractItemId: ${contractItemId}`);
+      }
+    } catch (statusUpdateError) {
+      // Status 업데이트 실패는 경고로만 처리 (SEDP 전송은 성공했으므로)
+      console.warn("Failed to update status after SEDP send:", statusUpdateError);
+    }
+
+    return {
+      success: true,
+      message: "Data successfully sent to SEDP",
+      data: result
+    };
+  } catch (error: any) {
+    console.error("Error sending data to SEDP:", error);
+    return {
+      success: false,
+      message: error.message || "Failed to send data to SEDP"
+    };
+  }
+}
+
+
+export async function deleteFormDataByTags({
+  formCode,
+  contractItemId,
+  tagIdxs,
+}: {
+  formCode: string
+  contractItemId: number
+  tagIdxs: string[]
+}): Promise<{
+  error?: string
+  success?: boolean
+  deletedCount?: number
+  deletedTagsCount?: number
+}> {
+  try {
+    // 입력 검증
+    if (!formCode || !contractItemId || !Array.isArray(tagIdxs) || tagIdxs.length === 0) {
+      return {
+        error: "Missing required parameters: formCode, contractItemId, tagIdxs",
+      }
+    }
+
+    console.log(`[DELETE ACTION] Deleting tags for formCode: ${formCode}, contractItemId: ${contractItemId}, tagNos:`, tagIdxs)
+
+    // 트랜잭션으로 안전하게 처리
+    const result = await db.transaction(async (tx) => {
+      // 1. 현재 formEntry 데이터 가져오기
+      const currentEntryResult = await tx
+        .select()
+        .from(formEntries)
+        .where(
+          and(
+            eq(formEntries.formCode, formCode),
+            eq(formEntries.contractItemId, contractItemId)
+          )
+        )
+        .orderBy(desc(formEntries.updatedAt))
+        .limit(1)
+
+      if (currentEntryResult.length === 0) {
+        throw new Error("Form entry not found")
+      }
+
+      const currentEntry = currentEntryResult[0]
+      let currentData = Array.isArray(currentEntry.data) ? currentEntry.data : []
+
+      console.log(`[DELETE ACTION] Current data count: ${currentData.length}`)
+
+      // 2. 삭제할 항목들 필터링 (formEntries에서)
+      const updatedData = currentData.filter((item: any) =>
+        !tagIdxs.includes(item.TAG_IDX)
+      )
+
+      const deletedFromFormEntries = currentData.length - updatedData.length
+
+      console.log(`[DELETE ACTION] Updated data count: ${updatedData.length}`)
+      console.log(`[DELETE ACTION] Deleted ${deletedFromFormEntries} items from formEntries`)
+
+      if (deletedFromFormEntries === 0) {
+        throw new Error("No items were found to delete in formEntries")
+      }
+
+      // 3. tags 테이블에서 해당 태그들 삭제
+      const deletedTagsResult = await tx
+        .delete(tags)
+        .where(
+          and(
+            eq(tags.contractItemId, contractItemId),
+            inArray(tags.tagIdx, tagIdxs)
+          )
+        )
+        .returning({ tagNo: tags.tagNo })
+
+      const deletedTagsCount = deletedTagsResult.length
+
+      console.log(`[DELETE ACTION] Deleted ${deletedTagsCount} items from tags table`)
+      console.log(`[DELETE ACTION] Deleted tag numbers:`, deletedTagsResult.map(t => t.tagNo))
+
+      // 4. formEntries 데이터 업데이트
+      await tx
+        .update(formEntries)
+        .set({
+          data: updatedData,
+          updatedAt: new Date(),
+        })
+        .where(
+          and(
+            eq(formEntries.formCode, formCode),
+            eq(formEntries.contractItemId, contractItemId)
+          )
+        )
+
+      return {
+        deletedFromFormEntries,
+        deletedTagsCount,
+        deletedTagNumbers: deletedTagsResult.map(t => t.tagNo)
+      }
+    })
+
+    // 5. 캐시 무효화
+    const cacheKey = `form-data-${formCode}-${contractItemId}`
+    revalidateTag(cacheKey)
+    revalidateTag(`tags-${contractItemId}`)
+
+    // 페이지 재검증 (필요한 경우)
+
+    console.log(`[DELETE ACTION] Transaction completed successfully`)
+    console.log(`[DELETE ACTION] FormEntries deleted: ${result.deletedFromFormEntries}`)
+    console.log(`[DELETE ACTION] Tags deleted: ${result.deletedTagsCount}`)
+
+    return {
+      success: true,
+      deletedCount: result.deletedFromFormEntries,
+      deletedTagsCount: result.deletedTagsCount,
+    }
+
+  } catch (error) {
+    console.error("[DELETE ACTION] Error deleting form data:", error)
+    return {
+      error: error instanceof Error ? error.message : "An unexpected error occurred",
+    }
+  }
+}
+
+/**
+ * Server action to exclude selected tags by updating their status
+ */
+export async function excludeFormDataByTags({
+  formCode,
+  contractItemId,
+  tagNumbers,
+}: {
+  formCode: string
+  contractItemId: number
+  tagNumbers: string[]
+}): Promise<{
+  error?: string
+  success?: boolean
+  excludedCount?: number
+}> {
+  try {
+    // 입력 검증
+    if (!formCode || !contractItemId || !Array.isArray(tagNumbers) || tagNumbers.length === 0) {
+      return {
+        error: "Missing required parameters: formCode, contractItemId, tagNumbers",
+      }
+    }
+
+    console.log(`[EXCLUDE ACTION] Excluding tags for formCode: ${formCode}, contractItemId: ${contractItemId}, tagNumbers:`, tagNumbers)
+
+    // 트랜잭션으로 안전하게 처리
+    const result = await db.transaction(async (tx) => {
+      // 1. 현재 formEntry 데이터 가져오기
+      const currentEntryResult = await tx
+        .select()
+        .from(formEntries)
+        .where(
+          and(
+            eq(formEntries.formCode, formCode),
+            eq(formEntries.contractItemId, contractItemId)
+          )
+        )
+        .orderBy(desc(formEntries.updatedAt))
+        .limit(1)
+
+      if (currentEntryResult.length === 0) {
+        throw new Error("Form entry not found")
+      }
+
+      const currentEntry = currentEntryResult[0]
+      let currentData = Array.isArray(currentEntry.data) ? currentEntry.data : []
+
+      console.log(`[EXCLUDE ACTION] Current data count: ${currentData.length}`)
+
+      // 2. TAG_NO가 일치하는 항목들의 status를 'excluded'로 업데이트
+      let excludedCount = 0
+      const updatedData = currentData.map((item: any) => {
+        if (tagNumbers.includes(item.TAG_NO)) {
+          excludedCount++
+          return {
+            ...item,
+            status: 'excluded',
+            excludedAt: new Date().toISOString() // 제외 시간 추가 (선택사항)
+          }
+        }
+        return item
+      })
+
+      console.log(`[EXCLUDE ACTION] Excluded ${excludedCount} items`)
+
+      if (excludedCount === 0) {
+        throw new Error("No items were found to exclude")
+      }
+
+      // 3. formEntries 데이터 업데이트
+      await tx
+        .update(formEntries)
+        .set({
+          data: updatedData,
+          updatedAt: new Date(),
+        })
+        .where(
+          and(
+            eq(formEntries.formCode, formCode),
+            eq(formEntries.contractItemId, contractItemId)
+          )
+        )
+
+      return {
+        excludedCount,
+        excludedTagNumbers: tagNumbers
+      }
+    })
+
+    // 4. 캐시 무효화
+    const cacheKey = `form-data-${formCode}-${contractItemId}`
+    revalidateTag(cacheKey)
+
+    console.log(`[EXCLUDE ACTION] Transaction completed successfully`)
+    console.log(`[EXCLUDE ACTION] Tags excluded: ${result.excludedCount}`)
+
+    return {
+      success: true,
+      excludedCount: result.excludedCount,
+    }
+
+  } catch (error) {
+    console.error("[EXCLUDE ACTION] Error excluding form data:", error)
+    return {
+      error: error instanceof Error ? error.message : "An unexpected error occurred",
+    }
+  }
+}
+
+
+
+export async function getRegisters(projectCode: string): Promise<Register[]> {
+  try {
+    // 토큰(API 키) 가져오기
+    const apiKey = await getSEDPToken();
+    const SEDP_API_BASE_URL = process.env.SEDP_API_BASE_URL || 'http://sedpwebapi.ship.samsung.co.kr/api';
+
+    const response = await fetch(
+      `${SEDP_API_BASE_URL}/Register/Get`,
+      {
+        method: 'POST',
+        headers: {
+          'Content-Type': 'application/json',
+          'accept': '*/*',
+          'ApiKey': apiKey,
+          'ProjectNo': projectCode
+        },
+        body: JSON.stringify({
+          ProjectNo: projectCode,
+          ContainDeleted: false
+        })
+      }
+    );
+
+    if (!response.ok) {
+      throw new Error(`레지스터 요청 실패: ${response.status} ${response.statusText}`);
+    }
+
+    // 안전하게 JSON 파싱
+    let data;
+    try {
+      data = await response.json();
+    } catch (parseError) {
+      console.error(`프로젝트 ${projectCode}의 레지스터 응답 파싱 실패:`, parseError);
+      // 응답 내용 로깅
+      const text = await response.clone().text();
+      console.log(`응답 내용: ${text.substring(0, 200)}${text.length > 200 ? '...' : ''}`);
+      throw new Error(`레지스터 응답 파싱 실패: ${parseError instanceof Error ? parseError.message : String(parseError)}`);
+    }
+
+    // 결과를 배열로 변환 (단일 객체인 경우 배열로 래핑)
+    const registers: Register[] = Array.isArray(data) ? data : [data];
+
+    console.log(`프로젝트 ${projectCode}에서 ${registers.length}개의 유효한 레지스터를 가져왔습니다.`);
+    return registers;
+  } catch (error) {
+    console.error(`프로젝트 ${projectCode}의 레지스터 가져오기 실패:`, error);
+    throw error;
+  }
+}
\ No newline at end of file
diff --git a/lib/forms-plant/stat.ts b/lib/forms-plant/stat.ts
new file mode 100644
index 00000000..f13bab61
--- /dev/null
+++ b/lib/forms-plant/stat.ts
@@ -0,0 +1,375 @@
+"use server"
+
+import db from "@/db/db"
+import { vendors, contracts, contractItems, forms, formEntries, formMetas, tags, 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, contractItemId: number, 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: forms.id,
+        formCode: forms.formCode,
+        contractItemId: forms.contractItemId
+      })
+      .from(forms)
+      .where(
+        and(
+          eq(forms.contractItemId, contractItemId),
+          eq(forms.formCode, formCode)
+        )
+      )
+
+    vendorFormCount += formsList.length
+
+    // 5. formEntries 조회
+    const entriesList = await db
+      .select({
+        id: formEntries.id,
+        formCode: formEntries.formCode,
+        data: formEntries.data
+      })
+      .from(formEntries)
+      .where(
+        and(
+          eq(formEntries.contractItemId, contractItemId),
+          eq(formEntries.formCode, formCode)
+        )
+      )
+
+    // 6. TAG별 편집 가능 필드 조회
+    const editableFieldsByTag = await getEditableFieldsByTag(contractItemId, 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.error('Error getting vendor form status:', error)
+    throw new Error('벤더별 Form 입력 현황 조회 중 오류가 발생했습니다.')
+  }
+}
\ No newline at end of file