-- 1.1.init-poa-service.sql

-- 将以下 sql 生成的 insert 语句，在 developer_center 下进行执行

-- SERVICE 记录
SELECT CONCAT(
  'INSERT INTO \`TB_DEV_SERVICE\` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES',
  '(\'', ID,  '\',\'0\',\'', NAME, '\',\'', DESCRIPTION, '\',\'', ORIGIN, '\',\'','1','\');') INFO
FROM platform_openapi.SERVICE
order by ID
;

/*
DELETE FROM TB_DEV_SERVICE;

INSERT INTO `TB_DEV_SERVICE` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES('admincenter','0','管理中心','管理中心','http://admin-center-poa-svc.admin-center.svc.cluster.local:8080','1');
INSERT INTO `TB_DEV_SERVICE` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES('authz','0','授权服务','授权服务','http://user-authorization-poa-svc.user-authorization-service.svc.cluster.local:8080','1');
INSERT INTO `TB_DEV_SERVICE` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES('communicate','0','通信服务','通信服务','http://communicate-center-poa-svc.communicate-center.svc.cluster.local:8080','1');
INSERT INTO `TB_DEV_SERVICE` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES('echo','0','echo','echo server','http://echo.platform.nwpu.edu.cn','1');
INSERT INTO `TB_DEV_SERVICE` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES('messagecenter','0','消息服务','消息服务','http://message-service.paas.nwpu.edu.cn/center/api','1');
INSERT INTO `TB_DEV_SERVICE` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES('user','0','用户服务','用户服务','http://user-data-service-poa-svc.user-data-service.svc.cluster.local:8080','1');
INSERT INTO `TB_DEV_SERVICE` (ID,DELETED, NAME, DESCRIPTION, ORIGIN, SYSTEM_ID) VALUES('userThirdparty','0','用户服务（第三方应用）','用户服务（第三方应用）','http://user-data-service-poa-svc.user-data-service.svc.cluster.local:8080','1');
*/


-- OAS 文档 记录
SELECT CONCAT(
  'INSERT INTO \`TB_DEV_SERVICE_SPEC\` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES',
  '(\'', SERVICE_ID,'_',EDIT_VERSION,  '\',\'0\',\'', SERVICE_ID, '\',\'', API_VERSION, '\',\'', EDIT_VERSION, '\',\'','','\', 0, 1, 1, 0);') as INSERT_SQL
FROM platform_openapi.API_SPEC
order by SERVICE_ID, API_VERSION
;

/*
INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('admincenter_1618843667044','0','admincenter','v1','1618843667044','', 0, 1, 1, 0);
INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('authz_1618847916306','0','authz','v1','1618847916306','', 0, 1, 1, 0);
INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('communicate_1609773934184','0','communicate','v1','1609773934184','', 0, 1, 1, 0);
INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('echo_1575287118791','0','echo','v1','1575287118791','', 0, 1, 1, 0);
INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('echo_1575432281641','0','echo','v1','1575432281641','', 0, 1, 1, 0);
INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('echo_1575524533071','0','echo','v1','1575524533071','', 0, 1, 1, 0);
INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('messagecenter_1615306817363','0','messagecenter','v1','1615306817363','', 0, 1, 1, 0);
INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('messagecenter_1615364096757','0','messagecenter','v1','1615364096757','', 0, 1, 1, 0);
INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('user_1618846743727','0','user','v1','1618846743727','', 0, 1, 1, 0);
INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('userThirdparty_1618846743994','0','userThirdparty','v1','1618846743994','', 0, 1, 1, 0);
INSERT INTO `TB_DEV_SERVICE_SPEC` (ID,DELETED, SERVICE_ID, API_VERSION, EDIT_VERSION, DESCRIPTION, SCOPE_COUNT, PUBLISHED, ENABLED, USE_STATUS) VALUES('userTransout_1618846744130','0','userTransout','v1','1618846744130','', 0, 1, 1, 0);
*/


