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

-- yjj 2022-04-19 增加表单列表数据导入日志记录表
CREATE TABLE FORM_DATA_TEMPLATE_IMPORT_LOG (
  "ID_"                   NVARCHAR2(64)       NOT NULL,
  "IMPORT_ACCOUNT_"       NVARCHAR2(255)      DEFAULT NULL,
  "IMPORT_NAME_"          NVARCHAR2(255)      DEFAULT NULL,
  "IMPORT_TIME_"          DATE                DEFAULT NULL,
  "STATUS_"               CHAR(1)             DEFAULT NULL,
  "TENANT_ID_"            NVARCHAR2(64)       DEFAULT NULL,
  "REASON_"               CLOB,
  "TEMP_ALIAS_"           NVARCHAR2(64)
);
COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.ID_ IS '主键';
COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.IMPORT_ACCOUNT_ IS '导入人账号';
COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.IMPORT_NAME_ IS '导入人名称';
COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.IMPORT_TIME_ IS '导入时间';
COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.STATUS_ IS '导入状态 1-导入成功 0-导入失败';
COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.TENANT_ID_ IS '租户id';
COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.TEMP_ALIAS_ IS '导入表单列表别名';
COMMENT ON TABLE FORM_DATA_TEMPLATE_IMPORT_LOG IS '表单列表导入日志记录';

-- 2022-04-24 yijj 增加关联表sql字段
ALTER TABLE FORM_DATA_TEMPLATE ADD JOIN_TABLES_SQL_ CLOB;
COMMENT ON COLUMN FORM_DATA_TEMPLATE.JOIN_TABLES_SQL_ IS '关联表SQL';

-- 黎扬贵 2022-05-07 新增任务表索引
CREATE INDEX "IDX_BPM_TASK_OWNER_ID_"  ON "BPM_TASK" ("OWNER_ID_");
CREATE INDEX "IDX_BPM_TASK_ASSIGNEE_ID_" ON "BPM_TASK" ("ASSIGNEE_ID_");

-- 黎扬贵 2022-05-07  新增任务候选人表 索引
CREATE INDEX "IDX_TASKCANDIDATE_EXECUTOR" ON "BPM_TASK_CANDIDATE" ("EXECUTOR_");
CREATE INDEX "IDX_TASKCANDIDATE_TYPE" ON "BPM_TASK_CANDIDATE" ("TYPE_");

-- 欧阳高龙 2022-05-09 组织与外部通讯录关系表
CREATE TABLE "UC_ORG_UNITE" (
  "ID_"             NVARCHAR2(64)     NOT NULL,
  "ORG_ID_"         NVARCHAR2(64)     DEFAULT NULL,
  "WX_WORK_ID_"     NVARCHAR2(100)    DEFAULT NULL,
  "DINGTALK_ID_"    NVARCHAR2(100)    DEFAULT NULL,
  "FLYBOOK_ID_"     NVARCHAR2(100)    DEFAULT NULL,
  "DELETE_FLAG_"    NUMBER(1,0)       DEFAULT 0  NOT NULL,
  "UPDATE_TIME_"    DATE,
  "CREATE_TIME_"    DATE,
  "TENANT_ID_"      NVARCHAR2(64)     DEFAULT NULL
);
COMMENT ON COLUMN "UC_ORG_UNITE"."ID_" IS '主键';
COMMENT ON COLUMN "UC_ORG_UNITE"."ORG_ID_" IS '平台组织id';
COMMENT ON COLUMN "UC_ORG_UNITE"."WX_WORK_ID_" IS '企业微信组织id';
COMMENT ON COLUMN "UC_ORG_UNITE"."DINGTALK_ID_" IS '阿里钉钉组织id';
COMMENT ON COLUMN "UC_ORG_UNITE"."FLYBOOK_ID_" IS '飞书组织id';
COMMENT ON COLUMN "UC_ORG_UNITE"."DELETE_FLAG_" IS '是否删除';
COMMENT ON COLUMN "UC_ORG_UNITE"."UPDATE_TIME_" IS '更新时间';
COMMENT ON COLUMN "UC_ORG_UNITE"."CREATE_TIME_" IS '创建时间';
COMMENT ON COLUMN "UC_ORG_UNITE"."TENANT_ID_" IS '租户ID';
COMMENT ON TABLE "UC_ORG_UNITE" IS '组织与外部通讯录关系表';
-- Primary Key structure for table UC_ORG_UNITE
ALTER TABLE "UC_ORG_UNITE" ADD CONSTRAINT "SYS_C00143854" PRIMARY KEY ("ID_");

