path: root/lib/tech-vendor-possible-items/service.ts

"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)
import { eq, and, inArray, desc, asc, or, ilike, isNull } from "drizzle-orm";
import db from "@/db/db";
import { 
  techVendors, 
  techVendorPossibleItems
} from "@/db/schema/techVendors";
import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items";
import { unstable_cache } from "@/lib/unstable-cache";
import { filterColumns } from "@/lib/filter-columns";
import type { GetTechVendorPossibleItemsSchema } from "./validations";
import {
  selectTechVendorPossibleItemsWithJoin,
  countTechVendorPossibleItemsWithJoin,
} from "./repository";

export interface TechVendorPossibleItemsData {
  id: number;
  vendorId: number;
  vendorCode: string | null;
  vendorName: string;
  vendorEmail: string | null;
  techVendorType: string;
  itemCode: string;
  workType: string | null;
  shipTypes: string | null;
  itemList: string | null;
  subItemList: string | null;
  createdAt: Date;
  updatedAt: Date;
}

export interface CreateTechVendorPossibleItemData {
  vendorId: number; // 필수: 벤더 ID (Add Dialog에서 벤더 선택 시 사용)
  itemCode: string; // 필수: 아이템 코드
  workType?: string | null; // 공종 (아이템에서 가져온 정보)
  shipTypes?: string | null; // 선종 (아이템에서 가져온 정보)
  itemList?: string | null; // 아이템리스트 (아이템에서 가져온 정보)
  subItemList?: string | null; // 서브아이템리스트 (아이템에서 가져온 정보)
}

export interface ImportTechVendorPossibleItemData {
  vendorCode?: string;
  vendorEmail: string; // 필수: 벤더 이메일
  itemCode: string; // 필수: 아이템 코드
  workType?: string;
  shipTypes?: string;
  itemList?: string;
  subItemList?: string;
}

export interface ImportResult {
  success: boolean;
  totalRows: number;
  successCount: number;
  failedRows: { 
    row: number; 
    error: string; 
    vendorCode?: string; 
    vendorEmail?: string;
    itemCode?: string;
    workType?: string;
    shipTypes?: string;
    itemList?: string;
    subItemList?: string;
  }[];
}



/**
 * 견적프로젝트 패턴에 맞는 메인 조회 함수
 */
export async function getTechVendorPossibleItems(input: GetTechVendorPossibleItemsSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        // 고급 필터링 (DataTableAdvancedToolbar용)
        const advancedWhere = filterColumns({
          table: techVendorPossibleItems,
          filters: input.filters,
          joinOperator: input.joinOperator,
        });

        // 전역 검색 (search box용)
        let globalWhere;
        if (input.search) {
          const s = `%${input.search}%`;
          globalWhere = or(
            ilike(techVendors.vendorCode, s),
            ilike(techVendors.vendorName, s),
            ilike(techVendorPossibleItems.vendorEmail, s),
            ilike(techVendorPossibleItems.itemCode, s),
            ilike(techVendorPossibleItems.workType, s),
            ilike(techVendorPossibleItems.shipTypes, s),
            ilike(techVendorPossibleItems.itemList, s),
            ilike(techVendorPossibleItems.subItemList, s),
          );
        }

        // 기존 호환성을 위한 개별 필터들
        const legacyFilters = [];
        if (input.vendorCode) {
          legacyFilters.push(ilike(techVendorPossibleItems.vendorCode, `%${input.vendorCode}%`));
        }
        if (input.vendorName) {
          legacyFilters.push(ilike(techVendors.vendorName, `%${input.vendorName}%`));
        }
        if (input.itemCode) {
          legacyFilters.push(ilike(techVendorPossibleItems.itemCode, `%${input.itemCode}%`));
        }

        // 벤더 타입 필터링
        if (input.vendorType && input.vendorType !== "all") {
          const vendorTypeMap = {
            "ship": "조선",
            "top": "해양TOP", 
            "hull": "해양HULL"
          };
          
          const actualVendorType = vendorTypeMap[input.vendorType as keyof typeof vendorTypeMap] || input.vendorType;
          
          if (actualVendorType) {
            legacyFilters.push(ilike(techVendors.techVendorType, `%${actualVendorType}%`));
          }
        }

        // 모든 조건 결합
        const finalWhere = and(
          advancedWhere,
          globalWhere,
          ...(legacyFilters.length > 0 ? [and(...legacyFilters)] : [])
        );

        // 정렬 조건
        const orderBy = [desc(techVendorPossibleItems.createdAt)];

        // 트랜잭션 내에서 Repository 호출
        const { data, total } = await db.transaction(async (tx) => {
          const data = await selectTechVendorPossibleItemsWithJoin(tx, finalWhere, orderBy, offset, input.perPage);

          const total = await countTechVendorPossibleItemsWithJoin(tx, finalWhere);
          return { data, total };
        });

        const pageCount = Math.ceil(total / input.perPage);

        return { data, pageCount, totalCount: total };
      } catch (error) {
        console.error("Error fetching tech vendor possible items:", error);
        return { data: [], pageCount: 0, totalCount: 0 };
      }
    },
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: ["tech-vendor-possible-items"],
    }
  )();
}



