SYSTEM CONFIGURATION

Paste this into Supabase SQL Editor and run it once.

-- Ledger schema for bring-your-own Supabase setup.
-- Run in Supabase SQL Editor.

create extension if not exists pgcrypto;
create extension if not exists "uuid-ossp";

create table if not exists public.workspaces (
  id uuid primary key default uuid_generate_v4(),
  name text not null,
  slug text not null unique,
  owner_id uuid not null references auth.users(id) on delete cascade
);

create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  username text unique,
  avatar_url text,
  bio text,
  updated_at timestamptz default now()
);

create table if not exists public.posts (
  id bigint generated by default as identity primary key,
  author_id uuid not null references auth.users(id) on delete cascade,
  workspace_id uuid references public.workspaces(id) on delete set null,
  title text not null,
  content text not null,
  slug text not null,
  visibility text default 'public' check (visibility in ('public', 'private', 'unlisted')),
  inserted_at timestamptz not null default timezone('utc', now()),
  is_published boolean default false
);

create table if not exists public.comments (
  id bigint generated by default as identity primary key,
  post_id bigint not null references public.posts(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  content text not null,
  inserted_at timestamptz not null default timezone('utc', now())
);

create table if not exists public.writing_telemetry (
  id bigint generated by default as identity primary key,
  post_id bigint references public.posts(id) on delete set null,
  user_id uuid references auth.users(id) on delete set null,
  wpm integer,
  session_duration interval,
  sentiment_score double precision,
  inserted_at timestamptz default now()
);

create unique index if not exists posts_author_slug_unique on public.posts(author_id, slug);
create index if not exists posts_author_inserted_idx on public.posts(author_id, inserted_at desc);
create index if not exists posts_public_idx on public.posts(visibility, is_published, inserted_at desc);
create index if not exists comments_post_idx on public.comments(post_id, inserted_at asc);
create index if not exists telemetry_user_idx on public.writing_telemetry(user_id, inserted_at desc);

alter table public.profiles enable row level security;
alter table public.workspaces enable row level security;
alter table public.posts enable row level security;
alter table public.comments enable row level security;
alter table public.writing_telemetry enable row level security;

drop policy if exists "Profiles are readable by everyone" on public.profiles;
create policy "Profiles are readable by everyone"
on public.profiles
for select
to public
using (true);

drop policy if exists "Users can insert their own profile" on public.profiles;
create policy "Users can insert their own profile"
on public.profiles
for insert
to public
with check (auth.uid() = id);

drop policy if exists "Users can update their own profile" on public.profiles;
create policy "Users can update their own profile"
on public.profiles
for update
to public
using (auth.uid() = id)
with check (auth.uid() = id);

drop policy if exists "Workspaces are readable by owner" on public.workspaces;
create policy "Workspaces are readable by owner"
on public.workspaces
for select
to public
using (auth.uid() = owner_id);

drop policy if exists "Users can create workspaces they own" on public.workspaces;
create policy "Users can create workspaces they own"
on public.workspaces
for insert
to public
with check (auth.uid() = owner_id);

drop policy if exists "Owners can update their workspaces" on public.workspaces;
create policy "Owners can update their workspaces"
on public.workspaces
for update
to public
using (auth.uid() = owner_id)
with check (auth.uid() = owner_id);

drop policy if exists "Owners can delete their workspaces" on public.workspaces;
create policy "Owners can delete their workspaces"
on public.workspaces
for delete
to public
using (auth.uid() = owner_id);

drop policy if exists "Posts are readable if open+published or owner" on public.posts;
create policy "Posts are readable if open+published or owner"
on public.posts
for select
to public
using (
  ((visibility = 'public') and (is_published = true))
  or (auth.uid() = author_id)
);

drop policy if exists "Herkes kendi yazısını ekleyebilir." on public.posts;
create policy "Herkes kendi yazısını ekleyebilir."
on public.posts
for insert
to public
with check (auth.uid() = author_id);

drop policy if exists "Owners can update their posts" on public.posts;
create policy "Owners can update their posts"
on public.posts
for update
to public
using (auth.uid() = author_id)
with check (auth.uid() = author_id);

drop policy if exists "Owners can delete their posts" on public.posts;
create policy "Owners can delete their posts"
on public.posts
for delete
to public
using (auth.uid() = author_id);

drop policy if exists "Comments readable if parent post readable" on public.comments;
create policy "Comments readable if parent post readable"
on public.comments
for select
to public
using (
  exists (
    select 1
    from public.posts p
    where p.id = comments.post_id
      and (((p.visibility = 'public') and (p.is_published = true)) or (p.author_id = auth.uid()))
  )
);

drop policy if exists "Authenticated can insert comment on readable posts" on public.comments;
create policy "Authenticated can insert comment on readable posts"
on public.comments
for insert
to public
with check (
  (auth.role() = 'authenticated')
  and (auth.uid() = user_id)
  and exists (
    select 1
    from public.posts p
    where p.id = comments.post_id
      and (((p.visibility = 'public') and (p.is_published = true)) or (p.author_id = auth.uid()))
  )
);

drop policy if exists "Telemetry readable by owner" on public.writing_telemetry;
create policy "Telemetry readable by owner"
on public.writing_telemetry
for select
to public
using (auth.uid() = user_id);

drop policy if exists "Telemetry insert by owner" on public.writing_telemetry;
create policy "Telemetry insert by owner"
on public.writing_telemetry
for insert
to public
with check (auth.uid() = user_id);

-- Auto profile creation on signup/social login.
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
  base_username text;
  candidate text;
  n integer := 0;
begin
  base_username := lower(
    regexp_replace(
      coalesce(new.raw_user_meta_data->>'full_name', split_part(new.email, '@', 1), 'user'),
      '[^a-z0-9_]+',
      '-',
      'g'
    )
  );
  base_username := trim(both '-' from base_username);
  if base_username = '' then
    base_username := 'user';
  end if;

  candidate := base_username;
  while exists (select 1 from public.profiles p where p.username = candidate) loop
    n := n + 1;
    candidate := base_username || '-' || n::text;
  end loop;

  insert into public.profiles (id, username, avatar_url)
  values (
    new.id,
    candidate,
    new.raw_user_meta_data->>'avatar_url'
  )
  on conflict (id) do update
  set
    username = coalesce(public.profiles.username, excluded.username),
    avatar_url = coalesce(excluded.avatar_url, public.profiles.avatar_url),
    updated_at = now();

  return new;
end;
$$;

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

-- Optional backfill for existing users created before this trigger.
insert into public.profiles (id, username, avatar_url)
select
  u.id,
  null,
  u.raw_user_meta_data->>'avatar_url'
from auth.users u
left join public.profiles p on p.id = u.id
where p.id is null
on conflict do nothing;