setup_triggers_robust.sql

-- 1. Create the public.users table (Idempotent)
create table if not exists public.users (
  id uuid references auth.users on delete cascade not null primary key,
  email text,
  full_name text,
  avatar_url text,
  credits integer default 5,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- 2. Enable RLS
alter table public.users enable row level security;

-- 3. Create Policies (Secure: Only allow users to view/update their own data)
create policy "Users can view own profile" 
on public.users for select 
using (auth.uid() = id);

create policy "Users can update own profile" 
on public.users for update 
using (auth.uid() = id);

-- NOTE: We intentionally DO NOT create an INSERT policy for users.
-- Insertions will be handled by the Trigger (System Level).

-- 4. Create the Trigger Function
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.users (id, email, full_name, avatar_url, credits)
  values (
    new.id,
    new.email,
    new.raw_user_meta_data->>'full_name',
    new.raw_user_meta_data->>'avatar_url',
    5 -- Default credits
  );
  return new;
end;
$$ language plpgsql security definer;

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

-- 6. Backfill existing users (Fix for current broken state)
insert into public.users (id, email, full_name, avatar_url, credits)
select 
    id, 
    email, 
    raw_user_meta_data->>'full_name', 
    raw_user_meta_data->>'avatar_url', 
    5
from auth.users
where id not in (select id from public.users);