-- 为已拥有采集站权限的角色，自动补授媒体/执法记录仪权限
-- 执行后用户需重新登录以刷新权限缓存

INSERT INTO `SYSTEM_ROLE_PERMISSION`(`ROLE_ID`, `PERMISSION_ID`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `DELETED`)
SELECT rp.`ROLE_ID`, p_new.`ID`, 1, CURRENT_TIMESTAMP, NULL, NULL, 0
FROM `SYSTEM_ROLE_PERMISSION` rp
INNER JOIN `SYSTEM_PERMISSION` p_old ON p_old.`ID` = rp.`PERMISSION_ID` AND p_old.`CODE` = 'business:collectionStation:query' AND p_old.`DELETED` = 0
INNER JOIN `SYSTEM_PERMISSION` p_new ON p_new.`CODE` = 'business:collectionMedia:query' AND p_new.`DELETED` = 0
WHERE rp.`DELETED` = 0
AND NOT EXISTS (
  SELECT 1 FROM `SYSTEM_ROLE_PERMISSION` x
  WHERE x.`ROLE_ID` = rp.`ROLE_ID` AND x.`PERMISSION_ID` = p_new.`ID` AND x.`DELETED` = 0
);

INSERT INTO `SYSTEM_ROLE_PERMISSION`(`ROLE_ID`, `PERMISSION_ID`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `DELETED`)
SELECT rp.`ROLE_ID`, p_new.`ID`, 1, CURRENT_TIMESTAMP, NULL, NULL, 0
FROM `SYSTEM_ROLE_PERMISSION` rp
INNER JOIN `SYSTEM_PERMISSION` p_old ON p_old.`ID` = rp.`PERMISSION_ID` AND p_old.`CODE` = 'business:collectionStation:sync' AND p_old.`DELETED` = 0
INNER JOIN `SYSTEM_PERMISSION` p_new ON p_new.`CODE` = 'business:collectionMedia:sync' AND p_new.`DELETED` = 0
WHERE rp.`DELETED` = 0
AND NOT EXISTS (
  SELECT 1 FROM `SYSTEM_ROLE_PERMISSION` x
  WHERE x.`ROLE_ID` = rp.`ROLE_ID` AND x.`PERMISSION_ID` = p_new.`ID` AND x.`DELETED` = 0
);

INSERT INTO `SYSTEM_ROLE_PERMISSION`(`ROLE_ID`, `PERMISSION_ID`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `DELETED`)
SELECT rp.`ROLE_ID`, p_new.`ID`, 1, CURRENT_TIMESTAMP, NULL, NULL, 0
FROM `SYSTEM_ROLE_PERMISSION` rp
INNER JOIN `SYSTEM_PERMISSION` p_old ON p_old.`ID` = rp.`PERMISSION_ID` AND p_old.`CODE` = 'business:collectionStation:sync' AND p_old.`DELETED` = 0
INNER JOIN `SYSTEM_PERMISSION` p_new ON p_new.`CODE` = 'business:collectionMedia:download' AND p_new.`DELETED` = 0
WHERE rp.`DELETED` = 0
AND NOT EXISTS (
  SELECT 1 FROM `SYSTEM_ROLE_PERMISSION` x
  WHERE x.`ROLE_ID` = rp.`ROLE_ID` AND x.`PERMISSION_ID` = p_new.`ID` AND x.`DELETED` = 0
);

INSERT INTO `SYSTEM_ROLE_PERMISSION`(`ROLE_ID`, `PERMISSION_ID`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `DELETED`)
SELECT rp.`ROLE_ID`, p_new.`ID`, 1, CURRENT_TIMESTAMP, NULL, NULL, 0
FROM `SYSTEM_ROLE_PERMISSION` rp
INNER JOIN `SYSTEM_PERMISSION` p_old ON p_old.`ID` = rp.`PERMISSION_ID` AND p_old.`CODE` = 'business:collectionStation:query' AND p_old.`DELETED` = 0
INNER JOIN `SYSTEM_PERMISSION` p_new ON p_new.`CODE` = 'business:collectionDockDevice:query' AND p_new.`DELETED` = 0
WHERE rp.`DELETED` = 0
AND NOT EXISTS (
  SELECT 1 FROM `SYSTEM_ROLE_PERMISSION` x
  WHERE x.`ROLE_ID` = rp.`ROLE_ID` AND x.`PERMISSION_ID` = p_new.`ID` AND x.`DELETED` = 0
);