import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, jsonb } from "drizzle-orm/pg-core";
import { eq, sql, relations } from "drizzle-orm";
import { rfqsLast, rfqLastDetails, rfqPrItems } from "./rfqLast";
import { users } from "./users";
import { vendors } from "./vendors";
import { incoterms, paymentTerms } from "./procurementRFQ";

// ==========================================
// 1. 벤더 응답 메인 테이블 (견적서 헤더)
// ==========================================
export const rfqLastVendorResponses = pgTable(
    "rfq_last_vendor_responses",
    {
        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" }),
        
        // 응답 버전 관리
        responseVersion: integer("response_version").notNull().default(1),
        isLatest: boolean("is_latest").notNull().default(true),
        
        // 응답 상태
        status: varchar("status", { length: 30 })
            .$type<"초대됨" | "작성중" | "제출완료" | "수정요청" | "최종확정" | "취소">()
            .notNull()
            .default("초대됨"),
        
        // 제출 정보
        submittedAt: timestamp("submitted_at"),
        submittedBy: integer("submitted_by")
            .references(() => users.id, { onDelete: "set null" }),
        
        // 총액 정보
        totalAmount: numeric("total_amount", { precision: 15, scale: 2 }).$type<number>(),
        currency: varchar("currency", { length: 10 }).default("USD"),
        
        // 벤더 제안 조건 (구매자 제시와 다른 경우)
        vendorCurrency: varchar("vendor_currency", { length: 10 }),
        vendorPaymentTermsCode: varchar("vendor_payment_terms_code", { length: 50 })
            .references(() => paymentTerms.code, { onDelete: "set null" }),
        vendorIncotermsCode: varchar("vendor_incoterms_code", { length: 20 })
            .references(() => incoterms.code, { onDelete: "set null" }),
        vendorIncotermsDetail: varchar("vendor_incoterms_detail", { length: 255 }),
        vendorDeliveryDate: date("vendor_delivery_date", { mode: "date" }).$type<Date | null>(),
        vendorContractDuration: varchar("vendor_contract_duration", { length: 255 }),
        vendorTaxCode: varchar("vendor_tax_code", { length: 255 }),
        vendorPlaceOfShipping: varchar("vendor_place_of_shipping", { length: 255 }),
        vendorPlaceOfDestination: varchar("vendor_place_of_destination", { length: 255 }),
        
        // 초도품관리 응답
        vendorFirstYn: boolean("vendor_first_yn"),
        vendorFirstDescription: text("vendor_first_description"),
        vendorFirstAcceptance: varchar("vendor_first_acceptance", { length: 50 })
            .$type<"수용" | "부분수용" | "거부" | null>(),
        
        // Spare part 응답
        vendorSparepartYn: boolean("vendor_sparepart_yn"),
        vendorSparepartDescription: text("vendor_sparepart_description"),
        vendorSparepartAcceptance: varchar("vendor_sparepart_acceptance", { length: 50 })
            .$type<"수용" | "부분수용" | "거부" | null>(),
        
        // 연동제 적용 응답
        vendorMaterialPriceRelatedYn: boolean("vendor_material_price_related_yn"),
        vendorMaterialPriceRelatedReason: text("vendor_material_price_related_reason"),
        
        // 각 조건별 변경 사유 (구매자 제시와 다른 경우)
        currencyReason: text("currency_reason"),
        paymentTermsReason: text("payment_terms_reason"),
        deliveryDateReason: text("delivery_date_reason"),
        incotermsReason: text("incoterms_reason"),
        taxReason: text("tax_reason"),
        shippingReason: text("shipping_reason"),
        
        // 전체 비고 및 특이사항
        generalRemark: text("general_remark"),
        technicalProposal: text("technical_proposal"),
        
        // 감사 필드
        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(),
    }
);

