import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, jsonb, uniqueIndex, index } from "drizzle-orm/pg-core";
import { eq, sql, relations } from "drizzle-orm";
import { rfqsLast, rfqLastDetails, rfqLastAttachments, rfqLastAttachmentRevisions } from "./rfqLast";
import { users } from "./users";
import { vendors } from "./vendors";
import { rfqLastVendorAttachments } from "./rfqVendor";
import { projects } from "./projects";

// ==========================================
// 1. TBE 세션 메인 테이블
// ==========================================
export const rfqLastTbeSessions = pgTable(
    "rfq_last_tbe_sessions",
    {
        id: serial("id").primaryKey(),

        // RFQ 및 벤더 참조
        rfqsLastId: integer("rfqs_last_id")
            .notNull()
            .references(() => rfqsLast.id, { onDelete: "cascade" }),
        rfqLastDetailsId: integer("rfq_last_details_id")
            .notNull()
            .references(() => rfqLastDetails.id, { onDelete: "cascade" }),
        vendorId: integer("vendor_id")
            .notNull()
            .references(() => vendors.id, { onDelete: "cascade" }),

        // TBE 세션 정보
        sessionCode: varchar("session_code", { length: 50 }).unique(), // TBE-2025-001
        sessionTitle: varchar("session_title", { length: 255 }),
        sessionType: varchar("session_type", { length: 50 })
            .$type<"initial" | "revision" | "final">()
            .default("initial"),

        // 상태 관리
        status: varchar("status", { length: 30 })
            .$type<"준비중" | "진행중" | "검토중" | "보류" | "완료" | "취소">()
            .notNull()
            .default("준비중"),

        // 평가 결과 (단순화)
        evaluationResult: varchar("evaluation_result", { length: 30 })
            .$type<"Acceptable" | "Acceptable with Comment" | "Not Acceptable" | null>(),

        // 조건부 승인 시 조건
        conditionalRequirements: text("conditional_requirements"),
        conditionsFulfilled: boolean("conditions_fulfilled").default(false),

        // 벤더 피드백 및 간단한 Q&A
        vendorRemarks: text("vendor_remarks"), // 벤더가 TBE 과정에서 남기는 의견
        vendorQuestionsLog: jsonb("vendor_questions_log").$type<{
            question: string;
            answer: string;
            askedAt: string;
            answeredAt: string;
            answeredBy: number;
        }[]>(), // 간단한 Q&A 로그

        // 일정 관리
        plannedStartDate: date("planned_start_date", { mode: "date" }).$type<Date | null>(),
        actualStartDate: timestamp("actual_start_date", { withTimezone: true }).$type<Date | null>(),
        plannedEndDate: date("planned_end_date", { mode: "date" }).$type<Date | null>(),
        actualEndDate: timestamp("actual_end_date", { withTimezone: true }).$type<Date | null>(),

        // 참여자 정보
        leadEvaluatorId: integer("lead_evaluator_id")
            .references(() => users.id, { onDelete: "set null" }),
        technicalEvaluatorId: integer("technical_evaluator_id")
            .references(() => users.id, { onDelete: "set null" }),
        commercialEvaluatorId: integer("commercial_evaluator_id")
            .references(() => users.id, { onDelete: "set null" }),

        // 평가 요약
        technicalSummary: text("technical_summary"),
        commercialSummary: text("commercial_summary"),
        overallRemarks: text("overall_remarks"),

        // 감사 필드
        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(),

        // 최종 승인
        approvedBy: integer("approved_by")
            .references(() => users.id, { onDelete: "set null" }),
        approvedAt: timestamp("approved_at", { withTimezone: true }).$type<Date | null>(),
        approvalRemarks: text("approval_remarks"),
    },
    (table) => ({
        // 하나의 RFQ-벤더 조합에 대해 활성 TBE는 하나만
        uniqueActiveTbe: uniqueIndex("unique_active_tbe")
            .on(table.rfqsLastId, table.vendorId, table.status)
            .where(sql`${table.status} IN ('준비중', '진행중', '검토중', '보류')`),
    })
);

