/*
 change.sql 说明
 1.数据库相关修改添加到对应的 change.sql 文件，无需调整其他 sql  文件
 2.sql 语句验证正确后再提交
*/

-- 20221206 jjx 同步mysql脚本
ALTER TABLE PORTAL_SYS_COLUMN ADD STYLE_ CLOB;
COMMENT ON COLUMN PORTAL_SYS_COLUMN.STYLE_ IS 'CSS样式';

ALTER TABLE PORTAL_BIZ_SYSTEM ADD TOKEN_TYPE_ INT DEFAULT 0;
COMMENT ON COLUMN PORTAL_BIZ_SYSTEM.TOKEN_TYPE_ IS '票据入参方式，0-头部参数；1-url参数';

ALTER TABLE PORTAL_BIZ_SYSTEM ADD TOKEN_KEY_ NVARCHAR2(128);
COMMENT ON COLUMN PORTAL_BIZ_SYSTEM.TOKEN_KEY_ IS '票据入参KEY';

ALTER TABLE PORTAL_BIZ_SYSTEM ADD TOKEN_EXPIRE_ INT DEFAULT 3600;
COMMENT ON COLUMN PORTAL_BIZ_SYSTEM.TOKEN_EXPIRE_ IS '票据过期时间，单位秒';

ALTER TABLE PORTAL_BIZ_SYSTEM ADD TOKEN_API_INFO CLOB;
COMMENT ON COLUMN PORTAL_BIZ_SYSTEM.TOKEN_API_INFO IS '认证接口信息Json';

ALTER TABLE PORTAL_INTERFACE_MANAGER ADD SOURCE_TYPE_ INT DEFAULT 0;
COMMENT ON COLUMN PORTAL_INTERFACE_MANAGER.SOURCE_TYPE_ IS '接口来源，0-连接；1-服务';

ALTER TABLE PORTAL_CONNECTION_MANAGER ADD INIT_FLAG_ INT DEFAULT 0;
COMMENT ON COLUMN PORTAL_CONNECTION_MANAGER.INIT_FLAG_ IS '是否初始化数据，0或null-否；1-是';

ALTER TABLE FORM_CUSTOM_QUERY ADD API_ALIAS_ NVARCHAR2(64);
COMMENT ON COLUMN FORM_CUSTOM_QUERY.API_ALIAS_ IS '集成管理接口别名';

ALTER TABLE FORM_CUSTOM_DIALOG ADD  API_ALIAS_ NVARCHAR2(64);
COMMENT ON COLUMN FORM_CUSTOM_DIALOG.API_ALIAS_ IS '集成管理接口别名';

-- 2022-12-13 JJX BPM_TASK表中添加字段
ALTER TABLE BPM_TASK ADD TARGET_NODE_ NVARCHAR2(255);
COMMENT ON COLUMN BPM_TASK.TARGET_NODE_ IS '回到本节点';

-- 2022-12-14 liangjc 迁移流程推演-增加表
create table BPM_NODE_SETTING
(
    ID_             NVARCHAR2(255)  not null    primary key,
    CREATE_BY_      NVARCHAR2(64)   default NULL,
    CREATE_TIME_    DATE,
    CREATE_ORG_ID_  NVARCHAR2(64)  default NULL,
    UPDATE_BY_      NVARCHAR2(64)  default NULL,
    UPDATE_TIME_    DATE           default NULL,
    TENANT_ID_      NVARCHAR2(64)  default NULL,
    NODE_ID_        NVARCHAR2(64)  default NULL,
    DEF_ID          NVARCHAR2(64)  default NULL,
    FORECAST_       NUMBER         default NULL
);
comment on table BPM_NODE_SETTING is '流程定义节点设置表';
comment on column BPM_NODE_SETTING.ID_ is '主键';
comment on column BPM_NODE_SETTING.CREATE_BY_ is '创建人ID';
comment on column BPM_NODE_SETTING.CREATE_TIME_ is '创建时间';
comment on column BPM_NODE_SETTING.CREATE_ORG_ID_ is '创建者所属组织ID';
comment on column BPM_NODE_SETTING.UPDATE_BY_ is '更新人ID';
comment on column BPM_NODE_SETTING.UPDATE_TIME_ is '更新时间';
comment on column BPM_NODE_SETTING.TENANT_ID_ is '租户ID';
comment on column BPM_NODE_SETTING.NODE_ID_ is '节点id';
comment on column BPM_NODE_SETTING.DEF_ID is '流程定义id';
comment on column BPM_NODE_SETTING.FORECAST_ is '审批预测值(1为同意,2为反对,默认为1)';

-- 2022-12-14 liangjc 审批历史表增加字段
alter table BPM_CHECK_OPINION add VARS_ CLOB;
comment on column BPM_CHECK_OPINION.VARS_ is '流程变量';

-- url菜单增加配置认证接口 update by ouyanggaolong20221223
alter table PORTAL_SYS_MENU add TOKEN_ENABLED_ int default 0;
comment on column PORTAL_SYS_MENU.TOKEN_ENABLED_ is '是否追加token，0-否；1-是';

alter table PORTAL_SYS_MENU add TOKEN_TYPE_ int default 0;
comment on column PORTAL_SYS_MENU.TOKEN_TYPE_ is '获取模式，0-当前用户；1-接口获取';

alter table PORTAL_SYS_MENU add TOKEN_KEY_ NVARCHAR2(128);
comment on column PORTAL_SYS_MENU.TOKEN_KEY_ is '票据入参KEY';

