SET @db_name = DATABASE();

CREATE TABLE IF NOT EXISTS `tcm_payment_flow` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `flow_no` VARCHAR(64) NOT NULL COMMENT '平台流水号',
  `business_type` VARCHAR(32) NOT NULL COMMENT '业务类型：COURSE_PURCHASE课程购买 MEMBER_PURCHASE会员购买',
  `business_order_id` BIGINT UNSIGNED NOT NULL COMMENT '业务订单ID',
  `business_order_no` VARCHAR(32) NOT NULL COMMENT '业务订单编号',
  `flow_type` VARCHAR(32) NOT NULL COMMENT '流水类型：PAYMENT支付 REFUND退款',
  `trade_status` VARCHAR(32) NOT NULL COMMENT '交易状态：SUCCESS成功 REFUNDED已退款',
  `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `user_nickname` VARCHAR(64) DEFAULT NULL COMMENT '用户昵称快照',
  `user_phone` VARCHAR(20) DEFAULT NULL COMMENT '用户手机号快照',
  `product_name_snapshot` VARCHAR(255) NOT NULL COMMENT '商品名称快照',
  `payment_method` VARCHAR(32) NOT NULL COMMENT '支付方式：WECHAT微信 ALIPAY支付宝',
  `transaction_amount` DECIMAL(10,2) NOT NULL COMMENT '交易金额',
  `third_party_trade_no` VARCHAR(64) DEFAULT NULL COMMENT '第三方支付单号',
  `third_party_refund_no` VARCHAR(64) DEFAULT NULL COMMENT '第三方退款单号',
  `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
  `trade_time` DATETIME NOT NULL COMMENT '交易时间',
  `source_type` VARCHAR(32) NOT NULL COMMENT '来源类型：COURSE_PAYMENT课程支付 COURSE_REFUND课程退款 MEMBER_PAYMENT会员支付 MEMBER_REFUND会员退款',
  `source_id` BIGINT UNSIGNED NOT NULL COMMENT '来源记录ID',
  `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `created_by` VARCHAR(64) NOT NULL DEFAULT '系统初始化' COMMENT '创建人',
  `updated_by` VARCHAR(64) NOT NULL DEFAULT '系统初始化' COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_payment_flow_no` (`flow_no`),
  UNIQUE KEY `uk_payment_flow_source` (`source_type`, `source_id`),
  KEY `idx_payment_flow_business_order` (`business_type`, `business_order_id`),
  KEY `idx_payment_flow_business_order_no` (`business_order_no`),
  KEY `idx_payment_flow_user` (`app_user_id`),
  KEY `idx_payment_flow_flow_type` (`flow_type`),
  KEY `idx_payment_flow_trade_status` (`trade_status`),
  KEY `idx_payment_flow_payment_method` (`payment_method`),
  KEY `idx_payment_flow_trade_time` (`trade_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付流水表';

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4220, 4200, '支付流水', 'trade_payment_flow', 'C', '/trade/flow/payment-flow', 'Trade/PaymentFlow', NULL, 10, 1, 1, 'payment:flow:view', '支付流水菜单', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4220 OR `menu_code` = 'trade_payment_flow');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4221, 4220, '导出支付流水', 'trade_payment_flow_export', 'B', NULL, NULL, NULL, 1, 1, 1, 'payment:flow:export', '支付流水导出按钮', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4221 OR `menu_code` = 'trade_payment_flow_export');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4230, 4200, '退费流水', 'trade_refund_flow', 'C', '/trade/flow/refund-flow', 'Trade/RefundFlow', NULL, 20, 1, 1, 'refund:flow:view', '退费流水菜单', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4230 OR `menu_code` = 'trade_refund_flow');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4231, 4230, '导出退费流水', 'trade_refund_flow_export', 'B', NULL, NULL, NULL, 1, 1, 1, 'refund:flow:export', '退费流水导出按钮', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4231 OR `menu_code` = 'trade_refund_flow_export');

UPDATE `sys_menu`
SET `sort` = 30
WHERE `id` = 4210
  AND `parent_id` = 4200;

INSERT IGNORE INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT sr.id, sm.id
FROM `sys_role` sr
JOIN `sys_menu` sm ON sm.id IN (4220, 4221, 4230, 4231)
WHERE sr.deleted = 0
  AND sr.status = 1
  AND EXISTS (
    SELECT 1
    FROM `sys_role_menu` rm
    WHERE rm.role_id = sr.id
      AND rm.menu_id IN (4200, 4210, 4220)
  );