// src/lib/tasks/repository.ts
import db from "@/db/db";
import { rfqItems, rfqs, rfqsView, type Rfq,VendorResponse, vendorResponses, RfqViewWithItems } from "@/db/schema/rfq";
import {
  eq,
  inArray,
  asc,
  desc,
  count,
  gt,
} from "drizzle-orm";
import { PgTransaction } from "drizzle-orm/pg-core";
export type NewRfq = typeof rfqs.$inferInsert
export type NewRfqItem = typeof rfqItems.$inferInsert

/**
 * 단건/복수 조회 시 공통으로 사용 가능한 SELECT 함수 예시
 *  - 트랜잭션(tx)을 받아서 사용하도록 구현
 */
export async function selectRfqs(
  tx: PgTransaction<any, any, any>,
  params: {
    where?: any;
    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
    offset?: number;
    limit?: number;
  }
) {
  const { where, orderBy, offset = 0, limit = 10 } = params;

  return tx
    .select({
      rfqId: rfqsView.id,
      id: rfqsView.id,
      rfqCode: rfqsView.rfqCode,
      description: rfqsView.description,
      projectCode: rfqsView.projectCode,
      projectName: rfqsView.projectName,
      dueDate: rfqsView.dueDate,
      status: rfqsView.status,
      // createdBy → user 이메일
      createdBy: rfqsView.createdBy,          // still the numeric user ID
      createdByEmail: rfqsView.userEmail,        // string

      createdAt: rfqsView.createdAt,
      updatedAt: rfqsView.updatedAt,
      // ====================
      // 1) itemCount via subselect
      // ====================
      itemCount:rfqsView.itemCount,
      attachCount: rfqsView.attachmentCount,

      // user info
      // userId: users.id,
      userEmail: rfqsView.userEmail,
      userName: rfqsView.userName,
    })
    .from(rfqsView)
    .where(where ?? undefined)
    .orderBy(...(orderBy ?? []))
    .offset(offset)
    .limit(limit);
}
/** 총 개수 count */
export async function countRfqs(
  tx: PgTransaction<any, any, any>,
  where?: any
) {
  const res = await tx.select({ count: count() }).from(rfqsView).where(where);
  return res[0]?.count ?? 0;
}

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

/** 복수 Insert 예시 */
export async function insertRfqs(
  tx: PgTransaction<any, any, any>,
  data: Rfq[]
) {
  return tx.insert(rfqs).values(data).onConflictDoNothing();
}

/** 단건 삭제 */
export async function deleteRfqById(
  tx: PgTransaction<any, any, any>,
  rfqId: number
) {
  return tx.delete(rfqs).where(eq(rfqs.id, rfqId));
}

/** 복수 삭제 */
export async function deleteRfqsByIds(
  tx: PgTransaction<any, any, any>,
  ids: number[]
) {
  return tx.delete(rfqs).where(inArray(rfqs.id, ids));
}

/** 전체 삭제 */
export async function deleteAllRfqs(
  tx: PgTransaction<any, any, any>,
) {
  return tx.delete(rfqs);
}

/** 단건 업데이트 */
export async function updateRfq(
  tx: PgTransaction<any, any, any>,
  rfqId: number,
  data: Partial<Rfq>
) {
  return tx
    .update(rfqs)
    .set(data)
    .where(eq(rfqs.id, rfqId))
    .returning({ status: rfqs.status });
}

// /** 복수 업데이트 */
export async function updateRfqs(
  tx: PgTransaction<any, any, any>,
  ids: number[],
  data: Partial<Rfq>
) {
  return tx
    .update(rfqs)
    .set(data)
    .where(inArray(rfqs.id, ids))
    .returning({ status: rfqs.status, dueDate: rfqs.dueDate });
}


// 모든 task 조회
export const getAllRfqs = async (): Promise<Rfq[]> => {
  const datas = await db.select().from(rfqs).execute();
  return datas
};


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

export async function insertRfqItem(
  tx: PgTransaction<any, any, any>,
  data: NewRfqItem
) {
  return tx.insert(rfqItems).values(data).returning();
}

export const getRfqById = async (id: number): Promise<RfqViewWithItems | null> => {
  // 1) RFQ 단건 조회
  const rfqsRes = await db
    .select()
    .from(rfqsView)
    .where(eq(rfqsView.id, id))
    .limit(1);

  if (rfqsRes.length === 0) return null;
  const rfqRow = rfqsRes[0];

  // 2) 해당 RFQ 아이템 목록 조회
  const itemsRes = await db
    .select()
    .from(rfqItems)
    .where(eq(rfqItems.rfqId, id));

  // itemsRes: RfqItem[]

  // 3) RfqWithItems 형태로 반환
  const result: RfqViewWithItems = {
    ...rfqRow,
    lines: itemsRes,
  };

  return result;
};

/** 단건 업데이트 */
export async function updateRfqVendor(
  tx: PgTransaction<any, any, any>,
  rfqVendorId: number,
  data: Partial<VendorResponse>
) {
  return tx
    .update(vendorResponses)
    .set(data)
    .where(eq(vendorResponses.id, rfqVendorId))
    .returning({ status: vendorResponses.responseStatus });
}

/** 복수 업데이트 */
export async function updateRfqVendors(
  tx: PgTransaction<any, any, any>,
  ids: number[],
  data: Partial<VendorResponse>
) {
  return tx
    .update(vendorResponses)
    .set(data)
    .where(inArray(vendorResponses.id, ids))
    .returning({ status: vendorResponses.responseStatus });
}