-- # Put the your SQL below migration seperator.
-- !UP

create table if not exists users (
  id bigint generated by default as identity primary key,
  public_id varchar(90) unique,
  zitadel_id text unique,
  created_at timestamptz default current_timestamp,
  updated_at timestamptz default current_timestamp
  constraint public_id_length check (char_length(public_id) >= 3)
);

alter sequence users_id_seq restart with 10000;

alter table users enable row level security;

create policy "all user profiles are viewable by everyone." on users
for select using (true);

create policy "users can insert their own profile." on users
for insert with check (public.get_user_uid() = zitadel_id);

create policy "users can update own profile." on users
for update using (public.get_user_uid() = zitadel_id);

create or replace function get_current_user_id()
returns bigint
language plpgsql
as $$
  declare user_id bigint;
  begin
    select id into user_id from users where zitadel_id = get_user_uid();
    return user_id;
  end;
$$;

create trigger tr_mod_updated_at
before update on users
for each row
execute function moddatetime(updated_at);

-- seed the system user
insert into users (id, public_id, zitadel_id)
values ('1', 'system', '00000000-0000-0000-0000-000000000000');

-- !DOWN
drop trigger if exists tr_mod_updated_at on users;
drop function if exists get_local_user_uid;
drop table if exists users;
