create or replace function generate_passport_no()
returns text
language plpgsql
volatile
as $$
declare
  letters constant text := 'ABCDEFGHJKLMNPQRSTUVWXYZ';
  candidate text;
  i integer;
begin
  loop
    candidate := '';
    for i in 1..5 loop
      candidate := candidate || substr(letters, 1 + floor(random() * length(letters))::integer, 1);
    end loop;
    candidate := candidate || '-' || lpad(floor(random() * 100000)::integer::text, 5, '0');
    exit when not exists (select 1 from users where passport_no = candidate);
  end loop;
  return candidate;
end;
$$;

alter table users
  add column if not exists wechat_id text,
  add column if not exists last_login_at timestamptz;

create unique index if not exists idx_users_wechat_id
  on users(wechat_id)
  where wechat_id is not null and wechat_id <> '';

alter table users
  alter column passport_no drop default,
  alter column passport_no set default generate_passport_no();

do $$
declare
  item record;
  next_passport text;
begin
  for item in
    select id
    from users
    where passport_no is null
       or passport_no = ''
       or passport_no !~ '^[A-Z]{5}-[0-9]{5}$'
  loop
    loop
      next_passport := generate_passport_no();
      exit when not exists (
        select 1
        from users
        where passport_no = next_passport
          and id <> item.id
      );
    end loop;

    update users
    set passport_no = next_passport,
        updated_at = now()
    where id = item.id;
  end loop;
end;
$$;

alter table users
  alter column passport_no set not null;

create unique index if not exists idx_users_passport_no
  on users(passport_no);
