diff options
Diffstat (limited to 'db/schema/vendors.ts')
| -rw-r--r-- | db/schema/vendors.ts | 181 |
1 files changed, 174 insertions, 7 deletions
diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts index acdec3d2..f56a1288 100644 --- a/db/schema/vendors.ts +++ b/db/schema/vendors.ts @@ -1,7 +1,20 @@ // db/schema/vendors.ts -import { pgTable, serial, varchar, text, timestamp, boolean, integer ,pgView} from "drizzle-orm/pg-core"; +import { pgTable, serial, varchar, text, timestamp, boolean, integer ,pgView} from "drizzle-orm/pg-core"; import { items } from "./items"; -import { sql, eq} from "drizzle-orm"; +import { sql, eq, relations} from "drizzle-orm"; +import { users } from "./users"; + + +// 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(), @@ -32,7 +45,7 @@ export const vendors = pgTable("vendors", { }) .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 }), @@ -40,6 +53,7 @@ export const vendors = pgTable("vendors", { corporateRegistrationNumber: varchar("corporate_registration_number", { length: 100, }), + items: text("items"), creditAgency: varchar("credit_agency", { length: 50 }), creditRating: varchar("credit_rating", { length: 50 }), @@ -255,8 +269,12 @@ export const vendorInvestigationsView = pgView( export const vendorCandidates = pgTable("vendor_candidates", { id: serial("id").primaryKey(), companyName: varchar("company_name", { length: 255 }).notNull(), - contactEmail: varchar("contact_email", { 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 }), // 수집 출처 @@ -270,7 +288,10 @@ export const vendorCandidates = pgTable("vendor_candidates", { }) .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(), }); @@ -281,7 +302,7 @@ 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, @@ -359,4 +380,150 @@ export const vendorDetailView = pgView("vendor_detail_view").as((qb) => { }); // 타입 정의 -export type VendorDetailView = typeof vendorDetailView.$inferSelect;
\ No newline at end of file +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: vendors.id, + vendorName: vendors.vendorName, + vendorCode: vendors.vendorCode, + taxId: vendors.taxId, + address: vendors.address, + country: vendors.country, + phone: vendors.phone, + email: vendors.email, + website: vendors.website, + status: vendors.status, + vendorTypeId: vendors.vendorTypeId, + representativeName: vendors.representativeName, + representativeBirth: vendors.representativeBirth, + representativeEmail: vendors.representativeEmail, + representativePhone: vendors.representativePhone, + corporateRegistrationNumber: vendors.corporateRegistrationNumber, + items: vendors.items, + creditAgency: vendors.creditAgency, + creditRating: vendors.creditRating, + cashFlowRating: vendors.cashFlowRating, + createdAt: vendors.createdAt, + updatedAt: vendors.updatedAt, + + // Vendor type fields + vendorTypeName: vendorTypes.nameKo, + vendorTypeNameEn: vendorTypes.nameEn, + vendorTypeCode: vendorTypes.code, + + // Computed vendor category field + vendorCategory: sql` + 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}` + ); +}); + +// You can also create interfaces for the view +export type VendorWithType = typeof vendorsWithTypesView.$inferSelect;
\ No newline at end of file |