path: root/lib/forms/services.ts

// 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별 편집 가능 필드 조회 함수
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 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()로 캐시 무효화.
 */
type UpdateResponse = {
  success: boolean;
  message: string;
  data?: any;
};

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}`
          : "알 수 없는 오류가 발생했습니다.",
    };
  }
}
// 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; 
}

interface SEDPDataItem {
  TAG_NO: string;
  TAG_DESC: 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,
  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>();
  
  // Transform each row
  const transformedItems = [];
  
  for (const row of tableData) {
    // Create base SEDP item with required fields
    const sedpItem: SEDPDataItem = {
      TAG_NO: row.TAG_NO || "",
      TAG_DESC: row.TAG_DESC || "",
      ATTRIBUTES: [],
      // SCOPE: objectCode,
      SCOPE: formCode,
      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,
      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 || ""
          };
          
          // 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,
  designerNo: string = "253213"
): Promise<SEDPDataItem[]> {
  // Use the utility function within the async Server Action
  return transformDataToSEDPFormat(
    tableData,
    columnsJSON,
    formCode,
    objectCode,
    projectNo,
    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;
}

/**
 * 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/Create`,
      {
        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
    );
    
    // 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,
  tagNos,
}: {
  formCode: string
  contractItemId: number
  tagNos: string[]
}): Promise<{ 
  error?: string
  success?: boolean
  deletedCount?: number
  deletedTagsCount?: number
}> {
  try {
    // 입력 검증
    if (!formCode || !contractItemId || !Array.isArray(tagNos) || tagNos.length === 0) {
      return {
        error: "Missing required parameters: formCode, contractItemId, tagNos",
      }
    }

    console.log(`[DELETE ACTION] Deleting tags for formCode: ${formCode}, contractItemId: ${contractItemId}, tagNos:`, tagNos)

    // 트랜잭션으로 안전하게 처리
    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) => 
        !tagNos.includes(item.TAG_NO)
      )

      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.tagNo, tagNos)
          )
        )
        .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",
    }
  }
}