import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, jsonb, decimal } from "drizzle-orm/pg-core";
import { eq, sql, relations,and } from "drizzle-orm";
import { rfqsLast, rfqLastDetails, rfqPrItems } from "./rfqLast";
import { users } from "./users";
import { vendors } from "./vendors";
import { incoterms, paymentTerms } from "./procurementRFQ";
import { projects } from "./projects";

// ==========================================
// 1. 벤더 응답 메인 테이블 (견적서 헤더)
// ==========================================
export const rfqLastVendorResponses = pgTable(
    "rfq_last_vendor_responses",
    {
        id: serial("id").primaryKey(),

        // RFQ 및 벤더 참조
        rfqsLastId: integer("rfqs_last_id")
            .notNull()
            .references(() => rfqsLast.id, { onDelete: "cascade" }),
        rfqLastDetailsId: integer("rfq_last_details_id")
            .notNull()
            .references(() => rfqLastDetails.id, { onDelete: "cascade" }),
        vendorId: integer("vendor_id")
            .notNull()
            .references(() => vendors.id, { onDelete: "cascade" }),

        // 응답 버전 관리
        responseVersion: integer("response_version").notNull().default(1),
        isLatest: boolean("is_latest").notNull().default(true),

        isDocumentConfirmed: boolean("is_document_confirmed").default(false),
        // 참여 여부 관련 필드 (새로 추가)
        participationStatus: varchar("participation_status", { length: 20 })
            .$type<"미응답" | "참여" | "불참">()
            .notNull()
            .default("미응답"),
        participationRepliedAt: timestamp("participation_replied_at"),
        participationRepliedBy: integer("participation_replied_by")
            .references(() => users.id, { onDelete: "set null" }),
        nonParticipationReason: text("non_participation_reason"), // 불참 사유
        
        // 응답 상태 (수정: 참여 결정 후에만 의미 있음)
        status: varchar("status", { length: 30 })
            .$type<"대기중" | "작성중" | "제출완료" | "수정요청" | "최종확정" | "취소" | "삭제">()
            .notNull()
            .default("대기중"),
        // 제출 정보
        submittedAt: timestamp("submitted_at"),
        submittedBy: integer("submitted_by")
            .references(() => users.id, { onDelete: "set null" }),

        // 총액 정보
        totalAmount: numeric("total_amount", { precision: 15, scale: 2 }).$type<number>(),
        currency: varchar("currency", { length: 10 }).default("USD"),

        // 벤더 제안 조건 (구매자 제시와 다른 경우)
        vendorCurrency: varchar("vendor_currency", { length: 10 }),
        vendorPaymentTermsCode: varchar("vendor_payment_terms_code", { length: 50 })
            .references(() => paymentTerms.code, { onDelete: "set null" }),
        vendorIncotermsCode: varchar("vendor_incoterms_code", { length: 20 })
            .references(() => incoterms.code, { onDelete: "set null" }),
        vendorIncotermsDetail: varchar("vendor_incoterms_detail", { length: 255 }),
        vendorDeliveryDate: date("vendor_delivery_date", { mode: "date" }).$type<Date | null>(),
        vendorContractDuration: varchar("vendor_contract_duration", { length: 255 }),
        vendorTaxCode: varchar("vendor_tax_code", { length: 255 }),
        vendorPlaceOfShipping: varchar("vendor_place_of_shipping", { length: 255 }),
        vendorPlaceOfDestination: varchar("vendor_place_of_destination", { length: 255 }),

        // 초도품관리 응답
        vendorFirstYn: boolean("vendor_first_yn"),
        vendorFirstDescription: text("vendor_first_description"),
        vendorFirstAcceptance: varchar("vendor_first_acceptance", { length: 50 })
            .$type<"수용" | "부분수용" | "거부" | null>(),

        // Spare part 응답
        vendorSparepartYn: boolean("vendor_sparepart_yn"),
        vendorSparepartDescription: text("vendor_sparepart_description"),
        vendorSparepartAcceptance: varchar("vendor_sparepart_acceptance", { length: 50 })
            .$type<"수용" | "부분수용" | "거부" | null>(),

        // 연동제 적용 응답
        vendorMaterialPriceRelatedYn: boolean("vendor_material_price_related_yn"),
        vendorMaterialPriceRelatedReason: text("vendor_material_price_related_reason"),

        // 각 조건별 변경 사유 (구매자 제시와 다른 경우)
        currencyReason: text("currency_reason"),
        paymentTermsReason: text("payment_terms_reason"),
        deliveryDateReason: text("delivery_date_reason"),
        incotermsReason: text("incoterms_reason"),
        taxReason: text("tax_reason"),
        shippingReason: text("shipping_reason"),

        // 전체 비고 및 특이사항
        generalRemark: text("general_remark"),
        technicalProposal: text("technical_proposal"),

        // 감사 필드
        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(),

     
    }
);

