-- ✅ Presence + Online users
-- Run in Supabase SQL Editor

-- 1) Add columns
alter table if exists public.profiles
  add column if not exists last_seen timestamptz,
  add column if not exists current_page text;

-- 2) RLS: user can update his own profile (needed for presence)
alter table public.profiles enable row level security;

-- Allow select own profile (if you don't already have it)
do $$
begin
  if not exists (
    select 1 from pg_policies
    where schemaname='public' and tablename='profiles' and policyname='profiles_select_own'
  ) then
    create policy profiles_select_own on public.profiles
      for select
      using (auth.uid() = id);
  end if;
end $$;

-- Allow update own profile (presence)
do $$
begin
  if not exists (
    select 1 from pg_policies
    where schemaname='public' and tablename='profiles' and policyname='profiles_update_own'
  ) then
    create policy profiles_update_own on public.profiles
      for update
      using (auth.uid() = id)
      with check (auth.uid() = id);
  end if;
end $$;

-- Admin can read all profiles (optional)
do $$
begin
  if not exists (
    select 1 from pg_policies
    where schemaname='public' and tablename='profiles' and policyname='profiles_select_admin'
  ) then
    create policy profiles_select_admin on public.profiles
      for select
      using (exists (
        select 1 from public.profiles p
        where p.id = auth.uid() and lower(coalesce(p.role,''))='admin'
      ));
  end if;
end $$;

-- 3) Online view (last 5 minutes)
create or replace view public.v_online_users as
select
  id,
  full_name,
  role,
  current_page,
  last_seen
from public.profiles
where last_seen is not null
  and last_seen >= now() - interval '5 minutes'
order by last_seen desc;