import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, uniqueIndex, foreignKey } from "drizzle-orm/pg-core"; import { eq, sql, and, relations } 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() .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() .notNull(), validDate: date("valid_date", { mode: "date" }) .$type(), 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), rfqRevision: integer("rfq_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() .notNull(), validDate: date("valid_date", { mode: "date" }) .$type(), 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() .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"), revisionRequestComment: text("revision_request_comment"), // 응답 관련 날짜 requestedAt: timestamp("requested_at").notNull(), respondedAt: timestamp("responded_at"), revisionRequestedAt: timestamp("revision_requested_at"), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), createdBy: integer("created_by") .references(() => users.id, { onDelete: "set null" }) , updatedBy: integer("updated_by") .references(() => users.id, { onDelete: "set null" }) , }, (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 }), vendorCategory: varchar("vendor_category", { 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"), rfqRevision: integer("rfq_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, v.vendor_category as vendor_category, 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.rfq_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_with_types 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 vendorResponseSummaryView2 = 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; export const bRfqsRelations = relations(bRfqs, ({ one, many }) => ({ // 단일 관계 project: one(projects, { fields: [bRfqs.projectId], references: [projects.id], }), createdByUser: one(users, { fields: [bRfqs.createdBy], references: [users.id], relationName: "bRfqCreatedBy" }), updatedByUser: one(users, { fields: [bRfqs.updatedBy], references: [users.id], relationName: "bRfqUpdatedBy" }), // 다중 관계 attachments: many(bRfqsAttachments), initialRfqs: many(initialRfq), finalRfqs: many(finalRfq), })); // bRfqsAttachments 관계 정의 export const bRfqsAttachmentsRelations = relations(bRfqsAttachments, ({ one, many }) => ({ // 단일 관계 rfq: one(bRfqs, { fields: [bRfqsAttachments.rfqId], references: [bRfqs.id], }), createdByUser: one(users, { fields: [bRfqsAttachments.createdBy], references: [users.id], }), latestRevision: one(bRfqAttachmentRevisions, { fields: [bRfqsAttachments.latestRevisionId], references: [bRfqAttachmentRevisions.id], relationName: "attachmentLatestRevision" }), // 다중 관계 revisions: many(bRfqAttachmentRevisions), vendorResponses: many(vendorAttachmentResponses), })); // bRfqAttachmentRevisions 관계 정의 export const bRfqAttachmentRevisionsRelations = relations(bRfqAttachmentRevisions, ({ one }) => ({ attachment: one(bRfqsAttachments, { fields: [bRfqAttachmentRevisions.attachmentId], references: [bRfqsAttachments.id], }), createdByUser: one(users, { fields: [bRfqAttachmentRevisions.createdBy], references: [users.id], }), })); // vendorAttachmentResponses 관계 정의 export const vendorAttachmentResponsesRelations = relations(vendorAttachmentResponses, ({ one, many }) => ({ // 단일 관계 attachment: one(bRfqsAttachments, { fields: [vendorAttachmentResponses.attachmentId], references: [bRfqsAttachments.id], }), vendor: one(vendors, { fields: [vendorAttachmentResponses.vendorId], references: [vendors.id], }), // 다중 관계 responseAttachments: many(vendorResponseAttachmentsB), history: many(vendorResponseHistory), })); // vendorResponseAttachmentsB 관계 정의 export const vendorResponseAttachmentsBRelations = relations(vendorResponseAttachmentsB, ({ one }) => ({ vendorResponse: one(vendorAttachmentResponses, { fields: [vendorResponseAttachmentsB.vendorResponseId], references: [vendorAttachmentResponses.id], }), uploadedByUser: one(users, { fields: [vendorResponseAttachmentsB.uploadedBy], references: [users.id], }), })); // vendorResponseHistory 관계 정의 export const vendorResponseHistoryRelations_old = relations(vendorResponseHistory, ({ one }) => ({ vendorResponse: one(vendorAttachmentResponses, { fields: [vendorResponseHistory.vendorResponseId], references: [vendorAttachmentResponses.id], }), actionByUser: one(users, { fields: [vendorResponseHistory.actionBy], references: [users.id], }), })); // initialRfq 관계 정의 export const initialRfqRelations = relations(initialRfq, ({ one }) => ({ rfq: one(bRfqs, { fields: [initialRfq.rfqId], references: [bRfqs.id], }), vendor: one(vendors, { fields: [initialRfq.vendorId], references: [vendors.id], }), })); // finalRfq 관계 정의 export const finalRfqRelations = relations(finalRfq, ({ one }) => ({ rfq: one(bRfqs, { fields: [finalRfq.rfqId], references: [bRfqs.id], }), vendor: one(vendors, { fields: [finalRfq.vendorId], references: [vendors.id], }), })); // 업데이트된 vendorResponseAttachmentsEnhanced 뷰 export const vendorResponseAttachmentsEnhanced = pgView("vendor_response_attachments_enhanced", { // 벤더 응답 파일 기본 정보 responseAttachmentId: integer("response_attachment_id"), vendorResponseId: integer("vendor_response_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 }), description: varchar("description", { length: 500 }), uploadedAt: timestamp("uploaded_at"), // 응답 기본 정보 attachmentId: integer("attachment_id"), vendorId: integer("vendor_id"), rfqType: varchar("rfq_type", { length: 20 }), rfqRecordId: integer("rfq_record_id"), responseStatus: varchar("response_status", { length: 30 }), currentRevision: varchar("current_revision", { length: 10 }), respondedRevision: varchar("responded_revision", { length: 10 }), // 코멘트 관련 필드들 (새로 추가된 필드 포함) responseComment: text("response_comment"), vendorComment: text("vendor_comment"), revisionRequestComment: text("revision_request_comment"), // 새로 추가 // 날짜 관련 필드들 (새로 추가된 필드 포함) requestedAt: timestamp("requested_at"), respondedAt: timestamp("responded_at"), revisionRequestedAt: timestamp("revision_requested_at"), // 새로 추가 // 첨부파일 정보 attachmentType: varchar("attachment_type", { length: 50 }), serialNo: varchar("serial_no", { length: 50 }), rfqId: integer("rfq_id"), // 벤더 정보 vendorCode: varchar("vendor_code", { length: 50 }), vendorName: varchar("vendor_name", { length: 255 }), vendorCountry: varchar("vendor_country", { length: 100 }), // 발주처 현재 리비전 정보 latestClientRevisionId: integer("latest_client_revision_id"), latestClientRevisionNo: varchar("latest_client_revision_no", { length: 10 }), latestClientFileName: varchar("latest_client_file_name", { length: 255 }), // 리비전 비교 정보 isVersionMatched: boolean("is_version_matched"), versionLag: integer("version_lag"), // 몇 버전 뒤처져 있는지 needsUpdate: boolean("needs_update"), // 응답 파일 순서 (같은 응답에 대한 여러 파일이 있을 경우) fileSequence: integer("file_sequence"), isLatestResponseFile: boolean("is_latest_response_file"), }).as(sql` SELECT vra.id as response_attachment_id, vra.vendor_response_id, vra.file_name, vra.original_file_name, vra.file_path, vra.file_size, vra.file_type, vra.description, vra.uploaded_at, -- 응답 기본 정보 var.attachment_id, var.vendor_id, var.rfq_type, var.rfq_record_id, var.response_status, var.current_revision, var.responded_revision, -- 코멘트 (새로 추가된 필드 포함) var.response_comment, var.vendor_comment, var.revision_request_comment, -- 날짜 (새로 추가된 필드 포함) var.requested_at, var.responded_at, var.revision_requested_at, -- 첨부파일 정보 ba.attachment_type, ba.serial_no, ba.rfq_id, -- 벤더 정보 v.vendor_code, v.vendor_name, v.country as vendor_country, -- 발주처 현재 리비전 정보 latest_rev.id as latest_client_revision_id, latest_rev.revision_no as latest_client_revision_no, latest_rev.original_file_name as latest_client_file_name, -- 리비전 비교 CASE WHEN var.responded_revision = ba.current_revision THEN true ELSE false END as is_version_matched, -- 버전 차이 계산 (Rev.0, Rev.1 형태 가정) CASE WHEN var.responded_revision IS NULL THEN NULL WHEN ba.current_revision IS NULL THEN NULL ELSE CAST(SUBSTRING(ba.current_revision FROM '[0-9]+') AS INTEGER) - CAST(SUBSTRING(var.responded_revision FROM '[0-9]+') AS INTEGER) END as version_lag, CASE WHEN var.response_status = 'RESPONDED' AND var.responded_revision != ba.current_revision THEN true ELSE false END as needs_update, -- 파일 순서 ROW_NUMBER() OVER ( PARTITION BY var.id ORDER BY vra.uploaded_at DESC ) as file_sequence, -- 최신 응답 파일 여부 CASE WHEN ROW_NUMBER() OVER ( PARTITION BY var.id ORDER BY vra.uploaded_at DESC ) = 1 THEN true ELSE false END as is_latest_response_file FROM vendor_response_attachments_b vra JOIN vendor_attachment_responses var ON vra.vendor_response_id = var.id JOIN b_rfq_attachments ba ON var.attachment_id = ba.id LEFT JOIN vendors v ON var.vendor_id = v.id LEFT JOIN b_rfq_attachment_revisions latest_rev ON ba.latest_revision_id = latest_rev.id `); // 2. 첨부파일별 리비전 히스토리 전체 뷰 export const attachmentRevisionHistoryView = pgView("attachment_revision_history", { rfqId: integer("rfq_id"), rfqCode: varchar("rfq_code", { length: 50 }), attachmentId: integer("attachment_id"), attachmentType: varchar("attachment_type", { length: 50 }), serialNo: varchar("serial_no", { length: 50 }), // 발주처 리비전 정보 clientRevisionId: integer("client_revision_id"), clientRevisionNo: varchar("client_revision_no", { length: 10 }), clientFileName: varchar("client_file_name", { length: 255 }), clientFilePath: varchar("client_file_path", { length: 512 }), clientFileSize: integer("client_file_size"), clientRevisionComment: text("client_revision_comment"), clientRevisionCreatedAt: timestamp("client_revision_created_at"), isLatestClientRevision: boolean("is_latest_client_revision"), // 이 리비전에 대한 벤더 응답 통계 totalVendorResponses: integer("total_vendor_responses"), respondedVendors: integer("responded_vendors"), pendingVendors: integer("pending_vendors"), totalResponseFiles: integer("total_response_files"), }).as(sql` SELECT br.id as rfq_id, br.rfq_code, ba.id as attachment_id, ba.attachment_type, ba.serial_no, -- 발주처 리비전 정보 rev.id as client_revision_id, rev.revision_no as client_revision_no, rev.original_file_name as client_file_name, rev.file_size as client_file_size, rev.file_path as client_file_path, rev.revision_comment as client_revision_comment, rev.created_at as client_revision_created_at, rev.is_latest as is_latest_client_revision, -- 벤더 응답 통계 COALESCE(response_stats.total_responses, 0) as total_vendor_responses, COALESCE(response_stats.responded_count, 0) as responded_vendors, COALESCE(response_stats.pending_count, 0) as pending_vendors, COALESCE(response_stats.total_files, 0) as total_response_files FROM b_rfqs br JOIN b_rfq_attachments ba ON br.id = ba.rfq_id JOIN b_rfq_attachment_revisions rev ON ba.id = rev.attachment_id LEFT JOIN ( SELECT var.attachment_id, COUNT(*) as total_responses, 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(vra.id) as total_files FROM vendor_attachment_responses var LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id GROUP BY var.attachment_id ) response_stats ON ba.id = response_stats.attachment_id ORDER BY ba.id, rev.created_at DESC `); // 3. 벤더별 응답 현황 상세 뷰 (리비전 정보 포함) // 업데이트된 vendorResponseDetailView export const vendorResponseDetailView = pgView("vendor_response_detail", { // 기본 식별 정보 responseId: integer("response_id"), rfqId: integer("rfq_id"), rfqCode: varchar("rfq_code", { length: 50 }), rfqType: varchar("rfq_type", { length: 20 }), rfqRecordId: integer("rfq_record_id"), // 첨부파일 정보 attachmentId: integer("attachment_id"), attachmentType: varchar("attachment_type", { length: 50 }), serialNo: varchar("serial_no", { length: 50 }), attachmentDescription: varchar("attachment_description", { length: 500 }), // 벤더 정보 vendorId: integer("vendor_id"), vendorCode: varchar("vendor_code", { length: 50 }), vendorName: varchar("vendor_name", { length: 255 }), vendorCountry: varchar("vendor_country", { length: 100 }), // 응답 상태 정보 responseStatus: varchar("response_status", { length: 30 }), currentRevision: varchar("current_revision", { length: 10 }), respondedRevision: varchar("responded_revision", { length: 10 }), // 코멘트 관련 필드들 (새로 추가된 필드 포함) responseComment: text("response_comment"), vendorComment: text("vendor_comment"), revisionRequestComment: text("revision_request_comment"), // 새로 추가 // 날짜 관련 필드들 (새로 추가된 필드 포함) requestedAt: timestamp("requested_at"), respondedAt: timestamp("responded_at"), revisionRequestedAt: timestamp("revision_requested_at"), // 새로 추가 // 발주처 최신 리비전 정보 latestClientRevisionNo: varchar("latest_client_revision_no", { length: 10 }), latestClientFileName: varchar("latest_client_file_name", { length: 255 }), latestClientFileSize: integer("latest_client_file_size"), latestClientRevisionComment: text("latest_client_revision_comment"), // 리비전 분석 isVersionMatched: boolean("is_version_matched"), versionLag: integer("version_lag"), needsUpdate: boolean("needs_update"), hasMultipleRevisions: boolean("has_multiple_revisions"), // 응답 파일 통계 totalResponseFiles: integer("total_response_files"), latestResponseFileName: varchar("latest_response_file_name", { length: 255 }), latestResponseFileSize: integer("latest_response_file_size"), latestResponseUploadedAt: timestamp("latest_response_uploaded_at"), // 효과적인 상태 (UI 표시용) effectiveStatus: varchar("effective_status", { length: 50 }), }).as(sql` SELECT var.id as response_id, ba.rfq_id, br.rfq_code, var.rfq_type, var.rfq_record_id, -- 첨부파일 정보 ba.id as attachment_id, ba.attachment_type, ba.serial_no, ba.description as attachment_description, -- 벤더 정보 v.id as vendor_id, v.vendor_code, v.vendor_name, v.country as vendor_country, -- 응답 상태 var.response_status, var.current_revision, var.responded_revision, -- 코멘트 (새로 추가된 필드 포함) var.response_comment, var.vendor_comment, var.revision_request_comment, -- 날짜 (새로 추가된 필드 포함) var.requested_at, var.responded_at, var.revision_requested_at, -- 발주처 최신 리비전 latest_rev.revision_no as latest_client_revision_no, latest_rev.original_file_name as latest_client_file_name, latest_rev.file_size as latest_client_file_size, latest_rev.revision_comment as latest_client_revision_comment, -- 리비전 분석 CASE WHEN var.responded_revision = ba.current_revision THEN true ELSE false END as is_version_matched, CASE WHEN var.responded_revision IS NULL OR ba.current_revision IS NULL THEN NULL ELSE CAST(SUBSTRING(ba.current_revision FROM '[0-9]+') AS INTEGER) - CAST(SUBSTRING(var.responded_revision FROM '[0-9]+') AS INTEGER) END as version_lag, CASE WHEN var.response_status = 'RESPONDED' AND var.responded_revision != ba.current_revision THEN true ELSE false END as needs_update, CASE WHEN revision_count.total_revisions > 1 THEN true ELSE false END as has_multiple_revisions, -- 응답 파일 정보 COALESCE(file_stats.total_files, 0) as total_response_files, file_stats.latest_file_name as latest_response_file_name, file_stats.latest_file_size as latest_response_file_size, file_stats.latest_uploaded_at as latest_response_uploaded_at, -- 효과적인 상태 CASE WHEN var.response_status = 'NOT_RESPONDED' THEN 'NOT_RESPONDED' WHEN var.response_status = 'WAIVED' THEN 'WAIVED' WHEN var.response_status = 'REVISION_REQUESTED' THEN 'REVISION_REQUESTED' WHEN var.response_status = 'RESPONDED' AND var.responded_revision = ba.current_revision THEN 'UP_TO_DATE' WHEN var.response_status = 'RESPONDED' AND var.responded_revision != ba.current_revision THEN 'VERSION_MISMATCH' ELSE var.response_status END as effective_status FROM vendor_attachment_responses var JOIN b_rfq_attachments ba ON var.attachment_id = ba.id JOIN b_rfqs br ON ba.rfq_id = br.id LEFT JOIN vendors v ON var.vendor_id = v.id LEFT JOIN b_rfq_attachment_revisions latest_rev ON ba.latest_revision_id = latest_rev.id LEFT JOIN ( SELECT attachment_id, COUNT(*) as total_revisions FROM b_rfq_attachment_revisions GROUP BY attachment_id ) revision_count ON ba.id = revision_count.attachment_id LEFT JOIN ( SELECT vendor_response_id, COUNT(*) as total_files, MAX(original_file_name) as latest_file_name, MAX(file_size) as latest_file_size, MAX(uploaded_at) as latest_uploaded_at FROM vendor_response_attachments_b GROUP BY vendor_response_id ) file_stats ON var.id = file_stats.vendor_response_id `); // 4. RFQ 진행 현황 요약 뷰 (리비전 정보 포함) export const rfqProgressSummaryView = pgView("rfq_progress_summary", { rfqId: integer("rfq_id"), rfqCode: varchar("rfq_code", { length: 50 }), rfqStatus: varchar("rfq_status", { length: 30 }), dueDate: date("due_date"), daysToDeadline: integer("days_to_deadline"), // 첨부파일 통계 totalAttachments: integer("total_attachments"), attachmentsWithMultipleRevisions: integer("attachments_with_multiple_revisions"), totalClientRevisions: integer("total_client_revisions"), // 응답 통계 (INITIAL) initialVendorCount: integer("initial_vendor_count"), initialTotalResponses: integer("initial_total_responses"), initialRespondedCount: integer("initial_responded_count"), initialUpToDateCount: integer("initial_up_to_date_count"), initialVersionMismatchCount: integer("initial_version_mismatch_count"), initialResponseRate: numeric("initial_response_rate", { precision: 5, scale: 2 }), initialVersionMatchRate: numeric("initial_version_match_rate", { precision: 5, scale: 2 }), // 응답 통계 (FINAL) finalVendorCount: integer("final_vendor_count"), finalTotalResponses: integer("final_total_responses"), finalRespondedCount: integer("final_responded_count"), finalUpToDateCount: integer("final_up_to_date_count"), finalVersionMismatchCount: integer("final_version_mismatch_count"), finalResponseRate: numeric("final_response_rate", { precision: 5, scale: 2 }), finalVersionMatchRate: numeric("final_version_match_rate", { precision: 5, scale: 2 }), // 전체 파일 통계 totalResponseFiles: integer("total_response_files"), }).as(sql` SELECT br.id as rfq_id, br.rfq_code, br.status as rfq_status, br.due_date, (br.due_date - CURRENT_DATE) as days_to_deadline, -- 첨부파일 통계 attachment_stats.total_attachments, attachment_stats.attachments_with_multiple_revisions, attachment_stats.total_client_revisions, -- Initial RFQ 통계 COALESCE(initial_stats.vendor_count, 0) as initial_vendor_count, COALESCE(initial_stats.total_responses, 0) as initial_total_responses, COALESCE(initial_stats.responded_count, 0) as initial_responded_count, COALESCE(initial_stats.up_to_date_count, 0) as initial_up_to_date_count, COALESCE(initial_stats.version_mismatch_count, 0) as initial_version_mismatch_count, COALESCE(initial_stats.response_rate, 0) as initial_response_rate, COALESCE(initial_stats.version_match_rate, 0) as initial_version_match_rate, -- Final RFQ 통계 COALESCE(final_stats.vendor_count, 0) as final_vendor_count, COALESCE(final_stats.total_responses, 0) as final_total_responses, COALESCE(final_stats.responded_count, 0) as final_responded_count, COALESCE(final_stats.up_to_date_count, 0) as final_up_to_date_count, COALESCE(final_stats.version_mismatch_count, 0) as final_version_mismatch_count, COALESCE(final_stats.response_rate, 0) as final_response_rate, COALESCE(final_stats.version_match_rate, 0) as final_version_match_rate, COALESCE(file_stats.total_files, 0) as total_response_files FROM b_rfqs br LEFT JOIN ( SELECT ba.rfq_id, COUNT(*) as total_attachments, COUNT(CASE WHEN rev_count.total_revisions > 1 THEN 1 END) as attachments_with_multiple_revisions, SUM(rev_count.total_revisions) as total_client_revisions FROM b_rfq_attachments ba LEFT JOIN ( SELECT attachment_id, COUNT(*) as total_revisions FROM b_rfq_attachment_revisions GROUP BY attachment_id ) rev_count ON ba.id = rev_count.attachment_id GROUP BY ba.rfq_id ) attachment_stats ON br.id = attachment_stats.rfq_id LEFT JOIN ( SELECT br.id as rfq_id, COUNT(DISTINCT var.vendor_id) as vendor_count, COUNT(*) as total_responses, COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count, COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) as up_to_date_count, COUNT(CASE WHEN vrd.effective_status = 'VERSION_MISMATCH' THEN 1 END) as version_mismatch_count, ROUND( COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0), 2 ) as response_rate, ROUND( COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) * 100.0 / NULLIF(COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END), 0), 2 ) as version_match_rate FROM b_rfqs br JOIN vendor_response_detail vrd ON br.id = vrd.rfq_id JOIN vendor_attachment_responses var ON vrd.response_id = var.id WHERE var.rfq_type = 'INITIAL' GROUP BY br.id ) initial_stats ON br.id = initial_stats.rfq_id LEFT JOIN ( SELECT br.id as rfq_id, COUNT(DISTINCT var.vendor_id) as vendor_count, COUNT(*) as total_responses, COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count, COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) as up_to_date_count, COUNT(CASE WHEN vrd.effective_status = 'VERSION_MISMATCH' THEN 1 END) as version_mismatch_count, ROUND( COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0), 2 ) as response_rate, ROUND( COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) * 100.0 / NULLIF(COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END), 0), 2 ) as version_match_rate FROM b_rfqs br JOIN vendor_response_detail vrd ON br.id = vrd.rfq_id JOIN vendor_attachment_responses var ON vrd.response_id = var.id WHERE var.rfq_type = 'FINAL' GROUP BY br.id ) final_stats ON br.id = final_stats.rfq_id LEFT JOIN ( SELECT br.id as rfq_id, COUNT(vra.id) as total_files FROM b_rfqs br JOIN b_rfq_attachments ba ON br.id = ba.rfq_id JOIN vendor_attachment_responses var ON ba.id = var.attachment_id LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id GROUP BY br.id ) file_stats ON br.id = file_stats.rfq_id `); // 타입 정의 export type VendorResponseAttachmentEnhanced = typeof vendorResponseAttachmentsEnhanced.$inferSelect; export type AttachmentRevisionHistory = typeof attachmentRevisionHistoryView.$inferSelect; export type VendorResponseDetail = typeof vendorResponseDetailView.$inferSelect; export type RfqProgressSummary = typeof rfqProgressSummaryView.$inferSelect;