(대표님) OCR 박진석프로 요청 대응, rfq 변경된 요구사항 구현

1 files changed, 764 insertions, 0 deletions

diff --git a/db/schema/rfqLast.ts b/db/schema/rfqLast.ts
new file mode 100644
index 00000000..34cb7bf3
--- /dev/null
+++ b/db/schema/rfqLast.ts
@@ -0,0 +1,764 @@
+import { foreignKey, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex } from "drizzle-orm/pg-core";
+import { eq, sql, relations } from "drizzle-orm";
+import { projects } from "./projects";
+import { users } from "./users";
+import { vendors } from "./vendors";
+import { incoterms, paymentTerms } from "./procurementRFQ";
+
+
+export type RfqStatus =
+    | "RFQ 생성"
+    | "구매담당지정"
+    | "견적요청문서 확정"
+    | "Short List 확정"
+    | "TBE 완료"
+    | "RFQ 발송"
+    | "견적접수"
+    | "최종업체선정";
+
+
+export const rfqsLast = pgTable(
+    "rfqs_last",
+    {
+        id: serial("id").primaryKey(),
+
+        rfqCode: varchar("rfq_code", { length: 50 }).unique(), // ex) "RFQ-2025-001"
+
+        projectId: integer("project_id")
+            .references(() => projects.id, { onDelete: "set null" }),
+
+        // 자재코드, 자재명: ECC RFQ는 자재코드, 자재명을 가지지 않음
+        // WHY?: 여러 프로젝트 혹은 여러 시리즈의 동일 품목을 PR로 묶어 올리기 때문
+        // 아래 컬럼은 대표 자재코드, 대표 자재명으로 사용
+        itemCode: varchar("item_code", { length: 100 }),
+        itemName: varchar("item_name", { length: 255 }),
+
+        dueDate: date("due_date", { mode: "date" })
+            .$type<Date>(), // 인터페이스한 값은 dueDate가 없으므로 notNull 제약조건 제거
+
+        rfqSendDate: date("rfq_send_date", { mode: "date" })
+            .$type<Date | null>(), // notNull() 제약조건 제거, null 허용 (ECC에서 수신 후 보내지 않은 RFQ)
+
+        status: varchar("status", { length: 30 })
+            .$type<RfqStatus>()
+            .default("RFQ 생성")
+            .notNull(),
+
+        rfqSealedYn: boolean("rfq_sealed_yn").default(false),
+
+        EngPicName: varchar("eng_pic_name", { length: 50 }),
+
+        packageNo: varchar("package_no", { length: 50 }),
+        packageName: varchar("package_name", { length: 255 }),
+
+        remark: text("remark"),
+
+        pic: integer("pic")
+            .references(() => users.id, { onDelete: "set null" }),
+        picCode: varchar("pic_code", { length: 50 }), // 구매그룹에 대응시킴 (담당자 코드로 3자리)
+        picName: varchar("pic_name", { length: 50 }),
+
+        sentBy: integer("sent_by")
+            .references(() => users.id, { onDelete: "set null" }),
+
+        createdBy: integer("created_by")
+            .notNull()
+            .references(() => users.id, { onDelete: "set null" }),
+
+        updatedBy: integer("updated_by")
+            .notNull()
+            .references(() => users.id, { onDelete: "set null" }),
+
+        createdAt: timestamp("created_at").defaultNow().notNull(),
+        updatedAt: timestamp("updated_at").defaultNow().notNull(),
+
+        //일반견적 추가 필드
+        rfqType: varchar("rfq_type", { length: 255 }),
+        rfqTitle: varchar("rfq_title", { length: 255 }),
+
+        //ITB 추가 필드
+        projectCompany: varchar("project_company", { length: 255 }),
+        projectFlag: varchar("project_flag", { length: 255 }),
+        projectSite: varchar("project_site", { length: 255 }),
+        smCode: varchar("sm_code", { length: 255 }),
+
+        //RFQ 추가 필드
+        prNumber: varchar("pr_number", { length: 255 }),
+        prIssueDate: date("pr_issue_date", { mode: "date" })
+            .$type<Date | null>(),
+        // SS, II, null 값을 가질 수 있음.
+        // SS = 시리즈 통합, II = 품목 통합, 공란 = 통합 없음
+        series: varchar("series", { length: 50 }),
+
+    },
+
+);
+
+
+
+export const rfqLastDetails = pgTable(
+    "rfq_last_details",
+    {
+        id: serial("id").primaryKey(),
+        rfqsLastId: integer("rfqs_last_id")
+            .references(() => rfqsLast.id, { onDelete: "set null" }),
+        vendorsId: integer("vendors_id")
+            .references(() => vendors.id, { onDelete: "set null" }),
+        currency: varchar("currency", { length: 10 }).default("USD"),
+
+        // 정규화된 paymentTerms 참조
+        paymentTermsCode: varchar("payment_terms_code", { length: 50 })
+            .references(() => paymentTerms.code, { onDelete: "set null" }),
+        // paymentTerms 필드는 제거 (코드로 조회)
+
+        // 정규화된 incoterms 참조
+        incotermsCode: varchar("incoterms_code", { length: 20 })
+            .references(() => incoterms.code, { onDelete: "set null" }),
+        incotermsDetail: varchar("incoterms_detail", { length: 255 }),
+
+        deliveryDate: date("delivery_date", { mode: "date" })
+            .$type<Date>()
+            .notNull(),
+
+        contractDuration: varchar("contract_duration", { length: 255 }),
+
+
+        taxCode: varchar("tax_code", { length: 255 }).default("VV"),
+        placeOfShipping: varchar("place_of_shipping", { length: 255 }),
+        placeOfDestination: varchar("place_of_destination", { length: 255 }),
+        remark: text("remark"),
+        cancelReason: text("cancel_reason"),
+        updatedBy: integer("updated_by")
+            .notNull()
+            .references(() => users.id, { onDelete: "set null" }),
+        updatedAt: timestamp("updated_at").defaultNow().notNull(),
+
+        shortList: boolean('short_list').notNull().default(false),
+        returnYn: boolean('return_yn').notNull().default(false),
+        returnedAt: timestamp("returned_at").defaultNow(),
+
+
+        prjectGtcYn: boolean('prject_gtc_yn').notNull().default(false),
+        generalGtcYn: boolean('general_gtc_yn').notNull().default(false),
+        ndaYn: boolean('nda_yn').notNull().default(false),
+        agreementYn: boolean('agreement_yn').notNull().default(false),
+
+        materialPriceRelatedYn: boolean("material_price_related_yn").default(false),
+        sparepartYn: boolean("sparepart_yn").default(false),
+        firstYn: boolean("first_yn").default(false),
+
+        firstDescription: text("first_description"),
+        sparepartDescription: text("sparepart_escription"),
+        
+
+    }
+);
+
+export const rfqPrItems = pgTable(
+    "rfq_pr_items",
+    {
+        id: serial("id").primaryKey(),
+        rfqsLastId: integer("rfqs_last_id")
+            .references(() => rfqsLast.id, { onDelete: "set null" }),
+
+        rfqItem: varchar("rfq_item", { length: 50 }), // 단위
+        prItem: varchar("pr_item", { length: 50 }), // 단위
+        prNo: varchar("pr_no", { length: 50 }), // 단위
+
+        // itemId: integer("item_id")
+        //   .references(() => items.id, { onDelete: "set null" }),
+
+        materialCode: varchar("material_code", { length: 255 }),
+        materialCategory: varchar("material_category", { length: 255 }),
+        acc: varchar("acc", { length: 255 }),
+        materialDescription: varchar("material_description", { length: 255 }),
+        size: varchar("size", { length: 255 }),
+        deliveryDate: date("delivery_date", { mode: "date" })
+            .$type<Date>(),
+        quantity: numeric("quantity", { precision: 12, scale: 2 })
+            .$type<number>()
+            .default(1),
+        uom: varchar("uom", { length: 50 }), // 단위
+        grossWeight: numeric("gross_weight", { precision: 12, scale: 2 })
+            .$type<number>()
+            .default(1),
+        gwUom: varchar("gw_uom", { length: 50 }), // 단위
+
+        specNo: varchar("spec_no", { length: 255 }),
+        specUrl: varchar("spec_url", { length: 255 }),
+        trackingNo: varchar("tracking_no", { length: 255 }),
+
+        majorYn: boolean("major_yn").default(false),
+
+        projectDef: varchar("project_def", { length: 255 }),
+        projectSc: varchar("project_sc", { length: 255 }),
+        projectKl: varchar("project_kl", { length: 255 }),
+        projectLc: varchar("project_lc", { length: 255 }),
+        projectDl: varchar("project_dl", { length: 255 }),
+
+        remark: text("remark"),
+
+    },
+);
+
+
+//view
+export const rfqsLastView = pgView("rfqs_last_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({
+            // Basic RFQ identification
+            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 Type and Title (일반견적 필드)
+            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"),
+
+            // Project information
+            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"),
+
+            // Item information
+            itemCode: sql<string | null>`${rfqsLast.itemCode}`.as("item_code"),
+            itemName: sql<string | null>`${rfqsLast.itemName}`.as("item_name"),
+
+            // Package information
+            packageNo: sql<string | null>`${rfqsLast.packageNo}`.as("package_no"),
+            packageName: sql<string | null>`${rfqsLast.packageName}`.as("package_name"),
+
+            // Engineering PIC
+            engPicName: sql<string | null>`${rfqsLast.EngPicName}`.as("eng_pic_name"),
+
+            // Status and dates
+            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 information
+            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"),
+
+            // 가장 빠른 견적서 제출 날짜
+            earliestQuotationSubmittedAt: sql<Date | null>`(
+                SELECT MIN(submitted_at)
+                FROM rfq_last_vendor_responses
+                WHERE rfqs_last_id = ${rfqsLast.id}
+                AND submitted_at IS NOT NULL
+            )`.as("earliest_quotation_submitted_at"),
+
+            // Vendor details count
+            vendorCount: sql<number>`(
+                SELECT COUNT(*)
+                FROM rfq_last_details
+                WHERE rfqs_last_id = ${rfqsLast.id}
+            )`.as("vendor_count"),
+
+            // Short listed vendor count
+            shortListedVendorCount: sql<number>`(
+                SELECT COUNT(*)
+                FROM rfq_last_details
+                WHERE rfqs_last_id = ${rfqsLast.id}
+                AND short_list = true
+            )`.as("short_listed_vendor_count"),
+
+            // Quotation received count
+            quotationReceivedCount: sql<number>`(
+                SELECT COUNT(DISTINCT vendor_id)
+                FROM rfq_last_vendor_responses
+                WHERE rfqs_last_id  = ${rfqsLast.id}
+                AND submitted_at IS NOT NULL
+            )`.as("quotation_received_count"),
+
+            // Audit information
+            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"),
+
+            // PR Items related information
+            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"),
+
+            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)
+        .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));
+});
+
+// Updated rfqLastDetailsView with new fields
+export const rfqLastDetailsView = pgView("rfq_last_details_view").as((qb) => {
+    const rfqDetailsTable = alias(rfqLastDetails, "rfq_details");
+    const rfqsTable = alias(rfqsLast, "rfqs");
+    const projectsTable = alias(projects, "projects");
+    const vendorsTable = alias(vendors, "vendors");
+    const paymentTermsTable = alias(paymentTerms, "payment_terms");
+    const incotermsTable = alias(incoterms, "incoterms");
+    const updatedByUser = alias(users, "updated_by_user");
+
+    return qb
+        .select({
+            // rfqLastDetails 기본 필드
+            detailId: sql<number>`${rfqDetailsTable.id}`.as("detail_id"),
+            rfqId: sql<number>`${rfqsTable.id}`.as("rfq_id"),
+            rfqCode: sql<string>`${rfqsTable.rfqCode}`.as("rfq_code"),
+
+            // RFQ 타입 정보
+            rfqType: sql<string | null>`${rfqsTable.rfqType}`.as("rfq_type"),
+            rfqTitle: sql<string | null>`${rfqsTable.rfqTitle}`.as("rfq_title"),
+
+            // ITB 관련 정보
+            projectCompany: sql<string | null>`${rfqsTable.projectCompany}`.as("project_company"),
+            projectFlag: sql<string | null>`${rfqsTable.projectFlag}`.as("project_flag"),
+            projectSite: sql<string | null>`${rfqsTable.projectSite}`.as("project_site"),
+            smCode: sql<string | null>`${rfqsTable.smCode}`.as("sm_code"),
+
+            // PR 정보
+            prNumber: sql<string | null>`${rfqsTable.prNumber}`.as("pr_number"),
+            prIssueDate: sql<Date | null>`${rfqsTable.prIssueDate}`.as("pr_issue_date"),
+            series: sql<string | null>`${rfqsTable.series}`.as("series"),
+
+            // 프로젝트 관련 필드
+            projectCode: sql<string | null>`${projectsTable.code}`.as("project_code"),
+            projectName: sql<string | null>`${projectsTable.name}`.as("project_name"),
+
+            // 아이템 관련 필드
+            itemCode: sql<string | null>`${rfqsTable.itemCode}`.as("item_code"),
+            itemName: sql<string | null>`${rfqsTable.itemName}`.as("item_name"),
+
+            // 벤더 관련 필드
+            vendorId: sql<number | null>`${vendorsTable.id}`.as("vendor_id"),
+            vendorName: sql<string | null>`${vendorsTable.vendorName}`.as("vendor_name"),
+            vendorCode: sql<string | null>`${vendorsTable.vendorCode}`.as("vendor_code"),
+            vendorCountry: sql<string | null>`${vendorsTable.country}`.as("vendor_country"),
+
+            // RFQ 상세 정보 필드
+            currency: sql<string | null>`${rfqDetailsTable.currency}`.as("currency"),
+            paymentTermsCode: sql<string | null>`${paymentTermsTable.code}`.as("payment_terms_code"),
+            paymentTermsDescription: sql<string | null>`${paymentTermsTable.description}`.as("payment_terms_description"),
+            incotermsCode: sql<string | null>`${incotermsTable.code}`.as("incoterms_code"),
+            incotermsDescription: sql<string | null>`${incotermsTable.description}`.as("incoterms_description"),
+            incotermsDetail: sql<string | null>`${rfqDetailsTable.incotermsDetail}`.as("incoterms_detail"),
+            deliveryDate: sql<Date | null>`${rfqDetailsTable.deliveryDate}`.as("delivery_date"),
+            contractDuration: sql<string | null>`${rfqDetailsTable.contractDuration}`.as("contract_duration"),
+            taxCode: sql<string | null>`${rfqDetailsTable.taxCode}`.as("tax_code"),
+            placeOfShipping: sql<string | null>`${rfqDetailsTable.placeOfShipping}`.as("place_of_shipping"),
+            placeOfDestination: sql<string | null>`${rfqDetailsTable.placeOfDestination}`.as("place_of_destination"),
+
+            // Boolean 필드들
+            shortList: sql<boolean>`${rfqDetailsTable.shortList}`.as("short_list"),
+            returnYn: sql<boolean>`${rfqDetailsTable.returnYn}`.as("return_yn"),
+            returnedAt: sql<Date | null>`${rfqDetailsTable.returnedAt}`.as("returned_at"),
+
+            // GTC/NDA/Agreement 필드
+            prjectGtcYn: sql<boolean>`${rfqDetailsTable.prjectGtcYn}`.as("prject_gtc_yn"),
+            generalGtcYn: sql<boolean>`${rfqDetailsTable.generalGtcYn}`.as("general_gtc_yn"),
+            ndaYn: sql<boolean>`${rfqDetailsTable.ndaYn}`.as("nda_yn"),
+            agreementYn: sql<boolean>`${rfqDetailsTable.agreementYn}`.as("agreement_yn"),
+
+            // 추가 YN 필드들
+            materialPriceRelatedYn: sql<boolean | null>`${rfqDetailsTable.materialPriceRelatedYn}`.as("material_price_related_yn"),
+            sparepartYn: sql<boolean | null>`${rfqDetailsTable.sparepartYn}`.as("sparepart_yn"),
+            firstYn: sql<boolean | null>`${rfqDetailsTable.firstYn}`.as("first_yn"),
+
+            // Remarks
+            remark: sql<string | null>`${rfqDetailsTable.remark}`.as("remark"),
+            cancelReason: sql<string | null>`${rfqDetailsTable.cancelReason}`.as("cancel_reason"),
+
+            firstDescription: sql<string | null>`${rfqDetailsTable.firstDescription}`.as("first_description"),
+            sparepartDescription: sql<string | null>`${rfqDetailsTable.sparepartDescription}`.as("sparepart_description"),
+
+
+            // 업데이트 관련 필드
+            updatedBy: sql<number>`${rfqDetailsTable.updatedBy}`.as("updated_by"),
+            updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"),
+            updatedAt: sql<Date | null>`${rfqDetailsTable.updatedAt}`.as("updated_at"),
+
+            // PR items 관련 카운트
+            prItemsCount: sql<number>`(
+                SELECT COUNT(*) 
+                FROM rfq_pr_items 
+                WHERE rfqs_last_id = ${rfqsTable.id}
+            )`.as("pr_items_count"),
+
+            majorItemsCount: sql<number>`(
+                SELECT COUNT(*) 
+                FROM rfq_pr_items 
+                WHERE rfqs_last_id = ${rfqsTable.id} 
+                AND major_yn = true
+            )`.as("major_items_count"),
+
+
+            // 견적서 관련 필드
+            hasQuotation: sql<boolean>`(
+                SELECT COUNT(*) > 0
+                FROM rfq_last_vendor_responses
+                WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+            )`.as("has_quotation"),
+
+            quotationStatus: sql<string | null>`(
+                SELECT status
+                FROM rfq_last_vendor_responses
+                WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+                ORDER BY created_at DESC LIMIT 1
+            )`.as("quotation_status"),
+
+            quotationTotalPrice: sql<number | null>`(
+                SELECT total_amount
+                FROM rfq_last_vendor_responses
+                WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+                ORDER BY created_at DESC LIMIT 1
+            )`.as("quotation_total_price"),
+
+            quotationVersion: sql<number | null>`(
+                SELECT response_version
+                FROM rfq_last_vendor_responses
+                WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+                ORDER BY response_version DESC LIMIT 1
+            )`.as("quotation_version"),
+
+            quotationVersionCount: sql<number>`(
+                SELECT COUNT(DISTINCT response_version)
+                FROM rfq_last_vendor_responses
+                WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+            )`.as("quotation_version_count"),
+
+            lastQuotationDate: sql<Date | null>`(
+                SELECT created_at
+                FROM rfq_last_vendor_responses
+                WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+                ORDER BY response_version DESC LIMIT 1
+            )`.as("last_quotation_date"),
+
+            quotationSubmittedAt: sql<Date | null>`(
+                SELECT submitted_at
+                FROM rfq_last_vendor_responses
+                WHERE rfqs_last_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+                ORDER BY response_version DESC LIMIT 1
+            )`.as("quotation_submitted_at")
+        })
+        .from(rfqDetailsTable)
+        .leftJoin(rfqsTable, eq(rfqDetailsTable.rfqsLastId, rfqsTable.id))
+        .leftJoin(projectsTable, eq(rfqsTable.projectId, projectsTable.id))
+        .leftJoin(vendorsTable, eq(rfqDetailsTable.vendorsId, vendorsTable.id))
+        .leftJoin(paymentTermsTable, eq(rfqDetailsTable.paymentTermsCode, paymentTermsTable.code))
+        .leftJoin(incotermsTable, eq(rfqDetailsTable.incotermsCode, incotermsTable.code))
+        .leftJoin(updatedByUser, eq(rfqDetailsTable.updatedBy, updatedByUser.id));
+});
+
+// Updated prItemsLastView to match new table structure
+export const prItemsLastView = pgView("pr_items_last_view").as((qb) => {
+    return qb
+        .select({
+            // PR Item 기본 필드
+            id: rfqPrItems.id,
+            rfqsLastId: rfqPrItems.rfqsLastId,
+            rfqItem: rfqPrItems.rfqItem,
+            prItem: rfqPrItems.prItem,
+            prNo: rfqPrItems.prNo,
+
+            // Material 정보
+            materialCode: rfqPrItems.materialCode,
+            materialCategory: rfqPrItems.materialCategory,
+            acc: rfqPrItems.acc,
+            materialDescription: rfqPrItems.materialDescription,
+            size: rfqPrItems.size,
+
+            // Delivery & Quantity
+            deliveryDate: rfqPrItems.deliveryDate,
+            quantity: rfqPrItems.quantity,
+            uom: rfqPrItems.uom,
+            grossWeight: rfqPrItems.grossWeight,
+            gwUom: rfqPrItems.gwUom,
+
+            // Specification
+            specNo: rfqPrItems.specNo,
+            specUrl: rfqPrItems.specUrl,
+            trackingNo: rfqPrItems.trackingNo,
+
+            // Major flag
+            majorYn: rfqPrItems.majorYn,
+
+            // Project 정보
+            projectDef: rfqPrItems.projectDef,
+            projectSc: rfqPrItems.projectSc,
+            projectKl: rfqPrItems.projectKl,
+            projectLc: rfqPrItems.projectLc,
+            projectDl: rfqPrItems.projectDl,
+
+            // Remark
+            remark: rfqPrItems.remark,
+
+            // RFQ 관련 정보 (from rfqsLast)
+            rfqCode: rfqsLast.rfqCode,
+            rfqType: rfqsLast.rfqType,
+            rfqTitle: rfqsLast.rfqTitle,
+            itemCode: rfqsLast.itemCode,
+            itemName: rfqsLast.itemName,
+            prNumber: rfqsLast.prNumber,
+            prIssueDate: rfqsLast.prIssueDate,
+            series: rfqsLast.series,
+            status: rfqsLast.status,
+
+            // ITB 관련 정보
+            projectCompany: rfqsLast.projectCompany,
+            projectFlag: rfqsLast.projectFlag,
+            projectSite: rfqsLast.projectSite,
+            smCode: rfqsLast.smCode,
+
+            // Package 정보
+            packageNo: rfqsLast.packageNo,
+            packageName: rfqsLast.packageName,
+
+            // Project 정보
+            projectCode: projects.code,
+            projectName: projects.name
+        })
+        .from(rfqPrItems)
+        .leftJoin(rfqsLast, eq(rfqPrItems.rfqsLastId, rfqsLast.id))
+        .leftJoin(projects, eq(rfqsLast.projectId, projects.id));
+});
+
+
+
+export const rfqLastAttachments = pgTable(
+    "rfq_last_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(() => rfqsLast.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 rfqLastAttachmentRevisions = pgTable(
+    "rfq_last_attachment_revisions",
+    {
+        id: serial("id").primaryKey(),
+        attachmentId: integer("attachment_id")
+            .notNull()
+            .references(() => rfqLastAttachments.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) => ({
+        // 첨부파일당 하나의 최신 리비전만 허용
+        rfqLastLatestRevisionIdx: uniqueIndex('rfq_last_latest_revision_idx')
+            .on(t.attachmentId, t.isLatest)
+            .where(eq(t.isLatest, true)),
+
+        // 첨부파일 + 리비전 번호 유니크
+        rfqLastAttachmentRevisionIdx: uniqueIndex('rfq_last_attachment_revision_idx')
+            .on(t.attachmentId, t.revisionNo),
+    })
+)
+
+// 첨부파일 + 최신 리비전 뷰
+export const rfpAttachmentsWithLatestRevisionView = pgView("rfq_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 rfq_last_attachments a
+    LEFT JOIN rfq_last_attachment_revisions r ON a.latest_revision_id = r.id
+    LEFT JOIN users u ON a.created_by = u.id
+  `)
+
+
+export type RfqsLastView = typeof rfqsLastView.$inferSelect;
+export type PrItemsLastView = typeof prItemsLastView.$inferSelect;
+export type RfqLastDetailsView = typeof rfqLastDetailsView.$inferSelect;
+
+
+export const rfqLastRelations = relations(
+    rfqsLast,
+    ({ one, many }) => ({
+        project: one(projects, {
+            fields: [rfqsLast.projectId],
+            references: [projects.id],
+        }),
+        // item: one(items, {
+        //   fields: [rfqsLast.itemId],
+        //   references: [items.id],
+        // }),
+        createdByUser: one(users, {
+            fields: [rfqsLast.createdBy],
+            references: [users.id],
+            relationName: "rfqCreator",
+        }),
+        updatedByUser: one(users, {
+            fields: [rfqsLast.updatedBy],
+            references: [users.id],
+            relationName: "rfqUpdater",
+        }),
+        rfqDetails: many(rfqLastDetails),
+        rfqPrItems: many(rfqPrItems, { relationName: "rfqPrItems" }),
+
+    })
+);
+
+export const prItemsLastRelations = relations(
+    rfqPrItems,
+    ({ one }) => ({
+        rfq: one(rfqsLast, {
+            fields: [rfqPrItems.rfqsLastId],
+            references: [rfqsLast.id],
+            relationName: "rfqPrItems"
+        }),
+    })
+);
+
+// rfqLastDetails 테이블의 관계 정의
+export const rfqDetailsLastRelations = relations(
+    rfqLastDetails,
+    ({ one }) => ({
+        rfq: one(rfqsLast, {
+            fields: [rfqLastDetails.rfqsLastId],
+            references: [rfqsLast.id],
+        }),
+        vendor: one(vendors, {
+            fields: [rfqLastDetails.vendorsId],
+            references: [vendors.id],
+        }),
+        paymentTerms: one(paymentTerms, {
+            fields: [rfqLastDetails.paymentTermsCode],
+            references: [paymentTerms.code],
+        }),
+        incoterms: one(incoterms, {
+            fields: [rfqLastDetails.incotermsCode],
+            references: [incoterms.code],
+        }),
+        updatedByUser: one(users, {
+            fields: [rfqLastDetails.updatedBy],
+            references: [users.id],
+        }),
+    })
+);
+