"use server";

import { GetAvlListSchema, GetAvlDetailSchema, GetProjectAvlSchema, GetStandardAvlSchema } from "./validations";
import { AvlListItem, AvlDetailItem, CreateAvlListInput, UpdateAvlListInput, ActionResult, AvlVendorInfoInput } from "./types";
import type { NewAvlVendorInfo, AvlVendorInfo } from "@/db/schema/avl/avl";
import db from "@/db/db";
import { avlList, avlVendorInfo } from "@/db/schema/avl/avl";
import { eq, and, or, ilike, count, desc, asc, sql } from "drizzle-orm";
import { debugLog, debugError, debugSuccess, debugWarn } from "@/lib/debug-utils";
import { revalidateTag, unstable_cache } from "next/cache";

/**
 * AVL 리스트 조회
 * avl_list 테이블에서 실제 데이터를 조회합니다.
 */
const _getAvlLists = async (input: GetAvlListSchema) => {
  try {
    const offset = (input.page - 1) * input.perPage;

    debugLog('AVL 리스트 조회 시작', { input, offset });

    // 검색 조건 구성
    const whereConditions: any[] = [];

    // 검색어 기반 필터링
    if (input.search) {
      const searchTerm = `%${input.search}%`;
      whereConditions.push(
        or(
          ilike(avlList.constructionSector, searchTerm),
          ilike(avlList.projectCode, searchTerm),
          ilike(avlList.shipType, searchTerm),
          ilike(avlList.avlKind, searchTerm)
        )
      );
    }

    // 필터 조건 추가
    if (input.isTemplate === "true") {
      whereConditions.push(eq(avlList.isTemplate, true));
    } else if (input.isTemplate === "false") {
      whereConditions.push(eq(avlList.isTemplate, false));
    }

    if (input.constructionSector) {
      whereConditions.push(ilike(avlList.constructionSector, `%${input.constructionSector}%`));
    }
    if (input.projectCode) {
      whereConditions.push(ilike(avlList.projectCode, `%${input.projectCode}%`));
    }
    if (input.shipType) {
      whereConditions.push(ilike(avlList.shipType, `%${input.shipType}%`));
    }
    if (input.avlKind) {
      whereConditions.push(ilike(avlList.avlKind, `%${input.avlKind}%`));
    }
    if (input.htDivision) {
      whereConditions.push(eq(avlList.htDivision, input.htDivision));
    }
    if (input.rev) {
      whereConditions.push(eq(avlList.rev, parseInt(input.rev)));
    }

    // 정렬 조건 구성
    const orderByConditions: any[] = [];
    input.sort.forEach((sortItem) => {
      const column = sortItem.id as keyof typeof avlList;

      if (column && avlList[column]) {
        if (sortItem.desc) {
          orderByConditions.push(sql`${avlList[column]} desc`);
        } else {
          orderByConditions.push(sql`${avlList[column]} asc`);
        }
      }
    });

    // 기본 정렬 (등재일 내림차순)
    if (orderByConditions.length === 0) {
      orderByConditions.push(desc(avlList.createdAt));
    }

    // 총 개수 조회
    const totalCount = await db
      .select({ count: count() })
      .from(avlList)
      .where(and(...whereConditions));

    // 데이터 조회
    const data = await db
      .select()
      .from(avlList)
      .where(and(...whereConditions))
      .orderBy(...orderByConditions)
      .limit(input.perPage)
      .offset(offset);

    // 데이터 변환 (timestamp -> string)
    const transformedData: AvlListItem[] = data.map((item, index) => ({
      ...item,
      no: offset + index + 1,
      selected: false,
      createdAt: ((item as any).createdAt as Date)?.toISOString().split('T')[0] || '',
      updatedAt: ((item as any).updatedAt as Date)?.toISOString().split('T')[0] || '',
      // 추가 필드들 (실제로는 JOIN이나 별도 쿼리로 가져와야 함)
      projectInfo: item.projectCode || '',
      shipType: item.shipType || '',
      avlType: item.avlKind || '',
      htDivision: item.htDivision || '',
      rev: item.rev || 1,
    }));

    const pageCount = Math.ceil(totalCount[0].count / input.perPage);

    debugSuccess('AVL 리스트 조회 완료', { recordCount: transformedData.length, pageCount });

    return {
      data: transformedData,
      pageCount
    };
  } catch (err) {
    debugError('AVL 리스트 조회 실패', { error: err, input });
    console.error("Error in getAvlLists:", err);
    return { data: [], pageCount: 0 };
  }
};

// 캐시된 버전 export - 동일한 입력에 대해 캐시 사용
export const getAvlLists = unstable_cache(
  _getAvlLists,
  ['avl-list'],
  {
    tags: ['avl-list'],
    revalidate: 300, // 5분 캐시
  }
);

/**
 * AVL 상세 정보 조회 (특정 AVL ID의 모든 vendor info)
 */