alter table PORTAL_SYS_MENU add TOKEN_API_ CLOB;
comment on column PORTAL_SYS_MENU.TOKEN_API_ is '认证接口信息Json，base64编码';

-- 2022-11-30 liangjc 用户自定义待办事项表增加收藏列表排序 和 租户id字段
alter table PORTAL_CUSTOM_TODO add MARK_SN_ int;
comment on column PORTAL_CUSTOM_TODO.MARK_SN_ is '收藏列表排序字段';

alter table PORTAL_CUSTOM_TODO add TENANT_ID_ NVARCHAR2(64);
comment on column PORTAL_CUSTOM_TODO.TENANT_ID_ is '租户ID';

-- 2022-12-29 修改任务名称长度
ALTER TABLE BPM_TASK MODIFY NAME_ NVARCHAR2(100);


-- 2023-1-3 jjx 迁移待办中心
alter table BPM_CHECK_OPINION add QUALFIEDS_ACCOUNT_ clob;
comment on column BPM_CHECK_OPINION.QUALFIEDS_ACCOUNT_ is '有审批资格用户帐号串';

alter table BPM_CHECK_OPINION add AUDITOR_ACCOUNT_ NVARCHAR2(255);
comment on column BPM_CHECK_OPINION.AUDITOR_ACCOUNT_ is '实际审判人';

create table BPM_FLOW_MQ_LOG
(
    ID_              NVARCHAR2(64)  not null    primary key,
    TYPE_            NVARCHAR2(40)  not null,
    TITLE_           NVARCHAR2(300),
    CONTENT_         CLOB,
    STATUS_          INT            not null,
    TRY_TIME_        INT,
    EXCEPTION_       NVARCHAR2(2000),
    LOG_TIME_        DATE,
    TARGET_          NVARCHAR2(64),
    PROC_INST_ID_    NVARCHAR2(64),
    TASK_ID_         NVARCHAR2(64),
    OWNER_ID_        NVARCHAR2(64),
    OWNER_NAME_      NVARCHAR2(64),
    TENANT_ID_       NVARCHAR2(64),
    YEARS_PARTITION_ INT
);
comment on column BPM_FLOW_MQ_LOG.ID_ is '主键';
comment on column BPM_FLOW_MQ_LOG.TYPE_ is '类型';
comment on column BPM_FLOW_MQ_LOG.TITLE_ is '标题';
comment on column BPM_FLOW_MQ_LOG.CONTENT_ is '内容';
comment on column BPM_FLOW_MQ_LOG.STATUS_ is '状态';
comment on column BPM_FLOW_MQ_LOG.TRY_TIME_ is '重试次数';
comment on column BPM_FLOW_MQ_LOG.EXCEPTION_ is '异常信息';
comment on column BPM_FLOW_MQ_LOG.LOG_TIME_ is '日志记录时间';
comment on column BPM_FLOW_MQ_LOG.TARGET_ is '日志目标类型';
comment on column BPM_FLOW_MQ_LOG.PROC_INST_ID_ is '实例ID';
comment on column BPM_FLOW_MQ_LOG.TASK_ID_ is '任务ID';
comment on column BPM_FLOW_MQ_LOG.OWNER_ID_ is '任务所属人ID';
comment on column BPM_FLOW_MQ_LOG.OWNER_NAME_ is '任务所属人姓名';
comment on column BPM_FLOW_MQ_LOG.TENANT_ID_ is '租户ID';

CREATE INDEX IDX_YEARS_PARTITION_  ON BPM_FLOW_MQ_LOG (YEARS_PARTITION_);
CREATE INDEX IDX_PROC_INST_ID_ ON BPM_FLOW_MQ_LOG (PROC_INST_ID_);
CREATE INDEX idx_bpm_flow_message_type  ON BPM_FLOW_MQ_LOG (TYPE_);

