// src/lib/vendors/repository.ts

import { eq, inArray, count, desc } from "drizzle-orm";
import db from '@/db/db';
import { SQL } from "drizzle-orm";
import { techVendors, techVendorContacts, techVendorPossibleItems, techVendorItemsView, type TechVendor, type TechVendorContact, type TechVendorItem, type TechVendorWithAttachments, techVendorAttachments } from "@/db/schema/techVendors";
import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items";

export type NewTechVendorContact = typeof techVendorContacts.$inferInsert
export type NewTechVendorItem = typeof techVendorPossibleItems.$inferInsert

type PaginationParams = {
  offset: number;
  limit: number;
};

// 메인 벤더 목록 조회 (첨부파일 정보 포함)
export async function selectTechVendorsWithAttachments(
  tx: any,
  params: {
    where?: SQL<unknown>;
    orderBy?: SQL<unknown>[];
  } & PaginationParams
) {
  const query = tx
    .select({
      id: techVendors.id,
      vendorName: techVendors.vendorName,
      vendorCode: techVendors.vendorCode,
      taxId: techVendors.taxId,
      address: techVendors.address,
      country: techVendors.country,
      phone: techVendors.phone,
      email: techVendors.email,
      website: techVendors.website,
      status: techVendors.status,
      techVendorType: techVendors.techVendorType,
      representativeName: techVendors.representativeName,
      representativeEmail: techVendors.representativeEmail,
      representativePhone: techVendors.representativePhone,
      representativeBirth: techVendors.representativeBirth,
      countryEng: techVendors.countryEng,
      countryFab: techVendors.countryFab,
      agentName: techVendors.agentName,
      agentPhone: techVendors.agentPhone,
      agentEmail: techVendors.agentEmail,
      items: techVendors.items,
      createdAt: techVendors.createdAt,
      updatedAt: techVendors.updatedAt,
    })
    .from(techVendors);

  // where 조건이 있는 경우
  if (params.where) {
    query.where(params.where);
  }

  // 정렬 조건이 있는 경우
  if (params.orderBy && params.orderBy.length > 0) {
    query.orderBy(...params.orderBy);
  } else {
    // 기본 정렬: 생성일 기준 내림차순
    query.orderBy(desc(techVendors.createdAt));
  }

  // 페이지네이션 적용
  query.offset(params.offset).limit(params.limit);

  const vendors = await query;

  // 첨부파일 정보 가져오기
  const vendorsWithAttachments = await Promise.all(
    vendors.map(async (vendor: TechVendor) => {
      const attachments = await tx
        .select({
          id: techVendorAttachments.id,
          fileName: techVendorAttachments.fileName,
          filePath: techVendorAttachments.filePath,
        })
        .from(techVendorAttachments)
        .where(eq(techVendorAttachments.vendorId, vendor.id));

      // 벤더의 worktype 조회
      const workTypes = await getVendorWorkTypes(tx, vendor.id, vendor.techVendorType);

      return {
        ...vendor,
        hasAttachments: attachments.length > 0,
        attachmentsList: attachments,
        workTypes: workTypes.join(', '), // 콤마로 구분해서 저장
      } as TechVendorWithAttachments;
    })
  );

  return vendorsWithAttachments;
}

// 메인 벤더 목록 수 조회 (첨부파일 정보 포함)
export async function countTechVendorsWithAttachments(
  tx: any,
  where?: SQL<unknown>
) {
  const query = tx.select({ count: count() }).from(techVendors);

  if (where) {
    query.where(where);
  }

  const result = await query;
  return result[0].count;
}

// 기술영업 벤더 조회
export async function selectTechVendors(
  tx: any,
  params: {
    where?: SQL<unknown>;
    orderBy?: SQL<unknown>[];
  } & PaginationParams
) {
  const query = tx.select().from(techVendors);

  if (params.where) {
    query.where(params.where);
  }

  if (params.orderBy && params.orderBy.length > 0) {
    query.orderBy(...params.orderBy);
  } else {
    query.orderBy(desc(techVendors.createdAt));
  }

  query.offset(params.offset).limit(params.limit);

  return query;
}

// 기술영업 벤더 수 카운트
export async function countTechVendors(tx: any, where?: SQL<unknown>) {
  const query = tx.select({ count: count() }).from(techVendors);

  if (where) {
    query.where(where);
  }

  const result = await query;
  return result[0].count;
}