/**
 * 페이지네이션을 포함한 tech vendor possible items 조회
 */
// export async function getTechVendorPossibleItemsWithPagination(
//   page: number = 1,
//   pageSize: number = 50,
//   searchTerm?: string,
//   vendorType?: string
// ): Promise<{
//   data: TechVendorPossibleItemsData[];
//   totalCount: number;
//   totalPages: number;
// }> {
//   const whereConditions = [];
  
//   if (searchTerm) {
//     whereConditions.push(
//       sql`(
//         ${techVendors.vendorName} ILIKE ${`%${searchTerm}%`} OR
//         ${techVendors.vendorCode} ILIKE ${`%${searchTerm}%`} OR
//         ${techVendorPossibleItems.itemCode} ILIKE ${`%${searchTerm}%`}
//       )`
//     );
//   }

//   // 벤더 타입 필터링 로직 추가
//   if (vendorType && vendorType !== "all") {
//     // URL의 vendorType 파라미터를 실제 벤더 타입으로 매핑
//     const vendorTypeMap = {
//       "ship": "조선",
//       "top": "해양TOP", 
//       "hull": "해양HULL"
//     };
    
//     const actualVendorType = vendorType in vendorTypeMap 
//       ? vendorTypeMap[vendorType as keyof typeof vendorTypeMap]
//       : vendorType; // 매핑되지 않는 경우 원본 값 사용
    
//     if (actualVendorType) {
//       // techVendorType 필드는 콤마로 구분된 문자열이므로 LIKE 사용
//       whereConditions.push(sql`${techVendors.techVendorType} ILIKE ${`%${actualVendorType}%`}`);
//     }
//   }

//   const whereClause = whereConditions.length > 0 ? and(...whereConditions) : undefined;

//   // 총 개수 조회
//   const [totalCountResult] = await db
//     .select({ count: count() })
//     .from(techVendorPossibleItems)
//     .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
//     .where(whereClause);

//   const totalCount = totalCountResult.count;
//   const totalPages = Math.ceil(totalCount / pageSize);
//   const offset = (page - 1) * pageSize;

//   // 데이터 조회
//   const data = await db
//     .select({
//       id: techVendorPossibleItems.id,
//       vendorId: techVendorPossibleItems.vendorId,
//       vendorCode: techVendors.vendorCode,
//       vendorName: techVendors.vendorName,
//       techVendorType: techVendors.techVendorType,
//       itemCode: techVendorPossibleItems.itemCode,
//       createdAt: techVendorPossibleItems.createdAt,
//       updatedAt: techVendorPossibleItems.updatedAt,
//     })
//     .from(techVendorPossibleItems)
//     .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
//     .where(whereClause)
//     .orderBy(desc(techVendorPossibleItems.createdAt))
//     .limit(pageSize)
//     .offset(offset);

//   return {
//     data,
//     totalCount,
//     totalPages,
//   };
// }

/**
 * tech vendor possible item 생성 (Add Dialog용 - vendorId 기반)
 */
