-- 增加 实例用户权限表
DROP TABLE IF EXISTS instance_user_permission;
CREATE TABLE instance_user_permission
(
    instance_key int8 NOT NULL,
    element_id   varchar(64) NOT NULL,
    user_id    varchar(64) ,
    operation    varchar(64),
    source_type  int4,
    created_time timestamp(6),
    updated_time timestamp(6),
    CONSTRAINT "instance_element_user_key" UNIQUE ("instance_key", "element_id", "user_id")
);

COMMENT ON COLUMN instance_user_permission.instance_key IS '实例ID';
COMMENT ON COLUMN instance_user_permission.element_id IS '节点ID';
COMMENT ON COLUMN instance_user_permission.user_id IS '审批人';
COMMENT ON COLUMN instance_user_permission.operation IS '操作';
COMMENT ON COLUMN instance_user_permission.source_type IS '来源类型 1.流程指定 2.创建时指定 3.指派 4.转办';
COMMENT ON COLUMN instance_user_permission.created_time IS '创建时间';
COMMENT ON COLUMN instance_user_permission.updated_time IS '更新时间';
COMMENT ON TABLE  instance_user_permission IS '实例用户权限';


DROP TABLE IF EXISTS process_instance_job_user;
CREATE TABLE "process_instance_job_user"
(
    "instance_key" int8,
    "job_user_id"  varchar(64),
    "job_key"      int8,
    "user_id"      varchar(64),
    "operation"    varchar(64),
    "state"        varchar(8),
    "created_time" timestamp(6),
    "updated_time" timestamp(6),
    "source"       varchar(64)
);
COMMENT ON COLUMN "process_instance_job_user"."instance_key" IS '实例ID';
COMMENT ON COLUMN "process_instance_job_user"."job_user_id" IS '任务用户ID';
COMMENT ON COLUMN "process_instance_job_user"."job_key" IS '任务ID';
COMMENT ON COLUMN "process_instance_job_user"."user_id" IS '用户ID';
COMMENT ON COLUMN "process_instance_job_user"."operation" IS '操作权限';
COMMENT ON COLUMN "process_instance_job_user"."state" IS '状态 0 未处理 1 已处理状态';
COMMENT ON COLUMN "process_instance_job_user"."created_time" IS '创建时间';
COMMENT ON COLUMN "process_instance_job_user"."updated_time" IS '更新时间';
COMMENT ON COLUMN "process_instance_job_user"."source" IS '来源';





-- 增加 消息场景表
DROP TABLE IF EXISTS message_send_queue;
CREATE TABLE "message_send_queue"
(
    "message_queue_id"     varchar(64) NOT NULL,
    "user_id"              varchar(64),
    "message_model_number" varchar(64),
    "notify_way"           varchar(64),
    "message_type"         varchar(32),
    "title"                varchar(255),
    "msg_info"             varchar(255),
    "template_value"       text,
    "message_template"     text,
    "image_model"          text,
    "model_file"           text,
    "action_model"         varchar(255),
    "phone_number"         varchar(64),
    "reason_type"          varchar(64),
    "state"                varchar(32),
    "retry_time"           int4,
    "created_time"         timestamp(6),
    "updated_time"         timestamp(6),
    CONSTRAINT "message_send_queue_pkey" PRIMARY KEY ("message_queue_id")
);

COMMENT ON COLUMN "message_send_queue"."message_queue_id" IS '消息队列ID';
COMMENT ON COLUMN "message_send_queue"."user_id" IS '用户ID';
COMMENT ON COLUMN "message_send_queue"."message_model_number" IS '模板编号';
COMMENT ON COLUMN "message_send_queue"."notify_way" IS '通知方式(1-站内信, 2-邮件, 3-短信)';
COMMENT ON COLUMN "message_send_queue"."message_type" IS '消息类型 (1-文本消息, 2-图片消息, 3-动作消息) ';
COMMENT ON COLUMN "message_send_queue"."title" IS '消息标题';
COMMENT ON COLUMN "message_send_queue"."msg_info" IS '消息内容';
COMMENT ON COLUMN "message_send_queue"."template_value" IS '参数值';
COMMENT ON COLUMN "message_send_queue"."message_template" IS '消息模板';
COMMENT ON COLUMN "message_send_queue"."image_model" IS '图片模板';
COMMENT ON COLUMN "message_send_queue"."model_file" IS '文件模板';
COMMENT ON COLUMN "message_send_queue"."action_model" IS '动作模版值';
COMMENT ON COLUMN "message_send_queue"."phone_number" IS '手机号';
COMMENT ON COLUMN "message_send_queue"."reason_type" IS '消息发送原因类型';
COMMENT ON COLUMN "message_send_queue"."state" IS '状态 1.未发送 2.已发送';
COMMENT ON COLUMN "message_send_queue"."retry_time" IS '重试次数';
COMMENT ON COLUMN "message_send_queue"."created_time" IS '创建时间';
COMMENT ON COLUMN "message_send_queue"."updated_time" IS '更新时间';
COMMENT ON TABLE "message_send_queue" IS '消息队列表';