-- 2023-1-3 jjx  更新移动端待办列表配置
UPDATE PORTAL_SYS_COLUMN SET "TEMPLATE_HTML" = 'PGRpdj4KICA8ZGl2ICBjbGFzcz0idG9kby1oZWFkZXIiIHN0eWxlPSJkaXNwbGF5OmZsZXg7anVzdGlmeS1jb250ZW50OiBzcGFjZS1iZXR3ZWVuO2hlaWdodDogMS4wNjY3cmVtO2FsaWduLWl0ZW1zOiBjZW50ZXI7cGFkZGluZzowIDAuMzJyZW07Ij4KICAgIDxodC1oNCBpbmxpbmU+5oiR55qE5b6F5YqePC9odC1oND4KICAgIDxzcGFuICBjbGFzcz0iaGVhZGVyLXJpZ2h0X19idG4iICBAY2xpY2s9IiRyb3V0ZXIucHVzaCgnL3dvcmsvbXlNYXR0ZXJzJykiPgogICAgICDmm7TlpJo8aSBjbGFzcz0iZWwtaWNvbi1hcnJvdy1yaWdodCI+PC9pPgogICAgPC9zcGFuPgogIDwvZGl2PgogIDx1bCBjbGFzcz0idG9kby1jb250ZW50X191bCIgc3R5bGU9InBhZGRpbmc6MCAwLjMycmVtOyI+CiAgICA8bGkgY2xhc3M9InRvZG8tY29udGVudF9faXRlbSIgc3R5bGU9ImRpc3BsYXk6ZmxleDtmbGV4LWRpcmVjdGlvbjogY29sdW1uOyIgdi1mb3I9IihpdGVtLGluZGV4KSBpbiBjdXJyZW50U2hvd0RhdGEiIDprZXk9Iml0ZW0uaWQiPgogICAgICA8cCBzdHlsZT0iY29sb3I6IzIyMjIyMjtmb250LXNpemU6MC40cmVtO2ZvbnQtd2VpZ2h0OiBib2xkO21hcmdpbjowLjIxMzNyZW0gMDt3aGl0ZS1zcGFjZTogbm93cmFwO292ZXJmbG93OiBoaWRkZW47dGV4dC1vdmVyZmxvdzogZWxsaXBzaXM7IiBAY2xpY2s9ImdvVG9EZXRhaWwoaXRlbSkiPnt7aXRlbS5zdWJqZWN0fX08L3A+CiAgICAgIDxkaXYgY2xhc3M9ImNvbnRlbnQtaXRlbV9fYm90dG9tIiBzdHlsZT0iZGlzcGxheTpmbGV4O2p1c3RpZnktY29udGVudDogc3BhY2UtYmV0d2Vlbjtib3JkZXItYm90dG9tOjFweCBzb2xpZCAjRUVFRUVFO3BhZGRpbmctYm90dG9tOjAuMzJyZW0iIDpzdHlsZT0iaW5kZXggPT09IDQgPyAnYm9yZGVyLWJvdHRvbTpub25lJzonJyI+CiAgICAgICAgPGRpdiBjbGFzcz0iY29udGVudC1pdGVtX19ib3R0b20tbGVmdCI+CiAgICAgICAgICA8c3BhbiBzdHlsZT0iY29sb3I6Izk5OTtmb250LXNpemU6MC4zMnJlbSI+PGkgY2xhc3M9Imljb24teW9uZ2h1MSIgc3R5bGU9ImZvbnQtc2l6ZTowLjM3MzNyZW07cGFkZGluZy1yaWdodDowLjA1MzNyZW0iPjwvaT57e2l0ZW0uY3JlYXRvcn19PC9zcGFuPgogICAgICAgICAgPHNwYW4gc3R5bGU9ImNvbG9yOiM5OTk7Zm9udC1zaXplOjAuMzJyZW07cGFkZGluZy1sZWZ0OjAuNTMzM3JlbSI+PGkgY2xhc3M9Imljb24tdGltZS1jaXJjbGUiIHN0eWxlPSJmb250LXNpemU6MC4zNzMzcmVtO3BhZGRpbmctcmlnaHQ6MC4wNTMzcmVtIj48L2k+e3tmb3JtYXRlRGF0ZShpdGVtLmNyZWF0ZVRpbWUpfX08L3NwYW4+CiAgICAgICAgPC9kaXY+CiAgICAgICAgPGRpdiBzdHlsZT0iY29sb3I6IzQwOUVGRjtmb250LXNpemU6MC4zMnJlbSI+e3tmaWx0ZXJEYXRlKGl0ZW0uY3JlYXRlVGltZSl9fTwvZGl2PgogICAgICA8L2Rpdj4KICAgIDwvbGk+CiAgPC91bD4KPC9kaXY+' 
WHERE ALIAS = 'wddbmobile';