// ==========================================
// 2. 벤더 견적 아이템 테이블 (PR 아이템별 가격)
// ==========================================
export const rfqLastVendorQuotationItems = pgTable(
    "rfq_last_vendor_quotation_items",
    {
        id: serial("id").primaryKey(),
        vendorResponseId: integer("vendor_response_id")
            .notNull()
            .references(() => rfqLastVendorResponses.id, { onDelete: "cascade" }),
        rfqPrItemId: integer("rfq_pr_item_id")
            .notNull()
            .references(() => rfqPrItems.id, { onDelete: "cascade" }),

        // PR 아이템 정보 (캐시)
        prNo: varchar("pr_no", { length: 50 }),
        materialCode: varchar("material_code", { length: 255 }),
        materialDescription: varchar("material_description", { length: 255 }),

        // 견적 정보
        quantity: numeric("quantity", { precision: 12, scale: 2 })
            .$type<number>()
            .notNull(),
        uom: varchar("uom", { length: 50 }),

        // 가격 정보
        unitPrice: numeric("unit_price", { precision: 15, scale: 4 })
            .$type<number>()
            .notNull(),
        totalPrice: numeric("total_price", { precision: 15, scale: 2 })
            .$type<number>()
            .notNull(),
        currency: varchar("currency", { length: 10 }).default("USD"),

        // 납기 정보
        vendorDeliveryDate: date("vendor_delivery_date", { mode: "date" }).$type<Date | null>(),
        leadTime: integer("lead_time"), // days

        // 제조사 정보
        manufacturer: varchar("manufacturer", { length: 255 }),
        manufacturerCountry: varchar("manufacturer_country", { length: 100 }),
        modelNo: varchar("model_no", { length: 255 }),

        // 기술 사양
        technicalCompliance: boolean("technical_compliance").default(true),
        alternativeProposal: text("alternative_proposal"),

        // 할인 정보
        discountRate: numeric("discount_rate", { precision: 5, scale: 2 }).$type<number>(),
        discountAmount: numeric("discount_amount", { precision: 15, scale: 2 }).$type<number>(),

        // 비고
        itemRemark: text("item_remark"),
        deviationReason: text("deviation_reason"), // 요구사항과 다른 경우 사유

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

// ==========================================
// 4. 벤더 첨부파일 테이블
// ==========================================
export const rfqLastVendorAttachments = pgTable(
    "rfq_last_vendor_attachments",
    {
        id: serial("id").primaryKey(),
        vendorResponseId: integer("vendor_response_id")
            .notNull()
            .references(() => rfqLastVendorResponses.id, { onDelete: "cascade" }),

        // 첨부파일 구분
        attachmentType: varchar("attachment_type", { length: 50 }).notNull(),
        // "견적서", "기술제안서", "인증서", "카탈로그", "도면", "테스트성적서", "기타"

        documentNo: varchar("document_no", { length: 100 }), // 문서번호

        // 파일 정보
        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: text("description"),

        // 유효기간 (인증서 등)
        validFrom: date("valid_from", { mode: "date" }).$type<Date | null>(),
        validTo: date("valid_to", { mode: "date" }).$type<Date | null>(),

        // 감사 필드
        uploadedBy: integer("uploaded_by")
            .notNull()
            .references(() => users.id, { onDelete: "set null" }),
        uploadedAt: timestamp("uploaded_at").defaultNow().notNull(),
    }
);

// ==========================================
// 4. 벤더 응답 이력 테이블 (변경 이력 관리)
// ==========================================
export const rfqLastVendorResponseHistory = pgTable(
    "rfq_last_vendor_response_history",
    {
        id: serial("id").primaryKey(),
        vendorResponseId: integer("vendor_response_id")
            .notNull()
            .references(() => rfqLastVendorResponses.id, { onDelete: "cascade" }),

        action: varchar("action", { length: 50 }).notNull(),
        // "생성", "수정", "제출", "철회", "재제출", "승인", "반려"

        previousStatus: varchar("previous_status", { length: 30 }),
        newStatus: varchar("new_status", { length: 30 }),

        changeDetails: jsonb("change_details"), // 변경된 필드들
        changeReason: text("change_reason"),

        performedBy: integer("performed_by")
            .notNull()
            .references(() => users.id, { onDelete: "set null" }),
        performedAt: timestamp("performed_at").defaultNow().notNull(),
    }
);

// ==========================================
// 5. RFQ-last 연동제 폼 테이블
// ==========================================
export const rfqLastPriceAdjustmentForms = pgTable('rfq_last_price_adjustment_forms', {
  id: serial('id').primaryKey(),
  
  // rfqLastVendorResponses 테이블과 외래 키로 연결
  rfqLastVendorResponsesId: integer('rfq_last_vendor_responses_id')
    .notNull()
    .references(() => rfqLastVendorResponses.id, { onDelete: 'cascade' }),
  
  // 품목등의 명칭
  itemName: varchar('item_name', { length: 255 }),

  // 조정대금 반영시점
  adjustmentReflectionPoint: varchar('adjustment_reflection_point', { length: 255 }),
  
  // 연동대상 주요 원재료
  majorApplicableRawMaterial: text('major_applicable_raw_material'),
  
  // 하도급대금등 연동 산식
  adjustmentFormula: text('adjustment_formula'),

  // 원재료 가격 기준지표
  rawMaterialPriceIndex: text('raw_material_price_index'),
  
  // 기준시점 및 비교시점
  referenceDate: date('reference_date'), // 기준시점
  comparisonDate: date('comparison_date'), // 비교시점

  // 연동 비율
  adjustmentRatio: decimal('adjustment_ratio', { precision: 5, scale: 2 }), // 소수점 2자리까지
  
  // 기타 사항
  notes: text('notes'),

  // 조정요건
  adjustmentConditions: text('adjustment_conditions'),
  
  // 연동 미적용 주요 원재료
  majorNonApplicableRawMaterial: text('major_non_applicable_raw_material'),
  
  // 조정주기
  adjustmentPeriod: varchar('adjustment_period', { length: 100 }),

  // 수탁기업(협력사) 작성자
  contractorWriter: varchar('contractor_writer', { length: 100 }),

  // 조정일
  adjustmentDate: date('adjustment_date'),
  
  // 연동 미적용 사유
  nonApplicableReason: text('non_applicable_reason'),
  
  // 메타 정보
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// ==========================================
// Views
// ==========================================

// 벤더 응답 종합 뷰
export const vendorResponseSummaryView = pgView("vendor_response_summary_view").as((qb) => {
    const responseTable = alias(rfqLastVendorResponses, "response");
    const rfqTable = alias(rfqsLast, "rfq");
    const detailTable = alias(rfqLastDetails, "detail");
    const vendorTable = alias(vendors, "vendor");
    const submittedByUser = alias(users, "submitted_by_user");

    return qb
        .select({
            // 응답 기본 정보
            responseId: sql<number>`${responseTable.id}`.as("response_id"),
            rfqId: sql<number>`${rfqTable.id}`.as("rfq_id"),
            rfqCode: sql<string>`${rfqTable.rfqCode}`.as("rfq_code"),

            // 벤더 정보
            vendorId: sql<number>`${vendorTable.id}`.as("vendor_id"),
            vendorName: sql<string>`${vendorTable.vendorName}`.as("vendor_name"),
            vendorCode: sql<string>`${vendorTable.vendorCode}`.as("vendor_code"),

            // 응답 상태
            responseVersion: sql<number>`${responseTable.responseVersion}`.as("response_version"),
            status: sql<string>`${responseTable.status}`.as("status"),
            isLatest: sql<boolean>`${responseTable.isLatest}`.as("is_latest"),

            // 제출 정보
            submittedAt: sql<Date | null>`${responseTable.submittedAt}`.as("submitted_at"),
            submittedByName: sql<string | null>`${submittedByUser.name}`.as("submitted_by_name"),

            // 금액 정보
            totalAmount: sql<number | null>`${responseTable.totalAmount}`.as("total_amount"),
            currency: sql<string>`${responseTable.currency}`.as("currency"),

            // 주요 조건 차이
            deliveryDateMatch: sql<boolean>`
                CASE 
                    WHEN ${detailTable.deliveryDate} = ${responseTable.vendorDeliveryDate} THEN true
                    ELSE false
                END
            `.as("delivery_date_match"),

            currencyMatch: sql<boolean>`
                CASE 
                    WHEN ${detailTable.currency} = ${responseTable.vendorCurrency} THEN true
                    ELSE false
                END
            `.as("currency_match"),

            // 아이템 수
            quotedItemCount: sql<number>`(
                SELECT COUNT(*)
                FROM rfq_last_vendor_quotation_items
                WHERE vendor_response_id = ${responseTable.id}
            )`.as("quoted_item_count"),

            // 첨부파일 수
            attachmentCount: sql<number>`(
                SELECT COUNT(*)
                FROM rfq_last_vendor_attachments
                WHERE vendor_response_id = ${responseTable.id}
            )`.as("attachment_count"),

            // 타임스탬프
            createdAt: sql<Date>`${responseTable.createdAt}`.as("created_at"),
            updatedAt: sql<Date>`${responseTable.updatedAt}`.as("updated_at"),
        })
        .from(responseTable)
        .leftJoin(rfqTable, eq(responseTable.rfqsLastId, rfqTable.id))
        .leftJoin(detailTable, eq(responseTable.rfqLastDetailsId, detailTable.id))
        .leftJoin(vendorTable, eq(responseTable.vendorId, vendorTable.id))
        .leftJoin(submittedByUser, eq(responseTable.submittedBy, submittedByUser.id));
});

// 벤더 견적 아이템 상세 뷰
export const vendorQuotationItemsDetailView = pgView("vendor_quotation_items_detail_view").as((qb) => {
    const quotationTable = alias(rfqLastVendorQuotationItems, "quotation");
    const prItemTable = alias(rfqPrItems, "pr_item");
    const responseTable = alias(rfqLastVendorResponses, "response");
    const rfqTable = alias(rfqsLast, "rfq");

    return qb
        .select({
            // 견적 아이템 정보
            quotationItemId: sql<number>`${quotationTable.id}`.as("quotation_item_id"),
            vendorResponseId: sql<number>`${quotationTable.vendorResponseId}`.as("vendor_response_id"),

            // RFQ 정보
            rfqCode: sql<string>`${rfqTable.rfqCode}`.as("rfq_code"),

            // PR 아이템 정보
            prNo: sql<string | null>`${prItemTable.prNo}`.as("pr_no"),
            prItem: sql<string | null>`${prItemTable.prItem}`.as("pr_item"),
            materialCode: sql<string | null>`${prItemTable.materialCode}`.as("material_code"),
            materialDescription: sql<string | null>`${prItemTable.materialDescription}`.as("material_description"),
            originalQuantity: sql<number | null>`${prItemTable.quantity}`.as("original_quantity"),
            originalDeliveryDate: sql<Date | null>`${prItemTable.deliveryDate}`.as("original_delivery_date"),

            // 벤더 견적 정보
            quotedQuantity: sql<number>`${quotationTable.quantity}`.as("quoted_quantity"),
            unitPrice: sql<number>`${quotationTable.unitPrice}`.as("unit_price"),
            totalPrice: sql<number>`${quotationTable.totalPrice}`.as("total_price"),
            currency: sql<string>`${quotationTable.currency}`.as("currency"),
            vendorDeliveryDate: sql<Date | null>`${quotationTable.vendorDeliveryDate}`.as("vendor_delivery_date"),
            leadTime: sql<number | null>`${quotationTable.leadTime}`.as("lead_time"),

            // 제조사 정보
            manufacturer: sql<string | null>`${quotationTable.manufacturer}`.as("manufacturer"),
            modelNo: sql<string | null>`${quotationTable.modelNo}`.as("model_no"),

            // 할인 정보
            discountRate: sql<number | null>`${quotationTable.discountRate}`.as("discount_rate"),
            discountAmount: sql<number | null>`${quotationTable.discountAmount}`.as("discount_amount"),

            // 기술 준수
            technicalCompliance: sql<boolean>`${quotationTable.technicalCompliance}`.as("technical_compliance"),
            alternativeProposal: sql<string | null>`${quotationTable.alternativeProposal}`.as("alternative_proposal"),

            // 비고
            itemRemark: sql<string | null>`${quotationTable.itemRemark}`.as("item_remark"),
            deviationReason: sql<string | null>`${quotationTable.deviationReason}`.as("deviation_reason"),
        })
        .from(quotationTable)
        .leftJoin(prItemTable, eq(quotationTable.rfqPrItemId, prItemTable.id))
        .leftJoin(responseTable, eq(quotationTable.vendorResponseId, responseTable.id))
        .leftJoin(rfqTable, eq(responseTable.rfqsLastId, rfqTable.id));
});

// Relations
export const vendorQuotationItemsRelations = relations(
    rfqLastVendorQuotationItems,
    ({ one }) => ({
      vendorResponse: one(rfqLastVendorResponses, {
        fields: [rfqLastVendorQuotationItems.vendorResponseId],
        references: [rfqLastVendorResponses.id],
      }),
      prItem: one(rfqPrItems, {
        fields: [rfqLastVendorQuotationItems.rfqPrItemId],
        references: [rfqPrItems.id],
      }),
    })
  );
  
  // 첨부파일 테이블의 relation
  export const vendorAttachmentsRelations = relations(
    rfqLastVendorAttachments,
    ({ one }) => ({
      vendorResponse: one(rfqLastVendorResponses, {
        fields: [rfqLastVendorAttachments.vendorResponseId],
        references: [rfqLastVendorResponses.id],
      }),
      uploadedByUser: one(users, {
        fields: [rfqLastVendorAttachments.uploadedBy],
        references: [users.id],
      }),
    })
  );
  
  // 이력 테이블의 relation
  export const vendorResponseHistoryRelations = relations(
    rfqLastVendorResponseHistory,
    ({ one }) => ({
      vendorResponse: one(rfqLastVendorResponses, {
        fields: [rfqLastVendorResponseHistory.vendorResponseId],
        references: [rfqLastVendorResponses.id],
      }),
      performedByUser: one(users, {
        fields: [rfqLastVendorResponseHistory.performedBy],
        references: [users.id],
      }),
    })
  );
  
  // 메인 응답 테이블의 relation (이미 있지만 완전한 버전)
  export const vendorResponseRelations = relations(
    rfqLastVendorResponses,
    ({ one, many }) => ({
      rfq: one(rfqsLast, {
        fields: [rfqLastVendorResponses.rfqsLastId],
        references: [rfqsLast.id],
      }),
      rfqDetail: one(rfqLastDetails, {
        fields: [rfqLastVendorResponses.rfqLastDetailsId],
        references: [rfqLastDetails.id],
      }),
      vendor: one(vendors, {
        fields: [rfqLastVendorResponses.vendorId],
        references: [vendors.id],
      }),
      submittedByUser: one(users, {
        fields: [rfqLastVendorResponses.submittedBy],
        references: [users.id],
        relationName: "vendorResponseSubmittedBy",
      }),
      participationRepliedByUser: one(users, {
        fields: [rfqLastVendorResponses.participationRepliedBy],
        references: [users.id],
        relationName: "vendorResponseParticipationRepliedBy",
      }),
      createdByUser: one(users, {
        fields: [rfqLastVendorResponses.createdBy],
        references: [users.id],
        relationName: "vendorResponseCreatedBy",
      }),
      updatedByUser: one(users, {
        fields: [rfqLastVendorResponses.updatedBy],
        references: [users.id],
        relationName: "vendorResponseUpdatedBy",
      }),
      quotationItems: many(rfqLastVendorQuotationItems),
      attachments: many(rfqLastVendorAttachments),
      history: many(rfqLastVendorResponseHistory),
      priceAdjustmentForm: one(rfqLastPriceAdjustmentForms, {
        fields: [rfqLastVendorResponses.id],
        references: [rfqLastPriceAdjustmentForms.rfqLastVendorResponsesId],
        relationName: "vendorResponsePriceAdjustmentForm",
      }),
    })
  );

  // 연동제 폼 테이블의 relation
  export const priceAdjustmentFormRelations = relations(
    rfqLastPriceAdjustmentForms,
    ({ one }) => ({
      vendorResponse: one(rfqLastVendorResponses, {
        fields: [rfqLastPriceAdjustmentForms.rfqLastVendorResponsesId],
        references: [rfqLastVendorResponses.id],
      }),
    })
  );

  
// Type exports
export type VendorResponse = typeof rfqLastVendorResponses.$inferSelect;
export type VendorQuotationItem = typeof rfqLastVendorQuotationItems.$inferSelect;
export type VendorAttachment = typeof rfqLastVendorAttachments.$inferSelect;
export type VendorResponseHistory = typeof rfqLastVendorResponseHistory.$inferSelect;


// vendorQuotationView - 벤더별 견적 현황을 보여주는 통합 뷰
export const vendorQuotationView = pgView("vendor_quotation_view").as((qb) => {
  const createdByUser = alias(users, "created_by_user");
  const updatedByUser = alias(users, "updated_by_user");
  const sentByUser = alias(users, "sent_by_user");
  const picUser = alias(users, "pic_user");

  return qb
      .select({
          // ===== RFQ 기본 정보 (rfqsLastView에서 가져온 필드들) =====
          id: sql<number>`${rfqsLast.id}`.as("id"),
          rfqCode: sql<string>`${rfqsLast.rfqCode}`.as("rfq_code"),
          series: sql<string | null>`${rfqsLast.series}`.as("series"),
          rfqSealedYn: sql<boolean | null>`${rfqsLast.rfqSealedYn}`.as("rfq_sealed_yn"),
          
          // RFQ 타입 정보
          rfqType: sql<string | null>`${rfqsLast.rfqType}`.as("rfq_type"),
          rfqTitle: sql<string | null>`${rfqsLast.rfqTitle}`.as("rfq_title"),
          
          // ITB 관련 필드
          projectCompany: sql<string | null>`${rfqsLast.projectCompany}`.as("project_company"),
          projectFlag: sql<string | null>`${rfqsLast.projectFlag}`.as("project_flag"),
          projectSite: sql<string | null>`${rfqsLast.projectSite}`.as("project_site"),
          smCode: sql<string | null>`${rfqsLast.smCode}`.as("sm_code"),
          
          // RFQ 추가 필드
          prNumber: sql<string | null>`${rfqsLast.prNumber}`.as("pr_number"),
          prIssueDate: sql<Date | null>`${rfqsLast.prIssueDate}`.as("pr_issue_date"),
          
          // 프로젝트 정보
          projectId: sql<number | null>`${rfqsLast.projectId}`.as("project_id"),
          projectCode: sql<string | null>`${projects.code}`.as("project_code"),
          projectName: sql<string | null>`${projects.name}`.as("project_name"),
          
          // 아이템 정보
          itemCode: sql<string | null>`${rfqsLast.itemCode}`.as("item_code"),
          itemName: sql<string | null>`${rfqsLast.itemName}`.as("item_name"),
          
          // 패키지 정보
          packageNo: sql<string | null>`${rfqsLast.packageNo}`.as("package_no"),
          packageName: sql<string | null>`${rfqsLast.packageName}`.as("package_name"),
          
          engPicName: sql<string | null>`${rfqsLast.EngPicName}`.as("eng_pic_name"),
          
          // 상태와 날짜
          status: sql<string>`${rfqsLast.status}`.as("status"),
          rfqSendDate: sql<Date | null>`${rfqsLast.rfqSendDate}`.as("rfq_send_date"),
          dueDate: sql<Date | null>`${rfqsLast.dueDate}`.as("due_date"),
          
          // PIC 정보
          picId: sql<number | null>`${rfqsLast.pic}`.as("pic_id"),
          picCode: sql<string | null>`${rfqsLast.picCode}`.as("pic_code"),
          picName: sql<string | null>`${rfqsLast.picName}`.as("pic_name"),
          picUserName: sql<string | null>`${picUser.name}`.as("pic_user_name"),
          
          // 감사 정보
          createdBy: sql<number>`${rfqsLast.createdBy}`.as("created_by"),
          createdByUserName: sql<string | null>`${createdByUser.name}`.as("created_by_user_name"),
          createdAt: sql<Date>`${rfqsLast.createdAt}`.as("created_at"),
          sentBy: sql<number | null>`${rfqsLast.sentBy}`.as("sent_by"),
          sentByUserName: sql<string | null>`${sentByUser.name}`.as("sent_by_user_name"),
          updatedBy: sql<number>`${rfqsLast.updatedBy}`.as("updated_by"),
          updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"),
          updatedAt: sql<Date>`${rfqsLast.updatedAt}`.as("updated_at"),
          remark: sql<string | null>`${rfqsLast.remark}`.as("remark"),

          // ===== 벤더별 정보 =====
          vendorId: sql<number | null>`${vendors.id}`.as("vendor_id"),
          vendorName: sql<string | null>`${vendors.vendorName}`.as("vendor_name"),
          vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
          
          // rfqLastDetails 정보
          rfqLastDetailsId: sql<number | null>`${rfqLastDetails.id}`.as("rfq_last_details_id"),
          emailSentAt: sql<Date | null>`${rfqLastDetails.emailSentAt}`.as("email_sent_at"),
          emailStatus: sql<string | null>`${rfqLastDetails.emailStatus}`.as("email_status"),
          shortList: sql<boolean>`${rfqLastDetails.shortList}`.as("short_list"),
          
          // ===== 벤더 응답 정보 (rfqLastVendorResponses) =====
          vendorResponseId: sql<number | null>`${rfqLastVendorResponses.id}`.as("vendor_response_id"),
          
          // 참여 상태
          participationStatus: sql<string | null>`${rfqLastVendorResponses.participationStatus}`.as("participation_status"),
          participationRepliedAt: sql<Date | null>`${rfqLastVendorResponses.participationRepliedAt}`.as("participation_replied_at"),
          nonParticipationReason: sql<string | null>`${rfqLastVendorResponses.nonParticipationReason}`.as("non_participation_reason"),
          
          // 응답 상태
          responseStatus: sql<string | null>`${rfqLastVendorResponses.status}`.as("response_status"),
          responseVersion: sql<number | null>`${rfqLastVendorResponses.responseVersion}`.as("response_version"),
          submittedAt: sql<Date | null>`${rfqLastVendorResponses.submittedAt}`.as("submitted_at"),
          
          // 금액 정보
          totalAmount: sql<number | null>`${rfqLastVendorResponses.totalAmount}`.as("total_amount"),
          vendorCurrency: sql<string | null>`${rfqLastVendorResponses.vendorCurrency}`.as("vendor_currency"),
          
          // 벤더 제안 조건
          vendorPaymentTermsCode: sql<string | null>`${rfqLastVendorResponses.vendorPaymentTermsCode}`.as("vendor_payment_terms_code"),
          vendorIncotermsCode: sql<string | null>`${rfqLastVendorResponses.vendorIncotermsCode}`.as("vendor_incoterms_code"),
          vendorDeliveryDate: sql<Date | null>`${rfqLastVendorResponses.vendorDeliveryDate}`.as("vendor_delivery_date"),
          
          // ===== 계산된 필드 - displayStatus =====
          displayStatus: sql<string | null>`
              CASE 
                  WHEN ${rfqLastVendorResponses.participationStatus} = '불참' THEN '불참'
                  WHEN ${rfqLastVendorResponses.participationStatus} = '참여' THEN 
                      COALESCE(${rfqLastVendorResponses.status}, '작성중')
                  WHEN ${rfqLastVendorResponses.participationStatus} = '미응답' OR ${rfqLastVendorResponses.participationStatus} IS NULL THEN
                      CASE
                          WHEN ${rfqLastDetails.emailSentAt} IS NOT NULL THEN '미응답'
                          ELSE NULL
                      END
                  ELSE '미응답'
              END
          `.as("display_status"),

          // ===== 집계 정보 (RFQ 레벨) =====
          vendorCount: sql<number>`(
              SELECT COUNT(*)
              FROM rfq_last_details d
              WHERE d.rfqs_last_id = ${rfqsLast.id}
              AND d.is_latest = true
          )`.as("vendor_count"),
          
          shortListedVendorCount: sql<number>`(
              SELECT COUNT(*)
              FROM rfq_last_details d
              WHERE d.rfqs_last_id = ${rfqsLast.id}
              AND d.short_list = true
              AND d.is_latest = true
          )`.as("short_listed_vendor_count"),
          
          quotationReceivedCount: sql<number>`(
              SELECT COUNT(DISTINCT r.vendor_id)
              FROM rfq_last_vendor_responses r
              WHERE r.rfqs_last_id = ${rfqsLast.id}
              AND r.submitted_at IS NOT NULL
              AND r.is_latest = true
          )`.as("quotation_received_count"),
          
          earliestQuotationSubmittedAt: sql<Date | null>`(
              SELECT MIN(r.submitted_at)
              FROM rfq_last_vendor_responses r
              WHERE r.rfqs_last_id = ${rfqsLast.id}
              AND r.submitted_at IS NOT NULL
              AND r.is_latest = true
          )`.as("earliest_quotation_submitted_at"),

          // PR Items 관련 정보
          majorItemMaterialCode: sql<string | null>`(
              SELECT material_code 
              FROM rfq_pr_items 
              WHERE rfqs_last_id = ${rfqsLast.id}
              AND major_yn = true
              LIMIT 1
          )`.as("major_item_material_code"),

          majorItemMaterialDescription: sql<string | null>`(
              SELECT material_description 
              FROM rfq_pr_items 
              WHERE rfqs_last_id = ${rfqsLast.id}
              AND major_yn = true
              LIMIT 1
          )`.as("major_item_material_description"),

          majorItemMaterialCategory: sql<string | null>`(
              SELECT material_category 
              FROM rfq_pr_items 
              WHERE rfqs_last_id = ${rfqsLast.id}
              AND major_yn = true
              LIMIT 1
          )`.as("major_item_material_category"),

          majorItemPrNo: sql<string | null>`(
              SELECT pr_no 
              FROM rfq_pr_items 
              WHERE rfqs_last_id = ${rfqsLast.id}
              AND major_yn = true
              LIMIT 1
          )`.as("major_item_pr_no"),

          prItemsCount: sql<number>`(
              SELECT COUNT(*) 
              FROM rfq_pr_items 
              WHERE rfqs_last_id = ${rfqsLast.id}
          )`.as("pr_items_count"),

          majorItemsCount: sql<number>`(
              SELECT COUNT(*) 
              FROM rfq_pr_items 
              WHERE rfqs_last_id = ${rfqsLast.id}
              AND major_yn = true
          )`.as("major_items_count")
      })
      .from(rfqsLast)
      .innerJoin(rfqLastDetails, 
          and(
              eq(rfqLastDetails.rfqsLastId, rfqsLast.id),
              eq(rfqLastDetails.isLatest, true)
          )
      )
      .leftJoin(vendors, eq(rfqLastDetails.vendorsId, vendors.id))
      .leftJoin(rfqLastVendorResponses,
          and(
              eq(rfqLastVendorResponses.rfqsLastId, rfqsLast.id),
              eq(rfqLastVendorResponses.vendorId, vendors.id),
              eq(rfqLastVendorResponses.isLatest, true)
          )
      )
      .leftJoin(projects, eq(rfqsLast.projectId, projects.id))
      .leftJoin(createdByUser, eq(rfqsLast.createdBy, createdByUser.id))
      .leftJoin(updatedByUser, eq(rfqsLast.updatedBy, updatedByUser.id))
      .leftJoin(sentByUser, eq(rfqsLast.sentBy, sentByUser.id))
      .leftJoin(picUser, eq(rfqsLast.pic, picUser.id));
});

// Type export
export type VendorQuotationView = typeof vendorQuotationView.$inferSelect;