// 벤더 상태별 카운트
export async function groupByTechVendorStatus(tx: any) {
  const result = await tx
    .select({
      status: techVendors.status,
      count: count(),
    })
    .from(techVendors)
    .groupBy(techVendors.status);

  return result;
}

// 벤더 상세 정보 조회
export async function getTechVendorById(id: number) {
  const result = await db
    .select()
    .from(techVendors)
    .where(eq(techVendors.id, id));

  return result.length > 0 ? result[0] : null;
}

// 벤더 연락처 정보 조회
export async function getTechVendorContactsById(id: number) {
  const result = await db
    .select()
    .from(techVendorContacts)
    .where(eq(techVendorContacts.id, id));

  return result.length > 0 ? result[0] : null;
}

// 신규 벤더 생성
export async function insertTechVendor(
  tx: any,
  data: Omit<TechVendor, "id" | "createdAt" | "updatedAt">
) {
  return tx
    .insert(techVendors)
    .values({
      ...data,
      createdAt: new Date(),
      updatedAt: new Date(),
    })
    .returning();
}

// 벤더 정보 업데이트 (단일)
export async function updateTechVendor(
  tx: any,
  id: string | number,
  data: Partial<TechVendor>
) {
  return tx
    .update(techVendors)
    .set({
      ...data,
      updatedAt: new Date(),
    })
    .where(eq(techVendors.id, Number(id)))
    .returning();
}

// 벤더 정보 업데이트 (다수)
export async function updateTechVendors(
  tx: any,
  ids: (string | number)[],
  data: Partial<TechVendor>
) {
  return tx
    .update(techVendors)
    .set({
      ...data,
      updatedAt: new Date(),
    })
    .where(inArray(techVendors.id, ids.map(id => Number(id))))
    .returning();
}

// 벤더 연락처 조회
export async function selectTechVendorContacts(
  tx: any,
  params: {
    where?: SQL<unknown>;
    orderBy?: SQL<unknown>[];
  } & PaginationParams
) {
  const query = tx.select().from(techVendorContacts);

  if (params.where) {
    query.where(params.where);
  }

  if (params.orderBy && params.orderBy.length > 0) {
    query.orderBy(...params.orderBy);
  } else {
    query.orderBy(desc(techVendorContacts.createdAt));
  }

  query.offset(params.offset).limit(params.limit);

  return query;
}

// 벤더 연락처 수 카운트
export async function countTechVendorContacts(tx: any, where?: SQL<unknown>) {
  const query = tx.select({ count: count() }).from(techVendorContacts);

  if (where) {
    query.where(where);
  }

  const result = await query;
  return result[0].count;
}

// 연락처 생성
export async function insertTechVendorContact(
  tx: any,
  data: Omit<TechVendorContact, "id" | "createdAt" | "updatedAt">
) {
  return tx
    .insert(techVendorContacts)
    .values({
      ...data,
      createdAt: new Date(),
      updatedAt: new Date(),
    })
    .returning();
}

// 아이템 목록 조회
export async function selectTechVendorItems(
  tx: any,
  params: {
    where?: SQL<unknown>;
    orderBy?: SQL<unknown>[];
  } & PaginationParams
) {
  const query = tx.select().from(techVendorItemsView);

  if (params.where) {
    query.where(params.where);
  }

  if (params.orderBy && params.orderBy.length > 0) {
    query.orderBy(...params.orderBy);
  } else {
    query.orderBy(desc(techVendorItemsView.createdAt));
  }

  query.offset(params.offset).limit(params.limit);

  return query;
}

// 아이템 수 카운트
export async function countTechVendorItems(tx: any, where?: SQL<unknown>) {
  const query = tx.select({ count: count() }).from(techVendorItemsView);

  if (where) {
    query.where(where);
  }

  const result = await query;
  return result[0].count;
}

// 아이템 생성
export async function insertTechVendorItem(
  tx: any,
  data: Omit<TechVendorItem, "id" | "createdAt" | "updatedAt">
) {
  return tx
    .insert(techVendorPossibleItems)
    .values({
      ...data,
      createdAt: new Date(),
      updatedAt: new Date(),
    })
    .returning();
}

