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

import {
  techSalesRfqs,
  techSalesVendorQuotations,
  users,
  biddingProjects
} from "@/db/schema";
import { techVendors } from "@/db/schema/techVendors";
import {
    asc,
    desc, count, SQL, sql, eq
} 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 조회 with 조인 (Repository)
 */
export async function selectTechSalesRfqsWithJoin(
  tx: PgTransaction<any, any, any>,
  options: {
    where?: SQL;
    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[];
    offset?: number;
    limit?: number;
    rfqType?: "SHIP" | "TOP" | "HULL";
  }
) {
  const { where, orderBy, offset = 0, limit = 10, rfqType } = options;

  // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행
  let query = tx.select({
    // RFQ 기본 정보
    id: techSalesRfqs.id,
    rfqCode: techSalesRfqs.rfqCode,
    rfqType: techSalesRfqs.rfqType,
    biddingProjectId: techSalesRfqs.biddingProjectId,
    materialCode: techSalesRfqs.materialCode,
    
    // 날짜 및 상태 정보
    dueDate: techSalesRfqs.dueDate,
    rfqSendDate: techSalesRfqs.rfqSendDate,
    status: techSalesRfqs.status,
    
    // 담당자 및 비고
    picCode: techSalesRfqs.picCode,
    remark: techSalesRfqs.remark,
    cancelReason: techSalesRfqs.cancelReason,
    description: techSalesRfqs.description,
    
    // 생성/수정 정보
    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`,
    
    // 프로젝트 정보 (조인)
    pspid: biddingProjects.pspid,
    projNm: biddingProjects.projNm,
    sector: biddingProjects.sector,
    projMsrm: biddingProjects.projMsrm,
    ptypeNm: biddingProjects.ptypeNm,
    
    // 첨부파일 개수 (타입별로 분리)
    attachmentCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_attachments 
      WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
      AND tech_sales_attachments.attachment_type = 'RFQ_COMMON'
    )`,
    hasTbeAttachments: sql<boolean>`(
      SELECT CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END
      FROM tech_sales_attachments 
      WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
      AND tech_sales_attachments.attachment_type = 'TBE_RESULT'
    )`,
    hasCbeAttachments: sql<boolean>`(
      SELECT CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END
      FROM tech_sales_attachments 
      WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
      AND tech_sales_attachments.attachment_type = 'CBE_RESULT'
    )`,
    
    // 벤더 견적 개수
    quotationCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_vendor_quotations 
      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
    )`,

    // 아이템 개수
    itemCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_rfq_items 
      WHERE tech_sales_rfq_items.rfq_id = ${techSalesRfqs.id}
    )`,

    // WorkTypes aggregation - RFQ에 연결된 모든 아이템들의 workType을 콤마로 구분하여 반환
    workTypes: sql<string>`(
      SELECT STRING_AGG(DISTINCT 
        CASE 
          WHEN tri.item_type = 'SHIP' THEN ship.work_type
          WHEN tri.item_type = 'TOP' THEN top.work_type  
          WHEN tri.item_type = 'HULL' THEN hull.work_type
          ELSE NULL
        END, ', '
      )
      FROM tech_sales_rfq_items tri
      LEFT JOIN item_shipbuilding ship ON tri.item_shipbuilding_id = ship.id AND tri.item_type = 'SHIP'
      LEFT JOIN item_offshore_top top ON tri.item_offshore_top_id = top.id AND tri.item_type = 'TOP'  
      LEFT JOIN item_offshore_hull hull ON tri.item_offshore_hull_id = hull.id AND tri.item_type = 'HULL'
      WHERE tri.rfq_id = ${techSalesRfqs.id}
      AND (ship.work_type IS NOT NULL OR top.work_type IS NOT NULL OR hull.work_type IS NOT NULL)
    )`,
  })
  .from(techSalesRfqs)
  
  // 프로젝트 정보 조인 추가
  .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id))
  
  // 사용자 정보 조인
  .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`)
  
;

  // rfqType 필터링
  const conditions = [];
  if (rfqType) {
    conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`);
  }
  if (where) {
    conditions.push(where);
  }

  if (conditions.length > 0) {
    query = query.where(sql`${sql.join(conditions, sql` AND `)}`);
  }
  
  // orderBy 적용
  const queryWithOrderBy = orderBy?.length 
    ? query.orderBy(...orderBy)
    : query.orderBy(desc(techSalesRfqs.createdAt));
  
  // offset과 limit 적용 후 실행
  return queryWithOrderBy.offset(offset).limit(limit);
}

