Domů Procházet Nápověda
Registrovat se Přihlásit se
kym
/
haha
2
0
Rozštěpit 0
Soubory Úkoly 0 Pull Requesty 0 Wiki
Strom: 0556a52195
Větve Značky
haha
/
haha-miniapp
/
src
/
main
/
resources
/
sql
/
order.sql

order.sql 21 KB
Historie Surový

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119 
  1. -- 创建订单表
    2. 

  2. CREATE TABLE IF NOT EXISTS `t_order` (
    3. 

  3.   `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
    4. 

  4.   `order_no` VARCHAR(64) NOT NULL COMMENT '订单号（系统内部订单号）',
    5. 

  5.   `out_trade_no` VARCHAR(64) DEFAULT NULL COMMENT '商户订单号（支付订单号）',
    6. 

  6.   `haha_order_no` VARCHAR(64) DEFAULT NULL COMMENT '哈哈平台订单号',
    7. 

  7.   `user_id` BIGINT NOT NULL COMMENT '用户ID',
    8. 

  8.   `device_id` VARCHAR(64) NOT NULL COMMENT '设备序列号',
    9. 

  9.   `total_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '订单总金额',
    10. 

  10.   `pay_status` VARCHAR(20) NOT NULL DEFAULT 'UNPAID' COMMENT '支付状态：UNPAID-未支付，PAID-已支付，REFUND-已退款',
    11. 

  11.   `video_url` VARCHAR(500) DEFAULT NULL COMMENT '购物视频URL',
    12. 

  12.   `confidence` DECIMAL(5,2) DEFAULT NULL COMMENT '识别置信度（0-100）',
    13. 

  13.   `items_json` TEXT DEFAULT NULL COMMENT '订单商品明细JSON',
    14. 

  14.   `status` INT NOT NULL DEFAULT 1 COMMENT '订单状态：0-已取消，1-待支付，2-已完成，3-已关闭',
    15. 

  15.   `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    16. 

  16.   `pay_time` DATETIME DEFAULT NULL COMMENT '支付时间',
    17. 

  17.   PRIMARY KEY (`id`),
    18. 

  18.   UNIQUE KEY `uk_order_no` (`order_no`),
    19. 

  19.   KEY `idx_user_id` (`user_id`),
    20. 

  20.   KEY `idx_device_id` (`device_id`),
    21. 

  21.   KEY `idx_create_time` (`create_time`),
    22. 

  22.   KEY `idx_haha_order_no` (`haha_order_no`)
    23. 

  23. ) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
    24. 

  24. 

  25. -- 插入订单表测试数据
    26. 

  26. INSERT IGNORE INTO `t_order` (`order_no`, `out_trade_no`, `haha_order_no`, `user_id`, `device_id`, `total_amount`, `pay_status`, `video_url`, `confidence`, `items_json`, `status`, `create_time`, `pay_time`) VALUES
    27. 

  27.     ('ORD202601252110345920', 'OUT202601252110347833', 'HAHA202601252110346621', 10000, 'DEVICE_001', 43.0, 'REFUND', NULL, 84.1, '[{"name": "口香糖", "price": 3.0, "quantity": 2}, {"name": "可乐", "price": 3.5, "quantity": 2}, {"name": "面包", "price": 7.5, "quantity": 1}, {"name": "面包", "price": 7.5, "quantity": 3}]', 3, '2026-01-17 14:52:34', NULL),
    28. 

  28.     ('ORD202601252110342389', 'OUT202601252110343653', 'HAHA202601252110348775', 10000, 'DEVICE_003', 46.0, 'PAID', NULL, NULL, '[{"name": "口香糖", "price": 3.0, "quantity": 1}, {"name": "薯片", "price": 6.0, "quantity": 3}, {"name": "饮料", "price": 5.0, "quantity": 3}, {"name": "口香糖", "price": 3.0, "quantity": 1}, {"name": "雪碧", "price": 3.5, "quantity": 2}]', 2, '2026-01-06 03:14:34', '2026-01-06 03:30:34'),
    29. 

  29.     ('ORD202601252110342567', NULL, 'HAHA202601252110341393', 10000, 'DEVICE_006', 62.5, 'UNPAID', 'https://example.com/video/ORD202601252110342567.mp4', 92.56, '[{"name": "坚果", "price": 12.0, "quantity": 3}, {"name": "矿泉水", "price": 2.0, "quantity": 3}, {"name": "饼干", "price": 4.5, "quantity": 1}, {"name": "巧克力", "price": 8.0, "quantity": 2}]', 0, '2026-01-06 10:01:34', NULL),
    30. 

  30.     ('ORD202601252110346048', NULL, 'HAHA202601252110345459', 10000, 'DEVICE_001', 14.5, 'UNPAID', 'https://example.com/video/ORD202601252110346048.mp4', NULL, '[{"name": "矿泉水", "price": 2.0, "quantity": 2}, {"name": "可乐", "price": 3.5, "quantity": 1}, {"name": "矿泉水", "price": 2.0, "quantity": 1}, {"name": "饮料", "price": 5.0, "quantity": 1}]', 1, '2026-01-21 13:45:34', NULL),
    31. 

  31.     ('ORD202601252110341086', 'OUT202601252110341077', 'HAHA202601252110345389', 10000, 'DEVICE_006', 6.0, 'UNPAID', 'https://example.com/video/ORD202601252110341086.mp4', NULL, '[{"name": "矿泉水", "price": 2.0, "quantity": 3}]', 1, '2025-12-29 22:14:34', NULL),
    32. 

  32.     ('ORD202601252110343968', NULL, NULL, 10000, 'DEVICE_007', 43.0, 'PAID', 'https://example.com/video/ORD202601252110343968.mp4', 88.96, '[{"name": "坚果", "price": 12.0, "quantity": 3}, {"name": "可乐", "price": 3.5, "quantity": 2}]', 2, '2025-12-27 05:30:34', '2025-12-27 06:00:34'),
    33. 

  33.     ('ORD202601252110344836', 'OUT202601252110343564', NULL, 10000, 'DEVICE_001', 22.5, 'PAID', 'https://example.com/video/ORD202601252110344836.mp4', NULL, '[{"name": "饼干", "price": 4.5, "quantity": 1}, {"name": "薯片", "price": 6.0, "quantity": 3}]', 2, '2026-01-11 19:29:34', '2026-01-11 19:35:34'),
    34. 

  34.     ('ORD202601252110341177', 'OUT202601252110345719', 'HAHA202601252110345877', 10000, 'DEVICE_005', 36.0, 'REFUND', 'https://example.com/video/ORD202601252110341177.mp4', NULL, '[{"name": "薯片", "price": 6.0, "quantity": 2}, {"name": "坚果", "price": 12.0, "quantity": 2}]', 3, '2026-01-08 09:21:34', NULL),
    35. 

  35.     ('ORD202601252110348307', 'OUT202601252110342226', 'HAHA202601252110345370', 10000, 'DEVICE_002', 7.5, 'PAID', 'https://example.com/video/ORD202601252110348307.mp4', NULL, '[{"name": "可乐", "price": 3.5, "quantity": 1}, {"name": "矿泉水", "price": 2.0, "quantity": 2}]', 2, '2026-01-20 05:21:34', '2026-01-20 05:42:34'),
    36. 

  36.     ('ORD202601252110348755', 'OUT202601252110345810', 'HAHA202601252110344532', 10000, 'DEVICE_005', 22.0, 'UNPAID', NULL, 99.68, '[{"name": "口香糖", "price": 3.0, "quantity": 2}, {"name": "巧克力", "price": 8.0, "quantity": 2}]', 0, '2026-01-07 20:30:34', NULL),
    37. 

  37.     ('ORD202601252110344404', 'OUT202601252110348190', NULL, 10000, 'DEVICE_009', 25.5, 'PAID', 'https://example.com/video/ORD202601252110344404.mp4', NULL, '[{"name": "口香糖", "price": 3.0, "quantity": 1}, {"name": "面包", "price": 7.5, "quantity": 3}]', 2, '2025-12-27 00:02:34', '2025-12-27 00:15:34'),
    38. 

  38.     ('ORD202601252110343825', NULL, NULL, 10000, 'DEVICE_009', 9.0, 'REFUND', NULL, 94.52, '[{"name": "饼干", "price": 4.5, "quantity": 2}]', 3, '2025-12-31 14:02:34', NULL),
    39. 

  39.     ('ORD202601252110348464', 'OUT202601252110343933', 'HAHA202601252110341123', 10000, 'DEVICE_002', 15.0, 'PAID', 'https://example.com/video/ORD202601252110348464.mp4', 92.73, '[{"name": "面包", "price": 7.5, "quantity": 2}]', 2, '2025-12-26 05:36:34', '2025-12-26 05:57:34'),
    40. 

  40.     ('ORD202601252110342408', 'OUT202601252110341810', 'HAHA202601252110344480', 10000, 'DEVICE_001', 16.0, 'UNPAID', 'https://example.com/video/ORD202601252110342408.mp4', NULL, '[{"name": "巧克力", "price": 8.0, "quantity": 2}]', 1, '2026-01-14 03:19:34', NULL),
    41. 

  41.     ('ORD202601252110345569', 'OUT202601252110344489', 'HAHA202601252110343588', 10000, 'DEVICE_010', 12.0, 'PAID', 'https://example.com/video/ORD202601252110345569.mp4', 85.1, '[{"name": "坚果", "price": 12.0, "quantity": 1}]', 2, '2025-12-27 12:51:34', '2025-12-27 13:16:34'),
    42. 

  42.     ('ORD202601252110348594', 'OUT202601252110348455', NULL, 10000, 'DEVICE_009', 19.0, 'PAID', NULL, 90.45, '[{"name": "饮料", "price": 5.0, "quantity": 1}, {"name": "雪碧", "price": 3.5, "quantity": 1}, {"name": "雪碧", "price": 3.5, "quantity": 3}]', 2, '2025-12-31 14:31:34', '2025-12-31 14:45:34'),
    43. 

  43.     ('ORD202601252110348085', 'OUT202601252110346505', NULL, 10000, 'DEVICE_010', 8.0, 'PAID', NULL, NULL, '[{"name": "饼干", "price": 4.5, "quantity": 1}, {"name": "雪碧", "price": 3.5, "quantity": 1}]', 2, '2026-01-17 22:09:34', '2026-01-17 22:19:34'),
    44. 

  44.     ('ORD202601252110343688', 'OUT202601252110342550', 'HAHA202601252110343176', 10000, 'DEVICE_010', 24.0, 'REFUND', NULL, NULL, '[{"name": "巧克力", "price": 8.0, "quantity": 3}]', 3, '2026-01-25 14:49:34', NULL),
    45. 

  45.     ('ORD202601252110341919', 'OUT202601252110341806', 'HAHA202601252110343116', 10000, 'DEVICE_001', 9.0, 'PAID', 'https://example.com/video/ORD202601252110341919.mp4', NULL, '[{"name": "雪碧", "price": 3.5, "quantity": 2}, {"name": "矿泉水", "price": 2.0, "quantity": 1}]', 2, '2026-01-23 05:13:34', '2026-01-23 05:26:34'),
    46. 

  46.     ('ORD202601252110346035', NULL, 'HAHA202601252110342496', 10000, 'DEVICE_007', 65.0, 'UNPAID', 'https://example.com/video/ORD202601252110346035.mp4', 80.24, '[{"name": "饼干", "price": 4.5, "quantity": 3}, {"name": "雪碧", "price": 3.5, "quantity": 3}, {"name": "巧克力", "price": 8.0, "quantity": 1}, {"name": "口香糖", "price": 3.0, "quantity": 3}, {"name": "坚果", "price": 12.0, "quantity": 2}]', 1, '2026-01-09 14:03:34', NULL),
    47. 

  47.     ('ORD202601252110343091', 'OUT202601252110349313', NULL, 10000, 'DEVICE_005', 7.5, 'UNPAID', 'https://example.com/video/ORD202601252110343091.mp4', 81.26, '[{"name": "口香糖", "price": 3.0, "quantity": 1}, {"name": "饼干", "price": 4.5, "quantity": 1}]', 1, '2026-01-23 02:35:34', NULL),
    48. 

  48.     ('ORD202601252110345610', NULL, 'HAHA202601252110341193', 10000, 'DEVICE_006', 10.5, 'UNPAID', 'https://example.com/video/ORD202601252110345610.mp4', NULL, '[{"name": "雪碧", "price": 3.5, "quantity": 3}]', 0, '2026-01-25 05:00:34', NULL),
    49. 

  49.     ('ORD202601252110345955', 'OUT202601252110347834', NULL, 10000, 'DEVICE_001', 9.0, 'PAID', 'https://example.com/video/ORD202601252110345955.mp4', NULL, '[{"name": "口香糖", "price": 3.0, "quantity": 3}]', 2, '2026-01-20 05:10:34', '2026-01-20 05:33:34'),
    50. 

  50.     ('ORD202601252110345190', NULL, 'HAHA202601252110349146', 10000, 'DEVICE_007', 49.0, 'PAID', 'https://example.com/video/ORD202601252110345190.mp4', NULL, '[{"name": "口香糖", "price": 3.0, "quantity": 2}, {"name": "巧克力", "price": 8.0, "quantity": 2}, {"name": "口香糖", "price": 3.0, "quantity": 1}, {"name": "面包", "price": 7.5, "quantity": 2}, {"name": "口香糖", "price": 3.0, "quantity": 3}]', 2, '2026-01-03 05:10:34', '2026-01-03 05:22:34'),
    51. 

  51.     ('ORD202601252110349920', 'OUT202601252110342403', 'HAHA202601252110344363', 10000, 'DEVICE_007', 5.0, 'PAID', NULL, NULL, '[{"name": "饮料", "price": 5.0, "quantity": 1}]', 2, '2026-01-22 13:01:34', '2026-01-22 13:19:34'),
    52. 

  52.     ('ORD202601252110345236', 'OUT202601252110342498', 'HAHA202601252110343272', 10000, 'DEVICE_008', 25.5, 'UNPAID', NULL, NULL, '[{"name": "雪碧", "price": 3.5, "quantity": 2}, {"name": "坚果", "price": 12.0, "quantity": 1}, {"name": "矿泉水", "price": 2.0, "quantity": 1}, {"name": "饼干", "price": 4.5, "quantity": 1}]', 1, '2026-01-14 19:56:34', NULL),
    53. 

  53.     ('ORD202601252110341423', NULL, 'HAHA202601252110341823', 10000, 'DEVICE_002', 33.0, 'UNPAID', NULL, 85.44, '[{"name": "坚果", "price": 12.0, "quantity": 2}, {"name": "口香糖", "price": 3.0, "quantity": 3}]', 1, '2026-01-06 19:34:34', NULL),
    54. 

  54.     ('ORD202601252110341533', NULL, 'HAHA202601252110342284', 10000, 'DEVICE_009', 7.0, 'UNPAID', 'https://example.com/video/ORD202601252110341533.mp4', NULL, '[{"name": "可乐", "price": 3.5, "quantity": 2}]', 1, '2026-01-12 17:53:34', NULL),
    55. 

  55.     ('ORD202601252110345888', 'OUT202601252110341261', NULL, 10000, 'DEVICE_006', 69.5, 'REFUND', NULL, NULL, '[{"name": "坚果", "price": 12.0, "quantity": 2}, {"name": "雪碧", "price": 3.5, "quantity": 1}, {"name": "薯片", "price": 6.0, "quantity": 3}, {"name": "薯片", "price": 6.0, "quantity": 1}, {"name": "薯片", "price": 6.0, "quantity": 3}]', 3, '2026-01-20 09:26:34', NULL),
    56. 

  56.     ('ORD202601252110343952', 'OUT202601252110347635', 'HAHA202601252110343170', 10000, 'DEVICE_004', 29.0, 'PAID', 'https://example.com/video/ORD202601252110343952.mp4', 82.29, '[{"name": "饼干", "price": 4.5, "quantity": 1}, {"name": "可乐", "price": 3.5, "quantity": 2}, {"name": "饮料", "price": 5.0, "quantity": 2}, {"name": "面包", "price": 7.5, "quantity": 1}]', 2, '2026-01-10 00:28:34', '2026-01-10 00:48:34'),
    57. 

  57.     ('ORD202601252110344665', 'OUT202601252110342457', 'HAHA202601252110343046', 10000, 'DEVICE_008', 24.5, 'REFUND', NULL, 86.67, '[{"name": "可乐", "price": 3.5, "quantity": 2}, {"name": "可乐", "price": 3.5, "quantity": 3}, {"name": "雪碧", "price": 3.5, "quantity": 2}]', 3, '2026-01-17 07:02:34', NULL),
    58. 

  58.     ('ORD202601252110341837', 'OUT202601252110347753', 'HAHA202601252110349386', 10000, 'DEVICE_010', 35.0, 'REFUND', NULL, NULL, '[{"name": "面包", "price": 7.5, "quantity": 3}, {"name": "口香糖", "price": 3.0, "quantity": 3}, {"name": "雪碧", "price": 3.5, "quantity": 1}]', 3, '2026-01-22 16:50:34', NULL),
    59. 

  59.     ('ORD202601252110342223', 'OUT202601252110342591', NULL, 10000, 'DEVICE_006', 34.0, 'UNPAID', NULL, 86.49, '[{"name": "巧克力", "price": 8.0, "quantity": 2}, {"name": "薯片", "price": 6.0, "quantity": 3}]', 1, '2025-12-30 11:49:34', NULL),
    60. 

  60.     ('ORD202601252110343815', 'OUT202601252110346369', 'HAHA202601252110342340', 10000, 'DEVICE_002', 3.5, 'REFUND', 'https://example.com/video/ORD202601252110343815.mp4', 92.58, '[{"name": "雪碧", "price": 3.5, "quantity": 1}]', 3, '2026-01-10 15:53:34', NULL),
    61. 

  61.     ('ORD202601252110344588', 'OUT202601252110349889', NULL, 10000, 'DEVICE_008', 48.0, 'UNPAID', 'https://example.com/video/ORD202601252110344588.mp4', 99.93, '[{"name": "薯片", "price": 6.0, "quantity": 2}, {"name": "坚果", "price": 12.0, "quantity": 3}]', 1, '2026-01-12 23:14:34', NULL),
    62. 

  62.     ('ORD202601252110342958', NULL, 'HAHA202601252110342690', 10000, 'DEVICE_003', 2.0, 'REFUND', NULL, 97.48, '[{"name": "矿泉水", "price": 2.0, "quantity": 1}]', 3, '2026-01-04 04:41:34', NULL),
    63. 

  63.     ('ORD202601252110341689', 'OUT202601252110346847', 'HAHA202601252110348888', 10000, 'DEVICE_001', 35.0, 'PAID', 'https://example.com/video/ORD202601252110341689.mp4', NULL, '[{"name": "面包", "price": 7.5, "quantity": 3}, {"name": "饼干", "price": 4.5, "quantity": 1}, {"name": "矿泉水", "price": 2.0, "quantity": 3}, {"name": "矿泉水", "price": 2.0, "quantity": 1}]', 2, '2025-12-26 10:15:34', '2025-12-26 10:22:34'),
    64. 

  64.     ('ORD202601252110348189', 'OUT202601252110341430', 'HAHA202601252110343407', 10000, 'DEVICE_009', 74.5, 'REFUND', 'https://example.com/video/ORD202601252110348189.mp4', 89.09, '[{"name": "饼干", "price": 4.5, "quantity": 1}, {"name": "巧克力", "price": 8.0, "quantity": 2}, {"name": "口香糖", "price": 3.0, "quantity": 3}, {"name": "口香糖", "price": 3.0, "quantity": 3}, {"name": "坚果", "price": 12.0, "quantity": 3}]', 3, '2026-01-23 13:21:34', NULL),
    65. 

  65.     ('ORD202601252110346432', NULL, NULL, 10000, 'DEVICE_009', 3.5, 'REFUND', NULL, NULL, '[{"name": "可乐", "price": 3.5, "quantity": 1}]', 3, '2026-01-23 05:54:34', NULL),
    66. 

  66.     ('ORD202601252110341485', NULL, 'HAHA202601252110345095', 10000, 'DEVICE_004', 19.5, 'REFUND', NULL, 86.07, '[{"name": "口香糖", "price": 3.0, "quantity": 1}, {"name": "雪碧", "price": 3.5, "quantity": 1}, {"name": "饼干", "price": 4.5, "quantity": 2}, {"name": "矿泉水", "price": 2.0, "quantity": 2}]', 3, '2025-12-30 17:10:34', NULL),
    67. 

  67.     ('ORD202601252110341615', 'OUT202601252110344250', NULL, 10000, 'DEVICE_004', 32.5, 'REFUND', NULL, 91.46, '[{"name": "可乐", "price": 3.5, "quantity": 2}, {"name": "口香糖", "price": 3.0, "quantity": 1}, {"name": "面包", "price": 7.5, "quantity": 2}, {"name": "面包", "price": 7.5, "quantity": 1}]', 3, '2025-12-27 10:31:34', NULL),
    68. 

  68.     ('ORD202601252110346188', 'OUT202601252110343359', NULL, 10000, 'DEVICE_005', 48.5, 'REFUND', 'https://example.com/video/ORD202601252110346188.mp4', 92.71, '[{"name": "可乐", "price": 3.5, "quantity": 2}, {"name": "巧克力", "price": 8.0, "quantity": 3}, {"name": "口香糖", "price": 3.0, "quantity": 2}, {"name": "面包", "price": 7.5, "quantity": 1}, {"name": "矿泉水", "price": 2.0, "quantity": 2}]', 3, '2026-01-17 02:02:34', NULL),
    69. 

  69.     ('ORD202601252110344579', 'OUT202601252110349271', 'HAHA202601252110344919', 10000, 'DEVICE_010', 22.5, 'UNPAID', 'https://example.com/video/ORD202601252110344579.mp4', NULL, '[{"name": "饼干", "price": 4.5, "quantity": 3}, {"name": "口香糖", "price": 3.0, "quantity": 3}]', 1, '2026-01-03 04:44:34', NULL),
    70. 

  70.     ('ORD202601252110348101', NULL, NULL, 10000, 'DEVICE_003', 43.0, 'PAID', 'https://example.com/video/ORD202601252110348101.mp4', 82.97, '[{"name": "可乐", "price": 3.5, "quantity": 2}, {"name": "坚果", "price": 12.0, "quantity": 1}, {"name": "薯片", "price": 6.0, "quantity": 2}, {"name": "坚果", "price": 12.0, "quantity": 1}]', 2, '2025-12-30 07:45:34', '2025-12-30 07:56:34'),
    71. 

  71.     ('ORD202601252110341881', 'OUT202601252110347746', 'HAHA202601252110348053', 10000, 'DEVICE_003', 28.5, 'REFUND', NULL, NULL, '[{"name": "面包", "price": 7.5, "quantity": 3}, {"name": "矿泉水", "price": 2.0, "quantity": 3}]', 3, '2026-01-08 18:39:34', NULL),
    72. 

  72.     ('ORD202601252110346673', 'OUT202601252110347179', 'HAHA202601252110342567', 10000, 'DEVICE_010', 40.0, 'UNPAID', NULL, NULL, '[{"name": "口香糖", "price": 3.0, "quantity": 3}, {"name": "薯片", "price": 6.0, "quantity": 2}, {"name": "矿泉水", "price": 2.0, "quantity": 2}, {"name": "饮料", "price": 5.0, "quantity": 3}]', 1, '2025-12-26 12:01:34', NULL),
    73. 

  73.     ('ORD202601252110343535', 'OUT202601252110342752', NULL, 10000, 'DEVICE_009', 13.5, 'REFUND', NULL, NULL, '[{"name": "饼干", "price": 4.5, "quantity": 1}, {"name": "饼干", "price": 4.5, "quantity": 2}]', 3, '2026-01-19 13:32:34', NULL),
    74. 

  74.     ('ORD202601252110342391', 'OUT202601252110345772', 'HAHA202601252110348319', 10000, 'DEVICE_001', 18.5, 'PAID', 'https://example.com/video/ORD202601252110342391.mp4', 98.39, '[{"name": "饮料", "price": 5.0, "quantity": 3}, {"name": "雪碧", "price": 3.5, "quantity": 1}]', 2, '2026-01-05 02:46:34', '2026-01-05 03:02:34'),
    75. 

  75.     ('ORD202601252110342174', 'OUT202601252110342891', 'HAHA202601252110346685', 10000, 'DEVICE_005', 22.5, 'REFUND', 'https://example.com/video/ORD202601252110342174.mp4', NULL, '[{"name": "可乐", "price": 3.5, "quantity": 3}, {"name": "薯片", "price": 6.0, "quantity": 2}]', 3, '2026-01-01 02:28:34', NULL),
    76. 

  76.     ('ORD202601252110349995', 'OUT202601252110345175', 'HAHA202601252110346279', 10000, 'DEVICE_003', 53.0, 'UNPAID', NULL, 80.66, '[{"name": "雪碧", "price": 3.5, "quantity": 1}, {"name": "饼干", "price": 4.5, "quantity": 1}, {"name": "口香糖", "price": 3.0, "quantity": 1}, {"name": "薯片", "price": 6.0, "quantity": 3}, {"name": "巧克力", "price": 8.0, "quantity": 3}]', 1, '2026-01-13 23:39:34', NULL),
    77. 

  77.     ('ORD202601252110347484', 'OUT202601252110349012', 'HAHA202601252110347571', 10000, 'DEVICE_010', 15.0, 'REFUND', NULL, 93.72, '[{"name": "巧克力", "price": 8.0, "quantity": 1}, {"name": "雪碧", "price": 3.5, "quantity": 2}]', 3, '2026-01-11 19:49:34', NULL),
    78. 

  78.     ('ORD202601252110345218', NULL, 'HAHA202601252110343189', 10000, 'DEVICE_003', 39.0, 'UNPAID', NULL, NULL, '[{"name": "薯片", "price": 6.0, "quantity": 1}, {"name": "薯片", "price": 6.0, "quantity": 3}, {"name": "面包", "price": 7.5, "quantity": 2}]', 1, '2026-01-11 16:11:34', NULL),
    79. 

  79.     ('ORD202601252110347510', 'OUT202601252110347258', 'HAHA202601252110347241', 10000, 'DEVICE_004', 88.0, 'REFUND', NULL, NULL, '[{"name": "雪碧", "price": 3.5, "quantity": 3}, {"name": "坚果", "price": 12.0, "quantity": 3}, {"name": "面包", "price": 7.5, "quantity": 3}, {"name": "饮料", "price": 5.0, "quantity": 2}, {"name": "口香糖", "price": 3.0, "quantity": 3}]', 3, '2026-01-23 22:28:34', NULL),
    80. 

  80.     ('ORD202601252110342013', 'OUT202601252110348634', 'HAHA202601252110345165', 10000, 'DEVICE_003', 54.5, 'UNPAID', 'https://example.com/video/ORD202601252110342013.mp4', 80.9, '[{"name": "巧克力", "price": 8.0, "quantity": 3}, {"name": "可乐", "price": 3.5, "quantity": 1}, {"name": "饮料", "price": 5.0, "quantity": 3}, {"name": "薯片", "price": 6.0, "quantity": 2}]', 1, '2026-01-02 17:53:34', NULL),
    81. 

  81.     ('ORD202601252110346533', 'OUT202601252110341183', NULL, 10000, 'DEVICE_003', 58.0, 'REFUND', NULL, NULL, '[{"name": "巧克力", "price": 8.0, "quantity": 1}, {"name": "巧克力", "price": 8.0, "quantity": 1}, {"name": "矿泉水", "price": 2.0, "quantity": 3}, {"name": "坚果", "price": 12.0, "quantity": 3}]', 3, '2026-01-12 07:50:34', NULL);
    82. 

  82. 

  83. -- ============================================
    84. 

  84. -- 支付系统多渠道重构 - 新增字段
    85. 

  85. -- ============================================
    86. 

  86. ALTER TABLE `t_order`
    87. 

  87. ADD COLUMN `pay_channel` VARCHAR(20) DEFAULT NULL COMMENT '支付渠道（wechat/alipay/alipay_credit/balance）' AFTER `pay_status`,
    88. 

  88. ADD COLUMN `pay_type` VARCHAR(50) DEFAULT NULL COMMENT '支付方式描述' AFTER `pay_channel`,
    89. 

  89. ADD COLUMN `transaction_id` VARCHAR(100) DEFAULT NULL COMMENT '第三方支付平台交易号' AFTER `pay_type`,
    90. 

  90. ADD COLUMN `refund_status` VARCHAR(20) DEFAULT NULL COMMENT '退款状态' AFTER `pay_time`,
    91. 

  91. ADD COLUMN `refund_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '退款金额' AFTER `refund_status`,
    92. 

  92. ADD COLUMN `refund_time` DATETIME DEFAULT NULL COMMENT '退款时间' AFTER `refund_amount`,
    93. 

  93. ADD COLUMN `refund_reason` VARCHAR(255) DEFAULT NULL COMMENT '退款原因' AFTER `refund_time`;
    94. 

  94. 

  95. -- 新增索引
    96. 

  96. ALTER TABLE `t_order`
    97. 

  97. ADD INDEX `idx_pay_channel` (`pay_channel`),
    98. 

  98. ADD INDEX `idx_transaction_id` (`transaction_id`);
    99. 

  99. 

  100. -- =============================================
    101. 

  101. -- 支付分（先享后付）相关字段
    102. 

  102. -- =============================================
    103. 

  103. ALTER TABLE t_order ADD COLUMN pay_score_order_id VARCHAR(64) DEFAULT NULL COMMENT '支付分服务订单号';
    104. 

  104. ALTER TABLE t_order ADD COLUMN pay_score_state VARCHAR(20) DEFAULT NULL COMMENT '支付分订单状态';
    105. 

  105. ALTER TABLE t_order ADD COLUMN service_id VARCHAR(64) DEFAULT NULL COMMENT '支付分服务ID';
    106. 

  106. ALTER TABLE t_order ADD COLUMN service_start_time DATETIME DEFAULT NULL COMMENT '服务开始时间';
    107. 

  107. ALTER TABLE t_order ADD COLUMN service_end_time DATETIME DEFAULT NULL COMMENT '服务结束时间';
    108. 

  108. ALTER TABLE t_order ADD INDEX idx_pay_score_state (pay_score_state);
    109. 

  109. 

  110. -- =============================================
    111. 

  111. -- 订单幂等性：为activity_id添加唯一索引
    112. 

  112. -- 确保同一activity_id不会创建多个订单
    113. 

  113. -- =============================================
    114. 

  114. -- 注意：如果已存在重复的activity_id数据，需要先清理后再添加索引
    115. 

  115. -- 可以使用以下查询检查重复数据：
    116. 

  116. -- SELECT activity_id, COUNT(*) as cnt FROM t_order WHERE activity_id IS NOT NULL GROUP BY activity_id HAVING COUNT(*) > 1;
    117. 

  117. 

  118. ALTER TABLE `t_order`
    119. 

  119. ADD UNIQUE INDEX `uk_activity_id` (`activity_id`);
    120.