path: root/db/schema/vendors.ts

// db/schema/vendors.ts
import {  pgTable, serial, varchar, text, timestamp, boolean, integer ,pgView } from "drizzle-orm/pg-core";
import { items, materials } from "./items";
import { sql, eq, relations } from "drizzle-orm";


// vendorTypes 테이블 생성
export const vendorTypes = pgTable("vendor_types", {
  id: serial("id").primaryKey(),
  code: varchar("code", { length: 50 }).notNull().unique(),
  nameKo: varchar("name_ko", { length: 255 }).notNull(),
  nameEn: varchar("name_en", { length: 255 }).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});


export const vendors = pgTable("vendors", {
  id: serial("id").primaryKey(),
  vendorName: varchar("vendor_name", { length: 255 }).notNull(),
  // 벤더 코드 유니크 아니어도 괜찮은지?
  vendorCode: varchar("vendor_code", { length: 100 }),
  //사업자번호이고, 법인등록번호는 corporateRegistrationNumber
  // MDG에서 taxId 없이 수신되는 벤더도 저장 가능하도록 nullable로 변경
  taxId: varchar("tax_id", { length: 100 }),
  address: text("address"),
  addressDetail: text("address_detail"),
  postalCode: varchar("postal_code", { length: 20 }),
  country: varchar("country", { length: 100 }),
  phone: varchar("phone", { length: 50 }),
  email: varchar("email", { length: 255 }),
  website: varchar("website", { length: 255 }),
  status: varchar("status", {
    length: 30,
    enum: [
      "PENDING_REVIEW",   // 가입 신청 중 (초기 신청)
      "IN_REVIEW",        // 심사 중
      "REJECTED",         // 심사 거부됨
      "IN_PQ",            // PQ 진행 중
      "PQ_SUBMITTED",     // PQ 제출
      "PQ_FAILED",        // PQ 실패
      "PQ_APPROVED",      // PQ 통과, 승인됨
      "APPROVED",         // PQ 통과, 승인됨
      "READY_TO_SEND",         // PQ 통과, 승인됨
      "ACTIVE",           // 활성 상태 (실제 거래 중)
      "INACTIVE",         // 비활성 상태 (일시적)
      "BLACKLISTED",      // 거래 금지 상태
    ]
  })
    .notNull()
    .default("PENDING_REVIEW"),
  vendorTypeId: integer("vendor_type_id").references(() => vendorTypes.id),
  representativeName: varchar("representative_name", { length: 255 }),
  representativeBirth: varchar("representative_birth", { length: 20 }),
  representativeEmail: varchar("representative_email", { length: 255 }),
  representativePhone: varchar("representative_phone", { length: 50 }),
  representativeWorkExpirence: boolean("representative_work_expirence").default(false),
  corporateRegistrationNumber: varchar("corporate_registration_number", {
    length: 100,
  }),
  items: text("items"), // 이전 입력값들이 필요 없을 때 제거할 것. 벤더의 공급품목은 vendorPossibleMaterials 테이블에서 관리함.

  creditAgency: varchar("credit_agency", { length: 50 }),
  creditRating: varchar("credit_rating", { length: 50 }),
  cashFlowRating: varchar("cash_flow_rating", { length: 50 }),

  businessSize: varchar("business_size", { length: 255 }),

  // 성조회 가입여부: 공제회 가입여부이며, 구매에서 직접 입력하겠다는 값임. 'E'=해당없음, 'Y'=가입, 'N'=미가입, null='-'
  isAssociationMember: varchar("is_association_member", { length: 1 }),

  // // PQ 승인시 받을 정보
  // // 그룹사
  // groupCompany: varchar("group_company", { length: 255 }),
  // // 설립일
  // establishmentDate: timestamp("establishment_date"),
  // // Fax 주소
  // faxAddress: varchar("fax_address", { length: 255 }),
  // // 임직원 수
  // employeeCount: varchar("employee_count", { length: 255 }),
  // // 연간 매출액
  // annualSales: text("annual_sales"),
  // // 자산
  // assets: text("assets"),
  // // 생산능력
  // productionCapacity: text("production_capacity"),
  // // 조직도
  // organizationChart: text("organization_chart"),


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

  // VENDOR_GENERAL 테이블은 별도 테이블로 분리함
});

