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

export const rfqs = pgTable(
    "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" }),

        bidProjectId: integer("bid_project_id")
            .references(() => biddingProjects.id, { onDelete: "set null" }),

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

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

        status: varchar("status", { length: 30 })
            .$type<"DRAFT" | "PUBLISHED" | "EVALUATION" | "AWARDED">()
            .default("DRAFT")
            .notNull(),

        // Budgetary / Purchase 여부
        rfqType: varchar("rfq_type", { length: 30 }).default("PURCHASE"),

        // 자기 자신 테이블 FK(부모 RFQ)
        parentRfqId: integer("parent_rfq_id"),

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

        createdAt: timestamp("created_at").defaultNow().notNull(),
        updatedAt: timestamp("updated_at").defaultNow().notNull(),
    },
    // <==== 여기서 자기참조 FK를 정의 (수정됨)
    (table) => {
        return {
            // parentRfqId -> rfqs.id
            parentFk: foreignKey({
                columns: [table.parentRfqId],
                foreignColumns: [table.id],
            }).onDelete("set null"), // 메서드 체이닝 방식으로 변경
        };
    }
);

export const rfqItems = pgTable("rfq_items", {
    id: serial("id").primaryKey(),
    rfqId: integer("rfq_id")
        .notNull()
        .references(() => rfqs.id, { onDelete: "cascade" }),
    itemCode: varchar("item_code", { length: 100 })
        .notNull()
        .references(() => items.itemCode, { onDelete: "cascade" }),
    description: text("description"),
    quantity: numeric("quantity", { precision: 12, scale: 2 })
        .$type<number>()
        .default(1),
    uom: varchar("uom", { length: 50 }), // 단위
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const rfqAttachments = pgTable("rfq_attachments", {
    id: serial("id").primaryKey(),
    rfqId: integer("rfq_id").references(() => rfqs.id),
    vendorId: integer("vendor_id").references(() => vendors.id),
    fileName: varchar("file_name", { length: 255 }).notNull(),
    filePath: varchar("file_path", { length: 1024 }).notNull(),
    evaluationId: integer("evaluation_id")
        .references(() => rfqEvaluations.id)
        .$type<number | null>(),
    cbeId: integer("cbe_id")
        .references(() => cbeEvaluations.id)
        .$type<number | null>(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    commentId: integer("comment_id")
        .references(() => rfqComments.id)
        .$type<number | null>(),
});

export const rfqComments = pgTable("rfq_comments", {
    id: serial("id").primaryKey(),
    rfqId: integer("rfq_id").references(() => rfqs.id),
    vendorId: integer("vendor_id").references(() => vendors.id),
    commentText: text("comment_text").notNull(),
    commentedBy: integer("commented_by")
        .references(() => users.id) // users 테이블의 id 필드 참조 추가
        .notNull(),
    // 아래처럼, 평가(TBE/CBE)에 속한 코멘트인지 여부를 구분할 필드
    evaluationId: integer("evaluation_id")
        .references(() => rfqEvaluations.id)
        .$type<number | null>(),
    cbeId: integer("cbe_id")
        .references(() => vendorResponses.id)
        .$type<number | null>(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const rfqEvaluations = pgTable("rfq_evaluations", {
    id: serial("id").primaryKey(),
    rfqId: integer("rfq_id")
        .notNull()
        .references(() => rfqs.id),
    vendorId: integer("vendor_id")
        .notNull()
        .references(() => vendors.id),
    evalType: varchar("eval_type", { length: 30 })
        .$type<"TBE" | "CBE">(),
    // TBE or CBE (기술 / 상업)
    result: varchar("result", { length: 255 }), // 예: "PASS", "FAIL", "ACCEPTABLE" 등
    notes: text("notes"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
});


// General vendor response to RFQ invitation
export const vendorResponses = pgTable(
    "vendor_responses",
    {
        id: serial("id").primaryKey(),

        rfqId: integer("rfq_id")
            .notNull()
            .references(() => rfqs.id, { onDelete: "cascade" }),

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

        // Vendor's overall response status
        responseStatus: varchar("response_status", { length: 30 })
            .$type<"INVITED" | "ACCEPTED" | "DECLINED" | "REVIEWING" | "RESPONDED">()
            .default("REVIEWING")
            .notNull(),

        // General notes from vendor
        notes: text("notes"),

        // Metadata
        respondedBy: varchar("responded_by", { length: 255 }),  // Could be vendor contact name
        respondedAt: timestamp("responded_at").defaultNow().notNull(),
        updatedAt: timestamp("updated_at").defaultNow().notNull(),
    },
    (table) => ({
        // Each vendor can only have one response per RFQ
        uniqueConstraint: uniqueIndex("vendor_response_unique").on(
            table.rfqId,
            table.vendorId
        ),
    })
);

// Technical response details from vendor
export const vendorTechnicalResponses = pgTable(
    "vendor_technical_responses",
    {
        id: serial("id").primaryKey(),

        // Link to main response
        responseId: integer("response_id")
            .notNull()
            .references(() => vendorResponses.id, { onDelete: "cascade" }),

        responseStatus: varchar("response_status", { length: 30 })
            .$type<"PENDING" | "IN_PROGRESS" | "SUBMITTED" | "REJECTED" | "ACCEPTED">()
            .default("PENDING")
            .notNull(),


        // 간소화된 기술 응답 필드
        // 기술 내용은 주로 첨부 파일로 받으므로 최소한의 필드만 유지
        summary: text("summary"),  // 간단한 기술 응답 요약
        notes: text("notes"),      // 추가 노트나 코멘트

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

// Commercial response details from vendor
export const vendorCommercialResponses = pgTable(
    "vendor_commercial_responses",
    {
        id: serial("id").primaryKey(),

        // Link to main response
        responseId: integer("response_id")
            .notNull()
            .references(() => vendorResponses.id, { onDelete: "cascade" }),

        responseStatus: varchar("response_status", { length: 30 })
            .$type<"PENDING" | "IN_PROGRESS" | "SUBMITTED" | "REJECTED" | "ACCEPTED">()
            .default("PENDING")
            .notNull(),

        // Commercial response fields
        totalPrice: numeric("total_price", { precision: 18, scale: 2 }).$type<number>(),
        currency: varchar("currency", { length: 10 }).default("USD"),

        // Commercial terms
        paymentTerms: varchar("payment_terms", { length: 255 }),
        incoterms: varchar("incoterms", { length: 50 }),
        deliveryPeriod: varchar("delivery_period", { length: 100 }),
        warrantyPeriod: varchar("warranty_period", { length: 100 }),

        // Validity of offer
        validityPeriod: varchar("validity_period", { length: 100 }),

        // Price breakdown or other commercial notes
        priceBreakdown: text("price_breakdown"),
        commercialNotes: text("commercial_notes"),

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


// Attachment table for vendor responses
export const vendorResponseAttachments = pgTable(
    "vendor_response_attachments",
    {
        id: serial("id").primaryKey(),

        // Can be linked to any type of response
        responseId: integer("response_id")
            .references(() => vendorResponses.id, { onDelete: "cascade" }),
        technicalResponseId: integer("technical_response_id")
            .references(() => vendorTechnicalResponses.id, { onDelete: "cascade" }),
        commercialResponseId: integer("commercial_response_id")
            .references(() => vendorCommercialResponses.id, { onDelete: "cascade" }),

        // File details
        fileName: varchar("file_name", { length: 255 }).notNull(),
        filePath: varchar("file_path", { length: 1024 }).notNull(),
        fileType: varchar("file_type", { length: 50 }),

        // 첨부 파일 분류
        attachmentType: varchar("attachment_type", { length: 50 })
            .$type<"TECHNICAL_SPEC" | "QUALITY_CERT" | "COMPLIANCE_DOC" | "COMMERCIAL" | "OTHER">(),
        description: varchar("description", { length: 255 }),

        // Metadata
        uploadedAt: timestamp("uploaded_at").defaultNow().notNull(),
        uploadedBy: varchar("uploaded_by", { length: 255 }),
    }
);


export const cbeEvaluations = pgTable("cbe_evaluations", {
    id: serial("id").primaryKey(),

    // 어떤 RFQ에 대한 CBE인지
    rfqId: integer("rfq_id")
        .notNull()
        .references(() => rfqs.id, { onDelete: "cascade" }),

    // 어떤 협력업체의 상업평가(CBE)인지
    vendorId: integer("vendor_id")
        .notNull()
        .references(() => vendors.id, { onDelete: "cascade" }),

    // 평가자(작성자) 정보가 필요하다면 (예: 상업팀 담당자)
    evaluatedBy: integer("evaluated_by")
        .references(() => users.id, { onDelete: "set null" }),

    // 평가 일시
    evaluatedAt: timestamp("evaluated_at").defaultNow().notNull(),

    // 상업평가 결과(예: ACCEPT, REJECT, PENDING 등)
    result: varchar("result", { length: 50 }),

    // 총 금액(견적 기준일 수도 있고, 평가 과정에서 조정이 있을 수도 있음)
    totalCost: numeric("total_cost", { precision: 18, scale: 2 }).$type<number>(),

    // 통화
    currency: varchar("currency", { length: 10 }).default("USD"),

    // 결제 조건, 인도 조건, 기타 상업 조건
    paymentTerms: varchar("payment_terms", { length: 255 }),
    incoterms: varchar("incoterms", { length: 50 }),
    deliverySchedule: text("delivery_schedule"), // 정해진 형식이 있다면 varchar, 아니면 text

    // 기타 비고
    notes: text("notes"),

    // 기본 관리 필드
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
});



export const cbeView = pgView("cbe_view").as((qb) => {
    return qb
        .select({
            cbeId: sql<number>`${cbeEvaluations.id}`.as("cbe_id"),
            rfqId: sql<number>`${cbeEvaluations.rfqId}`.as("rfq_id"),
            vendorId: sql<number>`${cbeEvaluations.vendorId}`.as("vendor_id"),
            totalCost: sql<number | null>`${cbeEvaluations.totalCost}`.as("total_cost"),
            currency: sql<string | null>`${cbeEvaluations.currency}`.as("currency"),
            paymentTerms: sql<string | null>`${cbeEvaluations.paymentTerms}`.as("payment_terms"),
            incoterms: sql<string | null>`${cbeEvaluations.incoterms}`.as("incoterms"),
            result: sql<string | null>`${cbeEvaluations.result}`.as("result"),
            notes: sql<string | null>`${cbeEvaluations.notes}`.as("notes"),
            evaluatedBy: sql<number | null>`${cbeEvaluations.evaluatedBy}`.as("evaluated_by"),
            evaluatedAt: sql<Date>`${cbeEvaluations.evaluatedAt}`.as("evaluated_at"),

            rfqCode: sql<string>`${rfqs.rfqCode}`.as("rfq_code"),
            rfqDescription: sql<string | null>`${rfqs.description}`.as("rfq_description"),
            vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
            vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),

            projectId: sql<number | null>`${projects.id}`.as("project_id"),
            projectCode: sql<string | null>`${projects.code}`.as("project_code"),
            projectName: sql<string | null>`${projects.name}`.as("project_name"),

            evaluatorName: sql<string | null>`${users.name}`.as("evaluator_name"),
            evaluatorEmail: sql<string | null>`${users.email}`.as("evaluator_email"),

        })
        .from(cbeEvaluations)
        .innerJoin(rfqs, eq(cbeEvaluations.rfqId, rfqs.id))
        .innerJoin(vendors, eq(cbeEvaluations.vendorId, vendors.id))
        .leftJoin(projects, eq(rfqs.projectId, projects.id))
        .leftJoin(users, eq(cbeEvaluations.evaluatedBy, users.id));
});


//view

export const rfqsView = pgView("rfqs_view").as((qb) => {
    return qb
        .select({
            // rfqs 주요 필드
            id: sql<number>`${rfqs.id}`.as("rfq_id"),
            status: sql<string>`${rfqs.status}`.as("status"),
            createdAt: sql<Date>`${rfqs.createdAt}`.as("created_at"),
            updatedAt: sql<Date>`${rfqs.updatedAt}`.as("updated_at"),
            createdBy: sql<number | null>`${rfqs.createdBy}`.as("created_by"),
            rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),

            // rfqs 추가 필드
            rfqCode: sql<string>`${rfqs.rfqCode}`.as("rfq_code"),
            description: sql<string | null>`${rfqs.description}`.as("description"),
            dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),
            parentRfqId: sql<number | null>`${rfqs.parentRfqId}`.as("parent_rfq_id"),

            // 프로젝트 테이블 필드
            projectId: sql<number | null>`${projects.id}`.as("project_id"),
            projectCode: sql<string | null>`${projects.code}`.as("project_code"),
            projectName: sql<string | null>`${projects.name}`.as("project_name"),

            // users 테이블 관련 필드
            userEmail: sql<string | null>`${users.email}`.as("user_email"),
            userName: sql<string | null>`${users.name}`.as("user_name"),

            // rfq_items 및 rfq_attachments 카운트
            itemCount: sql<number>`(
          SELECT COUNT(*) 
          FROM "rfq_items" 
          WHERE "rfq_items"."rfq_id" = ${rfqs.id}
        )`.as("item_count"),

            attachmentCount: sql<number>`(
          SELECT COUNT(*) 
          FROM "rfq_attachments" 
          WHERE "rfq_attachments"."rfq_id" = ${rfqs.id}
        )`.as("attachment_count"),


        })
        .from(rfqs)
        .leftJoin(projects, eq(rfqs.projectId, projects.id))
        .leftJoin(users, eq(rfqs.createdBy, users.id))
})

// vendorRfqView 업데이트 협력업체 기준
export const vendorRfqView = pgView("vendor_rfq_view").as((qb) => {
    return qb
        .select({
            // vendors 테이블
            vendorId: sql<number>`${vendors.id}`.as("vendor_id"),
            vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
            vendorCode: sql<string>`${vendors.vendorCode}`.as("vendor_code"),
            address: sql<string | null>`${vendors.address}`.as("address"),
            country: sql<string | null>`${vendors.country}`.as("country"),
            email: sql<string | null>`${vendors.email}`.as("email"),
            website: sql<string | null>`${vendors.website}`.as("website"),
            vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"),

            // rfqVendors 테이블
            rfqId: sql<number | null>`${vendorResponses.rfqId}`.as("rfq_id"),
            rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"),
            rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"),

            // rfqs 테이블
            rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"),
            description: sql<string | null>`${rfqs.description}`.as("description"),
            dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),

            // 프로젝트 테이블 (업데이트됨)
            projectId: sql<number | null>`${projects.id}`.as("project_id"),
            projectCode: sql<string | null>`${projects.code}`.as("project_code"),
            projectName: sql<string | null>`${projects.name}`.as("project_name"),
        })
        .from(vendors)
        .leftJoin(vendorResponses, eq(vendorResponses.vendorId, vendors.id))
        .leftJoin(rfqs, eq(vendorResponses.rfqId, rfqs.id))
        .leftJoin(projects, eq(rfqs.projectId, projects.id)) // 프로젝트 테이블 조인 추가
})



// vendorTbeView 업데이트
export const vendorTbeView = pgView("vendor_tbe_view").as((qb) => {
    return qb
        .select({
            // vendors 기본정보
            vendorId: sql<number>`${vendors.id}`.as("vendor_id"),
            vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
            vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
            address: sql<string | null>`${vendors.address}`.as("address"),
            country: sql<string | null>`${vendors.country}`.as("country"),
            email: sql<string | null>`${vendors.email}`.as("email"),
            website: sql<string | null>`${vendors.website}`.as("website"),
            vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"),

            // rfq_vendors
            vendorResponseId: sql<number | null>`${vendorResponses.id}`.as("vendor_response_id"),
            rfqId: sql<number | null>`${vendorResponses.rfqId}`.as("rfq_id"),
            rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"),
            rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"),

             // TBE 응답 상태 추가
             technicalResponseId: sql<number | null>`${vendorTechnicalResponses.id}`.as("technical_response_id"),
             technicalResponseStatus: sql<string | null>`${vendorTechnicalResponses.responseStatus}`.as("technical_response_status"),
             technicalSummary: sql<string | null>`${vendorTechnicalResponses.summary}`.as("technical_summary"),
             technicalNotes: sql<string | null>`${vendorTechnicalResponses.notes}`.as("technical_notes"),
             technicalUpdated: sql<Date | null>`${vendorTechnicalResponses.updatedAt}`.as("technical_updated"),

            // rfqs
            rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"),
            rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),
            rfqStatus: sql<string | null>`${rfqs.status}`.as("rfq_status"),
            description: sql<string | null>`${rfqs.description}`.as("description"),
            dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),

            // 프로젝트 테이블 필드 (업데이트됨)
            projectId: sql<number | null>`${projects.id}`.as("project_id"),
            projectCode: sql<string | null>`${projects.code}`.as("project_code"),
            projectName: sql<string | null>`${projects.name}`.as("project_name"),

            // rfq_evaluations (TBE 전용)
            tbeId: sql<number | null>`${rfqEvaluations.id}`.as("tbe_id"),
            tbeResult: sql<string | null>`${rfqEvaluations.result}`.as("tbe_result"),
            tbeNote: sql<string | null>`${rfqEvaluations.notes}`.as("tbe_note"),
            tbeUpdated: sql<Date | null>`${rfqEvaluations.updatedAt}`.as("tbe_updated"),
        })
        .from(vendors)
        .leftJoin(
            vendorResponses,
            eq(vendorResponses.vendorId, vendors.id)
        )
        // 1) rfqVendors ↔ rfqs
        .leftJoin(
            rfqs,
            eq(vendorResponses.rfqId, rfqs.id)
        )
        // 2) 프로젝트 테이블 조인 추가
        .leftJoin(
            projects,
            eq(rfqs.projectId, projects.id)
            
        )
        .leftJoin(
            vendorTechnicalResponses,
            eq(vendorTechnicalResponses.responseId, vendorResponses.id)
        )
        // 3) rfqEvaluations
        .leftJoin(
            rfqEvaluations,
            and(
                eq(rfqEvaluations.vendorId, vendors.id),
                eq(rfqEvaluations.evalType, "TBE"),
                eq(rfqEvaluations.rfqId, vendorResponses.rfqId)
            )
        )
})

export const vendorCbeView = pgView("vendor_cbe_view").as((qb) => {
    return qb
        .select({
            // [1] Vendor 기본정보
            vendorId: sql<number>`${vendors.id}`.as("vendor_id"),
            vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
            vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
            address: sql<string | null>`${vendors.address}`.as("address"),
            country: sql<string | null>`${vendors.country}`.as("country"),
            email: sql<string | null>`${vendors.email}`.as("email"),
            website: sql<string | null>`${vendors.website}`.as("website"),
            vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"),

            // [2] rfq_vendors (vendorResponses)
            vendorResponseId: sql<number | null>`${vendorResponses.id}`.as("vendor_response_id"),
            rfqId: sql<number | null>`${vendorResponses.rfqId}`.as("rfq_id"),
            rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"),
            rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"),

            // [3] rfqs
            rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"),
            rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),
            description: sql<string | null>`${rfqs.description}`.as("description"),
            dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),

            // [4] 프로젝트 정보
            projectId: sql<number | null>`${projects.id}`.as("project_id"),
            projectCode: sql<string | null>`${projects.code}`.as("project_code"),
            projectName: sql<string | null>`${projects.name}`.as("project_name"),

            // [5] CBE 평가(cbeEvaluations)
            cbeId: sql<number | null>`${cbeEvaluations.id}`.as("cbe_id"),
            cbeResult: sql<string | null>`${cbeEvaluations.result}`.as("cbe_result"),
            cbeNote: sql<string | null>`${cbeEvaluations.notes}`.as("cbe_note"),
            cbeUpdated: sql<Date | null>`${cbeEvaluations.updatedAt}`.as("cbe_updated"),

            // 상업평가용 추가 필드들
            totalCost: sql<number | null>`${cbeEvaluations.totalCost}`.as("total_cost"),
            currency: sql<string | null>`${cbeEvaluations.currency}`.as("currency"),
            paymentTerms: sql<string | null>`${cbeEvaluations.paymentTerms}`.as("payment_terms"),
            incoterms: sql<string | null>`${cbeEvaluations.incoterms}`.as("incoterms"),
            deliverySchedule: sql<string | null>`${cbeEvaluations.deliverySchedule}`.as("delivery_schedule"),
        })
        .from(vendors)
        .leftJoin(
            vendorResponses,
            eq(vendorResponses.vendorId, vendors.id)
        )
        .leftJoin(
            rfqs,
            eq(vendorResponses.rfqId, rfqs.id)
        )
        .leftJoin(
            projects,
            eq(rfqs.projectId, projects.id)
        )
        .leftJoin(
            cbeEvaluations,
            and(
                eq(cbeEvaluations.vendorId, vendors.id),
                eq(cbeEvaluations.rfqId, vendorResponses.rfqId)
            )
        );
});

// Types for TypeScript
export type VendorResponse = typeof vendorResponses.$inferSelect;
export type VendorTechnicalResponse = typeof vendorTechnicalResponses.$inferSelect;
export type VendorCommercialResponse = typeof vendorCommercialResponses.$inferSelect;
export type VendorResponseAttachment = typeof vendorResponseAttachments.$inferSelect;


// View to combine vendor response data with projects
export const vendorResponsesView = pgView("vendor_responses_view").as((qb) => {
    return qb
        .select({
            // Response identification
            responseId: sql<number>`${vendorResponses.id}`.as("response_id"),
            rfqId: sql<number>`${vendorResponses.rfqId}`.as("rfq_id"),
            vendorId: sql<number>`${vendorResponses.vendorId}`.as("vendor_id"),

            // RFQ information
            rfqCode: sql<string>`${rfqs.rfqCode}`.as("rfq_code"),
            rfqDescription: sql<string | null>`${rfqs.description}`.as("rfq_description"),
            rfqDueDate: sql<Date | null>`${rfqs.dueDate}`.as("rfq_due_date"),
            rfqStatus: sql<string>`${rfqs.status}`.as("rfq_status"),
            rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),
            rfqCreatedAt: sql<Date>`${rfqs.createdAt}`.as("rfq_created_at"),
            rfqUpdatedAt: sql<Date>`${rfqs.updatedAt}`.as("rfq_updated_at"),
            rfqCreatedBy: sql<number | null>`${rfqs.createdBy}`.as("rfq_created_by"),

            // Project information
            projectId: sql<number | null>`${projects.id}`.as("project_id"),
            projectCode: sql<string | null>`${projects.code}`.as("project_code"),
            projectName: sql<string | null>`${projects.name}`.as("project_name"),

            // Vendor information
            vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
            vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),

            // Response status
            responseStatus: sql<"INVITED" | "ACCEPTED" | "DECLINED" | "REVIEWING" | "RESPONDED">`${vendorResponses.responseStatus}`
                .as("response_status"),
            respondedAt: sql<Date>`${vendorResponses.respondedAt}`.as("responded_at"),

            // Technical response indicators
            hasTechnicalResponse: sql<boolean>`CASE WHEN ${vendorTechnicalResponses.id} IS NOT NULL THEN TRUE ELSE FALSE END`.as("has_technical_response"),
            technicalResponseId: sql<number | null>`${vendorTechnicalResponses.id}`.as("technical_response_id"),

            // Commercial response indicators
            hasCommercialResponse: sql<boolean>`CASE WHEN ${vendorCommercialResponses.id} IS NOT NULL THEN TRUE ELSE FALSE END`.as("has_commercial_response"),
            commercialResponseId: sql<number | null>`${vendorCommercialResponses.id}`.as("commercial_response_id"),
            totalPrice: sql<number | null>`${vendorCommercialResponses.totalPrice}`.as("total_price"),
            currency: sql<string | null>`${vendorCommercialResponses.currency}`.as("currency"),

            // Evaluation links
            tbeId: sql<number | null>`${rfqEvaluations.id}`.as("tbe_id"),
            tbeResult: sql<string | null>`${rfqEvaluations.result}`.as("tbe_result"),
            cbeId: sql<number | null>`${cbeEvaluations.id}`.as("cbe_id"),
            cbeResult: sql<string | null>`${cbeEvaluations.result}`.as("cbe_result"),

            // Attachments count
            attachmentCount: sql<number>`(
          SELECT COUNT(*) 
          FROM "vendor_response_attachments" 
          WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id}
        )`.as("attachment_count"),
        })
        .from(vendorResponses)
        .innerJoin(rfqs, eq(vendorResponses.rfqId, rfqs.id))
        .innerJoin(vendors, eq(vendorResponses.vendorId, vendors.id))
        .leftJoin(projects, eq(rfqs.projectId, projects.id)) // Added project join
        .leftJoin(
            vendorTechnicalResponses,
            eq(vendorTechnicalResponses.responseId, vendorResponses.id)
        )
        .leftJoin(
            vendorCommercialResponses,
            eq(vendorCommercialResponses.responseId, vendorResponses.id)
        )
        .leftJoin(
            rfqEvaluations,
            and(
                eq(rfqEvaluations.rfqId, vendorResponses.rfqId),
                eq(rfqEvaluations.vendorId, vendorResponses.vendorId),
                eq(rfqEvaluations.evalType, "TBE")
            )
        )
        .leftJoin(
            cbeEvaluations,
            and(
                eq(cbeEvaluations.rfqId, vendorResponses.rfqId),
                eq(cbeEvaluations.vendorId, vendorResponses.vendorId)
            )
        );
});


export type VendorResponsesView = typeof vendorResponsesView.$inferSelect


export type Rfq = typeof rfqs.$inferSelect
export type RfqItem = typeof rfqItems.$inferSelect
export type RfqAttach = typeof rfqAttachments.$inferSelect
export type RfqComment = typeof rfqComments.$inferSelect
export type RfqEvaluation = typeof rfqEvaluations.$inferSelect

export type VendorRfqViewBase = typeof vendorRfqView.$inferSelect

// 2) 우리가 코드에서 필요한 속성까지 합친 확장 타입
export interface VendorRfqViewWithComments extends VendorRfqViewBase {
    comments?: {
        id: number
        commentText: string
        vendorId?: number
        evaluationId?: number
        createdAt: Date
        commentedBy?: number
    }[]
    // 필요하다면 quote, attachments 등 추가 필드도...
}

export type VendorTbeView = typeof vendorTbeView.$inferSelect
export type VendorCbeView = typeof vendorCbeView.$inferSelect

export interface RfqWithItemCount {
    // 기본 RFQ 식별자
    rfqId: number;
    // id: number;  // rfqId와 동일 (편의상 중복)

    // RFQ 기본 정보
    rfqCode: string;
    description: string | null;
    status: string;  // 필요시 "DRAFT" | "PUBLISHED" | "EVALUATION" | "AWARDED" 로 제한 가능

    // 날짜 정보
    dueDate: Date | null;
    createdAt: Date;
    updatedAt: Date;

    // 프로젝트 관련 정보
    projectCode: string | null;
    projectName: string | null;

    // 생성자 정보
    createdBy: number | null;
    createdByEmail: string | null;

    // 사용자 정보
    userId?: number | null;
    userEmail?: string | null;
    userName?: string | null;

    // 집계 정보 (서브쿼리로 계산됨)
    itemCount: number;
    attachCount: number;

    // 추가 필드 (필요시 데이터 변환 함수에서 채울 수 있음)
    rfqType?: string | null;
    projectId?: number | null;
    parentRfqId?: number | null;

    // 다른 필요한 필드들
    vendorId?: number | null;
    rfqVendorId?: number | null;
    rfqVendorStatus?: string | null;
    rfqVendorUpdated?: Date | null;
}

export interface RfqWithItems extends Rfq {
    lines: RfqItem[];
}

export interface RfqViewWithItems extends RfqsView {
    lines: RfqItem[];
}

export interface RfqWithAll extends VendorResponsesView {

    // 아래처럼 배열/객체로
    items: Array<{
        id: number
        itemCode: string
        itemName: string
        quantity?: number
        uom?: string
        description?: string | null
    }>
    attachments: Array<{
        id: number
        fileName: string
        filePath: string
        vendorId?: number | null
        evaluationId?: number | null
    }>
    comments: Array<{
        id: number
        commentText: string
        vendorId?: number | null
        evaluationId?: number | null
        createdAt: Date
    }>

}
export type RfqsView = typeof rfqsView.$inferSelect


export const vendorResponseCBEView = pgView("vendor_response_cbe_view").as((qb) => {
    return qb
        .select({
            // 기본 응답 식별 정보
            responseId: sql<number>`${vendorResponses.id}`.as("response_id"),
            rfqId: sql<number>`${vendorResponses.rfqId}`.as("rfq_id"),
            vendorId: sql<number>`${vendorResponses.vendorId}`.as("vendor_id"),

            // vendorResponses 상세 정보
            responseStatus: sql<string>`${vendorResponses.responseStatus}`.as("response_status"),
            notes: sql<string | null>`${vendorResponses.notes}`.as("response_notes"),
            respondedBy: sql<string | null>`${vendorResponses.respondedBy}`.as("responded_by"),
            respondedAt: sql<Date>`${vendorResponses.respondedAt}`.as("responded_at"),
            responseUpdatedAt: sql<Date>`${vendorResponses.updatedAt}`.as("response_updated_at"),

            // RFQ 정보
            rfqCode: sql<string>`${rfqs.rfqCode}`.as("rfq_code"),
            rfqDescription: sql<string | null>`${rfqs.description}`.as("rfq_description"),
            rfqDueDate: sql<Date | null>`${rfqs.dueDate}`.as("rfq_due_date"),
            rfqStatus: sql<string>`${rfqs.status}`.as("rfq_status"),
            rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),

            // 협력업체 정보
            vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
            vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
            vendorStatus: sql<string | null>`${vendors.status}`.as("vendor_status"),

            // 프로젝트 정보
            projectId: sql<number | null>`${projects.id}`.as("project_id"),
            projectCode: sql<string | null>`${projects.code}`.as("project_code"),
            projectName: sql<string | null>`${projects.name}`.as("project_name"),

            // 상업 응답 상세 정보
            commercialResponseId: sql<number | null>`${vendorCommercialResponses.id}`.as("commercial_response_id"),
            commercialResponseStatus: sql<string | null>`${vendorCommercialResponses.responseStatus}`.as("commercial_response_status"),
            totalPrice: sql<number | null>`${vendorCommercialResponses.totalPrice}`.as("total_price"),
            currency: sql<string | null>`${vendorCommercialResponses.currency}`.as("currency"),
            paymentTerms: sql<string | null>`${vendorCommercialResponses.paymentTerms}`.as("payment_terms"),
            incoterms: sql<string | null>`${vendorCommercialResponses.incoterms}`.as("incoterms"),
            deliveryPeriod: sql<string | null>`${vendorCommercialResponses.deliveryPeriod}`.as("delivery_period"),
            warrantyPeriod: sql<string | null>`${vendorCommercialResponses.warrantyPeriod}`.as("warranty_period"),
            validityPeriod: sql<string | null>`${vendorCommercialResponses.validityPeriod}`.as("validity_period"),
            priceBreakdown: sql<string | null>`${vendorCommercialResponses.priceBreakdown}`.as("price_breakdown"),
            commercialNotes: sql<string | null>`${vendorCommercialResponses.commercialNotes}`.as("commercial_notes"),
            commercialCreatedAt: sql<Date | null>`${vendorCommercialResponses.createdAt}`.as("commercial_created_at"),
            commercialUpdatedAt: sql<Date | null>`${vendorCommercialResponses.updatedAt}`.as("commercial_updated_at"),

            // 첨부파일 개수 및 상세 정보
            attachmentCount: sql<number>`(
                SELECT COUNT(*) 
                FROM "vendor_response_attachments" 
                WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id}
            )`.as("attachment_count"),

            commercialAttachmentCount: sql<number>`(
                SELECT COUNT(*) 
                FROM "vendor_response_attachments" 
                WHERE "vendor_response_attachments"."commercial_response_id" = ${vendorCommercialResponses.id}
            )`.as("commercial_attachment_count"),

            // 첨부파일 유형별 개수 (선택적)
            technicalAttachmentCount: sql<number>`(
                SELECT COUNT(*) 
                FROM "vendor_response_attachments" 
                WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id}
                AND "vendor_response_attachments"."attachment_type" = 'TECHNICAL_SPEC'
            )`.as("technical_attachment_count"),

            // 최신 첨부파일 정보 (선택적)
            latestAttachmentDate: sql<Date | null>`(
                SELECT MAX("uploaded_at") 
                FROM "vendor_response_attachments" 
                WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id}
            )`.as("latest_attachment_date"),
        })
        .from(vendorResponses)
        .innerJoin(rfqs, eq(vendorResponses.rfqId, rfqs.id))
        .innerJoin(vendors, eq(vendorResponses.vendorId, vendors.id))
        .leftJoin(projects, eq(rfqs.projectId, projects.id))
        .leftJoin(
            vendorCommercialResponses,
            eq(vendorCommercialResponses.responseId, vendorResponses.id)
        );
});

// TypeScript 타입 정의
export type VendorResponseCBEView = typeof vendorResponseCBEView.$inferSelect;