/**
 * RFQ 개수 직접 조회 (뷰 대신 테이블 조인 사용)
 */
export async function countTechSalesRfqsWithJoin(
  tx: PgTransaction<any, any, any>,
  where?: any,
  rfqType?: "SHIP" | "TOP" | "HULL"
) {
  const conditions = [];
  if (rfqType) {
    conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`);
  }
  if (where) {
    conditions.push(where);
  }

  const finalWhere = conditions.length > 0 ? sql`${sql.join(conditions, sql` AND `)}` : undefined;

  const res = await tx
    .select({ count: count() })
    .from(techSalesRfqs)
    .where(finalWhere);
  return res[0]?.count ?? 0;
}

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

  // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행
  let query = tx.select({
    // 견적 기본 정보
    id: techSalesVendorQuotations.id,
    rfqId: techSalesVendorQuotations.rfqId,
    rfqCode: techSalesRfqs.rfqCode,
    rfqType: techSalesRfqs.rfqType,
    vendorId: techSalesVendorQuotations.vendorId,
    vendorName: techVendors.vendorName,
    vendorCode: techVendors.vendorCode,
    
    // 견적 상세 정보
    totalPrice: techSalesVendorQuotations.totalPrice,
    currency: techSalesVendorQuotations.currency,
    validUntil: techSalesVendorQuotations.validUntil,
    status: techSalesVendorQuotations.status,
    remark: techSalesVendorQuotations.remark,
    quotationVersion: techSalesVendorQuotations.quotationVersion,
    rejectionReason: techSalesVendorQuotations.rejectionReason,
    vendorFlags: techSalesVendorQuotations.vendorFlags,

    // 날짜 정보
    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,
    
    // 프로젝트 핵심 정보 - null 체크 추가
    pspid: techSalesRfqs.biddingProjectId,
    projNm: biddingProjects.projNm,
    sector: biddingProjects.sector,
    
    // 첨부파일 개수
    attachmentCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_attachments 
      WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
    )`,

    // 견적서 첨부파일 개수
    quotationAttachmentCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_vendor_quotation_attachments 
      WHERE tech_sales_vendor_quotation_attachments.quotation_id = ${techSalesVendorQuotations.id}
    )`,

    // RFQ 아이템 개수
    itemCount: sql<number>`(
      SELECT COUNT(*) 
      FROM tech_sales_rfq_items 
      WHERE tech_sales_rfq_items.rfq_id = ${techSalesRfqs.id}
    )`,

  })
  .from(techSalesVendorQuotations)
  .leftJoin(techSalesRfqs, sql`${techSalesVendorQuotations.rfqId} = ${techSalesRfqs.id}`)
  .leftJoin(techVendors, sql`${techSalesVendorQuotations.vendorId} = ${techVendors.id}`)
    // 프로젝트 정보 조인 추가
    .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id))
  .leftJoin(sql`${users} AS created_user`, sql`${techSalesVendorQuotations.createdBy} = created_user.id`)
  .leftJoin(sql`${users} AS updated_user`, sql`${techSalesVendorQuotations.updatedBy} = updated_user.id`);

  // rfqType 필터링
  const conditions = [];
  if (rfqType) {
    conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`);
  }
  if (where) {
    conditions.push(where);
  }

  if (conditions.length > 0) {
    query = query.where(sql`${sql.join(conditions, sql` AND `)}`);
  }
  
  // orderBy 적용
  const queryWithOrderBy = orderBy?.length 
    ? query.orderBy(...orderBy)
    : query.orderBy(desc(techSalesVendorQuotations.createdAt));
  
  // offset과 limit 적용 후 실행
  return queryWithOrderBy.offset(offset).limit(limit);
}

