1 files changed, 271 insertions, 0 deletions
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<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 = tech_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 tech_vendor_attachments a
+      WHERE a.vendor_id = tech_vendors.id)
+    `.as("attachments"),
+      // 첨부파일 수 (수정된 버전)
+      attachmentCount: sql<number>`
+      (SELECT COUNT(*)
+      FROM tech_vendor_attachments a
+      WHERE a.vendor_id = tech_vendors.id)
+    `.as("attachment_count"),
+      // 연락처 수
+      contactCount: sql<number>`
+        (SELECT COUNT(*) 
+        FROM vendor_contacts c
+        WHERE c.vendor_id = tech_vendors.id)
+      `.as("contact_count"),
+      
+      // 가능 아이템 목록 JSON
+      possibleItems: sql<string>`
+        (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<number>`
+        (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<Date>`${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