UPDATE PORTAL_SYS_COLUMN SET 
"TEMPLATE_JAVA_SCRIPT_" = 'ewogIGNvbXB1dGVkOnsKICAgIGN1cnJlbnRTaG93RGF0YSgpewogICAgICBsZXQgZGF0YSA9IFtdCiAgICAgIGlmKHRoaXMuZGF0YSYmdGhpcy5kYXRhLnJvd3MmJnRoaXMuZGF0YS5yb3dzLmxlbmd0aCl7CiAgICAgICAgZGF0YSA9IHRoaXMuZGF0YS5yb3dzLnNwbGljZSgwLDUpCiAgICAgIH0KICAgICAgcmV0dXJuIGRhdGEKICAgIH0sCiAgIAogIH0sCiAgICBtb3VudGVkKCl7CiAgICAgCiAgICB9LAogICAgIG1ldGhvZHM6IHsKICAgICAgIGdvVG9EZXRhaWwocm93KXsKICAgICAgICAgaWYgKHJvdy5zdGF0dXMgPT0gJ1NIQVJFJyB8fCByb3cuc3RhdHVzID09ICdCQUNLU0hBUkUnKSB7CiAgICAgICAgICAgIGlmIChyb3cuaWRlbnRpdHlMaXN0ICYmIHJvdy5pZGVudGl0eUxpc3QubGVuZ3RoID09IDEpIHsKICAgICAgICAgICAgICB0aGlzLiRyb3V0ZXIucHVzaCgKICAgICAgICAgICAgICAgICcvdGFzay8nICsgcm93LmlkICsgJy8nICsgcm93LmlkZW50aXR5TGlzdFswXS5pZAogICAgICAgICAgICAgICkKICAgICAgICAgICAgfSBlbHNlIHsKICAgICAgICAgICAgICBsZXQgaHRtbCA9CiAgICAgICAgICAgICAgICAnPHNlbGVjdCByZWY9JyArCiAgICAgICAgICAgICAgICBNYXRoLnJhbmRvbSgpICsKICAgICAgICAgICAgICAgICcgaWQ9ImFwcHJvdmFsTGVhZGVyU2VsZWN0IiBzdHlsZT0id2lkdGg6IDIwMHB4O2hlaWdodDogMzBweDtib3JkZXItcmFkaXVzOiA0cHg7bWFyZ2luOiAyMHB4IDcwcHg7IiA+PG9wdGlvbiB2YWx1ZT0iIj7or7fpgInmi6k8L29wdGlvbj4nCiAgICAgICAgICAgICAgcm93LmlkZW50aXR5TGlzdC5mb3JFYWNoKChpZGVudGl0eSkgPT4gewogICAgICAgICAgICAgICAgaHRtbCArPQogICAgICAgICAgICAgICAgICAnPG9wdGlvbiB2YWx1ZT0iJyArCiAgICAgICAgICAgICAgICAgIGlkZW50aXR5LmlkICsKICAgICAgICAgICAgICAgICAgJyI+JyArCiAgICAgICAgICAgICAgICAgIGlkZW50aXR5Lm5hbWUgKwogICAgICAgICAgICAgICAgICAnPC9vcHRpb24+JwogICAgICAgICAgICAgIH0pCiAgICAgICAgICAgICAgaHRtbCArPSAnPC9zZWxlY3Q+JwogICAgICAgICAgICAgIGxldCB0aGlzXyA9IHRoaXMKICAgICAgICAgICAgICB0aGlzLiRhbGVydChodG1sLCAn6K+36YCJ5oup5LiA5Liq6aKG5a+85Luj5Li65a6h5om5JywgewogICAgICAgICAgICAgICAgZGFuZ2Vyb3VzbHlVc2VIVE1MU3RyaW5nOiB0cnVlLAogICAgICAgICAgICAgICAgYmVmb3JlQ2xvc2U6IGZ1bmN0aW9uIChhY3Rpb24sIGluc3RhbmNlLCBkb25lKSB7CiAgICAgICAgICAgICAgICAgIGlmIChhY3Rpb24gPT0gJ2NvbmZpcm0nKSB7CiAgICAgICAgICAgICAgICAgICAgbGV0IHNlID0gZG9jdW1lbnQuZ2V0RWxlbWVudEJ5SWQoJ2FwcHJvdmFsTGVhZGVyU2VsZWN0JykKICAgICAgICAgICAgICAgICAgICBpZiAoc2Uuc2VsZWN0ZWRJbmRleCA9PSAwKSB7CiAgICAgICAgICAgICAgICAgICAgICBOb3RpZnkoewogICAgICAgICAgICAgICAgICAgICAgICB0eXBlOiAnd2FybmluZycsCiAgICAgICAgICAgICAgICAgICAgICAgIG1lc3NhZ2U6ICfor7fpgInmi6nku6PkuLrlrqHmibnnmoTpooblr7wnLAogICAgICAgICAgICAgICAgICAgICAgfSkKICAgICAgICAgICAgICAgICAgICB9IGVsc2UgewogICAgICAgICAgICAgICAgICAgICAgaW5zdGFuY2UuY2xvc2UoKQogICAgICAgICAgICAgICAgICAgICAgdGhpc18uJHJvdXRlci5wdXNoKAogICAgICAgICAgICAgICAgICAgICAgICAnL3Rhc2svJyArIHJvdy5pZCArICcvJyArIHNlW3NlLnNlbGVjdGVkSW5kZXhdLnZhbHVlCiAgICAgICAgICAgICAgICAgICAgICApCiAgICAgICAgICAgICAgICAgICAgfQogICAgICAgICAgICAgICAgICB9IGVsc2UgewogICAgICAgICAgICAgICAgICAgIGluc3RhbmN' WHERE ALIAS = 'wddbmobile';

