/**
* This trigger automatically creates a user entry when a new user signs up via Supabase Auth.
*/
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, avatar_url, full_name)
  values (new.id, new.raw_user_meta_data->>'avatar_url', new.raw_user_meta_data->>'full_name');
  return new;
end;
$$ language plpgsql security definer;

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

/**
* This trigger automatically creates a nofication entry when a user follow other user.
*/
create or replace function public.handle_new_follower()
returns trigger as $$
begin
  insert into public.notifications (target_id, type, payload, source_id)
  values (
    new.following_id,
    'follow',
    (select row_to_json(c) from (select b.username, b.avatar_url from followers a left join profiles b on a.user_id = b.id where a.user_id = new.user_id limit 1) c),
    new.user_id
  );
  return new;
end;
$$ language plpgsql security definer;

/**
* This function populate the story with tagging details.
*/
create or replace function get_stories (user_name text)
  returns table (id uuid, date date, story text, tagging uuid[], user_id uuid, image text, created_at timestamp, tags json)
  language plpgsql
  as
  $$
    begin
    return query
      select a.*, json_agg(b) as tags from stories a left join tags b on b.id = any(a.tagging) where a.user_id = (select c.id from profiles c where c.username = user_name) group by a.id;
    end;
  $$

/**
* This function populate the story with filtered tagging details.
*/
create or replace function get_tagged_stories (user_name text, tag_id uuid)
  returns table (id uuid, date date, story text, tagging uuid[], user_id uuid, image text, created_at timestamp, tags json)
  language plpgsql
  as
  $$
    begin
    return query
      select a.*, json_agg(b) as tags from stories a left join tags b on b.id = any(a.tagging) where a.user_id = (select c.id from profiles c where c.username = user_name) and b.id = tag_id group by a.id;
    end;
  $$

/**
* This function returns the stories of the users you are following. Used in '/home'.
*/
create or replace function get_following_stories ()
  returns table (id uuid, date date, story text, tagging uuid[], user_id uuid, image text, created_at timestamp, tags json, user_data json)
  language plpgsql
  as
  $$
    begin
    return query
      select d.*, row_to_json(e) as user_data from profiles e right join
      (select a.*, json_agg(b) as tags
        from stories a
        left join tags b
        on b.id = any(a.tagging)
        where a.user_id in (select c.following_id from followers c where c.user_id = auth.uid())
        group by a.id
        order by created_at desc
      ) d
      on e.id = d.user_id
        ;
    end;
  $$

/**
* This function returns profiles that you should follow. WIP
*/
create or replace function get_suggested_user()
  returns setof profiles
  language plpgsql
  as
  $$
    begin
    return query
      select * from profiles where id <> auth.uid() order by random();
    end;
  $$