export async function createTechVendorPossibleItem(
  data: CreateTechVendorPossibleItemData
): Promise<{ success: boolean; error?: string }> {
  try {
    // 벤더 ID로 벤더 조회
    const vendor = await db
      .select()
      .from(techVendors)
      .where(eq(techVendors.id, data.vendorId))
      .limit(1);

    if (!vendor[0]) {
      return { success: false, error: "벤더를 찾을 수 없습니다." };
    }

    // 중복 체크 (벤더 + 아이템코드 + 공종 + 선종 조합)
    const existing = await db
      .select()
      .from(techVendorPossibleItems)
      .where(
        and(
          eq(techVendorPossibleItems.vendorId, data.vendorId),
          eq(techVendorPossibleItems.itemCode, data.itemCode),
          data.workType 
            ? eq(techVendorPossibleItems.workType, data.workType)
            : isNull(techVendorPossibleItems.workType),
          data.shipTypes 
            ? eq(techVendorPossibleItems.shipTypes, data.shipTypes)
            : isNull(techVendorPossibleItems.shipTypes)
        )
      )
      .limit(1);

    if (existing.length > 0) {
      return { success: false, error: "이미 존재하는 벤더-아이템 조합입니다." };
    }

    // 새로운 아이템 생성 (선택한 아이템의 정보를 그대로 저장)
    await db.insert(techVendorPossibleItems).values({
      vendorId: vendor[0].id,
      vendorCode: vendor[0].vendorCode,
      vendorEmail: vendor[0].email,
      itemCode: data.itemCode,
      workType: data.workType,
      shipTypes: data.shipTypes,
      itemList: data.itemList,
      subItemList: data.subItemList,
    });

    return { success: true };
  } catch (error) {
    console.error("Failed to create tech vendor possible item:", error);
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "생성 중 오류가 발생했습니다." 
    };
  }
}

/**
 * tech vendor possible items 삭제
 */
export async function deleteTechVendorPossibleItems(
  ids: number[]
): Promise<{ success: boolean; error?: string }> {
  try {
    await db
      .delete(techVendorPossibleItems)
      .where(inArray(techVendorPossibleItems.id, ids));

    return { success: true };
  } catch (error) {
    console.error("Failed to delete tech vendor possible items:", error);
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "삭제 중 오류가 발생했습니다." 
    };
  }
}

/**
 * 벤더 코드로 벤더 정보 조회
 */
export async function getTechVendorByCode(vendorCode: string) {
  const result = await db
    .select()
    .from(techVendors)
    .where(eq(techVendors.vendorCode, vendorCode))
    .limit(1);

  return result[0] || null;
}

/**
 * 벤더 이메일로 벤더 정보 조회
 */
export async function getTechVendorByEmail(vendorEmail: string) {
  const result = await db
    .select()
    .from(techVendors)
    .where(eq(techVendors.email, vendorEmail))
    .limit(1);

  return result[0] || null;
}

/**
 * 벤더 타입에 따라 적절한 아이템 테이블에서 아이템 조회
 */
export async function getItemByCodeAndVendorType(itemCode: string, vendorType: string) {
  try {
    switch (vendorType) {
      case "조선":
        const shipItem = await db
          .select()
          .from(itemShipbuilding)
          .where(eq(itemShipbuilding.itemCode, itemCode))
          .limit(1);
        return shipItem[0] ? {
          itemCode: shipItem[0].itemCode,
          workType: shipItem[0].workType
        } : null;

      case "해양TOP":
        const topItem = await db
          .select()
          .from(itemOffshoreTop)
          .where(eq(itemOffshoreTop.itemCode, itemCode))
          .limit(1);
        return topItem[0] ? {
          itemCode: topItem[0].itemCode,
          workType: topItem[0].workType
        } : null;

      case "해양HULL":
        const hullItem = await db
          .select()
          .from(itemOffshoreHull)
          .where(eq(itemOffshoreHull.itemCode, itemCode))
          .limit(1);
        return hullItem[0] ? {
          itemCode: hullItem[0].itemCode,
          workType: hullItem[0].workType
        } : null;

      default:
        return null;
    }
  } catch (error) {
    console.error("Error fetching item by code and vendor type:", error);
    return null;
  }
}