export const vendorContacts = pgTable("vendor_contacts", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  contactName: varchar("contact_name", { length: 255 }).notNull(),
  contactPosition: varchar("contact_position", { length: 100 }),
  contactDepartment: varchar("contact_department", { length: 100 }),
  contactTask: varchar("contact_task", { length: 100 }),
  contactEmail: varchar("contact_email", { length: 255 }).notNull(),
  contactPhone: varchar("contact_phone", { length: 50 }),
  isPrimary: boolean("is_primary").default(false).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});


export const vendorPossibleItems = pgTable("vendor_possible_items", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  // itemId: integer("item_id"), // 별도 item 테이블 연동시
  itemCode: varchar("item_code", { length: 100 })
    .notNull()
    .references(() => items.itemCode, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});


// MDG 자재마스터에 대해 벤더의 공급품목 정보를 저장하는 테이블
// 업체 회원 가입시 입력 정보는 업체입력정보(notConfirmed), 구매담당자가 PQ, 실사, 정규업체등록요청 했을 때, PO I/F 통해 받은 자재그룹코드는 확정정보(Confirmed)임
// MDG 자재마스터와의 정규화 레벨은 의도적으로 낮춤. (스캔 비용)
export const vendorPossibleMaterials = pgTable("vendor_possible_materials", {
  // 인공키
  id: serial("id").primaryKey(),
  // 벤더 아이디
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  
  // 자재그룹코드 (MATKL)
  itemCode: varchar("item_code", { length: 100 }),
  // 자재그룹명 (= 자재명)
  itemName: varchar("item_name", { length: 255 }),
  // 업체유형 (vendorType에서 가져오거나 강미경프로가 제공하겠다는 자재그룹코드별 업체유형 정보를 저장. confirm 건들은 강미경프로 제공 데이터를, notConfirmed 건들은 vendorType 정보 사용)
  vendorType: varchar("vendor_type", { length: 255 }),

  registerUserId: integer("register_user_id"), // 등록자ID (추후 필요시 FK 세팅)
  registerUserName: varchar("register_user_name", { length: 255 }), // 등록자명

  // 확정정보 여부 (업체입력시 확정정보 아님, 구매담당자가 PQ, 실사, 정규업체등록요청 했을 때, PO I/F 통해 받은 자재그룹코드는 확정정보임)
  isConfirmed: boolean("is_confirmed").default(false),
  
  // 확정정보에 한해 필요한 부가정보 (시작)
  recentPoNo: varchar("recent_po_no", { length: 100 }), // 최근 Po. No
  recentPoDate: timestamp("recent_po_date"), // 최근 Po일
  recentDeliveryDate: timestamp("recent_delivery_date"), // 최근 납품일
  recentOrderDate: timestamp("recent_order_date"), // 최근 발주일
  recentOrderUserName: varchar("recent_order_user_name", { length: 255 }), // 최근 발주자명
  purchaseGroupCode: varchar("purchase_group_code", { length: 10 }), // 구매그룹코드
  // 확정정보에 한해 필요한 부가정보 (끝)
  
  createdAt: timestamp("created_at").defaultNow().notNull(), // 등록일
  updatedAt: timestamp("updated_at").defaultNow().notNull(), // 수정일
});

export const vendorItemsView = pgView("vendor_items_view").as((qb) => {
  return qb
    .select({
      // vendorPossibleItems의 "id" -> "vendorItemId"
      vendorItemId: vendorPossibleItems.id,
      vendorId: vendorPossibleItems.vendorId,

      // items의 "id" -> "itemId"
      // itemId: items.id,
      itemName: items.itemName,
      itemCode: items.itemCode,
      description: items.description,

      createdAt: vendorPossibleItems.createdAt,
      updatedAt: vendorPossibleItems.updatedAt,
    })
    .from(vendorPossibleItems)
    .leftJoin(items, eq(vendorPossibleItems.itemCode, items.itemCode))
})