// 벤더의 worktype 조회
export async function getVendorWorkTypes(
  tx: any,
  vendorId: number,
  vendorType: string
): Promise<string[]> {
  try {
    // 벤더의 possible items 조회 - 모든 필드 가져오기
    const possibleItems = await tx
      .select({ 
        itemCode: techVendorPossibleItems.itemCode,
        shipTypes: techVendorPossibleItems.shipTypes,
        itemList: techVendorPossibleItems.itemList,
        subItemList: techVendorPossibleItems.subItemList,
        workType: techVendorPossibleItems.workType
      })
      .from(techVendorPossibleItems)
      .where(eq(techVendorPossibleItems.vendorId, vendorId));
    if (!possibleItems.length) {
      return [];
    }

    const workTypes: string[] = [];

    // 벤더 타입에 따라 해당하는 아이템 테이블에서 worktype 조회
    if (vendorType.includes('조선')) {
      const itemCodes = possibleItems
        .map((item: { itemCode?: string | null }) => item.itemCode)
        .filter(Boolean);
      
      if (itemCodes.length > 0) {
        const shipWorkTypes = await tx
          .select({ workType: itemShipbuilding.workType })
          .from(itemShipbuilding)
          .where(inArray(itemShipbuilding.itemCode, itemCodes));
        
        workTypes.push(...shipWorkTypes.map((item: { workType: string | null }) => item.workType).filter(Boolean));
      }
    }

    if (vendorType.includes('해양TOP')) {
      // 1. 아이템코드가 있는 경우
      const itemCodesTop = possibleItems
        .map((item: { itemCode?: string | null }) => item.itemCode)
        .filter(Boolean) as string[];

      if (itemCodesTop.length > 0) {
        const topWorkTypes = await tx
          .select({ workType: itemOffshoreTop.workType })
          .from(itemOffshoreTop)
          .where(inArray(itemOffshoreTop.itemCode, itemCodesTop));

        workTypes.push(
          ...topWorkTypes
            .map((item: { workType: string | null }) => item.workType)
            .filter(Boolean) as string[]
        );
      }

      // 2. 아이템코드가 없는 경우 서브아이템리스트로 매칭
      const itemsWithoutCodeTop = possibleItems.filter(
        (item: { itemCode?: string | null; subItemList?: string | null }) =>
          !item.itemCode && item.subItemList
      );
      if (itemsWithoutCodeTop.length > 0) {
        const subItemListsTop = itemsWithoutCodeTop
          .map((item: { subItemList?: string | null }) => item.subItemList)
          .filter(Boolean) as string[];

        if (subItemListsTop.length > 0) {
          const topWorkTypesBySubItem = await tx
            .select({ workType: itemOffshoreTop.workType })
            .from(itemOffshoreTop)
            .where(inArray(itemOffshoreTop.subItemList, subItemListsTop));

          workTypes.push(
            ...topWorkTypesBySubItem
              .map((item: { workType: string | null }) => item.workType)
              .filter(Boolean) as string[]
          );
        }
      }
    }
    if (vendorType.includes('해양HULL')) {
      // 1. 아이템코드가 있는 경우
      const itemCodes = possibleItems
        .map((item: { itemCode?: string | null }) => item.itemCode)
        .filter(Boolean);
      
      if (itemCodes.length > 0) {
        const hullWorkTypes = await tx
          .select({ workType: itemOffshoreHull.workType })
          .from(itemOffshoreHull)
          .where(inArray(itemOffshoreHull.itemCode, itemCodes));
        
        workTypes.push(...hullWorkTypes.map((item: { workType: string | null }) => item.workType).filter(Boolean));
      }

      // 2. 아이템코드가 없는 경우 서브아이템리스트로 매칭
      const itemsWithoutCodeHull = possibleItems.filter(
        (item: { itemCode?: string | null; subItemList?: string | null }) =>
          !item.itemCode && item.subItemList
      );

      if (itemsWithoutCodeHull.length > 0) {
        const subItemListsHull = itemsWithoutCodeHull
          .map((item: { subItemList?: string | null }) => item.subItemList)
          .filter(Boolean) as string[];
        
        if (subItemListsHull.length > 0) {
          const hullWorkTypesBySubItem = await tx
            .select({ workType: itemOffshoreHull.workType })
            .from(itemOffshoreHull)
            .where(inArray(itemOffshoreHull.subItemList, subItemListsHull));
          
          workTypes.push(...hullWorkTypesBySubItem.map((item: { workType: string | null }) => item.workType).filter(Boolean));
        }
      }
    }
    // 중복 제거 후 반환
    const uniqueWorkTypes = [...new Set(workTypes)];
    
    return uniqueWorkTypes;
  } catch (error) {
    console.error('getVendorWorkTypes 오류:', error);
    return [];
  }
}