/**
 * 아이템 코드로 아이템 정보 조회 (기존 함수 - 호환성 유지)
 */
export async function getItemByCode(itemCode: string) {
  // 기존 items 테이블 대신 조선 테이블에서 먼저 조회 시도
  try {
    const shipItem = await db
      .select()
      .from(itemShipbuilding)
      .where(eq(itemShipbuilding.itemCode, itemCode))
      .limit(1);
    
    if (shipItem[0]) {
      return {
        itemCode: shipItem[0].itemCode,
      };
    }

    const topItem = await db
      .select()
      .from(itemOffshoreTop)
      .where(eq(itemOffshoreTop.itemCode, itemCode))
      .limit(1);
    
    if (topItem[0]) {
      return {
        itemCode: topItem[0].itemCode,
      };
    }

    const hullItem = await db
      .select()
      .from(itemOffshoreHull)
      .where(eq(itemOffshoreHull.itemCode, itemCode))
      .limit(1);
    
    if (hullItem[0]) {
      return {
        itemCode: hullItem[0].itemCode,
      };
    }

    return null;
  } catch (error) {
    console.error("Error fetching item by code:", error);
    return null;
  }
}

/**
 * Import 기능: 벤더이메일과 아이템정보를 통한 batch insert (새로운 스키마 버전)
 */
export async function importTechVendorPossibleItems(
  data: ImportTechVendorPossibleItemData[]
): Promise<ImportResult> {
  const result: ImportResult = {
    success: true,
    totalRows: data.length,
    successCount: 0,
    failedRows: [],
  };

  for (let i = 0; i < data.length; i++) {
    const row = data[i];
    const rowNumber = i + 1;

    try {
      // 벤더 이메일로 벤더 찾기 (필수)
      let vendor = null;
      
      if (row.vendorEmail && row.vendorEmail.trim()) {
        vendor = await getTechVendorByEmail(row.vendorEmail);
      } else {
        result.failedRows.push({
          row: rowNumber,
          error: "벤더 이메일은 필수입니다.",
          vendorCode: row.vendorCode,
          vendorEmail: row.vendorEmail,
          itemCode: row.itemCode,
          workType: row.workType,
          shipTypes: row.shipTypes,
          itemList: row.itemList,
          subItemList: row.subItemList,
        });
        continue;
      }
      
      if (!vendor) {
        result.failedRows.push({
          row: rowNumber,
          error: `벤더 이메일 '${row.vendorEmail}'을(를) 찾을 수 없습니다.`,
          vendorCode: row.vendorCode,
          vendorEmail: row.vendorEmail,
          itemCode: row.itemCode,
          workType: row.workType,
          shipTypes: row.shipTypes,
          itemList: row.itemList,
          subItemList: row.subItemList,
        });
        continue;
      }

      // 중복 체크 (벤더 + 아이템코드 + 공종 + 선종 조합)
      const existing = await db
        .select()
        .from(techVendorPossibleItems)
        .where(
          and(
            eq(techVendorPossibleItems.vendorId, vendor.id),
            eq(techVendorPossibleItems.itemCode, row.itemCode),
            row.workType 
              ? eq(techVendorPossibleItems.workType, row.workType)
              : isNull(techVendorPossibleItems.workType),
            row.shipTypes 
              ? eq(techVendorPossibleItems.shipTypes, row.shipTypes)
              : isNull(techVendorPossibleItems.shipTypes)
          )
        )
        .limit(1);

      if (existing.length > 0) {
        result.failedRows.push({
          row: rowNumber,
          error: `이미 존재하는 벤더-아이템 조합입니다.`,
          vendorCode: row.vendorCode,
          vendorEmail: row.vendorEmail,
          itemCode: row.itemCode,
          workType: row.workType,
          shipTypes: row.shipTypes,
          itemList: row.itemList,
          subItemList: row.subItemList,
        });
        continue;
      }

      // 새로운 아이템 생성
      await db.insert(techVendorPossibleItems).values({
        vendorId: vendor.id,
        vendorCode: vendor.vendorCode,
        vendorEmail: vendor.email,
        itemCode: row.itemCode,
        workType: row.workType || null,
        shipTypes: row.shipTypes || null,
        itemList: row.itemList || null,
        subItemList: row.subItemList || null,
      });

      result.successCount++;
    } catch (error) {
      result.failedRows.push({
        row: rowNumber,
        error: error instanceof Error ? error.message : "알 수 없는 오류",
        vendorCode: row.vendorCode,
        vendorEmail: row.vendorEmail,
        itemCode: row.itemCode,
        workType: row.workType,
        shipTypes: row.shipTypes,
        itemList: row.itemList,
        subItemList: row.subItemList,
      });
    }
  }

  if (result.failedRows.length > 0) {
    result.success = false;
  }

  return result;
}