// ==========================================
// 2. TBE 문서 검토 테이블
// ==========================================
export const rfqLastTbeDocumentReviews = pgTable(
    "rfq_last_tbe_document_reviews",
    {
        id: serial("id").primaryKey(),
        tbeSessionId: integer("tbe_session_id")
            .notNull()
            .references(() => rfqLastTbeSessions.id, { onDelete: "cascade" }),

        // 문서 참조 (구매자 문서 또는 벤더 문서)
        documentSource: varchar("document_source", { length: 20 })
            .$type<"buyer" | "vendor">()
            .notNull(),

        // 구매자 문서인 경우
        buyerAttachmentId: integer("buyer_attachment_id")
            .references(() => rfqLastAttachments.id, { onDelete: "cascade" }),
        buyerAttachmentRevisionId: integer("buyer_attachment_revision_id")
            .references(() => rfqLastAttachmentRevisions.id, { onDelete: "cascade" }),

        // 벤더 문서인 경우
        vendorAttachmentId: integer("vendor_attachment_id")
            .references(() => rfqLastTbeVendorDocuments.id, { onDelete: "cascade" }),

        // 검토 정보
        documentType: varchar("document_type", { length: 50 }),
        documentName: varchar("document_name", { length: 255 }),

        reviewStatus: varchar("review_status", { length: 30 })
            .$type<"미검토" | "검토중" | "검토완료" | "재검토필요" | "승인" | "반려">()
            .notNull()
            .default("미검토"),

        // 검토 결과
        technicalCompliance: boolean("technical_compliance"),
        qualityAcceptable: boolean("quality_acceptable"),
        requiresRevision: boolean("requires_revision").default(false),

        // 검토 의견
        reviewComments: text("review_comments"),
        revisionRequirements: text("revision_requirements"),

        // PDFTron 관련
        hasPdftronComments: boolean("has_pdftron_comments").default(false),
        pdftronDocumentId: varchar("pdftron_document_id", { length: 255 }),
        pdftronAnnotationCount: integer("pdftron_annotation_count").default(0),

        // 검토자 정보
        reviewedBy: integer("reviewed_by")
            .references(() => users.id, { onDelete: "set null" }),
        reviewedAt: timestamp("reviewed_at", { withTimezone: true }).$type<Date | null>(),

        // 추가 검토자 (복수 검토자 지원)
        additionalReviewers: jsonb("additional_reviewers").$type<number[]>(),

        createdAt: timestamp("created_at").defaultNow().notNull(),
        updatedAt: timestamp("updated_at").defaultNow().notNull(),
    },
    (table) => ({
        tbeSessionIdx: index("idx_tbe_session").on(table.tbeSessionId),
        documentSourceIdx: index("idx_document_source").on(table.documentSource),
        reviewStatusIdx: index("idx_review_status").on(table.reviewStatus),
    })
);

