"use server";

import { GetVendorPoolSchema } from "./validations";
import { VendorPool } from "./types";
import db from "@/db/db";
import { vendorPool } from "@/db/schema/avl/vendor-pool";
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";

/**
 * Vendor Pool 목록 조회
 * vendor_pool 테이블에서 실제 데이터를 조회합니다.
 */
const _getVendorPools = async (input: GetVendorPoolSchema) => {
  try {
    const offset = (input.page - 1) * input.perPage;

    debugLog('Vendor Pool 목록 조회 시작', { input, offset });

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

    // 검색어 기반 필터링
    if (input.search) {
      const searchTerm = `%${input.search}%`;
      whereConditions.push(
        or(
          ilike(vendorPool.constructionSector, searchTerm),
          ilike(vendorPool.designCategoryCode, searchTerm),
          ilike(vendorPool.designCategory, searchTerm),
          ilike(vendorPool.vendorName, searchTerm),
          ilike(vendorPool.materialGroupCode, searchTerm),
          ilike(vendorPool.materialGroupName, searchTerm),
          ilike(vendorPool.packageName, searchTerm),
          ilike(vendorPool.avlVendorName, searchTerm),
          ilike(vendorPool.similarVendorName, searchTerm)
        )
      );
    }

    // Advanced filters 처리 (DataTableFilterList에서 생성된 필터들)
    if (input.filters && input.filters.length > 0) {
      const filterConditions: any[] = [];

      for (const filter of input.filters) {
        let condition: any = null;

        switch (filter.id) {
          case 'constructionSector':
            if (filter.operator === 'eq') {
              condition = eq(vendorPool.constructionSector, filter.value as string);
            } else if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.constructionSector, `%${filter.value}%`);
            }
            break;
          case 'htDivision':
            if (filter.operator === 'eq') {
              condition = eq(vendorPool.htDivision, filter.value as string);
            } else if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.htDivision, `%${filter.value}%`);
            }
            break;
          case 'designCategoryCode':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.designCategoryCode, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.designCategoryCode, filter.value as string);
            }
            break;
          case 'designCategory':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.designCategory, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.designCategory, filter.value as string);
            }
            break;
          case 'packageCode':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.packageCode, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.packageCode, filter.value as string);
            }
            break;
          case 'packageName':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.packageName, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.packageName, filter.value as string);
            }
            break;
          case 'materialGroupCode':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.materialGroupCode, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.materialGroupCode, filter.value as string);
            }
            break;
          case 'materialGroupName':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.materialGroupName, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.materialGroupName, filter.value as string);
            }
            break;
          case 'vendorCode':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.vendorCode, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.vendorCode, filter.value as string);
            }
            break;
          case 'vendorName':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.vendorName, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.vendorName, filter.value as string);
            }
            break;
          case 'faStatus':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.faStatus, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.faStatus, filter.value as string);
            }
            break;
          case 'tier':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.tier, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.tier, filter.value as string);
            }
            break;
          case 'headquarterLocation':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.headquarterLocation, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.headquarterLocation, filter.value as string);
            }
            break;
          case 'manufacturingLocation':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.manufacturingLocation, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.manufacturingLocation, filter.value as string);
            }
            break;
          case 'avlVendorName':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.avlVendorName, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.avlVendorName, filter.value as string);
            }
            break;
          case 'registrant':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.registrant, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.registrant, filter.value as string);
            }
            break;
          case 'lastModifier':
            if (filter.operator === 'iLike') {
              condition = ilike(vendorPool.lastModifier, `%${filter.value}%`);
            } else if (filter.operator === 'eq') {
              condition = eq(vendorPool.lastModifier, filter.value as string);
            }
            break;
          case 'hasAvl':
            if (filter.operator === 'eq') {
              condition = eq(vendorPool.hasAvl, filter.value === 'true');
            }
            break;
          case 'isAgent':
            if (filter.operator === 'eq') {
              condition = eq(vendorPool.isAgent, filter.value === 'true');
            }
            break;
          case 'isBlacklist':
            if (filter.operator === 'eq') {
              condition = eq(vendorPool.isBlacklist, filter.value === 'true');
            }
            break;
          case 'isBcc':
            if (filter.operator === 'eq') {
              condition = eq(vendorPool.isBcc, filter.value === 'true');
            }
            break;
          case 'registrationDate':
          case 'lastModifiedDate':
            // 날짜 필터 처리 (단순화된 버전)
            if (filter.operator === 'eq' && filter.value) {
              const dateValue = new Date(filter.value as string);
              if (filter.id === 'registrationDate') {
                condition = eq(vendorPool.registrationDate, dateValue);
              } else {
                condition = eq(vendorPool.lastModifiedDate, dateValue);
              }
            }
            break;
        }

        if (condition) {
          filterConditions.push(condition);
        }
      }

      // 필터 조건들을 AND 또는 OR로 결합
      if (filterConditions.length > 0) {
        if (input.joinOperator === 'or') {
          whereConditions.push(or(...filterConditions));
        } else {
          whereConditions.push(and(...filterConditions));
        }
      }
    }

    // 기존 필터 조건 추가 (하위 호환성 유지)
    if (input.constructionSector) {
      whereConditions.push(eq(vendorPool.constructionSector, input.constructionSector));
    }
    if (input.htDivision) {
      whereConditions.push(eq(vendorPool.htDivision, input.htDivision));
    }
    if (input.designCategoryCode) {
      whereConditions.push(ilike(vendorPool.designCategoryCode, `%${input.designCategoryCode}%`));
    }
    if (input.designCategory) {
      whereConditions.push(ilike(vendorPool.designCategory, `%${input.designCategory}%`));
    }
    if (input.equipBulkDivision) {
      whereConditions.push(eq(vendorPool.equipBulkDivision, input.equipBulkDivision));
    }
    if (input.packageCode) {
      whereConditions.push(ilike(vendorPool.packageCode, `%${input.packageCode}%`));
    }
    if (input.packageName) {
      whereConditions.push(ilike(vendorPool.packageName, `%${input.packageName}%`));
    }
    if (input.materialGroupCode) {
      whereConditions.push(ilike(vendorPool.materialGroupCode, `%${input.materialGroupCode}%`));
    }
    if (input.materialGroupName) {
      whereConditions.push(ilike(vendorPool.materialGroupName, `%${input.materialGroupName}%`));
    }
    if (input.vendorCode) {
      whereConditions.push(ilike(vendorPool.vendorCode, `%${input.vendorCode}%`));
    }
    if (input.vendorName) {
      whereConditions.push(ilike(vendorPool.vendorName, `%${input.vendorName}%`));
    }
    if (input.faStatus) {
      whereConditions.push(ilike(vendorPool.faStatus, `%${input.faStatus}%`));
    }
    if (input.tier) {
      whereConditions.push(ilike(vendorPool.tier, `%${input.tier}%`));
    }
    if (input.hasAvl === "true") {
      whereConditions.push(eq(vendorPool.hasAvl, true));
    } else if (input.hasAvl === "false") {
      whereConditions.push(eq(vendorPool.hasAvl, false));
    }
    if (input.isAgent === "true") {
      whereConditions.push(eq(vendorPool.isAgent, true));
    } else if (input.isAgent === "false") {
      whereConditions.push(eq(vendorPool.isAgent, false));
    }
    if (input.isBlacklist === "true") {
      whereConditions.push(eq(vendorPool.isBlacklist, true));
    } else if (input.isBlacklist === "false") {
      whereConditions.push(eq(vendorPool.isBlacklist, false));
    }
    if (input.isBcc === "true") {
      whereConditions.push(eq(vendorPool.isBcc, true));
    } else if (input.isBcc === "false") {
      whereConditions.push(eq(vendorPool.isBcc, false));
    }

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

      // id 컬럼의 경우 특별 처리 (No. 컬럼 정렬용)
      if (column === 'id') {
        if (sortItem.desc) {
          orderByConditions.push(sql`${vendorPool.id} desc`);
        } else {
          orderByConditions.push(sql`${vendorPool.id} asc`);
        }
      } else if (column && vendorPool[column]) {
        if (sortItem.desc) {
          orderByConditions.push(sql`${vendorPool[column]} desc`);
        } else {
          orderByConditions.push(sql`${vendorPool[column]} asc`);
        }
      }
    });

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

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

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

    // 데이터 변환 (timestamp -> string)
    const transformedData = data.map((item, index) => ({
      ...item,
      no: offset + index + 1,
      selected: false,
      registrationDate: item.registrationDate ? item.registrationDate.toISOString().split('T')[0] : '',
      lastModifiedDate: item.lastModifiedDate ? item.lastModifiedDate.toISOString().split('T')[0] : '',
      // string 필드들의 null 처리
      packageCode: item.packageCode || '',
      packageName: item.packageName || '',
      materialGroupCode: item.materialGroupCode || '',
      materialGroupName: item.materialGroupName || '',
      smCode: item.smCode || '',
      similarMaterialNamePurchase: item.similarMaterialNamePurchase || '',
      similarMaterialNameOther: item.similarMaterialNameOther || '',
      vendorCode: item.vendorCode || '',
      vendorName: item.vendorName || '',
      faStatus: item.faStatus || '',
      faRemark: item.faRemark || '',
      tier: item.tier || '',
      contractSignerCode: item.contractSignerCode || '',
      contractSignerName: item.contractSignerName || '',
      headquarterLocation: item.headquarterLocation || '',
      manufacturingLocation: item.manufacturingLocation || '',
      avlVendorName: item.avlVendorName || '',
      similarVendorName: item.similarVendorName || '',
      purchaseOpinion: item.purchaseOpinion || '',
      picName: item.picName || '',
      picEmail: item.picEmail || '',
      picPhone: item.picPhone || '',
      agentName: item.agentName || '',
      agentEmail: item.agentEmail || '',
      agentPhone: item.agentPhone || '',
      recentQuoteDate: item.recentQuoteDate || '',
      recentQuoteNumber: item.recentQuoteNumber || '',
      recentOrderDate: item.recentOrderDate || '',
      recentOrderNumber: item.recentOrderNumber || '',
      registrant: item.registrant || '',
      lastModifier: item.lastModifier || '',
      // boolean 필드들을 적절히 처리
      faTarget: item.faTarget ?? false,
      hasAvl: item.hasAvl ?? false,
      isAgent: item.isAgent ?? false,
      isBlacklist: item.isBlacklist ?? false,
      isBcc: item.isBcc ?? false,
      // 선종 적용 정보
      shipTypeCommon: item.shipTypeCommon ?? false,
      shipTypeAmax: item.shipTypeAmax ?? false,
      shipTypeSmax: item.shipTypeSmax ?? false,
      shipTypeVlcc: item.shipTypeVlcc ?? false,
      shipTypeLngc: item.shipTypeLngc ?? false,
      shipTypeCont: item.shipTypeCont ?? false,
      offshoreTypeCommon: item.offshoreTypeCommon ?? false,
      offshoreTypeFpso: item.offshoreTypeFpso ?? false,
      offshoreTypeFlng: item.offshoreTypeFlng ?? false,
      offshoreTypeFpu: item.offshoreTypeFpu ?? false,
      offshoreTypePlatform: item.offshoreTypePlatform ?? false,
      offshoreTypeWtiv: item.offshoreTypeWtiv ?? false,
      offshoreTypeGom: item.offshoreTypeGom ?? false,
    }));

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

    debugSuccess('Vendor Pool 목록 조회 완료', { recordCount: transformedData.length, pageCount });

    return {
      data: transformedData,
      pageCount
    };
  } catch (err) {
    debugError('Vendor Pool 목록 조회 실패', { error: err, input });
    console.error("Error in getVendorPools:", err);
    return { data: [], pageCount: 0 };
  }
};

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