export const vendorMaterialsView = pgView("vendor_materials_view").as((qb) => {
  return qb
    .select({
      // vendorPossibleItems의 "id" -> "vendorItemId"
      vendorItemId: vendorPossibleMaterials.id,
      vendorId: vendorPossibleMaterials.vendorId,

      // items의 "id" -> "itemId"
      // itemId: items.id,
      itemName: materials.itemName,
      itemCode: materials.itemCode,
      description: materials.description,
      unitOfMeasure: materials.unitOfMeasure,
      steelType: materials.steelType,
      gradeMaterial: materials.gradeMaterial,

      createdAt: vendorPossibleMaterials.createdAt,
      updatedAt: vendorPossibleMaterials.updatedAt,
    })
    .from(vendorPossibleMaterials)
    .leftJoin(materials, eq(vendorPossibleMaterials.itemCode, materials.itemCode))
})

export const vendorAttachments = pgTable("vendor_attachments", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").references(() => vendors.id),
  fileType: varchar("file_type", { length: 255 }),
  fileName: varchar("file_name", { length: 255 }).notNull(),
  filePath: varchar("file_path", { length: 1024 }).notNull(),
  attachmentType: varchar("attachment_type", {
    length: 50,
  }).default("GENERAL"),

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


export type Vendor = typeof vendors.$inferSelect
export type VendorContact = typeof vendorContacts.$inferSelect
export type VendorItem = typeof vendorPossibleItems.$inferSelect
export type VendorAttach = typeof vendorAttachments.$inferSelect

export type VendorWithAttachments = Vendor & {
  hasAttachments?: boolean;
  attachmentsList?: VendorAttach[];
}

export type VendorItemsView = typeof vendorItemsView.$inferSelect
export type VendorMaterialsView = typeof vendorMaterialsView.$inferSelect


export const vendorCandidates = pgTable("vendor_candidates", {
  id: serial("id").primaryKey(),
  companyName: varchar("company_name", { length: 255 }).notNull(),
  contactEmail: varchar("contact_email", { length: 255 }),
  contactPhone: varchar("contact_phone", { length: 50 }),
  
  // 후보 업체는 사업자번호가 없을 수 있으므로 nullable로 변경
  taxId: varchar("tax_id", { length: 100 }),
  address: text("address"),

  country: varchar("country", { length: 100 }),
  // 웹 크롤링 등으로 얻은 상태나 분류
  source: varchar("source", { length: 100 }),        // 수집 출처
  status: varchar("status", {
    length: 30,
    enum: [
      "COLLECTED",  // 단순 데이터 수집 완료
      "INVITED",    // 초청 메일 발송
      "DISCARDED",  // 불필요, 검토 후 배제됨
    ],
  })
    .notNull()
    .default("COLLECTED"),
  remark: text("remark"),
  items: text("items").notNull(),
  vendorId: integer("vendor_id")
    .references(() => vendors.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export type VendorCandidates = typeof vendorCandidates.$inferSelect;


export const vendorDetailView = pgView("vendor_detail_view").as((qb) => {
  return qb
    .select({
      // 협력업체 기본 정보
      id: vendors.id,
      vendorName: vendors.vendorName,
      vendorCode: vendors.vendorCode,
      taxId: vendors.taxId,
      address: vendors.address,
      addressDetail: vendors.addressDetail,
      postalCode: vendors.postalCode,
      businessSize:vendors.businessSize,
      country: vendors.country,
      phone: vendors.phone,
      email: vendors.email,
      website: vendors.website,
      status: vendors.status,
      representativeName: vendors.representativeName,
      representativeBirth: vendors.representativeBirth,
      representativeEmail: vendors.representativeEmail,
      representativePhone: vendors.representativePhone,
      corporateRegistrationNumber: vendors.corporateRegistrationNumber,
      creditAgency: vendors.creditAgency,
      creditRating: vendors.creditRating,
      cashFlowRating: vendors.cashFlowRating,
      createdAt: vendors.createdAt,
      updatedAt: vendors.updatedAt,
      
      // 연락처 정보 (수정된 버전)
      contacts: sql<string>`
        (SELECT COALESCE(
          json_agg(
            json_build_object(
              'id', c.id,
              'contactName', c.contact_name,
              'contactPosition', c.contact_position,
              'contactEmail', c.contact_email,
              'contactPhone', c.contact_phone,
              'isPrimary', c.is_primary
            )
          ),
          '[]'::json
        )
        FROM vendor_contacts c
        WHERE c.vendor_id = vendors.id)
      `.as("contacts"),
      
      // 첨부파일 정보 (수정된 버전)
      attachments: sql<string>`
        (SELECT COALESCE(
          json_agg(
            json_build_object(
              'id', a.id,
              'fileName', a.file_name,
              'filePath', a.file_path,
              'attachmentType', a.attachment_type,
              'createdAt', a.created_at
            )
            ORDER BY a.attachment_type, a.created_at DESC
          ),
          '[]'::json
        )
        FROM vendor_attachments a
        WHERE a.vendor_id = vendors.id)
      `.as("attachments"),
      
      // 첨부파일 수 (수정된 버전)
      attachmentCount: sql<number>`
        (SELECT COUNT(*)
        FROM vendor_attachments a
        WHERE a.vendor_id = vendors.id)
      `.as("attachment_count"),
      
      // 연락처 수 (수정된 버전)
      contactCount: sql<number>`
        (SELECT COUNT(*) 
        FROM vendor_contacts c
        WHERE c.vendor_id = vendors.id)
      `.as("contact_count")
    })
    .from(vendors)
});

// 타입 정의
export type VendorDetailView = typeof vendorDetailView.$inferSelect;


// 관계 설정 (DrizzleORM을 사용한다고 가정)
export const vendorRelations = relations(vendors, ({ one }) => ({
  vendorType: one(vendorTypes, {
    fields: [vendors.vendorTypeId],
    references: [vendorTypes.id],
  }),
}));

export const vendorTypeRelations = relations(vendorTypes, ({ many }) => ({
  vendors: many(vendors),
}));

export type VendorTypes = typeof vendorTypes.$inferSelect;




export const vendorCandidatesWithVendorInfo = pgView("vendor_candidates_with_vendor_info").as((qb) => {
  return qb
    .select({
      // ----------------------------------------
      // 1) vendorCandidates 기본 필드
      id: vendorCandidates.id,
      companyName: vendorCandidates.companyName,
      contactEmail: vendorCandidates.contactEmail,
      contactPhone: vendorCandidates.contactPhone,
      taxId: vendorCandidates.taxId,
      address: vendorCandidates.address,
      country: vendorCandidates.country,
      source: vendorCandidates.source,
      status: vendorCandidates.status,
      items: vendorCandidates.items,
      remark: vendorCandidates.remark,
      // remark, items, vendorId 등 필요한 필드도 추가
      createdAt: vendorCandidates.createdAt,
      updatedAt: vendorCandidates.updatedAt,

      // ----------------------------------------
      // 2) vendors 조인해서 가져올 필드
      vendorName: vendors.vendorName,
      vendorCode: vendors.vendorCode,
      vendorCreatedAt: sql<Date>`${vendors.createdAt}`.as("vendor_created_at"),

      // ----------------------------------------
      // 3) 마지막 상태 변경 시각, 변경자 (action = 'status_change')
      lastStatusChangeAt: sql<Date>`(
        SELECT l2."created_at"
          FROM "vendor_candidate_logs" l2
         WHERE l2."vendor_candidate_id" = "vendor_candidates"."id"
           AND l2."action" = 'status_change'
         ORDER BY l2."created_at" DESC
         LIMIT 1
      )`.as("last_status_change_at"),

      lastStatusChangeBy: sql<string>`(
        SELECT u."name"
          FROM "users" u
          JOIN "vendor_candidate_logs" l3
            ON l3."user_id" = u."id"
         WHERE l3."vendor_candidate_id" = "vendor_candidates"."id"
           AND l3."action" = 'status_change'
         ORDER BY l3."created_at" DESC
         LIMIT 1
      )`.as("last_status_change_by"),

      // ----------------------------------------
      // 4) 마지막 초청(Invite) 시각, 초청자 (action = 'invite_sent')
      lastInvitationAt: sql<Date>`(
        SELECT l4."created_at"
          FROM "vendor_candidate_logs" l4
         WHERE l4."vendor_candidate_id" = "vendor_candidates"."id"
           AND l4."action" = 'invite_sent'
         ORDER BY l4."created_at" DESC
         LIMIT 1
      )`.as("last_invitation_at"),

      lastInvitationBy: sql<string>`(
        SELECT u2."name"
          FROM "users" u2
          JOIN "vendor_candidate_logs" l5
            ON l5."user_id" = u2."id"
         WHERE l5."vendor_candidate_id" = "vendor_candidates"."id"
           AND l5."action" = 'invite_sent'
         ORDER BY l5."created_at" DESC
         LIMIT 1
      )`.as("last_invitation_by"),
    })
    .from(vendorCandidates)
    .leftJoin(vendors, eq(vendorCandidates.vendorId, vendors.id));
});

export type VendorCandidatesWithVendorInfo = typeof vendorCandidatesWithVendorInfo.$inferSelect;


export const vendorsWithTypesView = pgView("vendors_with_types").as((qb) => {
  return qb
    .select({
      // Vendor fields
      id: sql<number>`${vendors.id}`.as("id"),
      vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
      vendorCode: sql<string>`${vendors.vendorCode}`.as("vendor_code"),
      taxId: sql<string>`${vendors.taxId}`.as("tax_id"),
      address: sql<string>`${vendors.address}`.as("address"),
      addressDetail: sql<string>`${vendors.addressDetail}`.as("address_detail"),
      postalCode: sql<string>`${vendors.postalCode}`.as("postal_code"),
      country: sql<string>`${vendors.country}`.as("country"),
      phone: sql<string>`${vendors.phone}`.as("phone"),
      email: sql<string>`${vendors.email}`.as("email"),
      businessSize: sql<string>`${vendors.businessSize}`.as("business_size"),
      website: sql<string>`${vendors.website}`.as("website"),
      status: sql<string>`${vendors.status}`.as("status"),
      vendorTypeId: sql<number>`${vendors.vendorTypeId}`.as("vendor_type_id"),
      representativeName: sql<string>`${vendors.representativeName}`.as("representative_name"),
      representativeBirth: sql<string>`${vendors.representativeBirth}`.as("representative_birth"),
      representativeEmail: sql<string>`${vendors.representativeEmail}`.as("representative_email"),
      representativePhone: sql<string>`${vendors.representativePhone}`.as("representative_phone"),
      corporateRegistrationNumber: sql<string>`${vendors.corporateRegistrationNumber}`.as("corporate_registration_number"),
      items: sql<string>`${vendors.items}`.as("items"),
      creditAgency: sql<string>`${vendors.creditAgency}`.as("credit_agency"),
      creditRating: sql<string>`${vendors.creditRating}`.as("credit_rating"),
      cashFlowRating: sql<string>`${vendors.cashFlowRating}`.as("cash_flow_rating"),
      isAssociationMember: sql<string>`${vendors.isAssociationMember}`.as("is_association_member"),
      createdAt: sql<Date>`${vendors.createdAt}`.as("created_at"),
      updatedAt: sql<Date>`${vendors.updatedAt}`.as("updated_at"),

      // Vendor type fields
      vendorTypeName: sql<string>`${vendorTypes.nameKo}`.as("vendor_type_name"),
      vendorTypeNameEn: sql<string>`${vendorTypes.nameEn}`.as("vendor_type_name_en"),
      vendorTypeCode: sql<string>`${vendorTypes.code}`.as("vendor_type_code"),

      // Computed vendor category field
      vendorCategory: sql<string>`
        CASE
          WHEN ${vendors.status} = 'ACTIVE' THEN '정규업체'
          WHEN ${vendors.status} IN ('INACTIVE', 'BLACKLISTED', 'REJECTED') THEN ''
          ELSE '잠재업체'
        END
      `.as("vendor_category")
    })
    .from(vendors)
    .leftJoin(
      vendorTypes,
      sql`${vendors.vendorTypeId} = ${vendorTypes.id}`
    );
});

// 확장된 업체 뷰 - 대표품목 정보 포함
export const vendorsWithTypesAndMaterialsView = pgView("vendors_with_types_and_materials").as((qb) => {
  return qb
    .select({
      // 기존 모든 필드들
      id: sql<number>`${vendors.id}`.as("id"),
      vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
      vendorCode: sql<string>`${vendors.vendorCode}`.as("vendor_code"),
      taxId: sql<string>`${vendors.taxId}`.as("tax_id"),
      address: sql<string>`${vendors.address}`.as("address"),
      addressDetail: sql<string>`${vendors.addressDetail}`.as("address_detail"),
      postalCode: sql<string>`${vendors.postalCode}`.as("postal_code"),
      country: sql<string>`${vendors.country}`.as("country"),
      phone: sql<string>`${vendors.phone}`.as("phone"),
      email: sql<string>`${vendors.email}`.as("email"),
      businessSize: sql<string>`${vendors.businessSize}`.as("business_size"),
      website: sql<string>`${vendors.website}`.as("website"),
      status: sql<string>`${vendors.status}`.as("status"),
      vendorTypeId: sql<number>`${vendors.vendorTypeId}`.as("vendor_type_id"),
      representativeName: sql<string>`${vendors.representativeName}`.as("representative_name"),
      representativeBirth: sql<string>`${vendors.representativeBirth}`.as("representative_birth"),
      representativeEmail: sql<string>`${vendors.representativeEmail}`.as("representative_email"),
      representativePhone: sql<string>`${vendors.representativePhone}`.as("representative_phone"),
      corporateRegistrationNumber: sql<string>`${vendors.corporateRegistrationNumber}`.as("corporate_registration_number"),
      items: sql<string>`${vendors.items}`.as("items"),
      creditAgency: sql<string>`${vendors.creditAgency}`.as("credit_agency"),
      creditRating: sql<string>`${vendors.creditRating}`.as("credit_rating"),
      cashFlowRating: sql<string>`${vendors.cashFlowRating}`.as("cash_flow_rating"),
      isAssociationMember: sql<string>`${vendors.isAssociationMember}`.as("is_association_member"),
      createdAt: sql<Date>`${vendors.createdAt}`.as("created_at"),
      updatedAt: sql<Date>`${vendors.updatedAt}`.as("updated_at"),

      // Vendor type fields
      vendorTypeName: sql<string>`${vendorTypes.nameKo}`.as("vendor_type_name"),
      vendorTypeNameEn: sql<string>`${vendorTypes.nameEn}`.as("vendor_type_name_en"),
      vendorTypeCode: sql<string>`${vendorTypes.code}`.as("vendor_type_code"),

      // Computed vendor category field
      vendorCategory: sql<string>`
        CASE
          WHEN ${vendors.status} = 'ACTIVE' THEN '정규업체'
          WHEN ${vendors.status} IN ('INACTIVE', 'BLACKLISTED', 'REJECTED') THEN ''
          ELSE '잠재업체'
        END
      `.as("vendor_category"),

      // 업체대표품목 1, 2, 3 - isConfirmed = true를 우선적으로, 그 다음 false 순으로 정렬
      primaryMaterial1: sql<string>`
        (SELECT CASE 
           WHEN vpm1.item_name IS NOT NULL AND vpm1.item_code IS NOT NULL 
           THEN vpm1.item_name || E'\n(' || vpm1.item_code || ')'
           ELSE NULL
         END
         FROM vendor_possible_materials vpm1
         WHERE vpm1.vendor_id = ${vendors.id}
         ORDER BY vpm1.is_confirmed DESC NULLS LAST, vpm1.created_at DESC
         LIMIT 1 OFFSET 0)
      `.as("primary_material_1"),

      primaryMaterial2: sql<string>`
        (SELECT CASE 
           WHEN vpm2.item_name IS NOT NULL AND vpm2.item_code IS NOT NULL 
           THEN vpm2.item_name || E'\n(' || vpm2.item_code || ')'
           ELSE NULL
         END
         FROM vendor_possible_materials vpm2
         WHERE vpm2.vendor_id = ${vendors.id}
         ORDER BY vpm2.is_confirmed DESC NULLS LAST, vpm2.created_at DESC
         LIMIT 1 OFFSET 1)
      `.as("primary_material_2"),

      primaryMaterial3: sql<string>`
        (SELECT CASE 
           WHEN vpm3.item_name IS NOT NULL AND vpm3.item_code IS NOT NULL 
           THEN vpm3.item_name || E'\n(' || vpm3.item_code || ')'
           ELSE NULL
         END
         FROM vendor_possible_materials vpm3
         WHERE vpm3.vendor_id = ${vendors.id}
         ORDER BY vpm3.is_confirmed DESC NULLS LAST, vpm3.created_at DESC
         LIMIT 1 OFFSET 2)
      `.as("primary_material_3"),

      // 최근 발주 실적 정보 - 가장 최신 계약 정보
      recentPoNumber: sql<string>`
        (SELECT c.contract_no
         FROM contracts c
         WHERE c.vendor_id = ${vendors.id}
         ORDER BY c.start_date DESC NULLS LAST
         LIMIT 1)
      `.as("recent_po_number"),
      
      recentPoOrderBy: sql<string>`
        (SELECT CASE 
           WHEN u.name IS NOT NULL AND u.user_code IS NOT NULL 
           THEN u.name || E'\n(' || u.user_code || ')'
           ELSE c.purchase_group
         END
         FROM contracts c
         LEFT JOIN users u ON c.purchase_group = u.user_code
         WHERE c.vendor_id = ${vendors.id}
         ORDER BY c.start_date DESC NULLS LAST
         LIMIT 1)
      `.as("recent_po_order_by"),
      
      recentPoDate: sql<Date>`
        (SELECT c.start_date
         FROM contracts c
         WHERE c.vendor_id = ${vendors.id}
         ORDER BY c.start_date DESC NULLS LAST
         LIMIT 1)
      `.as("recent_po_date"),

      // 최근 조달 실적 정보 (TODO 구현을 위한 플레이스홀더)
      recentDeliveryNumber: sql<string>`NULL`.as("recent_delivery_number"),
      recentDeliveryBy: sql<string>`NULL`.as("recent_delivery_by"),
      recentDeliveryDate: sql<Date>`NULL`.as("recent_delivery_date"),

      // TODO 컬럼들 (UI만 구현)
      regularEvaluationGrade: sql<string>`NULL`.as("regular_evaluation_grade"),
      faContract: sql<string>`NULL`.as("fa_contract"),
      avlRegistration: sql<string>`NULL`.as("avl_registration"),
      regularVendorRegistration: sql<string>`NULL`.as("regular_vendor_registration")
    })
    .from(vendors)
    .leftJoin(
      vendorTypes,
      sql`${vendors.vendorTypeId} = ${vendorTypes.id}`
    );
});


// You can also create interfaces for the view
export type VendorWithType = typeof vendorsWithTypesView.$inferSelect;
export type VendorWithTypeAndMaterials = typeof vendorsWithTypesAndMaterialsView.$inferSelect;