-- 增加 消息发送队列表
DROP TABLE IF EXISTS message_send_queue;
CREATE TABLE message_send_queue
(
    message_queue_id     varchar(64) NOT NULL,
    user_id              varchar(64),
    message_model_number varchar(64),
    notify_way           varchar(64),
    message_type         varchar(32),
    title                varchar(255),
    msg_info             varchar(255),
    template_value       text,
    message_template     text,
    image_model          text,
    model_file           text,
    action_model         varchar(255),
    phone_number         varchar(64),
    reason_type          varchar(64),
    state                varchar(32),
    retry_time           int4,
    created_time         timestamp(6),
    updated_time         timestamp(6),
    CONSTRAINT message_send_queue_pkey PRIMARY KEY (message_queue_id)
)
;
COMMENT ON COLUMN message_send_queue.message_queue_id IS '消息队列ID';
COMMENT ON COLUMN message_send_queue.user_id IS '用户ID';
COMMENT ON COLUMN message_send_queue.message_model_number IS '模板编号';
COMMENT ON COLUMN message_send_queue.notify_way IS '通知方式(1-站内信, 2-邮件, 3-短信)';
COMMENT ON COLUMN message_send_queue.message_type IS '消息类型 (1-文本消息, 2-图片消息, 3-动作消息) ';
COMMENT ON COLUMN message_send_queue.title IS '消息标题';
COMMENT ON COLUMN message_send_queue.msg_info IS '消息内容';
COMMENT ON COLUMN message_send_queue.template_value IS '参数值';
COMMENT ON COLUMN message_send_queue.message_template IS '消息模板';
COMMENT ON COLUMN message_send_queue.image_model IS '图片模板';
COMMENT ON COLUMN message_send_queue.model_file IS '文件模板';
COMMENT ON COLUMN message_send_queue.action_model IS '动作模版值';
COMMENT ON COLUMN message_send_queue.phone_number IS '手机号';
COMMENT ON COLUMN message_send_queue.reason_type IS '消息发送原因类型';
COMMENT ON COLUMN message_send_queue.state IS '状态 1.未发送 2.已发送';
COMMENT ON COLUMN message_send_queue.retry_time IS '重试次数';
COMMENT ON COLUMN message_send_queue.created_time IS '创建时间';
COMMENT ON COLUMN message_send_queue.updated_time IS '更新时间';
COMMENT ON TABLE message_send_queue IS '消息队列表';





-- 新增传阅表 process_instance_cc_info
DROP TABLE IF EXISTS process_instance_cc_info;
CREATE TABLE process_instance_cc_info
(
    process_instance_cc_info_id varchar(64),
    instance_key int8,
    user_id      varchar(64),
    from_user_id varchar(64),
    element_id   varchar(64),
    created_time timestamp,
    is_read      int4,
    updated_time timestamp,
    CONSTRAINT process_instance_cc_info_pkey PRIMARY KEY (process_instance_cc_info_id)
);
COMMENT ON COLUMN process_instance_cc_info.process_instance_cc_info_id IS '传阅ID';
COMMENT ON COLUMN process_instance_cc_info.instance_key IS '实例Key';
COMMENT ON COLUMN process_instance_cc_info.user_id IS '用户ID';
COMMENT ON COLUMN process_instance_cc_info.from_user_id IS '来源用户ID';
COMMENT ON COLUMN process_instance_cc_info.element_id IS '节点ID';
COMMENT ON COLUMN process_instance_cc_info.created_time IS '创建时间';
COMMENT ON COLUMN process_instance_cc_info.is_read IS '是否已阅 0-未阅 1-已阅';
COMMENT ON COLUMN process_instance_cc_info.updated_time IS '更新时间';





-- 删除弃用的消息发送记录表 message_send_log
drop table message_send_log;





-- 流程信息表 增加字段 数据来源
ALTER TABLE process_info ADD COLUMN data_source varchar(8);
ALTER TABLE process_info ADD COLUMN free_cc varchar(8);
ALTER TABLE process_info ADD COLUMN opinion_required varchar(8);
COMMENT ON COLUMN process_info.data_source IS '数据来源';
COMMENT ON COLUMN process_info.free_cc IS '自由传阅';
COMMENT ON COLUMN process_info.opinion_required IS '审批意见 是否必填';
-- 流程信息表 刷数据(给历史数据 为 新增的字段 设置值)
update process_info set data_source = '1' where item_type in ('1', '2');
update process_info set data_source = '2' where item_type in ('5');





