import { foreignKey, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check } from "drizzle-orm/pg-core";
import { eq, sql, relations } from "drizzle-orm";
import { projects } from "./projects";
import { users } from "./users";
import { vendors } from "./vendors";

export const procurementRfqs = pgTable(
  "procurement_rfqs",
  {
    id: serial("id").primaryKey(),

    // RFQ 고유 코드
    rfqCode: varchar("rfq_code", { length: 50 }).unique(), // ex) "RFQ-2025-001"

    // 프로젝트: ECC RFQ는 프로젝트 테이블과 1:N 관계를 가져야 함
    // WHY?: 여러 프로젝트 혹은 여러 시리즈의 동일 품목을 PR로 묶어 올리기 때문
    projectId: varchar("project_id", { length: 1000 }),

    // SS, II, null 값을 가질 수 있음.
    // SS = 시리즈 통합, II = 품목 통합, 공란 = 통합 없음
    series: varchar("series", { length: 50 }),

    // 자재코드, 자재명: ECC RFQ는 자재코드, 자재명을 가지지 않음
    // WHY?: 여러 프로젝트 혹은 여러 시리즈의 동일 품목을 PR로 묶어 올리기 때문
    // 아래 컬럼은 대표 자재코드, 대표 자재명으로 사용
    itemCode: varchar("item_code", { length: 100 }),
    itemName: varchar("item_name", { length: 255 }),

    dueDate: date("due_date", { mode: "date" })
      .$type<Date>(), // 인터페이스한 값은 dueDate가 없으므로 notNull 제약조건 제거

    rfqSendDate: date("rfq_send_date", { mode: "date" })
      .$type<Date | null>(), // notNull() 제약조건 제거, null 허용 (ECC에서 수신 후 보내지 않은 RFQ)

    status: varchar("status", { length: 30 })
      .$type<"RFQ Created" | "RFQ Vendor Assignned" | "RFQ Sent" | "Quotation Analysis" | "PO Transfer" | "PO Create">()
      .default("RFQ Created")
      .notNull(),

    rfqSealedYn: boolean("rfq_sealed_yn").default(false),
    picCode: varchar("pic_code", { length: 50 }), // 구매그룹에 대응시킴 (담당자 코드로 3자리)

    remark: text("remark"),
    // 생성자

    sentBy: integer("sent_by")
      .references(() => users.id, { onDelete: "set null" }),


    createdBy: integer("created_by")
      .notNull()
      .references(() => users.id, { onDelete: "set null" }),

    updatedBy: integer("updated_by")
      .notNull()
      .references(() => users.id, { onDelete: "set null" }),

    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },

);

/**
 * 지불조건, 인코텀즈, 선적/하역(출발지, 도착지) 테이블은 Non-SAP에서 동기화 (Oracle DB to PostgreSQL)
 * 동기화 로직은 instrumentation.ts 에서 node-cron 을 통해 job으로 등록됨
 */