// ==========================================
// 3. PDFTron 코멘트 관리
// ==========================================
// 수정된 스키마 (버전 관리 제거)
export const rfqLastTbePdftronComments = pgTable(
    "rfq_last_tbe_pdftron_comments",
    {
        id: serial("id").primaryKey(),
        documentReviewId: integer("document_review_id")
            .notNull()
            .references(() => rfqLastTbeDocumentReviews.id, { onDelete: "cascade" }),

        // PDFTron 문서 식별자
        pdftronDocumentId: varchar("pdftron_document_id", { length: 255 }).notNull(),

        // XFDF XML 전체 저장 (모든 annotation 포함)
        xfdfString: text("xfdf_string").notNull(),

        // 파싱된 annotation 데이터 (검색/필터링용)
        annotationData: jsonb("annotation_data").$type<{
            annotations: {
                id: string;
                type: string;
                page: number;
                author: string;
                subject: string;
                contents?: string;
                color?: string;
                opacity?: number;
                createdDate: string;
                modifiedDate?: string;
                customData?: {
                    category?: "technical" | "commercial" | "quality" | "compliance" | "general";
                    severity?: "minor" | "major" | "critical";
                    status?: "open" | "resolved" | "rejected" | "deferred";
                    createdBy?: number;
                    createdByType?: "buyer" | "vendor";
                    resolvedBy?: number;
                    resolvedAt?: string;
                    resolutionNote?: string;
                };
                replies?: {
                    author: string;
                    contents: string;
                    createdDate: string;
                }[];
                coords?: number[]; // 좌표 데이터
            }[];
        }>(),

        // 요약 정보 (빠른 조회용)
        commentSummary: jsonb("comment_summary").$type<{
            total: number;
            open: number;
            resolved: number;
            rejected: number;
            deferred: number;
            byCategory: Record<string, number>;
            bySeverity: Record<string, number>;
            byAuthor: Record<string, number>;
        }>(),

        // 작성자 정보
        createdBy: integer("created_by")
            .notNull()
            .references(() => users.id, { onDelete: "restrict" }),
        createdByType: varchar("created_by_type", { length: 20 })
            .$type<"buyer" | "vendor">()
            .notNull(),

        // 마지막 수정자
        lastModifiedBy: integer("last_modified_by")
            .references(() => users.id, { onDelete: "set null" }),

        createdAt: timestamp("created_at").defaultNow().notNull(),
        updatedAt: timestamp("updated_at").defaultNow().notNull(),
    },
    (table) => ({
        documentReviewIdx: index("idx_pdftron_doc_review").on(table.documentReviewId),
        documentIdIdx: index("idx_pdftron_doc_id").on(table.pdftronDocumentId),

        // documentReviewId와 pdftronDocumentId 조합 유니크
        uniqueDocument: uniqueIndex("unique_document")
            .on(table.documentReviewId, table.pdftronDocumentId),
    })
)
// ==========================================
// 4. TBE 새로운 벤더 첨부파일 (TBE 중 추가 제출)
// ==========================================
export const rfqLastTbeVendorDocuments = pgTable(
    "rfq_last_tbe_vendor_documents",
    {
        id: serial("id").primaryKey(),
        tbeSessionId: integer("tbe_session_id")
            .notNull()
            .references(() => rfqLastTbeSessions.id, { onDelete: "cascade" }),

        // 문서 구분
        documentType: varchar("document_type", { length: 50 })
            .$type<"clarification" | "revised_proposal" | "additional_info" | "certificate" | "test_report" | "drawing">()
            .notNull(),

        // 문서 검토 요청에 대한 응답인 경우
        isResponseToReviewId: integer("is_response_to_review_id")
            .references(() => rfqLastTbeDocumentReviews.id, { onDelete: "set null" }),

        // 파일 정보
        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 }),

        // 문서 메타데이터
        documentNo: varchar("document_no", { length: 100 }),
        revisionNo: varchar("revision_no", { length: 20 }),
        issueDate: date("issue_date", { mode: "date" }).$type<Date | null>(),

        // 설명
        description: text("description"),
        submittalRemarks: text("submittal_remarks"),

        // 검토 상태
        reviewRequired: boolean("review_required").default(true),
        reviewStatus: varchar("review_status", { length: 30 })
            .$type<"pending" | "reviewing" | "approved" | "rejected">()
            .default("pending"),

        // 제출자 정보
        submittedBy: integer("submitted_by")
            .notNull()
            .references(() => users.id, { onDelete: "set null" }),
        submittedAt: timestamp("submitted_at").defaultNow().notNull(),

        // 검토자 정보
        reviewedBy: integer("reviewed_by")
            .references(() => users.id, { onDelete: "set null" }),
        reviewedAt: timestamp("reviewed_at", { withTimezone: true }).$type<Date | null>(),
        reviewComments: text("review_comments"),
    },
    (table) => ({
        tbeSessionIdx: index("idx_tbe_vendor_doc_session").on(table.tbeSessionId),
        reviewStatusIdx: index("idx_tbe_vendor_doc_status").on(table.reviewStatus),
    })
);