// ==========================================
// 2. 벤더 견적 아이템 테이블 (PR 아이템별 가격)
// ==========================================
export const rfqLastVendorQuotationItems = pgTable(
    "rfq_last_vendor_quotation_items",
    {
        id: serial("id").primaryKey(),
        vendorResponseId: integer("vendor_response_id")
            .notNull()
            .references(() => rfqLastVendorResponses.id, { onDelete: "cascade" }),
        rfqPrItemId: integer("rfq_pr_item_id")
            .notNull()
            .references(() => rfqPrItems.id, { onDelete: "cascade" }),
        
        // PR 아이템 정보 (캐시)
        prNo: varchar("pr_no", { length: 50 }),
        materialCode: varchar("material_code", { length: 255 }),
        materialDescription: varchar("material_description", { length: 255 }),
        
        // 견적 정보
        quantity: numeric("quantity", { precision: 12, scale: 2 })
            .$type<number>()
            .notNull(),
        uom: varchar("uom", { length: 50 }),
        
        // 가격 정보
        unitPrice: numeric("unit_price", { precision: 15, scale: 4 })
            .$type<number>()
            .notNull(),
        totalPrice: numeric("total_price", { precision: 15, scale: 2 })
            .$type<number>()
            .notNull(),
        currency: varchar("currency", { length: 10 }).default("USD"),
        
        // 납기 정보
        vendorDeliveryDate: date("vendor_delivery_date", { mode: "date" }).$type<Date | null>(),
        leadTime: integer("lead_time"), // days
        
        // 제조사 정보
        manufacturer: varchar("manufacturer", { length: 255 }),
        manufacturerCountry: varchar("manufacturer_country", { length: 100 }),
        modelNo: varchar("model_no", { length: 255 }),
        
        // 기술 사양
        technicalCompliance: boolean("technical_compliance").default(true),
        alternativeProposal: text("alternative_proposal"),
        
        // 할인 정보
        discountRate: numeric("discount_rate", { precision: 5, scale: 2 }).$type<number>(),
        discountAmount: numeric("discount_amount", { precision: 15, scale: 2 }).$type<number>(),
        
        // 비고
        itemRemark: text("item_remark"),
        deviationReason: text("deviation_reason"), // 요구사항과 다른 경우 사유
        
        createdAt: timestamp("created_at").defaultNow().notNull(),
        updatedAt: timestamp("updated_at").defaultNow().notNull(),
    }
);

// ==========================================
// 4. 벤더 첨부파일 테이블
// ==========================================
export const rfqLastVendorAttachments = pgTable(
    "rfq_last_vendor_attachments",
    {
        id: serial("id").primaryKey(),
        vendorResponseId: integer("vendor_response_id")
            .notNull()
            .references(() => rfqLastVendorResponses.id, { onDelete: "cascade" }),
        
        // 첨부파일 구분
        attachmentType: varchar("attachment_type", { length: 50 }).notNull(),
        // "견적서", "기술제안서", "인증서", "카탈로그", "도면", "테스트성적서", "기타"
        
        documentNo: varchar("document_no", { length: 100 }), // 문서번호
        
        // 파일 정보
        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: text("description"),
        
        // 유효기간 (인증서 등)
        validFrom: date("valid_from", { mode: "date" }).$type<Date | null>(),
        validTo: date("valid_to", { mode: "date" }).$type<Date | null>(),
        
        // 감사 필드
        uploadedBy: integer("uploaded_by")
            .notNull()
            .references(() => users.id, { onDelete: "set null" }),
        uploadedAt: timestamp("uploaded_at").defaultNow().notNull(),
    }
);

// ==========================================
// 4. 벤더 응답 이력 테이블 (변경 이력 관리)
// ==========================================
export const rfqLastVendorResponseHistory = pgTable(
    "rfq_last_vendor_response_history",
    {
        id: serial("id").primaryKey(),
        vendorResponseId: integer("vendor_response_id")
            .notNull()
            .references(() => rfqLastVendorResponses.id, { onDelete: "cascade" }),
        
        action: varchar("action", { length: 50 }).notNull(),
        // "생성", "수정", "제출", "철회", "재제출", "승인", "반려"
        
        previousStatus: varchar("previous_status", { length: 30 }),
        newStatus: varchar("new_status", { length: 30 }),
        
        changeDetails: jsonb("change_details"), // 변경된 필드들
        changeReason: text("change_reason"),
        
        performedBy: integer("performed_by")
            .notNull()
            .references(() => users.id, { onDelete: "set null" }),
        performedAt: timestamp("performed_at").defaultNow().notNull(),
    }
);

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

