pay_wallet.sql 9.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153
  1. -- ----------------------------
  2. -- 会员钱包表
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `pay_wallet`;
  5. CREATE TABLE `pay_wallet`
  6. (
  7. `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  8. `user_id` bigint NOT NULL COMMENT '用户编号',
  9. `user_type` tinyint NOT NULL DEFAULT 0 COMMENT '用户类型',
  10. `balance` int NOT NULL DEFAULT 0 COMMENT '余额,单位分',
  11. `total_expense` int NOT NULL DEFAULT 0 COMMENT '累计支出,单位分',
  12. `total_recharge` int NOT NULL DEFAULT 0 COMMENT '累计充值,单位分',
  13. `freeze_price` int NOT NULL DEFAULT 0 COMMENT '冻结金额,单位分',
  14. `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者',
  15. `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  16. `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
  17. `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  18. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  19. `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
  20. PRIMARY KEY (`id`) USING BTREE
  21. ) ENGINE=InnoDB COMMENT='会员钱包表';
  22. -- ----------------------------
  23. -- 会员钱包流水表
  24. -- ----------------------------
  25. DROP TABLE IF EXISTS `pay_wallet_transaction`;
  26. CREATE TABLE `pay_wallet_transaction`
  27. (
  28. `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  29. `wallet_id` bigint NOT NULL COMMENT '会员钱包 id',
  30. `biz_type` tinyint NOT NULL COMMENT '关联类型',
  31. `biz_id` varchar(64) NOT NULL COMMENT '关联业务编号',
  32. `no` varchar(64) NOT NULL COMMENT '流水号',
  33. `title` varchar(128) NOT NULL COMMENT '流水标题',
  34. `price` int NOT NULL COMMENT '交易金额, 单位分',
  35. `balance` int NOT NULL COMMENT '余额, 单位分',
  36. `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者',
  37. `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  38. `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
  39. `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  40. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  41. `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
  42. PRIMARY KEY (`id`) USING BTREE
  43. ) ENGINE=InnoDB COMMENT='会员钱包流水表';
  44. -- ----------------------------
  45. -- 会员钱包充值
  46. -- ----------------------------
  47. DROP TABLE IF EXISTS `pay_wallet_recharge`;
  48. CREATE TABLE `pay_wallet_recharge`
  49. (
  50. `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  51. `wallet_id` bigint NOT NULL COMMENT '会员钱包 id',
  52. `total_price` int NOT NULL COMMENT '用户实际到账余额,例如充 100 送 20,则该值是 120',
  53. `pay_price` int NOT NULL COMMENT '实际支付金额',
  54. `bonus_price` int NOT NULL COMMENT '钱包赠送金额',
  55. `pay_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否已支付:[0:未支付 1:已经支付过]',
  56. `pay_order_id` bigint NULL COMMENT '支付订单编号',
  57. `pay_channel_code` varchar(16) NULL COMMENT '支付成功的支付渠道',
  58. `pay_time` datetime NULL COMMENT '订单支付时间',
  59. `pay_refund_id` bigint NULL COMMENT '支付退款单编号',
  60. `refund_total_price` int NOT NULL DEFAULT 0 COMMENT '退款金额,包含赠送金额',
  61. `refund_pay_price` int NOT NULL DEFAULT 0 COMMENT '退款支付金额',
  62. `refund_bonus_price` int NOT NULL DEFAULT 0 COMMENT '退款钱包赠送金额',
  63. `refund_time` datetime NULL COMMENT '退款时间',
  64. `refund_status` int NOT NULL DEFAULT 0 COMMENT '退款状态',
  65. `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者',
  66. `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  67. `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
  68. `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  69. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  70. `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
  71. PRIMARY KEY (`id`) USING BTREE
  72. ) ENGINE=InnoDB COMMENT='会员钱包充值';
  73. -- ----------------------------
  74. -- 转账单表
  75. -- ----------------------------
  76. DROP TABLE IF EXISTS `pay_transfer`;
  77. CREATE TABLE `pay_transfer`
  78. (
  79. `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  80. `type` int NOT NULL COMMENT '类型',
  81. `app_id` bigint NOT NULL COMMENT '应用编号',
  82. `merchant_order_id` varchar(64) NOT NULL COMMENT '商户订单编号',
  83. `price` int NOT NULL COMMENT '转账金额,单位:分',
  84. `title` varchar(512) NOT NULL COMMENT '转账标题',
  85. `payee_info` varchar(512) NOT NULL COMMENT '收款人信息,不同类型和渠道不同',
  86. `status` tinyint NOT NULL COMMENT '转账状态',
  87. `success_time` datetime NULL COMMENT '转账成功时间',
  88. `extension_id` bigint NULL COMMENT '转账渠道编号',
  89. `no` varchar(64) NULL COMMENT '转账单号',
  90. `channel_id` bigint NULL COMMENT '转账渠道编号',
  91. `channel_code` varchar(32) NULL COMMENT '转账渠道编码',
  92. `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者',
  93. `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  94. `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
  95. `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  96. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  97. `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
  98. PRIMARY KEY (`id`) USING BTREE
  99. ) ENGINE=InnoDB COMMENT='转账单表';
  100. -- ----------------------------
  101. -- 转账扩展单
  102. -- ----------------------------
  103. DROP TABLE IF EXISTS `pay_transfer_extension`;
  104. CREATE TABLE `pay_transfer_extension`
  105. (
  106. `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  107. `no` varchar(64) NOT NULL COMMENT '转账单号',
  108. `transfer_id` bigint NOT NULL COMMENT '转账单编号',
  109. `channel_id` bigint NOT NULL COMMENT '转账渠道编号',
  110. `channel_code` varchar(32) NOT NULL COMMENT '转账渠道编码',
  111. `channel_extras` varchar(512) NULL DEFAULT NULL COMMENT '支付渠道的额外参数',
  112. `status` tinyint NOT NULL COMMENT '转账状态',
  113. `channel_notify_data` varchar(4096) NULL DEFAULT NULL COMMENT '支付渠道异步通知的内容',
  114. `creator` varchar(64) NULL DEFAULT '' COMMENT '创建者',
  115. `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  116. `updater` varchar(64) NULL DEFAULT '' COMMENT '更新者',
  117. `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  118. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  119. `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
  120. PRIMARY KEY (`id`) USING BTREE
  121. ) ENGINE=InnoDB COMMENT='转账拓展单表';
  122. -- ----------------------------
  123. -- Table structure for pay_demo_transfer
  124. -- ----------------------------
  125. DROP TABLE IF EXISTS `pay_demo_transfer`;
  126. CREATE TABLE `pay_demo_transfer` (
  127. `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单编号',
  128. `user_id` bigint UNSIGNED NOT NULL COMMENT '用户编号',
  129. `price` int NOT NULL COMMENT '转账金额,单位:分',
  130. `type` int NOT NULL COMMENT '转账类型',
  131. `payee_info` varchar(512) NOT NULL COMMENT '收款人信息,不同类型和渠道不同',
  132. `transfer_status` tinyint NOT NULL DEFAULT 0 COMMENT '转账状态',
  133. `pay_transfer_id` bigint NULL DEFAULT NULL COMMENT '转账订单编号',
  134. `pay_channel_code` varchar(16) NULL DEFAULT NULL COMMENT '转账支付成功渠道',
  135. `transfer_time` datetime NULL DEFAULT NULL COMMENT '转账支付时间',
  136. `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT '' COMMENT '创建者',
  137. `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  138. `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT '' COMMENT '更新者',
  139. `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  140. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  141. `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
  142. PRIMARY KEY (`id`) USING BTREE
  143. ) ENGINE = InnoDB COMMENT = '示例业务转账订单\n';
  144. ALTER TABLE `pay_channel`
  145. MODIFY COLUMN `config` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '支付渠道配置' AFTER `app_id`;