import { eq, desc, count, SQL, sql, and, or, ilike } from "drizzle-orm";
import { 
  techVendors, 
  techVendorPossibleItems
} from "@/db/schema/techVendors";
import type { PgTransaction } from "drizzle-orm/pg-core";

/**
 * 기술영업 벤더 가능 아이템 목록 조회 (조인 포함)
 */
export async function selectTechVendorPossibleItemsWithJoin(
  tx: PgTransaction<any, any, any>,
  where: SQL | undefined,
  orderBy: SQL[],
  offset: number,
  limit: number
) {
  return await tx
    .select({
      id: techVendorPossibleItems.id,
      vendorId: techVendorPossibleItems.vendorId,
      vendorCode: techVendorPossibleItems.vendorCode, // 테이블에서 직접 조회
      vendorName: techVendors.vendorName,
      vendorEmail: techVendorPossibleItems.vendorEmail, // 테이블에서 직접 조회
      techVendorType: techVendors.techVendorType,
      vendorStatus: techVendors.status,
      itemCode: techVendorPossibleItems.itemCode,
      // 새로운 스키마: 테이블에서 직접 조회
      workType: techVendorPossibleItems.workType,
      shipTypes: techVendorPossibleItems.shipTypes,
      itemList: techVendorPossibleItems.itemList,
      subItemList: techVendorPossibleItems.subItemList,
      createdAt: techVendorPossibleItems.createdAt,
      updatedAt: techVendorPossibleItems.updatedAt,
    })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .where(where)
    .orderBy(...(orderBy || [desc(techVendorPossibleItems.createdAt)]))
    .limit(limit)
    .offset(offset);
}

/**
 * 기술영업 벤더 가능 아이템 총 개수 조회 (조인 포함)
 */
export async function countTechVendorPossibleItemsWithJoin(
  tx: PgTransaction<any, any, any>, 
  where?: SQL | undefined
) {
  const [result] = await tx
    .select({ count: count() })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .where(where);
  
  return result.count;
}

/**
 * 새로운 필드들을 위한 그룹별 통계 조회
 */
export async function getTechVendorPossibleItemsGroupStats(
  tx: PgTransaction<any, any, any>,
  groupBy: 'workType' | 'shipTypes' | 'vendorCode' | 'vendorEmail',
  where?: SQL | undefined
) {
  const groupField = techVendorPossibleItems[groupBy];
  
  return await tx
    .select({
      groupValue: groupField,
      count: count(),
      vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),
      itemCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.itemCode})`.as('itemCount'),
    })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .where(where)
    .groupBy(groupField)
    .orderBy(desc(count()));
}

/**
 * 공종별 통계 조회
 */
export async function getWorkTypeStats(
  tx: PgTransaction<any, any, any>,
  where?: SQL | undefined
) {
  return await tx
    .select({
      workType: techVendorPossibleItems.workType,
      count: count(),
      vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),
      itemCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.itemCode})`.as('itemCount'),
    })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .where(where)
    .groupBy(techVendorPossibleItems.workType)
    .orderBy(desc(count()));
}

/**
 * 선종별 통계 조회
 */
export async function getShipTypeStats(
  tx: PgTransaction<any, any, any>,
  where?: SQL | undefined
) {
  return await tx
    .select({
      shipTypes: techVendorPossibleItems.shipTypes,
      count: count(),
      vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),
      itemCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.itemCode})`.as('itemCount'),
    })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .where(where)
    .groupBy(techVendorPossibleItems.shipTypes)
    .orderBy(desc(count()));
}

/**
 * 벤더별 통계 조회
 */
export async function getVendorStats(
  tx: PgTransaction<any, any, any>,
  where?: SQL | undefined
) {
  return await tx
    .select({
      vendorId: techVendorPossibleItems.vendorId,
      vendorCode: techVendorPossibleItems.vendorCode,
      vendorName: techVendors.vendorName,
      vendorEmail: techVendorPossibleItems.vendorEmail,
      itemCount: count(),
      distinctItemCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.itemCode})`.as('distinctItemCount'),
      workTypeCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.workType})`.as('workTypeCount'),
      shipTypeCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.shipTypes})`.as('shipTypeCount'),
      latestUpdate: sql<Date>`MAX(${techVendorPossibleItems.updatedAt})`.as('latestUpdate'),
    })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .where(where)
    .groupBy(
      techVendorPossibleItems.vendorId,
      techVendorPossibleItems.vendorCode,
      techVendors.vendorName,
      techVendorPossibleItems.vendorEmail
    )
    .orderBy(desc(count()));
}