/**
 * 모든 기술영업 벤더 조회 (드롭다운용)
 */
export async function getAllTechVendors() {
  return await db
    .select({
      id: techVendors.id,
      vendorCode: techVendors.vendorCode,
      vendorName: techVendors.vendorName,
      techVendorType: techVendors.techVendorType,
    })
    .from(techVendors)
    .where(eq(techVendors.status, "ACTIVE"))
    .orderBy(asc(techVendors.vendorName));
}

/**
 * 고유한 벤더 타입 목록 조회 (필터용)
 */
export async function getUniqueTechVendorTypes(): Promise<string[]> {
  try {
    const result = await db
      .select({
        techVendorType: techVendors.techVendorType,
      })
      .from(techVendors)
      .where(eq(techVendors.status, "ACTIVE"));

    // techVendorType이 JSON 배열 형태로 저장된 경우를 고려
    const allTypes = new Set<string>();
    
    result.forEach(row => {
      try {
        // techVendorType이 JSON 문자열인지 확인
        if (row.techVendorType && row.techVendorType.startsWith('[')) {
          const types = JSON.parse(row.techVendorType);
          if (Array.isArray(types)) {
            types.forEach(type => {
              if (type && typeof type === 'string') {
                allTypes.add(type.trim());
              }
            });
          }
        } else if (row.techVendorType) {
          // 단순 문자열인 경우
          row.techVendorType.split(',').forEach(type => {
            const trimmedType = type.trim();
            if (trimmedType) {
              allTypes.add(trimmedType);
            }
          });
        }
      } catch {
        // JSON 파싱 실패시 문자열로 처리
        if (row.techVendorType) {
          row.techVendorType.split(',').forEach(type => {
            const trimmedType = type.trim();
            if (trimmedType) {
              allTypes.add(trimmedType);
            }
          });
        }
      }
    });

    return Array.from(allTypes).sort();
  } catch (error) {
    console.error("Error fetching unique tech vendor types:", error);
    // 오류 발생시 기본 벤더 타입 반환
    return ["조선", "해양TOP", "해양HULL"];
  }
}

/**
 * 벤더 타입에 따른 아이템 목록 조회
 */