-- Scope 记录
SELECT CONCAT(
  'INSERT INTO \`TB_DEV_SERVICE_SCOPE\` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES',
  '(\'', SERVICE_ID,'_',SCOPE,  '\',\'0\',\'', SERVICE_ID, '\',\'', API_VERSION, '\',\'', SCOPE, '\',\'','','\', 1, 1);') as INSERT_SQL
from (
  select SERVICE_ID, API_VERSION, EDIT_VERSION, SCOPE
  from platform_openapi.API
  where EDIT_VERSION in
  (
    select max(EDIT_VERSION) as EDIT_VERSION
    from platform_openapi.API
    group by SERVICE_ID, API_VERSION, SCOPE
  )
  group by SERVICE_ID, API_VERSION, EDIT_VERSION, SCOPE
) API_TMP
order by SERVICE_ID, API_VERSION, EDIT_VERSION, SCOPE
;

/*
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('admincenter:v1:readAdminCenterPermission','0','admincenter','v1','admincenter:v1:readAdminCenterPermission','读取权限', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('admincenter:v1:readMenu','0','admincenter','v1','admincenter:v1:readMenu','读取后台菜单', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('authz:v1:readRole','0','authz','v1','authz:v1:readRole','读取角色', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('communicate:v1:communicationCheck','0','communicate','v1','communicate:v1:communicationCheck','检查', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('communicate:v1:communicationSend','0','communicate','v1','communicate:v1:communicationSend','发送', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('echo:v1:delete','0','echo','v1','echo:v1:delete','DELETE请求', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('echo:v1:get','0','echo','v1','echo:v1:get','GET请求', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('echo:v1:post','0','echo','v1','echo:v1:post','POST请求', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('echo:v1:put','0','echo','v1','echo:v1:put','PUT请求', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('messagecenter:v1:readMessage','0','messagecenter','v1','messagecenter:v1:readMessage','查询消息', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('messagecenter:v1:sendMessage','0','messagecenter','v1','messagecenter:v1:sendMessage','发送消息', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('messagecenter:v1:writeMessage','0','messagecenter','v1','messagecenter:v1:writeMessage','修改消息', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('user:v1:readGroup','0','user','v1','user:v1:readGroup','读取用户组', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('user:v1:readLabel','0','user','v1','user:v1:readLabel','读取用户标签', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('user:v1:readOrganization','0','user','v1','user:v1:readOrganization','读取组织机构', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('user:v1:readPost','0','user','v1','user:v1:readPost','读取岗位', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('user:v1:readUser','0','user','v1','user:v1:readUser','读取用户', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('user:v1:readUserSecret','0','user','v1','user:v1:readUserSecret','读取用户私密信息', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('user:v1:writePost','0','user','v1','user:v1:writePost','写入岗位', 1, 1);
INSERT INTO `TB_DEV_SERVICE_SCOPE` (ID, DELETED, SERVICE_ID, API_VERSION, NAME, DESCRIPTION, PUBLISHED, ENABLED) VALUES('userThirdparty:v1:adminAppGroupWrite','0','userThirdparty','v1','userThirdparty:v1:adminAppGroupWrite','写入应用的用户组（普通）', 1, 1);
*/


-- 统计 各 SERVICE 的 Scope 数，更新 TB_DEV_SERVICE_SPEC 的 SCOPE_COUNT 字段
UPDATE TB_DEV_SERVICE_SPEC sp, (
  SELECT SERVICE_ID, API_VERSION, count(1) as SCOPE_COUNT
  FROM TB_DEV_SERVICE_SCOPE
  GROUP BY SERVICE_ID, API_VERSION
) sc
SET sp.SCOPE_COUNT=sc.SCOPE_COUNT
WHERE sp.SERVICE_ID=sc.SERVICE_ID 
  AND sp.API_VERSION=sc.API_VERSION
;