const _getAvlDetail = async (input: GetAvlDetailSchema & { avlListId: number }) => {
  try {
    const offset = (input.page - 1) * input.perPage;

    debugLog('AVL 상세 조회 시작', { input, offset });

    // 검색 조건 구성
    const whereConditions: any[] = [];

    // AVL 리스트 ID 필터 (필수)
    whereConditions.push(eq(avlVendorInfo.avlListId, input.avlListId));

    // 검색어 기반 필터링
    if (input.search) {
      const searchTerm = `%${input.search}%`;
      whereConditions.push(
        or(
          ilike(avlVendorInfo.disciplineName, searchTerm),
          ilike(avlVendorInfo.materialNameCustomerSide, searchTerm),
          ilike(avlVendorInfo.vendorName, searchTerm),
          ilike(avlVendorInfo.avlVendorName, searchTerm)
        )
      );
    }

    // 필터 조건 추가
    if (input.equipBulkDivision) {
      whereConditions.push(eq(avlVendorInfo.equipBulkDivision, input.equipBulkDivision === "EQUIP" ? "E" : "B"));
    }
    if (input.disciplineCode) {
      whereConditions.push(ilike(avlVendorInfo.disciplineCode, `%${input.disciplineCode}%`));
    }
    if (input.disciplineName) {
      whereConditions.push(ilike(avlVendorInfo.disciplineName, `%${input.disciplineName}%`));
    }
    if (input.materialNameCustomerSide) {
      whereConditions.push(ilike(avlVendorInfo.materialNameCustomerSide, `%${input.materialNameCustomerSide}%`));
    }
    if (input.packageCode) {
      whereConditions.push(ilike(avlVendorInfo.packageCode, `%${input.packageCode}%`));
    }
    if (input.packageName) {
      whereConditions.push(ilike(avlVendorInfo.packageName, `%${input.packageName}%`));
    }
    if (input.materialGroupCode) {
      whereConditions.push(ilike(avlVendorInfo.materialGroupCode, `%${input.materialGroupCode}%`));
    }
    if (input.materialGroupName) {
      whereConditions.push(ilike(avlVendorInfo.materialGroupName, `%${input.materialGroupName}%`));
    }
    if (input.vendorCode) {
      whereConditions.push(ilike(avlVendorInfo.vendorCode, `%${input.vendorCode}%`));
    }
    if (input.vendorName) {
      whereConditions.push(ilike(avlVendorInfo.vendorName, `%${input.vendorName}%`));
    }
    if (input.avlVendorName) {
      whereConditions.push(ilike(avlVendorInfo.avlVendorName, `%${input.avlVendorName}%`));
    }
    if (input.tier) {
      whereConditions.push(ilike(avlVendorInfo.tier, `%${input.tier}%`));
    }
    if (input.faTarget === "true") {
      whereConditions.push(eq(avlVendorInfo.faTarget, true));
    } else if (input.faTarget === "false") {
      whereConditions.push(eq(avlVendorInfo.faTarget, false));
    }
    if (input.faStatus) {
      whereConditions.push(ilike(avlVendorInfo.faStatus, `%${input.faStatus}%`));
    }
    if (input.isAgent === "true") {
      whereConditions.push(eq(avlVendorInfo.isAgent, true));
    } else if (input.isAgent === "false") {
      whereConditions.push(eq(avlVendorInfo.isAgent, false));
    }
    if (input.contractSignerName) {
      whereConditions.push(ilike(avlVendorInfo.contractSignerName, `%${input.contractSignerName}%`));
    }
    if (input.headquarterLocation) {
      whereConditions.push(ilike(avlVendorInfo.headquarterLocation, `%${input.headquarterLocation}%`));
    }
    if (input.manufacturingLocation) {
      whereConditions.push(ilike(avlVendorInfo.manufacturingLocation, `%${input.manufacturingLocation}%`));
    }
    if (input.hasAvl === "true") {
      whereConditions.push(eq(avlVendorInfo.hasAvl, true));
    } else if (input.hasAvl === "false") {
      whereConditions.push(eq(avlVendorInfo.hasAvl, false));
    }
    if (input.isBlacklist === "true") {
      whereConditions.push(eq(avlVendorInfo.isBlacklist, true));
    } else if (input.isBlacklist === "false") {
      whereConditions.push(eq(avlVendorInfo.isBlacklist, false));
    }
    if (input.isBcc === "true") {
      whereConditions.push(eq(avlVendorInfo.isBcc, true));
    } else if (input.isBcc === "false") {
      whereConditions.push(eq(avlVendorInfo.isBcc, false));
    }
    if (input.techQuoteNumber) {
      whereConditions.push(ilike(avlVendorInfo.techQuoteNumber, `%${input.techQuoteNumber}%`));
    }
    if (input.quoteCode) {
      whereConditions.push(ilike(avlVendorInfo.quoteCode, `%${input.quoteCode}%`));
    }
    if (input.quoteCountry) {
      whereConditions.push(ilike(avlVendorInfo.quoteCountry, `%${input.quoteCountry}%`));
    }
    if (input.remark) {
      whereConditions.push(ilike(avlVendorInfo.remark, `%${input.remark}%`));
    }

    // 정렬 조건 구성
    const orderByConditions: any[] = [];
    input.sort.forEach((sortItem) => {
      const column = sortItem.id as keyof typeof avlVendorInfo;

      if (column && avlVendorInfo[column]) {
        if (sortItem.desc) {
          orderByConditions.push(sql`${avlVendorInfo[column]} desc`);
        } else {
          orderByConditions.push(sql`${avlVendorInfo[column]} asc`);
        }
      }
    });

    // 기본 정렬
    if (orderByConditions.length === 0) {
      orderByConditions.push(asc(avlVendorInfo.id));
    }

    // 총 개수 조회
    const totalCount = await db
      .select({ count: count() })
      .from(avlVendorInfo)
      .where(and(...whereConditions));

    // 데이터 조회
    const data = await db
      .select()
      .from(avlVendorInfo)
      .where(and(...whereConditions))
      .orderBy(...orderByConditions)
      .limit(input.perPage)
      .offset(offset);

    // 데이터 변환 (timestamp -> string, DB 필드 -> UI 필드)
    const transformedData: AvlDetailItem[] = data.map((item, index) => ({
      ...(item as any),
      no: offset + index + 1,
      selected: false,
      createdAt: ((item as any).createdAt as Date)?.toISOString().split('T')[0] || '',
      updatedAt: ((item as any).updatedAt as Date)?.toISOString().split('T')[0] || '',
      // UI 표시용 필드 변환
      equipBulkDivision: item.equipBulkDivision === "E" ? "EQUIP" : "BULK",
      faTarget: item.faTarget ?? false,
      agentStatus: item.isAgent ? "예" : "아니오",
      shiAvl: item.hasAvl ?? false,
      shiBlacklist: item.isBlacklist ?? false,
      shiBcc: item.isBcc ?? false,
      salesQuoteNumber: item.techQuoteNumber || '',
      quoteCode: item.quoteCode || '',
      salesVendorInfo: item.quoteVendorName || '',
      salesCountry: item.quoteCountry || '',
      totalAmount: item.quoteTotalAmount ? item.quoteTotalAmount.toString() : '',
      quoteReceivedDate: item.quoteReceivedDate || '',
      recentQuoteDate: item.recentQuoteDate || '',
      recentQuoteNumber: item.recentQuoteNumber || '',
      recentOrderDate: item.recentOrderDate || '',
      recentOrderNumber: item.recentOrderNumber || '',
      remarks: item.remark || '',
    }));

    const pageCount = Math.ceil(totalCount[0].count / input.perPage);

    debugSuccess('AVL 상세 조회 완료', { recordCount: transformedData.length, pageCount });

    return {
      data: transformedData,
      pageCount
    };
  } catch (err) {
    debugError('AVL 상세 조회 실패', { error: err, input });
    console.error("Error in getAvlDetail:", err);
    return { data: [], pageCount: 0 };
  }
};

// 캐시된 버전 export
export const getAvlDetail = unstable_cache(
  _getAvlDetail,
  ['avl-detail'],
  {
    tags: ['avl-detail'],
    revalidate: 300, // 5분 캐시
  }
);

/**
 * AVL 리스트 상세 정보 조회 (단일)
 */
