path: root/db/schema/rfqLast.ts

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"),

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

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

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

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

            majorItemsCount: sql<number>`(
                SELECT COUNT(*) 
                FROM rfq_pr_items 
                WHERE rfqs_last_id = ${rfqsLast.id}
                AND major_yn = true
            )`.as("major_items_count")
        })
        .from(rfqsLast)
        .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 type RfqLastAttachments = typeof rfqLastAttachments.$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],
        }),
    })
);