import { eq, desc, count, SQL, sql } from "drizzle-orm";
import { 
  techVendors, 
  techVendorPossibleItems
} from "@/db/schema/techVendors";
import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items";
import type { PgTransaction } from "drizzle-orm/pg-core";

/**
 * 새로운 스키마에 맞는 기술영업 벤더 가능 아이템 목록 조회 (조인 포함)
 * techVendorPossibleItems는 shipbuildingItemId, offshoreTopItemId, offshoreHullItemId 중 하나만 가짐
 */
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,
      shipbuildingItemId: techVendorPossibleItems.shipbuildingItemId,
      offshoreTopItemId: techVendorPossibleItems.offshoreTopItemId,
      offshoreHullItemId: techVendorPossibleItems.offshoreHullItemId,
      createdAt: techVendorPossibleItems.createdAt,
      updatedAt: techVendorPossibleItems.updatedAt,
      // 벤더 정보
      vendorCode: techVendors.vendorCode,
      vendorName: techVendors.vendorName,
      vendorEmail: techVendors.email,
      vendorStatus: techVendors.status,
      techVendorType: techVendors.techVendorType,
      // 조선 아이템 정보 (shipbuildingItemId가 있을 때만)
      shipItemCode: itemShipbuilding.itemCode,
      shipWorkType: itemShipbuilding.workType,
      shipItemList: itemShipbuilding.itemList,
      shipTypes: itemShipbuilding.shipTypes,
      // 해양 TOP 아이템 정보 (offshoreTopItemId가 있을 때만)
      topItemCode: itemOffshoreTop.itemCode,
      topWorkType: itemOffshoreTop.workType,
      topItemList: itemOffshoreTop.itemList,
      topSubItemList: itemOffshoreTop.subItemList,
      // 해양 HULL 아이템 정보 (offshoreHullItemId가 있을 때만)
      hullItemCode: itemOffshoreHull.itemCode,
      hullWorkType: itemOffshoreHull.workType,
      hullItemList: itemOffshoreHull.itemList,
      hullSubItemList: itemOffshoreHull.subItemList,
    })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .leftJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
    .leftJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
    .leftJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.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))
    .leftJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
    .leftJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
    .leftJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id))
    .where(where);
  
  return result.count;
}

/**
 * 공종별 통계 조회 (새로운 스키마 적용)
 */
export async function getWorkTypeStats(
  tx: PgTransaction<any, any, any>,
  where?: SQL | undefined
) {
  // 각 아이템 타입별로 별도 쿼리 실행 후 통합
  const shipStats = await tx
    .select({
      workType: itemShipbuilding.workType,
      count: count(),
      vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),
      itemCount: sql<number>`COUNT(DISTINCT ${itemShipbuilding.itemCode})`.as('itemCount'),
    })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .innerJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
    .where(where)
    .groupBy(itemShipbuilding.workType)
    .orderBy(desc(count()));

  const topStats = await tx
    .select({
      workType: itemOffshoreTop.workType,
      count: count(),
      vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),
      itemCount: sql<number>`COUNT(DISTINCT ${itemOffshoreTop.itemCode})`.as('itemCount'),
    })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .innerJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
    .where(where)
    .groupBy(itemOffshoreTop.workType)
    .orderBy(desc(count()));

  const hullStats = await tx
    .select({
      workType: itemOffshoreHull.workType,
      count: count(),
      vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),
      itemCount: sql<number>`COUNT(DISTINCT ${itemOffshoreHull.itemCode})`.as('itemCount'),
    })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .innerJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id))
    .where(where)
    .groupBy(itemOffshoreHull.workType)
    .orderBy(desc(count()));

  // 결과 통합
  return [...shipStats, ...topStats, ...hullStats];
}

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

/**
 * 벤더별 통계 조회
 */
export async function getVendorStats(
  tx: PgTransaction<any, any, any>,
  where?: SQL | undefined
) {
  return await tx
    .select({
      vendorId: techVendorPossibleItems.vendorId,
      vendorCode: techVendors.vendorCode,
      vendorName: techVendors.vendorName,
      vendorEmail: techVendors.email,
      vendorStatus: techVendors.status,
      itemCount: count(),
      latestUpdate: sql<Date>`MAX(${techVendorPossibleItems.updatedAt})`.as('latestUpdate'),
    })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .leftJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
    .leftJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
    .leftJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id))
    .where(where)
    .groupBy(
      techVendorPossibleItems.vendorId,
      techVendors.vendorCode,
      techVendors.vendorName,
      techVendors.email,
      techVendors.status
    )
    .orderBy(desc(count()));
}

/**
 * 아이템 타입별 통계 조회 (조선, 해양TOP, 해양HULL)
 */
export async function getItemTypeStats(
  tx: PgTransaction<any, any, any>,
  where?: SQL | undefined
) {
  const [shipCount] = await tx
    .select({ count: count() })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .innerJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
    .where(where);

  const [topCount] = await tx
    .select({ count: count() })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .innerJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
    .where(where);

  const [hullCount] = await tx
    .select({ count: count() })
    .from(techVendorPossibleItems)
    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
    .innerJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id))
    .where(where);

  return [
    { itemType: "조선", count: shipCount.count },
    { itemType: "해양TOP", count: topCount.count },
    { itemType: "해양HULL", count: hullCount.count },
  ];
}