-- 欧阳高龙 2022-05-09 增加外部通讯录同步使用到的配置信息
ALTER TABLE UC_USER_UNITE ADD "FLYBOOK_ID_" NVARCHAR2(100) DEFAULT NULL;
comment on column UC_USER_UNITE.FLYBOOK_ID_ is '飞书用户ID';
ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_FLAG_" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE;
comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_FLAG_ is '是否同步组织架构';
ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_SCOPE_" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE;
comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_SCOPE_ is '同步范围，0-全部；1-仅用户；2-部分';
ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_ORG_ID_" NVARCHAR2(64) DEFAULT NULL;
comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_ORG_ID_ is '同步的组织的ID';
ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_ORG_" NVARCHAR2(64) DEFAULT NULL;
comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_ORG_ is '同步组织';
ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_DIRECTION_" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE;
comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_DIRECTION_ is '同步方式，拉取或上传';
ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "AUTO_SYNC_" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE;
comment on column PORTAL_SYS_EXTERNAL_UNITE.AUTO_SYNC_ is '同步方法，是否自动同步';
ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_FREQUENCY_" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE;
comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_FREQUENCY_ is '同步频率，0-每周；1-每月；2-每小时';
ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_STARTING_TIME" DATE;
comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_STARTING_TIME is '同步起始时间';
ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "AGENT_NAME_" NVARCHAR2(64) DEFAULT NULL;
comment on column PORTAL_SYS_EXTERNAL_UNITE.AGENT_NAME_ is '应用名称';
ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "ALIAS_" NVARCHAR2(64) DEFAULT NULL;
comment on column PORTAL_SYS_EXTERNAL_UNITE.ALIAS_ is '应用别名';


-- 2022-04-24 yijj 增加角色授权菜单设置为是否本级及下级
ALTER TABLE PORTAL_SYS_ROLE_AUTH ADD IS_OPEN_ NUMBER(*,0) DEFAULT NULL;
COMMENT ON COLUMN PORTAL_SYS_ROLE_AUTH.IS_OPEN_ IS '是否本级及下级，1-是本级及下级，0-不是本级及下级';

-- 2022-7-22 liangjc修改栏目类型的类型 增加js脚本字段
ALTER TABLE portal_sys_column ADD TEMPLATE_JAVA_SCRIPT_ CLOB default null ;
alter table portal_sys_column rename column COL_TYPE  to name_tmp;
alter table portal_sys_column add COL_TYPE NVARCHAR2(50);
update portal_sys_column set COL_TYPE=trim(name_tmp);
alter table portal_sys_column drop column name_tmp;

-- 2022-7-25 liangjc portal_sys_column 增加长度
ALTER TABLE portal_sys_column MODIFY DATA_PARAM NVARCHAR2(2000);

-- 2022-07-22 liangjc迁移 个人待办事项表（三门核电）
CREATE TABLE "PORTAL_CUSTOM_TODO" (
  "ID_"                   NVARCHAR2(64)     NOT NULL,
  "USER_ID_"              NVARCHAR2(64)     NOT NULL,
  "TASK_NAME_"            NVARCHAR2(255),
  "MARK_"                 NVARCHAR2(255),
  "STATUS_"               NVARCHAR2(1),
  "REMARK_"               NVARCHAR2(2000),
  "IS_REPEAT_"            NVARCHAR2(1),
  "REPEAT_RATE_"          NUMBER,
  "REPEAT_TYPE_"          NVARCHAR2(1),
  "REPEAT_START_TIME_"    DATE,
  "CREATE_TIME_"          DATE,
  "COMPLETE_TIME_"        DATE,
  "DEAD_LINE_"            DATE,
  "UPDATE_TIME_"          DATE,
  "SN_"                   NUMBER,
  primary key (ID_)
);
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."ID_" IS '主键';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."USER_ID_" IS '用户id';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."TASK_NAME_" IS '任务事项';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."MARK_" IS '标记';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."STATUS_" IS '状态。0:未完成，1：已完成';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."REMARK_" IS '备注';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."IS_REPEAT_" IS '是否重复任务。0:否，1:是';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."REPEAT_RATE_" IS '重复频率';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."REPEAT_TYPE_" IS '重复类型：1:年。2:月，3:日，4:小时，5:分钟';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."REPEAT_START_TIME_" IS '重复任务开始时间';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."CREATE_TIME_" IS '创建时间';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."COMPLETE_TIME_" IS '完成时间';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."DEAD_LINE_" IS '截止时间';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."UPDATE_TIME_" IS '更新时间';
COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."SN_" IS '排序号';
COMMENT ON TABLE "PORTAL_CUSTOM_TODO" IS '用户自定义待办事项表';

-- 2022-7-26 liangjc 获取微信公众号素材列表
INSERT INTO qrtz_job_details(SCHED_NAME, JOB_NAME, JOB_GROUP, DESCRIPTION, JOB_CLASS_NAME, IS_DURABLE, IS_NONCONCURRENT, IS_UPDATE_DATA, REQUESTS_RECOVERY, JOB_DATA) VALUES ('quartzScheduler', '获取微信公众号素材列表', '-1', '获取微信公众号素材列表', 'com.hotent.column.job.PullWeixinMaterialJob', '1', '1', '0', '0', HEXTORAW('ACED0005737200156F72672E71756172747A2E4A6F62446174614D61709FB083E8BFA9B0CB020000787200266F72672E71756172747A2E7574696C732E537472696E674B65794469727479466C61674D61708208E8C3FBC55D280200015A0013616C6C6F77735472616E7369656E74446174617872001D6F72672E71756172747A2E7574696C732E4469727479466C61674D617013E62EAD28760ACE0200025A000564697274794C00036D617074000F4C6A6176612F7574696C2F4D61703B787000737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F40000000000010770800000010000000007800'));