UPDATE PORTAL_SYS_COLUMN SET 
"TEMPLATE_JAVA_SCRIPT_" = TEMPLATE_JAVA_SCRIPT_ ||'lLmNsb3NlKCkKICAgICAgICAgICAgICAgICAgfQogICAgICAgICAgICAgICAgfSwKICAgICAgICAgICAgICB9KQogICAgICAgICAgICB9CiAgICAgICAgICB9IGVsc2UgaWYgKAogICAgICAgICAgICByb3cuc3RhdHVzID09ICdBR0VOVCcgJiYKICAgICAgICAgICAgcm93Lm93bmVySWQgIT0gcm93LmFzc2lnbmVlSWQgJiYKICAgICAgICAgICAgcm93Lm93bmVySWQgPT0gdGhpcy4kc3RvcmUuc3RhdGUubG9naW4uY3VycmVudFVzZXIudXNlcklkCiAgICAgICAgICApIHsKICAgICAgICAgICAgLy/lp5TmiZjku7vliqEKICAgICAgICAgICAgdGhpcy4kcm91dGVyLnB1c2goJy9pbnN0UmVhZC8nICsgcm93LnByb2NJbnN0SWQgKyAnL2FnZW50JykKICAgICAgICAgIH0gZWxzZSB7CiAgICAgICAgICAgIHRoaXMuJHJvdXRlci5wdXNoKCcvdGFzay8nICsgcm93LmlkICsgJy8wJykKICAgICAgICAgIH0KICAgICAgIH0sCiAgICAgIGZpbHRlckRhdGUoZGF0ZSkgewogICAgICAgIHZhciBjdXJyZW50RGF0ZSA9IG5ldyBEYXRlKCkuZ2V0VGltZSgpCiAgICAgICAgZGF0ZSA9IG5ldyBEYXRlKGRhdGUucmVwbGFjZSgvLS9nLCAnLycpKS5nZXRUaW1lKCkKICAgICAgICBpZiAoZGF0ZSAhPSB1bmRlZmluZWQpIHsKICAgICAgICAgIHZhciB0b3RhbCA9IChjdXJyZW50RGF0ZSAtIGRhdGUpIC8gMTAwMAogICAgICAgICAgdmFyIGRheSA9IHBhcnNlSW50KHRvdGFsIC8gKDI0ICogNjAgKiA2MCkpIC8v6K6h566X5pW05pWw5aSp5pWwCiAgICAgICAgICB2YXIgYWZ0ZXJEYXkgPSB0b3RhbCAtIGRheSAqIDI0ICogNjAgKiA2MCAvL+WPluW+l+eul+WHuuWkqeaVsOWQjuWJqeS9meeahOenkuaVsAogICAgICAgICAgdmFyIGhvdXIgPSBwYXJzZUludChhZnRlckRheSAvICg2MCAqIDYwKSkgLy/orqHnrpfmlbTmlbDlsI/ml7bmlbAKICAgICAgICAgIHZhciBhZnRlckhvdXIgPSB0b3RhbCAtIGRheSAqIDI0ICogNjAgKiA2MCAtIGhvdXIgKiA2MCAqIDYwIC8v5Y+W5b6X566X5Ye65bCP5pe25pWw5ZCO5Ymp5L2Z55qE56eS5pWwCiAgICAgICAgICB2YXIgbWluID0gcGFyc2VJbnQoYWZ0ZXJIb3VyIC8gNjApIC8v6K6h566X5pW05pWw5YiGCiAgICAgICAgICB2YXIgYWZ0ZXJNaW4gPSBwYXJzZUludCgKICAgICAgICAgICAgdG90YWwgLSBkYXkgKiAyNCAqIDYwICogNjAgLSBob3VyICogNjAgKiA2MCAtIG1pbiAqIDYwIC8v5Y+W5b6X566X5Ye65YiG5ZCO5Ymp5L2Z55qE56eS5pWwCiAgICAgICAgICApCiAgICAgICAgICB2YXIgdmFsID0gJycKICAgICAgICAgIGlmIChkYXkgIT0gMCkgewogICAgICAgICAgICByZXR1cm4gKHZhbCArPSBkYXkgKyAnZCcpCiAgICAgICAgICB9IGVsc2UgaWYgKGhvdXIgIT0gMCkgewogICAgICAgICAgICByZXR1cm4gKHZhbCArPSBob3VyICsgJ2gnKQogICAgICAgICAgfSBlbHNlIGlmIChtaW4gIT0gMCkgewogICAgICAgICAgICByZXR1cm4gKHZhbCArPSBtaW4gKyAnbScpCiAgICAgICAgICB9IGVsc2UgaWYgKGFmdGVyTWluICE9IDApIHsKICAgICAgICAgICAgcmV0dXJuICh2YWwgKz0gYWZ0ZXJNaW4gKyAncycpCiAgICAgICAgICB9CiAgICAgICAgfQogICAgICB9LAogICAgfSwKICAgIAp9'  WHERE ALIAS = 'wddbmobile';

-- 2023-1-3 jjx 增加diy组件表
CREATE TABLE FORM_CUSTOM_COMPONENT (
  "ID_" 			NVARCHAR2(255),
  "NAME_" 			NVARCHAR2(255),
  "ALIAS_" 			NVARCHAR2(255),
  "TENANT_ID_" 		NVARCHAR2(255),
  "CREATE_BY_" 		NVARCHAR2(255),
  "UPDATE_BY_" 		NVARCHAR2(255),
  "CREATE_ORG_ID_" 	NVARCHAR2(255),
  "CREATE_TIME_"	DATE,
  "UPDATE_TIME_" 	DATE,
  "TEMPLATE_" 		CLOB,
  "JS_CODE_" 		CLOB,
  "DATA_CODE_" 		CLOB,
  PRIMARY KEY ("ID_")
);

-- 2023-1-3 jjx 增加diy组件菜单
INSERT INTO PORTAL_SYS_MENU (ID_, PARENT_ID_, NAME_, ALIAS_, ACTIVE_TAB_, MENU_ICON_, OPENED_, SN_, TABS_STYLE_, PATH_, HREF_, TENANT_ID_) VALUES ('1550294793437974528', '3631013', 'DIY组件', 'formCustomComponentManager', NULL, NULL, NULL, '1', NULL, '-1.1.3441002.3631013.1550294793437974528.', NULL, '-1');

-- 2023-1-3 jjx 添加UDS同步记录表 
CREATE TABLE "UC_BATCH_SYNC" (
  "ID_" NVARCHAR2(64) 				NOT 	NULL,
  "EXECUTE_TIME_" 						DATE,
  "DATA_IDS_" 								CLOB,
  "STATUS_" 									INT 	DEFAULT 0,
  "CREATE_TIME_" 							DATE,
  "UPDATE_TIME_" 							DATE,
  "CREATE_BY_" 								NVARCHAR2(64),
  "CREATE_ORG_ID_"						NVARCHAR2(64),
  "UPDATE_BY_" 								NVARCHAR2(64),
  PRIMARY KEY ("ID_")
);
COMMENT ON COLUMN UC_BATCH_SYNC.EXECUTE_TIME_ IS '当前时间';
COMMENT ON COLUMN UC_BATCH_SYNC.DATA_IDS_ IS '同步数据ID，json格式；调用函数名和入参。';
COMMENT ON COLUMN UC_BATCH_SYNC.STATUS_ IS '状态，0-正在同步；1-已提交；2-已回滚';
COMMENT ON TABLE UC_BATCH_SYNC IS 'UDS同步记录表';

