path: root/db/schema/bRfq.ts

import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, uniqueIndex, foreignKey } from "drizzle-orm/pg-core";
import { eq, sql, and } from "drizzle-orm";
import { projects } from "./projects";
import { users } from "./users";
import { vendors } from "./vendors";
import { incoterms, paymentTerms } from "./procurementRFQ";

export const bRfqs = pgTable(
    "b_rfqs",
    {
        id: serial("id").primaryKey(),

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

        // 프로젝트 참조
        projectId: integer("project_id")
            .references(() => projects.id, { onDelete: "set null" }),

        description: varchar("description", { length: 255 }),

        remark: text("remark"),

        dueDate: date("due_date", { mode: "date" })
            .$type<Date>()
            .notNull(),

        status: varchar("status", { length: 30 })
            .$type<"DRAFT" | "Doc. Received" | "PIC Assigned" | "Doc. Confirmed" | "Init. RFQ Sent" | "Init. RFQ Answered" | "TBE started" | "TBE finished" | "Final RFQ Sent" | "Quotation Received" | "Vendor Selected">()
            .default("DRAFT")
            .notNull(),

        picCode: varchar("pic_code", { length: 50 }),
        picName: varchar("pic_name", { length: 50 }),
        EngPicName: varchar("eng_pic_name", { length: 50 }),

        projectCompany: varchar("project_company", { length: 255 }),
        projectFlag: varchar("project_flag", { length: 255 }),
        projectSite: varchar("project_site", { length: 255 }),

        packageNo: varchar("package_no", { length: 50 }),
        packageName: varchar("package_name", { length: 255 }),

        // 생성자
        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(),
    }
);

