path: root/db/schema/bRfq.ts

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

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

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

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

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

        remark: text("remark"),

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

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

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

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

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

        // 생성자
        createdBy: integer("created_by")
            .notNull()
            .references(() => users.id, { onDelete: "set null" }),

        updatedBy: integer("updated_by")
            .notNull()
            .references(() => users.id, { onDelete: "set null" }),

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

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

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

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

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

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

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

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

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

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

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

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

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

    rfqRevision: integer("rfq_revision")
        .notNull().default(0),

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    vendorRemark: text("vendor_remark"),

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

export const bRfqsAttachments = pgTable(
    "b_rfq_attachments",
    {
        id: serial("id").primaryKey(),
        attachmentType: varchar("attachment_type", { length: 50 }).notNull(),
        serialNo: varchar("serial_no", { length: 50 }).notNull(),
        rfqId: integer("rfq_id")
            .notNull()
            .references(() => bRfqs.id),

        // 현재 리비전 정보 (빠른 접근용)
        currentRevision: varchar("current_revision", { length: 10 }).notNull().default("Rev.0"),
        latestRevisionId: integer("latest_revision_id"), // self-reference to bRfqAttachmentRevisions

        // 메타 정보
        description: varchar("description", { length: 500 }),
        createdBy: integer("created_by")
            .references(() => users.id, { onDelete: "set null" })
            .notNull(),
        createdAt: timestamp("created_at").defaultNow().notNull(),
        updatedAt: timestamp("updated_at").defaultNow().notNull(),
    }
)

// 리비전 테이블 (모든 파일 버전 관리)
export const bRfqAttachmentRevisions = pgTable(
    "b_rfq_attachment_revisions",
    {
        id: serial("id").primaryKey(),
        attachmentId: integer("attachment_id")
            .notNull()
            .references(() => bRfqsAttachments.id, { onDelete: "cascade" }),

        // 리비전 정보
        revisionNo: varchar("revision_no", { length: 10 }).notNull(), // "Rev.0", "Rev.1", "Rev.2"
        revisionComment: text("revision_comment"),
        isLatest: boolean("is_latest").notNull().default(true),

        // 파일 정보
        fileName: varchar("file_name", { length: 255 }).notNull(),
        originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
        filePath: varchar("file_path", { length: 512 }).notNull(),
        fileSize: integer("file_size"),
        fileType: varchar("file_type", { length: 100 }),

        // 리비전 생성 정보
        createdBy: integer("created_by")
            .references(() => users.id, { onDelete: "set null" })
            .notNull(),
        createdAt: timestamp("created_at").defaultNow().notNull(),

        
    },
    (t) => ({
        // 첨부파일당 하나의 최신 리비전만 허용
        latestRevisionIdx: uniqueIndex('latest_revision_idx')
            .on(t.attachmentId, t.isLatest)
            .where(eq(t.isLatest, true)),

        // 첨부파일 + 리비전 번호 유니크
        attachmentRevisionIdx: uniqueIndex('attachment_revision_idx')
            .on(t.attachmentId, t.revisionNo),
    })
)

// 첨부파일 + 최신 리비전 뷰
export const attachmentsWithLatestRevisionView = pgView("attachments_with_latest_revision", {
    // 메인 첨부파일 정보
    attachmentId: integer("attachment_id"),
    attachmentType: varchar("attachment_type", { length: 50 }),
    serialNo: varchar("serial_no", { length: 50 }),
    rfqId: integer("rfq_id"),
    description: varchar("description", { length: 500 }),
    currentRevision: varchar("current_revision", { length: 10 }),

    // 최신 리비전 파일 정보
    revisionId: integer("revision_id"),
    fileName: varchar("file_name", { length: 255 }),
    originalFileName: varchar("original_file_name", { length: 255 }),
    filePath: varchar("file_path", { length: 512 }),
    fileSize: integer("file_size"),
    fileType: varchar("file_type", { length: 100 }),
    revisionComment: text("revision_comment"),

    // 생성/수정 정보
    createdBy: integer("created_by"),
    createdByName: varchar("created_by_name", { length: 255 }),
    createdAt: timestamp("created_at"),
    updatedAt: timestamp("updated_at"),
}).as(sql`
    SELECT 
      a.id as attachment_id,
      a.attachment_type,
      a.serial_no,
      a.rfq_id,
      a.description,
      a.current_revision,
      
      r.id as revision_id,
      r.file_name,
      r.original_file_name,
      r.file_path,
      r.file_size,
      r.file_type,
      r.revision_comment,
      
      a.created_by,
      u.name as created_by_name,
      a.created_at,
      a.updated_at
    FROM b_rfq_attachments a
    LEFT JOIN b_rfq_attachment_revisions r ON a.latest_revision_id = r.id
    LEFT JOIN users u ON a.created_by = u.id
  `)