export async function getAvlListById(id: number): Promise<AvlListItem | null> {
  try {
    const data = await db
      .select()
      .from(avlList)
      .where(eq(avlList.id, id))
      .limit(1);

    if (data.length === 0) {
      return null;
    }

    const item = data[0];

    // 데이터 변환
    const transformedData: AvlListItem = {
      ...item,
      no: 1,
      selected: false,
      createdAt: ((item as any).createdAt as Date)?.toISOString().split('T')[0] || '',
      updatedAt: ((item as any).updatedAt as Date)?.toISOString().split('T')[0] || '',
      projectInfo: item.projectCode || '',
      shipType: item.shipType || '',
      avlType: item.avlKind || '',
      htDivision: item.htDivision || '',
      rev: item.rev || 1,
    };

    return transformedData;
  } catch (err) {
    console.error("Error in getAvlListById:", err);
    return null;
  }
}

/**
 * AVL Vendor Info 상세 정보 조회 (단일)
 */
export async function getAvlVendorInfoById(id: number): Promise<AvlDetailItem | null> {
  try {
    const data = await db
      .select()
      .from(avlVendorInfo)
      .where(eq(avlVendorInfo.id, id))
      .limit(1);

    if (data.length === 0) {
      return null;
    }

    const item = data[0];

    // 데이터 변환
    const transformedData: AvlDetailItem = {
      ...(item as any),
      no: 1,
      selected: false,
      createdAt: ((item as any).createdAt as Date)?.toISOString().split('T')[0] || '',
      updatedAt: ((item as any).updatedAt as Date)?.toISOString().split('T')[0] || '',
      equipBulkDivision: item.equipBulkDivision === "E" ? "EQUIP" : "BULK",
      faTarget: item.faTarget ?? false,
      agentStatus: item.isAgent ? "예" : "아니오",
      shiAvl: item.hasAvl ?? false,
      shiBlacklist: item.isBlacklist ?? false,
      shiBcc: item.isBcc ?? false,
      salesQuoteNumber: item.techQuoteNumber || '',
      quoteCode: item.quoteCode || '',
      salesVendorInfo: item.quoteVendorName || '',
      salesCountry: item.quoteCountry || '',
      totalAmount: item.quoteTotalAmount ? item.quoteTotalAmount.toString() : '',
      quoteReceivedDate: item.quoteReceivedDate || '',
      recentQuoteDate: item.recentQuoteDate || '',
      recentQuoteNumber: item.recentQuoteNumber || '',
      recentOrderDate: item.recentOrderDate || '',
      recentOrderNumber: item.recentOrderNumber || '',
      remarks: item.remark || '',
    };

    return transformedData;
  } catch (err) {
    console.error("Error in getAvlVendorInfoById:", err);
    return null;
  }
}

/**
 * AVL 액션 처리
 * 신규등록, 일괄입력, 저장 등의 액션을 처리
 */
export async function handleAvlAction(
  action: string,
  data?: any
): Promise<ActionResult> {
  try {
    switch (action) {
      case "new-registration":
        return { success: true, message: "신규 AVL 등록 모드" };

      case "standard-registration":
        return { success: true, message: "표준 AVL 등재 모드" };

      case "project-registration":
        return { success: true, message: "프로젝트 AVL 등재 모드" };

      case "bulk-import":
        if (!data?.file) {
          return { success: false, message: "업로드할 파일이 없습니다." };
        }
        console.log("일괄 입력 처리:", data.file);
        return { success: true, message: "일괄 입력 처리가 시작되었습니다." };

      case "save":
        console.log("변경사항 저장:", data);
        return { success: true, message: "변경사항이 저장되었습니다." };

      case "edit":
        if (!data?.id) {
          return { success: false, message: "수정할 항목 ID가 없습니다." };
        }
        return { success: true, message: "수정 모달이 열렸습니다.", data: { id: data.id } };

      case "delete":
        if (!data?.id) {
          return { success: false, message: "삭제할 항목 ID가 없습니다." };
        }
        // 실제 삭제 처리
        const deleteResult = await deleteAvlList(data.id);
        if (deleteResult) {
          return { success: true, message: "항목이 삭제되었습니다.", data: { id: data.id } };
        } else {
          return { success: false, message: "항목 삭제에 실패했습니다." };
        }

      case "view-detail":
        if (!data?.id) {
          return { success: false, message: "조회할 항목 ID가 없습니다." };
        }
        return { success: true, message: "상세 정보가 조회되었습니다.", data: { id: data.id } };

      default:
        return { success: false, message: `알 수 없는 액션입니다: ${action}` };
    }
  } catch (err) {
    console.error("Error in handleAvlAction:", err);
    return { success: false, message: "액션 처리 중 오류가 발생했습니다." };
  }
}

// 클라이언트에서 호출할 수 있는 서버 액션 래퍼들
export async function createAvlListAction(data: CreateAvlListInput): Promise<AvlListItem | null> {
  return await createAvlList(data);
}

export async function updateAvlListAction(id: number, data: UpdateAvlListInput): Promise<AvlListItem | null> {
  return await updateAvlList(id, data);
}

export async function deleteAvlListAction(id: number): Promise<boolean> {
  return await deleteAvlList(id);
}

export async function handleAvlActionAction(action: string, data?: any): Promise<ActionResult> {
  return await handleAvlAction(action, data);
}

/**
 * AVL 리스트 생성
 */
export async function createAvlList(data: CreateAvlListInput): Promise<AvlListItem | null> {
  try {
    debugLog('AVL 리스트 생성 시작', { inputData: data });

    const currentTimestamp = new Date();

    // 데이터베이스에 삽입할 데이터 준비
    const insertData = {
      isTemplate: data.isTemplate ?? false,
      constructionSector: data.constructionSector,
      projectCode: data.projectCode,
      shipType: data.shipType,
      avlKind: data.avlKind,
      htDivision: data.htDivision,
      rev: data.rev ?? 1,
      createdBy: data.createdBy || 'system',
      updatedBy: data.updatedBy || 'system',
    };

    debugLog('DB INSERT 시작', { table: 'avl_list', data: insertData });

    // 데이터베이스에 삽입
    const result = await db
      .insert(avlList)
      .values(insertData)
      .returning();

    if (result.length === 0) {
      debugError('DB 삽입 실패: 결과가 없음', { insertData });
      throw new Error("Failed to create AVL list");
    }

    debugSuccess('DB INSERT 완료', { table: 'avl_list', result: result[0] });

    const createdItem = result[0];

    // 생성된 데이터를 AvlListItem 타입으로 변환
    const transformedData: AvlListItem = {
      ...createdItem,
      no: 1,
      selected: false,
      createdAt: createdItem.createdAt ? createdItem.createdAt.toISOString().split('T')[0] : '',
      updatedAt: createdItem.updatedAt ? createdItem.updatedAt.toISOString().split('T')[0] : '',
      projectInfo: createdItem.projectCode || '',
      shipType: createdItem.shipType || '',
      avlType: createdItem.avlKind || '',
      htDivision: createdItem.htDivision || '',
      rev: createdItem.rev || 1,
    };

    debugSuccess('AVL 리스트 생성 완료', { result: transformedData });

    // 캐시 무효화
    revalidateTag('avl-list');

    debugSuccess('AVL 캐시 무효화 완료', { tags: ['avl-list'] });

    return transformedData;
  } catch (err) {
    debugError('AVL 리스트 생성 실패', { error: err, inputData: data });
    console.error("Error in createAvlList:", err);
    return null;
  }
}

