diff options
Diffstat (limited to 'db/schema/vendors.ts')
| -rw-r--r-- | db/schema/vendors.ts | 118 |
1 files changed, 118 insertions, 0 deletions
diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts new file mode 100644 index 00000000..b2005537 --- /dev/null +++ b/db/schema/vendors.ts @@ -0,0 +1,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
\ No newline at end of file |