// ==========================================
// 5. TBE 이력 관리
// ==========================================
export const rfqLastTbeHistory = pgTable(
    "rfq_last_tbe_history",
    {
        id: serial("id").primaryKey(),
        tbeSessionId: integer("tbe_session_id")
            .notNull()
            .references(() => rfqLastTbeSessions.id, { onDelete: "cascade" }),

        // 액션 타입
        actionType: varchar("action_type", { length: 50 })
            .$type<"status_change" | "evaluation_update" | "document_review" | "approval">()
            .notNull(),

        // 상태 변경
        previousStatus: varchar("previous_status", { length: 30 }),
        newStatus: varchar("new_status", { length: 30 }),

        // 변경 내용
        changeDescription: text("change_description"),
        changeDetails: jsonb("change_details"),

        // 수행자
        performedBy: integer("performed_by")
            .notNull()
            .references(() => users.id, { onDelete: "set null" }),
        performedByType: varchar("performed_by_type", { length: 20 })
            .$type<"buyer" | "vendor">()
            .notNull(),

        performedAt: timestamp("performed_at").defaultNow().notNull(),

        // 추가 컨텍스트
        relatedEntityType: varchar("related_entity_type", { length: 50 }),
        relatedEntityId: integer("related_entity_id"),
        remarks: text("remarks"),
    },
    (table) => ({
        tbeSessionIdx: index("idx_history_tbe_session").on(table.tbeSessionId),
        actionTypeIdx: index("idx_history_action_type").on(table.actionType),
        performedAtIdx: index("idx_history_performed_at").on(table.performedAt),
    })
);

// ==========================================
// Views
// ==========================================

// TBE 세션 종합 뷰
export const tbeSessionSummaryView = pgView("tbe_session_summary_view").as((qb) => {
    const tbeSession = alias(rfqLastTbeSessions, "tbe");
    const rfq = alias(rfqsLast, "rfq");
    const vendor = alias(vendors, "vendor");
    const leadEvaluator = alias(users, "lead_evaluator");

    return qb
        .select({
            // TBE 기본 정보
            tbeId: sql<number>`${tbeSession.id}`.as("tbe_id"),
            sessionCode: sql<string>`${tbeSession.sessionCode}`.as("session_code"),
            sessionTitle: sql<string>`${tbeSession.sessionTitle}`.as("session_title"),

            // RFQ 정보
            rfqCode: sql<string>`${rfq.rfqCode}`.as("rfq_code"),

            // 벤더 정보
            vendorName: sql<string>`${vendor.vendorName}`.as("vendor_name"),
            vendorCode: sql<string>`${vendor.vendorCode}`.as("vendor_code"),

            // 상태 및 결과
            status: sql<string>`${tbeSession.status}`.as("status"),
            evaluationResult: sql<string | null>`${tbeSession.evaluationResult}`.as("evaluation_result"),

            // 평가자
            leadEvaluatorName: sql<string | null>`${leadEvaluator.name}`.as("lead_evaluator_name"),

            // 문서 검토 통계
            totalDocuments: sql<number>`(
                SELECT COUNT(*)
                FROM rfq_last_tbe_document_reviews
                WHERE tbe_session_id = ${tbeSession.id}
            )`.as("total_documents"),

            reviewedDocuments: sql<number>`(
                SELECT COUNT(*)
                FROM rfq_last_tbe_document_reviews
                WHERE tbe_session_id = ${tbeSession.id}
                AND review_status IN ('검토완료', '승인')
            )`.as("reviewed_documents"),

            // PDFTron 코멘트 통계
            totalComments: sql<number>`(
                SELECT COUNT(*)
                FROM rfq_last_tbe_pdftron_comments pc
                JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id
                WHERE dr.tbe_session_id = ${tbeSession.id}
            )`.as("total_comments"),


            // 일정
            actualStartDate: sql<Date | null>`${tbeSession.actualStartDate}`.as("actual_start_date"),
            actualEndDate: sql<Date | null>`${tbeSession.actualEndDate}`.as("actual_end_date"),

            // 타임스탬프
            createdAt: sql<Date>`${tbeSession.createdAt}`.as("created_at"),
            updatedAt: sql<Date>`${tbeSession.updatedAt}`.as("updated_at"),
        })
        .from(tbeSession)
        .leftJoin(rfq, eq(tbeSession.rfqsLastId, rfq.id))
        .leftJoin(vendor, eq(tbeSession.vendorId, vendor.id))
        .leftJoin(leadEvaluator, eq(tbeSession.leadEvaluatorId, leadEvaluator.id));
});

