create policy "Individuals can view their own todos." 
    on todos for select 
    using ( auth.uid() = user_id );

select *
from todos
where auth.uid() = todos.user_id; -- ポリシーが暗黙のうちに付加されます。

-- 1. テーブルを作成
create table profiles (
  id uuid references auth.users,
  avatar_url text
);

-- 2. RLSを有効化
alter table profiles
  enable row level security;

-- 3. ポリシーを作成
create policy "Public profiles are viewable by everyone."
  on profiles for select using (
    true
  );

-- 1. テーブルを作成
create table profiles (
  id uuid references auth.users,
  avatar_url text
);

-- 2. RLSを有効化
alter table profiles
  enable row level security;

-- 3. ポリシーを作成
create policy "Users can update their own profiles."
  on profiles for update using (
    auth.uid() = id
  );

create table teams (
  id serial primary key,
  name text
);

-- 2. 多対多のジョインを作成
create table members (
  team_id bigint references teams,
  user_id uuid references auth.users
);

-- 3. RLSを有効化
alter table teams
  enable row level security;

-- 4. ポリシーを作成
create policy "Team members can update team details if they belong to the team."
  on teams
  for update using (
    auth.uid() in (
      select user_id from members
      where team_id = id
    )
  );

-- 1. 上記の「ジョインを含むポリシー」の例を参照

-- 2.  RLSを有効化
alter table members
  enable row level security

-- 3.  SECURITY DEFINER関数を作成
create or replace function get_teams_for_authenticated_user()
returns setof bigint
language sql
security definer
set search_path = public
stable
as $$
    select team_id
    from members
    where user_id = auth.uid()
$$;

-- 4. ポリシーを作成
create policy "Team members can update team members if they belong to the team."
  on members
  for all using (
    team_id in (
      select get_teams_for_authenticated_user()
    )
  );

-- 1. テーブルを作成
create table leaderboard (
  id uuid references auth.users,
  high_score bigint
);

-- 2. RLSを有効化
alter table leaderboard
  enable row level security;

-- 3. ポリシーを作成
create policy "Only Blizzard staff can update leaderboard"
  on leaderboard
  for update using (
    right(auth.email(), 13) = '@blizzard.com'
  );

-- 1. テーブルを作成
create table if not exists stories (
  id uuid not null primary key DEFAULT uuid_generate_v4(),
  created_at timestamp with time zone default timezone('utc' :: text, now()) not null,
  content text not null
);

-- 2. RLSを有効化
alter table stories
  enable row level security;

-- 3. ポリシーを作成
create policy "Stories are live for a day"
  on stories
  for select using (
    created_at > (current_timestamp - interval '1 day')
  );
``

create table posts (
  id            serial    primary key,
  creator_id    uuid      not null     references auth.users(id),
  title         text      not null,
  body          text      not null,
  publish_date  date      not null     default now(),
  audience      uuid[]    null -- many to many table omitted for brevity
);

create table comments (
  id            serial    primary key,
  post_id       int       not null     references posts(id)  on delete cascade,
  user_id       uuid      not null     references auth.users(id),
  body          text      not null,
  comment_date  date      not null     default now()
);

create policy "Creator can see their own posts"
on posts
for select
using (
  auth.uid() = posts.creator_id
);

create policy "Logged in users can see the posts if they belong to the post 'audience'."
on posts
for select
using (
  auth.uid() = any (posts.audience)
);

create policy "Users can see all comments for posts they have access to."
on comments
for select
using (
  exists (
    select 1 from posts
    where posts.id = comments.post_id
  )
);

/**
 * REALTIME SUBSCRIPTIONS
 * 公開されたテーブルでのみ、リアルタイムでのリスニングを許可する。
 */

begin;
  -- リアルタイムのパブリケーションを削除
  drop publication if exists supabase_realtime;

  -- パブリケーションを再作成しますが、どのテーブルに対しても有効にしません
  create publication supabase_realtime;
commit;

-- パブリケーションにテーブルを追加
alter publication supabase_realtime add table products;

-- パブリケーションに他のテーブルを追加
alter publication supabase_realtime add table posts;