/* eslint-disable @typescript-eslint/no-explicit-any */

import {
  techSalesRfqs,
  techSalesVendorQuotations,
  vendors,
  users,
  itemShipbuilding
} from "@/db/schema";
import {
    asc,
    desc, count, SQL, sql
} from "drizzle-orm";
import { PgTransaction } from "drizzle-orm/pg-core";


export type NewTechSalesRfq = typeof techSalesRfqs.$inferInsert;
/**
 * 기술영업 RFQ 생성
 * ID 및 생성일 리턴
 */
export async function insertTechSalesRfq(
  tx: PgTransaction<any, any, any>,
  data: NewTechSalesRfq
) {
  return tx
  .insert(techSalesRfqs)
  .values(data)
  .returning({ id: techSalesRfqs.id, createdAt: techSalesRfqs.createdAt });
}

/**
 * 단건/복수 조회 시 공통으로 사용 가능한 SELECT 함수 예시
 *  - 트랜잭션(tx)을 받아서 사용하도록 구현
 */
export async function selectTechSalesRfqs(
  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()
    .from(techSalesRfqs)
    .where(where ?? undefined)
    .orderBy(...(orderBy ?? []))
    .offset(offset)
    .limit(limit);
}
/** 총 개수 count */
export async function countTechSalesRfqs(
  tx: PgTransaction<any, any, any>,
  where?: any
) {
  const res = await tx.select({ count: count() }).from(techSalesRfqs).where(where);
  return res[0]?.count ?? 0;
}

/**
 * RFQ 정보 직접 조인 조회 (뷰 대신 테이블 조인 사용) 
 */
export async function selectTechSalesRfqsWithJoin(
  tx: PgTransaction<any, any, any>,
  params: {
    where?: any;
    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[];
    offset?: number;
    limit?: number;
  }
) {
  const { where, orderBy, offset = 0, limit = 10 } = params;

  // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행
  const query = tx.select({
    // RFQ 기본 정보
    id: techSalesRfqs.id,
    rfqCode: techSalesRfqs.rfqCode,
    itemId: techSalesRfqs.itemId,
    itemName: itemShipbuilding.itemList,
    materialCode: techSalesRfqs.materialCode,
    
    // 날짜 및 상태 정보
    dueDate: techSalesRfqs.dueDate,
    rfqSendDate: techSalesRfqs.rfqSendDate,
    status: techSalesRfqs.status,
    
    // 담당자 및 비고
    picCode: techSalesRfqs.picCode,
    remark: techSalesRfqs.remark,
    cancelReason: techSalesRfqs.cancelReason,
    
    // 생성/수정 정보
    createdAt: techSalesRfqs.createdAt,
    updatedAt: techSalesRfqs.updatedAt,
    
    // 사용자 정보
    createdBy: techSalesRfqs.createdBy,
    createdByName: sql<string>`created_user.name`,
    updatedBy: techSalesRfqs.updatedBy,
    updatedByName: sql<string>`updated_user.name`,
    sentBy: techSalesRfqs.sentBy,
    sentByName: sql<string | null>`sent_user.name`,
    
    // 프로젝트 정보 (스냅샷)
    projectSnapshot: techSalesRfqs.projectSnapshot,
    seriesSnapshot: techSalesRfqs.seriesSnapshot,
    
    // 프로젝트 핵심 정보
    pspid: sql<string>`${techSalesRfqs.projectSnapshot}->>'pspid'`,
    projNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'projNm'`,
    sector: sql<string>`${techSalesRfqs.projectSnapshot}->>'sector'`,
    projMsrm: sql<number>`(${techSalesRfqs.projectSnapshot}->>'projMsrm')::int`,
    ptypeNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'ptypeNm'`,
    
    // 첨부파일 개수
    attachmentCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_attachments 
      WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
    )`,
    
    // 벤더 견적 개수
    quotationCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_vendor_quotations 
      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
    )`,
  })
  .from(techSalesRfqs)
  .leftJoin(itemShipbuilding, sql`split_part(${techSalesRfqs.materialCode}, ',', 1) = ${itemShipbuilding.itemCode}`)
  .leftJoin(sql`${users} AS created_user`, sql`${techSalesRfqs.createdBy} = created_user.id`)
  .leftJoin(sql`${users} AS updated_user`, sql`${techSalesRfqs.updatedBy} = updated_user.id`)
  .leftJoin(sql`${users} AS sent_user`, sql`${techSalesRfqs.sentBy} = sent_user.id`);

  // where 조건 적용
  const queryWithWhere = where ? query.where(where) : query;
  
  // orderBy 적용
  const queryWithOrderBy = orderBy?.length 
    ? queryWithWhere.orderBy(...orderBy)
    : queryWithWhere.orderBy(desc(techSalesRfqs.createdAt));
  
  // offset과 limit 적용 후 실행
  return queryWithOrderBy.offset(offset).limit(limit);
}

