INSERT INTO public.identity_user(id, created_time, name, telephone, tenant_user_id)
select
  max_id,max_lastmodifytime,max_name,max_loginno,max_id
from (
       select max(id) as max_id,COALESCE(max(a.LASTMODIFYTIME),'2021-01-01') as max_lastmodifytime,max(a.NAME) as max_name,
              LOGINNO as max_loginno from t_user a
       where REPLACE(COALESCE(LOGINNO,'0'),'','0')<>'0'
       group by LOGINNO
     ) as t where max_loginno not in (select telephone from identity_user);


insert into public.identity_principals(id,created_time,status,identity_user_id,last_updated_time)
select
  a.id,'2021-01-01',
  (case when COALESCE(b.LOCKFLAG,1)=0 or COALESCE(b.STATUS,1)=0 or COALESCE(b.DIMISSION,1)=0 then 2 else 0 end),
  a.id,
  a.created_time
from identity_user a join t_user b on a.id = b.ID and a.id not in (select id from identity_principals);

insert into public.identity_userpassword(id,user_name,password,algorithm_type)
select
  a.id,b.LOGINNO,b.LOGINPWD,(case when b.PWDTYPE='SM2' then 0 else 1 end) as PWDTYPE
from identity_principals a join t_user b on a.id = b.ID and a.id not in (select id from identity_userpassword);



CREATE OR REPLACE FUNCTION SUBSTRING_INDEX(varchar, varchar, integer)
            RETURNS varchar AS $$
            DECLARE
            tokens varchar[];
length integer ;
indexnum integer;
BEGIN
  tokens := pg_catalog.string_to_array($1, $2);
length := pg_catalog.array_upper(tokens, 1);
indexnum := length - ($3 * -1) + 1;
IF $3 >= 0 THEN
RETURN pg_catalog.array_to_string(tokens[1:$3], $2);
ELSE
RETURN pg_catalog.array_to_string(tokens[indexnum:length], $2);
END IF;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;