/**
 * AVL 리스트 업데이트
 */
export async function updateAvlList(id: number, data: UpdateAvlListInput): Promise<AvlListItem | null> {
  try {
    debugLog('AVL 리스트 업데이트 시작', { id, updateData: data });

    const currentTimestamp = new Date();

    // 업데이트할 데이터 준비
    const updateData: any = {};

    if (data.isTemplate !== undefined) updateData.isTemplate = data.isTemplate;
    if (data.constructionSector !== undefined) updateData.constructionSector = data.constructionSector;
    if (data.projectCode !== undefined) updateData.projectCode = data.projectCode;
    if (data.shipType !== undefined) updateData.shipType = data.shipType;
    if (data.avlKind !== undefined) updateData.avlKind = data.avlKind;
    if (data.htDivision !== undefined) updateData.htDivision = data.htDivision;
    if (data.rev !== undefined) updateData.rev = data.rev;
    if (data.createdBy !== undefined) updateData.createdBy = data.createdBy;
    if (data.updatedBy !== undefined) updateData.updatedBy = data.updatedBy;

    updateData.updatedAt = currentTimestamp;

    // 업데이트할 데이터가 없는 경우
    if (Object.keys(updateData).length <= 1) {
      return await getAvlListById(id);
    }

    // 데이터베이스 업데이트
    const result = await db
      .update(avlList)
      .set(updateData)
      .where(eq(avlList.id, id))
      .returning();

    if (result.length === 0) {
      throw new Error("AVL list not found or update failed");
    }

    const updatedItem = result[0];

    // 업데이트된 데이터를 AvlListItem 타입으로 변환
    const transformedData: AvlListItem = {
      ...updatedItem,
      no: 1,
      selected: false,
      createdAt: updatedItem.createdAt ? updatedItem.createdAt.toISOString().split('T')[0] : '',
      updatedAt: updatedItem.updatedAt ? updatedItem.updatedAt.toISOString().split('T')[0] : '',
      projectInfo: updatedItem.projectCode || '',
      shipType: updatedItem.shipType || '',
      avlType: updatedItem.avlKind || '',
      htDivision: updatedItem.htDivision || '',
      rev: updatedItem.rev || 1,
    };

    debugSuccess('AVL 리스트 업데이트 완료', { id, result: transformedData });

    // 캐시 무효화
    revalidateTag('avl-list');

    return transformedData;
  } catch (err) {
    debugError('AVL 리스트 업데이트 실패', { error: err, id, updateData: data });
    console.error("Error in updateAvlList:", err);
    return null;
  }
}

/**
 * AVL 리스트 삭제
 */
export async function deleteAvlList(id: number): Promise<boolean> {
  try {
    debugLog('AVL 리스트 삭제 시작', { id });

    // 데이터베이스에서 삭제
    const result = await db
      .delete(avlList)
      .where(eq(avlList.id, id));

    // 삭제 확인을 위한 재조회
    const checkDeleted = await db
      .select({ id: avlList.id })
      .from(avlList)
      .where(eq(avlList.id, id))
      .limit(1);

    const isDeleted = checkDeleted.length === 0;

    if (isDeleted) {
      debugSuccess('AVL 리스트 삭제 완료', { id });
      revalidateTag('avl-list');
    } else {
      debugWarn('AVL 리스트 삭제 실패: 항목이 존재함', { id });
    }

    return isDeleted;
  } catch (err) {
    debugError('AVL 리스트 삭제 실패', { error: err, id });
    console.error("Error in deleteAvlList:", err);
    return false;
  }
}

/**
 * AVL Vendor Info 생성
 */
