setup_rpc.sql

-- 1. Ensure the table exists and has the right columns
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
);

-- Add columns if they are missing (idempotent)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'full_name') THEN
        ALTER TABLE public.users ADD COLUMN full_name text;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'avatar_url') THEN
        ALTER TABLE public.users ADD COLUMN avatar_url text;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'credits') THEN
        ALTER TABLE public.users ADD COLUMN credits integer default 5;
    END IF;
END $$;

-- 2. Create a Secure RPC Function to Handle Login
-- This function runs with 'SECURITY DEFINER' privileges, meaning it bypasses RLS.
-- This allows us to safely insert/update the user record without needing the Service Role Key in the frontend.

create or replace function public.handle_user_login(
  user_email text,
  user_full_name text,
  user_avatar_url text
)
returns void
language plpgsql
security definer -- CRITICAL: Runs as database owner
as $$
declare
  current_user_id uuid;
begin
  -- Get the ID of the currently authenticated user invoking this function
  current_user_id := auth.uid();

  if current_user_id is null then
    raise exception 'Not authenticated';
  end if;

  -- Insert or Update the user record
  insert into public.users (id, email, full_name, avatar_url, credits)
  values (
    current_user_id,
    user_email,
    user_full_name,
    user_avatar_url,
    5 -- Default credits for new users
  )
  on conflict (id) do update set
    email = excluded.email,
    full_name = excluded.full_name,
    avatar_url = excluded.avatar_url,
    -- We DO NOT reset credits on login, only update profile info
    credits = public.users.credits; 
end;
$$;

-- 3. Grant permission to call this function
grant execute on function public.handle_user_login to authenticated;