/**
 * 벤더 견적서 개수 직접 조회 (뷰 대신 테이블 조인 사용)
 */
export async function countTechSalesVendorQuotationsWithJoin(
  tx: PgTransaction<any, any, any>,
  where?: any,
  rfqType?: "SHIP" | "TOP" | "HULL"
) {
  const conditions = [];
  if (rfqType) {
    conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`);
  }
  if (where) {
    conditions.push(where);
  }

  const finalWhere = conditions.length > 0 ? sql`${sql.join(conditions, sql` AND `)}` : undefined;

  const res = await tx
    .select({ count: count() })
    .from(techSalesVendorQuotations)
    .leftJoin(techSalesRfqs, sql`${techSalesVendorQuotations.rfqId} = ${techSalesRfqs.id}`)
    .leftJoin(techVendors, sql`${techSalesVendorQuotations.vendorId} = ${techVendors.id}`)
    .where(finalWhere);
  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;
    rfqType?: "SHIP" | "TOP" | "HULL";
  }
) {
  const { where, orderBy, offset = 0, limit = 10, rfqType } = params;

  // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행
  let query = tx.select({
    // RFQ 기본 정보
    id: techSalesRfqs.id,
    rfqCode: techSalesRfqs.rfqCode,
    rfqType: techSalesRfqs.rfqType,
    status: techSalesRfqs.status,
    dueDate: techSalesRfqs.dueDate,
    rfqSendDate: techSalesRfqs.rfqSendDate,
    materialCode: techSalesRfqs.materialCode,

    // 프로젝트 정보 - null 체크 추가
    pspid: techSalesRfqs.biddingProjectId,
    projNm: biddingProjects.projNm,
    sector: biddingProjects.sector,
    projMsrm: biddingProjects.projMsrm,
    ptypeNm: biddingProjects.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`,

    // 아이템 정보 - rfqType에 따라 다른 테이블에서 조회
    itemName: sql<string>`
      CASE 
        WHEN ${techSalesRfqs.rfqType} = 'SHIP' THEN ship_items.item_list
        WHEN ${techSalesRfqs.rfqType} = 'TOP' THEN top_items.item_list
        WHEN ${techSalesRfqs.rfqType} = 'HULL' THEN hull_items.item_list
        ELSE NULL
      END
    `,
  })
  .from(techSalesRfqs)
  .leftJoin(sql`${users} AS created_user`, sql`${techSalesRfqs.createdBy} = created_user.id`)
  
  // 아이템 정보 조인
  .leftJoin(
    sql`(
      SELECT DISTINCT ON (rfq_id) 
        tri.rfq_id,
        ship.item_list
      FROM tech_sales_rfq_items tri
      LEFT JOIN item_shipbuilding ship ON tri.item_shipbuilding_id = ship.id
      WHERE tri.item_type = 'SHIP'
      ORDER BY rfq_id, tri.id
    ) AS ship_items`, 
    sql`ship_items.rfq_id = ${techSalesRfqs.id} AND ${techSalesRfqs.rfqType} = 'SHIP'`
  )
  .leftJoin(
    sql`(
      SELECT DISTINCT ON (rfq_id) 
        tri.rfq_id,
        top.item_list
      FROM tech_sales_rfq_items tri
      LEFT JOIN item_offshore_top top ON tri.item_offshore_top_id = top.id
      WHERE tri.item_type = 'TOP'
      ORDER BY rfq_id, tri.id
    ) AS top_items`, 
    sql`top_items.rfq_id = ${techSalesRfqs.id} AND ${techSalesRfqs.rfqType} = 'TOP'`
  )
  .leftJoin(
    sql`(
      SELECT DISTINCT ON (rfq_id) 
        tri.rfq_id,
        hull.item_list
      FROM tech_sales_rfq_items tri
      LEFT JOIN item_offshore_hull hull ON tri.item_offshore_hull_id = hull.id
      WHERE tri.item_type = 'HULL'
      ORDER BY rfq_id, tri.id
    ) AS hull_items`, 
    sql`hull_items.rfq_id = ${techSalesRfqs.id} AND ${techSalesRfqs.rfqType} = 'HULL'`
  );

  // rfqType 필터링
  const conditions = [];
  if (rfqType) {
    conditions.push(sql`${techSalesRfqs.rfqType} = ${rfqType}`);
  }
  if (where) {
    conditions.push(where);
  }

  if (conditions.length > 0) {
    query = query.where(sql`${sql.join(conditions, sql` AND `)}`);
  }

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