// 지불조건 테이블 (Non-SAP 에서 동기화)
export const paymentTerms = pgTable("payment_terms", {
  code: varchar("code", { length: 50 }).primaryKey(),
  description: varchar("description", { length: 255 }).notNull(),
  // days: integer("days").notNull(),
  isActive: boolean("is_active").default(true).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

// 인코텀즈 테이블 (Non-SAP 에서 동기화)
export const incoterms = pgTable("incoterms", {
  code: varchar("code", { length: 20 }).primaryKey(),
  description: varchar("description", { length: 255 }).notNull(),
  isActive: boolean("is_active").default(true).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

// 선적/하역 테이블 (Non-SAP 에서 동기화)
export const placeOfShipping = pgTable("place_of_shipping", {
  code: varchar("code", { length: 20 }).primaryKey(),
  description: varchar("description", { length: 255 }).notNull(),
  isActive: boolean("is_active").default(true).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

export const procurementRfqDetails = pgTable(
  "procurement_rfq_details",
  {
    id: serial("id").primaryKey(),
    procurementRfqsId: integer("procurement_rfqs_id")
      .references(() => procurementRfqs.id, { onDelete: "set null" }),
    vendorsId: integer("vendors_id")
      .references(() => vendors.id, { onDelete: "set null" }),
    currency: varchar("currency", { length: 10 }).default("USD"),

    // 정규화된 paymentTerms 참조
    paymentTermsCode: varchar("payment_terms_code", { length: 50 })
      .references(() => paymentTerms.code, { onDelete: "set null" }),
    // paymentTerms 필드는 제거 (코드로 조회)

    // 정규화된 incoterms 참조
    incotermsCode: varchar("incoterms_code", { length: 20 })
      .references(() => incoterms.code, { onDelete: "set null" }),
    incotermsDetail: varchar("incoterms_detail", { length: 255 }),

    deliveryDate: date("delivery_date", { mode: "date" })
      .$type<Date>()
      .notNull(),
    taxCode: varchar("tax_code", { length: 255 }).default("VV"),
    placeOfShipping: varchar("place_of_shipping", { length: 255 }),
    placeOfDestination: varchar("place_of_destination", { length: 255 }),
    remark: text("remark"),
    cancelReason: text("cancel_reason"),
    updatedBy: integer("updated_by")
      .notNull()
      .references(() => users.id, { onDelete: "set null" }),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
    materialPriceRelatedYn: boolean("material_price_related_yn").default(false),
  }
);

export const prItems = pgTable(
  "pr_items",
  {
    id: serial("id").primaryKey(),
    procurementRfqsId: integer("procurement_rfqs_id")
      .references(() => procurementRfqs.id, { onDelete: "set null" }),

    rfqItem: varchar("rfq_item", { length: 50 }), // 단위
    prItem: varchar("pr_item", { length: 50 }), // 단위
    prNo: varchar("pr_no", { length: 50 }), // 단위

    // itemId: integer("item_id")
    //   .references(() => items.id, { onDelete: "set null" }),

    materialCode: varchar("material_code", { length: 255 }),
    materialCategory: varchar("material_category", { length: 255 }),
    acc: varchar("acc", { length: 255 }),
    materialDescription: varchar("material_description", { length: 255 }),
    size: varchar("size", { length: 255 }),
    deliveryDate: date("delivery_date", { mode: "date" })
      .$type<Date>(),
    quantity: numeric("quantity", { precision: 12, scale: 2 })
      .$type<number>()
      .default(1),
    uom: varchar("uom", { length: 50 }), // 단위
    grossWeight: numeric("gross_weight", { precision: 12, scale: 2 })
      .$type<number>()
      .default(1),
    gwUom: varchar("gw_uom", { length: 50 }), // 단위

    specNo: varchar("spec_no", { length: 255 }),
    specUrl: varchar("spec_url", { length: 255 }),
    trackingNo: varchar("tracking_no", { length: 255 }),

    majorYn: boolean("major_yn").default(false),

    projectDef: varchar("project_def", { length: 255 }),
    projectSc: varchar("project_sc", { length: 255 }),
    projectKl: varchar("project_kl", { length: 255 }),
    projectLc: varchar("project_lc", { length: 255 }),
    projectDl: varchar("project_dl", { length: 255 }),

    remark: text("remark"),

  },
);


//view
export const procurementRfqsView = pgView("procurement_rfqs_view").as((qb) => {
  const createdByUser = alias(users, "created_by_user");
  const updatedByUser = alias(users, "updated_by_user");
  const sentByUser = alias(users, "sent_by_user");
  
  return qb
    .select({
      // Basic RFQ identification
      id: sql<number>`${procurementRfqs.id}`.as("id"),
      rfqCode: sql<string>`${procurementRfqs.rfqCode}`.as("rfq_code"),
      series: sql<string | null>`${procurementRfqs.series}`.as("series"),
      rfqSealedYn: sql<string | null>`${procurementRfqs.rfqSealedYn}`.as("rfq_sealed_yn"),
      
      // Project information
      projectCode: sql<string | null>`${projects.code}`.as("project_code"),
      projectName: sql<string | null>`${projects.name}`.as("project_name"),
      
      // Item information
      itemCode: sql<string | null>`${procurementRfqs.itemCode}`.as("item_code"),
      itemName: sql<string | null>`${procurementRfqs.itemName}`.as("item_name"),
      
      // Status and dates
      status: sql<string>`${procurementRfqs.status}`.as("status"),
      picCode: sql<string | null>`${procurementRfqs.picCode}`.as("pic_code"),
      rfqSendDate: sql<Date | null>`${procurementRfqs.rfqSendDate}`.as("rfq_send_date"),
      dueDate: sql<Date | null>`${procurementRfqs.dueDate}`.as("due_date"),
      
      // 가장 빠른 견적서 제출 날짜 추가
      earliestQuotationSubmittedAt: sql<Date | null>`(
        SELECT MIN(submitted_at)
        FROM procurement_vendor_quotations
        WHERE rfq_id = ${procurementRfqs.id}
        AND submitted_at IS NOT NULL
      )`.as("earliest_quotation_submitted_at"),
      
      // Audit information
      createdByUserName: sql<string | null>`${createdByUser.name}`.as("created_by_user_name"),
      sentByUserName: sql<string | null>`${sentByUser.name}`.as("sent_by_user_name"),
      updatedAt: sql<Date>`${procurementRfqs.updatedAt}`.as("updated_at"),
      updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"),
      remark: sql<string | null>`${procurementRfqs.remark}`.as("remark"),
      
      // Related item information
      majorItemMaterialCode: sql<string | null>`(
        SELECT material_code 
        FROM pr_items 
        WHERE procurement_rfqs_id = ${procurementRfqs.id}
        AND major_yn = true
        LIMIT 1
      )`.as("major_item_material_code"),
      
      poNo: sql<string | null>`(
        SELECT pr_no 
        FROM pr_items 
        WHERE procurement_rfqs_id = ${procurementRfqs.id}
        AND major_yn = true
        LIMIT 1
      )`.as("po_no"),
      
      prItemsCount: sql<number>`(
        SELECT COUNT(*) 
        FROM pr_items 
        WHERE procurement_rfqs_id = ${procurementRfqs.id}
      )`.as("pr_items_count")
    })
    .from(procurementRfqs)
    .leftJoin(projects, eq(procurementRfqs.projectId, projects.id))
    // .leftJoin(items, eq(procurementRfqs.itemId, items.id))
    .leftJoin(createdByUser, eq(procurementRfqs.createdBy, createdByUser.id))
    .leftJoin(updatedByUser, eq(procurementRfqs.updatedBy, updatedByUser.id))
    .leftJoin(sentByUser, eq(procurementRfqs.sentBy, sentByUser.id));
});

// 수정된 pr_items_view
export const prItemsView = pgView("pr_items_view").as((qb) => {
  return qb
    .select({
      id: prItems.id,
      procurementRfqsId: prItems.procurementRfqsId,
      rfqItem: prItems.rfqItem,
      prItem: prItems.prItem,
      prNo: prItems.prNo,
      // itemId: prItems.itemId,
      materialCode: prItems.materialCode,
      materialCategory: prItems.materialCategory,
      acc: prItems.acc,
      materialDescription: prItems.materialDescription,
      size: prItems.size,
      deliveryDate: prItems.deliveryDate,
      quantity: prItems.quantity,
      uom: prItems.uom,
      grossWeight: prItems.grossWeight, // 필드명 수정
      gwUom: prItems.gwUom,
      specNo: prItems.specNo,
      specUrl: prItems.specUrl,
      trackingNo: prItems.trackingNo,
      majorYn: prItems.majorYn,
      projectDef: prItems.projectDef,
      projectSc: prItems.projectSc,
      projectKl: prItems.projectKl,
      projectLc: prItems.projectLc,
      projectDl: prItems.projectDl,
      remark: prItems.remark,
      rfqCode: procurementRfqs.rfqCode,
      itemCode: procurementRfqs.itemCode,
      itemName: procurementRfqs.itemName
    })
    .from(prItems)
    .leftJoin(procurementRfqs, eq(prItems.procurementRfqsId, procurementRfqs.id))
});

export const procurementRfqDetailsView = pgView("procurement_rfq_details_view").as((qb) => {
  // 기존 별칭 정의 유지
  const rfqDetailsTable = alias(procurementRfqDetails, "rfq_details");
  const rfqsTable = alias(procurementRfqs, "rfqs");
  const projectsTable = alias(projects, "projects");
  const vendorsTable = alias(vendors, "vendors");
  const paymentTermsTable = alias(paymentTerms, "payment_terms");
  const incotermsTable = alias(incoterms, "incoterms");
  const updatedByUser = alias(users, "updated_by_user");

  return qb
    .select({
      // procurementRfqDetails 필드
      detailId: sql<number>`${rfqDetailsTable.id}`.as("detail_id"),
      rfqId: sql<number>`${rfqsTable.id}`.as("rfq_id"),
      rfqCode: sql<string>`${rfqsTable.rfqCode}`.as("rfq_code"),

      // 프로젝트 관련 필드
      projectCode: sql<string | null>`${projectsTable.code}`.as("project_code"),
      projectName: sql<string | null>`${projectsTable.name}`.as("project_name"),

      // 아이템 관련 필드
      itemCode: sql<string | null>`${rfqsTable.itemCode}`.as("item_code"),
      itemName: sql<string | null>`${rfqsTable.itemName}`.as("item_name"),

      // 벤더 관련 필드
      vendorName: sql<string | null>`${vendorsTable.vendorName}`.as("vendor_name"),
      vendorCode: sql<string | null>`${vendorsTable.vendorCode}`.as("vendor_code"),
      vendorId: sql<string | null>`${vendorsTable.id}`.as("vendor_id"),
      vendorCountry: sql<string | null>`${vendorsTable.country}`.as("vendor_country"),

      // RFQ 상세 정보 필드
      currency: sql<string | null>`${rfqDetailsTable.currency}`.as("currency"),
      paymentTermsCode: sql<string | null>`${paymentTermsTable.code}`.as("payment_terms_code"),
      paymentTermsDescription: sql<string | null>`${paymentTermsTable.description}`.as("payment_terms_description"),
      incotermsCode: sql<string | null>`${incotermsTable.code}`.as("incoterms_code"),
      incotermsDescription: sql<string | null>`${incotermsTable.description}`.as("incoterms_description"),
      incotermsDetail: sql<string | null>`${rfqDetailsTable.incotermsDetail}`.as("incoterms_detail"),
      deliveryDate: sql<Date | null>`${rfqDetailsTable.deliveryDate}`.as("delivery_date"),
      taxCode: sql<string | null>`${rfqDetailsTable.taxCode}`.as("tax_code"),
      placeOfShipping: sql<string | null>`${rfqDetailsTable.placeOfShipping}`.as("place_of_shipping"),
      placeOfDestination: sql<string | null>`${rfqDetailsTable.placeOfDestination}`.as("place_of_destination"),
      materialPriceRelatedYn: sql<boolean | null>`${rfqDetailsTable.materialPriceRelatedYn}`.as("material_price_related_yn"),

      // 업데이트 관련 필드
      updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"),
      updatedAt: sql<Date | null>`${rfqDetailsTable.updatedAt}`.as("updated_at"),

      // 추가적으로 pr_items 개수도 카운트하는 필드 추가
      prItemsCount: sql<number>`(
              SELECT COUNT(*) 
              FROM pr_items 
              WHERE procurement_rfqs_id = ${rfqsTable.id}
          )`.as("pr_items_count"),

      // 메이저 아이템 개수도 카운트
      majorItemsCount: sql<number>`(
              SELECT COUNT(*) 
              FROM pr_items 
              WHERE procurement_rfqs_id = ${rfqsTable.id} 
              AND major_yn = true
          )`.as("major_items_count"),

      // 새로운 필드 추가: 코멘트 수 카운트
      commentCount: sql<number>`(
                SELECT COUNT(*) 
                FROM procurement_rfq_comments 
                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
            )`.as("comment_count"),

      // 새로운 필드 추가: 최근 코멘트 날짜
      lastCommentDate: sql<Date | null>`(
                SELECT created_at 
                FROM procurement_rfq_comments 
                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
                ORDER BY created_at DESC LIMIT 1
            )`.as("last_comment_date"),

      // 새로운 필드 추가: 벤더가 마지막으로 코멘트한 날짜
      lastVendorCommentDate: sql<Date | null>`(
                SELECT created_at 
                FROM procurement_rfq_comments 
                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} AND is_vendor_comment = true
                ORDER BY created_at DESC LIMIT 1
            )`.as("last_vendor_comment_date"),

      // 새로운 필드 추가: 첨부파일 수 카운트
      attachmentCount: sql<number>`(
                SELECT COUNT(*) 
                FROM procurement_rfq_attachments 
                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
            )`.as("attachment_count"),

      // 새로운 필드 추가: 견적서 제출 여부
      hasQuotation: sql<boolean>`(
                SELECT COUNT(*) > 0
                FROM procurement_vendor_quotations
                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
            )`.as("has_quotation"),

      // 새로운 필드 추가: 견적서 상태
      quotationStatus: sql<string | null>`(
                SELECT status
                FROM procurement_vendor_quotations
                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
                ORDER BY created_at DESC LIMIT 1
            )`.as("quotation_status"),

      // 새로운 필드 추가: 견적서 총액
      quotationTotalPrice: sql<number | null>`(
                SELECT total_price
                FROM procurement_vendor_quotations
                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
                ORDER BY created_at DESC LIMIT 1
            )`.as("quotation_total_price"),

      // 최신 견적서 버전
      quotationVersion: sql<number | null>`(
        SELECT quotation_version
        FROM procurement_vendor_quotations
        WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
        ORDER BY quotation_version DESC LIMIT 1
    )`.as("quotation_version"),

      // 총 견적서 버전 수
      quotationVersionCount: sql<number>`(
        SELECT COUNT(DISTINCT quotation_version)
        FROM procurement_vendor_quotations
        WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
    )`.as("quotation_version_count"),

      // 마지막 견적서 생성 날짜
      lastQuotationDate: sql<Date | null>`(
        SELECT created_at
        FROM procurement_vendor_quotations
        WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
        ORDER BY quotation_version DESC LIMIT 1
    )`.as("last_quotation_date"),
    })
    .from(rfqDetailsTable)
    // 기존 조인 유지
    .leftJoin(rfqsTable, eq(rfqDetailsTable.procurementRfqsId, rfqsTable.id))
    .leftJoin(projectsTable, eq(rfqsTable.projectId, projectsTable.id))
    // .leftJoin(itemsTable, eq(rfqsTable.itemId, itemsTable.id))
    .leftJoin(vendorsTable, eq(rfqDetailsTable.vendorsId, vendorsTable.id))
    .leftJoin(paymentTermsTable, eq(rfqDetailsTable.paymentTermsCode, paymentTermsTable.code))
    .leftJoin(incotermsTable, eq(rfqDetailsTable.incotermsCode, incotermsTable.code))
    .leftJoin(updatedByUser, eq(rfqDetailsTable.updatedBy, updatedByUser.id));
});

export const procurementAttachments = pgTable(
  "procurement_attachments",
  {
    id: serial("id").primaryKey(),
    attachmentType: varchar("attachment_type", { length: 50 }).notNull(), // 'RFQ_COMMON', 'VENDOR_SPECIFIC'
    procurementRfqsId: integer("procurement_rfqs_id")
      .references(() => procurementRfqs.id, { onDelete: "cascade" }),
    procurementRfqDetailsId: integer("procurement_rfq_details_id")
      .references(() => procurementRfqDetails.id, { onDelete: "cascade" }),
    fileName: varchar("file_name", { length: 255 }).notNull(),
    originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
    filePath: varchar("file_path", { length: 512 }).notNull(),
    fileSize: integer("file_size"),
    fileType: varchar("file_type", { length: 100 }),
    description: varchar("description", { length: 500 }),
    createdBy: integer("created_by")
      .references(() => users.id, { onDelete: "set null" })
      .notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => ({
    attachmentTypeCheck: check(
      "attachment_type_check",
      sql`${table.procurementRfqsId} IS NOT NULL OR ${table.procurementRfqDetailsId} IS NOT NULL`
    )
  })
);

export type ProcurementRfqsView = typeof procurementRfqsView.$inferSelect;
export type PrItemsView = typeof prItemsView.$inferSelect;
export type ProcurementRfqDetailsView = typeof procurementRfqDetailsView.$inferSelect;


//vendor response
export const procurementVendorQuotations = pgTable(
  "procurement_vendor_quotations",
  {
    id: serial("id").primaryKey(),
    rfqId: integer("rfq_id")
      .notNull()
      .references(() => procurementRfqs.id, { onDelete: "cascade" }),
    vendorId: integer("vendor_id")
      .notNull()
      .references(() => vendors.id, { onDelete: "set null" }),

    // 견적 요약 정보
    quotationCode: varchar("quotation_code", { length: 50 }),
    quotationVersion: integer("quotation_version").default(1),
    totalItemsCount: integer("total_items_count").default(0),
    subTotal: numeric("sub_total").default("0"),
    taxTotal: numeric("tax_total").default("0"),
    discountTotal: numeric("discount_total").default("0"),
    totalPrice: numeric("total_price").default("0"),
    currency: varchar("currency", { length: 10 }).default("USD"),

    // 견적 유효성 및 배송 정보
    validUntil: date("valid_until", { mode: "date" }).$type<Date>(),
    estimatedDeliveryDate: date("estimated_delivery_date", { mode: "date" }).$type<Date>(),

    // 지불 조건 등 상세 정보
    paymentTermsCode: varchar("payment_terms_code", { length: 50 })
      .references(() => paymentTerms.code, { onDelete: "set null" }),
    incotermsCode: varchar("incoterms_code", { length: 20 })
      .references(() => incoterms.code, { onDelete: "set null" }),
    incotermsDetail: varchar("incoterms_detail", { length: 255 }),

    // 상태 관리
    status: varchar("status", { length: 30 })
      .$type<"Draft" | "Submitted" | "Revised" | "Rejected" | "Accepted">()
      .default("Draft")
      .notNull(),

    // 기타 정보
    remark: text("remark"),
    rejectionReason: text("rejection_reason"),
    submittedAt: timestamp("submitted_at"),
    acceptedAt: timestamp("accepted_at"),

    // 감사 필드
    createdBy: integer("created_by"),
    updatedBy: integer("updated_by"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  }
);

export const procurementRfqComments = pgTable(
  "procurement_rfq_comments",
  {
    id: serial("id").primaryKey(),
    rfqId: integer("rfq_id")
      .notNull()
      .references(() => procurementRfqs.id, { onDelete: "cascade" }),
    vendorId: integer("vendor_id")
      .references(() => vendors.id, { onDelete: "set null" }),
    userId: integer("user_id")
      .references(() => users.id, { onDelete: "set null" }),
    content: text("content").notNull(),
    isVendorComment: boolean("is_vendor_comment").default(false),
    isRead: boolean("is_read").default(false), // 읽음 상태 추가
    parentCommentId: integer("parent_comment_id"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  // 자기참조 FK 정의
  (table) => {
    return {
      parentFk: foreignKey({
        columns: [table.parentCommentId],
        foreignColumns: [table.id],
      }).onDelete("set null"),
    };
  }
);

export const procurementRfqAttachments = pgTable(
  "procurement_rfq_attachments",
  {
    id: serial("id").primaryKey(),
    rfqId: integer("rfq_id")
      .notNull()
      .references(() => procurementRfqs.id, { onDelete: "cascade" }),
    commentId: integer("comment_id")
      .references(() => procurementRfqComments.id, { onDelete: "cascade" }),
    quotationId: integer("quotation_id")
      .references(() => procurementVendorQuotations.id, { onDelete: "cascade" }),
    fileName: varchar("file_name", { length: 255 }).notNull(),
    fileSize: integer("file_size").notNull(),
    fileType: varchar("file_type", { length: 100 }),
    filePath: varchar("file_path", { length: 500 }).notNull(),
    isVendorUpload: boolean("is_vendor_upload").default(false),
    uploadedBy: integer("uploaded_by")
      .references(() => users.id, { onDelete: "set null" }),
    vendorId: integer("vendor_id")
      .references(() => vendors.id, { onDelete: "set null" }),
    uploadedAt: timestamp("uploaded_at").defaultNow().notNull(),
  }
);

export const procurementQuotationItems = pgTable(
  "procurement_quotation_items",
  {
    id: serial("id").primaryKey(),
    quotationId: integer("quotation_id")
      .notNull()
      .references(() => procurementVendorQuotations.id, { onDelete: "cascade" }),

    // PR 아이템과의 연결 추가
    prItemId: integer("pr_item_id")
      .notNull()
      .references(() => prItems.id, { onDelete: "cascade" }),

    // 원본 PR 아이템 정보 참조 (읽기 전용)
    materialCode: varchar("material_code", { length: 50 }),
    materialDescription: varchar("material_description", { length: 255 }),
    quantity: numeric("quantity").notNull(),
    uom: varchar("uom", { length: 20 }),

    // 벤더가 입력하는 정보
    unitPrice: numeric("unit_price").notNull(),
    totalPrice: numeric("total_price").notNull(),
    currency: varchar("currency", { length: 10 }).default("USD"),
    vendorMaterialCode: varchar("vendor_material_code", { length: 50 }),
    vendorMaterialDescription: varchar("vendor_material_description", { length: 255 }),

    // 배송 관련 정보
    deliveryDate: date("delivery_date", { mode: "date" }).$type<Date>(),
    leadTimeInDays: integer("lead_time_in_days"),

    // 세금 및 기타 정보
    taxRate: numeric("tax_rate"),
    taxAmount: numeric("tax_amount"),
    discountRate: numeric("discount_rate"),
    discountAmount: numeric("discount_amount"),

    // 기타 정보
    remark: text("remark"),
    isAlternative: boolean("is_alternative").default(false),
    isRecommended: boolean("is_recommended").default(false),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  }
);


// procurementRfqComments 테이블의 관계 정의 - 타입 명시
export const procurementRfqCommentsRelations = relations(
  procurementRfqComments,
  ({ one, many }) => ({
    user: one(users, {
      fields: [procurementRfqComments.userId],
      references: [users.id],
    }),
    vendor: one(vendors, {
      fields: [procurementRfqComments.vendorId],
      references: [vendors.id],
    }),
    rfq: one(procurementRfqs, {
      fields: [procurementRfqComments.rfqId],
      references: [procurementRfqs.id],
    }),
    parentComment: one(procurementRfqComments, {
      fields: [procurementRfqComments.parentCommentId],
      references: [procurementRfqComments.id],
      relationName: "commentHierarchy",
    }),
    childComments: many(procurementRfqComments, { relationName: "commentHierarchy" }),
    attachments: many(procurementRfqAttachments, { relationName: "commentAttachments" }),
  })
);

export const procurementRfqAttachmentsRelations = relations(
  procurementRfqAttachments,
  ({ one }) => ({
    comment: one(procurementRfqComments, {
      fields: [procurementRfqAttachments.commentId],
      references: [procurementRfqComments.id],
      relationName: "commentAttachments",
    }),
    rfq: one(procurementRfqs, {
      fields: [procurementRfqAttachments.rfqId],
      references: [procurementRfqs.id],
    }),
    uploader: one(users, {
      fields: [procurementRfqAttachments.uploadedBy],
      references: [users.id],
    }),
    vendor: one(vendors, {
      fields: [procurementRfqAttachments.vendorId],
      references: [vendors.id],
    }),
  })
);


export const procurementRfqsRelations = relations(
  procurementRfqs,
  ({ one, many }) => ({
    project: one(projects, {
      fields: [procurementRfqs.projectId],
      references: [projects.id],
    }),
    // item: one(items, {
    //   fields: [procurementRfqs.itemId],
    //   references: [items.id],
    // }),
    createdByUser: one(users, {
      fields: [procurementRfqs.createdBy],
      references: [users.id],
      relationName: "rfqCreator",
    }),
    updatedByUser: one(users, {
      fields: [procurementRfqs.updatedBy],
      references: [users.id],
      relationName: "rfqUpdater",
    }),
    rfqDetails: many(procurementRfqDetails),
    prItems: many(prItems, { relationName: "rfqPrItems" }),
    quotations: many(procurementVendorQuotations),

  })
);

export const prItemsRelations = relations(
  prItems,
  ({ one }) => ({
    rfq: one(procurementRfqs, {
      fields: [prItems.procurementRfqsId],
      references: [procurementRfqs.id],
      relationName: "rfqPrItems"
    }),
  })
);

// procurementRfqDetails 테이블의 관계 정의
export const procurementRfqDetailsRelations = relations(
  procurementRfqDetails,
  ({ one }) => ({
    rfq: one(procurementRfqs, {
      fields: [procurementRfqDetails.procurementRfqsId],
      references: [procurementRfqs.id],
    }),
    vendor: one(vendors, {
      fields: [procurementRfqDetails.vendorsId],
      references: [vendors.id],
    }),
    paymentTerms: one(paymentTerms, {
      fields: [procurementRfqDetails.paymentTermsCode],
      references: [paymentTerms.code],
    }),
    incoterms: one(incoterms, {
      fields: [procurementRfqDetails.incotermsCode],
      references: [incoterms.code],
    }),
    updatedByUser: one(users, {
      fields: [procurementRfqDetails.updatedBy],
      references: [users.id],
    }),
  })
);

export const vendorsRelations = relations(
  vendors,
  ({ many }) => ({
    users: many(users, {
      relationName: "vendorUsers"
    }),
    quotations: many(procurementVendorQuotations),
  })
);

export const usersRelations = relations(
  users,
  ({ one }) => ({
    vendor: one(vendors, {
      fields: [users.companyId],
      references: [vendors.id],
      relationName: "vendorUsers"
    }),
  })
);

export const procurementVendorQuotationsRelations = relations(
  procurementVendorQuotations,
  ({ one, many }) => ({
    rfq: one(procurementRfqs, {
      fields: [procurementVendorQuotations.rfqId],
      references: [procurementRfqs.id],
      // relationName 제거
    }),
    vendor: one(vendors, {
      fields: [procurementVendorQuotations.vendorId],
      references: [vendors.id],
      // relationName 제거
    }),
    items: many(procurementQuotationItems),
    paymentTerms: one(paymentTerms, {
      fields: [procurementVendorQuotations.paymentTermsCode],
      references: [paymentTerms.code],
    }),
    incoterms: one(incoterms, {
      fields: [procurementVendorQuotations.incotermsCode],
      references: [incoterms.code],
    }),
  })
);

export const procurementQuotationItemsRelations = relations(
  procurementQuotationItems,
  ({ one }) => ({
    quotation: one(procurementVendorQuotations, {
      fields: [procurementQuotationItems.quotationId],
      references: [procurementVendorQuotations.id],
    }),
    prItem: one(prItems, {
      fields: [procurementQuotationItems.prItemId],
      references: [prItems.id],
    }),
  })
);

export type ProcurementVendorQuotations = typeof procurementVendorQuotations.$inferSelect;
export type Incoterm = typeof incoterms.$inferSelect;