schema.sql
27.1 KB
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
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
-- 外卖骑手配送模块 数据库建表脚本
-- 数据库：dili_rider
CREATE DATABASE IF NOT EXISTS dili_rider DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE dili_rider;
-- 骑手信息表
CREATE TABLE `rider` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '骑手ID',
`mobile` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
`user_login` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '登录名',
`user_nickname` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '昵称',
`user_pass` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '密码（MD5）',
`avatar` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '头像',
`avatar_thumb` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '头像缩略图',
`city_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '城市ID',
`level_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '等级ID',
`type` TINYINT NOT NULL DEFAULT 1 COMMENT '类型：1=兼职 2=全职',
`user_status` TINYINT NOT NULL DEFAULT 2 COMMENT '审核状态：0=拒绝 1=通过 2=待审核',
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '账号状态：0=禁用 1=正常',
`balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '余额（兼职用）',
`is_rest` TINYINT NOT NULL DEFAULT 0 COMMENT '是否休息：0=否 1=是',
`id_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '身份证号',
`thumb` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '手持身份证照片',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '注册时间',
`is_del` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除：0=正常 1=已删除',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_mobile` (`mobile`),
KEY `idx_city_id` (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='骑手信息表';
-- 骑手等级配置表
CREATE TABLE `rider_level` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`city_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '城市ID',
`level_id` INT NOT NULL DEFAULT 0 COMMENT '等级编号',
`name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '等级名称',
`is_default` TINYINT NOT NULL DEFAULT 0 COMMENT '是否默认',
`trans_nums` INT NOT NULL DEFAULT 0 COMMENT '每日转单次数上限',
`run_fee_mode` TINYINT NOT NULL DEFAULT 1 COMMENT '跑腿收入模式：1=固定 2=比例 3=距离',
`run_fix_money` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '跑腿固定金额',
`run_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT '跑腿比例（%）',
`distance_basic` INT NOT NULL DEFAULT 0 COMMENT '起始距离（米）',
`distance_basic_money` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '基础配送费',
`distance_more_money` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '超出每公里费',
`distance_max_money` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '最高配送费上限',
`work_fee_mode` TINYINT NOT NULL DEFAULT 1 COMMENT '办事收入模式：1=固定 2=比例',
`work_fix_money` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '办事固定金额',
`work_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT '办事比例（%）',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_city_level` (`city_id`, `level_id`),
KEY `idx_city_default` (`city_id`, `is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='骑手等级配置表';
-- 骑手实时位置表
CREATE TABLE `rider_location` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`uid` BIGINT UNSIGNED NOT NULL COMMENT '骑手ID',
`lng` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '经度',
`lat` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '纬度',
`update_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='骑手实时位置表';
-- 骑手余额流水表
CREATE TABLE `rider_balance` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`uid` BIGINT UNSIGNED NOT NULL COMMENT '骑手ID',
`type` TINYINT NOT NULL DEFAULT 1 COMMENT '类型：1=收入 2=提现',
`action` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '动作标识',
`action_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '关联ID',
`order_no` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '订单号',
`nums` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '变动金额',
`total` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '变动后余额',
`add_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '记录时间',
PRIMARY KEY (`id`),
KEY `idx_uid` (`uid`),
KEY `idx_action_id` (`action_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='骑手余额流水表';
-- 骑手订单统计表
CREATE TABLE `rider_order_count` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`uid` BIGINT UNSIGNED NOT NULL COMMENT '骑手ID',
`count_date` INT NOT NULL COMMENT '统计日期yyyyMMdd',
`orders` INT NOT NULL DEFAULT 0 COMMENT '完成订单数',
`transfers` INT NOT NULL DEFAULT 0 COMMENT '转单数',
`distance` BIGINT NOT NULL DEFAULT 0 COMMENT '配送距离（米）',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uid_date` (`uid`, `count_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='骑手订单统计表';
-- 骑手拒单记录表
CREATE TABLE `rider_orders_refuse` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`rider_id` BIGINT UNSIGNED NOT NULL COMMENT '骑手ID',
`oid` BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
`add_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '拒单时间',
PRIMARY KEY (`id`),
KEY `idx_rider_id` (`rider_id`),
KEY `idx_oid` (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='骑手拒单记录表';
-- 订单主表
CREATE TABLE `orders` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
`order_no` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '订单号',
`uid` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户ID',
`rider_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '骑手ID',
`old_rider_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '原始骑手ID',
`city_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '城市ID',
`type` TINYINT NOT NULL DEFAULT 6 COMMENT '订单类型：6=外卖配送',
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1待支付 2已支付 3已接单 4服务中 6已完成 7退款申请 8退款成功 9退款拒绝 10已取消',
`pay_type` TINYINT NOT NULL DEFAULT 0 COMMENT '支付类型：1=支付宝 2=微信',
`money` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '订单金额',
`money_delivery` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '配送费',
`money_total` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '实付总金额',
`rider_income` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '骑手收入',
`substation_income` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '站点收入',
`is_income` TINYINT NOT NULL DEFAULT 0 COMMENT '结算状态：0=未结算 1=待结算 2=已结算',
`is_trans` TINYINT NOT NULL DEFAULT 0 COMMENT '转单状态：0=未转 1=通过 2=申请中 3=拒绝',
`code` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '完成码',
`f_name` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '起点名称',
`f_addr` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '起点地址',
`f_lng` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '起点经度',
`f_lat` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '起点纬度',
`t_name` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '终点名称',
`t_addr` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '终点地址',
`t_lng` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '终点经度',
`t_lat` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '终点纬度',
`recip_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '收件人姓名',
`recip_phone` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '收件人电话',
`extra` TEXT COMMENT '附加信息JSON（距离、重量等）',
`thumbs` TEXT COMMENT '取件照片JSON数组',
`store_oid` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '关联店铺订单ID',
`is_del` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除',
`add_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '下单时间',
`pay_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '支付时间',
`grap_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '接单时间',
`pick_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '取件时间',
`complete_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '完成时间',
`trans_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '转单时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no` (`order_no`),
KEY `idx_rider_id` (`rider_id`),
KEY `idx_uid` (`uid`),
KEY `idx_city_status` (`city_id`, `status`),
KEY `idx_old_rider_trans` (`old_rider_id`, `is_trans`, `trans_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';
-- 城市表（配送中台核心配置）
CREATE TABLE `city` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '城市ID',
`pid` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父级ID，0=省级',
`name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '城市名称',
`area_code` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '行政区划码',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0=未开通 1=已开通',
`rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT '平台抽成比例（%）',
`list_order` INT NOT NULL DEFAULT 0 COMMENT '排序',
PRIMARY KEY (`id`),
KEY `idx_pid_order` (`pid`, `list_order`),
KEY `idx_area_code` (`area_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='城市表';
-- 配送计价方案主表
CREATE TABLE `delivery_fee_plan` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`city_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '租户ID',
`name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '方案名称',
`is_default` TINYINT NOT NULL DEFAULT 0 COMMENT '是否默认方案',
`min_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '保底费用',
`distance_basic` DECIMAL(10,2) NOT NULL DEFAULT 3.00 COMMENT '预计送达基础距离(km)',
`distance_basic_time` INT NOT NULL DEFAULT 30 COMMENT '预计送达基础时间(分钟)',
`distance_more_time` INT NOT NULL DEFAULT 10 COMMENT '预计送达超出每km增加时间(分钟)',
`rider_distance` DECIMAL(10,2) NOT NULL DEFAULT 3.00 COMMENT '附近骑手展示范围(km)',
`rider_time` INT NOT NULL DEFAULT 0 COMMENT '预计接单时间(分钟)',
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0=禁用 1=启用',
`list_order` INT NOT NULL DEFAULT 0 COMMENT '排序',
`remark` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`update_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_city_default` (`city_id`, `is_default`),
KEY `idx_city_status` (`city_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配送计价方案主表';
-- 配送计价维度主配置表
CREATE TABLE `delivery_fee_plan_dimension` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`plan_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '方案ID',
`dimension_type` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '维度类型：base/distance/weight/piece/time',
`enabled` TINYINT NOT NULL DEFAULT 0 COMMENT '是否启用',
`base_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '基础费',
`start_distance` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '起步里程(km)',
`start_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '起步费用',
`first_weight` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '首重(kg)',
`first_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '首重费用',
`unit_weight_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '续重单价',
`cap_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '封顶费用',
`extra_json` TEXT COMMENT '扩展配置',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`update_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_plan_dimension` (`plan_id`, `dimension_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配送计价维度主配置表';
-- 里程阶梯表
CREATE TABLE `delivery_fee_plan_distance_step` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`plan_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '方案ID',
`end_distance` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '结束里程(km)',
`unit_distance` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '每档里程(km)',
`unit_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '每档加价',
`list_order` INT NOT NULL DEFAULT 0 COMMENT '排序',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`update_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_plan_order` (`plan_id`, `list_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配送计价里程阶梯表';
-- 件数区间表
CREATE TABLE `delivery_fee_plan_piece_rule` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`plan_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '方案ID',
`start_piece` INT NOT NULL DEFAULT 0 COMMENT '起始件数',
`end_piece` INT NOT NULL DEFAULT 0 COMMENT '结束件数',
`fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '费用',
`list_order` INT NOT NULL DEFAULT 0 COMMENT '排序',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`update_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_plan_order` (`plan_id`, `list_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配送计价件数区间表';
-- 时段附加费表
CREATE TABLE `delivery_fee_plan_time_rule` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`plan_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '方案ID',
`start_minute` INT NOT NULL DEFAULT 0 COMMENT '开始分钟',
`end_minute` INT NOT NULL DEFAULT 0 COMMENT '结束分钟',
`fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '附加费',
`enabled` TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用',
`list_order` INT NOT NULL DEFAULT 0 COMMENT '排序',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`update_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_plan_order` (`plan_id`, `list_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配送计价时段附加费表';
-- 分站管理员表（每城市一个，管理本城市骑手和订单）
CREATE TABLE `substation` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分站ID',
`city_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '管理城市ID',
`user_login` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '登录账号',
`user_nickname` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '昵称',
`user_pass` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '密码（MD5）',
`mobile` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
`avatar` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '头像',
`user_status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0=禁用 1=正常',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_login` (`user_login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分站管理员表（一个城市/租户下可有多个管理员）';
-- 商家入驻申请表
CREATE TABLE `merchant_enter` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '联系人姓名',
`mobile` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
`store_name` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '店铺名称',
`type` TINYINT NOT NULL DEFAULT 1 COMMENT '类型：1=商家入驻 2=骑手入驻 3=商务合作',
`city_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '城市ID',
`remark` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0=未处理 1=已通过 -1=已拒绝',
`add_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '申请时间',
PRIMARY KEY (`id`),
KEY `idx_status_type` (`status`, `type`),
KEY `idx_city_id` (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商家入驻申请表';
-- 商家账号表
CREATE TABLE `merchant_users` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`store_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '关联店铺ID',
`mobile` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号（登录账号）',
`user_nickname` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '昵称',
`user_status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0=禁用 1=正常',
`type` TINYINT NOT NULL DEFAULT 1 COMMENT '类型：1=商家',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_mobile` (`mobile`),
KEY `idx_store_id` (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商家账号表';
-- 商家店铺表
CREATE TABLE `merchant_store` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '店铺ID',
`name` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '店铺名称',
`thumb` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '封面图',
`city_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '所属城市ID',
`address` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '店铺地址',
`lng` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '经度',
`lat` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '纬度',
`operating_state` TINYINT NOT NULL DEFAULT 1 COMMENT '营业状态：0=打烊 1=营业',
`automatic_order` TINYINT NOT NULL DEFAULT 0 COMMENT '自动接单：0=否 1=是',
`shipping_type` TINYINT NOT NULL DEFAULT 1 COMMENT '配送类型：1=外卖配送 2=到店自提',
`free_shipping` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '免运费门槛，0=不免',
`up_to_send` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '起送金额，0=不限',
`open_date` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '营业日期JSON，如[1,2,3,4,5]',
`open_time` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '营业时间JSON，如["09:00","22:00"]',
`about` TEXT COMMENT '店铺简介',
`account_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '关联账号ID',
`app_key` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '接入方AppKey，为空=平台自建',
`out_store_id` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '接入方门店编号，用于推单时自动匹配',
`list_order` INT NOT NULL DEFAULT 0 COMMENT '排序',
`is_del` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除',
`add_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_city_id` (`city_id`),
KEY `idx_app_out_store` (`app_key`, `out_store_id`),
KEY `idx_order_del` (`list_order`, `is_del`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商家店铺表';
-- 开放平台应用表
CREATE TABLE `open_app` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`app_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '应用名称',
`app_key` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'AppKey',
`app_secret` VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'AppSecret',
`city_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '关联城市/租户ID（必填，租户隔离核心字段）',
`store_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '关联店铺ID，0=不限制',
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0=禁用 1=正常',
`webhook_url` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Webhook回调地址',
`webhook_events` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '订阅事件JSON数组',
`remark` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_app_key` (`app_key`),
KEY `idx_city_id` (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='开放平台应用表';
-- Webhook 推送日志表
CREATE TABLE `webhook_log` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`app_id` BIGINT UNSIGNED NOT NULL COMMENT '应用ID',
`event` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '事件类型',
`biz_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '业务ID',
`url` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '推送URL',
`payload` TEXT COMMENT '推送内容JSON',
`response_code` INT NOT NULL DEFAULT 0 COMMENT 'HTTP响应码',
`response_body` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '响应内容',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0=失败 1=成功',
`retry_count` INT NOT NULL DEFAULT 0 COMMENT '重试次数',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_app_event` (`app_id`, `event`),
KEY `idx_biz_id` (`biz_id`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Webhook推送日志表';
-- 超级管理员表
CREATE TABLE `admin_user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_login` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '登录账号',
`user_pass` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '密码（MD5）',
`user_nickname` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '昵称',
`user_status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0=禁用 1=正常',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_login` (`user_login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='超级管理员表';
-- orders 表补充字段（如已有 orders 表，执行以下 ALTER）
ALTER TABLE `orders` ADD COLUMN `out_order_no` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '外部系统订单号' AFTER `order_no`;
ALTER TABLE `orders` ADD COLUMN `app_key` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '接入方AppKey' AFTER `out_order_no`;
ALTER TABLE `orders` ADD COLUMN `callback_url` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '状态回调地址' AFTER `app_key`;
ALTER TABLE `orders` ADD INDEX `idx_app_out_order` (`app_key`, `out_order_no`);
-- 骑手评价表
CREATE TABLE `rider_evaluate` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`uid` BIGINT UNSIGNED NOT NULL COMMENT '评价用户ID',
`oid` BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
`rid` BIGINT UNSIGNED NOT NULL COMMENT '骑手ID',
`content` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '评价内容',
`star` TINYINT NOT NULL DEFAULT 5 COMMENT '星级1-5',
`city_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`add_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uid_oid` (`uid`, `oid`),
KEY `idx_rid` (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='骑手评价表';
-- 退款原因配置表
CREATE TABLE `orders_refund_reason` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '原因描述',
`role` TINYINT NOT NULL DEFAULT 1 COMMENT '1=用户 2=骑手',
`list_order` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='退款原因配置表';
-- 退款申请记录表
CREATE TABLE `orders_refund_record` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`oid` BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
`order_no` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '订单号',
`uid` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '申请人ID',
`role` TINYINT NOT NULL DEFAULT 1 COMMENT '1=用户 2=骑手',
`reason_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`reason` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '退款原因',
`money` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '退款金额',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0=待处理 1=通过 2=拒绝',
`remark` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '处理备注',
`add_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`handle_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_oid` (`oid`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='退款申请记录表';
-- 退款原因初始数据
INSERT INTO `orders_refund_reason` (`name`, `role`, `list_order`) VALUES
('骑手长时间未接单', 1, 1),
('骑手态度恶劣', 1, 2),
('物品损坏', 1, 3),
('其他原因', 1, 99),
('用户恶意单', 2, 1),
('无法完成配送', 2, 2),
('其他原因', 2, 99);
-- 外部门店表（接入方通过开放平台同步自己系统的门店）
CREATE TABLE `ext_store` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`app_key` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '所属应用AppKey',
`out_store_id` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '接入方门店原始ID',
`name` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '门店名称',
`address` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '门店地址',
`lng` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '经度',
`lat` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '纬度',
`city_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '所属城市ID',
`phone` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '联系电话',
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0=关闭 1=营业',
`remark` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`update_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_app_store` (`app_key`, `out_store_id`),
KEY `idx_city_id` (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='外部门店表';
-- orders 表补充货物快照字段
ALTER TABLE `orders` ADD COLUMN `items_json` TEXT COMMENT '货物清单快照JSON' AFTER `callback_url`;
ALTER TABLE `orders` ADD COLUMN `item_remark` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '整单货物备注' AFTER `items_json`;
ALTER TABLE `orders` ADD COLUMN `ext_store_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '关联外部门店ID' AFTER `item_remark`;