// 벤더 응답 종합 뷰
export const vendorResponseSummaryView = pgView("vendor_response_summary_view").as((qb) => {
    const responseTable = alias(rfqLastVendorResponses, "response");
    const rfqTable = alias(rfqsLast, "rfq");
    const detailTable = alias(rfqLastDetails, "detail");
    const vendorTable = alias(vendors, "vendor");
    const submittedByUser = alias(users, "submitted_by_user");

    return qb
        .select({
            // 응답 기본 정보
            responseId: sql<number>`${responseTable.id}`.as("response_id"),
            rfqId: sql<number>`${rfqTable.id}`.as("rfq_id"),
            rfqCode: sql<string>`${rfqTable.rfqCode}`.as("rfq_code"),
            
            // 벤더 정보
            vendorId: sql<number>`${vendorTable.id}`.as("vendor_id"),
            vendorName: sql<string>`${vendorTable.vendorName}`.as("vendor_name"),
            vendorCode: sql<string>`${vendorTable.vendorCode}`.as("vendor_code"),
            
            // 응답 상태
            responseVersion: sql<number>`${responseTable.responseVersion}`.as("response_version"),
            status: sql<string>`${responseTable.status}`.as("status"),
            isLatest: sql<boolean>`${responseTable.isLatest}`.as("is_latest"),
            
            // 제출 정보
            submittedAt: sql<Date | null>`${responseTable.submittedAt}`.as("submitted_at"),
            submittedByName: sql<string | null>`${submittedByUser.name}`.as("submitted_by_name"),
            
            // 금액 정보
            totalAmount: sql<number | null>`${responseTable.totalAmount}`.as("total_amount"),
            currency: sql<string>`${responseTable.currency}`.as("currency"),
            
            // 주요 조건 차이
            deliveryDateMatch: sql<boolean>`
                CASE 
                    WHEN ${detailTable.deliveryDate} = ${responseTable.vendorDeliveryDate} THEN true
                    ELSE false
                END
            `.as("delivery_date_match"),
            
            currencyMatch: sql<boolean>`
                CASE 
                    WHEN ${detailTable.currency} = ${responseTable.vendorCurrency} THEN true
                    ELSE false
                END
            `.as("currency_match"),
            
            // 아이템 수
            quotedItemCount: sql<number>`(
                SELECT COUNT(*)
                FROM rfq_last_vendor_quotation_items
                WHERE vendor_response_id = ${responseTable.id}
            )`.as("quoted_item_count"),
            
            // 첨부파일 수
            attachmentCount: sql<number>`(
                SELECT COUNT(*)
                FROM rfq_last_vendor_attachments
                WHERE vendor_response_id = ${responseTable.id}
            )`.as("attachment_count"),
            
            // 타임스탬프
            createdAt: sql<Date>`${responseTable.createdAt}`.as("created_at"),
            updatedAt: sql<Date>`${responseTable.updatedAt}`.as("updated_at"),
        })
        .from(responseTable)
        .leftJoin(rfqTable, eq(responseTable.rfqsLastId, rfqTable.id))
        .leftJoin(detailTable, eq(responseTable.rfqLastDetailsId, detailTable.id))
        .leftJoin(vendorTable, eq(responseTable.vendorId, vendorTable.id))
        .leftJoin(submittedByUser, eq(responseTable.submittedBy, submittedByUser.id));
});

