path: root/db/schema/rfqLast.ts

import {bigint, jsonb, decimal, json,index, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, uniqueIndex, unique } 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 생성"
    | "구매담당지정"
    | "견적요청문서 확정"
    | "TBE 요청"
    | "TBE 완료"
    | "RFQ 발송"
    | "견적접수"
    | "최종업체선정"
    | "RFQ 삭제";


export const rfqsLast = pgTable(
    "rfqs_last",
    {
        id: serial("id").primaryKey(),

        // RFQ 채번 룰에 따라 생성한 넘버
        // 생성 규칙은 `RFQ${userCode}${00001}` 형식으로 순차 증가
        // 구매그룹코드(userCode)별로 일련번호를 증가시켜야 하는지는 강미경프로 답변에 따라 작업
        rfqCode: varchar("rfq_code", { length: 50 }).unique(), // ex) "RFQ-2025-001"


        // ANFNR (RFQ/Bidding No) 로 ECC측 RFQ ID (내부적으로만 사용)
        // IF로 받은 게 아니면 null로 처리되고 유일성 검사에서 제외됨
        ANFNR: varchar("ANFNR", { length: 50 }).unique(),

        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: timestamp("due_date", { withTimezone: true })
            .$type<Date | null>(),
        rfqSendDate: timestamp("rfq_send_date", { withTimezone: true })
            .$type<Date | null>(),

        status: varchar("status", { length: 30 })
            .$type<RfqStatus>()
            .default("RFQ 생성")
            .notNull(),

        rfqSealedYn: boolean("rfq_sealed_yn").default(false),

        EngPicName: varchar("eng_pic_name", { length: 50 }),
        EngPicEmployeeNumber: varchar("eng_pic_employee_number", { 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")
            .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 }),

        // 입찰 사전견적 추가 필드
        biddingNumber: varchar("bidding_number", { length: 50 }),
        contractStartDate: date("contract_start_date", { mode: "date" })
            .$type<Date | null>(),
        contractEndDate: date("contract_end_date", { mode: "date" })
            .$type<Date | null>(),

        //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 }),

        // RFQ 삭제 사유
        deleteReason: text("delete_reason"),

    },

);



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" }),
        
        // 정규화된 incoterms 참조
        incotermsCode: varchar("incoterms_code", { length: 20 })
            .references(() => incoterms.code, { onDelete: "set null" }),
        incotermsDetail: varchar("incoterms_detail", { length: 255 }),
        
        deliveryDate: timestamp("delivery_date", { withTimezone: true })
            .$type<Date | null>(),
        
        contractDuration: varchar("contract_duration", { length: 255 }),
        
        taxCode: varchar("tax_code", { length: 255 }),
        placeOfShipping: varchar("place_of_shipping", { length: 255 }),
        placeOfDestination: varchar("place_of_destination", { length: 255 }),
        remark: text("remark"),
        cancelReason: text("cancel_reason"),
        
        // ===== 업체 선정 관련 컬럼 추가 =====
        // 선정 정보
        isSelected: boolean("is_selected").default(false),
        selectionDate: timestamp("selection_date", { withTimezone: true })
            .$type<Date | null>(),
        selectionReason: text("selection_reason"),
        selectedBy: integer("selected_by")
            .references(() => users.id, { onDelete: "set null" }),
        
        // 가격 및 평가 정보
        totalAmount: decimal("total_amount", { precision: 15, scale: 2 }),
        priceRank: integer("price_rank"),
        technicalScore: decimal("technical_score", { precision: 5, scale: 2 }), // 기술평가 점수 (선택)
        commercialScore: decimal("commercial_score", { precision: 5, scale: 2 }), // 상업평가 점수 (선택)
        totalScore: decimal("total_score", { precision: 5, scale: 2 }), // 종합점수 (선택)
        
        // 선정 승인 프로세스 (선택사항)
        selectionApprovalStatus: varchar("selection_approval_status", { length: 30 })
            .$type<"대기" | "승인" | "반려" | null>(),
        selectionApprovedBy: integer("selection_approved_by")
            .references(() => users.id, { onDelete: "set null" }),
        selectionApprovedAt: timestamp("selection_approved_at", { withTimezone: true })
            .$type<Date | null>(),
        selectionApprovalComment: text("selection_approval_comment"),
        
        // 계약 전환 정보 (선택사항)
        contractStatus: varchar("contract_status", { length: 30 })
            .$type<"대기" | "진행중" | "완료" | "취소" |"입찰진행중"|"일반계약 진행중"| null>(),
        contractCreatedAt: timestamp("contract_created_at", { withTimezone: true })
            .$type<Date | null>(),
        contractNo: varchar("contract_no", { length: 50 }),
        
        // ===== 기존 컬럼들 =====
        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(),
        
        projectGtcYn: boolean('project_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),
        gtcType: varchar("gtc_type", { length: 30 })
            .$type<"general" | "project" | "none" | null>()
            .default("none"),
        
        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_description"), // 오타 수정
        
        sendVersion: integer("send_version").default(0),
        isLatest: boolean("is_latest").notNull().default(true),
        
        // 이메일 발송 추적 필드
        emailSentAt: timestamp("email_sent_at"),
        emailSentTo: text("email_sent_to"),
        emailResentCount: integer("email_resent_count").default(0),
        lastEmailSentAt: timestamp("last_email_sent_at"),
        emailStatus: varchar("email_status", { length: 30 })
            .$type<"pending" | "sent" | "failed" | "bounced" | null>(),
            
        // ===== 추가 메타데이터 =====
        createdAt: timestamp("created_at").defaultNow().notNull(), // 생성일 추가
        createdBy: integer("created_by")
            .references(() => users.id, { onDelete: "set null" }),
    },
    (table) => {
        return {
            // 복합 유니크 인덱스: rfqsLastId + vendorsId + isLatest(true)
            // partial unique index를 사용하여 isLatest=true인 경우에만 유니크 제약
            uniqueLatestVendor: uniqueIndex("unique_latest_vendor")
                .on(table.rfqsLastId, table.vendorsId)
                .where(sql`${table.isLatest} = true`),
            
            // 선정된 업체는 RFQ당 하나만 (partial unique index)
            uniqueSelectedVendor: uniqueIndex("unique_selected_vendor")
                .on(table.rfqsLastId)
                .where(sql`${table.isSelected} = true AND ${table.isLatest} = true`),
            
            // 성능을 위한 추가 인덱스들
            rfqIdIndex: index("idx_rfqs_last_id").on(table.rfqsLastId),
            vendorIdIndex: index("idx_vendors_id").on(table.vendorsId),
            isLatestIndex: index("idx_is_latest").on(table.isLatest),
            isSelectedIndex: index("idx_is_selected").on(table.isSelected),
            priceRankIndex: index("idx_price_rank").on(table.priceRank),
            selectionDateIndex: index("idx_selection_date").on(table.selectionDate),
        };
    }
);

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),
        // 구매 요구사항: 소수점 3자리로 변경 요청, default 1 삭제.
        // 해당 스키마 적용 시 drop prItemsLastView 후 재생성 필요.
        grossWeight: numeric("gross_weight", { precision: 12, scale: 3 })
            .$type<number>(),
        gwUom: varchar("gw_uom", { length: 50 }), // 단위

        specNo: varchar("spec_no", { length: 255 }),
        specUrl: varchar("spec_url", { length: 255 }),
        specification: varchar("specification", { length: 2000 }),
        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"),
            ANFNR: sql<string | null>`${rfqsLast.ANFNR}`.as("ANFNR"),
            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"),

            // 입찰 사전견적 추가 필드
            biddingNumber: sql<string | null>`${rfqsLast.biddingNumber}`.as("bidding_number"),
            contractStartDate: sql<Date | null>`${rfqsLast.contractStartDate}`.as("contract_start_date"),
            contractEndDate: sql<Date | null>`${rfqsLast.contractEndDate}`.as("contract_end_date"),

            // 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"),
            
            // Classification (선급)
            classNo: sql<string | null>`
                CASE 
                    WHEN ${projects.CLS_1} IS NOT NULL AND ${projects.CLS_2} IS NOT NULL 
                        THEN CONCAT(${projects.CLS_1}, '-', ${projects.CLS_2})
                    WHEN ${projects.CLS_1} IS NOT NULL 
                        THEN ${projects.CLS_1}
                    ELSE NULL
                END
            `.as("class_no"),

            // 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"),
            engPicEmployeeNumber: sql<string | null>`${rfqsLast.EngPicEmployeeNumber}`.as("eng_pic_employee_number"),

            // 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"),
            deleteReason: sql<string | null>`${rfqsLast.deleteReason}`.as("delete_reason"),

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

            // 입찰 사전견적 추가 필드
            biddingNumber: sql<string | null>`${rfqsTable.biddingNumber}`.as("bidding_number"),
            contractStartDate: sql<Date | null>`${rfqsTable.contractStartDate}`.as("contract_start_date"),
            contractEndDate: sql<Date | null>`${rfqsTable.contractEndDate}`.as("contract_end_date"),

            // 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 필드
            projectGtcYn: sql<boolean>`${rfqDetailsTable.projectGtcYn}`.as("project_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"),
            gtcType: sql<string | null>`${rfqDetailsTable.gtcType}`.as("gtc_type"),

            // 추가 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"),
            isLatest: sql<boolean>`${rfqDetailsTable.isLatest}`.as("is_latest"),

            sendVersion: sql<string | null>`${rfqDetailsTable.sendVersion}`.as("send_version"),
            emailSentAt: sql<string | null>`${rfqDetailsTable.emailSentAt}`.as("email_sent_at"),
            emailSentTo: sql<string | null>`${rfqDetailsTable.emailSentTo}`.as("email_sent_to"),
            emailResentCount: sql<number | null>`${rfqDetailsTable.emailResentCount}`.as("email_resent_count"),
            lastEmailSentAt: sql<string | null>`${rfqDetailsTable.lastEmailSentAt}`.as("last_email_sent_at"),
            emailStatus: sql<string | null>`${rfqDetailsTable.emailStatus}`.as("email_status"),



            // 업데이트 관련 필드
            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,
            specification: rfqPrItems.specification,
            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,
            biddingNumber: rfqsLast.biddingNumber,
            contractStartDate: rfqsLast.contractStartDate,
            contractEndDate: rfqsLast.contractEndDate,
            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],
        }),
    })
);