DROP INDEX IDX_JOB_CODE_UNQ;
CREATE UNIQUE INDEX IDX_JOB_CODE_UNQ ON UC_ORG_JOB ("CODE_","TENANT_ID_","IS_DELE_");

DROP INDEX IDX_JOB_CODE_UNQ;
CREATE UNIQUE INDEX IDX_JOB_CODE_UNQ ON UC_ORG_JOB ("CODE_","TENANT_ID_","IS_DELE_");

-- 2023年1月5日 jjx 门户管理表新增管理员id、管理员名称字段
alter table PORTAL_SYS_LAYOUT_MANAGE add MANAGER_ID_ NVARCHAR2(32);
comment on column PORTAL_SYS_LAYOUT_MANAGE.MANAGER_ID_ is '管理员ID';

alter table PORTAL_SYS_LAYOUT_MANAGE add MANAGER_NAME_ NVARCHAR2(32);
comment on column PORTAL_SYS_LAYOUT_MANAGE.MANAGER_NAME_ is '管理员名称';

-- 2023年1月9日 jjx 增加扩展jar包类型 
ALTER TABLE BPM_EXTEND_JAR ADD TYPE_ INT DEFAULT 0;
COMMENT ON COLUMN BPM_EXTEND_JAR.TYPE_ IS 'jar包类型。0：script脚本扩展，1：用户数据同步';


DROP INDEX IDX_ROLE_CODE;
CREATE UNIQUE INDEX IDX_ROLE_CODE ON UC_ROLE ("CODE_","TENANT_ID_","IS_DELE_");

-- uc_org、uc_org_post和uc_role唯一索引增加删除标志 update by 欧阳高龙20230111
DROP INDEX IDX_ORG_CODE_UNQ;
CREATE UNIQUE INDEX IDX_ORG_CODE_UNQ ON UC_ORG ("CODE_","TENANT_ID_","IS_DELE_");
DROP INDEX IDX_POST_CODE_UNQ;
CREATE UNIQUE INDEX IDX_POST_CODE_UNQ ON UC_ORG_POST ("CODE_","TENANT_ID_","IS_DELE_");
DROP INDEX IDX_ROLE_CODE;
CREATE UNIQUE INDEX IDX_ROLE_CODE ON UC_ROLE ("CODE_","TENANT_ID_","IS_DELE_");
-- 增加用户数据同步定时任务 update by 欧阳高龙20230111
insert into qrtz_job_details VALUES(
'quartzScheduler', '用户数据同步', '-1', '用户数据同步定时任务，结合外部扩展jar包上传', 'com.hotent.job.job.UserSyncJob', '1', '1', '0', '0',null);

-- 同步mysql脚本 20230201 jjx
ALTER TABLE UC_PARAMS MODIFY ("JSON_" NVARCHAR2(1700));

CREATE TABLE UC_MATRIX_AUTH (
  ID_           NVARCHAR2(64)   NOT NULL,
  MATRIX_ID_    NVARCHAR2(64),
  USER_ID_      NVARCHAR2(64),
  CREATE_TIME_  DATE,
  CREATE_BY_    NVARCHAR2(64),
  UPDATE_TIME_  DATE,
  UPDATE_BY_    NVARCHAR2(64),
  PRIMARY KEY (ID_)
);
CREATE INDEX IDX_MATRIX_AUTH_USERID ON UC_MATRIX_AUTH (USER_ID_);
COMMENT ON COLUMN UC_MATRIX_AUTH.MATRIX_ID_ IS '关系矩阵ID';
COMMENT ON COLUMN UC_MATRIX_AUTH.USER_ID_ IS '管理员ID';
COMMENT ON TABLE UC_MATRIX_AUTH IS '关系矩阵管理员';

ALTER TABLE BPM_FORM_MODIFY_RECORD ADD BO_ALIAS_ NVARCHAR2(64);
COMMENT ON COLUMN BPM_FORM_MODIFY_RECORD.BO_ALIAS_ IS 'bo别名';

-- 2023-02-03 huangtp 新增门户管理授权表
create table PORTAL_SYS_LAYOUT_AUTH
(
    ID_          NVARCHAR2(64) not null     constraint PORTAL_SYS_LAYOUT_AUTH_PK
        primary key,
    LAYOUT_ID_   NVARCHAR2(64),
    USER_ID_     NVARCHAR2(64),
    CREATE_TIME_ DATE,
    CREATE_BY_   NVARCHAR2(64),
    UPDATE_TIME_ DATE,
    UPDATE_BY_   NVARCHAR2(64),
    TENANT_ID_   NVARCHAR2(32) default '-1'
);
COMMENT ON COLUMN PORTAL_SYS_LAYOUT_AUTH.LAYOUT_ID_ IS '门户ID';
COMMENT ON COLUMN PORTAL_SYS_LAYOUT_AUTH.USER_ID_ IS '管理员ID';
COMMENT ON TABLE PORTAL_SYS_LAYOUT_AUTH IS '门户管理授权表';