export async function createAvlVendorInfo(data: AvlVendorInfoInput): Promise<AvlDetailItem | null> {
  try {
    debugLog('AVL Vendor Info 생성 시작', { inputData: data });

    const currentTimestamp = new Date();

    // UI 필드를 DB 필드로 변환
    const insertData: NewAvlVendorInfo = {
      avlListId: data.avlListId,
      ownerSuggestion: data.ownerSuggestion ?? false,
      shiSuggestion: data.shiSuggestion ?? false,
      equipBulkDivision: data.equipBulkDivision === "EQUIP" ? "E" : "B",
      disciplineCode: data.disciplineCode || null,
      disciplineName: data.disciplineName,
      materialNameCustomerSide: data.materialNameCustomerSide,
      packageCode: data.packageCode || null,
      packageName: data.packageName || null,
      materialGroupCode: data.materialGroupCode || null,
      materialGroupName: data.materialGroupName || null,
      vendorId: data.vendorId || null,
      vendorName: data.vendorName || null,
      vendorCode: data.vendorCode || null,
      avlVendorName: data.avlVendorName || null,
      tier: data.tier || null,
      faTarget: data.faTarget ?? false,
      faStatus: data.faStatus || null,
      isAgent: data.isAgent ?? false,
      contractSignerId: data.contractSignerId || null,
      contractSignerName: data.contractSignerName || null,
      contractSignerCode: data.contractSignerCode || null,
      headquarterLocation: data.headquarterLocation || null,
      manufacturingLocation: data.manufacturingLocation || null,
      hasAvl: data.shiAvl ?? false,
      isBlacklist: data.shiBlacklist ?? false,
      isBcc: data.shiBcc ?? false,
      techQuoteNumber: data.salesQuoteNumber || null,
      quoteCode: data.quoteCode || null,
      quoteVendorId: data.quoteVendorId || null,
      quoteVendorName: data.salesVendorInfo || null,
      quoteVendorCode: data.quoteVendorCode || null,
      quoteCountry: data.salesCountry || null,
      quoteTotalAmount: data.totalAmount ? data.totalAmount.replace(/,/g, '') as any : null,
      quoteReceivedDate: data.quoteReceivedDate || null,
      recentQuoteDate: data.recentQuoteDate || null,
      recentQuoteNumber: data.recentQuoteNumber || null,
      recentOrderDate: data.recentOrderDate || null,
      recentOrderNumber: data.recentOrderNumber || null,
      remark: data.remarks || null,
    };

    debugLog('DB INSERT 시작', { table: 'avl_vendor_info', data: insertData });

    // 데이터베이스에 삽입
    const result = await db
      .insert(avlVendorInfo)
      .values(insertData as any)
      .returning();

    if (result.length === 0) {
      debugError('DB 삽입 실패: 결과가 없음', { insertData });
      throw new Error("Failed to create AVL vendor info");
    }

    debugSuccess('DB INSERT 완료', { table: 'avl_vendor_info', result: result[0] });

    const createdItem = result[0];

    // 생성된 데이터를 AvlDetailItem 타입으로 변환
    const transformedData: AvlDetailItem = {
      ...(createdItem as any),
      no: 1,
      selected: false,
      createdAt: createdItem.createdAt ? createdItem.createdAt.toISOString().split('T')[0] : '',
      updatedAt: createdItem.updatedAt ? createdItem.updatedAt.toISOString().split('T')[0] : '',
      equipBulkDivision: createdItem.equipBulkDivision === "E" ? "EQUIP" : "BULK",
      faTarget: createdItem.faTarget ?? false,
      agentStatus: createdItem.isAgent ? "예" : "아니오",
      shiAvl: createdItem.hasAvl ?? false,
      shiBlacklist: createdItem.isBlacklist ?? false,
      shiBcc: createdItem.isBcc ?? false,
      salesQuoteNumber: createdItem.techQuoteNumber || '',
      quoteCode: createdItem.quoteCode || '',
      salesVendorInfo: createdItem.quoteVendorName || '',
      salesCountry: createdItem.quoteCountry || '',
      totalAmount: createdItem.quoteTotalAmount ? createdItem.quoteTotalAmount.toString() : '',
      quoteReceivedDate: createdItem.quoteReceivedDate || '',
      recentQuoteDate: createdItem.recentQuoteDate || '',
      recentQuoteNumber: createdItem.recentQuoteNumber || '',
      recentOrderDate: createdItem.recentOrderDate || '',
      recentOrderNumber: createdItem.recentOrderNumber || '',
      remarks: createdItem.remark || '',
    };

    debugSuccess('AVL Vendor Info 생성 완료', { result: transformedData });

    // 캐시 무효화
    revalidateTag('avl-detail');

    return transformedData;
  } catch (err) {
    debugError('AVL Vendor Info 생성 실패', { error: err, inputData: data });
    console.error("Error in createAvlVendorInfo:", err);
    return null;
  }
}

/**
 * AVL Vendor Info 업데이트
 */
export async function updateAvlVendorInfo(id: number, data: Partial<AvlVendorInfoInput>): Promise<AvlDetailItem | null> {
  try {
    debugLog('AVL Vendor Info 업데이트 시작', { id, data });

    // 간단한 필드 매핑
    const updateData: any = { updatedAt: new Date() };

    // ownerSuggestion과 shiSuggestion 추가
    if (data.ownerSuggestion !== undefined) updateData.ownerSuggestion = data.ownerSuggestion;
    if (data.shiSuggestion !== undefined) updateData.shiSuggestion = data.shiSuggestion;

    if (data.equipBulkDivision !== undefined) updateData.equipBulkDivision = data.equipBulkDivision === "EQUIP" ? "E" : "B";
    if (data.disciplineCode !== undefined) updateData.disciplineCode = data.disciplineCode;
    if (data.disciplineName !== undefined) updateData.disciplineName = data.disciplineName;
    if (data.materialNameCustomerSide !== undefined) updateData.materialNameCustomerSide = data.materialNameCustomerSide;
    if (data.packageCode !== undefined) updateData.packageCode = data.packageCode;
    if (data.packageName !== undefined) updateData.packageName = data.packageName;
    if (data.materialGroupCode !== undefined) updateData.materialGroupCode = data.materialGroupCode;
    if (data.materialGroupName !== undefined) updateData.materialGroupName = data.materialGroupName;
    if (data.vendorId !== undefined) updateData.vendorId = data.vendorId;
    if (data.vendorName !== undefined) updateData.vendorName = data.vendorName;
    if (data.vendorCode !== undefined) updateData.vendorCode = data.vendorCode;
    if (data.avlVendorName !== undefined) updateData.avlVendorName = data.avlVendorName;
    if (data.tier !== undefined) updateData.tier = data.tier;
    if (data.faTarget !== undefined) updateData.faTarget = data.faTarget;
    if (data.faStatus !== undefined) updateData.faStatus = data.faStatus;
    if (data.isAgent !== undefined) updateData.isAgent = data.isAgent;
    if (data.contractSignerId !== undefined) updateData.contractSignerId = data.contractSignerId;
    if (data.contractSignerName !== undefined) updateData.contractSignerName = data.contractSignerName;
    if (data.contractSignerCode !== undefined) updateData.contractSignerCode = data.contractSignerCode;
    if (data.headquarterLocation !== undefined) updateData.headquarterLocation = data.headquarterLocation;
    if (data.manufacturingLocation !== undefined) updateData.manufacturingLocation = data.manufacturingLocation;
    if (data.shiAvl !== undefined) updateData.hasAvl = data.shiAvl;
    if (data.shiBlacklist !== undefined) updateData.isBlacklist = data.shiBlacklist;
    if (data.shiBcc !== undefined) updateData.isBcc = data.shiBcc;
    if (data.salesQuoteNumber !== undefined) updateData.techQuoteNumber = data.salesQuoteNumber;
    if (data.quoteCode !== undefined) updateData.quoteCode = data.quoteCode;
    if (data.quoteVendorId !== undefined) updateData.quoteVendorId = data.quoteVendorId;
    if (data.quoteVendorCode !== undefined) updateData.quoteVendorCode = data.quoteVendorCode;
    if (data.salesCountry !== undefined) updateData.quoteCountry = data.salesCountry;
    if (data.quoteReceivedDate !== undefined) updateData.quoteReceivedDate = data.quoteReceivedDate;
    if (data.recentQuoteDate !== undefined) updateData.recentQuoteDate = data.recentQuoteDate;
    if (data.recentQuoteNumber !== undefined) updateData.recentQuoteNumber = data.recentQuoteNumber;
    if (data.recentOrderDate !== undefined) updateData.recentOrderDate = data.recentOrderDate;
    if (data.recentOrderNumber !== undefined) updateData.recentOrderNumber = data.recentOrderNumber;
    if (data.remarks !== undefined) updateData.remark = data.remarks;

    // 숫자 변환
    if (data.totalAmount !== undefined) {
      updateData.quoteTotalAmount = data.totalAmount ? parseFloat(data.totalAmount.replace(/,/g, '')) || null : null;
    }

    // 문자열 필드
    if (data.salesVendorInfo !== undefined) updateData.quoteVendorName = data.salesVendorInfo;

    debugLog('업데이트할 데이터', { updateData });

    // 데이터베이스 업데이트
    const result = await db
      .update(avlVendorInfo)
      .set(updateData)
      .where(eq(avlVendorInfo.id, id))
      .returning();

    if (result.length === 0) {
      throw new Error("AVL vendor info not found");
    }

    debugSuccess('AVL Vendor Info 업데이트 성공', { id });

    revalidateTag('avl-detail');

    // 업데이트된 데이터 조회해서 반환
    return await getAvlVendorInfoById(id);
  } catch (err) {
    debugError('AVL Vendor Info 업데이트 실패', { id, error: err });
    console.error("Error in updateAvlVendorInfo:", err);
    return null;
  }
}