export const vendorSelections = pgTable(
    "vendor_selections",
    {
      id: serial("id").primaryKey(),
      
      // RFQ 정보
      rfqId: integer("rfq_id")
        .notNull()
        .references(() => rfqsLast.id, { onDelete: "cascade" }),
      rfqCode: varchar("rfq_code", { length: 50 }),
      
      // 선정된 업체 정보
      vendorId: integer("vendor_id")
        .notNull()
        .references(() => vendors.id, { onDelete: "restrict" }),
      vendorName: varchar("vendor_name", { length: 255 }).notNull(),
      vendorCode: varchar("vendor_code", { length: 50 }).notNull(),
      
      // 선정 금액 정보
      selectedAmount: decimal("selected_amount", { precision: 15, scale: 2 }).notNull(),
      currency: varchar("currency", { length: 10 }).notNull(),
      
      // 선정 사유 및 평가
      selectionReason: text("selection_reason").notNull(),
      priceRank: integer("price_rank"),
      hasConditionDifferences: boolean("has_condition_differences").default(false),
      criticalDifferences: json("critical_differences").$type<string[]>(),
      
      // 선정 승인 정보 (옵션)
      approvalStatus: varchar("approval_status", { length: 30 })
        .$type<"대기" | "승인" | "반려">()
        .default("대기"),
      approvedBy: integer("approved_by")
        .references(() => users.id, { onDelete: "set null" }),
      approvedAt: timestamp("approved_at", { withTimezone: true }),
      approvalComment: text("approval_comment"),
      
      // 메타 정보
      selectedBy: integer("selected_by")
        .references(() => users.id, { onDelete: "set null" }),
      selectedAt: timestamp("selected_at", { withTimezone: true }).notNull(),
      
      createdAt: timestamp("created_at").defaultNow().notNull(),
      updatedAt: timestamp("updated_at").defaultNow().notNull(),
    },
    (table) => {
      return {
        // RFQ당 하나의 선정만 가능
        uniqueRfqSelection: unique().on(table.rfqId),
        // 인덱스
        rfqIdIdx: index("vendor_selections_rfq_id_idx").on(table.rfqId),
        vendorIdIdx: index("vendor_selections_vendor_id_idx").on(table.vendorId),
        approvalStatusIdx: index("vendor_selections_approval_status_idx").on(table.approvalStatus),
      };
    }
  );



  export type PurchaseRequestStatus = 
  | "작성중"        // 임시저장 상태
  | "요청완료"      // 설계 엔지니어가 최종 제출
  | "검토중"        // 구매팀 검토 중
  | "승인"          // 승인되어 RFQ 생성 가능
  | "반려"          // 반려됨
  | "RFQ생성완료";  // rfqsLast로 이관 완료

