-- Create the users table if it doesn't exist
create table if not exists public.users (
  id uuid references auth.users on delete cascade not null primary key,
  email text not null,
  name text,
  role text default 'client',
  username text,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Add unique constraint to username
alter table public.users add constraint users_username_key unique (username);

-- Enable Row Level Security
alter table public.users enable row level security;

-- Create RLS policies
create policy "Clients can view their own data"
on public.users for select
using (auth.uid() = id);

create policy "Clients can update their own data"
on public.users for update
using (auth.uid() = id);

create policy "Admins can view all data"
on public.users for select
using (
  auth.uid() = id or
  (select role from public.users where id = auth.uid()) = 'admin'
);

create policy "Admins can update all data"
on public.users for update
using (
  auth.uid() = id or
  (select role from public.users where id = auth.uid()) = 'admin'
);

-- Drop existing trigger and function if they exist
drop trigger if exists on_auth_user_created on auth.users;
drop function if exists public.handle_new_user();

-- Create the trigger function to handle new user creation
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.users (id, email, name, role)
  values (
    new.id,
    new.email,
    coalesce(new.raw_user_meta_data->>'full_name', new.email),
    'client'  -- Set default role to 'client'
  );
  return new;
end;
$$ language plpgsql security definer;

-- Create the trigger
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

-- Create function to add role to JWT
create or replace function public.add_custom_role_to_jwt(event jsonb)
returns jsonb
language plpgsql
as $$
declare
  user_role text;
begin
  select role into user_role from public.users where id = event->>'user_id';
  return jsonb_set(event, '{user_role}', to_jsonb(user_role));
end;
$$;

-- Insert any existing auth users that don't have a corresponding public.users record
insert into public.users (id, email, name, role)
select 
    a.id,
    a.email,
    coalesce(a.raw_user_meta_data->>'full_name', a.email) as name,
    'client' as role
from auth.users a
left join public.users p on a.id = p.id
where p.id is null; 