// Relations 정의
export const tbeSessionRelations = relations(
    rfqLastTbeSessions,
    ({ one, many }) => ({
        rfq: one(rfqsLast, {
            fields: [rfqLastTbeSessions.rfqsLastId],
            references: [rfqsLast.id],
        }),
        rfqDetail: one(rfqLastDetails, {
            fields: [rfqLastTbeSessions.rfqLastDetailsId],
            references: [rfqLastDetails.id],
        }),
        vendor: one(vendors, {
            fields: [rfqLastTbeSessions.vendorId],
            references: [vendors.id],
        }),
        leadEvaluator: one(users, {
            fields: [rfqLastTbeSessions.leadEvaluatorId],
            references: [users.id],
            relationName: "tbeLeadEvaluator",
        }),
        documentReviews: many(rfqLastTbeDocumentReviews),
        vendorDocuments: many(rfqLastTbeVendorDocuments),
        history: many(rfqLastTbeHistory),
    })
);

// Type exports
export type TbeSession = typeof rfqLastTbeSessions.$inferSelect;
export type TbeDocumentReview = typeof rfqLastTbeDocumentReviews.$inferSelect;
export type TbePdftronComment = typeof rfqLastTbePdftronComments.$inferSelect;
export type TbeVendorDocument = typeof rfqLastTbeVendorDocuments.$inferSelect;
export type TbeHistory = typeof rfqLastTbeHistory.$inferSelect;

