-- =====================================================================
-- 为「四川智和诚」「湖北威马」商户创建 qqflow 流程管理员账号
--
-- 执行库：qqflow 审批引擎库（qqflowengine）
-- 执行顺序：先执行本脚本，再在 wl-scrm 库执行
--           wl-scrm-center/sql/20261028_wlscrm_bind_zhc_wm_qqflow_accounts.sql
--
-- 账号约定（两个脚本必须一致）：
--   四川智和诚  username = zhc_flow，明文密码 = zhc123456
--   湖北威马    username = wm_flow ，明文密码 = wm123456
--
-- qqflow 密码使用 BCrypt 存储，哈希由下列命令生成（勿替换成明文）：
--   htpasswd -bnBC 10 "" 'zhc123456' | tr -d ':\n'
--   htpasswd -bnBC 10 "" 'wm123456'  | tr -d ':\n'
--
-- 说明：
--   · qqflow 前端菜单看 sys_user.employee_type，必须为 flow_manager；
--   · qqflow 后端接口 @PreAuthorize 看 sys_user_role → sys_role.role_code，
--     必须同时绑定 flow_manager 角色（本脚本会补齐）；
--   · 脚本幂等：重复执行会重置账号密码并补齐角色，不会重复插入。
-- =====================================================================

-- 执行前确认当前库：必须是 qqflowengine。
SELECT DATABASE() AS current_database;

-- -------------------------------------------------------------------
-- 0) 公共准备：确保 flow_manager 角色存在且启用
-- -------------------------------------------------------------------
INSERT INTO sys_role (role_code, role_name, role_scope, parent_id, dept_id, status)
SELECT 'flow_manager', '流程管理员', 'tenant', 0, 0, 1
WHERE NOT EXISTS (
    SELECT 1 FROM sys_role
    WHERE role_code COLLATE utf8mb4_general_ci = 'flow_manager' COLLATE utf8mb4_general_ci
);

UPDATE sys_role
SET status = 1
WHERE role_code COLLATE utf8mb4_general_ci = 'flow_manager' COLLATE utf8mb4_general_ci;

SET @flow_manager_role_id = (
    SELECT id FROM sys_role
    WHERE role_code COLLATE utf8mb4_general_ci = 'flow_manager' COLLATE utf8mb4_general_ci
    LIMIT 1
);

-- =====================================================================
-- 1) 四川智和诚（tenancy_id = 119304923710091）
-- =====================================================================
SET @username = 'zhc_flow';
SET @password_hash = '$2y$10$PffgaT28otYLtLyo5nftKOoQW8Rl9PekZKJDGVXnb9klH1LeoUcUa';
SET @real_name = '四川智和诚流程管理员';

-- 1.0 执行前检查：用户名是否已被占用
--     有结果时先确认是不是本次要重置的账号；如果是别人在用的账号，请换一个 username。
SELECT id, username, real_name, employee_type, status
FROM sys_user
WHERE username COLLATE utf8mb4_general_ci = @username COLLATE utf8mb4_general_ci;

-- 1.1 更新已有账号（仅限本脚本管理的账号）
--     只有 real_name 或 employee_type 对得上才更新，避免误改同名但无关的账号
UPDATE sys_user
SET password = @password_hash,
    real_name = @real_name,
    employee_type = 'flow_manager',
    status = 0,
    update_time = NOW()
WHERE username COLLATE utf8mb4_general_ci = @username COLLATE utf8mb4_general_ci
  AND (real_name = @real_name OR employee_type = 'flow_manager');

-- 1.2 不存在则创建
--     若 username 已被无关账号占用，这里会因唯一键失败而中断（第 1.0 步会先提示）
INSERT INTO sys_user
    (username, password, real_name, dept_id, employee_type, status, wecom_remind_enabled)
SELECT @username, @password_hash, @real_name, 0, 'flow_manager', 0, 0
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM sys_user
    WHERE username COLLATE utf8mb4_general_ci = @username COLLATE utf8mb4_general_ci
);

-- 1.3 绑定 flow_manager 角色（后端接口鉴权使用）
SET @user_id = (
    SELECT id FROM sys_user
    WHERE username COLLATE utf8mb4_general_ci = @username COLLATE utf8mb4_general_ci
    LIMIT 1
);

INSERT INTO sys_user_role (user_id, role_id)
SELECT @user_id, @flow_manager_role_id
WHERE @user_id IS NOT NULL
  AND @flow_manager_role_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM sys_user_role
      WHERE user_id = @user_id
        AND role_id = @flow_manager_role_id
  );

-- =====================================================================
-- 2) 湖北威马（tenancy_id = 119306205010001）
-- =====================================================================
SET @username = 'wm_flow';
SET @password_hash = '$2y$10$9EIvGo/0AFwdrPzglk9OBuLXRzyfowiI4nzm8Gx1euh1ZDkN05vyq';
SET @real_name = '湖北威马流程管理员';

-- 2.0 执行前检查：用户名是否已被占用
SELECT id, username, real_name, employee_type, status
FROM sys_user
WHERE username COLLATE utf8mb4_general_ci = @username COLLATE utf8mb4_general_ci;

-- 2.1 更新已有账号（仅限本脚本管理的账号）
UPDATE sys_user
SET password = @password_hash,
    real_name = @real_name,
    employee_type = 'flow_manager',
    status = 0,
    update_time = NOW()
WHERE username COLLATE utf8mb4_general_ci = @username COLLATE utf8mb4_general_ci
  AND (real_name = @real_name OR employee_type = 'flow_manager');

-- 2.2 不存在则创建
INSERT INTO sys_user
    (username, password, real_name, dept_id, employee_type, status, wecom_remind_enabled)
SELECT @username, @password_hash, @real_name, 0, 'flow_manager', 0, 0
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM sys_user
    WHERE username COLLATE utf8mb4_general_ci = @username COLLATE utf8mb4_general_ci
);

-- 2.3 绑定 flow_manager 角色
SET @user_id = (
    SELECT id FROM sys_user
    WHERE username COLLATE utf8mb4_general_ci = @username COLLATE utf8mb4_general_ci
    LIMIT 1
);

INSERT INTO sys_user_role (user_id, role_id)
SELECT @user_id, @flow_manager_role_id
WHERE @user_id IS NOT NULL
  AND @flow_manager_role_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM sys_user_role
      WHERE user_id = @user_id
        AND role_id = @flow_manager_role_id
  );

-- =====================================================================
-- 3) 校验：应返回 2 行，employee_type 均为 flow_manager，
--    role_code 均为 flow_manager，pwd_len=60、pwd_prefix=$2y$
-- =====================================================================
SELECT u.id            AS user_id,
       u.username,
       u.employee_type,
       u.status        AS user_status,
       CHAR_LENGTH(u.password) AS pwd_len,
       LEFT(u.password, 4)     AS pwd_prefix,
       r.id            AS role_id,
       r.role_code,
       r.role_name,
       r.status        AS role_status
FROM sys_user u
LEFT JOIN sys_user_role ur ON ur.user_id = u.id
LEFT JOIN sys_role r ON r.id = ur.role_id
WHERE u.username COLLATE utf8mb4_general_ci IN ('zhc_flow', 'wm_flow')
ORDER BY u.username;