// 2. 벤더별 첨부파일 응답 현황 관리
export const vendorAttachmentResponses = pgTable(
    "vendor_attachment_responses",
    {
        id: serial("id").primaryKey(),
        attachmentId: integer("attachment_id")
            .notNull()
            .references(() => bRfqsAttachments.id, { onDelete: "cascade" }),

        vendorId: integer("vendor_id")
            .notNull()
            .references(() => vendors.id, { onDelete: "cascade" }),

        rfqType: varchar("rfq_type", { length: 20 })
            .$type<"INITIAL" | "FINAL">()
            .notNull(), // initial_rfq 또는 final_rfq 구분

        rfqRecordId: integer("rfq_record_id").notNull(), // initialRfq.id 또는 finalRfq.id

        responseStatus: varchar("response_status", { length: 30 })
            .$type<"NOT_RESPONDED" | "RESPONDED" | "REVISION_REQUESTED" | "WAIVED">()
            .default("NOT_RESPONDED")
            .notNull(),

        currentRevision: varchar("current_revision", { length: 10 }).default("Rev.0"),
        respondedRevision: varchar("responded_revision", { length: 10 }),

        responseComment: text("response_comment"),
        vendorComment: text("vendor_comment"),

        revisionRequestComment: text("revision_request_comment"),


        // 응답 관련 날짜
        requestedAt: timestamp("requested_at").notNull(),
        respondedAt: timestamp("responded_at"),
        revisionRequestedAt: timestamp("revision_requested_at"),

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

        createdBy: integer("created_by")
        .references(() => users.id, { onDelete: "set null" })
      ,

        updatedBy: integer("updated_by")
        .references(() => users.id, { onDelete: "set null" })
        ,
    }, (t) => ({
        // attachmentId + vendorId + rfqType 유니크
        vendorResponseIdx: uniqueIndex('vendor_response_idx').on(
            t.attachmentId.asc(),
            t.vendorId.asc(),
            t.rfqType.asc(),
        ),
    }));

// 3. 벤더 응답 첨부파일
export const vendorResponseAttachmentsB = pgTable(
    "vendor_response_attachments_b",
    {
        id: serial("id").primaryKey(),
        vendorResponseId: integer("vendor_response_id")
            .notNull()
            .references(() => vendorAttachmentResponses.id, { onDelete: "cascade" }),

        fileName: varchar("file_name", { length: 255 }).notNull(),
        originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
        filePath: varchar("file_path", { length: 512 }).notNull(),
        fileSize: integer("file_size"),
        fileType: varchar("file_type", { length: 100 }),

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

        uploadedBy: integer("uploaded_by")
            .references(() => users.id, { onDelete: "set null" }),
        uploadedAt: timestamp("uploaded_at").defaultNow().notNull(),
    },
);

// 4. 응답 히스토리 추적 (선택사항)
export const vendorResponseHistory = pgTable(
    "vendor_response_history",
    {
        id: serial("id").primaryKey(),
        vendorResponseId: integer("vendor_response_id")
            .notNull()
            .references(() => vendorAttachmentResponses.id, { onDelete: "cascade" }),

        action: varchar("action", { length: 50 })
            .$type<"REQUESTED" | "RESPONDED" | "REVISION_REQUESTED" | "REVISED" | "WAIVED">()
            .notNull(),

        previousStatus: varchar("previous_status", { length: 30 }),
        newStatus: varchar("new_status", { length: 30 }),

        comment: text("comment"),

        actionBy: integer("action_by")
            .references(() => users.id, { onDelete: "set null" }),
        actionAt: timestamp("action_at").defaultNow().notNull(),
    },
);

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

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

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

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

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

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

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

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


