use obpm2;
create table identity_toc_oauth_subjects
(
    id varchar(100) not null primary key ,
    co_auth_type tinyint(2) not null default 0,
    union_id varchar(300) not null,
    open_id varchar(100) not null,
    sys_code  varchar(30) not null,
    nicky  varchar(50) not null,
    content varchar(500),
    status tinyint(2) not null default 0,
    identity_user_id varchar(100) not null default '#1',
    tenant_user_id varchar(100) not null default '#1',
    created_time timestamp not null default current_timestamp,
    assigned_time timestamp null default null
);
create UNIQUE  index idx_identity_toc_subject_type_union_open_id on identity_toc_oauth_subjects(co_auth_type,union_id,open_id);


use obpm2;
create table external_groups
(
    id varchar(50) not null primary key ,
    name varchar(200) not null,
    display_order smallint not null default 0,
    index_tree varchar(500) not null,
    parent_id  varchar(50) null,
    reference_type tinyint(2) not null default 0,
    reference_number varchar(200) not null,
    created_time timestamp not null default current_timestamp,
    assigned_time timestamp null default null
);

ALTER TABLE external_groups
    ADD CONSTRAINT fk_ref_external_groups_id
        FOREIGN KEY (parent_id) REFERENCES external_groups(id);

create table external_members
(
    id varchar(50) not null primary key ,
    note varchar(200) null,
    invite_code varchar(50) not null default 0,
    invite_type tinyint(2) not null default 0,
    group_ids_json json not null,
    tenant_user_id varchar(50) not null references tenant_users(id),

    approved_information_status tinyint(2) not null default 0,
    approved_information_note varchar(200)  null ,
    approved_information_last_updated_time timestamp null ,
    join_time timestamp not null default current_timestamp,
    deleted_time timestamp null ,
    operator_employee_id varchar(100)  null,
    operator_employee_name varchar(50)  null,
    reference_type tinyint(2) not null default 0,
    reference_number varchar(200) not null,
    created_time timestamp not null default current_timestamp,
    assigned_time timestamp null default null
);

ALTER TABLE external_groups add principal_id varchar(50) null;
ALTER TABLE external_groups
    ADD CONSTRAINT fk_ref_external_principal_id
        FOREIGN KEY (principal_id) REFERENCES external_members(id);

ALTER TABLE external_groups add principal_name varchar(50) null;

ALTER TABLE external_groups add  last_updated_time timestamp null;

alter table external_members add invite_group_id varchar(50) null;
alter table external_members add invite_group_name varchar(200) null;
alter table external_members add last_updated_time timestamp null;



create unique index PK_UNIQUE_DATA_ex_members_type_ref_tenant_user_id on external_members(reference_type,reference_number,tenant_user_id);


alter table external_members modify id varchar(100);
alter table external_groups modify principal_id varchar(100);
alter table external_members add member_type smallint not null default 1 COMMENT '人员类型';

/**
  修复旧数据: 组织也是负责人
 */
INSERT INTO `obpm2`.`external_members`
(`id`, `note`, `invite_code`, `invite_type`, `group_ids_json`, `tenant_user_id`, `approved_information_status`, `approved_information_note`, `approved_information_last_updated_time`, `join_time`, `deleted_time`, `operator_employee_id`, `operator_employee_name`, `reference_type`, `reference_number`, `created_time`, `assigned_time`, `invite_group_id`, `invite_group_name`, `last_updated_time`, `member_type`)
select CONCAT((case when INSTR(u.id,'--__')>0 then SUBSTR(u.id,1,INSTR(u.id,'--__')-1) else u.id end),
              '_',
              (case when INSTR(o.id,'--__')>0 then SUBSTR(o.id,1,INSTR(o.id,'--__')-1) else o.id end)
           ) AS ID,
       '自动创建成员','',2,'[]',u.id,1,'自动通过',o.created_time,o.created_time,null,null,null,0,o.id,o.created_time,o.created_time,null,null,o.created_time,0 from obpm2.tenant_users u join obpm2.tenant_organizations o on u.id=o.tenant_user_id
where o.id in (select cc.reference_number from obpm2.external_members cc ) and
        CONCAT((case when INSTR(u.id,'--__')>0 then SUBSTR(u.id,1,INSTR(u.id,'--__')-1) else u.id end),
               '_',
               (case when INSTR(o.id,'--__')>0 then SUBSTR(o.id,1,INSTR(o.id,'--__')-1) else o.id end)
            ) in ('t2sr4Vzy_3gdNhAB1','0jBUyJmv_8lKPnJ6L','qXOoJUbb_g3MhS8EN','REJHKayJ_IuhgAVJf','t2sr4Vzy_wPD7hh5G','t2sr4Vzy_XaxplKT0','qXOoJUbb_Y4pbLfge','t2sr4Vzy_ZcwgJ5Nm')

drop index PK_UNIQUE_DATA_ex_members_type_ref_tenant_user_id on external_members;
drop index PK_UNIQUE_DATA_EX_MEMBERS_TENANT_USER_ID_REF_NUM_TYPE on external_members;
create unique index FK_UNIQUE_DATA_EX_MEMBERS_TENANT_USER_ID_REF_NUM_TYPE on external_members(tenant_user_id,reference_number,reference_type);