// src/lib/vendors/repository.ts

import { and, eq, inArray, count, gt, AnyColumn, SQLWrapper, SQL} from "drizzle-orm";
import { PgTransaction } from "drizzle-orm/pg-core";
import { VendorContact, vendorContacts, vendorItemsView, vendorMaterialsView, vendorPossibleItems, vendorPossibleMaterials, vendors, vendorsWithTypesView, vendorsWithTypesAndMaterialsView, type Vendor } from "@/db/schema/vendors";
import db from '@/db/db';
import { items } from "@/db/schema/items";
import { rfqs,rfqItems, rfqEvaluations, vendorResponses } from "@/db/schema/rfq";
import { sql } from "drizzle-orm";

interface SelectVendorsOptions {
  where?: any;
  orderBy?: any[];
  offset?: number;
  limit?: number;
}
export declare function asc(column: AnyColumn | SQLWrapper): SQL;
export declare function desc(column: AnyColumn | SQLWrapper): SQL;
export type NewVendorContact = typeof vendorContacts.$inferInsert
export type NewVendorItem = typeof vendorPossibleItems.$inferInsert

/**
 * 1) SELECT (목록 조회)
 */
export async function selectVendors(
    tx: PgTransaction<any, any, any>,
  { where, orderBy, offset, limit }: SelectVendorsOptions
) {
  return tx
    .select()
    .from(vendors)
    .where(where ?? undefined)
    .orderBy(...(orderBy ?? []))
    .offset(offset ?? 0)
    .limit(limit ?? 20);
}

/**
 * 2) COUNT
 */
export async function countVendors(
    tx: PgTransaction<any, any, any>,
    where?: any
  ) {
    const res = await tx.select({ count: count() }).from(vendors).where(where);
    return res[0]?.count ?? 0;
  }
  

  export async function selectVendorsWithTypes (
    tx: PgTransaction<any, any, any>,
  { where, orderBy, offset, limit }: SelectVendorsOptions
) {
  return tx
    .select()
    .from(vendorsWithTypesView)
    .where(where ?? undefined)
    .orderBy(...(orderBy ?? []))
    .offset(offset ?? 0)
    .limit(limit ?? 20);
}

/**
 * 2) COUNT
 */
export async function countVendorsWithTypes(
    tx: PgTransaction<any, any, any>,
    where?: any
  ) {
    const res = await tx.select({ count: count() }).from(vendorsWithTypesView).where(where);
    return res[0]?.count ?? 0;
  }

/**
 * 새로운 확장 뷰를 사용하는 조회 함수
 */
export async function selectVendorsWithTypesAndMaterials(
  tx: PgTransaction<any, any, any>,
  { where, orderBy, offset, limit }: SelectVendorsOptions
) {
  return tx
    .select()
    .from(vendorsWithTypesAndMaterialsView)
    .where(where ?? undefined)
    .orderBy(...(orderBy ?? []))
    .offset(offset ?? 0)
    .limit(limit ?? 20);
}

/**
 * 새로운 확장 뷰에 대한 COUNT 함수
 */
export async function countVendorsWithTypesAndMaterials(
    tx: PgTransaction<any, any, any>,
    where?: any
  ) {
    const res = await tx.select({ count: count() }).from(vendorsWithTypesAndMaterialsView).where(where);
    return res[0]?.count ?? 0;
  }
  

/**
 * 3) INSERT (단일 협력업체 생성)
 *    - id/createdAt/updatedAt은 DB default 사용
 *    - 반환값은 "생성된 레코드" 배열 ([newVendor])
 */
export async function insertVendor(
  tx: PgTransaction<any, any, any>,
  data: Omit<Vendor, "id" | "createdAt" | "updatedAt" | "businessSize">
) {
  return tx.insert(vendors).values(data).returning();
}

/**
 * 4) UPDATE (단일 협력업체)
 */
export async function updateVendor(
  tx: PgTransaction<any, any, any>,
  id: string,
  data: Partial<Vendor>
) {
  return tx
    .update(vendors)
    .set(data)
    .where(eq(vendors.id, Number(id)))
    .returning();
}

