// src/lib/vendors/repository.ts

import { eq, inArray, count, desc, not, isNull, and } from "drizzle-orm";
import db from '@/db/db';
import { SQL } from "drizzle-orm";
import { techVendors, techVendorContacts, techVendorPossibleItems, type TechVendor, type TechVendorContact, 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 selectTechVendorPossibleItems(
  tx: any,
  params: {
    where?: SQL<unknown>;
    orderBy?: SQL<unknown>[];
  } & PaginationParams
) {
  const query = tx.select({
    id: techVendorPossibleItems.id,
    vendorId: techVendorPossibleItems.vendorId,
    shipbuildingItemId: techVendorPossibleItems.shipbuildingItemId,
    offshoreTopItemId: techVendorPossibleItems.offshoreTopItemId,
    offshoreHullItemId: techVendorPossibleItems.offshoreHullItemId,
    createdAt: techVendorPossibleItems.createdAt,
    updatedAt: techVendorPossibleItems.updatedAt,
  }).from(techVendorPossibleItems);

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

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

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

  return query;
}

// 아이템 수 카운트 (새 스키마용)
export async function countTechVendorPossibleItems(tx: any, where?: SQL<unknown>) {
  const query = tx.select({ count: count() }).from(techVendorPossibleItems);

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

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

// 아이템 생성
export async function insertTechVendorItem(
  tx: any,
  data: Omit<NewTechVendorItem, "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 {
    const workTypes: string[] = [];

    // 벤더 타입에 따라 해당하는 아이템 테이블에서 worktype 조회
    if (vendorType.includes('조선')) {
      // 조선 아이템들의 workType 조회
      const shipWorkTypes = await tx
        .select({ workType: itemShipbuilding.workType })
        .from(techVendorPossibleItems)
        .leftJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
        .where(and(
          eq(techVendorPossibleItems.vendorId, vendorId),
          not(isNull(techVendorPossibleItems.shipbuildingItemId))
        ));
      workTypes.push(...shipWorkTypes.map((item: { workType: string | null }) => item.workType).filter(Boolean));
    }

    if (vendorType.includes('해양TOP')) {
      // 해양 TOP 아이템들의 workType 조회
      const topWorkTypes = await tx
        .select({ workType: itemOffshoreTop.workType })
        .from(techVendorPossibleItems)
        .leftJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
        .where(and(
          eq(techVendorPossibleItems.vendorId, vendorId),
          not(isNull(techVendorPossibleItems.offshoreTopItemId))
        ));
      workTypes.push(
        ...topWorkTypes
          .map((item: { workType: string | null }) => item.workType)
          .filter(Boolean) as string[]
      );
    }

    if (vendorType.includes('해양HULL')) {
      // 해양 HULL 아이템들의 workType 조회
      const hullWorkTypes = await tx
        .select({ workType: itemOffshoreHull.workType })
        .from(techVendorPossibleItems)
        .leftJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id))
        .where(and(
          eq(techVendorPossibleItems.vendorId, vendorId),
          not(isNull(techVendorPossibleItems.offshoreHullItemId))
        ));
      workTypes.push(...hullWorkTypes.map((item: { workType: string | null }) => item.workType).filter(Boolean));
    }

    // 중복 제거 후 반환
    const uniqueWorkTypes = [...new Set(workTypes)];
    
    return uniqueWorkTypes;
  } catch (error) {
    console.error('getVendorWorkTypes 오류:', error);
    return [];
  }
}