/**
 * 단일 벤더 견적서 직접 조인 조회 (단일 견적서 상세용)
 */
export async function selectSingleTechSalesVendorQuotationWithJoin(
  tx: PgTransaction<any, any, any>,
  quotationId: number
) {
  const result = await tx.select({
    // 견적 기본 정보
    id: techSalesVendorQuotations.id,
    rfqId: techSalesVendorQuotations.rfqId,
    vendorId: techSalesVendorQuotations.vendorId,
    
    // 견적 상세 정보
    quotationCode: techSalesVendorQuotations.quotationCode,
    quotationVersion: techSalesVendorQuotations.quotationVersion,
    totalPrice: techSalesVendorQuotations.totalPrice,
    currency: techSalesVendorQuotations.currency,
    validUntil: techSalesVendorQuotations.validUntil,
    status: techSalesVendorQuotations.status,
    remark: techSalesVendorQuotations.remark,
    rejectionReason: techSalesVendorQuotations.rejectionReason,
    vendorFlags: techSalesVendorQuotations.vendorFlags, // 벤더 구분자 정보 추가

    // 날짜 정보
    submittedAt: techSalesVendorQuotations.submittedAt,
    acceptedAt: techSalesVendorQuotations.acceptedAt,
    createdAt: techSalesVendorQuotations.createdAt,
    updatedAt: techSalesVendorQuotations.updatedAt,
    
    // 생성/수정 사용자
    createdBy: techSalesVendorQuotations.createdBy,
    updatedBy: techSalesVendorQuotations.updatedBy,
    
    // RFQ 정보
    rfqCode: techSalesRfqs.rfqCode,
    rfqType: techSalesRfqs.rfqType,
    rfqStatus: techSalesRfqs.status,
    dueDate: techSalesRfqs.dueDate,
    rfqSendDate: techSalesRfqs.rfqSendDate,
    materialCode: techSalesRfqs.materialCode,
    description: techSalesRfqs.description,
    rfqRemark: techSalesRfqs.remark,
    picCode: techSalesRfqs.picCode,
    
    // RFQ 생성자 정보
    rfqCreatedBy: techSalesRfqs.createdBy,
    rfqCreatedByName: sql<string | null>`rfq_created_user.name`,
    rfqCreatedByEmail: sql<string | null>`rfq_created_user.email`,
    
    // 벤더 정보
    vendorName: techVendors.vendorName,
    vendorCode: techVendors.vendorCode,
    vendorCountry: techVendors.country,
    vendorEmail: techVendors.email,
    vendorPhone: techVendors.phone,
    
    // 프로젝트 정보
    biddingProjectId: techSalesRfqs.biddingProjectId,
    pspid: biddingProjects.pspid,
    projNm: biddingProjects.projNm,
    sector: biddingProjects.sector,
    projMsrm: biddingProjects.projMsrm,
    ptypeNm: biddingProjects.ptypeNm,
    
  })
  .from(techSalesVendorQuotations)
  .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id))
  .leftJoin(techVendors, eq(techSalesVendorQuotations.vendorId, techVendors.id))
  .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id))
  .leftJoin(sql`${users} AS rfq_created_user`, sql`${techSalesRfqs.createdBy} = rfq_created_user.id`)
  .where(eq(techSalesVendorQuotations.id, quotationId));

  return result[0] || null;
}