-
Notifications
You must be signed in to change notification settings - Fork 22
/
Copy pathScript.sql
300 lines (264 loc) · 13 KB
/
Script.sql
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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
-- 系统表结构文件
-- 1、客服管理系统
-- Drop table
-- DROP TABLE staffadmin.shunt;
CREATE TABLE staffadmin.shunt
(
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
created_by varchar(500) NULL,
created_date timestamp NOT NULL,
last_modified_by varchar(500) NULL,
last_modified_date timestamp NULL,
organization_id int4 NOT NULL,
"name" varchar(250) NULL,
shunt_class_id int8 NULL,
code varchar(500) NULL,
CONSTRAINT shunt_pkey PRIMARY KEY (id)
);
CREATE
INDEX shunt_name_idx ON staffadmin.shunt USING btree (name);
CREATE
INDEX shunt_organization_id_idx ON staffadmin.shunt USING btree (organization_id);
create
index shunt_code_index on staffadmin.shunt using btree (code);
-- Drop table
-- DROP TABLE staffadmin.shunt_class;
CREATE TABLE staffadmin.shunt_class
(
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
created_by varchar(500) NULL,
created_date timestamp NOT NULL,
last_modified_by varchar(500) NULL,
last_modified_date timestamp NULL,
organization_id int4 NOT NULL,
class_name varchar(250) NULL,
catalogue int8 NULL,
CONSTRAINT shunt_class_pkey PRIMARY KEY (id)
);
CREATE
INDEX shunt_class_class_name_idx ON staffadmin.shunt_class USING btree (class_name);
CREATE
INDEX shunt_class_organization_id_idx ON staffadmin.shunt_class USING btree (organization_id);
-- Drop table
-- DROP TABLE staffadmin.staff;
CREATE TABLE staffadmin.staff
(
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
created_by varchar(500) NULL,
created_date timestamp NOT NULL,
last_modified_by varchar(500) NULL,
last_modified_date timestamp NULL,
organization_id int4 NULL,
username varchar(250) NULL,
"password" varchar(250) NULL,
"role" varchar(50) NULL,
staff_group_id int8 NULL,
real_name varchar(250) NULL,
nick_name varchar(250) NULL,
avatar varchar(250) NULL,
simultaneous_service int2 NULL,
max_ticket_per_day int2 NULL,
max_ticket_all_time int2 NULL,
staff_type int2 NULL,
gender int2 NULL,
mobile_phone varchar(50) NULL,
personalized_signature varchar(500) NULL,
enabled boolean default true,
CONSTRAINT staff_group_pkey PRIMARY KEY (id)
);
CREATE
INDEX staff_organization_id_idx ON staffadmin.staff USING btree (organization_id);
CREATE
INDEX staff_organization_id_name_idx ON staffadmin.staff USING btree (organization_id, username);
CREATE
INDEX staff_staff_group_id_idx ON staffadmin.staff USING btree (staff_group_id);
create
index staff_enabled on staffadmin.staff USING btree (enabled);
create
index staff_staff_type on staffadmin.staff using btree (staff_type);
-- Drop table
-- DROP TABLE staffadmin.staff_config;
CREATE TABLE staffadmin.staff_config
(
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
created_by varchar(500) NULL,
created_date timestamp NOT NULL,
last_modified_by varchar(500) NULL,
last_modified_date timestamp NULL,
organization_id int4 NULL,
staff_id int4 NULL,
shunt_id int4 NULL,
priority int2 NULL,
CONSTRAINT staff_config_group_pkey PRIMARY KEY (id)
);
CREATE
INDEX staff_config_shunt_id_name_idx ON staffadmin.staff_config USING btree (shunt_id);
CREATE
INDEX staff_config_staff_id_idx ON staffadmin.staff_config USING btree (staff_id);
-- Drop table
-- DROP TABLE staffadmin.staff_group;
CREATE TABLE staffadmin.staff_group
(
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
created_by varchar(500) NULL,
created_date timestamp NOT NULL,
last_modified_by varchar(500) NULL,
last_modified_date timestamp NULL,
organization_id int4 NULL,
group_name varchar(250) NULL,
CONSTRAINT staff_group_pkey PRIMARY KEY (id)
);
CREATE
INDEX staff_group_group_name_idx ON staffadmin.staff_group USING btree (group_name);
CREATE
INDEX staff_group_organization_id_idx ON staffadmin.staff_group USING btree (organization_id);
-- Drop table
-- DROP TABLE staffadmin.shunt_ui_config;
CREATE TABLE staffadmin.shunt_ui_config
(
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
organization_id int4 NULL,
created_by varchar(500) NULL,
created_date timestamp NOT NULL,
last_modified_by varchar(500) NULL,
last_modified_date timestamp NULL,
shunt_id int8 NULL,
config varchar(2000) NULL,
hot_question varchar(2000) NULL,
CONSTRAINT shunt_ui_config_pkey PRIMARY KEY (id)
);
CREATE
INDEX shunt_ui_config_shunt_id ON staffadmin.shunt_ui_config USING btree (shunt_id);
CREATE
INDEX shunt_ui_config_organization_id_idx ON staffadmin.shunt_ui_config USING btree (organization_id);
-- 快捷回复
CREATE TABLE staffadmin.quick_reply
(
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
organization_id int4 NULL,
created_by varchar(500) NULL,
created_date timestamp NOT NULL,
last_modified_by varchar(500) NULL,
last_modified_date timestamp NULL,
staff_id int8 NULL,
group_id int8 NULL,
title varchar(250) NULL,
content varchar(500) NULL,
personal boolean default false,
CONSTRAINT quick_reply_pkey PRIMARY KEY (id)
);
CREATE
INDEX quick_reply_staff_id ON staffadmin.quick_reply USING btree (staff_id);
CREATE
INDEX quick_reply_group_id ON staffadmin.quick_reply USING btree (group_id);
CREATE
INDEX quick_reply_organization_id_idx ON staffadmin.quick_reply USING btree (organization_id);
-- 快捷回复 分组
CREATE TABLE staffadmin.quick_reply_group
(
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
organization_id int4 NULL,
created_by varchar(500) NULL,
created_date timestamp NOT NULL,
last_modified_by varchar(500) NULL,
last_modified_date timestamp NULL,
staff_id int8 NULL,
group_name varchar(250) NULL,
personal boolean default false,
CONSTRAINT quick_reply_group_pkey PRIMARY KEY (id)
);
CREATE
INDEX quick_reply_group_staff_id ON staffadmin.quick_reply_group USING btree (staff_id);
CREATE
INDEX quick_reply_group_organization_id_idx ON staffadmin.quick_reply_group USING btree (organization_id);
-- 系统配置
CREATE TABLE staffadmin.properties
(
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
organization_id int4 NULL,
created_by varchar(500) NULL,
created_date timestamp NOT NULL,
last_modified_by varchar(500) NULL,
last_modified_date timestamp NULL,
key varchar(250) NOT NULL,
value varchar(250) NULL,
label varchar(250) NOT NULL,
available boolean default true,
personal boolean default false,
staff_id int8 NULL,
CONSTRAINT properties_pkey PRIMARY KEY (id)
);
CREATE
INDEX properties_key ON staffadmin.properties USING btree (key);
CREATE
INDEX properties_personal ON staffadmin.properties USING btree (personal);
CREATE
INDEX properties_organization_id_idx ON staffadmin.properties USING btree (organization_id);
-- Drop table
-- DROP TABLE bot.shunt_ui_config;
CREATE TABLE bot.bot_config
(
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
organization_id int4 NULL,
created_by varchar(500) NULL,
created_date timestamp NOT NULL,
last_modified_by varchar(500) NULL,
last_modified_date timestamp NULL,
bot_id int8 NOT NULL,
knowledge_base_id int8 NOT NULL,
no_answer_reply varchar(500) NOT NULL,
CONSTRAINT bot_config_pkey PRIMARY KEY (id)
);
CREATE
UNIQUE INDEX bot_config_bot_id_idx ON bot.bot_config USING btree (bot_id);
CREATE
INDEX bot_config_knowledge_base_id_idx ON bot.bot_config USING btree (knowledge_base_id);
CREATE
INDEX bot_config_organization_id_idx ON bot.bot_config USING btree (organization_id);
CREATE TABLE bot.knowledge_base
(
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
organization_id int4 NULL,
created_by varchar(500) NULL,
created_date timestamp NOT NULL,
last_modified_by varchar(500) NULL,
last_modified_date timestamp NULL,
name varchar(250) NOT NULL,
description varchar(250) NULL,
CONSTRAINT knowledge_base_pkey PRIMARY KEY (id)
);
CREATE
INDEX knowledge_base_organization_id_idx ON bot.knowledge_base USING btree (organization_id);
CREATE TABLE bot.topic_category
(
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
organization_id int4 NULL,
created_by varchar(500) NULL,
created_date timestamp NOT NULL,
last_modified_by varchar(500) NULL,
last_modified_date timestamp NULL,
name varchar(250) NOT NULL,
knowledge_base_id int8 NOT NULL,
pid int8 NULL,
CONSTRAINT topic_category_pkey PRIMARY KEY (id)
);
CREATE
INDEX topic_category_organization_id_idx ON bot.topic_category USING btree (organization_id);
CREATE
INDEX topic_category_pid_idx ON bot.topic_category USING btree (pid);
-- init data
INSERT INTO staffadmin.staff (created_by, created_date, last_modified_by, last_modified_date, organization_id, username,
password, role, staff_group_id, real_name, nick_name, simultaneous_service,
max_ticket_per_day, max_ticket_all_time, staff_type, gender, mobile_phone,
personalized_signature, avatar, enabled)
VALUES (null, '2021-08-21 14:49:48.651012', 'admin', '2021-08-21 14:49:48.655014', 9491, 'admin',
'$2a$10$VexRl.NdcNFQWpKED9wnyegU8OWqqrA/7UKJEMpF/E2yQDcpGJBiW', 'ROLE_ADMIN', 1, '野原新之助', '蜡笔小新', 8, 999, 999,
1, 0, '', '', '172f9c603cf2493389913f8be3e32f71.jpg', true);
INSERT INTO staffadmin.staff_group (created_by, created_date, last_modified_by, last_modified_date, organization_id, group_name) VALUES ('sys', '2021-04-19 15:44:35.608115', 'sys', '2021-04-19 15:44:35.608115', 9491, '客服组');
INSERT INTO staffadmin.staff_group (created_by, created_date, last_modified_by, last_modified_date, organization_id, group_name) VALUES ('sys', '2021-08-19 13:41:18.863132', 'sys', '2021-08-19 13:41:18.863132', 9491, '机器人组');
INSERT INTO staffadmin.properties (organization_id, created_by, created_date, last_modified_by, last_modified_date, key, value, label, available, personal, staff_id) VALUES (9491, 'sys', '2021-07-22 19:15:18.000000', 'sys', '2021-07-22 19:15:26.000000', 'sys', null, '系统配置', true, false, null);
INSERT INTO staffadmin.properties (organization_id, created_by, created_date, last_modified_by, last_modified_date, key, value, label, available, personal, staff_id) VALUES (9491, 'sys', '2021-07-22 19:16:47.000000', 'sys', '2021-07-22 19:16:50.000000', 'sys.autoReply', null, '自动回复', true, false, null);
INSERT INTO staffadmin.properties (organization_id, created_by, created_date, last_modified_by, last_modified_date, key, value, label, available, personal, staff_id) VALUES (9491, 'sys', '2021-07-24 14:37:05.000000', 'sys', '2021-07-24 14:37:10.000000', 'sys.autoReply.content', '温馨提醒：目前咨询较多，客服正在努力解答中，请按捺您激动的小心灵~', '回复内容', true, false, null);
INSERT INTO staffadmin.properties (organization_id, created_by, created_date, last_modified_by, last_modified_date, key, value, label, available, personal, staff_id) VALUES (9491, 'sys', '2021-07-24 14:34:55.000000', 'sys', '2021-07-24 14:34:50.000000', 'sys.autoReply.timeout', '3', '超时(分钟)', true, false, null);
INSERT INTO staffadmin.properties (organization_id, created_by, created_date, last_modified_by, last_modified_date, key, value, label, available, personal, staff_id) VALUES (9491, 'admin', '2021-08-31 19:13:38.000000', 'admin', '2021-08-31 19:13:36.000000', 'sys.clientTimeout', null, '客户超时关闭', true, false, null);
INSERT INTO staffadmin.properties (organization_id, created_by, created_date, last_modified_by, last_modified_date, key, value, label, available, personal, staff_id) VALUES (9491, 'admin', '2021-08-31 19:14:51.000000', 'admin', '2021-08-31 19:15:04.000000', 'sys.clientTimeout.timeout', '15', '超时(分钟)', true, false, null);