export const initialRfq = pgTable("initial_rfq", {
    id: serial("id").primaryKey(),
    rfqId: integer("rfq_id")
        .notNull()
        .references(() => bRfqs.id),

    initialRfqStatus: varchar("initial_rfq_status", { length: 30 })
        .$type<"DRAFT" | "Init. RFQ Sent" | "S/L Decline" | "Init. RFQ Answered">()
        .default("DRAFT")
        .notNull(),

    vendorId: integer("vendor_id")
        .notNull()
        .references(() => vendors.id),

    dueDate: date("due_date", { mode: "date" })
        .$type<Date>()
        .notNull(),

    validDate: date("valid_date", { mode: "date" })
        .$type<Date>(),

    incotermsCode: varchar("incoterms_code", { length: 20 })
        .references(() => incoterms.code, { onDelete: "set null" }),

    gtc: varchar("gtc", { length: 255 }),
    gtcValidDate: varchar("gtc_valid_date", { length: 255 }),

    classification: varchar("classification", { length: 255 }),
    sparepart: varchar("sparepart", { length: 255 }),

    shortList: boolean('short_list').notNull().default(false),
    returnYn: boolean('return_yn').notNull().default(false),

    cpRequestYn: boolean('cp_request_yn').notNull().default(false),

    prjectGtcYn: boolean('prject_gtc_yn').notNull().default(false),

    returnRevision: integer("return_revision")
    .notNull().default(0),

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

export const finalRfq = pgTable("final_rfq", {
    id: serial("id").primaryKey(),
    rfqId: integer("rfq_id")
        .notNull()
        .references(() => bRfqs.id),

    finalRfqStatus: varchar("final_rfq_status", { length: 30 })
        .$type<"DRAFT" | "Final RFQ Sent" | "Final RFQ Answered">()
        .default("DRAFT")
        .notNull(),

    vendorId: integer("vendor_id")
        .notNull()
        .references(() => vendors.id),

    dueDate: date("due_date", { mode: "date" })
        .$type<Date>()
        .notNull(),

    validDate: date("valid_date", { mode: "date" })
        .$type<Date>(),

    incotermsCode: varchar("incoterms_code", { length: 20 })
        .references(() => incoterms.code, { onDelete: "set null" }),

    gtc: varchar("gtc", { length: 255 }),
    gtcValidDate: varchar("gtc_valid_date", { length: 255 }),

    classification: varchar("classification", { length: 255 }),
    sparepart: varchar("sparepart", { length: 255 }),

    shortList: boolean('short_list').notNull().default(false),
    returnYn: boolean('return_yn').notNull().default(false),

    cpRequestYn: boolean('cp_request_yn').notNull().default(false),

    prjectGtcYn: boolean('prject_gtc_yn').notNull().default(true),

    returnRevision: integer("return_revision")
    .notNull().default(0),

    currency: varchar("currency", { length: 10 }).default("KRW"),

    paymentTermsCode: varchar("payment_terms_code", { length: 50 })
    .references(() => paymentTerms.code, { onDelete: "set null" }),

    taxCode: varchar("tax_code", { length: 255 }).default("VV"),
    deliveryDate: date("delivery_date", { mode: "date" })
      .$type<Date>()
      .notNull(),

    placeOfShipping: varchar("place_of_shipping", { length: 255 }),
    placeOfDestination: varchar("place_of_destination", { length: 255 }),

    firsttimeYn: boolean('firsttime_yn').notNull().default(true),
    materialPriceRelatedYn: boolean("material_price_related_yn").default(false),
    remark: text("remark"),

    vendorRemark: text("vendor_remark"),

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

export const bRfqsAttachments = pgTable(
    "b_rfq_attachments",
    {
      id: serial("id").primaryKey(),
      attachmentType: varchar("attachment_type", { length: 50 }).notNull(),
      serialNo: varchar("serial_no", { length: 50 }).notNull(),
      rfqId: integer("rfq_id")
        .notNull()
        .references(() => bRfqs.id),
      
      // 현재 리비전 정보 (빠른 접근용)
      currentRevision: varchar("current_revision", { length: 10 }).notNull().default("Rev.0"),
      latestRevisionId: integer("latest_revision_id"), // self-reference to bRfqAttachmentRevisions
      
      // 메타 정보
      description: varchar("description", { length: 500 }),
      createdBy: integer("created_by")
        .references(() => users.id, { onDelete: "set null" })
        .notNull(),
      createdAt: timestamp("created_at").defaultNow().notNull(),
      updatedAt: timestamp("updated_at").defaultNow().notNull(),
    }
  )
  
  // 리비전 테이블 (모든 파일 버전 관리)
  export const bRfqAttachmentRevisions = pgTable(
    "b_rfq_attachment_revisions",
    {
      id: serial("id").primaryKey(),
      attachmentId: integer("attachment_id")
        .notNull()
        .references(() => bRfqsAttachments.id, { onDelete: "cascade" }),
        
      // 리비전 정보
      revisionNo: varchar("revision_no", { length: 10 }).notNull(), // "Rev.0", "Rev.1", "Rev.2"
      revisionComment: text("revision_comment"),
      isLatest: boolean("is_latest").notNull().default(true),
      
      // 파일 정보
      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 }),
      
      // 리비전 생성 정보
      createdBy: integer("created_by")
        .references(() => users.id, { onDelete: "set null" })
        .notNull(),
      createdAt: timestamp("created_at").defaultNow().notNull(),
    }, 
    (t) => ({
      // 첨부파일당 하나의 최신 리비전만 허용
      latestRevisionIdx: uniqueIndex('latest_revision_idx')
        .on(t.attachmentId, t.isLatest)
        .where(eq(t.isLatest, true)),
      
      // 첨부파일 + 리비전 번호 유니크
      attachmentRevisionIdx: uniqueIndex('attachment_revision_idx')
        .on(t.attachmentId, t.revisionNo),
    })
  )
  
  // 첨부파일 + 최신 리비전 뷰
  export const attachmentsWithLatestRevisionView = pgView("attachments_with_latest_revision", {
    // 메인 첨부파일 정보
    attachmentId: integer("attachment_id"),
    attachmentType: varchar("attachment_type", { length: 50 }),
    serialNo: varchar("serial_no", { length: 50 }),
    rfqId: integer("rfq_id"),
    description: varchar("description", { length: 500 }),
    currentRevision: varchar("current_revision", { length: 10 }),
    
    // 최신 리비전 파일 정보
    revisionId: integer("revision_id"),
    fileName: varchar("file_name", { length: 255 }),
    originalFileName: varchar("original_file_name", { length: 255 }),
    filePath: varchar("file_path", { length: 512 }),
    fileSize: integer("file_size"),
    fileType: varchar("file_type", { length: 100 }),
    revisionComment: text("revision_comment"),
    
    // 생성/수정 정보
    createdBy: integer("created_by"),
    createdByName: varchar("created_by_name", { length: 255 }),
    createdAt: timestamp("created_at"),
    updatedAt: timestamp("updated_at"),
  }).as(sql`
    SELECT 
      a.id as attachment_id,
      a.attachment_type,
      a.serial_no,
      a.rfq_id,
      a.description,
      a.current_revision,
      
      r.id as revision_id,
      r.file_name,
      r.original_file_name,
      r.file_path,
      r.file_size,
      r.file_type,
      r.revision_comment,
      
      a.created_by,
      u.name as created_by_name,
      a.created_at,
      a.updated_at
    FROM b_rfq_attachments a
    LEFT JOIN b_rfq_attachment_revisions r ON a.latest_revision_id = r.id
    LEFT JOIN users u ON a.created_by = u.id
  `)

// 2. 벤더별 첨부파일 응답 현황 관리
export const vendorAttachmentResponses = pgTable(
    "vendor_attachment_responses", 
    {
        id: serial("id").primaryKey(),
        attachmentId: integer("attachment_id")
            .notNull()
            .references(() => bRfqsAttachments.id, { onDelete: "cascade" }),
        
        vendorId: integer("vendor_id")
            .notNull()
            .references(() => vendors.id, { onDelete: "cascade" }),
            
        rfqType: varchar("rfq_type", { length: 20 })
            .$type<"INITIAL" | "FINAL">()
            .notNull(), // initial_rfq 또는 final_rfq 구분
            
        rfqRecordId: integer("rfq_record_id").notNull(), // initialRfq.id 또는 finalRfq.id
        
        responseStatus: varchar("response_status", { length: 30 })
            .$type<"NOT_RESPONDED" | "RESPONDED" | "REVISION_REQUESTED" | "WAIVED">()
            .default("NOT_RESPONDED")
            .notNull(),
            
        currentRevision: varchar("current_revision", { length: 10 }).default("Rev.0"),
        respondedRevision: varchar("responded_revision", { length: 10 }),
        
        responseComment: text("response_comment"),
        vendorComment: text("vendor_comment"),
        
        // 응답 관련 날짜
        requestedAt: timestamp("requested_at").notNull(),
        respondedAt: timestamp("responded_at"),
        
        createdAt: timestamp("created_at").defaultNow().notNull(),
        updatedAt: timestamp("updated_at").defaultNow().notNull(),
    },(t) => ({
        // attachmentId + vendorId + rfqType 유니크
        vendorResponseIdx: uniqueIndex('vendor_response_idx').on(
          t.attachmentId.asc(),
          t.vendorId.asc(),
          t.rfqType.asc(),
        ),
    }));

// 3. 벤더 응답 첨부파일
export const vendorResponseAttachmentsB = pgTable(
    "vendor_response_attachments_b",
    {
        id: serial("id").primaryKey(),
        vendorResponseId: integer("vendor_response_id")
            .notNull()
            .references(() => vendorAttachmentResponses.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 }),
        
        uploadedBy: integer("uploaded_by")
            .references(() => users.id, { onDelete: "set null" }),
        uploadedAt: timestamp("uploaded_at").defaultNow().notNull(),
    },
);

// 4. 응답 히스토리 추적 (선택사항)
export const vendorResponseHistory = pgTable(
    "vendor_response_history",
    {
        id: serial("id").primaryKey(),
        vendorResponseId: integer("vendor_response_id")
            .notNull()
            .references(() => vendorAttachmentResponses.id, { onDelete: "cascade" }),
            
        action: varchar("action", { length: 50 })
            .$type<"REQUESTED" | "RESPONDED" | "REVISION_REQUESTED" | "REVISED" | "WAIVED">()
            .notNull(),
            
        previousStatus: varchar("previous_status", { length: 30 }),
        newStatus: varchar("new_status", { length: 30 }),
        
        comment: text("comment"),
        
        actionBy: integer("action_by")
            .references(() => users.id, { onDelete: "set null" }),
        actionAt: timestamp("action_at").defaultNow().notNull(),
    },
);

// === 유용한 뷰들 ===

// 1. bRfqs 기본 마스터 뷰 (프로젝트 정보 포함)
export const bRfqsMasterView = pgView("b_rfqs_master", {
    rfqId: integer("rfq_id"),
    rfqCode: varchar("rfq_code", { length: 50 }),
    description: varchar("description", { length: 255 }),
    status: varchar("status", { length: 30 }),
    dueDate: date("due_date"),
    picCode: varchar("pic_code", { length: 50 }),
    picName: varchar("pic_name", { length: 50 }),
    EngPicName: varchar("eng_pic_name", { length: 50 }),
    packageNo: varchar("package_no", { length: 50 }),
    packageName: varchar("package_name", { length: 255 }),
    projectId: integer("project_id"),
    projectCode: varchar("project_code", { length: 50 }),
    projectName: text("project_name"),
    projectType: varchar("project_type", { length: 20 }),
    projectCompany: varchar("project_company", { length: 255 }),
    projectFlag: varchar("project_flag", { length: 255 }),
    projectSite: varchar("project_site", { length: 255 }),
    totalAttachments: integer("total_attachments"),
    createdAt: timestamp("created_at"),
    updatedAt: timestamp("updated_at"),
}).as(sql`
    SELECT 
        br.id as rfq_id,
        br.rfq_code,
        br.description,
        br.status,
        br.due_date,
        br.pic_code,
        br.pic_name,
        br.eng_pic_name,
        br.package_no,
        br.package_name,
        br.project_id,
        p.code as project_code,
        p.name as project_name,
        p.type as project_type,
        br.project_company,
        br.project_flag,
        br.project_site,
        COALESCE(att_count.total_attachments, 0) as total_attachments,
        br.created_at,
        br.updated_at
    FROM b_rfqs br
    LEFT JOIN projects p ON br.project_id = p.id
    LEFT JOIN (
        SELECT rfq_id, COUNT(*) as total_attachments
        FROM b_rfq_attachments
        GROUP BY rfq_id
    ) att_count ON br.id = att_count.rfq_id
`);

// 2. Initial RFQ 상세 뷰 (벤더, 인코텀즈 정보 포함)
export const initialRfqDetailView = pgView("initial_rfq_detail", {
    rfqId: integer("rfq_id"),
    rfqCode: varchar("rfq_code", { length: 50 }),
    rfqStatus: varchar("rfq_status", { length: 30 }),
    initialRfqId: integer("initial_rfq_id"),
    initialRfqStatus: varchar("initial_rfq_status", { length: 30 }),
    vendorId: integer("vendor_id"),
    vendorCode: varchar("vendor_code", { length: 50 }),
    vendorName: varchar("vendor_name", { length: 255 }),
    vendorCountry: varchar("vendor_country", { length: 100 }),
    vendorBusinessSize: varchar("vendor_business_size", { length: 50 }),
    dueDate: date("due_date"),
    validDate: date("valid_date"),
    incotermsCode: varchar("incoterms_code", { length: 20 }),
    incotermsDescription: varchar("incoterms_description", { length: 255 }),
    shortList: boolean("short_list"),
    returnYn: boolean("return_yn"),
    cpRequestYn: boolean("cp_request_yn"),
    prjectGtcYn: boolean("prject_gtc_yn"),
    returnRevision: integer("return_revision"),
    gtc: varchar("gtc", { length: 255 }),
    gtcValidDate: varchar("gtc_valid_date", { length: 255 }),
    classification: varchar("classification", { length: 255 }),
    sparepart: varchar("sparepart", { length: 255 }),
    createdAt: timestamp("created_at"),
    updatedAt: timestamp("updated_at"),
}).as(sql`
    SELECT 
        br.id as rfq_id,
        br.rfq_code,
        br.status as rfq_status,
        ir.id as initial_rfq_id,
        ir.initial_rfq_status,
        ir.vendor_id,
        v.vendor_code,
        v.vendor_name,
        v.country as vendor_country,
        v.business_size as vendor_business_size,
        ir.due_date,
        ir.valid_date,
        ir.incoterms_code,
        inc.description as incoterms_description,
        ir.short_list,
        ir.return_yn,
        ir.cp_request_yn,
        ir.prject_gtc_yn,
        ir.return_revision,
        ir.gtc,
        ir.gtc_valid_date,
        ir.classification,
        ir.sparepart,
        ir.created_at,
        ir.updated_at
    FROM b_rfqs br
    JOIN initial_rfq ir ON br.id = ir.rfq_id
    LEFT JOIN vendors v ON ir.vendor_id = v.id
    LEFT JOIN incoterms inc ON ir.incoterms_code = inc.code
`);

// 3. Final RFQ 상세 뷰 (벤더, 인코텀즈, 결제조건 정보 포함)
export const finalRfqDetailView = pgView("final_rfq_detail", {
    rfqId: integer("rfq_id"),
    rfqCode: varchar("rfq_code", { length: 50 }),
    rfqStatus: varchar("rfq_status", { length: 30 }),
    finalRfqId: integer("final_rfq_id"),
    finalRfqStatus: varchar("final_rfq_status", { length: 30 }),
    vendorId: integer("vendor_id"),
    vendorCode: varchar("vendor_code", { length: 50 }),
    vendorName: varchar("vendor_name", { length: 255 }),
    vendorCountry: varchar("vendor_country", { length: 100 }),
    vendorBusinessSize: varchar("vendor_business_size", { length: 50 }),
    dueDate: date("due_date"),
    validDate: date("valid_date"),
    deliveryDate: date("delivery_date"),
    incotermsCode: varchar("incoterms_code", { length: 20 }),
    incotermsDescription: varchar("incoterms_description", { length: 255 }),
    paymentTermsCode: varchar("payment_terms_code", { length: 50 }),
    paymentTermsDescription: varchar("payment_terms_description", { length: 255 }),
    currency: varchar("currency", { length: 10 }),
    taxCode: varchar("tax_code", { length: 255 }),
    placeOfShipping: varchar("place_of_shipping", { length: 255 }),
    placeOfDestination: varchar("place_of_destination", { length: 255 }),
    shortList: boolean("short_list"),
    returnYn: boolean("return_yn"),
    cpRequestYn: boolean("cp_request_yn"),
    prjectGtcYn: boolean("prject_gtc_yn"),
    firsttimeYn: boolean("firsttime_yn"),
    materialPriceRelatedYn: boolean("material_price_related_yn"),
    returnRevision: integer("return_revision"),
    gtc: varchar("gtc", { length: 255 }),
    gtcValidDate: varchar("gtc_valid_date", { length: 255 }),
    classification: varchar("classification", { length: 255 }),
    sparepart: varchar("sparepart", { length: 255 }),
    remark: text("remark"),
    vendorRemark: text("vendor_remark"),
    createdAt: timestamp("created_at"),
    updatedAt: timestamp("updated_at"),
}).as(sql`
    SELECT 
        br.id as rfq_id,
        br.rfq_code,
        br.status as rfq_status,
        fr.id as final_rfq_id,
        fr.final_rfq_status,
        fr.vendor_id,
        v.vendor_code,
        v.vendor_name,
        v.country as vendor_country,
        v.business_size as vendor_business_size,
        fr.due_date,
        fr.valid_date,
        fr.delivery_date,
        fr.incoterms_code,
        inc.description as incoterms_description,
        fr.payment_terms_code,
        pt.description as payment_terms_description,
        fr.currency,
        fr.tax_code,
        fr.place_of_shipping,
        fr.place_of_destination,
        fr.short_list,
        fr.return_yn,
        fr.cp_request_yn,
        fr.prject_gtc_yn,
        fr.firsttime_yn,
        fr.material_price_related_yn,
        fr.return_revision,
        fr.gtc,
        fr.gtc_valid_date,
        fr.classification,
        fr.sparepart,
        fr.remark,
        fr.vendor_remark,
        fr.created_at,
        fr.updated_at
    FROM b_rfqs br
    JOIN final_rfq fr ON br.id = fr.rfq_id
    LEFT JOIN vendors v ON fr.vendor_id = v.id
    LEFT JOIN incoterms inc ON fr.incoterms_code = inc.code
    LEFT JOIN payment_terms pt ON fr.payment_terms_code = pt.code
`);

// 4. 벤더 응답 현황 요약 뷰
export const vendorResponseSummaryView = pgView("vendor_response_summary", {
    rfqId: integer("rfq_id"),
    rfqCode: varchar("rfq_code", { length: 50 }),
    rfqStatus: varchar("rfq_status", { length: 30 }),
    vendorId: integer("vendor_id"),
    vendorCode: varchar("vendor_code", { length: 50 }),
    vendorName: varchar("vendor_name", { length: 255 }),
    vendorCountry: varchar("vendor_country", { length: 100 }),
    vendorBusinessSize: varchar("vendor_business_size", { length: 50 }),
    rfqType: varchar("rfq_type", { length: 20 }),
    totalAttachments: integer("total_attachments"),
    respondedCount: integer("responded_count"),
    pendingCount: integer("pending_count"),
    waivedCount: integer("waived_count"),
    revisionRequestedCount: integer("revision_requested_count"),
    responseRate: numeric("response_rate", { precision: 5, scale: 2 }),
    completionRate: numeric("completion_rate", { precision: 5, scale: 2 }),
}).as(sql`
    SELECT 
        br.id as rfq_id,
        br.rfq_code,
        br.status as rfq_status,
        v.id as vendor_id,
        v.vendor_code,
        v.vendor_name,
        v.country as vendor_country,
        v.business_size as vendor_business_size,
        var.rfq_type,
        COUNT(var.id) as total_attachments,
        COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count,
        COUNT(CASE WHEN var.response_status = 'NOT_RESPONDED' THEN 1 END) as pending_count,
        COUNT(CASE WHEN var.response_status = 'WAIVED' THEN 1 END) as waived_count,
        COUNT(CASE WHEN var.response_status = 'REVISION_REQUESTED' THEN 1 END) as revision_requested_count,
        ROUND(
            (COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / 
             NULLIF(COUNT(CASE WHEN var.response_status != 'WAIVED' THEN 1 END), 0)), 
            2
        ) as response_rate,
        ROUND(
            ((COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) + 
              COUNT(CASE WHEN var.response_status = 'WAIVED' THEN 1 END)) * 100.0 / COUNT(var.id)), 
            2
        ) as completion_rate
    FROM b_rfqs br
    JOIN b_rfq_attachments bra ON br.id = bra.rfq_id
    JOIN vendor_attachment_responses var ON bra.id = var.attachment_id
    JOIN vendors v ON var.vendor_id = v.id
    GROUP BY br.id, br.rfq_code, br.status, v.id, v.vendor_code, v.vendor_name, v.country, v.business_size, var.rfq_type
`);

// 5. RFQ 전체 진행 현황 대시보드 뷰
export const rfqDashboardView = pgView("rfq_dashboard", {
    rfqId: integer("rfq_id"),
    rfqCode: varchar("rfq_code", { length: 50 }),
    description: varchar("description", { length: 255 }),
    status: varchar("status", { length: 30 }),
    dueDate: date("due_date"),
    projectCode: varchar("project_code", { length: 50 }),
    projectName: text("project_name"),
    packageNo: varchar("package_no", { length: 50 }),
    packageName: varchar("package_name", { length: 255 }),
    picCode: varchar("pic_code", { length: 50 }),
    picName: varchar("pic_name", { length: 50 }),
    engPicName: varchar("eng_pic_name", { length: 50 }),
    projectCompany: varchar("project_company", { length: 255 }),
    projectFlag: varchar("project_flag", { length: 255 }),
    projectSite: varchar("project_site", { length: 255 }),
    totalAttachments: integer("total_attachments"),
    initialVendorCount: integer("initial_vendor_count"),
    finalVendorCount: integer("final_vendor_count"),
    initialResponseRate: numeric("initial_response_rate", { precision: 5, scale: 2 }),
    finalResponseRate: numeric("final_response_rate", { precision: 5, scale: 2 }),
    overallProgress: numeric("overall_progress", { precision: 5, scale: 2 }),
    daysToDeadline: integer("days_to_deadline"),
    createdAt: timestamp("created_at"),
    updatedAt: timestamp("updated_at"),
    remark: text("remark"),
    updatedByName: varchar("updated_by_name", { length: 255 }),
    updatedByEmail: varchar("updated_by_email", { length: 255 }),
}).as(sql`
    -- ② SELECT 절 확장 -------------------------------------------
    SELECT
        br.id               AS rfq_id,
        br.rfq_code,
        br.description,
        br.status,
        br.due_date,
        p.code              AS project_code,
        p.name              AS project_name,
        br.package_no,
        br.package_name,
        br.pic_code,
        br.pic_name,
        br.eng_pic_name,
        br.project_company,
        br.project_flag,
        br.project_site,
        br.remark,
  
        -- 첨부/벤더 요약 -----------------------
        COALESCE(att_count.total_attachments, 0)       AS total_attachments,
        COALESCE(init_summary.vendor_count, 0)         AS initial_vendor_count,
        COALESCE(final_summary.vendor_count, 0)        AS final_vendor_count,
        COALESCE(init_summary.avg_response_rate, 0)    AS initial_response_rate,
        COALESCE(final_summary.avg_response_rate, 0)   AS final_response_rate,
  
        -- 진행률·마감까지 일수 --------------
        CASE 
            WHEN br.status = 'DRAFT'            THEN 0
            WHEN br.status = 'Doc. Received'    THEN 10
            WHEN br.status = 'PIC Assigned'     THEN 20
            WHEN br.status = 'Doc. Confirmed'   THEN 30
            WHEN br.status = 'Init. RFQ Sent'   THEN 40
            WHEN br.status = 'Init. RFQ Answered' THEN 50
            WHEN br.status = 'TBE started'      THEN 60
            WHEN br.status = 'TBE finished'     THEN 70
            WHEN br.status = 'Final RFQ Sent'   THEN 80
            WHEN br.status = 'Quotation Received' THEN 90
            WHEN br.status = 'Vendor Selected'  THEN 100
            ELSE 0
        END                                        AS overall_progress,
        (br.due_date - CURRENT_DATE)               AS days_to_deadline,
  
        br.created_at,
        br.updated_at,
  
        -- 💡 추가되는 컬럼 -------------------
        upd.name    AS updated_by_name,
        upd.email   AS updated_by_email
    FROM b_rfqs br
    LEFT JOIN projects p                     ON br.project_id = p.id
  
    -- ③ 사용자 정보 조인 --------------------
    LEFT JOIN users upd                      ON br.updated_by = upd.id
  
    -- (나머지 이미 있던 JOIN 들은 그대로) -----
    LEFT JOIN (
        SELECT rfq_id, COUNT(*) AS total_attachments
        FROM b_rfq_attachments
        GROUP BY rfq_id
    ) att_count                              ON br.id = att_count.rfq_id
  
    LEFT JOIN (
        SELECT 
            rfq_id, 
            COUNT(DISTINCT vendor_id) AS vendor_count,
            AVG(response_rate)        AS avg_response_rate
        FROM vendor_response_summary
        WHERE rfq_type = 'INITIAL'
        GROUP BY rfq_id
    ) init_summary                           ON br.id = init_summary.rfq_id
  
    LEFT JOIN (
        SELECT 
            rfq_id, 
            COUNT(DISTINCT vendor_id) AS vendor_count,
            AVG(response_rate)        AS avg_response_rate
        FROM vendor_response_summary
        WHERE rfq_type = 'FINAL'
        GROUP BY rfq_id
    ) final_summary                          ON br.id = final_summary.rfq_id
  `);

// 사용 예시 타입 정의
export type VendorAttachmentResponse = typeof vendorAttachmentResponses.$inferSelect;
export type NewVendorAttachmentResponse = typeof vendorAttachmentResponses.$inferInsert;
export type AttachmentRevision = typeof bRfqAttachmentRevisions.$inferSelect;
export type ResponseAttachment = typeof vendorResponseAttachmentsB.$inferSelect;

export type InitialRfqDetailView = typeof initialRfqDetailView.$inferSelect;
export type FinalRfqDetailView = typeof finalRfqDetailView.$inferSelect;
export type RfqDashboardView = typeof rfqDashboardView.$inferSelect;