export const purchaseRequests = pgTable(
  "purchase_requests",
  {
    id: serial("id").primaryKey(),
    
    // 요청 번호 (PR-2025-00001 형식)
    requestCode: varchar("request_code", { length: 50 }).unique().notNull(),
    
    // 프로젝트 정보
    projectId: integer("project_id")
      .references(() => projects.id, { onDelete: "set null" }),
    projectCode: varchar("project_code", { length: 100 }),
    projectName: varchar("project_name", { length: 255 }),
    
    // ITB 관련 필드
    projectCompany: varchar("project_company", { length: 255 }),
    projectSite: varchar("project_site", { length: 255 }),
    classNo: varchar("class_no", { length: 50 }),
    
    // 패키지 정보
    packageNo: varchar("package_no", { length: 50 }),
    packageName: varchar("package_name", { length: 255 }),
    
    // 자재 정보
    majorItemMaterialCategory: varchar("major_item_material_category", { length: 100 }),
    majorItemMaterialDescription: varchar("major_item_material_description", { length: 255 }),
    smCode: varchar("sm_code", { length: 255 }),
    
    // 요청 내용
    requestTitle: varchar("request_title", { length: 255 }).notNull(),
    requestDescription: text("request_description"),
    estimatedBudget: varchar("estimated_budget", { length: 100 }),
    requestedDeliveryDate: timestamp("requested_delivery_date", { withTimezone: true }),
    
    // 아이템 목록 (JSON 배열)
    items: jsonb("items").$type<{
      id: string;
      itemCode: string;
      itemName: string;
      specification: string;
      quantity: number;
      unit: string;
      estimatedUnitPrice?: number;
      remarks?: string;
    }[]>().default([]),
    
    // 상태 관리
    status: varchar("status", { length: 30 })
      .$type<PurchaseRequestStatus>()
      .default("작성중")
      .notNull(),
    
    // 반려 사유
    rejectReason: text("reject_reason"),
    
    // 요청 확정 정보
    confirmedAt: timestamp("confirmed_at", { withTimezone: true }),
    confirmedBy: integer("confirmed_by")
      .references(() => users.id, { onDelete: "set null" }),
    
    // RFQ 생성 정보
    rfqId: integer("rfq_id"),
    rfqCode: varchar("rfq_code", { length: 50 }),
    rfqCreatedAt: timestamp("rfq_created_at", { withTimezone: true }),
    
    // 담당자 정보
    engPicId: integer("eng_pic_id")
      .references(() => users.id, { onDelete: "set null" }),
    engPicName: varchar("eng_pic_name", { length: 50 }),
    
    // 구매 담당자
    purchasePicId: integer("purchase_pic_id")
      .references(() => users.id, { onDelete: "set null" }),
    purchasePicName: varchar("purchase_pic_name", { length: 50 }),
    
    // 메타 정보
    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 purchaseRequestAttachments = pgTable(
  "purchase_request_attachments",
  {
    id: serial("id").primaryKey(),
    requestId: integer("request_id")
      .notNull()
      .references(() => purchaseRequests.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 }),
    
    // 첨부파일 분류
    category: varchar("category", { length: 50 }).notNull().default("설계문서"),
    description: varchar("description", { length: 500 }),
    
    // 메타 정보
    createdBy: integer("created_by")
      .references(() => users.id, { onDelete: "set null" })
      .notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => ({
    requestFileIdx: uniqueIndex("request_file_idx").on(table.requestId, table.fileName),
  })
);



export const purchaseRequestsView = pgView("purchase_requests_view", {
    // 기본 정보
    id: serial("id"),
    requestCode: varchar("request_code", { length: 50 }),
    requestTitle: varchar("request_title", { length: 255 }),
    requestDescription: text("request_description"),
    
    // 프로젝트 정보
    projectId: integer("project_id"),
    projectCode: varchar("project_code", { length: 100 }),
    projectName: varchar("project_name", { length: 255 }),
    projectCompany: varchar("project_company", { length: 255 }),
    projectSite: varchar("project_site", { length: 255 }),
    classNo: varchar("class_no", { length: 50 }),
    
    // 패키지 정보
    packageNo: varchar("package_no", { length: 50 }),
    packageName: varchar("package_name", { length: 255 }),
    
    // 자재 정보
    majorItemMaterialCategory: varchar("major_item_material_category", { length: 100 }),
    majorItemMaterialDescription: varchar("major_item_material_description", { length: 255 }),
    smCode: varchar("sm_code", { length: 255 }),
    
    // 예산 및 납기
    estimatedBudget: varchar("estimated_budget", { length: 100 }),
    requestedDeliveryDate: timestamp("requested_delivery_date", { withTimezone: true }),
    
    // 아이템 정보
    items: jsonb("items"),
    itemCount: integer("item_count"),
    totalQuantity: numeric("total_quantity"),
    totalEstimatedAmount: numeric("total_estimated_amount"),
    
    // 상태 정보
    status: varchar("status", { length: 30 }),
    rejectReason: text("reject_reason"),
    
    // 확정 정보
    confirmedAt: timestamp("confirmed_at", { withTimezone: true }),
    confirmedBy: integer("confirmed_by"),
    confirmedByName: varchar("confirmed_by_name", { length: 100 }),
    
    // RFQ 정보
    rfqId: integer("rfq_id"),
    rfqCode: varchar("rfq_code", { length: 50 }),
    rfqCreatedAt: timestamp("rfq_created_at", { withTimezone: true }),
    
    // 담당자 정보
    engPicId: integer("eng_pic_id"),
    engPicName: varchar("eng_pic_name", { length: 50 }),
    engPicEmail: varchar("eng_pic_email", { length: 100 }),
    
    purchasePicId: integer("purchase_pic_id"),
    purchasePicName: varchar("purchase_pic_name", { length: 50 }),
    purchasePicEmail: varchar("purchase_pic_email", { length: 100 }),
    
    // 첨부파일 정보
    attachmentCount: bigint("attachment_count", { mode: "number" }),
    
    // 생성/수정 정보
    createdBy: integer("created_by"),
    createdByName: varchar("created_by_name", { length: 100 }),
    createdByEmail: varchar("created_by_email", { length: 255 }),
    updatedBy: integer("updated_by"),
    updatedByName: varchar("updated_by_name", { length: 100 }),
    updatedByEmail: varchar("updated_by_email", { length: 255 }),
    createdAt: timestamp("created_at"),
    updatedAt: timestamp("updated_at"),
  }).as(sql`
    SELECT 
      pr.id,
      pr.request_code,
      pr.request_title,
      pr.request_description,
      
      -- 프로젝트 정보
      pr.project_id,
      pr.project_code,
      pr.project_name,
      pr.project_company,
      pr.project_site,
      pr.class_no,
      
      -- 패키지 정보
      pr.package_no,
      pr.package_name,
      
      -- 자재 정보
      pr.major_item_material_category,
      pr.major_item_material_description,
      pr.sm_code,
      
      -- 예산 및 납기
      pr.estimated_budget,
      pr.requested_delivery_date,
      
      -- 아이템 정보
      pr.items,
      jsonb_array_length(pr.items) as item_count,
      (
        SELECT SUM((item->>'quantity')::numeric)
        FROM jsonb_array_elements(pr.items) as item
      ) as total_quantity,
      (
        SELECT SUM(
          (item->>'quantity')::numeric * 
          COALESCE((item->>'estimatedUnitPrice')::numeric, 0)
        )
        FROM jsonb_array_elements(pr.items) as item
      ) as total_estimated_amount,
      
      -- 상태 정보
      pr.status,
      pr.reject_reason,
      
      -- 확정 정보
      pr.confirmed_at,
      pr.confirmed_by,
      cb.name as confirmed_by_name,
      
      -- RFQ 정보
      pr.rfq_id,
      pr.rfq_code,
      pr.rfq_created_at,
      
      -- 담당자 정보
      pr.eng_pic_id,
      pr.eng_pic_name,
      ep.email as eng_pic_email,
      
      pr.purchase_pic_id,
      pr.purchase_pic_name,
      pp.email as purchase_pic_email,
      
      -- 첨부파일 수
      (
        SELECT COUNT(*)
        FROM purchase_request_attachments pra
        WHERE pra.request_id = pr.id
      ) as attachment_count,
      
      -- 생성/수정 정보
      pr.created_by,
      cr.name as created_by_name,
      cr.email as created_by_email,
      pr.updated_by,
      up.name as updated_by_name,
      up.email as updated_by_email,
      pr.created_at,
      pr.updated_at
      
    FROM purchase_requests pr
    LEFT JOIN users cb ON cb.id = pr.confirmed_by
    LEFT JOIN users ep ON ep.id = pr.eng_pic_id
    LEFT JOIN users pp ON pp.id = pr.purchase_pic_id
    LEFT JOIN users cr ON cr.id = pr.created_by
    LEFT JOIN users up ON up.id = pr.updated_by
  `);
  
  export type PurchaseRequestView = typeof purchaseRequestsView.$inferSelect;