/**
 * RFQ 개수 직접 조회 (뷰 대신 테이블 조인 사용)
 */
export async function countTechSalesRfqsWithJoin(
  tx: PgTransaction<any, any, any>,
  where?: any
) {
  const res = await tx
    .select({ count: count() })
    .from(techSalesRfqs)
    .leftJoin(itemShipbuilding, sql`split_part(${techSalesRfqs.materialCode}, ',', 1) = ${itemShipbuilding.itemCode}`)
    .where(where ?? undefined);
  return res[0]?.count ?? 0;
}

/**
 * 벤더 견적서 직접 조인 조회 (뷰 대신 테이블 조인 사용)
 */
export async function selectTechSalesVendorQuotationsWithJoin(
  tx: PgTransaction<any, any, any>,
  params: {
    where?: any;
    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[];
    offset?: number;
    limit?: number;
  }
) {
  const { where, orderBy, offset = 0, limit = 10 } = params;

  // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행
  const query = tx.select({
    // 견적 기본 정보
    id: techSalesVendorQuotations.id,
    rfqId: techSalesVendorQuotations.rfqId,
    rfqCode: techSalesRfqs.rfqCode,
    vendorId: techSalesVendorQuotations.vendorId,
    vendorName: vendors.vendorName,
    vendorCode: vendors.vendorCode,
    
    // 견적 상세 정보
    totalPrice: techSalesVendorQuotations.totalPrice,
    currency: techSalesVendorQuotations.currency,
    validUntil: techSalesVendorQuotations.validUntil,
    status: techSalesVendorQuotations.status,
    remark: techSalesVendorQuotations.remark,
    rejectionReason: techSalesVendorQuotations.rejectionReason,
    
    // 날짜 정보
    submittedAt: techSalesVendorQuotations.submittedAt,
    acceptedAt: techSalesVendorQuotations.acceptedAt,
    createdAt: techSalesVendorQuotations.createdAt,
    updatedAt: techSalesVendorQuotations.updatedAt,
    
    // 생성/수정 사용자
    createdBy: techSalesVendorQuotations.createdBy,
    createdByName: sql<string | null>`created_user.name`,
    updatedBy: techSalesVendorQuotations.updatedBy,
    updatedByName: sql<string | null>`updated_user.name`,
    
    // 프로젝트 정보
    materialCode: techSalesRfqs.materialCode,
    itemId: techSalesRfqs.itemId,
    itemName: itemShipbuilding.itemList,
    
    // 프로젝트 핵심 정보
    pspid: sql<string>`${techSalesRfqs.projectSnapshot}->>'pspid'`,
    projNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'projNm'`,
    sector: sql<string>`${techSalesRfqs.projectSnapshot}->>'sector'`,
    
    // 첨부파일 개수
    attachmentCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_attachments 
      WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
    )`,
  })
  .from(techSalesVendorQuotations)
  .leftJoin(techSalesRfqs, sql`${techSalesVendorQuotations.rfqId} = ${techSalesRfqs.id}`)
  .leftJoin(vendors, sql`${techSalesVendorQuotations.vendorId} = ${vendors.id}`)
  .leftJoin(itemShipbuilding, sql`split_part(${techSalesRfqs.materialCode}, ',', 1) = ${itemShipbuilding.itemCode}`)
  .leftJoin(sql`${users} AS created_user`, sql`${techSalesVendorQuotations.createdBy} = created_user.id`)
  .leftJoin(sql`${users} AS updated_user`, sql`${techSalesVendorQuotations.updatedBy} = updated_user.id`);

  // where 조건 적용
  const queryWithWhere = where ? query.where(where) : query;
  
  // orderBy 적용
  const queryWithOrderBy = orderBy?.length 
    ? queryWithWhere.orderBy(...orderBy)
    : queryWithWhere.orderBy(desc(techSalesVendorQuotations.createdAt));
  
  // offset과 limit 적용 후 실행
  return queryWithOrderBy.offset(offset).limit(limit);
}

/**
 * 벤더 견적서 개수 직접 조회 (뷰 대신 테이블 조인 사용)
 */