export const bRfqsRelations = relations(bRfqs, ({ one, many }) => ({
    // 단일 관계
    project: one(projects, {
      fields: [bRfqs.projectId],
      references: [projects.id],
    }),
    createdByUser: one(users, {
      fields: [bRfqs.createdBy],
      references: [users.id],
      relationName: "bRfqCreatedBy"
    }),
    updatedByUser: one(users, {
      fields: [bRfqs.updatedBy],
      references: [users.id],
      relationName: "bRfqUpdatedBy"
    }),
    
    // 다중 관계
    attachments: many(bRfqsAttachments),
    initialRfqs: many(initialRfq),
    finalRfqs: many(finalRfq),
  }));
  
  // bRfqsAttachments 관계 정의
  export const bRfqsAttachmentsRelations = relations(bRfqsAttachments, ({ one, many }) => ({
    // 단일 관계
    rfq: one(bRfqs, {
      fields: [bRfqsAttachments.rfqId],
      references: [bRfqs.id],
    }),
    createdByUser: one(users, {
      fields: [bRfqsAttachments.createdBy],
      references: [users.id],
    }),
    latestRevision: one(bRfqAttachmentRevisions, {
      fields: [bRfqsAttachments.latestRevisionId],
      references: [bRfqAttachmentRevisions.id],
      relationName: "attachmentLatestRevision"
    }),
    
    // 다중 관계
    revisions: many(bRfqAttachmentRevisions),
    vendorResponses: many(vendorAttachmentResponses),
  }));
  
  // bRfqAttachmentRevisions 관계 정의
  export const bRfqAttachmentRevisionsRelations = relations(bRfqAttachmentRevisions, ({ one }) => ({
    attachment: one(bRfqsAttachments, {
      fields: [bRfqAttachmentRevisions.attachmentId],
      references: [bRfqsAttachments.id],
    }),
    createdByUser: one(users, {
      fields: [bRfqAttachmentRevisions.createdBy],
      references: [users.id],
    }),
  }));
  
  // vendorAttachmentResponses 관계 정의
  export const vendorAttachmentResponsesRelations = relations(vendorAttachmentResponses, ({ one, many }) => ({
    // 단일 관계
    attachment: one(bRfqsAttachments, {
      fields: [vendorAttachmentResponses.attachmentId],
      references: [bRfqsAttachments.id],
    }),
    vendor: one(vendors, {
      fields: [vendorAttachmentResponses.vendorId],
      references: [vendors.id],
    }),
    
    // 다중 관계
    responseAttachments: many(vendorResponseAttachmentsB),
    history: many(vendorResponseHistory),
  }));
  
  // vendorResponseAttachmentsB 관계 정의
  export const vendorResponseAttachmentsBRelations = relations(vendorResponseAttachmentsB, ({ one }) => ({
    vendorResponse: one(vendorAttachmentResponses, {
      fields: [vendorResponseAttachmentsB.vendorResponseId],
      references: [vendorAttachmentResponses.id],
    }),
    uploadedByUser: one(users, {
      fields: [vendorResponseAttachmentsB.uploadedBy],
      references: [users.id],
    }),
  }));
  
  // vendorResponseHistory 관계 정의
  export const vendorResponseHistoryRelations_old = relations(vendorResponseHistory, ({ one }) => ({
    vendorResponse: one(vendorAttachmentResponses, {
      fields: [vendorResponseHistory.vendorResponseId],
      references: [vendorAttachmentResponses.id],
    }),
    actionByUser: one(users, {
      fields: [vendorResponseHistory.actionBy],
      references: [users.id],
    }),
  }));
  
  // initialRfq 관계 정의
  export const initialRfqRelations = relations(initialRfq, ({ one }) => ({
    rfq: one(bRfqs, {
      fields: [initialRfq.rfqId],
      references: [bRfqs.id],
    }),
    vendor: one(vendors, {
      fields: [initialRfq.vendorId],
      references: [vendors.id],
    }),
  }));
  
  // finalRfq 관계 정의
  export const finalRfqRelations = relations(finalRfq, ({ one }) => ({
    rfq: one(bRfqs, {
      fields: [finalRfq.rfqId],
      references: [bRfqs.id],
    }),
    vendor: one(vendors, {
      fields: [finalRfq.vendorId],
      references: [vendors.id],
    }),
  }));
  