export async function getItemsByVendorType(vendorTypes: string): Promise<{
  itemCode: string;
  itemList: string | null;
  workType: string | null;
  shipTypes?: string | null;
  subItemList?: string | null;
}[]> {
  try {
    // 벤더 타입 파싱 개선
    let types: string[] = [];
    if (!vendorTypes) {
      return [];
    }
    
    if (vendorTypes.startsWith('[') && vendorTypes.endsWith(']')) {
      // JSON 배열 형태
      try {
        const parsed = JSON.parse(vendorTypes);
        types = Array.isArray(parsed) ? parsed.filter(Boolean) : [vendorTypes];
      } catch {
        types = [vendorTypes];
      }
    } else if (vendorTypes.includes(',')) {
      // 콤마로 구분된 문자열
      types = vendorTypes.split(',').map(t => t.trim()).filter(Boolean);
    } else {
      // 단일 문자열
      types = [vendorTypes.trim()].filter(Boolean);
    }
    // 벤더 타입 정렬 - 조선 > 해양TOP > 해양HULL 순
    const typeOrder = ["조선", "해양TOP", "해양HULL"];
    types.sort((a, b) => {
      const indexA = typeOrder.indexOf(a);
      const indexB = typeOrder.indexOf(b);
      
      // 정의된 순서에 있는 경우 우선순위 적용
      if (indexA !== -1 && indexB !== -1) {
        return indexA - indexB;
      }
      // 정의된 순서에 없는 경우 마지막에 배치하고 알파벳 순으로 정렬
      if (indexA !== -1) return -1;
      if (indexB !== -1) return 1;
      return a.localeCompare(b);
    });
    
    const allItems: any[] = [];

    // 각 벤더 타입에 따라 해당 아이템 테이블에서 조회
    for (const type of types) {
      switch (type) {
        case "조선":
          const shipItems = await db
            .select({
              itemCode: itemShipbuilding.itemCode,
              itemList: itemShipbuilding.itemList,
              workType: itemShipbuilding.workType,
              shipTypes: itemShipbuilding.shipTypes,
            })
            .from(itemShipbuilding);
          allItems.push(...shipItems);
          break;

        case "해양TOP":
          const topItems = await db
            .select({
              itemCode: itemOffshoreTop.itemCode,
              itemList: itemOffshoreTop.itemList,
              workType: itemOffshoreTop.workType,
              subItemList: itemOffshoreTop.subItemList,
            })
            .from(itemOffshoreTop);
          allItems.push(...topItems);
          break;

        case "해양HULL":
          const hullItems = await db
            .select({
              itemCode: itemOffshoreHull.itemCode,
              itemList: itemOffshoreHull.itemList,
              workType: itemOffshoreHull.workType,
              subItemList: itemOffshoreHull.subItemList,
            })
            .from(itemOffshoreHull);
          allItems.push(...hullItems);
          break;
      }
    }
    // // 중복 제거 (itemCode 기준)
    // const uniqueItems = allItems.filter((item, index, self) => 
    //   index === self.findIndex(i => i.itemCode === item.itemCode)
    // );

    // const finalItems = uniqueItems.filter(item => item.itemCode); // itemCode가 있는 것만 반환
    // console.log("Final items after deduplication and filtering:", finalItems.length);
    
    return allItems;
  } catch (error) {
    console.error("Error fetching items by vendor type:", error);
    return [];
  }
} 

/**
 * Excel Export 기능: 기술영업 벤더 가능 아이템 목록 내보내기
 */
export async function exportTechVendorPossibleItemsToExcel(): Promise<{
  success: boolean;
  data?: Array<{
    벤더코드: string | null;
    벤더명: string;
    벤더이메일: string | null;
    벤더타입: string;
    아이템코드: string;
    공종: string | null;
    선종: string | null;
    아이템리스트: string | null;
    서브아이템리스트: string | null;
    생성일: string;
  }>;
  error?: string;
}> {
  try {
    // 모든 데이터 조회 (페이지네이션 없이)
    const allData = await db
      .select({
        vendorCode: techVendorPossibleItems.vendorCode,
        vendorName: techVendors.vendorName,
        vendorEmail: techVendorPossibleItems.vendorEmail,
        techVendorType: techVendors.techVendorType,
        itemCode: techVendorPossibleItems.itemCode,
        workType: techVendorPossibleItems.workType,
        shipTypes: techVendorPossibleItems.shipTypes,
        itemList: techVendorPossibleItems.itemList,
        subItemList: techVendorPossibleItems.subItemList,
        createdAt: techVendorPossibleItems.createdAt,
      })
      .from(techVendorPossibleItems)
      .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
      .orderBy(desc(techVendorPossibleItems.createdAt));

    // Excel 형태로 변환
    const excelData = allData.map(item => ({
      벤더코드: item.vendorCode,
      벤더명: item.vendorName,
      벤더이메일: item.vendorEmail,
      벤더타입: item.techVendorType,
      아이템코드: item.itemCode,
      공종: item.workType,
      선종: item.shipTypes,
      아이템리스트: item.itemList,
      서브아이템리스트: item.subItemList,
      생성일: item.createdAt.toISOString().split('T')[0], // YYYY-MM-DD 형식
    }));

    return {
      success: true,
      data: excelData,
    };
  } catch (error) {
    console.error("Error exporting tech vendor possible items:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "내보내기 중 오류가 발생했습니다.",
    };
  }
}