-- 2023-02-04 huangtp 新增门户管理授权表用户名称、用户账号字段
ALTER TABLE PORTAL_SYS_LAYOUT_AUTH ADD user_name_ NVARCHAR2(64);
COMMENT ON COLUMN PORTAL_SYS_LAYOUT_AUTH.user_name_ IS '用户名称';
ALTER TABLE PORTAL_SYS_LAYOUT_AUTH ADD account_ NVARCHAR2(64);
COMMENT ON COLUMN PORTAL_SYS_LAYOUT_AUTH.account_ IS '用户账号';

-- 2023-2-6 liangjc 门户，新闻公告，栏目初始化栏目的权限为所有人
INSERT INTO portal_sys_auth_user ( ID_, AUTHORIZE_ID_, RIGHT_TYPE_, OBJ_TYPE_, TENANT_ID_ )
SELECT
	id,
	id,
	'everyone',
	'layoutManage',
	TENANT_ID_
FROM
	portal_sys_layout_manage
WHERE
	ID NOT IN ( SELECT AUTHORIZE_ID_ FROM portal_sys_auth_user )
	AND ID NOT IN ( SELECT id_ FROM portal_sys_auth_user );	
	
	
INSERT INTO portal_sys_auth_user ( ID_, AUTHORIZE_ID_, RIGHT_TYPE_, OBJ_TYPE_, TENANT_ID_ )
SELECT
	id_,
	id_,
	'everyone',
	'newsNotice',
	TENANT_ID_
FROM
	PORTAL_NEWS_NOTICE_
WHERE
	ID_ NOT IN ( SELECT AUTHORIZE_ID_ FROM portal_sys_auth_user )
	AND ID_ NOT IN ( SELECT id_ FROM portal_sys_auth_user );	
	
INSERT INTO portal_sys_auth_user ( ID_, AUTHORIZE_ID_, RIGHT_TYPE_, OBJ_TYPE_, TENANT_ID_ )
SELECT
	ID,
	ID,
	'everyone',
	'indexColumn',
	TENANT_ID_
FROM
	portal_sys_column
WHERE
	ID NOT IN ( SELECT AUTHORIZE_ID_ FROM portal_sys_auth_user )
	AND ID NOT IN ( SELECT id_ FROM portal_sys_auth_user );

ALTER TABLE portal_meeting_config ADD VERSION_ INT DEFAULT 1;
comment on column portal_meeting_config.VERSION_ is '版本号';

-- 2023-02-13 huangtp 关系矩阵授权表加上用户账号
ALTER TABLE UC_MATRIX_AUTH ADD ACCOUNT_ NVARCHAR2(64) DEFAULT '' NOT NULL;
comment on column UC_MATRIX_AUTH.ACCOUNT_ is '用户账号';

-- 2023-02-15 jjx 同步mysql脚本
UPDATE portal_sys_menu SET ACTIVE_TAB_='bizSystemManager' where ID_='1437738312683724800';
UPDATE portal_sys_menu SET PARENT_ID_='1437738312683724800',
PATH_='-1.1.1437982148582154240.1437738312683724800.1451456425861406720.',
SN_=2,
TYPE_='catalog'
where ID_='1451456425861406720';

INSERT INTO portal_sys_menu(ID_, PARENT_ID_, NAME_, ALIAS_, ACTIVE_TAB_, MENU_ICON_, OPENED_, SN_, TABS_STYLE_, PATH_, HREF_, TENANT_ID_, ROUTE_EXTEND_, TYPE_, IS_REFRESH_, token_Enabled_, TOKEN_TYPE_, TOKEN_KEY_, TOKEN_API_)
VALUES ('1625684930068721664', '1437738312683724800', '外部系统管理', 'bizSystemManager', '', '', NULL, 1, '', '-1.1.1437982148582154240.1437738312683724800.1625684930068721664.', '', '-1', '', 'catalog', 1, 0, 0, NULL, NULL);

ALTER TABLE PORTAL_SYS_COLUMN ADD IS_INIT_ SMALLINT;
COMMENT ON COLUMN PORTAL_SYS_COLUMN.IS_INIT_ IS '是否初始化栏目(新增租户给租户初始化)';
update PORTAL_SYS_COLUMN set IS_INIT_=1 where TENANT_ID_ = -1;

-- 2023-02-16 liangjc oracle缺失催办模板脚本补充
INSERT INTO PORTAL_SYS_MSG_TPL (ID_,NAME_,KEY_,TYPE_KEY_,IS_DEFAULT_,SUBJECT_,PLAIN_,HTML_,CREATE_BY_,CREATE_TIME_,CREATE_ORG_ID_,UPDATE_BY_,UPDATE_TIME_,SMS_TEMPLATE_NO_,VOICE_TEMPLATE_NO_,TENANT_ID_)
VALUES('22','催办模板',	'promoter-default',	'promoter',	1,'催办提醒','${taskSubject}','<p><a href="${baseUrl}/${frontBaseUrl}/matter/approvalForm?taskId=${taskId}&instId=${instId}&isGetApprovalBtn=true">${taskSubject}</a></p>',	NULL,NULL,NULL,	NULL,NULL,NULL,NULL,'-1');

INSERT INTO portal_meeting_config(ID_, BIND_FLOW_, FLOW_KEY_, FLOW_NAME_, PUBLISH_SCHEDULE_, FIELD_MAPPING_, CREATE_BY_, UPDATE_BY_, CREATE_TIME_, UPDATE_TIME_, CREATE_ORG_ID_, TENANT_ID_, DIC_CODE_, DIC_NAME_, COLUMN_ID_) VALUES ('1', 0, NULL, NULL, 1, NULL, '1', '1', NULL, NULL, NULL, '-1', NULL, NULL, NULL);