-- 2022-7-29 liangjc portal_sys_column 增加长度
alter table portal_sys_column rename column DATA_PARAM  to name_tmp;
alter table portal_sys_column add DATA_PARAM CLOB;
update portal_sys_column set DATA_PARAM=trim(name_tmp);
alter table portal_sys_column drop column name_tmp;

-- 2022-8-1 liangjc portal_sys_layout_manage 增加栏目间隔
ALTER TABLE portal_sys_layout_manage ADD GUTTER_ number default null;
comment on column portal_sys_layout_manage.GUTTER_ is '栏目间隔';

-- 2022-8-1 liangjc 会议室相关表迁移
CREATE TABLE "PORTAL_MEETING_APPLY" (
  "ID_"                 NVARCHAR2(64)     NOT NULL,
  "SUBJECT_"            NVARCHAR2(300),
  "MEETING_NAME_"       NVARCHAR2(64),
  "MEETING_ID_"         NVARCHAR2(64),
  "MEETING_OWNER_"      NVARCHAR2(64),
  "OWNER_OWNER_ID_"     NVARCHAR2(64),
  "MEETING_DEP_"        NVARCHAR2(64),
  "MEETING_DEP_ID_"     NVARCHAR2(64),
  "PARTICIPANTS_"       CLOB,
  "PARTICIPANTS_ID_"    CLOB,
  "DEPARTMENT_"         NVARCHAR2(1000),
  "DEPARTMENT_ID_"      NVARCHAR2(1000),
  "OUTSIDERS_"          NVARCHAR2(600),
  "ATTENDANCE_"         NUMBER(11,0),
  "MEETING_NOTES_"      CLOB,
  "FILES_"              CLOB,
  "APPLY_DESC_"         CLOB,
  "START_TIME_"         TIMESTAMP (0),
  "END_TIME_"           TIMESTAMP (0),
  "BIND_FLOW_"          NUMBER(6,0),
  "FLOW_KEY_"           NVARCHAR2(64),
  "FLOW_NAME_"          NVARCHAR2(64),
  "PUBLISH_SCHEDULE_"   NUMBER(6,0),
  "STATUS_"             NVARCHAR2(20),
  "SERVICE_"            NVARCHAR2(600),
  "PRO_INST_ID_"        NVARCHAR2(64),
  "CREATE_BY_"          NVARCHAR2(64),
  "UPDATE_BY_"          NVARCHAR2(64),
  "CREATE_TIME_"        TIMESTAMP (0),
  "UPDATE_TIME_"        TIMESTAMP (0),
  "CREATE_ORG_ID_"      NVARCHAR2(64),
  "TENANT_ID_"          NVARCHAR2(64),
  PRIMARY KEY ("ID_")
);
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."ID_" IS '主键';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."SUBJECT_" IS '会议主题';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."MEETING_NAME_" IS '会议室名称';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."MEETING_ID_" IS '会议室id';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."MEETING_OWNER_" IS '会议申请人';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."OWNER_OWNER_ID_" IS '会议申请人id';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."MEETING_DEP_" IS '所属部门';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."MEETING_DEP_ID_" IS '所属部门id';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."PARTICIPANTS_" IS '参会人员';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."PARTICIPANTS_ID_" IS '参会人员id';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."DEPARTMENT_" IS '参会部门';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."DEPARTMENT_ID_" IS '参会部门id';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."OUTSIDERS_" IS '外部人员';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."ATTENDANCE_" IS '参会人数';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."MEETING_NOTES_" IS '会议说明';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."FILES_" IS '相关附件';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."APPLY_DESC_" IS '申请说明';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."START_TIME_" IS '开始时间';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."END_TIME_" IS '结束时间';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."BIND_FLOW_" IS '是否绑定流程';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."FLOW_KEY_" IS '流程key';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."FLOW_NAME_" IS '流程名称';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."PUBLISH_SCHEDULE_" IS '发布到日程';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."STATUS_" IS '申请状态';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."SERVICE_" IS '所需服务';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."PRO_INST_ID_" IS '流程实例id';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."CREATE_BY_" IS '创建人id';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."UPDATE_BY_" IS '更新人id';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."CREATE_TIME_" IS '创建时间';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."UPDATE_TIME_" IS '更新时间';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."CREATE_ORG_ID_" IS '创建人所属部门id';
COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."TENANT_ID_" IS '租户id';
COMMENT ON TABLE "PORTAL_MEETING_APPLY" IS '会议室申请';

