economic_crime/docs/20261015_alarm_audit_demo_accounts.sql

-- 审核监督三人组演示账号
-- 市局审核法制员 / 公职律师 新开六位警号；经侦支队审核员沿用已有账号章成（zhangcheng）
-- 密码 123456（Argon2id 与系统一致）

UPDATE t_role
SET role_name = '市局审核法制员',
    remark = '审核监督-法制席位',
    update_time = NOW()
WHERE role_code = 'alarm_audit_legal';

UPDATE t_role
SET role_name = '公职律师',
    remark = '审核监督-公职律师席位',
    update_time = NOW()
WHERE role_code = 'alarm_audit_lawyer';

UPDATE t_role
SET role_name = '经侦支队审核员',
    remark = '审核监督-经侦席位（演示：章成）',
    update_time = NOW()
WHERE role_code = 'alarm_audit_jz';

INSERT INTO t_role (role_name, role_code, remark, create_time, update_time)
SELECT '市局审核法制员', 'alarm_audit_legal', '审核监督-法制席位', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM t_role WHERE role_code = 'alarm_audit_legal');

INSERT INTO t_role (role_name, role_code, remark, create_time, update_time)
SELECT '公职律师', 'alarm_audit_lawyer', '审核监督-公职律师席位', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM t_role WHERE role_code = 'alarm_audit_lawyer');

INSERT INTO t_role (role_name, role_code, remark, create_time, update_time)
SELECT '经侦支队审核员', 'alarm_audit_jz', '审核监督-经侦席位（演示：章成）', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM t_role WHERE role_code = 'alarm_audit_jz');

-- 审核组仅开「接报案中心 → 审核监督」及功能点
INSERT INTO t_role_menu (role_id, menu_id, create_time, update_time)
SELECT r.role_id, m.menu_id, NOW(), NOW()
FROM t_role r
CROSS JOIN t_menu m
WHERE r.role_code IN ('alarm_audit_legal', 'alarm_audit_lawyer', 'alarm_audit_jz')
  AND m.deleted_flag = 0
  AND (
    (m.menu_name = '接报案中心' AND m.menu_type = 1)
    OR m.path = '/business/alarm/audit-list'
    OR m.menu_id IN (SELECT menu_id FROM t_menu WHERE path = '/business/alarm/audit-list' AND deleted_flag = 0)
    OR m.parent_id IN (SELECT menu_id FROM t_menu WHERE path = '/business/alarm/audit-list' AND deleted_flag = 0)
  )
  AND NOT EXISTS (
      SELECT 1 FROM t_role_menu x WHERE x.role_id = r.role_id AND x.menu_id = m.menu_id
  );

INSERT INTO t_employee (
    login_name, login_pwd, actual_name, gender, phone, department_id, email,
    disabled_flag, deleted_flag, administrator_flag, remark, create_time, update_time
)
SELECT v.login_name,
       '$argon2id$v=19$m=16384,t=2,p=1$e/hqRAZYCYHydMS3SPo7yA$5hdCxLG7q+Jtf6KLJHVg/yb0I8LZrPuKUF66jLq+Drc',
       v.actual_name, v.gender, v.phone,
       COALESCE((SELECT department_id FROM t_department d WHERE d.department_id = v.dept_id), 1),
       CONCAT(v.login_name, '@tl-police.cn'),
       0, 0, 0, v.remark, NOW(), NOW()
FROM (VALUES
    ('340704', '孙磊', 1, '13834070401', 118, '市局审核法制员'),
    ('340705', '林静', 0, '13834070501', 118, '公职律师')
) AS v(login_name, actual_name, gender, phone, dept_id, remark)
WHERE NOT EXISTS (
    SELECT 1 FROM t_employee e WHERE e.login_name = v.login_name AND e.deleted_flag IN (0, FALSE)
);

-- 席位一人一岗：解绑旧岗号，避免同席位多人抢票
DELETE FROM t_role_employee re
USING t_role r, t_employee e
WHERE re.role_id = r.role_id
  AND re.employee_id = e.employee_id
  AND r.role_code IN ('alarm_audit_legal', 'alarm_audit_lawyer', 'alarm_audit_jz')
  AND e.login_name IN ('sjjbazxfzg', 'sjjbazxgzlvg', 'sjjbazxjzg');

-- 章成去掉系统演示 cto，保留作废主任 + 经侦审核员
DELETE FROM t_role_employee re
USING t_role r, t_employee e
WHERE re.role_id = r.role_id
  AND re.employee_id = e.employee_id
  AND e.login_name = 'zhangcheng'
  AND r.role_code = 'cto';

INSERT INTO t_role_employee (role_id, employee_id, create_time, update_time)
SELECT r.role_id, e.employee_id, NOW(), NOW()
FROM (VALUES
    ('340704', 'alarm_audit_legal'),
    ('340705', 'alarm_audit_lawyer'),
    ('zhangcheng', 'alarm_audit_jz')
) AS v(login_name, role_code)
JOIN t_employee e ON e.login_name = v.login_name AND e.deleted_flag IN (0, FALSE)
JOIN t_role r ON r.role_code = v.role_code
WHERE NOT EXISTS (
    SELECT 1 FROM t_role_employee x WHERE x.role_id = r.role_id AND x.employee_id = e.employee_id
);

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_class WHERE relkind = 'S' AND relname = 't_employee_employee_id_seq') THEN
        PERFORM setval('t_employee_employee_id_seq', (SELECT COALESCE(MAX(employee_id), 1) FROM t_employee));
    END IF;
    IF EXISTS (SELECT 1 FROM pg_class WHERE relkind = 'S' AND relname = 't_role_role_id_seq') THEN
        PERFORM setval('t_role_role_id_seq', (SELECT COALESCE(MAX(role_id), 1) FROM t_role));
    END IF;
END $$;