/**
 * 5) UPDATE (복수 협력업체)
 *    - 여러 개의 id를 받아 일괄 업데이트
 */
export async function updateVendors(
  tx: PgTransaction<any, any, any>,
  ids: string[],
  data: Partial<Vendor>
) {
  const numericIds = ids.map((i) => Number(i));
  return tx
    .update(vendors)
    .set(data)
    .where(inArray(vendors.id, numericIds))
    .returning();
}

/** status 기준 groupBy */
export async function groupByStatus(
    tx: PgTransaction<any, any, any>,
) {
  return tx
    .select({
      status: vendors.status,
      count: count(),
    })
    .from(vendors)
    .groupBy(vendors.status)
    .having(gt(count(), 0));
}


// ID로 사용자 조회
export const getVendorById = async (id: number): Promise<Vendor | null> => {
  const vendorsRes = await db.select().from(vendors).where(eq(vendors.id, id)).execute();
  if (vendorsRes.length === 0) return null;

  const vendor = vendorsRes[0];
  return vendor
};

export const getVendorContactsById = async (id: number): Promise<VendorContact | null> => {
  const contactsRes = await db.select().from(vendorContacts).where(eq(vendorContacts.vendorId, id)).execute();
  if (contactsRes.length === 0) return null;

  const contact = contactsRes[0];
  return contact
};

export const getVendorContactById = async (id: number): Promise<VendorContact | null> => {
  const contactsRes = await db.select().from(vendorContacts).where(eq(vendorContacts.id, id)).execute();
  if (contactsRes.length === 0) return null;

  const contact = contactsRes[0];
  return contact
};

export async function updateVendorContactById(
  tx: PgTransaction<any, any, any>,
  id: number,
  data: Partial<VendorContact>
) {
  return tx
    .update(vendorContacts)
    .set(data)
    .where(eq(vendorContacts.id, id))
    .returning();
}

export async function selectVendorContacts(
  tx: PgTransaction<any, any, any>,
  params: {
    where?: any; // drizzle-orm의 조건식 (and, eq...) 등
    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
    offset?: number;
    limit?: number;
  }
) {
  const { where, orderBy, offset = 0, limit = 10 } = params;

  return tx
    .select()
    .from(vendorContacts)
    .where(where)
    .orderBy(...(orderBy ?? []))
    .offset(offset)
    .limit(limit);
}

export async function countVendorContacts(
  tx: PgTransaction<any, any, any>,
  where?: any
) {
  const res = await tx.select({ count: count() }).from(vendorContacts).where(where);
  return res[0]?.count ?? 0;
}

export async function insertVendorContact(
  tx: PgTransaction<any, any, any>,
  data: NewVendorContact // DB와 동일한 insert 가능한 타입
) {
  // returning() 사용 시 배열로 돌아오므로 [0]만 리턴
  return tx
    .insert(vendorContacts)
    .values(data)
    .returning({ id: vendorContacts.id, createdAt: vendorContacts.createdAt });
}


export async function selectVendorItems(
  tx: PgTransaction<any, any, any>,
  params: {
    where?: any; // drizzle-orm의 조건식 (and, eq...) 등
    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
    offset?: number;
    limit?: number;
  }
) {
  const { where, orderBy, offset = 0, limit = 10 } = params;

  return tx
  .select({
    // vendor_possible_items cols
    vendorItemId: vendorItemsView.vendorItemId,
    vendorId: vendorItemsView.vendorId,
    itemCode: vendorItemsView.itemCode,
    createdAt: vendorItemsView.createdAt,
    updatedAt: vendorItemsView.updatedAt,
    itemName: vendorItemsView.itemName,         
    description: vendorItemsView.description,  
  })
  .from(vendorItemsView)
  .where(where ?? undefined)
  .orderBy(...(orderBy ?? []))
  .offset(offset)
  .limit(limit);
}

export async function countVendorItems(
  tx: PgTransaction<any, any, any>,
  where?: any
) {
  const res = await tx.select({ count: count() }).from(vendorItemsView).where(where);
  return res[0]?.count ?? 0;
}