CREATE TABLE "PORTAL_MEETING_CONFIG" (
  "ID_"                  VARCHAR(64)    NOT NULL,
  "BIND_FLOW_"           NUMBER(6,0),
  "FLOW_KEY_"            NVARCHAR2(64),
  "FLOW_NAME_"           NVARCHAR2(64),
  "PUBLISH_SCHEDULE_"    NUMBER(6,0),
  "FIELD_MAPPING_"       CLOB,
  "CREATE_BY_"           NVARCHAR2(64),
  "UPDATE_BY_"           NVARCHAR2(64),
  "CREATE_TIME_"         TIMESTAMP (0),
  "UPDATE_TIME_"         TIMESTAMP (0),
  "CREATE_ORG_ID_"       NVARCHAR2(64),
  "TENANT_ID_"           NVARCHAR2(64),
  "DIC_CODE_"            NVARCHAR2(64),
  "DIC_NAME_"            NVARCHAR2(64),
  "COLUMN_ID_"           NVARCHAR2(64),
  PRIMARY KEY ("ID_")
);
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."ID_" IS '主键';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."BIND_FLOW_" IS '是否绑定流程';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."FLOW_KEY_" IS '流程key';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."FLOW_NAME_" IS '流程名称';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."PUBLISH_SCHEDULE_" IS '发布到日程';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."FIELD_MAPPING_" IS '流程实例ID';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."CREATE_BY_" IS '创建人id';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."UPDATE_BY_" IS '更新人id';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."CREATE_TIME_" IS '创建时间';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."UPDATE_TIME_" IS '更新时间';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."CREATE_ORG_ID_" IS '创建人所属部门id';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."TENANT_ID_" IS '租户id';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."DIC_CODE_" IS '会议室设备字典编码';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."DIC_NAME_" IS '会议室设备字典名称';
COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."COLUMN_ID_" IS 'lan';
COMMENT ON TABLE "PORTAL_MEETING_CONFIG" IS '会议室配置';

CREATE TABLE "PORTAL_MEETING_ROOM" (
  "ID_"                     NVARCHAR2(64)     NOT NULL,
  "NAME_"                   NVARCHAR2(64),
  "ALIAS_"                  NVARCHAR2(64),
  "TYPE_"                   NVARCHAR2(64),
  "CAPACITY_"               NUMBER(11,0),
  "ADDRESS_"                NVARCHAR2(600),
  "FLOOR_ROOM_NUMBER_"      NVARCHAR2(64),
  "EQUIPMENT_"              CLOB,
  "STATUS_"                 NVARCHAR2(20),
  "DESC_"                   CLOB,
  "CREATE_BY_"              NVARCHAR2(64),
  "UPDATE_BY_"              NVARCHAR2(64),
  "CREATE_TIME_"            TIMESTAMP (0),
  "UPDATE_TIME_"            TIMESTAMP (0),
  "CREATE_ORG_ID_"          NVARCHAR2(64),
  "TENANT_ID_"              NVARCHAR2(64),
  PRIMARY KEY ("ID_")
);
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."ID_" IS '主键';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."NAME_" IS '会议室名称';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."ALIAS_" IS '会议室别名';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."TYPE_" IS '会议室类型';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."CAPACITY_" IS '会议室容量';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."ADDRESS_" IS '会议室地址';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."FLOOR_ROOM_NUMBER_" IS '楼层房间号';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."EQUIPMENT_" IS '包含设备';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."STATUS_" IS '会议室状态（可用、不可用）';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."DESC_" IS '会议室说明描述';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."CREATE_BY_" IS '创建人id';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."UPDATE_BY_" IS '更新人id';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."CREATE_TIME_" IS '创建时间';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."UPDATE_TIME_" IS '更新时间';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."CREATE_ORG_ID_" IS '创建人所属部门id';
COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."TENANT_ID_" IS '租户id';
COMMENT ON TABLE "PORTAL_MEETING_ROOM" IS '会议室管理';


-- 2022-8-4 xiejun uc_user 增加双因素验证密钥
alter table uc_user add TWO_VERIFY_SECRET_ NVARCHAR2(50) default null;
COMMENT ON COLUMN uc_user.TWO_VERIFY_SECRET_ IS '双因素验证绑定密钥';

CREATE TABLE UC_ONE_TIME_CODE(
ID_               NVARCHAR2(64)   NOT NULL  PRIMARY KEY ,
CODE_             NVARCHAR2(50)   NOT NULL ,
USER_ID_          NVARCHAR2(64)   NOT NULL,
IS_USED_          NUMBER(1)       DEFAULT 0,
TENANT_ID_        NVARCHAR2(64),
CREATE_TIME_      DATE NOT NULL ,
CREATOR_          NVARCHAR2(32)   NOT NULL,
CREATE_BY_        NVARCHAR2(64)   NOT NULL,
IS_DELE_          NUMBER(1)       default 0 not null
);
comment on table UC_ONE_TIME_CODE is '双因素验证一次性码表';
comment on column UC_ONE_TIME_CODE.ID_ is '主键';
comment on column UC_ONE_TIME_CODE.CODE_ is '一次性验证码';
comment on column UC_ONE_TIME_CODE.USER_ID_ is '所属用户ID';
comment on column UC_ONE_TIME_CODE.IS_USED_ is '是否已使用;0:未使用 1:已使用';
comment on column UC_ONE_TIME_CODE.TENANT_ID_ is '租户id';
comment on column UC_ONE_TIME_CODE.CREATE_TIME_ is '创建时间';
comment on column UC_ONE_TIME_CODE.CREATOR_ is '创建人';
comment on column UC_ONE_TIME_CODE.CREATE_BY_ is '创建人ID';
comment on column UC_ONE_TIME_CODE.IS_DELE_ is '是否删除;0否 1是';

