// src/lib/vendors/repository.ts

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

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));

      return {
        ...vendor,
        hasAttachments: attachments.length > 0,
        attachmentsList: attachments,
      } 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();
}