// 업데이트된 vendorResponseAttachmentsEnhanced 뷰
export const vendorResponseAttachmentsEnhanced = pgView("vendor_response_attachments_enhanced", {
    // 벤더 응답 파일 기본 정보
    responseAttachmentId: integer("response_attachment_id"),
    vendorResponseId: integer("vendor_response_id"),
    fileName: varchar("file_name", { length: 255 }),
    originalFileName: varchar("original_file_name", { length: 255 }),
    filePath: varchar("file_path", { length: 512 }),
    fileSize: integer("file_size"),
    fileType: varchar("file_type", { length: 100 }),
    description: varchar("description", { length: 500 }),
    uploadedAt: timestamp("uploaded_at"),
    
    // 응답 기본 정보
    attachmentId: integer("attachment_id"),
    vendorId: integer("vendor_id"),
    rfqType: varchar("rfq_type", { length: 20 }),
    rfqRecordId: integer("rfq_record_id"),
    responseStatus: varchar("response_status", { length: 30 }),
    currentRevision: varchar("current_revision", { length: 10 }),
    respondedRevision: varchar("responded_revision", { length: 10 }),
    
    // 코멘트 관련 필드들 (새로 추가된 필드 포함)
    responseComment: text("response_comment"),
    vendorComment: text("vendor_comment"),
    revisionRequestComment: text("revision_request_comment"), // 새로 추가
    
    // 날짜 관련 필드들 (새로 추가된 필드 포함)
    requestedAt: timestamp("requested_at"),
    respondedAt: timestamp("responded_at"),
    revisionRequestedAt: timestamp("revision_requested_at"), // 새로 추가
    
    // 첨부파일 정보
    attachmentType: varchar("attachment_type", { length: 50 }),
    serialNo: varchar("serial_no", { length: 50 }),
    rfqId: integer("rfq_id"),
    
    // 벤더 정보
    vendorCode: varchar("vendor_code", { length: 50 }),
    vendorName: varchar("vendor_name", { length: 255 }),
    vendorCountry: varchar("vendor_country", { length: 100 }),
    
    // 발주처 현재 리비전 정보
    latestClientRevisionId: integer("latest_client_revision_id"),
    latestClientRevisionNo: varchar("latest_client_revision_no", { length: 10 }),
    latestClientFileName: varchar("latest_client_file_name", { length: 255 }),
    
    // 리비전 비교 정보
    isVersionMatched: boolean("is_version_matched"),
    versionLag: integer("version_lag"), // 몇 버전 뒤처져 있는지
    needsUpdate: boolean("needs_update"),
    
    // 응답 파일 순서 (같은 응답에 대한 여러 파일이 있을 경우)
    fileSequence: integer("file_sequence"),
    isLatestResponseFile: boolean("is_latest_response_file"),
    
}).as(sql`
    SELECT 
        vra.id as response_attachment_id,
        vra.vendor_response_id,
        vra.file_name,
        vra.original_file_name,
        vra.file_path,
        vra.file_size,
        vra.file_type,
        vra.description,
        vra.uploaded_at,
        
        -- 응답 기본 정보
        var.attachment_id,
        var.vendor_id,
        var.rfq_type,
        var.rfq_record_id,
        var.response_status,
        var.current_revision,
        var.responded_revision,
        
        -- 코멘트 (새로 추가된 필드 포함)
        var.response_comment,
        var.vendor_comment,
        var.revision_request_comment,
        
        -- 날짜 (새로 추가된 필드 포함)
        var.requested_at,
        var.responded_at,
        var.revision_requested_at,
        
        -- 첨부파일 정보
        ba.attachment_type,
        ba.serial_no,
        ba.rfq_id,
        
        -- 벤더 정보
        v.vendor_code,
        v.vendor_name,
        v.country as vendor_country,
        
        -- 발주처 현재 리비전 정보
        latest_rev.id as latest_client_revision_id,
        latest_rev.revision_no as latest_client_revision_no,
        latest_rev.original_file_name as latest_client_file_name,
        
        -- 리비전 비교
        CASE 
            WHEN var.responded_revision = ba.current_revision THEN true 
            ELSE false 
        END as is_version_matched,
        
        -- 버전 차이 계산 (Rev.0, Rev.1 형태 가정)
        CASE 
            WHEN var.responded_revision IS NULL THEN NULL
            WHEN ba.current_revision IS NULL THEN NULL
            ELSE CAST(SUBSTRING(ba.current_revision FROM '[0-9]+') AS INTEGER) - 
                 CAST(SUBSTRING(var.responded_revision FROM '[0-9]+') AS INTEGER)
        END as version_lag,
        
        CASE 
            WHEN var.response_status = 'RESPONDED' 
                 AND var.responded_revision != ba.current_revision THEN true 
            ELSE false 
        END as needs_update,
        
        -- 파일 순서
        ROW_NUMBER() OVER (
            PARTITION BY var.id 
            ORDER BY vra.uploaded_at DESC
        ) as file_sequence,
        
        -- 최신 응답 파일 여부
        CASE 
            WHEN ROW_NUMBER() OVER (
                PARTITION BY var.id 
                ORDER BY vra.uploaded_at DESC
            ) = 1 THEN true 
            ELSE false 
        END as is_latest_response_file
        
    FROM vendor_response_attachments_b vra
    JOIN vendor_attachment_responses var ON vra.vendor_response_id = var.id
    JOIN b_rfq_attachments ba ON var.attachment_id = ba.id
    LEFT JOIN vendors v ON var.vendor_id = v.id
    LEFT JOIN b_rfq_attachment_revisions latest_rev ON ba.latest_revision_id = latest_rev.id
`);