-- 2022-8-19 liangjc portal_user_layout_config 增加创建时间字段
alter table PORTAL_USER_LAYOUT_CONFIG	add CREATE_TIME_ DATE;
comment on column PORTAL_USER_LAYOUT_CONFIG.CREATE_TIME_ is '创建时间';

-- 2022-8-26 jjx 增加双因子认证系统属性默认配置
INSERT INTO "PORTAL_SYS_PROPERTIES"("ID", "NAME", "ALIAS", "GROUP_", "VALUE", "ENCRYPT", "DESCRIPTION", "UPDATE_TIME_", "CREATE_BY_", "CREATE_TIME_", "CREATE_ORG_ID_", "UPDATE_BY_", "TENANT_ID_") VALUES ('8', '双因素认证', 'twoVerifyConf', NULL, 'false', '0', '双因子验证系统配置', NULL, '1', TO_DATE('2022-08-26 10:47:04', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL, NULL);

-- 2022-8-26 jjx 月活跃限制表
create table BPM_BLOOM
(
    ID_           NVARCHAR2(64) not null  constraint BPM_BLOOM_PK  primary key,
    BLOOM_BYTES_  clob,
    CODE_         NVARCHAR2(255)
);
comment on table BPM_BLOOM is '布隆过滤器数据表';
comment on column BPM_BLOOM.ID_ is '主键';
comment on column BPM_BLOOM.BLOOM_BYTES_ is '布隆过滤器的数据';
comment on column BPM_BLOOM.CODE_ is '编码，做唯一记录识别';

-- 2022-08-29 yijj  BO定义表增加后端校验json字段
ALTER TABLE "FORM_BO_DEF" ADD ("VERIFY_JSON_" CLOB);
COMMENT ON COLUMN "FORM_BO_DEF"."VERIFY_JSON_" IS '后端接口校验json';

-- 2022-8-30 liangjc 删除模板管理菜单
delete from portal_sys_menu where ALIAS_='formTemplateList';

-- 2022-8-30 liangjc 增加元件管理关联查询 对话框 流水号 分类 ，默认分类初始化
INSERT INTO portal_sys_type_group VALUES ('16', 'Query_TYPE', '元件关联查询', 1, 16, 1, NULL, NULL, NULL, '1', NULL);
INSERT INTO portal_sys_type_group VALUES ('17', 'DIALOG_TYPE', '元件对话框', 1, 17, 1, NULL, NULL, NULL, '1', NULL);
INSERT INTO portal_sys_type_group VALUES ('18', 'IDENTITY_MANAGER_TYPE', '元件流水号', 1, 18, 1, NULL, NULL, NULL, '1', NULL);
INSERT INTO portal_sys_type(ID_, TYPE_GROUP_KEY_, NAME_, TYPE_KEY_, STRU_TYPE_, PARENT_ID_, DEPTH_, PATH_, IS_LEAF_, OWNER_ID_, SN_, CREATE_BY_, CREATE_TIME_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, ICON_, TENANT_ID_) VALUES ('16100', 'Query_TYPE', '默认分类', 'default', '1', '16', 1, '16.16100.', 'Y', '0', 0, '1', NULL, NULL, NULL, NULL, NULL, '-1');
INSERT INTO portal_sys_type(ID_, TYPE_GROUP_KEY_, NAME_, TYPE_KEY_, STRU_TYPE_, PARENT_ID_, DEPTH_, PATH_, IS_LEAF_, OWNER_ID_, SN_, CREATE_BY_, CREATE_TIME_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, ICON_, TENANT_ID_) VALUES ('17100', 'DIALOG_TYPE', '默认分类', 'default', '1', '17', 1, '17.17100.', 'Y', '0', 0, '1', NULL, NULL, NULL, NULL, NULL, '-1');
INSERT INTO portal_sys_type(ID_, TYPE_GROUP_KEY_, NAME_, TYPE_KEY_, STRU_TYPE_, PARENT_ID_, DEPTH_, PATH_, IS_LEAF_, OWNER_ID_, SN_, CREATE_BY_, CREATE_TIME_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, ICON_, TENANT_ID_) VALUES ('18100', 'IDENTITY_MANAGER_TYPE', '默认分类', 'default', '1', '18', 1, '18.18100.', 'Y', '0', 0, '1', NULL, NULL, NULL, NULL, NULL, '-1');

-- 2022-8-31 liangjc 增加元件管理关联查询 对话框 流水号 分类字段
ALTER TABLE form_custom_query ADD TYPE_ID_ NVARCHAR2(64);
ALTER TABLE form_custom_query ADD TYPE_NAME_ NVARCHAR2(64);
ALTER TABLE form_custom_dialog ADD TYPE_ID_ NVARCHAR2(64);
ALTER TABLE form_custom_dialog ADD TYPE_NAME_ NVARCHAR2(64);
ALTER TABLE portal_sys_identity ADD TYPE_ID_ NVARCHAR2(64);
ALTER TABLE portal_sys_identity ADD TYPE_NAME_ NVARCHAR2(64);
comment on column form_custom_query.TYPE_ID_ is '分类id';
comment on column form_custom_query.TYPE_NAME_ is '分类名称';
comment on column form_custom_dialog.TYPE_ID_ is '分类id';
comment on column form_custom_dialog.TYPE_NAME_ is '分类名称';
comment on column portal_sys_identity.TYPE_ID_ is '分类id';
comment on column portal_sys_identity.TYPE_NAME_ is '分类名称';

-- 2022-8-31 liangjc form_url_form增加所属系统编码字段
ALTER TABLE form_url_form ADD SYS_CODE_ NVARCHAR2(50);
comment on column form_url_form.SYS_CODE_ is '所属系统编码';

-- 2022-8-31 liangjc form_url_form系统编码字段修复
update form_url_form t1,portal_biz_system t2 set t1.SYS_CODE_ = t2.SYS_CODE_ where t1.SYS_ID_=t2.ID_ and t1.SYS_CODE_ is null;

-- 2022-9-1 jjx 添加应用端安全设置页面--执行后需要手动在菜单管理中执行下更新动作
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_") VALUES ('1584788054295793664', '1441341228585766912', '安全设置', 'security', NULL, NULL, NULL, '4', NULL, '-1.3.1441341228585766912.1584788054295793664.', NULL, '-1', '{"path":"security","component":"@/views/personal/security","redirect":"","alwaysShow":false,"caseSensitive":false,"meta":{"title":"安全设置","isChildren":true,"icon":""}}', 'page', '1');

-- 2022-9-1 liangjc邮件增加smtpHost，sendPort字段
ALTER TABLE uc_tenant_mail_server ADD SMTP_HOST_ NVARCHAR2(128);
ALTER TABLE uc_tenant_mail_server ADD SEND_PORT_ NVARCHAR2(20);
comment on column uc_tenant_mail_server.SMTP_HOST_ is 'smt主机';
comment on column uc_tenant_mail_server.SEND_PORT_ is '邮件服务器的端口';

--2022-9-2  菜单增加是否刷新字段和国际化temp字段
ALTER TABLE "PORTAL_SYS_MENU" ADD ("IS_REFRESH_" NUMBER);
COMMENT ON COLUMN "PORTAL_SYS_MENU"."IS_REFRESH_" IS '是否刷新，默认刷新， 1刷新0不刷新';

--2022-9-2 设置我的任务和流程启动默认不刷新
UPDATE portal_sys_menu SET IS_REFRESH_ = '0' WHERE ALIAS_ in ('myTask','startProcess')

-- 2022-9-5 liangjc表单模板增加汇总区域字段
ALTER TABLE FORM_DATA_TEMPLATE ADD SUMMARY_REGION_HTML_ CLOB;
COMMENT ON COLUMN FORM_DATA_TEMPLATE.SUMMARY_REGION_HTML_ IS '汇总区域';

-- 2022-9-5 liangjc字段长度调整
ALTER TABLE PORTAL_SYS_POPUP MODIFY NAME_ NVARCHAR2(200);

-- 2022-9-6 liangjc字段长度调整
ALTER TABLE BPM_TASK_COMMU MODIFY OPINION_ NVARCHAR2(1500);
ALTER TABLE BPM_PRO_CPTO MODIFY OPINION_ NVARCHAR2(1500);

-- 2022-9-5 yijj 增加会签数据 entity对象 用户组类型
ALTER TABLE "BPM_TASK_SIGNDATA" ADD ("IDENTITY_TYPE_" NVARCHAR2(64));
COMMENT ON COLUMN "BPM_TASK_SIGNDATA"."IDENTITY_TYPE_" IS '用户组类型';

-- 2022-09-19 yijj 修改菜单别名
UPDATE portal_sys_menu SET ALIAS_ = 'Index' WHERE ID_ ='1441359958220918784';
UPDATE portal_sys_menu SET ALIAS_ = 'NewMatter' WHERE ID_ ='1441342209381814272';
UPDATE portal_sys_menu SET ALIAS_ = 'MyTask' WHERE ID_ ='1441342471236407296';
UPDATE portal_sys_menu SET ALIAS_ = 'Request' WHERE ID_ ='1441342583597617152';
UPDATE portal_sys_menu SET ALIAS_ = 'CirculateMatter' WHERE ID_ ='1443153816786980864';
UPDATE portal_sys_menu SET ALIAS_ = 'Setting' WHERE ID_ ='1441938397294415872';
UPDATE portal_sys_menu SET ALIAS_ = 'Message' WHERE ID_ ='1494487013470261248';
UPDATE portal_sys_menu SET ALIAS_ = 'EmailCenter' WHERE ID_ ='1441938698676129792';
UPDATE portal_sys_menu SET ALIAS_ = 'AppCenter' WHERE ID_ ='1441941148611039232';

-- 2022-9-23 liangjc 表单设计、用户管理、组织管理 tab标签样式与其他模块保持一致
update portal_sys_menu set TABS_STYLE_='' where ID_ in  ('3391046','3441027','3441023');

-- jjx 2022-10-08 租户域名
ALTER TABLE UC_TENANT_MANAGE ADD BASE_URL_ NVARCHAR2(128);
COMMENT ON COLUMN UC_TENANT_MANAGE.BASE_URL_ IS '租户域名'

-- 2022-10-8 liangjc 数据视图 增加汇总区域字段
ALTER TABLE FORM_QUERY_VIEW ADD SUMMARY_REGION_HTML_ CLOB;
COMMENT ON COLUMN FORM_QUERY_VIEW.SUMMARY_REGION_HTML_ IS '汇总区域';

-- 2022-10-12 liangjc 修改人员脚本getUserById入参
update bpm_multi_script set ARGUMENT_='[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"ID"}]'  where ID_=1338377219818524672;

-- jjx 2022-10-08 删除栏目中的不兼容的配置
DELETE FROM PORTAL_SYS_COLUMN WHERE ID = '1267788861858254848';
DELETE FROM PORTAL_SYS_COLUMN WHERE ID = '1267788537730830336'

-- jjx 2022-10-19 修改人员脚本内提示信息
UPDATE bpm_multi_script SET  ARGUMENT_ = '[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"组织ID","paraCt":"base:org-selector","paraCtBindKey":"code"},{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"角色别名","paraCt":"base:role-selector","paraCtBindKey":"code"}]' WHERE ID_ = '1338367741324824576';

UPDATE bpm_multi_script SET ARGUMENT_ = '[{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"汇报线类型编码","paraCt":"","paraCtBindName":"ID_","paraCtBindKey":"","multiSelect":false}]' WHERE ID_ = '12';

UPDATE bpm_multi_script SET  ARGUMENT_ = '[{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"汇报线类型编码","paraCt":"","paraCtBindName":"ID_","paraCtBindKey":"","multiSelect":false}]' WHERE ID_ = '13';

UPDATE bpm_multi_script SET METHOD_DESC_ = '获取逐级审批(获取主负责人)' WHERE ID_ = '1338375124944031744';

UPDATE bpm_multi_script SET METHOD_DESC_ = 'sql查询用户ID(固定值SQL要用\" \"包裹)' WHERE ID_ = '1338379764150439936';

delete from bpm_multi_script where ID_ = '1338384186821709824';

UPDATE bpm_multi_script SET METHOD_DESC_ = '获取上一节点用户的上级部门（主）的负责人' WHERE ID_ = '16';
UPDATE bpm_multi_script SET METHOD_DESC_ = '获取上一点节用户所在部门的（主）负责人'  WHERE ID_ = '18';
UPDATE bpm_multi_script SET METHOD_DESC_ = '通过下属管理获取上一节点用户的上级' WHERE ID_ = '22';

UPDATE bpm_multi_script SET  ARGUMENT_ = '[{"paraName":"arg0","paraType":"java.lang.Boolean","paraDesc":"是否主负责人(值为true或false)","paraCt":"null","$$hashKey":"object:2340","multiSelect":false},{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"维度编码（不传为默认维度）","multiSelect":false,"paraCt":"base:dem-selector","paraCtBindKey":"code","paraCtBindName":"code"}]
' WHERE ID_ = '16';

UPDATE bpm_multi_script SET  ARGUMENT_ = '[{"paraName":"arg0","paraType":"java.lang.Boolean","paraDesc":"是否主负责人(值为true或false)","paraCt":"","$$hashKey":"object:1955","multiSelect":false},{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"维度编码（不传为默认维度）","multiSelect":false,"paraCt":"base:dem-selector","paraCtBindKey":"code","paraCtBindName":"code"}]' WHERE ID_ = '17';

UPDATE bpm_multi_script SET  ARGUMENT_ = '[{"paraName":"arg0","paraType":"boolean","paraDesc":"是否主负责人(值为true或false)","paraCt":"","$$hashKey":"object:1387","multiSelect":false},{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"维度编码（不传为默认维度）","multiSelect":false,"paraCt":"base:dem-selector","paraCtBindKey":"code","paraCtBindName":"code"}]' WHERE ID_ = '18';

UPDATE BPM_MULTI_SCRIPT SET  METHOD_DESC_ = '获取上一节点用户的上级部门（主）的负责人(主组织生效）' WHERE ID_ = '16';


-- 2022-10-25 jjx 修改字段长度
alter table BPM_EXE_STACK modify TARGET_NODE_ VARCHAR2(200);
alter table BPM_INST_DELETE_LOG modify CONTENT_ VARCHAR2(550);

-- 2022-10-26 liangjc修改发布新闻公告和轮播图到栏目默认高度
update PORTAL_NEWS_TREE_ set COL_HEIGHT_=432 where COL_HEIGHT_ in (100,0);
-- 2022-10-27 jjx修改菜单名称
UPDATE "PORTAL_SYS_MENU" SET  "NAME_" = '工具管理' WHERE "ALIAS_" = 'synergy';
UPDATE "PORTAL_SYS_MENU" SET  "NAME_" = '协同办公' WHERE "ALIAS_" = 'portalNewsNoticeManager';

-- 2022-10-28 liangjc portal_news_notice_ 字段FILE_增加长度
alter table portal_news_notice_ rename column FILE_  to name_tmp;
alter table portal_news_notice_ add FILE_ CLOB;
update portal_news_notice_ set FILE_=trim(name_tmp);
alter table portal_news_notice_ drop column name_tmp;

-- 2022-10-28 liangjc增加新闻公告已阅待阅表
CREATE TABLE portal_news_notice_read (
  ID_ NVARCHAR2(64) NOT NULL,
  PORTAL_NEWS_NOTICE_ID_ NVARCHAR2(64) NOT NULL,
  USER_ID_ NVARCHAR2(64),
  TENANT_ID_ NVARCHAR2(64),
  PRIMARY KEY (ID_)
);
CREATE INDEX IDX_PORTAL_NEWS_NOTICE_ID
  ON portal_news_notice_read (PORTAL_NEWS_NOTICE_ID_);
CREATE INDEX IDX_USER_ID
  ON portal_news_notice_read (USER_ID_);
COMMENT ON COLUMN portal_news_notice_read.PORTAL_NEWS_NOTICE_ID_ IS '新闻id';
COMMENT ON COLUMN portal_news_notice_read.USER_ID_ IS '用户id';
COMMENT ON TABLE portal_news_notice_read IS '新闻阅读记录';

-- 2022-11-1 jjx 栏目 增加分类
INSERT INTO "PORTAL_SYS_TYPE"("ID_", "TYPE_GROUP_KEY_", "NAME_", "TYPE_KEY_", "STRU_TYPE_", "PARENT_ID_", "DEPTH_", "PATH_", "IS_LEAF_", "OWNER_ID_", "SN_", "CREATE_BY_", "CREATE_TIME_", "CREATE_ORG_ID_", "UPDATE_BY_", "UPDATE_TIME_", "ICON_", "TENANT_ID_") VALUES ('1565643948817477632', 'INDEX_COLUMN_TYPE', '应用端', 'yyd', '0', '2', '1', '2.1587717367729848320.', 'Y', '0', '0', '1', TO_DATE('2022-10-31 14:56:35', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL, NULL, NULL, '-1');

INSERT INTO "PORTAL_SYS_TYPE"("ID_", "TYPE_GROUP_KEY_", "NAME_", "TYPE_KEY_", "STRU_TYPE_", "PARENT_ID_", "DEPTH_", "PATH_", "IS_LEAF_", "OWNER_ID_", "SN_", "CREATE_BY_", "CREATE_TIME_", "CREATE_ORG_ID_", "UPDATE_BY_", "UPDATE_TIME_", "ICON_", "TENANT_ID_") VALUES ('1565643975514222592', 'INDEX_COLUMN_TYPE', '管理端', 'gld', '0', '2', '1', '2.1587717401984729088.', 'Y', '0', '0', '1', TO_DATE('2022-10-31 14:56:35', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL, NULL, NULL, '-1');

INSERT INTO "PORTAL_SYS_TYPE"("ID_", "TYPE_GROUP_KEY_", "NAME_", "TYPE_KEY_", "STRU_TYPE_", "PARENT_ID_", "DEPTH_", "PATH_", "IS_LEAF_", "OWNER_ID_", "SN_", "CREATE_BY_", "CREATE_TIME_", "CREATE_ORG_ID_", "UPDATE_BY_", "UPDATE_TIME_", "ICON_", "TENANT_ID_") VALUES ('1586975403547820032', 'INDEX_COLUMN_TYPE', '移动端', 'ydd', '0', '2', '1', '2.1587717439670550528.', 'Y', '0', '0', '1', TO_DATE('2022-10-31 14:56:35', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL, NULL, NULL, '-1');

-- 2022-10-26 jason 驳回的节点
alter table BPM_PRO_STATUS
    add RECYCLE_NODE_ VARCHAR2(2) default '0';

comment on column BPM_PRO_STATUS.RECYCLE_NODE_ is '驳回按流程图执行回收的节点 0默认值  1因为驳回按照流程图执行目标节点之后的节点为1，如果重新运行到该节点变为0';

-- 2022-11-2 jjx  轮播图长度修改	
alter table PORTAL_NEWS_NOTICE_   modify ROTATING_DISPLAY_PICTURES_ VARCHAR2(550);

-- 2022-11-2 liangjc 增加 发布菜单源与菜单关联关系
CREATE TABLE portal_sys_auth_menu (
  ID_ NVARCHAR2(64) NOT NULL,
  AUTHORIZE_ID_ NVARCHAR2(64),
  MENU_ID_ NVARCHAR2(64),
  TENANT_ID_ NVARCHAR2(64),
  PRIMARY KEY (ID_)
);
COMMENT ON COLUMN portal_sys_auth_menu.AUTHORIZE_ID_ IS '发布菜单源主表ID';
COMMENT ON COLUMN portal_sys_auth_menu.MENU_ID_ IS '菜单id';
COMMENT ON TABLE portal_sys_auth_menu IS '发布菜单源与菜单关联关系';


-- 2022-11-6 jjx portal_sys_column别名字段索引改为唯一索引
drop index ALIAS_TENANT_ID__INDEX;
CREATE INDEX "ALIAS_TENANT_ID__INDEX"  ON "PORTAL_SYS_COLUMN" ("ALIAS", "TENANT_ID_");