alter table identity_userpassword add column old_user_name varchar(50);
update identity_userpassword set old_user_name = user_name;

alter table identity_user
add column idnum varchar(50) not null,
add constraint unique_identity_user_idnum unique (idnum);
DROP INDEX `unique_identity_user_telephone`,
MODIFY COLUMN `telephone`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL AFTER `name`;
ALTER TABLE `tenant_users` DROP INDEX `unique_tenant_user_telephone`;


INSERT INTO `tenant_user_credentials` (`id`, `created_time`, `address`, `credential_type`, `front_photo`, `number`, `reverse_photo`, `valid_date_from`, `valid_date_to`, `tenant_user_id`, `head_photo`, `name`, `selected`)
select CONCAT(replace(replace(`id`,'--__qFCcCnozYNwzoG3O6Ms',''),'--__XQfjpd9pI9QLFaMYoYm',''),FLOOR(RAND() * 1000)), `created_time`, null, '0', null, telephone, null, null, null, id, null, `name`, '1'
from tenant_users t1 where selected_credential_id is null and telephone != 'null'
and not EXISTS( select 1 from tenant_user_credentials t2 where t1.telephone= t2.number);


update t_user t1,tenant_employees t2,tenant_users t3,tenant_user_credentials t4
set t1.LOGINNO = t4.number,t1.name = t4.name,t3.name = t4.name
where t1.id = t2.id and t2.tenant_user_id = t3.id and t3.selected_credential_id = t4.id;

DROP TABLE IF EXISTS tmp_identity_user_id;
create table tmp_identity_user_id as select * from (
select t3.id from identity_userpassword t1,identity_principals t2,identity_user t3
where t1.id = t2.id and t2.identity_user_id = t3.id
group by t3.id HAVING count(1)>1
) as table_sql;

update tmp_identity_user_id tt,identity_user t0,identity_userpassword t1,identity_principals t2
set t1.user_name = CONCAT(t1.user_name,"_del")
where tt.id = t0.id
and t2.identity_user_id = t0.id
and t1.id = t2.id
and  t1.user_name = t0.telephone;


DROP TABLE IF EXISTS tmp_identity_userpassword_number;
create table tmp_identity_userpassword_number as select * from (
select DISTINCT t2.number from identity_userpassword t1,tenant_user_credentials t2,identity_principals t3
where t1.user_name = t2.number and t1.id = t3.id
) as sql_table;


ALTER TABLE `tmp_identity_userpassword_number`
ADD INDEX `index_number` (`number`) ;



DROP TABLE IF EXISTS tmp_identity_userpassword_identity;
create table tmp_identity_userpassword_identity as select * from (
select DISTINCT t3.identity_user_id from identity_userpassword t1,tenant_user_credentials t2,identity_principals t3
where t1.user_name = t2.number and t1.id = t3.id
) as sql_table;

ALTER TABLE `tmp_identity_userpassword_identity`
ADD INDEX `index_identity_user_id` (`identity_user_id`) ;


ALTER TABLE `identity_user`
ADD INDEX `index_idnum` (`idnum`) ;



update identity_user t1,tmp_identity_userpassword_identity t2 set t1.is_exist = 1
where t1.id = t2.identity_user_id;


update identity_user t1,tmp_identity_userpassword_number t2 set t1.is_exist = 1
where t1.idnum = t2.number;


DROP TABLE IF EXISTS tmp_identity_user_exist;
create table tmp_identity_user_exist as select * from (
select t1.idnum
from identity_user t1,identity_principals t2,identity_userpassword t3
where t1.is_exist is null
and t1.id = t2.identity_user_id
and t2.id = t3.id
group by t1.idnum having count(1)>1
)as sql_table;


update identity_user t1,identity_principals t2,identity_userpassword t3
set t3.user_name = t1.idnum
where t1.is_exist is null
and t1.id = t2.identity_user_id
and t2.id = t3.id
and t1.idnum not in (select idnum from tmp_identity_user_exist);


DROP TABLE IF EXISTS tmp_identity_userpassword_del;
create table tmp_identity_userpassword_del as select * from (
select * from identity_userpassword where user_name like '%del'
) as sql_table;


ALTER TABLE `tmp_identity_userpassword_del`
ADD UNIQUE INDEX `index_id` (`id`) ;



delete from identity_userpassword where user_name like '%del';
delete from identity_principals where id in (select id from tmp_identity_userpassword_del);


update tenant_users t1,tenant_user_credentials t2
set t2.number = t1.telephone
where t1.selected_credential_id = t2.id
and t2.number is null
and t2.credential_type=0
and t1.telephone !='null';