-- 流程历史信息表 增加字段 数据来源
ALTER TABLE process_info_history ADD COLUMN data_source varchar(8);
ALTER TABLE process_info_history ADD COLUMN free_cc varchar(8);
ALTER TABLE process_info_history ADD COLUMN opinion_required varchar(8);
COMMENT ON COLUMN process_info_history.data_source IS '数据来源';
COMMENT ON COLUMN process_info_history.free_cc IS '自由传阅';
COMMENT ON COLUMN process_info_history.opinion_required IS '审批意见 是否必填';
-- 流程历史信息表 刷数据(给历史数据 为 新增的字段 设置值)
update process_info_history set data_source = '1' where item_type in ('1', '2');
update process_info_history set data_source = '2' where item_type in ('5');





-- 流程节点表 增加字段 转办名称 传阅名称 指派名称
ALTER TABLE process_node_item ADD COLUMN transfer_name varchar(255);
ALTER TABLE process_node_item ADD COLUMN cc_name varchar(255);
ALTER TABLE process_node_item ADD COLUMN assign_name varchar(255);
ALTER TABLE process_node_item ADD COLUMN assign_element varchar(255);
COMMENT ON COLUMN process_node_item.transfer_name IS '转办名称';
COMMENT ON COLUMN process_node_item.cc_name IS '传阅名称';
COMMENT ON COLUMN process_node_item.assign_name IS '指派名称';
COMMENT ON COLUMN process_node_item.assign_element IS '指派节点';


ALTER TABLE process_instance_info ADD COLUMN notified int4;
COMMENT ON COLUMN process_instance_info.notified IS '是否已通知';

DROP TABLE IF EXISTS message_scene_info;
CREATE TABLE "message_scene_info" (
                                      "message_scene_id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
                                      "message_scene_key" varchar(64) COLLATE "pg_catalog"."default",
                                      "message_title" varchar(255) COLLATE "pg_catalog"."default",
                                      "notify_way" varchar(64) COLLATE "pg_catalog"."default",
                                      "message_type" varchar(32) COLLATE "pg_catalog"."default",
                                      "message_model_number" varchar(64) COLLATE "pg_catalog"."default",
                                      "message_template" text COLLATE "pg_catalog"."default",
                                      "image_model" text COLLATE "pg_catalog"."default",
                                      "model_file" text COLLATE "pg_catalog"."default",
                                      "action_model" text COLLATE "pg_catalog"."default",
                                      "state" varchar(32) COLLATE "pg_catalog"."default",
                                      "created_by" varchar(64) COLLATE "pg_catalog"."default",
                                      "created_time" timestamp(6),
                                      "updated_by" varchar(64) COLLATE "pg_catalog"."default",
                                      "updated_time" timestamp(6),
                                      CONSTRAINT "message_scene_info_pkey" PRIMARY KEY ("message_scene_id")
)
;
COMMENT ON COLUMN "message_scene_info"."message_scene_id" IS '消息场景ID';
COMMENT ON COLUMN "message_scene_info"."message_scene_key" IS '消息场景KEY';
COMMENT ON COLUMN "message_scene_info"."message_title" IS '消息标题';
COMMENT ON COLUMN "message_scene_info"."notify_way" IS '通知方式(1-站内信, 2-邮件, 3-短信)';
COMMENT ON COLUMN "message_scene_info"."message_type" IS '消息类型 (1-文本消息, 2-图片消息, 3-动作消息) ';
COMMENT ON COLUMN "message_scene_info"."message_model_number" IS '消息中心模板编号';
COMMENT ON COLUMN "message_scene_info"."message_template" IS '模板内容';
COMMENT ON COLUMN "message_scene_info"."image_model" IS '图片模版值 ';
COMMENT ON COLUMN "message_scene_info"."model_file" IS '模版文件 ';
COMMENT ON COLUMN "message_scene_info"."action_model" IS '动作模版值';
COMMENT ON COLUMN "message_scene_info"."state" IS '状态 0启动 1禁用';
COMMENT ON COLUMN "message_scene_info"."created_by" IS '创建人';
COMMENT ON COLUMN "message_scene_info"."created_time" IS '创建时间';
COMMENT ON COLUMN "message_scene_info"."updated_by" IS '更新人';
COMMENT ON COLUMN "message_scene_info"."updated_time" IS '更新时间';
COMMENT ON TABLE "message_scene_info" IS '消息场景信息';

ALTER TABLE "zeebe_variable"
ALTER COLUMN "value_" TYPE text COLLATE "pg_catalog"."default" USING "value_"::text;