-- Fase 3: perfiles publicos, conexiones (gym bro / entrenador / deportista alto rendimiento) y publicaciones.

alter table public.profiles add column is_public boolean not null default false;
alter table public.profiles add column bio text;

create table public.connections (
  id bigint generated always as identity primary key,
  requester_id uuid not null references auth.users (id) on delete cascade,
  addressee_id uuid not null references auth.users (id) on delete cascade,
  relationship_type text not null check (relationship_type in ('gym_bro', 'entrenador', 'deportista_alto_rendimiento')),
  status text not null default 'pending' check (status in ('pending', 'accepted', 'rejected', 'cancelled')),
  created_at timestamptz not null default now(),
  responded_at timestamptz,
  unique (requester_id, addressee_id)
);

create table public.posts (
  id bigint generated always as identity primary key,
  user_id uuid not null references auth.users (id) on delete cascade,
  content text not null,
  image_url text,
  visibility text not null default 'connections' check (visibility in ('public', 'connections')),
  created_at timestamptz not null default now()
);

-- Helper: dos usuarios estan conectados (solicitud aceptada, en cualquier direccion).
create or replace function public.are_connected(uid1 uuid, uid2 uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.connections c
    where c.status = 'accepted'
      and ((c.requester_id = uid1 and c.addressee_id = uid2)
        or (c.requester_id = uid2 and c.addressee_id = uid1))
  );
$$;

-- Helper: existe alguna solicitud entre ambos, sin importar el estado (pendiente, aceptada, etc).
-- Se usa para poder ver el nombre de quien te mando una solicitud, aunque todavia no la aceptes.
create or replace function public.has_connection_row(uid1 uuid, uid2 uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.connections c
    where (c.requester_id = uid1 and c.addressee_id = uid2)
       or (c.requester_id = uid2 and c.addressee_id = uid1)
  );
$$;

-- Helper: el perfil de un usuario es visible para quien pregunta (uno mismo, publico, o con solicitud entre ambos).
create or replace function public.profile_visible_to(target_user_id uuid, viewer_id uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select viewer_id = target_user_id
    or exists (select 1 from public.profiles p where p.id = target_user_id and p.is_public = true)
    or public.has_connection_row(viewer_id, target_user_id);
$$;

-- Directorio minimo para poder buscar gente y mandar solicitud, sin exponer perfiles completos
-- (la tabla profiles solo es legible si es publico, tuyo, o ya estas conectado).
create or replace function public.search_profiles(search_query text)
returns table (id uuid, display_name text)
language sql
stable
security definer
set search_path = public
as $$
  select p.id, p.display_name
  from public.profiles p
  where p.display_name ilike '%' || search_query || '%'
    and p.id != auth.uid()
  limit 20;
$$;

revoke all on function public.are_connected(uuid, uuid) from public;
revoke all on function public.has_connection_row(uuid, uuid) from public;
revoke all on function public.profile_visible_to(uuid, uuid) from public;
revoke all on function public.search_profiles(text) from public;
grant execute on function public.are_connected(uuid, uuid) to authenticated;
grant execute on function public.has_connection_row(uuid, uuid) to authenticated;
grant execute on function public.profile_visible_to(uuid, uuid) to authenticated;
grant execute on function public.search_profiles(text) to authenticated;

alter table public.connections enable row level security;
alter table public.posts enable row level security;

create policy "connections: involved parties can read" on public.connections
  for select using (auth.uid() = requester_id or auth.uid() = addressee_id);

create policy "connections: request as yourself" on public.connections
  for insert with check (auth.uid() = requester_id);

create policy "connections: requester or addressee can update" on public.connections
  for update using (auth.uid() = requester_id or auth.uid() = addressee_id)
  with check (auth.uid() = requester_id or auth.uid() = addressee_id);

create policy "posts: visible per profile visibility" on public.posts
  for select using (
    auth.uid() = user_id
    or (visibility = 'public' and public.profile_visible_to(user_id, auth.uid()))
    or (visibility = 'connections' and public.are_connected(auth.uid(), user_id))
  );

create policy "posts: owner write" on public.posts
  for insert with check (auth.uid() = user_id);

create policy "posts: owner update/delete" on public.posts
  for update using (auth.uid() = user_id) with check (auth.uid() = user_id);

create policy "posts: owner delete" on public.posts
  for delete using (auth.uid() = user_id);

-- Ampliar lectura de perfiles y logros del avatar a: dueno, perfiles publicos, o conexiones aceptadas.
create policy "profiles: public or connected read" on public.profiles
  for select using (public.profile_visible_to(id, auth.uid()));

create policy "user_badges: public or connected read" on public.user_badges
  for select using (public.profile_visible_to(user_id, auth.uid()));

create policy "user_artifacts: public or connected read" on public.user_artifacts
  for select using (public.profile_visible_to(user_id, auth.uid()));

create policy "avatar_equipment: public or connected read" on public.avatar_equipment
  for select using (public.profile_visible_to(user_id, auth.uid()));

create policy "points_events: public or connected read" on public.points_events
  for select using (public.profile_visible_to(user_id, auth.uid()));