export async function countTechSalesVendorQuotationsWithJoin(
  tx: PgTransaction<any, any, any>,
  where?: any
) {
  const res = await tx
    .select({ count: count() })
    .from(techSalesVendorQuotations)
    .leftJoin(techSalesRfqs, sql`${techSalesVendorQuotations.rfqId} = ${techSalesRfqs.id}`)
    .leftJoin(vendors, sql`${techSalesVendorQuotations.vendorId} = ${vendors.id}`)
    .leftJoin(itemShipbuilding, sql`split_part(${techSalesRfqs.materialCode}, ',', 1) = ${itemShipbuilding.itemCode}`)
    .where(where ?? undefined);
  return res[0]?.count ?? 0;
}

/**
 * RFQ 대시보드 데이터 직접 조인 조회 (뷰 대신 테이블 조인 사용)
 */
export async function selectTechSalesDashboardWithJoin(
  tx: PgTransaction<any, any, any>,
  params: {
    where?: any;
    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[];
    offset?: number;
    limit?: number;
  }
) {
  const { where, orderBy, offset = 0, limit = 10 } = params;

  // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행
  const query = tx.select({
    // RFQ 기본 정보
    id: techSalesRfqs.id,
    rfqCode: techSalesRfqs.rfqCode,
    status: techSalesRfqs.status,
    dueDate: techSalesRfqs.dueDate,
    rfqSendDate: techSalesRfqs.rfqSendDate,
    materialCode: techSalesRfqs.materialCode,
    
    // 아이템 정보
    itemId: techSalesRfqs.itemId,
    itemName: itemShipbuilding.itemList,
    
    // 프로젝트 정보
    pspid: sql<string>`${techSalesRfqs.projectSnapshot}->>'pspid'`,
    projNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'projNm'`,
    sector: sql<string>`${techSalesRfqs.projectSnapshot}->>'sector'`,
    projMsrm: sql<number>`(${techSalesRfqs.projectSnapshot}->>'projMsrm')::int`,
    ptypeNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'ptypeNm'`,
    
    // 벤더 견적 통계
    vendorCount: sql<number>`(
      SELECT COUNT(DISTINCT vendor_id) 
      FROM tech_sales_vendor_quotations 
      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
    )`,
    
    quotationCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_vendor_quotations 
      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
    )`,
    
    submittedQuotationCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_vendor_quotations 
      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
      AND tech_sales_vendor_quotations.status = 'Submitted'
    )`,
    
    minPrice: sql<string | null>`(
      SELECT MIN(total_price) 
      FROM tech_sales_vendor_quotations 
      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
      AND tech_sales_vendor_quotations.status = 'Submitted'
    )`,
    
    maxPrice: sql<string | null>`(
      SELECT MAX(total_price) 
      FROM tech_sales_vendor_quotations 
      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
      AND tech_sales_vendor_quotations.status = 'Submitted'
    )`,
    
    avgPrice: sql<string | null>`(
      SELECT AVG(total_price) 
      FROM tech_sales_vendor_quotations 
      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
      AND tech_sales_vendor_quotations.status = 'Submitted'
    )`,
    
    // 첨부파일 통계
    attachmentCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_attachments 
      WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
    )`,
    
    // 코멘트 통계
    commentCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_rfq_comments 
      WHERE tech_sales_rfq_comments.rfq_id = ${techSalesRfqs.id}
    )`,
    
    unreadCommentCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_rfq_comments 
      WHERE tech_sales_rfq_comments.rfq_id = ${techSalesRfqs.id}
      AND tech_sales_rfq_comments.is_read = false
    )`,
    
    // 생성/수정 정보
    createdAt: techSalesRfqs.createdAt,
    updatedAt: techSalesRfqs.updatedAt,
    createdByName: sql<string>`created_user.name`,
  })
  .from(techSalesRfqs)
  .leftJoin(itemShipbuilding, sql`split_part(${techSalesRfqs.materialCode}, ',', 1) = ${itemShipbuilding.itemCode}`)
  .leftJoin(sql`${users} AS created_user`, sql`${techSalesRfqs.createdBy} = created_user.id`);

  // where 조건 적용
  const queryWithWhere = where ? query.where(where) : query;
  
  // orderBy 적용
  const queryWithOrderBy = orderBy?.length 
    ? queryWithWhere.orderBy(...orderBy)
    : queryWithWhere.orderBy(desc(techSalesRfqs.updatedAt));
  
  // offset과 limit 적용 후 실행
  return queryWithOrderBy.offset(offset).limit(limit);
}