/**
 * AVL Vendor Info 삭제
 */
export async function deleteAvlVendorInfo(id: number): Promise<boolean> {
  try {
    debugLog('AVL Vendor Info 삭제 시작', { id });

    // 데이터베이스에서 삭제
    const result = await db
      .delete(avlVendorInfo)
      .where(eq(avlVendorInfo.id, id));

    // 삭제 확인을 위한 재조회
    const checkDeleted = await db
      .select({ id: avlVendorInfo.id })
      .from(avlVendorInfo)
      .where(eq(avlVendorInfo.id, id))
      .limit(1);

    const isDeleted = checkDeleted.length === 0;

    if (isDeleted) {
      debugSuccess('AVL Vendor Info 삭제 완료', { id });
      revalidateTag('avl-detail');
    } else {
      debugWarn('AVL Vendor Info 삭제 실패: 항목이 존재함', { id });
    }

    return isDeleted;
  } catch (err) {
    debugError('AVL Vendor Info 삭제 실패', { error: err, id });
    console.error("Error in deleteAvlVendorInfo:", err);
    return false;
  }
}

/**
 * 프로젝트 AVL Vendor Info 조회 (프로젝트별, isTemplate=false)
 * avl_list와 avlVendorInfo를 JOIN하여 프로젝트별 AVL 데이터를 조회합니다.
 */