/**
 * Vendor Pool 상세 정보 조회
 */
export async function getVendorPoolById(id: number): Promise<VendorPool | null> {
  try {
    const data = await db
      .select()
      .from(vendorPool)
      .where(eq(vendorPool.id, id))
      .limit(1);

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

    const item = data[0];

    // 데이터 변환 (timestamp -> string)
    const transformedData: VendorPool = {
      ...item,
      selected: false,
      registrationDate: item.registrationDate ? item.registrationDate.toISOString().split('T')[0] : '',
      lastModifiedDate: item.lastModifiedDate ? item.lastModifiedDate.toISOString().split('T')[0] : '',
      // string 필드들의 null 처리
      packageCode: item.packageCode || '',
      packageName: item.packageName || '',
      materialGroupCode: item.materialGroupCode || '',
      materialGroupName: item.materialGroupName || '',
      smCode: item.smCode || '',
      similarMaterialNamePurchase: item.similarMaterialNamePurchase || '',
      similarMaterialNameOther: item.similarMaterialNameOther || '',
      vendorCode: item.vendorCode || '',
      vendorName: item.vendorName || '',
      faStatus: item.faStatus || '',
      faRemark: item.faRemark || '',
      tier: item.tier || '',
      contractSignerCode: item.contractSignerCode || '',
      contractSignerName: item.contractSignerName || '',
      headquarterLocation: item.headquarterLocation || '',
      manufacturingLocation: item.manufacturingLocation || '',
      avlVendorName: item.avlVendorName || '',
      similarVendorName: item.similarVendorName || '',
      purchaseOpinion: item.purchaseOpinion || '',
      picName: item.picName || '',
      picEmail: item.picEmail || '',
      picPhone: item.picPhone || '',
      agentName: item.agentName || '',
      agentEmail: item.agentEmail || '',
      agentPhone: item.agentPhone || '',
      recentQuoteDate: item.recentQuoteDate || '',
      recentQuoteNumber: item.recentQuoteNumber || '',
      recentOrderDate: item.recentOrderDate || '',
      recentOrderNumber: item.recentOrderNumber || '',
      registrant: item.registrant || '',
      lastModifier: item.lastModifier || '',
      // boolean 필드들을 적절히 처리
      faTarget: item.faTarget ?? false,
      hasAvl: item.hasAvl ?? false,
      isAgent: item.isAgent ?? false,
      isBlacklist: item.isBlacklist ?? false,
      isBcc: item.isBcc ?? false,
      // 선종 적용 정보
      shipTypeCommon: item.shipTypeCommon ?? false,
      shipTypeAmax: item.shipTypeAmax ?? false,
      shipTypeSmax: item.shipTypeSmax ?? false,
      shipTypeVlcc: item.shipTypeVlcc ?? false,
      shipTypeLngc: item.shipTypeLngc ?? false,
      shipTypeCont: item.shipTypeCont ?? false,
      offshoreTypeCommon: item.offshoreTypeCommon ?? false,
      offshoreTypeFpso: item.offshoreTypeFpso ?? false,
      offshoreTypeFlng: item.offshoreTypeFlng ?? false,
      offshoreTypeFpu: item.offshoreTypeFpu ?? false,
      offshoreTypePlatform: item.offshoreTypePlatform ?? false,
      offshoreTypeWtiv: item.offshoreTypeWtiv ?? false,
      offshoreTypeGom: item.offshoreTypeGom ?? false,
    };

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

/**
 * Vendor Pool 액션 처리
 * 신규등록, 일괄입력, 저장 등의 액션을 처리
 */
export async function handleVendorPoolAction(
  action: string,
  data?: any
): Promise<{ success: boolean; message: string; data?: any }> {
  try {
    switch (action) {
      case "new-registration":
        // 신규 등록은 createVendorPool 함수를 통해 처리되므로 여기서는 성공 메시지만 반환
        return { success: true, message: "신규 등록 모달이 열렸습니다." };

      case "bulk-import":
        // TODO: 파일 업로드 및 일괄 데이터 처리 로직 구현 필요
        // 현재는 임시 구현 - 실제로는 파일 파싱 및 배치 삽입 로직이 필요
        if (!data?.file) {
          return { success: false, message: "업로드할 파일이 없습니다." };
        }
        console.log("일괄 입력 처리:", data.file);
        // 실제 구현 시: 파일 파싱 -> 데이터 검증 -> 배치 삽입
        return { success: true, message: "일괄 입력 처리가 시작되었습니다." };

      case "fa-detail":
        // FA 상세 정보 조회 - 실제로는 별도의 FA 조회 로직이 필요할 수 있음
        if (!data?.id) {
          return { success: false, message: "FA 대상 ID가 없습니다." };
        }
        console.log("FA 상세 조회:", data.id);
        return { success: true, message: "FA 상세 정보가 조회되었습니다.", data: { id: data.id } };

      case "save":
        // 변경사항 저장 - 실제로는 변경된 데이터들을 배치 업데이트하는 로직이 필요
        console.log("변경사항 저장:", data);
        // TODO: 변경된 항목들 검증 및 저장 로직 구현
        return { success: true, message: "변경사항이 저장되었습니다." };

      case "fixed-values":
        // 고정값 설정 - 실제로는 고정값 관리 모달과 설정 로직이 필요
        console.log("고정값 설정:", data);
        // TODO: 고정값 설정 모달 및 저장 로직 구현
        return { success: true, message: "고정값 설정이 완료되었습니다." };

      case "edit":
        // 수정은 updateVendorPool 함수를 통해 처리되므로 여기서는 성공 메시지만 반환
        if (!data?.id) {
          return { success: false, message: "수정할 항목 ID가 없습니다." };
        }
        return { success: true, message: "수정 모달이 열렸습니다.", data: { id: data.id } };

      case "delete":
        // 삭제는 deleteVendorPool 함수를 통해 처리되므로 여기서는 성공 메시지만 반환
        if (!data?.id) {
          return { success: false, message: "삭제할 항목 ID가 없습니다." };
        }
        return { success: true, message: "항목이 삭제되었습니다.", data: { id: data.id } };

      case "view-detail":
        // 상세 조회는 getVendorPoolById 함수를 통해 처리되므로 여기서는 성공 메시지만 반환
        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 handleVendorPoolAction:", err);
    return { success: false, message: "액션 처리 중 오류가 발생했습니다." };
  }
}

/**
 * Vendor Pool 생성
 */
export async function createVendorPool(data: Omit<VendorPool, 'id' | 'registrationDate' | 'lastModifiedDate'>): Promise<VendorPool | null> {
  try {
    debugLog('Vendor Pool 생성 시작', { inputData: data });

    debugLog('데이터 검증 시작', { data, requiredFields: ['constructionSector', 'htDivision', 'designCategory', 'vendorName'] });

    const currentTimestamp = new Date();

    // 데이터베이스에 삽입할 데이터 준비
    const insertData = {
      // 기본 정보
      constructionSector: data.constructionSector,
      htDivision: data.htDivision,

      // 설계 정보
      designCategoryCode: data.designCategoryCode,
      designCategory: data.designCategory,
      equipBulkDivision: data.equipBulkDivision,

      // 패키지 정보
      packageCode: data.packageCode,
      packageName: data.packageName,

      // 자재그룹 정보
      materialGroupCode: data.materialGroupCode,
      materialGroupName: data.materialGroupName,

      // 자재 관련 정보
      smCode: data.smCode,
      similarMaterialNamePurchase: data.similarMaterialNamePurchase,
      similarMaterialNameOther: data.similarMaterialNameOther,

      // 협력업체 정보
      vendorCode: data.vendorCode,
      vendorName: data.vendorName,

      // 사업 및 인증 정보
      faTarget: data.faTarget ?? false,
      faStatus: data.faStatus,
      faRemark: data.faRemark,
      tier: data.tier,
      isAgent: data.isAgent ?? false,

      // 계약 정보
      contractSignerCode: data.contractSignerCode,
      contractSignerName: data.contractSignerName,

      // 위치 정보
      headquarterLocation: data.headquarterLocation,
      manufacturingLocation: data.manufacturingLocation,

      // AVL 관련 정보
      avlVendorName: data.avlVendorName,
      similarVendorName: data.similarVendorName,
      hasAvl: data.hasAvl ?? false,

      // 상태 정보
      isBlacklist: data.isBlacklist ?? false,
      isBcc: data.isBcc ?? false,
      purchaseOpinion: data.purchaseOpinion,

      // AVL 적용 선종(조선)
      shipTypeCommon: data.shipTypeCommon ?? false,
      shipTypeAmax: data.shipTypeAmax ?? false,
      shipTypeSmax: data.shipTypeSmax ?? false,
      shipTypeVlcc: data.shipTypeVlcc ?? false,
      shipTypeLngc: data.shipTypeLngc ?? false,
      shipTypeCont: data.shipTypeCont ?? false,

      // AVL 적용 선종(해양)
      offshoreTypeCommon: data.offshoreTypeCommon ?? false,
      offshoreTypeFpso: data.offshoreTypeFpso ?? false,
      offshoreTypeFlng: data.offshoreTypeFlng ?? false,
      offshoreTypeFpu: data.offshoreTypeFpu ?? false,
      offshoreTypePlatform: data.offshoreTypePlatform ?? false,
      offshoreTypeWtiv: data.offshoreTypeWtiv ?? false,
      offshoreTypeGom: data.offshoreTypeGom ?? false,

      // eVCP 미등록 정보
      picName: data.picName,
      picEmail: data.picEmail,
      picPhone: data.picPhone,
      agentName: data.agentName,
      agentEmail: data.agentEmail,
      agentPhone: data.agentPhone,

      // 업체 실적 현황
      recentQuoteDate: data.recentQuoteDate,
      recentQuoteNumber: data.recentQuoteNumber,
      recentOrderDate: data.recentOrderDate,
      recentOrderNumber: data.recentOrderNumber,

      // 업데이트 히스토리
      registrationDate: currentTimestamp,
      registrant: data.registrant || 'system',
      lastModifiedDate: currentTimestamp,
      lastModifier: data.lastModifier || 'system',
    };

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

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

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

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

    const createdItem = result[0];

    // 생성된 데이터를 VendorPool 타입으로 변환
    const transformedData: VendorPool = {
      ...createdItem,
      selected: false,
      registrationDate: createdItem.registrationDate ? createdItem.registrationDate.toISOString().split('T')[0] : '',
      lastModifiedDate: createdItem.lastModifiedDate ? createdItem.lastModifiedDate.toISOString().split('T')[0] : '',
      // string 필드들의 null 처리
      packageCode: createdItem.packageCode || '',
      packageName: createdItem.packageName || '',
      materialGroupCode: createdItem.materialGroupCode || '',
      materialGroupName: createdItem.materialGroupName || '',
      smCode: createdItem.smCode || '',
      similarMaterialNamePurchase: createdItem.similarMaterialNamePurchase || '',
      similarMaterialNameOther: createdItem.similarMaterialNameOther || '',
      vendorCode: createdItem.vendorCode || '',
      vendorName: createdItem.vendorName || '',
      taxId: createdItem.taxId || '',
      faStatus: createdItem.faStatus || '',
      faRemark: createdItem.faRemark || '',
      tier: createdItem.tier || '',
      contractSignerCode: createdItem.contractSignerCode || '',
      contractSignerName: createdItem.contractSignerName || '',
      headquarterLocation: createdItem.headquarterLocation || '',
      manufacturingLocation: createdItem.manufacturingLocation || '',
      avlVendorName: createdItem.avlVendorName || '',
      similarVendorName: createdItem.similarVendorName || '',
      purchaseOpinion: createdItem.purchaseOpinion || '',
      picName: createdItem.picName || '',
      picEmail: createdItem.picEmail || '',
      picPhone: createdItem.picPhone || '',
      agentName: createdItem.agentName || '',
      agentEmail: createdItem.agentEmail || '',
      agentPhone: createdItem.agentPhone || '',
      recentQuoteDate: createdItem.recentQuoteDate || '',
      recentQuoteNumber: createdItem.recentQuoteNumber || '',
      recentOrderDate: createdItem.recentOrderDate || '',
      recentOrderNumber: createdItem.recentOrderNumber || '',
      registrant: createdItem.registrant || '',
      lastModifier: createdItem.lastModifier || '',
      // boolean 필드들을 적절히 처리
      faTarget: createdItem.faTarget ?? false,
      hasAvl: createdItem.hasAvl ?? false,
      isAgent: createdItem.isAgent ?? false,
      isBlacklist: createdItem.isBlacklist ?? false,
      isBcc: createdItem.isBcc ?? false,
      // 선종 적용 정보
      shipTypeCommon: createdItem.shipTypeCommon ?? false,
      shipTypeAmax: createdItem.shipTypeAmax ?? false,
      shipTypeSmax: createdItem.shipTypeSmax ?? false,
      shipTypeVlcc: createdItem.shipTypeVlcc ?? false,
      shipTypeLngc: createdItem.shipTypeLngc ?? false,
      shipTypeCont: createdItem.shipTypeCont ?? false,
      offshoreTypeCommon: createdItem.offshoreTypeCommon ?? false,
      offshoreTypeFpso: createdItem.offshoreTypeFpso ?? false,
      offshoreTypeFlng: createdItem.offshoreTypeFlng ?? false,
      offshoreTypeFpu: createdItem.offshoreTypeFpu ?? false,
      offshoreTypePlatform: createdItem.offshoreTypePlatform ?? false,
      offshoreTypeWtiv: createdItem.offshoreTypeWtiv ?? false,
      offshoreTypeGom: createdItem.offshoreTypeGom ?? false,
    };

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

    // 캐시 무효화 - 모든 Vendor Pool 관련 캐시를 갱신
    revalidateTag('vendor-pool-list');
    revalidateTag('vendor-pool-stats');

    debugSuccess('Vendor Pool 캐시 무효화 완료', { tags: ['vendor-pool-list', 'vendor-pool-stats'] });

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

    // Unique 제약 조건 위반 감지
    const errorMessage = err instanceof Error ? err.message : String(err);
    if (errorMessage.includes('unique_vendor_pool_combination') ||
        errorMessage.includes('duplicate key value') ||
        errorMessage.includes('violates unique constraint')) {
      debugError('Unique 제약 조건 위반 감지', {
        constructionSector: data.constructionSector,
        htDivision: data.htDivision,
        materialGroupCode: data.materialGroupCode,
        vendorName: data.vendorName,
        error: err
      });
      // Unique 제약 위반의 경우 특별한 에러 객체를 throw
      throw new Error('DUPLICATE_VENDOR_POOL');
    }

    return null;
  }
}

/**
 * Vendor Pool 업데이트
 */
export async function updateVendorPool(id: number, data: Partial<VendorPool>): Promise<VendorPool | null> {
  try {
    debugLog('Vendor Pool 업데이트 시작', { id, updateData: data });

    const currentTimestamp = new Date();

    // 업데이트할 데이터 준비 (id, registrationDate, registrant는 제외)
    const updateData: any = {};

    // 기본 정보
    if (data.constructionSector !== undefined) updateData.constructionSector = data.constructionSector;
    if (data.htDivision !== undefined) updateData.htDivision = data.htDivision;

    // 설계 정보
    if (data.designCategoryCode !== undefined) updateData.designCategoryCode = data.designCategoryCode;
    if (data.designCategory !== undefined) updateData.designCategory = data.designCategory;
    if (data.equipBulkDivision !== undefined) updateData.equipBulkDivision = data.equipBulkDivision;

    // 패키지 정보
    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.smCode !== undefined) updateData.smCode = data.smCode;
    if (data.similarMaterialNamePurchase !== undefined) updateData.similarMaterialNamePurchase = data.similarMaterialNamePurchase;
    if (data.similarMaterialNameOther !== undefined) updateData.similarMaterialNameOther = data.similarMaterialNameOther;

    // 협력업체 정보
    if (data.vendorCode !== undefined) updateData.vendorCode = data.vendorCode;
    if (data.vendorName !== undefined) updateData.vendorName = data.vendorName;

    // 사업 및 인증 정보
    if (data.faTarget !== undefined) updateData.faTarget = data.faTarget;
    if (data.faStatus !== undefined) updateData.faStatus = data.faStatus;
    if (data.faRemark !== undefined) updateData.faRemark = data.faRemark;
    if (data.tier !== undefined) updateData.tier = data.tier;
    if (data.isAgent !== undefined) updateData.isAgent = data.isAgent;

    // 계약 정보
    if (data.contractSignerCode !== undefined) updateData.contractSignerCode = data.contractSignerCode;
    if (data.contractSignerName !== undefined) updateData.contractSignerName = data.contractSignerName;

    // 위치 정보
    if (data.headquarterLocation !== undefined) updateData.headquarterLocation = data.headquarterLocation;
    if (data.manufacturingLocation !== undefined) updateData.manufacturingLocation = data.manufacturingLocation;

    // AVL 관련 정보
    if (data.avlVendorName !== undefined) updateData.avlVendorName = data.avlVendorName;
    if (data.similarVendorName !== undefined) updateData.similarVendorName = data.similarVendorName;
    if (data.hasAvl !== undefined) updateData.hasAvl = data.hasAvl;

    // 상태 정보
    if (data.isBlacklist !== undefined) updateData.isBlacklist = data.isBlacklist;
    if (data.isBcc !== undefined) updateData.isBcc = data.isBcc;
    if (data.purchaseOpinion !== undefined) updateData.purchaseOpinion = data.purchaseOpinion;

    // AVL 적용 선종(조선)
    if (data.shipTypeCommon !== undefined) updateData.shipTypeCommon = data.shipTypeCommon;
    if (data.shipTypeAmax !== undefined) updateData.shipTypeAmax = data.shipTypeAmax;
    if (data.shipTypeSmax !== undefined) updateData.shipTypeSmax = data.shipTypeSmax;
    if (data.shipTypeVlcc !== undefined) updateData.shipTypeVlcc = data.shipTypeVlcc;
    if (data.shipTypeLngc !== undefined) updateData.shipTypeLngc = data.shipTypeLngc;
    if (data.shipTypeCont !== undefined) updateData.shipTypeCont = data.shipTypeCont;

    // AVL 적용 선종(해양)
    if (data.offshoreTypeCommon !== undefined) updateData.offshoreTypeCommon = data.offshoreTypeCommon;
    if (data.offshoreTypeFpso !== undefined) updateData.offshoreTypeFpso = data.offshoreTypeFpso;
    if (data.offshoreTypeFlng !== undefined) updateData.offshoreTypeFlng = data.offshoreTypeFlng;
    if (data.offshoreTypeFpu !== undefined) updateData.offshoreTypeFpu = data.offshoreTypeFpu;
    if (data.offshoreTypePlatform !== undefined) updateData.offshoreTypePlatform = data.offshoreTypePlatform;
    if (data.offshoreTypeWtiv !== undefined) updateData.offshoreTypeWtiv = data.offshoreTypeWtiv;
    if (data.offshoreTypeGom !== undefined) updateData.offshoreTypeGom = data.offshoreTypeGom;

    // eVCP 미등록 정보
    if (data.picName !== undefined) updateData.picName = data.picName;
    if (data.picEmail !== undefined) updateData.picEmail = data.picEmail;
    if (data.picPhone !== undefined) updateData.picPhone = data.picPhone;
    if (data.agentName !== undefined) updateData.agentName = data.agentName;
    if (data.agentEmail !== undefined) updateData.agentEmail = data.agentEmail;
    if (data.agentPhone !== undefined) updateData.agentPhone = data.agentPhone;

    // 업체 실적 현황
    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;

    // 업데이트 히스토리
    updateData.lastModifiedDate = currentTimestamp;
    updateData.lastModifier = data.lastModifier || 'system';

    // 업데이트할 데이터가 없는 경우
    if (Object.keys(updateData).length === 2) { // lastModifiedDate, lastModifier만 있는 경우
      // 기존 데이터를 반환
      return await getVendorPoolById(id);
    }

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

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

    const updatedItem = result[0];

    // 업데이트된 데이터를 VendorPool 타입으로 변환
    const transformedData: VendorPool = {
      ...updatedItem,
      selected: false,
      registrationDate: updatedItem.registrationDate ? updatedItem.registrationDate.toISOString().split('T')[0] : '',
      lastModifiedDate: updatedItem.lastModifiedDate ? updatedItem.lastModifiedDate.toISOString().split('T')[0] : '',
      // string 필드들의 null 처리
      packageCode: updatedItem.packageCode || '',
      packageName: updatedItem.packageName || '',
      materialGroupCode: updatedItem.materialGroupCode || '',
      materialGroupName: updatedItem.materialGroupName || '',
      smCode: updatedItem.smCode || '',
      similarMaterialNamePurchase: updatedItem.similarMaterialNamePurchase || '',
      similarMaterialNameOther: updatedItem.similarMaterialNameOther || '',
      vendorCode: updatedItem.vendorCode || '',
      vendorName: updatedItem.vendorName || '',
      taxId: updatedItem.taxId || '',
      faStatus: updatedItem.faStatus || '',
      faRemark: updatedItem.faRemark || '',
      tier: updatedItem.tier || '',
      contractSignerCode: updatedItem.contractSignerCode || '',
      contractSignerName: updatedItem.contractSignerName || '',
      headquarterLocation: updatedItem.headquarterLocation || '',
      manufacturingLocation: updatedItem.manufacturingLocation || '',
      avlVendorName: updatedItem.avlVendorName || '',
      similarVendorName: updatedItem.similarVendorName || '',
      purchaseOpinion: updatedItem.purchaseOpinion || '',
      picName: updatedItem.picName || '',
      picEmail: updatedItem.picEmail || '',
      picPhone: updatedItem.picPhone || '',
      agentName: updatedItem.agentName || '',
      agentEmail: updatedItem.agentEmail || '',
      agentPhone: updatedItem.agentPhone || '',
      recentQuoteDate: updatedItem.recentQuoteDate || '',
      recentQuoteNumber: updatedItem.recentQuoteNumber || '',
      recentOrderDate: updatedItem.recentOrderDate || '',
      recentOrderNumber: updatedItem.recentOrderNumber || '',
      registrant: updatedItem.registrant || '',
      lastModifier: updatedItem.lastModifier || 'system',
      // boolean 필드들을 적절히 처리
      faTarget: updatedItem.faTarget ?? false,
      hasAvl: updatedItem.hasAvl ?? false,
      isAgent: updatedItem.isAgent ?? false,
      isBlacklist: updatedItem.isBlacklist ?? false,
      isBcc: updatedItem.isBcc ?? false,
      // 선종 적용 정보
      shipTypeCommon: updatedItem.shipTypeCommon ?? false,
      shipTypeAmax: updatedItem.shipTypeAmax ?? false,
      shipTypeSmax: updatedItem.shipTypeSmax ?? false,
      shipTypeVlcc: updatedItem.shipTypeVlcc ?? false,
      shipTypeLngc: updatedItem.shipTypeLngc ?? false,
      shipTypeCont: updatedItem.shipTypeCont ?? false,
      offshoreTypeCommon: updatedItem.offshoreTypeCommon ?? false,
      offshoreTypeFpso: updatedItem.offshoreTypeFpso ?? false,
      offshoreTypeFlng: updatedItem.offshoreTypeFlng ?? false,
      offshoreTypeFpu: updatedItem.offshoreTypeFpu ?? false,
      offshoreTypePlatform: updatedItem.offshoreTypePlatform ?? false,
      offshoreTypeWtiv: updatedItem.offshoreTypeWtiv ?? false,
      offshoreTypeGom: updatedItem.offshoreTypeGom ?? false,
    };

    debugSuccess('Vendor Pool 업데이트 완료', { id, result: transformedData });

    // 캐시 무효화 - 모든 Vendor Pool 관련 캐시를 갱신
    revalidateTag('vendor-pool-list');
    revalidateTag('vendor-pool-stats');

    debugSuccess('Vendor Pool 캐시 무효화 완료', { tags: ['vendor-pool-list', 'vendor-pool-stats'] });

    return transformedData;
  } catch (err) {
    debugError('Vendor Pool 업데이트 실패', { error: err, id, updateData: data });
    console.error("Error in updateVendorPool:", err);

    // Unique 제약 조건 위반 감지
    const errorMessage = err instanceof Error ? err.message : String(err);
    if (errorMessage.includes('unique_vendor_pool_combination') ||
        errorMessage.includes('duplicate key value') ||
        errorMessage.includes('violates unique constraint')) {
      debugError('Unique 제약 조건 위반 감지', {
        id,
        constructionSector: data.constructionSector,
        htDivision: data.htDivision,
        materialGroupCode: data.materialGroupCode,
        vendorName: data.vendorName,
        error: err
      });
      // Unique 제약 위반의 경우 특별한 에러 객체를 throw
      throw new Error('DUPLICATE_VENDOR_POOL');
    }

    return null;
  }
}

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

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

    // Drizzle에서는 delete의 반환값이 삭제된 행의 수를 나타냄
    // result.rowsAffected 또는 다른 방식으로 확인
    // 실제로는 affectedRows나 rowCount 등을 확인해야 하지만,
    // drizzle의 delete는 성공 시 빈 배열이나 특정 값을 반환할 수 있음

    // 삭제가 성공했는지 확인하기 위해 다시 조회해보기
    const checkDeleted = await db
      .select({ id: vendorPool.id })
      .from(vendorPool)
      .where(eq(vendorPool.id, id))
      .limit(1);

    // 조회 결과가 없으면 삭제 성공
    const isDeleted = checkDeleted.length === 0;

    if (isDeleted) {
      debugSuccess('Vendor Pool 삭제 완료', { id });

      // 캐시 무효화 - 모든 Vendor Pool 관련 캐시를 갱신
      revalidateTag('vendor-pool-list');
      revalidateTag('vendor-pool-stats');

      debugSuccess('Vendor Pool 캐시 무효화 완료', { tags: ['vendor-pool-list', 'vendor-pool-stats'] });
    } else {
      debugWarn('Vendor Pool 삭제 실패: 항목이 존재함', { id });
    }

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