// 2. 첨부파일별 리비전 히스토리 전체 뷰
export const attachmentRevisionHistoryView = pgView("attachment_revision_history", {
    rfqId: integer("rfq_id"),
    rfqCode: varchar("rfq_code", { length: 50 }),
    attachmentId: integer("attachment_id"),
    attachmentType: varchar("attachment_type", { length: 50 }),
    serialNo: varchar("serial_no", { length: 50 }),
    
    // 발주처 리비전 정보
    clientRevisionId: integer("client_revision_id"),
    clientRevisionNo: varchar("client_revision_no", { length: 10 }),
    clientFileName: varchar("client_file_name", { length: 255 }),
    clientFilePath: varchar("client_file_path", { length: 512 }),
    clientFileSize: integer("client_file_size"),
    clientRevisionComment: text("client_revision_comment"),
    clientRevisionCreatedAt: timestamp("client_revision_created_at"),
    isLatestClientRevision: boolean("is_latest_client_revision"),
    
    // 이 리비전에 대한 벤더 응답 통계
    totalVendorResponses: integer("total_vendor_responses"),
    respondedVendors: integer("responded_vendors"),
    pendingVendors: integer("pending_vendors"),
    totalResponseFiles: integer("total_response_files"),
    
}).as(sql`
    SELECT 
        br.id as rfq_id,
        br.rfq_code,
        ba.id as attachment_id,
        ba.attachment_type,
        ba.serial_no,
        
        -- 발주처 리비전 정보
        rev.id as client_revision_id,
        rev.revision_no as client_revision_no,
        rev.original_file_name as client_file_name,
        rev.file_size as client_file_size,
        rev.file_path as client_file_path,
        rev.revision_comment as client_revision_comment,
        rev.created_at as client_revision_created_at,
        rev.is_latest as is_latest_client_revision,
        
        -- 벤더 응답 통계
        COALESCE(response_stats.total_responses, 0) as total_vendor_responses,
        COALESCE(response_stats.responded_count, 0) as responded_vendors,
        COALESCE(response_stats.pending_count, 0) as pending_vendors,
        COALESCE(response_stats.total_files, 0) as total_response_files
        
    FROM b_rfqs br
    JOIN b_rfq_attachments ba ON br.id = ba.rfq_id
    JOIN b_rfq_attachment_revisions rev ON ba.id = rev.attachment_id
    LEFT JOIN (
        SELECT 
            var.attachment_id,
            COUNT(*) as total_responses,
            COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count,
            COUNT(CASE WHEN var.response_status = 'NOT_RESPONDED' THEN 1 END) as pending_count,
            COUNT(vra.id) as total_files
        FROM vendor_attachment_responses var
        LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id
        GROUP BY var.attachment_id
    ) response_stats ON ba.id = response_stats.attachment_id
    
    ORDER BY ba.id, rev.created_at DESC
`);