const _getProjectAvlVendorInfo = async (input: GetProjectAvlSchema) => {
  try {
    const offset = (input.page - 1) * input.perPage;

    debugLog('프로젝트 AVL Vendor Info 조회 시작', { input, offset });

    // 기본 JOIN 쿼리 구성 (프로젝트 AVL이므로 isTemplate=false)
    // 실제 쿼리는 아래에서 구성됨

    // 검색 조건 구성
    const whereConditions: any[] = [eq(avlList.isTemplate, false)]; // 기본 조건

    // 필수 필터: 프로젝트 코드
    if (input.projectCode) {
      whereConditions.push(ilike(avlList.projectCode, `%${input.projectCode}%`));
    }

    // 검색어 기반 필터링
    if (input.search) {
      const searchTerm = `%${input.search}%`;
      whereConditions.push(
        or(
          ilike(avlVendorInfo.disciplineName, searchTerm),
          ilike(avlVendorInfo.materialNameCustomerSide, searchTerm),
          ilike(avlVendorInfo.vendorName, searchTerm),
          ilike(avlVendorInfo.avlVendorName, searchTerm),
          ilike(avlVendorInfo.packageName, searchTerm),
          ilike(avlVendorInfo.materialGroupName, searchTerm)
        )
      );
    }

    // 추가 필터 조건들
    if (input.equipBulkDivision) {
      whereConditions.push(eq(avlVendorInfo.equipBulkDivision, input.equipBulkDivision === "EQUIP" ? "E" : "B"));
    }
    if (input.disciplineCode) {
      whereConditions.push(ilike(avlVendorInfo.disciplineCode, `%${input.disciplineCode}%`));
    }
    if (input.disciplineName) {
      whereConditions.push(ilike(avlVendorInfo.disciplineName, `%${input.disciplineName}%`));
    }
    if (input.materialNameCustomerSide) {
      whereConditions.push(ilike(avlVendorInfo.materialNameCustomerSide, `%${input.materialNameCustomerSide}%`));
    }
    if (input.packageCode) {
      whereConditions.push(ilike(avlVendorInfo.packageCode, `%${input.packageCode}%`));
    }
    if (input.packageName) {
      whereConditions.push(ilike(avlVendorInfo.packageName, `%${input.packageName}%`));
    }
    if (input.materialGroupCode) {
      whereConditions.push(ilike(avlVendorInfo.materialGroupCode, `%${input.materialGroupCode}%`));
    }
    if (input.materialGroupName) {
      whereConditions.push(ilike(avlVendorInfo.materialGroupName, `%${input.materialGroupName}%`));
    }
    if (input.vendorName) {
      whereConditions.push(ilike(avlVendorInfo.vendorName, `%${input.vendorName}%`));
    }
    if (input.vendorCode) {
      whereConditions.push(ilike(avlVendorInfo.vendorCode, `%${input.vendorCode}%`));
    }
    if (input.avlVendorName) {
      whereConditions.push(ilike(avlVendorInfo.avlVendorName, `%${input.avlVendorName}%`));
    }
    if (input.tier) {
      whereConditions.push(ilike(avlVendorInfo.tier, `%${input.tier}%`));
    }

    // 정렬 조건 구성
    const orderByConditions: any[] = [];
    input.sort.forEach((sortItem) => {
      const column = sortItem.id as keyof typeof avlVendorInfo;
      if (column && avlVendorInfo[column]) {
        if (sortItem.desc) {
          orderByConditions.push(sql`${avlVendorInfo[column]} desc`);
        } else {
          orderByConditions.push(sql`${avlVendorInfo[column]} asc`);
        }
      }
    });

    // 기본 정렬
    if (orderByConditions.length === 0) {
      orderByConditions.push(asc(avlVendorInfo.id));
    }

    // 총 개수 조회
    const totalCount = await db
      .select({ count: count() })
      .from(avlVendorInfo)
      .innerJoin(avlList, eq(avlVendorInfo.avlListId, avlList.id))
      .where(and(...whereConditions));

    // 데이터 조회 - JOIN 결과에서 필요한 필드들을 명시적으로 선택
    const data = await db
      .select({
        // avlVendorInfo의 모든 필드
        id: avlVendorInfo.id,
        avlListId: avlVendorInfo.avlListId,
        ownerSuggestion: avlVendorInfo.ownerSuggestion,
        shiSuggestion: avlVendorInfo.shiSuggestion,
        equipBulkDivision: avlVendorInfo.equipBulkDivision,
        disciplineCode: avlVendorInfo.disciplineCode,
        disciplineName: avlVendorInfo.disciplineName,
        materialNameCustomerSide: avlVendorInfo.materialNameCustomerSide,
        packageCode: avlVendorInfo.packageCode,
        packageName: avlVendorInfo.packageName,
        materialGroupCode: avlVendorInfo.materialGroupCode,
        materialGroupName: avlVendorInfo.materialGroupName,
        vendorId: avlVendorInfo.vendorId,
        vendorName: avlVendorInfo.vendorName,
        vendorCode: avlVendorInfo.vendorCode,
        avlVendorName: avlVendorInfo.avlVendorName,
        tier: avlVendorInfo.tier,
        faTarget: avlVendorInfo.faTarget,
        faStatus: avlVendorInfo.faStatus,
        isAgent: avlVendorInfo.isAgent,
        contractSignerId: avlVendorInfo.contractSignerId,
        contractSignerName: avlVendorInfo.contractSignerName,
        contractSignerCode: avlVendorInfo.contractSignerCode,
        headquarterLocation: avlVendorInfo.headquarterLocation,
        manufacturingLocation: avlVendorInfo.manufacturingLocation,
        hasAvl: avlVendorInfo.hasAvl,
        isBlacklist: avlVendorInfo.isBlacklist,
        isBcc: avlVendorInfo.isBcc,
        techQuoteNumber: avlVendorInfo.techQuoteNumber,
        quoteCode: avlVendorInfo.quoteCode,
        quoteVendorId: avlVendorInfo.quoteVendorId,
        quoteVendorName: avlVendorInfo.quoteVendorName,
        quoteVendorCode: avlVendorInfo.quoteVendorCode,
        quoteCountry: avlVendorInfo.quoteCountry,
        quoteTotalAmount: avlVendorInfo.quoteTotalAmount,
        quoteReceivedDate: avlVendorInfo.quoteReceivedDate,
        recentQuoteDate: avlVendorInfo.recentQuoteDate,
        recentQuoteNumber: avlVendorInfo.recentQuoteNumber,
        recentOrderDate: avlVendorInfo.recentOrderDate,
        recentOrderNumber: avlVendorInfo.recentOrderNumber,
        remark: avlVendorInfo.remark,
        createdAt: avlVendorInfo.createdAt,
        updatedAt: avlVendorInfo.updatedAt,
      })
      .from(avlVendorInfo)
      .innerJoin(avlList, eq(avlVendorInfo.avlListId, avlList.id))
      .where(and(...whereConditions))
      .orderBy(...orderByConditions)
      .limit(input.perPage)
      .offset(offset);

    // 데이터 변환
    const transformedData: AvlDetailItem[] = data.map((item: any, index) => ({
      ...item,
      no: offset + index + 1,
      selected: false,
      createdAt: (item.createdAt as Date)?.toISOString().split('T')[0] || '',
      updatedAt: (item.updatedAt as Date)?.toISOString().split('T')[0] || '',
      // UI 표시용 필드 변환
      equipBulkDivision: item.equipBulkDivision === "E" ? "EQUIP" : "BULK",
      faTarget: item.faTarget ?? false,
      faStatus: item.faStatus || '',
      agentStatus: item.isAgent ? "예" : "아니오",
      shiAvl: item.hasAvl ?? false,
      shiBlacklist: item.isBlacklist ?? false,
      shiBcc: item.isBcc ?? false,
      salesQuoteNumber: item.techQuoteNumber || '',
      quoteCode: item.quoteCode || '',
      salesVendorInfo: item.quoteVendorName || '',
      salesCountry: item.quoteCountry || '',
      totalAmount: item.quoteTotalAmount ? item.quoteTotalAmount.toString() : '',
      quoteReceivedDate: item.quoteReceivedDate || '',
      recentQuoteDate: item.recentQuoteDate || '',
      recentQuoteNumber: item.recentQuoteNumber || '',
      recentOrderDate: item.recentOrderDate || '',
      recentOrderNumber: item.recentOrderNumber || '',
      remarks: item.remark || '',
    }));

    const pageCount = Math.ceil(totalCount[0].count / input.perPage);

    debugSuccess('프로젝트 AVL Vendor Info 조회 완료', { recordCount: transformedData.length, pageCount });

    return {
      data: transformedData,
      pageCount
    };
  } catch (err) {
    debugError('프로젝트 AVL Vendor Info 조회 실패', { error: err, input });
    console.error("Error in getProjectAvlVendorInfo:", err);
    return { data: [], pageCount: 0 };
  }
};

// 캐시된 버전 export
export const getProjectAvlVendorInfo = unstable_cache(
  _getProjectAvlVendorInfo,
  ['project-avl-vendor-info'],
  {
    tags: ['project-avl-vendor-info'],
    revalidate: 300, // 5분 캐시
  }
);

/**
 * 표준 AVL Vendor Info 조회 (선종별 표준 AVL, isTemplate=true)
 * avl_list와 avlVendorInfo를 JOIN하여 표준 AVL 데이터를 조회합니다.
 */