// 벤더 견적 아이템 상세 뷰
export const vendorQuotationItemsDetailView = pgView("vendor_quotation_items_detail_view").as((qb) => {
    const quotationTable = alias(rfqLastVendorQuotationItems, "quotation");
    const prItemTable = alias(rfqPrItems, "pr_item");
    const responseTable = alias(rfqLastVendorResponses, "response");
    const rfqTable = alias(rfqsLast, "rfq");

    return qb
        .select({
            // 견적 아이템 정보
            quotationItemId: sql<number>`${quotationTable.id}`.as("quotation_item_id"),
            vendorResponseId: sql<number>`${quotationTable.vendorResponseId}`.as("vendor_response_id"),
            
            // RFQ 정보
            rfqCode: sql<string>`${rfqTable.rfqCode}`.as("rfq_code"),
            
            // PR 아이템 정보
            prNo: sql<string | null>`${prItemTable.prNo}`.as("pr_no"),
            prItem: sql<string | null>`${prItemTable.prItem}`.as("pr_item"),
            materialCode: sql<string | null>`${prItemTable.materialCode}`.as("material_code"),
            materialDescription: sql<string | null>`${prItemTable.materialDescription}`.as("material_description"),
            originalQuantity: sql<number | null>`${prItemTable.quantity}`.as("original_quantity"),
            originalDeliveryDate: sql<Date | null>`${prItemTable.deliveryDate}`.as("original_delivery_date"),
            
            // 벤더 견적 정보
            quotedQuantity: sql<number>`${quotationTable.quantity}`.as("quoted_quantity"),
            unitPrice: sql<number>`${quotationTable.unitPrice}`.as("unit_price"),
            totalPrice: sql<number>`${quotationTable.totalPrice}`.as("total_price"),
            currency: sql<string>`${quotationTable.currency}`.as("currency"),
            vendorDeliveryDate: sql<Date | null>`${quotationTable.vendorDeliveryDate}`.as("vendor_delivery_date"),
            leadTime: sql<number | null>`${quotationTable.leadTime}`.as("lead_time"),
            
            // 제조사 정보
            manufacturer: sql<string | null>`${quotationTable.manufacturer}`.as("manufacturer"),
            modelNo: sql<string | null>`${quotationTable.modelNo}`.as("model_no"),
            
            // 할인 정보
            discountRate: sql<number | null>`${quotationTable.discountRate}`.as("discount_rate"),
            discountAmount: sql<number | null>`${quotationTable.discountAmount}`.as("discount_amount"),
            
            // 기술 준수
            technicalCompliance: sql<boolean>`${quotationTable.technicalCompliance}`.as("technical_compliance"),
            alternativeProposal: sql<string | null>`${quotationTable.alternativeProposal}`.as("alternative_proposal"),
            
            // 비고
            itemRemark: sql<string | null>`${quotationTable.itemRemark}`.as("item_remark"),
            deviationReason: sql<string | null>`${quotationTable.deviationReason}`.as("deviation_reason"),
        })
        .from(quotationTable)
        .leftJoin(prItemTable, eq(quotationTable.rfqPrItemId, prItemTable.id))
        .leftJoin(responseTable, eq(quotationTable.vendorResponseId, responseTable.id))
        .leftJoin(rfqTable, eq(responseTable.rfqsLastId, rfqTable.id));
});

// Relations
export const vendorResponseRelations = relations(
    rfqLastVendorResponses,
    ({ one, many }) => ({
        rfq: one(rfqsLast, {
            fields: [rfqLastVendorResponses.rfqsLastId],
            references: [rfqsLast.id],
        }),
        rfqDetail: one(rfqLastDetails, {
            fields: [rfqLastVendorResponses.rfqLastDetailsId],
            references: [rfqLastDetails.id],
        }),
        vendor: one(vendors, {
            fields: [rfqLastVendorResponses.vendorId],
            references: [vendors.id],
        }),
        quotationItems: many(rfqLastVendorQuotationItems),
        attachments: many(rfqLastVendorAttachments),
        history: many(rfqLastVendorResponseHistory),
    })
);

// Type exports
export type VendorResponse = typeof rfqLastVendorResponses.$inferSelect;
export type VendorQuotationItem = typeof rfqLastVendorQuotationItems.$inferSelect;
export type VendorAttachment = typeof rfqLastVendorAttachments.$inferSelect;
export type VendorResponseHistory = typeof rfqLastVendorResponseHistory.$inferSelect;