(고건) 리스크 관리 페이지 추가

1 files changed, 105 insertions, 0 deletions

diff --git a/db/schema/risks/risks.ts b/db/schema/risks/risks.ts
new file mode 100644
index 00000000..7be18776
--- /dev/null
+++ b/db/schema/risks/risks.ts
@@ -0,0 +1,105 @@
+/* IMPORT */
+import {
+  boolean,
+  integer,
+  pgSchema,
+  serial,
+  text,
+  timestamp,
+  varchar,
+} from 'drizzle-orm/pg-core';
+import { eq, relations } from 'drizzle-orm';
+import { users } from '../users';
+import { vendors } from '../vendors';
+
+// ----------------------------------------------------------------------------------------------------
+
+/* TABLE SCHEMATA */
+const risksSchema = pgSchema('risks');
+const riskEvents = risksSchema.table('risk_events', {
+  id: serial('id').primaryKey(),
+  vendorId: integer('vendor_id')
+    .notNull()
+    .references(() => vendors.id, { onDelete: 'cascade' }),
+  provider: varchar('provider', { length: 128 }).notNull(),
+  eventType: varchar('event_type', { length: 50 }).notNull(),
+  content: text('content'),
+  eventStatus: boolean('event_status').default(true).notNull(),
+  managerId: integer('manager_id')
+    .references(() => users.id, { onDelete: 'set null' }),
+  adminComment: text('admin_comment'),
+  occuredAt: timestamp('occurred_at').defaultNow().notNull(),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+  createdBy: integer('created_by')
+    .references(() => users.id, { onDelete: 'set null' }),
+  updatedBy: integer('updated_by')
+    .references(() => users.id, { onDelete: 'set null' }),
+});
+
+// ----------------------------------------------------------------------------------------------------
+
+/* VIEWS */
+const risksView = risksSchema.view('risks_view').as((qb) =>
+  qb
+    .select({
+      id: riskEvents.id,
+      eventType: riskEvents.eventType,
+      vendorId: riskEvents.vendorId,
+      vendorCode: vendors.vendorCode,
+      vendorName: vendors.vendorName,
+      businessNumber: vendors.taxId,
+      provider: riskEvents.provider,
+      content: riskEvents.content,
+      eventStatus: riskEvents.eventStatus,
+      managerId: riskEvents.managerId,
+      managerName: users.name,
+      adminComment: riskEvents.adminComment,
+      occuredAt: riskEvents.occuredAt,
+    })
+    .from(riskEvents)
+    .leftJoin(vendors, eq(vendors.id, riskEvents.vendorId))
+    .leftJoin(users, eq(users.id, riskEvents.managerId))
+);
+
+// ----------------------------------------------------------------------------------------------------
+
+/* RELATIONS */
+const riskEventsRelations = relations(riskEvents, ({ one }) => ({
+  vendor: one(vendors, {
+    fields: [riskEvents.vendorId],
+    references: [vendors.id],
+  }),
+  manager: one(users, {
+    fields: [riskEvents.managerId],
+    references: [users.id],
+  }),
+  createdByUser: one(users, {
+    fields: [riskEvents.createdBy],
+    references: [users.id],
+  }),
+  updatedByUser: one(users, {
+    fields: [riskEvents.updatedBy],
+    references: [users.id],
+  }),
+}));
+
+// ----------------------------------------------------------------------------------------------------
+
+/* TYPES */
+type RiskEvents = typeof riskEvents.$inferSelect;
+type NewRiskEvents = typeof riskEvents.$inferInsert;
+type RisksView = typeof risksView.$inferSelect;
+
+// ----------------------------------------------------------------------------------------------------
+
+/* Export */
+export {
+  risksSchema,
+  risksView,
+  riskEvents,
+  riskEventsRelations,
+  type NewRiskEvents,
+  type RiskEvents,
+  type RisksView,
+};
\ No newline at end of file