const _getStandardAvlVendorInfo = async (input: GetStandardAvlSchema) => {
  try {
    const offset = (input.page - 1) * input.perPage;

    debugLog('표준 AVL Vendor Info 조회 시작', { input, offset });

    // 기본 JOIN 쿼리 구성 (표준 AVL이므로 isTemplate=true)
    // 실제 쿼리는 아래에서 구성됨

    // 검색 조건 구성
    const whereConditions: any[] = [eq(avlList.isTemplate, true)]; // 기본 조건

    // 필수 필터: 표준 AVL용 (공사부문, 선종, AVL종류, H/T)
    if (input.constructionSector) {
      whereConditions.push(ilike(avlList.constructionSector, `%${input.constructionSector}%`));
    }
    if (input.shipType) {
      whereConditions.push(ilike(avlList.shipType, `%${input.shipType}%`));
    }
    if (input.avlKind) {
      whereConditions.push(ilike(avlList.avlKind, `%${input.avlKind}%`));
    }
    if (input.htDivision) {
      whereConditions.push(eq(avlList.htDivision, input.htDivision));
    }

    // 검색어 기반 필터링
    if (input.search) {
      const searchTerm = `%${input.search}%`;
      whereConditions.push(
        or(
          ilike(avlVendorInfo.disciplineName, searchTerm),
          ilike(avlVendorInfo.materialNameCustomerSide, searchTerm),
          ilike(avlVendorInfo.vendorName, searchTerm),
          ilike(avlVendorInfo.avlVendorName, searchTerm),
          ilike(avlVendorInfo.packageName, searchTerm),
          ilike(avlVendorInfo.materialGroupName, searchTerm)
        )
      );
    }

    // 추가 필터 조건들
    if (input.equipBulkDivision) {
      whereConditions.push(eq(avlVendorInfo.equipBulkDivision, input.equipBulkDivision === "EQUIP" ? "E" : "B"));
    }
    if (input.disciplineCode) {
      whereConditions.push(ilike(avlVendorInfo.disciplineCode, `%${input.disciplineCode}%`));
    }
    if (input.disciplineName) {
      whereConditions.push(ilike(avlVendorInfo.disciplineName, `%${input.disciplineName}%`));
    }
    if (input.materialNameCustomerSide) {
      whereConditions.push(ilike(avlVendorInfo.materialNameCustomerSide, `%${input.materialNameCustomerSide}%`));
    }
    if (input.packageCode) {
      whereConditions.push(ilike(avlVendorInfo.packageCode, `%${input.packageCode}%`));
    }
    if (input.packageName) {
      whereConditions.push(ilike(avlVendorInfo.packageName, `%${input.packageName}%`));
    }
    if (input.materialGroupCode) {
      whereConditions.push(ilike(avlVendorInfo.materialGroupCode, `%${input.materialGroupCode}%`));
    }
    if (input.materialGroupName) {
      whereConditions.push(ilike(avlVendorInfo.materialGroupName, `%${input.materialGroupName}%`));
    }
    if (input.vendorName) {
      whereConditions.push(ilike(avlVendorInfo.vendorName, `%${input.vendorName}%`));
    }
    if (input.vendorCode) {
      whereConditions.push(ilike(avlVendorInfo.vendorCode, `%${input.vendorCode}%`));
    }
    if (input.avlVendorName) {
      whereConditions.push(ilike(avlVendorInfo.avlVendorName, `%${input.avlVendorName}%`));
    }
    if (input.tier) {
      whereConditions.push(ilike(avlVendorInfo.tier, `%${input.tier}%`));
    }

    // 정렬 조건 구성
    const orderByConditions: any[] = [];
    input.sort.forEach((sortItem) => {
      const column = sortItem.id as keyof typeof avlVendorInfo;
      if (column && avlVendorInfo[column]) {
        if (sortItem.desc) {
          orderByConditions.push(sql`${avlVendorInfo[column]} desc`);
        } else {
          orderByConditions.push(sql`${avlVendorInfo[column]} asc`);
        }
      }
    });

    // 기본 정렬
    if (orderByConditions.length === 0) {
      orderByConditions.push(asc(avlVendorInfo.id));
    }

    // 총 개수 조회
    const totalCount = await db
      .select({ count: count() })
      .from(avlVendorInfo)
      .innerJoin(avlList, eq(avlVendorInfo.avlListId, avlList.id))
      .where(and(...whereConditions));

    // 데이터 조회
    const data = await db
      .select()
      .from(avlVendorInfo)
      .innerJoin(avlList, eq(avlVendorInfo.avlListId, avlList.id))
      .where(and(...whereConditions))
      .orderBy(...orderByConditions)
      .limit(input.perPage)
      .offset(offset);

    // 데이터 변환
    const transformedData: AvlDetailItem[] = data.map((item: any, index) => ({
      ...(item.avl_vendor_info || item),
      no: offset + index + 1,
      selected: false,
      createdAt: ((item.avl_vendor_info || item).createdAt as Date)?.toISOString().split('T')[0] || '',
      updatedAt: ((item.avl_vendor_info || item).updatedAt as Date)?.toISOString().split('T')[0] || '',
      // UI 표시용 필드 변환
      equipBulkDivision: (item.avl_vendor_info || item).equipBulkDivision === "E" ? "EQUIP" : "BULK",
      faTarget: (item.avl_vendor_info || item).faTarget ?? false,
      faStatus: (item.avl_vendor_info || item).faStatus || '',
      agentStatus: (item.avl_vendor_info || item).isAgent ? "예" : "아니오",
      shiAvl: (item.avl_vendor_info || item).hasAvl ?? false,
      shiBlacklist: (item.avl_vendor_info || item).isBlacklist ?? false,
      shiBcc: (item.avl_vendor_info || item).isBcc ?? false,
      salesQuoteNumber: (item.avl_vendor_info || item).techQuoteNumber || '',
      quoteCode: (item.avl_vendor_info || item).quoteCode || '',
      salesVendorInfo: (item.avl_vendor_info || item).quoteVendorName || '',
      salesCountry: (item.avl_vendor_info || item).quoteCountry || '',
      totalAmount: (item.avl_vendor_info || item).quoteTotalAmount ? (item.avl_vendor_info || item).quoteTotalAmount.toString() : '',
      quoteReceivedDate: (item.avl_vendor_info || item).quoteReceivedDate || '',
      recentQuoteDate: (item.avl_vendor_info || item).recentQuoteDate || '',
      recentQuoteNumber: (item.avl_vendor_info || item).recentQuoteNumber || '',
      recentOrderDate: (item.avl_vendor_info || item).recentOrderDate || '',
      recentOrderNumber: (item.avl_vendor_info || item).recentOrderNumber || '',
      remarks: (item.avl_vendor_info || item).remark || '',
    }));

    const pageCount = Math.ceil(totalCount[0].count / input.perPage);

    debugSuccess('표준 AVL Vendor Info 조회 완료', { recordCount: transformedData.length, pageCount });

    return {
      data: transformedData,
      pageCount
    };
  } catch (err) {
    debugError('표준 AVL Vendor Info 조회 실패', { error: err, input });
    console.error("Error in getStandardAvlVendorInfo:", err);
    return { data: [], pageCount: 0 };
  }
};

// 캐시된 버전 export
export const getStandardAvlVendorInfo = unstable_cache(
  _getStandardAvlVendorInfo,
  ['standard-avl-vendor-info'],
  {
    tags: ['standard-avl-vendor-info'],
    revalidate: 300, // 5분 캐시
  }
);