export async function selectVendorMaterials(
  tx: PgTransaction<any, any, any>,
  params: {
    where?: any; // drizzle-orm의 조건식 (and, eq...) 등
    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
    offset?: number;
    limit?: number;
  }
) {
  const { where, orderBy, offset = 0, limit = 10 } = params;

  return tx
  .select({
    // vendor_possible_items cols
    vendorItemId: vendorMaterialsView.vendorItemId,
    vendorId: vendorMaterialsView.vendorId,
    itemCode: vendorMaterialsView.itemCode,
    createdAt: vendorMaterialsView.createdAt,
    updatedAt: vendorMaterialsView.updatedAt,
    itemName: vendorMaterialsView.itemName,         
    description: vendorMaterialsView.description,  
  })
  .from(vendorMaterialsView)
  .where(where ?? undefined)
  .orderBy(...(orderBy ?? []))
  .offset(offset)
  .limit(limit);
}

export async function countVendorMaterials(
  tx: PgTransaction<any, any, any>,
  where?: any
) {
  const res = await tx.select({ count: count() }).from(vendorMaterialsView).where(where);
  return res[0]?.count ?? 0;
}


export async function insertVendorItem(
  tx: PgTransaction<any, any, any>,
  data: NewVendorItem // DB와 동일한 insert 가능한 타입
) {
  // returning() 사용 시 배열로 돌아오므로 [0]만 리턴
  return tx
    .insert(vendorPossibleItems)
    .values(data)
    .returning({ id: vendorPossibleItems.id, createdAt: vendorPossibleItems.createdAt });
}

export async function insertVendorMaterial(
  tx: PgTransaction<any, any, any>,
  data: NewVendorItem // DB와 동일한 insert 가능한 타입
) {
  // returning() 사용 시 배열로 돌아오므로 [0]만 리턴
  return tx
    .insert(vendorPossibleMaterials)
    .values(data)
    .returning({ id: vendorPossibleMaterials.id, createdAt: vendorPossibleMaterials.createdAt });
}

export async function selectRfqHistory(
  tx: PgTransaction<any, any, any>,
  { where, orderBy, offset, limit }: SelectVendorsOptions
) {
  return tx
      .select({
          // RFQ 기본 정보
          id: rfqs.id,
          rfqCode: rfqs.rfqCode,

          description: rfqs.description,
          dueDate: rfqs.dueDate,
          status: rfqs.status,
          createdAt: rfqs.createdAt,
          
   
          // Item 정보 (집계)
          itemCount: sql<number>`count(distinct ${rfqItems.id})::integer`,
          
          // 평가 정보
          tbeResult: sql<string>`
              (select result from ${rfqEvaluations} 
              where rfq_id = ${rfqs.id} 
              and vendor_id = ${vendorResponses.vendorId} 
              and eval_type = 'TBE' 
              limit 1)`,
          cbeResult: sql<string>`
              (select result from ${rfqEvaluations} 
              where rfq_id = ${rfqs.id} 
              and vendor_id = ${vendorResponses.vendorId} 
              and eval_type = 'CBE' 
              limit 1)`
      })
      .from(rfqs)
      .innerJoin(vendorResponses, eq(rfqs.id, vendorResponses.rfqId))

      .leftJoin(rfqItems, eq(rfqs.id, rfqItems.rfqId))
      .where(where ?? undefined)
      .groupBy(
          rfqs.id,
          rfqs.rfqCode,
      
          rfqs.description,
          rfqs.dueDate,
          rfqs.status,
          rfqs.createdAt,
        
          vendorResponses.vendorId,
     
      )
      .orderBy(...(orderBy ?? []))
      .offset(offset ?? 0)
      .limit(limit ?? 20);
}

export async function countRfqHistory(
  tx: PgTransaction<any, any, any>,
  where?: any
) {
  const [{ count }] = await tx
      .select({
          count: sql<number>`count(distinct ${rfqs.id})::integer`,
      })
      .from(rfqs)
      .innerJoin(vendorResponses, eq(rfqs.id, vendorResponses.rfqId))
      .where(where ?? undefined);

  return count;
}

export const getTaxIdByVendorId = async (id: string): Promise<string | null> => {
  const numericId = Number(id);
  const vendor = await getVendorById(numericId);
  if (!vendor) return null;
  return vendor.taxId;
}