yumi-admin/scripts/sql/20260430_gm_gold_operation.sql

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `gm_gold_operation_record` (
  `id` bigint NOT NULL COMMENT '记录ID',
  `user_id` bigint NOT NULL COMMENT 'APP用户ID',
  `sys_origin` varchar(32) DEFAULT NULL COMMENT '系统来源',
  `operation_type` tinyint NOT NULL COMMENT '操作类型:0增加,1扣除',
  `quantity` decimal(20,2) NOT NULL COMMENT '金币数量',
  `reason_type` tinyint NOT NULL COMMENT '原因类型',
  `usd_quantity` decimal(20,2) DEFAULT NULL COMMENT '工资/充值折算USD金额',
  `remarks` varchar(500) NOT NULL COMMENT '备注',
  `status` varchar(20) NOT NULL DEFAULT 'PENDING' COMMENT '状态:PENDING待审核,APPROVED已通过,REJECTED已拒绝',
  `applicant_id` bigint NOT NULL COMMENT '申请后台用户ID',
  `applicant_name` varchar(100) DEFAULT NULL COMMENT '申请后台用户名称',
  `reviewer_id` bigint DEFAULT NULL COMMENT '审核后台用户ID',
  `reviewer_name` varchar(100) DEFAULT NULL COMMENT '审核后台用户名称',
  `review_remark` varchar(500) DEFAULT NULL COMMENT '审核备注',
  `track_id` varchar(64) NOT NULL COMMENT '钱包流水跟踪ID',
  `review_time` datetime DEFAULT NULL COMMENT '审核时间',
  `executed_time` datetime DEFAULT NULL COMMENT '执行时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user` bigint DEFAULT NULL COMMENT '创建用户',
  `update_user` bigint DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_gm_gold_track_id` (`track_id`),
  KEY `idx_gm_gold_status_time` (`status`, `create_time`),
  KEY `idx_gm_gold_user` (`user_id`),
  KEY `idx_gm_gold_applicant` (`applicant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='GM金币操作申请记录';

INSERT INTO sys_menu (
  id,
  parent_id,
  menu_name,
  path,
  menu_type,
  icon,
  create_user,
  update_user,
  sort,
  status,
  router,
  alias
)
SELECT
  1700000400,
  0,
  'GM操作',
  '/gm/operation',
  1,
  'lucide:shield',
  '0',
  '0',
  48,
  0,
  '/gm/operation',
  'GmOperation'
WHERE NOT EXISTS (
  SELECT 1 FROM sys_menu WHERE alias = 'GmOperation'
);

UPDATE sys_menu
SET
  parent_id = 0,
  menu_name = 'GM操作',
  path = '/gm/operation',
  menu_type = 1,
  icon = 'lucide:shield',
  create_user = '0',
  update_user = '0',
  sort = 48,
  status = 0,
  router = '/gm/operation'
WHERE alias = 'GmOperation';

INSERT INTO sys_menu (
  id,
  parent_id,
  menu_name,
  path,
  menu_type,
  icon,
  create_user,
  update_user,
  sort,
  status,
  router,
  alias
)
SELECT
  1700000401,
  (SELECT id FROM (SELECT id FROM sys_menu WHERE alias = 'GmOperation' LIMIT 1) AS parent_menu),
  '金币操作',
  'gm/gold-operation',
  2,
  'lucide:coins',
  '0',
  '0',
  1,
  0,
  'gold-operation',
  'GmGoldOperation'
WHERE NOT EXISTS (
  SELECT 1 FROM sys_menu WHERE alias = 'GmGoldOperation'
);

UPDATE sys_menu
SET
  parent_id = (SELECT id FROM (SELECT id FROM sys_menu WHERE alias = 'GmOperation' LIMIT 1) AS parent_menu),
  menu_name = '金币操作',
  path = 'gm/gold-operation',
  menu_type = 2,
  icon = 'lucide:coins',
  create_user = '0',
  update_user = '0',
  sort = 1,
  status = 0,
  router = 'gold-operation'
WHERE alias = 'GmGoldOperation';

INSERT INTO sys_menu (
  id,
  parent_id,
  menu_name,
  path,
  menu_type,
  icon,
  create_user,
  update_user,
  sort,
  status,
  router,
  alias
)
SELECT
  1700000402,
  (SELECT id FROM (SELECT id FROM sys_menu WHERE alias = 'GmOperation' LIMIT 1) AS parent_menu),
  '金币操作审核',
  'gm/gold-operation-review',
  2,
  'lucide:badge-check',
  '0',
  '0',
  2,
  0,
  'gold-operation-review',
  'GmGoldOperationReview'
WHERE NOT EXISTS (
  SELECT 1 FROM sys_menu WHERE alias = 'GmGoldOperationReview'
);

UPDATE sys_menu
SET
  parent_id = (SELECT id FROM (SELECT id FROM sys_menu WHERE alias = 'GmOperation' LIMIT 1) AS parent_menu),
  menu_name = '金币操作审核',
  path = 'gm/gold-operation-review',
  menu_type = 2,
  icon = 'lucide:badge-check',
  create_user = '0',
  update_user = '0',
  sort = 2,
  status = 0,
  router = 'gold-operation-review'
WHERE alias = 'GmGoldOperationReview';

DELETE role_menu
FROM sys_role_menu AS role_menu
JOIN sys_menu AS gm_menu
  ON gm_menu.id = role_menu.menu_id
LEFT JOIN sys_role AS valid_role
  ON valid_role.id = role_menu.role_id
WHERE gm_menu.alias IN ('GmOperation', 'GmGoldOperation', 'GmGoldOperationReview')
  AND valid_role.id IS NULL;

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT DISTINCT
  valid_source_role.id,
  target_menu.id
FROM sys_role_menu AS source_role
JOIN sys_role AS valid_source_role
  ON valid_source_role.id = source_role.role_id
JOIN sys_menu AS source_menu
  ON source_menu.id = source_role.menu_id
JOIN sys_menu AS target_menu
  ON target_menu.alias IN ('GmOperation', 'GmGoldOperation')
LEFT JOIN sys_role_menu AS existing_role_menu
  ON existing_role_menu.role_id = valid_source_role.id
  AND existing_role_menu.menu_id = target_menu.id
WHERE source_menu.alias IN ('OperateInsideOperationGold', 'OperateUserManage')
  AND existing_role_menu.id IS NULL;

DELETE role_menu
FROM sys_role_menu AS role_menu
JOIN sys_menu AS review_menu
  ON review_menu.id = role_menu.menu_id
WHERE review_menu.alias = 'GmGoldOperationReview';

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT DISTINCT
  yumi_role.id,
  target_menu.id
FROM sys_user AS yumi_user
JOIN sys_user_role AS user_role
  ON user_role.uid = yumi_user.uid
JOIN sys_role AS yumi_role
  ON yumi_role.id = user_role.role_id
JOIN sys_menu AS target_menu
  ON target_menu.alias IN ('GmOperation', 'GmGoldOperation', 'GmGoldOperationReview')
LEFT JOIN sys_role_menu AS existing_role_menu
  ON existing_role_menu.role_id = yumi_role.id
  AND existing_role_menu.menu_id = target_menu.id
WHERE LOWER(yumi_user.login_name) = 'yumiadmin'
  AND existing_role_menu.id IS NULL;