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
|
/* 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,
};
|