UPDATE PORTAL_SYS_PROPERTIES SET VALUE = 'bHfSv3x1fi+lJABUhmU8OeY08mPnTHxMWR1WRuHJKS+YRaSDGCyf8BgNKnk/fKbQ6GB5pHWcTo+Pv7lHddu4nR0yd0ZumquJU67O2r7qQsRL1iat2BjwCcvWIvV9R9fD7wB1PAmt1cZJVyEcqqR9MNjH1+rLhv0r/XXVUTo8DLRgFO3zWkBGqJVpKU2LKa6RKdIxeR7A5MkP3NcLLIjxF8Q4FgCrt+ocxpIvkSv9vGUagCMJdv+DO/5w3IHLiGBuOx9OzWcXxklfhuxxfPLRFBqnbSBjHl43GzCH4AAWNgoXvGWkdvJSqkb2sgme/tKrP6YXNuVGj5hluovbLbMRT8e4Ta8uqn9OB51DypxPdukgSkLw3QBa12S3TSQVXrx9GeM8skAX42N32OKxLj7R7TAcvC+iMPwVq8TFkhb3SQhI8iSTmm78T/LmjcDT0H4hF7xlpHbyUqpG9rIJnv7Sqxl20VdLRQ8aM8QwNIdsOhKhgsYfdoBs+eD/Fk0xqjSabUpuU1ePW3KNbmprxbg1wC+kD+SwF+UcjNcjIj6/7nVMRp5f3d8yyR0pQRSz5l3SkTgstjiLwzfngj73eizXn6WRZuGdSbqdMLHsVKVViJRClggscn8OTNdmwbVFdsEFlOpXb2v/2yaBGiWdmSfM282c37fPM7mFxGFz7nHh990gSkLw3QBa12S3TSQVXrx9DJnpKUGaQZHEzYWjEXVBLkdRpTqcFOPjRAEJjpVi3clSiAkfCZcGKRp6Ugwf6/Bn1ngz9wERdjLlLlf56GNFhUM3DSSxmT1rpmA0JJwNkBZ1mhgLjr5CkGep7eJ+qkoj5ykvtUR0THrV3TG0eMcch5ZUn4cXHCr/HhX/I/u+L/7SMLoCvgrkO4WQDqi/O42dzJUUF1VkdguJAASw8IY05i7+bP1+bL1NFi2ARuuuyV5EAINZJYa8n5LfIpZv87HEnz91kPExDYznMnNhuNUD+5YU6OQbK/UImWRvwChDashsEsI7yUeORtjd6AkNFzZHIzC0n3F8I9otlNpxSvdtO8ttmc6ttrqa99d+/rA18IKaxcE1ERlK4A3vMjTreiu/eeqaSI3PHJrupfaZLP9BcvyGZaK54FZTLZKrjkJ4DjsGPMG0HCW/5zl801p+af8b6Sh7IQkaY67Jnd2cPH9CeB4+bIcJ77yUxR8RVnKjNsgHwMzxjUDjXjruC1tssi/8' WHERE ALIAS = 'taskListFiledConf';

INSERT INTO  PORTAL_I18N_MESSAGE(ID_, TYPE_, KEY_, VALUE_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) VALUES ('1628285933335953408', 'en', 'eip.common.import', 'import',  '1', NULL, NULL, NULL, '-1');

INSERT INTO  PORTAL_I18N_MESSAGE(ID_, TYPE_, KEY_, VALUE_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) VALUES ('1628285933373702144', 'zh-TW', 'eip.common.import', '導入',  '1', NULL, NULL, NULL, '-1');

INSERT INTO  PORTAL_I18N_MESSAGE(ID_, TYPE_, KEY_, VALUE_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_)VALUES ('1628285933411450880', 'zh-CN', 'eip.common.import', '导入',  '1', NULL, NULL, NULL, '-1');
INSERT INTO  PORTAL_I18N_MESSAGE(ID_, TYPE_, KEY_, VALUE_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) VALUES ('1628286057118253056', 'en', 'eip.common.export', 'export',  '1', NULL, NULL, NULL, '-1');
INSERT INTO  PORTAL_I18N_MESSAGE(ID_, TYPE_, KEY_, VALUE_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_)VALUES ('1628286057156001792', 'zh-TW', 'eip.common.export', '導出',  '1', NULL, NULL, NULL, '-1');
INSERT INTO  PORTAL_I18N_MESSAGE(ID_, TYPE_, KEY_, VALUE_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) VALUES ('1628286057189556224', 'zh-CN', 'eip.common.export', '导出', '1', NULL, NULL, NULL, '-1');

ALTER TABLE "PORTAL_BIZ_SYSTEM" ADD PRIMARY KEY ("ID_");
ALTER TABLE "PORTAL_BIZ_SYSTEM_AUTH" ADD PRIMARY KEY ("ID_");
ALTER TABLE "PORTAL_MSG_SEND_LOG" ADD PRIMARY KEY ("ID_");
ALTER TABLE "PORTAL_URL_PERMISSION" ADD PRIMARY KEY ("ID");
ALTER TABLE "BPM_INST_DELETE_LOG" ADD PRIMARY KEY ("ID_");
ALTER TABLE "FORM_DATA_TEMPLATE_IMPORT_LOG" ADD PRIMARY KEY ("ID_");

-- 2023-3-6 liangjc 会议室配置默认不需要绑定流程脚本补充
update portal_meeting_config set BIND_FLOW_=0;