// 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 필드 추가, source 필드 보존)
    const oldItem = dataArray[idx];
    const updatedItem = {
      ...oldItem,
      ...newData,
      TAG_NO: oldItem.TAG_NO, // TAG_NO 변경 불가 시 유지
      source: oldItem.source, // 출처 필드 보존 (불변)
      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: row.status === "Deleted", // Set OP_DELETE based on status
      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("=".repeat(80));
    console.log("[SEDP Overwrite API] 요청 시작");
    console.log(`[SEDP Overwrite API] URL: ${SEDP_API_BASE_URL}/AdapterData/Overwrite`);
    console.log(`[SEDP Overwrite API] ProjectNo: ${projectCode}`);
    console.log("[SEDP Overwrite API] Request Body (전체):");
    console.log(JSON.stringify(sedpData, null, 2));

    // Check if there are any items with OP_DELETE=true
    const deleteItems = sedpData.filter(item => item.OP_DELETE === true);
    if (deleteItems.length > 0) {
      console.log("=".repeat(80));
      console.log(`[SEDP DELETE] ⚠️  OP_DELETE=true인 항목 ${deleteItems.length}개 발견`);
      console.log("[SEDP DELETE] 삭제 요청 항목만 추출:");
      console.log(JSON.stringify(deleteItems, null, 2));
    }
    console.log("=".repeat(80));

    // 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)) {
              // 삭제된 항목(status="Deleted")은 status를 유지하고, 
              // 나머지 항목만 "Sent to S-EDP"로 변경
              if (item.status === "Deleted") {
                return item; // Keep status="Deleted" unchanged
              }
              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,
  projectId,
}: {
  formCode: string
  contractItemId: number
  tagIdxs: string[]
  projectId?: number
}): Promise<{
  error?: string
  success?: boolean
  deletedCount?: number
  deletedTagsCount?: number
  sedpDeleteSuccess?: boolean
  sedpDeleteError?: string
}> {
  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}, tagIdxs:`, tagIdxs)

    // 1. 트랜잭션 전에 삭제할 항목들을 미리 조회하여 저장 (S-EDP 전송용)
    const entryForSedp = await db
      .select()
      .from(formEntries)
      .where(
        and(
          eq(formEntries.formCode, formCode),
          eq(formEntries.contractItemId, contractItemId)
        )
      )
      .orderBy(desc(formEntries.updatedAt))
      .limit(1)

    let itemsToSendToSedp: Record<string, unknown>[] = []
    
    if (entryForSedp.length > 0) {
      const dataForSedp = Array.isArray(entryForSedp[0].data) ? entryForSedp[0].data : []
      // 삭제할 항목들만 추출 (S-EDP로 OP_DELETE=true 전송용)
      itemsToSendToSedp = dataForSedp.filter((item: Record<string, unknown>) =>
        tagIdxs.includes(item.TAG_IDX as string)
      )
      
      console.log(`[DELETE ACTION] Found ${itemsToSendToSedp.length} items to send to SEDP before deletion`)
    }

    // 2. 트랜잭션으로 안전하게 처리 (완전 삭제)
    const result = await db.transaction(async (tx) => {
      // 2-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]
      const currentData = Array.isArray(currentEntry.data) ? currentEntry.data : []

      console.log(`[DELETE ACTION] Current data count: ${currentData.length}`)

      // 2-2. 삭제할 항목들을 완전히 제거 (status 마킹이 아님)
      const updatedData = currentData.filter((item: Record<string, unknown>) =>
        !tagIdxs.includes(item.TAG_IDX as string)
      )

      const deletedFromFormEntries = currentData.length - updatedData.length

      console.log(`[DELETE ACTION] Updated data count: ${updatedData.length}`)
      console.log(`[DELETE ACTION] Completely removed ${deletedFromFormEntries} items from formEntries`)

      if (deletedFromFormEntries === 0) {
        throw new Error("No items were found to delete in formEntries")
      }

      // 2-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))

      // 2-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)
      }
    })

    // 3. SEDP에 OP_DELETE=true로 전송 (projectId가 제공된 경우)
    let sedpDeleteSuccess = false
    let sedpDeleteError: string | undefined

    if (projectId) {
      try {
        console.log(`[DELETE ACTION] Attempting to send deleted items to SEDP for projectId: ${projectId}`)
        
        // 미리 저장해둔 삭제 항목들 사용
        const deletedItems = itemsToSendToSedp

        if (deletedItems.length > 0) {
          console.log(`[DELETE ACTION] Found ${deletedItems.length} deleted items before deduplication`)
          
          // 삭제 항목들에 status="Deleted" 마킹 (S-EDP에서 OP_DELETE=true로 전송하기 위해)
          const deletedItemsWithStatus = deletedItems.map((item: Record<string, unknown>) => ({
            ...item,
            status: "Deleted"
          } as Record<string, unknown>))
          
          console.log(`[DELETE ACTION] Deleted items TAG_NO list:`, deletedItemsWithStatus.map(item => ({ 
            TAG_NO: item.TAG_NO, 
            TAG_IDX: item.TAG_IDX 
          })))
          
          // Deduplicate by TAG_NO: keep only the oldest TAG_IDX for each TAG_NO
          // S-EDP has a unique key on (PROJ_NO, TOOLID, SCOPE, REV_NO, ITM_NO)
          // ITM_NO is mapped to TAG_NO, so we need to ensure only ONE item per TAG_NO
          const tagNoMap = new Map<string, Record<string, unknown>>();
          
          for (const item of deletedItemsWithStatus) {
            const tagNo = item.TAG_NO as string;
            const tagIdx = item.TAG_IDX as string;
            
            if (!tagNo) {
              console.warn(`[DELETE ACTION] Skipping item without TAG_NO`);
              continue;
            }
            
            if (!tagIdx) {
              console.warn(`[DELETE ACTION] Skipping item without TAG_IDX for TAG_NO "${tagNo}"`);
              continue;
            }
            
            if (!tagNoMap.has(tagNo)) {
              console.log(`[DELETE ACTION] TAG_NO "${tagNo}": Adding first occurrence with TAG_IDX ${tagIdx}`);
              tagNoMap.set(tagNo, item);
            } else {
              // Compare TAG_IDX (MongoDB ObjectID) - keep the oldest one
              const existingItem = tagNoMap.get(tagNo)!;
              const existingTagIdx = existingItem.TAG_IDX as string;
              
              // MongoDB ObjectID는 시간순으로 정렬 가능 (사전순으로 작은 값이 더 오래됨)
              if (tagIdx < existingTagIdx) {
                console.log(`[DELETE ACTION] TAG_NO "${tagNo}": Replacing TAG_IDX ${existingTagIdx} with older ${tagIdx}`);
                tagNoMap.set(tagNo, item);
              } else {
                console.log(`[DELETE ACTION] TAG_NO "${tagNo}": Skipping TAG_IDX ${tagIdx} (keeping older ${existingTagIdx})`);
              }
            }
          }
          
          const uniqueDeletedItems = Array.from(tagNoMap.values());
          console.log(`[DELETE ACTION] ✓ Deduplication complete: ${deletedItemsWithStatus.length} items → ${uniqueDeletedItems.length} unique items`)
          console.log(`[DELETE ACTION] Final items to send to SEDP:`, uniqueDeletedItems.map(item => ({ 
            TAG_NO: item.TAG_NO, 
            TAG_IDX: item.TAG_IDX 
          })))
          
          // Get form meta to get columns
          const formMetaResult = await db.query.formMetas.findFirst({
            where: and(
              eq(formMetas.formCode, formCode),
              eq(formMetas.projectId, projectId)
            )
          })

          if (formMetaResult && formMetaResult.columns) {
            // Send deleted items to SEDP (will have OP_DELETE=true)
            const sedpResult = await sendFormDataToSEDP(
              formCode,
              projectId,
              contractItemId,
              uniqueDeletedItems as GenericData[],
              formMetaResult.columns as DataTableColumnJSON[]
            )

            if (sedpResult.success) {
              sedpDeleteSuccess = true
              console.log(`[DELETE ACTION] Successfully sent ${uniqueDeletedItems.length} unique deleted items to SEDP with OP_DELETE=true`)
            } else {
              sedpDeleteError = sedpResult.message
              console.error(`[DELETE ACTION] Failed to send deleted items to SEDP:`, sedpResult.message)
            }
          } else {
            sedpDeleteError = "Form meta or columns not found"
            console.warn(`[DELETE ACTION] Form meta not found for formCode: ${formCode}, projectId: ${projectId}`)
          }
        } else {
          console.warn(`[DELETE ACTION] No deleted items found to send to SEDP`)
        }
      } catch (sedpError) {
        sedpDeleteError = sedpError instanceof Error ? sedpError.message : "SEDP delete failed"
        console.error(`[DELETE ACTION] SEDP delete error:`, sedpError)
        // SEDP 삭제 실패는 경고로만 처리 (로컬 삭제는 성공했으므로)
      }
    }

    // 4. 캐시 무효화
    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}`)
    console.log(`[DELETE ACTION] SEDP delete success: ${sedpDeleteSuccess}`)

    return {
      success: true,
      deletedCount: result.deletedFromFormEntries,
      deletedTagsCount: result.deletedTagsCount,
      sedpDeleteSuccess,
      sedpDeleteError,
    }

  } catch (error) {
    console.error("[DELETE ACTION] Error deleting form data:", error)
    return {
      error: error instanceof Error ? error.message : "An unexpected error occurred",
    }
  }
}