(김준회) AVL 기능 구현 1차 및 벤더풀 E/B 구분 개선

1 files changed, 1285 insertions, 0 deletions

diff --git a/lib/avl/service.ts b/lib/avl/service.ts
new file mode 100644
index 00000000..6a873ac1
--- /dev/null
+++ b/lib/avl/service.ts
@@ -0,0 +1,1285 @@
+"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분 캐시
+  }
+);