// ==========================================
// TBE Last 종합 뷰
// ==========================================
export const tbeLastView = pgView("tbe_last_view").as((qb) => {
    const leadUser = alias(users, "lead_user");

    return qb
        .select({
            // TBE Session 기본 정보
            tbeSessionId: sql<number>`${rfqLastTbeSessions.id}`.as("tbe_session_id"),
            sessionCode: sql<string>`${rfqLastTbeSessions.sessionCode}`.as("session_code"),
            sessionTitle: sql<string>`${rfqLastTbeSessions.sessionTitle}`.as("session_title"),
            sessionType: sql<string>`${rfqLastTbeSessions.sessionType}`.as("session_type"),
            sessionStatus: sql<string>`${rfqLastTbeSessions.status}`.as("session_status"),
            evaluationResult: sql<string | null>`${rfqLastTbeSessions.evaluationResult}`.as("evaluation_result"),
            conditionalRequirements: sql<string | null>`${rfqLastTbeSessions.conditionalRequirements}`.as("conditional_requirements"),
            conditionsFulfilled: sql<boolean>`${rfqLastTbeSessions.conditionsFulfilled}`.as("conditions_fulfilled"),

            // RFQ 정보
            rfqId: sql<number>`${rfqsLast.id}`.as("rfq_id"),
            rfqCode: sql<string>`${rfqsLast.rfqCode}`.as("rfq_code"),
            rfqType: sql<string | null>`${rfqsLast.rfqType}`.as("rfq_type"),
            rfqTitle: sql<string | null>`${rfqsLast.rfqTitle}`.as("rfq_title"),
            series: sql<string | null>`${rfqsLast.series}`.as("series"),
            rfqStatus: sql<string>`${rfqsLast.status}`.as("rfq_status"),
            rfqDueDate: sql<Date | null>`${rfqsLast.dueDate}`.as("rfq_due_date"),
            picName: sql<string>`${rfqsLast.picName}`.as("pic_name"),
            EngPicName: sql<string>`${rfqsLast.EngPicName}`.as("eng_pic_name"),


            // 패키지 정보
            packageNo: sql<string | null>`${rfqsLast.packageNo}`.as("package_no"),
            packageName: sql<string | null>`${rfqsLast.packageName}`.as("package_name"),

            // PR 정보
            prNumber: sql<string | null>`${rfqsLast.prNumber}`.as("pr_number"),
            prIssueDate: sql<Date | null>`${rfqsLast.prIssueDate}`.as("pr_issue_date"),

            // 프로젝트 정보
            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"),
            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"),

            // 아이템 정보
            itemCode: sql<string | null>`${rfqsLast.itemCode}`.as("item_code"),
            itemName: sql<string | null>`${rfqsLast.itemName}`.as("item_name"),

            // 벤더 정보
            vendorId: sql<number>`${vendors.id}`.as("vendor_id"),
            vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
            vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
            vendorCountry: sql<string | null>`${vendors.country}`.as("vendor_country"),
            vendorEmail: sql<string | null>`${vendors.email}`.as("vendor_email"),
            vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"),

            // RFQ Detail 정보
            rfqDetailId: sql<number>`${rfqLastDetails.id}`.as("rfq_detail_id"),
            shortList: sql<boolean>`${rfqLastDetails.shortList}`.as("short_list"),
            returnYn: sql<boolean>`${rfqLastDetails.returnYn}`.as("return_yn"),

            // TBE 일정
            plannedStartDate: sql<Date | null>`${rfqLastTbeSessions.plannedStartDate}`.as("planned_start_date"),
            actualStartDate: sql<Date | null>`${rfqLastTbeSessions.actualStartDate}`.as("actual_start_date"),
            plannedEndDate: sql<Date | null>`${rfqLastTbeSessions.plannedEndDate}`.as("planned_end_date"),
            actualEndDate: sql<Date | null>`${rfqLastTbeSessions.actualEndDate}`.as("actual_end_date"),

            // 평가자 정보
            leadEvaluatorId: sql<number | null>`${rfqLastTbeSessions.leadEvaluatorId}`.as("lead_evaluator_id"),
            leadEvaluatorName: sql<string | null>`lead_user.name`.as("lead_evaluator_name"),

            // PR 아이템 수
            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"),

            // 구매자 문서 수 (설계 문서)
            buyerDocumentsCount: sql<number>`(
        SELECT COUNT(*)
        FROM rfq_last_tbe_document_reviews
        WHERE tbe_session_id = ${rfqLastTbeSessions.id}
        AND document_source = 'buyer'
      )`.as("buyer_documents_count"),

            // 벤더 문서 수
            vendorDocumentsCount: sql<number>`(
        SELECT COUNT(*)
        FROM rfq_last_tbe_vendor_documents
        WHERE tbe_session_id = ${rfqLastTbeSessions.id}
      )`.as("vendor_documents_count"),

            // 검토 완료 문서 수
            reviewedDocumentsCount: sql<number>`(
        SELECT COUNT(*)
        FROM rfq_last_tbe_document_reviews
        WHERE tbe_session_id = ${rfqLastTbeSessions.id}
        AND review_status IN ('검토완료', '승인')
      )`.as("reviewed_documents_count"),

            // PDFTron 코멘트 수
            totalCommentsCount: sql<number>`(
        SELECT COUNT(*)
        FROM rfq_last_tbe_pdftron_comments pc
        JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id
        WHERE dr.tbe_session_id = ${rfqLastTbeSessions.id}
      )`.as("total_comments_count"),

            unresolvedCommentsCount: sql<number>`(
        SELECT COUNT(*)
        FROM rfq_last_tbe_pdftron_comments pc
        JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id
        WHERE dr.tbe_session_id = ${rfqLastTbeSessions.id}
        AND pc.status = 'open'
      )`.as("unresolved_comments_count"),

            // 타임스탬프
            createdAt: sql<Date>`${rfqLastTbeSessions.createdAt}`.as("created_at"),
            updatedAt: sql<Date>`${rfqLastTbeSessions.updatedAt}`.as("updated_at"),
        })
        .from(rfqLastTbeSessions)
        .leftJoin(rfqsLast, sql`${rfqLastTbeSessions.rfqsLastId} = ${rfqsLast.id}`)
        .leftJoin(rfqLastDetails, sql`${rfqLastTbeSessions.rfqLastDetailsId} = ${rfqLastDetails.id}`)
        .leftJoin(vendors, sql`${rfqLastTbeSessions.vendorId} = ${vendors.id}`)
        .leftJoin(projects, sql`${rfqsLast.projectId} = ${projects.id}`)
        .leftJoin(leadUser, sql`${rfqLastTbeSessions.leadEvaluatorId} = ${leadUser.id}`)
});

