1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
// db/schema/vendors.ts
import { pgTable, serial, varchar, text, timestamp, boolean, integer ,pgView} from "drizzle-orm/pg-core";
import { items } from "./items";
import { eq} from "drizzle-orm";
export const vendors = pgTable("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 }),
status: varchar("status", {
length: 30,
enum: [
"PENDING_REVIEW", // 가입 신청 중 (초기 신청)
"IN_REVIEW", // 심사 중
"REJECTED", // 심사 거부됨
"IN_PQ", // PQ 진행 중
"PQ_SUBMITTED", // PQ 제출
"PQ_FAILED", // PQ 실패
"APPROVED", // PQ 통과, 승인됨
"ACTIVE", // 활성 상태 (실제 거래 중)
"INACTIVE", // 비활성 상태 (일시적)
"BLACKLISTED", // 거래 금지 상태
]
})
.notNull()
.default("PENDING_REVIEW"),
representativeName: varchar("representative_name", { length: 255 }),
representativeBirth: varchar("representative_birth", { length: 20 }),
representativeEmail: varchar("representative_email", { length: 255 }),
representativePhone: varchar("representative_phone", { length: 50 }),
corporateRegistrationNumber: varchar("corporate_registration_number", {
length: 100,
}),
creditAgency: varchar("credit_agency", { length: 50 }),
creditRating: varchar("credit_rating", { length: 50 }),
cashFlowRating: varchar("cash_flow_rating", { length: 50 }),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
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 }),
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(),
});
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 vendorAttachments = pgTable("vendor_attachments", {
id: serial("id").primaryKey(),
vendorId: integer("vendor_id").references(() => vendors.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 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
|