// 3. 벤더별 응답 현황 상세 뷰 (리비전 정보 포함)
// 업데이트된 vendorResponseDetailView
export const vendorResponseDetailView = pgView("vendor_response_detail", {
    // 기본 식별 정보
    responseId: integer("response_id"),
    rfqId: integer("rfq_id"),
    rfqCode: varchar("rfq_code", { length: 50 }),
    rfqType: varchar("rfq_type", { length: 20 }),
    rfqRecordId: integer("rfq_record_id"),
    
    // 첨부파일 정보
    attachmentId: integer("attachment_id"),
    attachmentType: varchar("attachment_type", { length: 50 }),
    serialNo: varchar("serial_no", { length: 50 }),
    attachmentDescription: varchar("attachment_description", { length: 500 }),
    
    // 벤더 정보
    vendorId: integer("vendor_id"),
    vendorCode: varchar("vendor_code", { length: 50 }),
    vendorName: varchar("vendor_name", { length: 255 }),
    vendorCountry: varchar("vendor_country", { length: 100 }),
    
    // 응답 상태 정보
    responseStatus: varchar("response_status", { length: 30 }),
    currentRevision: varchar("current_revision", { length: 10 }),
    respondedRevision: varchar("responded_revision", { length: 10 }),
    
    // 코멘트 관련 필드들 (새로 추가된 필드 포함)
    responseComment: text("response_comment"),
    vendorComment: text("vendor_comment"),
    revisionRequestComment: text("revision_request_comment"), // 새로 추가
    
    // 날짜 관련 필드들 (새로 추가된 필드 포함)
    requestedAt: timestamp("requested_at"),
    respondedAt: timestamp("responded_at"),
    revisionRequestedAt: timestamp("revision_requested_at"), // 새로 추가
    
    // 발주처 최신 리비전 정보
    latestClientRevisionNo: varchar("latest_client_revision_no", { length: 10 }),
    latestClientFileName: varchar("latest_client_file_name", { length: 255 }),
    latestClientFileSize: integer("latest_client_file_size"),
    latestClientRevisionComment: text("latest_client_revision_comment"),
    
    // 리비전 분석
    isVersionMatched: boolean("is_version_matched"),
    versionLag: integer("version_lag"),
    needsUpdate: boolean("needs_update"),
    hasMultipleRevisions: boolean("has_multiple_revisions"),
    
    // 응답 파일 통계
    totalResponseFiles: integer("total_response_files"),
    latestResponseFileName: varchar("latest_response_file_name", { length: 255 }),
    latestResponseFileSize: integer("latest_response_file_size"),
    latestResponseUploadedAt: timestamp("latest_response_uploaded_at"),
    
    // 효과적인 상태 (UI 표시용)
    effectiveStatus: varchar("effective_status", { length: 50 }),
    
}).as(sql`
    SELECT 
        var.id as response_id,
        ba.rfq_id,
        br.rfq_code,
        var.rfq_type,
        var.rfq_record_id,
        
        -- 첨부파일 정보
        ba.id as attachment_id,
        ba.attachment_type,
        ba.serial_no,
        ba.description as attachment_description,
        
        -- 벤더 정보
        v.id as vendor_id,
        v.vendor_code,
        v.vendor_name,
        v.country as vendor_country,
        
        -- 응답 상태
        var.response_status,
        var.current_revision,
        var.responded_revision,
        
        -- 코멘트 (새로 추가된 필드 포함)
        var.response_comment,
        var.vendor_comment,
        var.revision_request_comment,
        
        -- 날짜 (새로 추가된 필드 포함)
        var.requested_at,
        var.responded_at,
        var.revision_requested_at,
        
        -- 발주처 최신 리비전
        latest_rev.revision_no as latest_client_revision_no,
        latest_rev.original_file_name as latest_client_file_name,
        latest_rev.file_size as latest_client_file_size,
        latest_rev.revision_comment as latest_client_revision_comment,
        
        -- 리비전 분석
        CASE 
            WHEN var.responded_revision = ba.current_revision THEN true 
            ELSE false 
        END as is_version_matched,
        
        CASE 
            WHEN var.responded_revision IS NULL OR ba.current_revision IS NULL THEN NULL
            ELSE CAST(SUBSTRING(ba.current_revision FROM '[0-9]+') AS INTEGER) - 
                 CAST(SUBSTRING(var.responded_revision FROM '[0-9]+') AS INTEGER)
        END as version_lag,
        
        CASE 
            WHEN var.response_status = 'RESPONDED' 
                 AND var.responded_revision != ba.current_revision THEN true 
            ELSE false 
        END as needs_update,
        
        CASE 
            WHEN revision_count.total_revisions > 1 THEN true 
            ELSE false 
        END as has_multiple_revisions,
        
        -- 응답 파일 정보
        COALESCE(file_stats.total_files, 0) as total_response_files,
        file_stats.latest_file_name as latest_response_file_name,
        file_stats.latest_file_size as latest_response_file_size,
        file_stats.latest_uploaded_at as latest_response_uploaded_at,
        
        -- 효과적인 상태
        CASE 
            WHEN var.response_status = 'NOT_RESPONDED' THEN 'NOT_RESPONDED'
            WHEN var.response_status = 'WAIVED' THEN 'WAIVED'
            WHEN var.response_status = 'REVISION_REQUESTED' THEN 'REVISION_REQUESTED'
            WHEN var.response_status = 'RESPONDED' AND var.responded_revision = ba.current_revision THEN 'UP_TO_DATE'
            WHEN var.response_status = 'RESPONDED' AND var.responded_revision != ba.current_revision THEN 'VERSION_MISMATCH'
            ELSE var.response_status
        END as effective_status
        
    FROM vendor_attachment_responses var
    JOIN b_rfq_attachments ba ON var.attachment_id = ba.id
    JOIN b_rfqs br ON ba.rfq_id = br.id
    LEFT JOIN vendors v ON var.vendor_id = v.id
    LEFT JOIN b_rfq_attachment_revisions latest_rev ON ba.latest_revision_id = latest_rev.id
    LEFT JOIN (
        SELECT 
            attachment_id,
            COUNT(*) as total_revisions
        FROM b_rfq_attachment_revisions
        GROUP BY attachment_id
    ) revision_count ON ba.id = revision_count.attachment_id
    LEFT JOIN (
        SELECT 
            vendor_response_id,
            COUNT(*) as total_files,
            MAX(original_file_name) as latest_file_name,
            MAX(file_size) as latest_file_size,
            MAX(uploaded_at) as latest_uploaded_at
        FROM vendor_response_attachments_b
        GROUP BY vendor_response_id
    ) file_stats ON var.id = file_stats.vendor_response_id
`);