// ==========================================
// TBE 문서 상세 뷰 (구매자 + 벤더 문서 통합)
// ==========================================
export const tbeDocumentsView = pgView("tbe_documents_view").as((qb) => {
    const dr = alias(rfqLastTbeDocumentReviews, "dr")
    const ba = alias(rfqLastAttachments, "ba")
    const baRev = alias(rfqLastAttachmentRevisions, "ba_rev")
    const vd = alias(rfqLastTbeVendorDocuments, "vd")

    return qb
        .select({
            // 기본키/세션
            documentReviewId: sql<number>`dr.id`.as("document_review_id"),
            tbeSessionId: sql<number>`dr.tbe_session_id`.as("tbe_session_id"),

            // 소스
            documentSource: sql<"buyer" | "vendor">`dr.document_source`.as("document_source"),

            // 문서 식별자: buyer면 buyerAttachmentId, vendor면 vendorAttachmentId
            documentId: sql<number | null>`
          CASE 
            WHEN dr.document_source = 'buyer' THEN dr.buyer_attachment_id
            WHEN dr.document_source = 'vendor' THEN dr.vendor_attachment_id
            ELSE NULL
          END
        `.as("document_id"),

            // 표시 정보
            documentType: sql<string | null>`dr.document_type`.as("document_type"),
            documentName: sql<string | null>`dr.document_name`.as("document_name"),

            // 파일 메타: buyer면 ba_rev.*, vendor면 vd.*
            originalFileName: sql<string | null>`COALESCE(ba_rev.original_file_name, vd.original_file_name)`.as("original_file_name"),
            filePath: sql<string | null>`COALESCE(ba_rev.file_path,         vd.file_path)`.as("file_path"),
            fileSize: sql<number | null>`COALESCE(ba_rev.file_size,         vd.file_size)`.as("file_size"),
            fileType: sql<string | null>`COALESCE(ba_rev.file_type,         vd.file_type)`.as("file_type"),

            // 리뷰 상태/정보 (dr 기준)
            reviewStatus: sql<string>`dr.review_status`.as("review_status"),
            technicalCompliance: sql<boolean | null>`dr.technical_compliance`.as("technical_compliance"),
            qualityAcceptable: sql<boolean | null>`dr.quality_acceptable`.as("quality_acceptable"),
            requiresRevision: sql<boolean>`COALESCE(dr.requires_revision, false)`.as("requires_revision"),

            // PDFTron
            hasPdftronComments: sql<boolean>`COALESCE(dr.has_pdftron_comments, false)`.as("has_pdftron_comments"),
            pdftronDocumentId: sql<string | null>`dr.pdftron_document_id`.as("pdftron_document_id"),
            pdftronAnnotationCount: sql<number>`COALESCE(dr.pdftron_annotation_count, 0)`.as("pdftron_annotation_count"),

            // 검토자/타임스탬프
            reviewedBy: sql<number | null>`dr.reviewed_by`.as("reviewed_by"),
            reviewedAt: sql<Date | null>`dr.reviewed_at`.as("reviewed_at"),
            reviewComments: sql<string | null>`dr.review_comments`.as("review_comments"),

            // 제출 정보(벤더 문서일 때 vd의 제출 정보 노출, 아니면 null)
            submittedBy: sql<number | null>`
          CASE WHEN dr.document_source = 'vendor' THEN vd.submitted_by ELSE NULL END
        `.as("submitted_by"),
            submittedAt: sql<Date | null>`
          CASE WHEN dr.document_source = 'vendor' THEN vd.submitted_at ELSE NULL END
        `.as("submitted_at"),

            // 생성/업데이트 시각: 리뷰기준
            createdAt: sql<Date>`dr.created_at`.as("created_at"),
            updatedAt: sql<Date>`dr.updated_at`.as("updated_at"),
        })
        .from(dr)
        // buyer: 리뷰가 가리키는 첨부 개정에 조인
        .leftJoin(ba, sql`dr.buyer_attachment_id = ${ba.id}`)
        .leftJoin(baRev, sql`dr.buyer_attachment_revision_id = ${baRev.id}`)
        // vendor: 리뷰가 가리키는 벤더 문서에 조인
        .leftJoin(vd, sql`dr.vendor_attachment_id = ${vd.id}`)
})

// Type exports
export type TbeLastView = typeof tbeLastView.$inferSelect;
export type TbeDocumentsView = typeof tbeDocumentsView.$inferSelect;