From 0547ab2fe1701d84753d0e078bba718a79b07a0c Mon Sep 17 00:00:00 2001 From: dujinkim Date: Fri, 23 May 2025 05:26:26 +0000 Subject: (최겸)기술영업 벤더 개발 초안(index 스키마 미포함 상태) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- db/schema/techVendors.ts | 271 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 271 insertions(+) create mode 100644 db/schema/techVendors.ts (limited to 'db/schema/techVendors.ts') diff --git a/db/schema/techVendors.ts b/db/schema/techVendors.ts new file mode 100644 index 00000000..55060adf --- /dev/null +++ b/db/schema/techVendors.ts @@ -0,0 +1,271 @@ +import { pgTable, serial, varchar, text, timestamp, integer, boolean, pgView } from "drizzle-orm/pg-core"; +import { sql, eq } from "drizzle-orm"; +import { items } from "./items"; + +// 벤더 타입 enum 정의 +export const VENDOR_TYPES = ["조선", "해양TOP", "해양HULL"] as const; +export type TechVendorType = typeof VENDOR_TYPES[number]; + +// 기술영업 벤더 테이블 +export const techVendors = pgTable("tech_vendors", { + id: serial("id").primaryKey(), + vendorName: varchar("vendor_name", { length: 255 }).notNull(), + vendorCode: varchar("vendor_code", { length: 100 }), + taxId: varchar("tax_id", { length: 100 }).notNull(), + address: text("address"), + country: varchar("country", { length: 100 }), + phone: varchar("phone", { length: 50 }), + email: varchar("email", { length: 255 }), + website: varchar("website", { length: 255 }), + // 벤더 타입 추가 + techVendorType: varchar("tech_vendor_type", { + length: 20, + enum: VENDOR_TYPES + }).notNull(), + // 상태 필드 추가 + status: varchar("status", { + length: 30, + enum: [ + "PENDING_REVIEW", + "IN_REVIEW", + "REJECTED", + "ACTIVE", + "INACTIVE", + "BLACKLISTED" + ] + }).default("PENDING_REVIEW").notNull(), + // 대표자 정보 + representativeName: varchar("representative_name", { length: 255 }), + representativeEmail: varchar("representative_email", { length: 255 }), + representativePhone: varchar("representative_phone", { length: 50 }), + representativeBirth: varchar("representative_birth", { length: 20 }), + + // 사업자등록번호 + corporateRegistrationNumber: varchar("corporate_registration_number", { + length: 100, + }), + items: text("items"), + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}); +export const techVendorContacts = pgTable("tech_vendor_contacts", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").notNull().references(() => techVendors.id), + contactName: varchar("contact_name", { length: 255 }).notNull(), + contactPosition: varchar("contact_position", { 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 techVendorPossibleItems = pgTable("tech_vendor_possible_items", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").notNull().references(() => techVendors.id), + // itemId: integer("item_id"), // 별도 item 테이블 연동시 + itemCode: varchar("item_code", { length: 100 }) + .notNull() + .references(() => items.itemCode, { onDelete: "cascade" }), + itemName: varchar("item_name", { length: 255 }).notNull(), + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}); +export const techVendorAttachments = pgTable("tech_vendor_attachments", { + id: serial("id").primaryKey(), + vendorId: integer("vendor_id").references(() => techVendors.id), + 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 const techVendorItemsView = pgView("tech_vendor_items_view").as((qb) => { + return qb + .select({ + vendorItemId: techVendorPossibleItems.id, + vendorId: techVendorPossibleItems.vendorId, + itemCode: items.itemCode, + itemName: items.itemName, + createdAt: techVendorPossibleItems.createdAt, + updatedAt: techVendorPossibleItems.updatedAt, + }) + .from(techVendorPossibleItems) + .leftJoin(items, eq(techVendorPossibleItems.itemCode, items.itemCode)); +}); + +// 벤더 상세 정보 뷰 (연락처 정보 포함) +export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) => { + return qb + .select({ + // 협력업체 기본 정보 + id: techVendors.id, + vendorName: techVendors.vendorName, + vendorCode: techVendors.vendorCode, + taxId: techVendors.taxId, + address: techVendors.address, + country: techVendors.country, + phone: techVendors.phone, + email: techVendors.email, + website: techVendors.website, + status: techVendors.status, + techVendorType: techVendors.techVendorType, + representativeName: techVendors.representativeName, + representativeEmail: techVendors.representativeEmail, + representativePhone: techVendors.representativePhone, + representativeBirth: techVendors.representativeBirth, + corporateRegistrationNumber: techVendors.corporateRegistrationNumber, + createdAt: techVendors.createdAt, + updatedAt: techVendors.updatedAt, + + // 연락처 정보 JSON + contacts: sql` + (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 = tech_vendors.id) + `.as("contacts"), + // 첨부파일 정보 (수정된 버전) + attachments: sql` + (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 tech_vendor_attachments a + WHERE a.vendor_id = tech_vendors.id) + `.as("attachments"), + // 첨부파일 수 (수정된 버전) + attachmentCount: sql` + (SELECT COUNT(*) + FROM tech_vendor_attachments a + WHERE a.vendor_id = tech_vendors.id) + `.as("attachment_count"), + // 연락처 수 + contactCount: sql` + (SELECT COUNT(*) + FROM vendor_contacts c + WHERE c.vendor_id = tech_vendors.id) + `.as("contact_count"), + + // 가능 아이템 목록 JSON + possibleItems: sql` + (SELECT COALESCE( + json_agg( + json_build_object( + 'itemCode', i.item_code, + 'itemName', it.item_name + ) + ), + '[]'::json + ) + FROM tech_vendor_possible_items i + LEFT JOIN items it ON i.item_code = it.item_code + WHERE i.vendor_id = tech_vendors.id) + `.as("possible_items"), + + // 아이템 수 + itemCount: sql` + (SELECT COUNT(*) + FROM tech_vendor_possible_items i + WHERE i.vendor_id = tech_vendors.id) + `.as("item_count") + }) + .from(techVendors); +}); + + +export const techVendorCandidates = pgTable("tech_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 }), + + taxId: varchar("tax_id", { length: 100 }).notNull(), + 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(() => techVendors.id, { onDelete: "cascade" }), + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), +}); +export const techVendorCandidatesWithVendorInfo = pgView("tech_vendor_candidates_with_vendor_info").as((qb) => { + return qb + .select({ + // ---------------------------------------- + // 1) techVendorCandidates 기본 필드 + id: techVendorCandidates.id, + companyName: techVendorCandidates.companyName, + contactEmail: techVendorCandidates.contactEmail, + contactPhone: techVendorCandidates.contactPhone, + taxId: techVendorCandidates.taxId, + address: techVendorCandidates.address, + country: techVendorCandidates.country, + source: techVendorCandidates.source, + status: techVendorCandidates.status, + items: techVendorCandidates.items, + remark: techVendorCandidates.remark, + createdAt: techVendorCandidates.createdAt, + updatedAt: techVendorCandidates.updatedAt, + + // ---------------------------------------- + // 2) techVendors 조인해서 가져올 필드 + vendorName: techVendors.vendorName, + vendorCode: techVendors.vendorCode, + vendorCreatedAt: sql`${techVendors.createdAt}`.as("vendor_created_at"), + + }) + .from(techVendorCandidates) + .leftJoin(techVendors, eq(techVendorCandidates.vendorId, techVendors.id)); +}); + +export type TechVendorCandidatesWithVendorInfo = typeof techVendorCandidatesWithVendorInfo.$inferSelect; + +export type TechVendor = typeof techVendors.$inferSelect +export type TechVendorContact = typeof techVendorContacts.$inferSelect +export type TechVendorItem = typeof techVendorPossibleItems.$inferSelect +export type TechVendorAttach = typeof techVendorAttachments.$inferSelect +export type TechVendorItemsView = typeof techVendorItemsView.$inferSelect +export type TechVendorDetailView = typeof techVendorDetailView.$inferSelect +export type TechVendorCandidate = typeof techVendorCandidates.$inferSelect +export type TechVendorWithAttachments = TechVendor & { + hasAttachments?: boolean; + attachmentsList?: TechVendorAttach[]; +} \ No newline at end of file -- cgit v1.2.3