// 4. RFQ 진행 현황 요약 뷰 (리비전 정보 포함)
export const rfqProgressSummaryView = pgView("rfq_progress_summary", {
    rfqId: integer("rfq_id"),
    rfqCode: varchar("rfq_code", { length: 50 }),
    rfqStatus: varchar("rfq_status", { length: 30 }),
    dueDate: date("due_date"),
    daysToDeadline: integer("days_to_deadline"),
    
    // 첨부파일 통계
    totalAttachments: integer("total_attachments"),
    attachmentsWithMultipleRevisions: integer("attachments_with_multiple_revisions"),
    totalClientRevisions: integer("total_client_revisions"),
    
    // 응답 통계 (INITIAL)
    initialVendorCount: integer("initial_vendor_count"),
    initialTotalResponses: integer("initial_total_responses"),
    initialRespondedCount: integer("initial_responded_count"),
    initialUpToDateCount: integer("initial_up_to_date_count"),
    initialVersionMismatchCount: integer("initial_version_mismatch_count"),
    initialResponseRate: numeric("initial_response_rate", { precision: 5, scale: 2 }),
    initialVersionMatchRate: numeric("initial_version_match_rate", { precision: 5, scale: 2 }),
    
    // 응답 통계 (FINAL)
    finalVendorCount: integer("final_vendor_count"),
    finalTotalResponses: integer("final_total_responses"),
    finalRespondedCount: integer("final_responded_count"),
    finalUpToDateCount: integer("final_up_to_date_count"),
    finalVersionMismatchCount: integer("final_version_mismatch_count"),
    finalResponseRate: numeric("final_response_rate", { precision: 5, scale: 2 }),
    finalVersionMatchRate: numeric("final_version_match_rate", { precision: 5, scale: 2 }),
    
    // 전체 파일 통계
    totalResponseFiles: integer("total_response_files"),
    
}).as(sql`
    SELECT 
        br.id as rfq_id,
        br.rfq_code,
        br.status as rfq_status,
        br.due_date,
        (br.due_date - CURRENT_DATE) as days_to_deadline,
        
        -- 첨부파일 통계
        attachment_stats.total_attachments,
        attachment_stats.attachments_with_multiple_revisions,
        attachment_stats.total_client_revisions,
        
        -- Initial RFQ 통계
        COALESCE(initial_stats.vendor_count, 0) as initial_vendor_count,
        COALESCE(initial_stats.total_responses, 0) as initial_total_responses,
        COALESCE(initial_stats.responded_count, 0) as initial_responded_count,
        COALESCE(initial_stats.up_to_date_count, 0) as initial_up_to_date_count,
        COALESCE(initial_stats.version_mismatch_count, 0) as initial_version_mismatch_count,
        COALESCE(initial_stats.response_rate, 0) as initial_response_rate,
        COALESCE(initial_stats.version_match_rate, 0) as initial_version_match_rate,
        
        -- Final RFQ 통계
        COALESCE(final_stats.vendor_count, 0) as final_vendor_count,
        COALESCE(final_stats.total_responses, 0) as final_total_responses,
        COALESCE(final_stats.responded_count, 0) as final_responded_count,
        COALESCE(final_stats.up_to_date_count, 0) as final_up_to_date_count,
        COALESCE(final_stats.version_mismatch_count, 0) as final_version_mismatch_count,
        COALESCE(final_stats.response_rate, 0) as final_response_rate,
        COALESCE(final_stats.version_match_rate, 0) as final_version_match_rate,
        
        COALESCE(file_stats.total_files, 0) as total_response_files
        
    FROM b_rfqs br
    LEFT JOIN (
        SELECT 
            ba.rfq_id,
            COUNT(*) as total_attachments,
            COUNT(CASE WHEN rev_count.total_revisions > 1 THEN 1 END) as attachments_with_multiple_revisions,
            SUM(rev_count.total_revisions) as total_client_revisions
        FROM b_rfq_attachments ba
        LEFT JOIN (
            SELECT 
                attachment_id,
                COUNT(*) as total_revisions
            FROM b_rfq_attachment_revisions
            GROUP BY attachment_id
        ) rev_count ON ba.id = rev_count.attachment_id
        GROUP BY ba.rfq_id
    ) attachment_stats ON br.id = attachment_stats.rfq_id
    
    LEFT JOIN (
        SELECT 
            br.id as rfq_id,
            COUNT(DISTINCT var.vendor_id) as vendor_count,
            COUNT(*) as total_responses,
            COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count,
            COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) as up_to_date_count,
            COUNT(CASE WHEN vrd.effective_status = 'VERSION_MISMATCH' THEN 1 END) as version_mismatch_count,
            ROUND(
                COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / 
                NULLIF(COUNT(*), 0), 2
            ) as response_rate,
            ROUND(
                COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) * 100.0 / 
                NULLIF(COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END), 0), 2
            ) as version_match_rate
        FROM b_rfqs br
        JOIN vendor_response_detail vrd ON br.id = vrd.rfq_id
        JOIN vendor_attachment_responses var ON vrd.response_id = var.id
        WHERE var.rfq_type = 'INITIAL'
        GROUP BY br.id
    ) initial_stats ON br.id = initial_stats.rfq_id
    
    LEFT JOIN (
        SELECT 
            br.id as rfq_id,
            COUNT(DISTINCT var.vendor_id) as vendor_count,
            COUNT(*) as total_responses,
            COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count,
            COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) as up_to_date_count,
            COUNT(CASE WHEN vrd.effective_status = 'VERSION_MISMATCH' THEN 1 END) as version_mismatch_count,
            ROUND(
                COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / 
                NULLIF(COUNT(*), 0), 2
            ) as response_rate,
            ROUND(
                COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) * 100.0 / 
                NULLIF(COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END), 0), 2
            ) as version_match_rate
        FROM b_rfqs br
        JOIN vendor_response_detail vrd ON br.id = vrd.rfq_id
        JOIN vendor_attachment_responses var ON vrd.response_id = var.id
        WHERE var.rfq_type = 'FINAL'
        GROUP BY br.id
    ) final_stats ON br.id = final_stats.rfq_id
    
    LEFT JOIN (
        SELECT 
            br.id as rfq_id,
            COUNT(vra.id) as total_files
        FROM b_rfqs br
        JOIN b_rfq_attachments ba ON br.id = ba.rfq_id
        JOIN vendor_attachment_responses var ON ba.id = var.attachment_id
        LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id
        GROUP BY br.id
    ) file_stats ON br.id = file_stats.rfq_id
`);

// 타입 정의
export type VendorResponseAttachmentEnhanced = typeof vendorResponseAttachmentsEnhanced.$inferSelect;
export type AttachmentRevisionHistory = typeof attachmentRevisionHistoryView.$inferSelect;
export type VendorResponseDetail = typeof vendorResponseDetailView.$inferSelect;
export type RfqProgressSummary = typeof rfqProgressSummaryView.$inferSelect;