water/server/plugin/saiadmin/db/20260902_add_user_complaint_feedback.sql

-- 用户管理：微信支付投诉与用户反馈。可重复执行。
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `sa_user_feedback` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '反馈ID',
    `agent_id`       BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '归属管理代理ID',
    `user_id`        BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '租借用户ID',
    `identity`       VARCHAR(32) NOT NULL DEFAULT '用户' COMMENT '提交人身份快照',
    `type`           TINYINT UNSIGNED NOT NULL DEFAULT 3 COMMENT '类型:1订单问题 2系统问题 3其他问题',
    `order_id`       BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '关联订单ID',
    `merchant_id`    BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '关联商户ID',
    `merchant_name`  VARCHAR(128) NOT NULL DEFAULT '' COMMENT '商户名称快照',
    `device_id`      BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '关联设备ID',
    `device_sn`      VARCHAR(128) NOT NULL DEFAULT '' COMMENT '设备SN快照',
    `device_type`    INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '设备类型快照',
    `nickname`       VARCHAR(128) NOT NULL DEFAULT '' COMMENT '用户昵称快照',
    `contact_phone`  VARCHAR(32) NOT NULL DEFAULT '' COMMENT '联系电话',
    `content`        TEXT NOT NULL COMMENT '反馈内容',
    `images`         JSON NULL COMMENT '反馈截图URL数组',
    `status`         TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态:0未处理 1处理中 2已处理',
    `remark`         VARCHAR(500) NOT NULL DEFAULT '' COMMENT '后台处理备注',
    `handled_by`     BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '最后处理管理员ID',
    `handled_name`   VARCHAR(64) NOT NULL DEFAULT '' COMMENT '最后处理管理员名',
    `handled_at`     DATETIME NULL COMMENT '处理完成时间',
    `created_by`     BIGINT UNSIGNED NULL COMMENT '创建者',
    `updated_by`     BIGINT UNSIGNED NULL COMMENT '更新者',
    `create_time`    DATETIME NOT NULL COMMENT '反馈时间',
    `update_time`    DATETIME NULL COMMENT '更新时间',
    `delete_time`    DATETIME NULL COMMENT '删除时间',
    PRIMARY KEY (`id`),
    KEY `idx_feedback_agent_status_time` (`agent_id`, `status`, `create_time`),
    KEY `idx_feedback_user` (`user_id`, `create_time`),
    KEY `idx_feedback_order` (`order_id`),
    KEY `idx_feedback_device_sn` (`device_sn`),
    KEY `idx_feedback_phone` (`contact_phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户反馈';

CREATE TABLE IF NOT EXISTS `sa_user_feedback_reply` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '回复ID',
    `feedback_id`  BIGINT UNSIGNED NOT NULL COMMENT '反馈ID',
    `author_type`  TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '作者类型:0管理员 1用户 2代理商',
    `author_id`    BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '作者ID',
    `author_name`  VARCHAR(128) NOT NULL DEFAULT '' COMMENT '作者名称快照',
    `content`      TEXT NOT NULL COMMENT '回复内容',
    `images`       JSON NULL COMMENT '回复图片URL数组',
    `created_by`   BIGINT UNSIGNED NULL COMMENT '创建者',
    `updated_by`   BIGINT UNSIGNED NULL COMMENT '更新者',
    `create_time`  DATETIME NOT NULL COMMENT '回复时间',
    `update_time`  DATETIME NULL COMMENT '更新时间',
    `delete_time`  DATETIME NULL COMMENT '删除时间',
    PRIMARY KEY (`id`),
    KEY `idx_feedback_reply_feedback` (`feedback_id`, `id`),
    KEY `idx_feedback_reply_author` (`author_type`, `author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户反馈回复';

-- 页面菜单本身承载列表接口权限。
UPDATE `sa_system_menu`
SET `slug` = 'core:user:complaint:index', `method` = 'GET',
    `remark` = '实时查询微信支付商户投诉', `update_time` = NOW()
WHERE `code` = 'UserComplaints' AND `delete_time` IS NULL;

UPDATE `sa_system_menu`
SET `slug` = 'core:user:feedback:index', `method` = 'GET',
    `remark` = '查询、回复和处理用户反馈', `update_time` = NOW()
WHERE `code` = 'UserFeedback' AND `delete_time` IS NULL;

-- 投诉子权限。
INSERT INTO `sa_system_menu` (`parent_id`,`name`,`code`,`slug`,`type`,`path`,`component`,`method`,`icon`,`sort`,`link_url`,`is_iframe`,`is_keep_alive`,`is_hidden`,`is_fixed_tab`,`is_full_page`,`generate_id`,`generate_key`,`status`,`remark`,`created_by`,`updated_by`,`create_time`,`update_time`,`delete_time`)
SELECT p.id, perms.name, perms.code, perms.slug, 3, NULL, NULL, perms.method, NULL, perms.sort,
       NULL,2,2,2,2,2,0,NULL,1,perms.remark,1,1,NOW(),NOW(),NULL
FROM `sa_system_menu` p
CROSS JOIN (
    SELECT '投诉支付配置选项' name, 'UserComplaintConfigs' code, 'core:user:complaint:configs' slug, 'GET' method, 100 sort, '读取当前账号可用的微信支付配置' remark
    UNION ALL SELECT '用户投诉详情', 'UserComplaintRead', 'core:user:complaint:read', 'GET', 90, '查询微信支付投诉详情'
    UNION ALL SELECT '用户投诉协商记录', 'UserComplaintHistory', 'core:user:complaint:history', 'GET', 80, '查询微信支付投诉协商记录'
    UNION ALL SELECT '回复用户投诉', 'UserComplaintReply', 'core:user:complaint:reply', 'POST', 70, '通过微信支付商户投诉接口回复用户'
    UNION ALL SELECT '完结用户投诉', 'UserComplaintComplete', 'core:user:complaint:complete', 'POST', 60, '通过微信支付商户投诉接口完结投诉'
) perms
WHERE p.code = 'UserComplaints' AND p.delete_time IS NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` m WHERE m.slug = perms.slug AND m.delete_time IS NULL);

-- 反馈子权限。
INSERT INTO `sa_system_menu` (`parent_id`,`name`,`code`,`slug`,`type`,`path`,`component`,`method`,`icon`,`sort`,`link_url`,`is_iframe`,`is_keep_alive`,`is_hidden`,`is_fixed_tab`,`is_full_page`,`generate_id`,`generate_key`,`status`,`remark`,`created_by`,`updated_by`,`create_time`,`update_time`,`delete_time`)
SELECT p.id, perms.name, perms.code, perms.slug, 3, NULL, NULL, perms.method, NULL, perms.sort,
       NULL,2,2,2,2,2,0,NULL,1,perms.remark,1,1,NOW(),NOW(),NULL
FROM `sa_system_menu` p
CROSS JOIN (
    SELECT '用户反馈详情' name, 'UserFeedbackRead' code, 'core:user:feedback:read' slug, 'GET' method, 100 sort, '查询反馈和完整回复记录' remark
    UNION ALL SELECT '更新用户反馈状态', 'UserFeedbackUpdate', 'core:user:feedback:update', 'PUT', 90, '更新反馈处理状态和备注'
    UNION ALL SELECT '回复用户反馈', 'UserFeedbackReply', 'core:user:feedback:reply', 'POST', 80, '新增后台回复并进入处理流程'
    UNION ALL SELECT '导出用户反馈', 'UserFeedbackExport', 'core:user:feedback:export', 'POST', 70, '按当前筛选条件导出反馈'
) perms
WHERE p.code = 'UserFeedback' AND p.delete_time IS NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` m WHERE m.slug = perms.slug AND m.delete_time IS NULL);

-- 已有页面权限的角色自动继承对应操作权限。
INSERT INTO `sa_system_role_menu` (`role_id`, `menu_id`)
SELECT DISTINCT source_role.role_id, target.id
FROM `sa_system_role_menu` source_role
INNER JOIN `sa_system_menu` page ON page.id = source_role.menu_id
    AND page.code IN ('UserComplaints', 'UserFeedback') AND page.delete_time IS NULL
INNER JOIN `sa_system_menu` target ON target.parent_id = page.id
    AND target.type = 3 AND target.delete_time IS NULL
LEFT JOIN `sa_system_role_menu` existing ON existing.role_id = source_role.role_id AND existing.menu_id = target.id
WHERE existing.id IS NULL;