-- ============================================================================
-- Invite-only blog comments — Supabase schema, security, and policies.
-- Run this once in your Supabase project: Dashboard → SQL Editor → paste → Run.
-- Safe to re-run (idempotent).
-- ============================================================================

-- ---------------------------------------------------------------------------
-- Tables
-- ---------------------------------------------------------------------------

-- The allowlist. You edit this by hand in the dashboard (Table Editor).
create table if not exists public.approved_emails (
  email      text primary key,
  note       text,                 -- optional: who they are / how you know them
  added_at   timestamptz not null default now()
);

-- Applications submitted from the /apply/ page. You review these, then copy
-- approved addresses into approved_emails above.
create table if not exists public.applications (
  id         uuid primary key default gen_random_uuid(),
  name       text,
  email      text,
  reason     text,
  created_at timestamptz not null default now()
);

-- Comments. page_key = the post's URL path (e.g. /en/wukong/), so each page
-- (and each language version) has its own thread.
create table if not exists public.comments (
  id           uuid primary key default gen_random_uuid(),
  page_key     text not null,
  author_email text not null,
  author_name  text,
  body         text not null check (char_length(body) between 1 and 5000),
  created_at   timestamptz not null default now()
);
create index if not exists comments_page_key_idx on public.comments (page_key, created_at);

-- ---------------------------------------------------------------------------
-- Approval check as a SECURITY DEFINER function so clients never need to read
-- (or be able to read) the allowlist directly.
-- ---------------------------------------------------------------------------
create or replace function public.is_email_approved(e text)
returns boolean
language sql
security definer
set search_path = public
as $$ select exists (select 1 from approved_emails where email = e) $$;

-- ---------------------------------------------------------------------------
-- Row-Level Security
-- ---------------------------------------------------------------------------
alter table public.comments        enable row level security;
alter table public.applications    enable row level security;
alter table public.approved_emails enable row level security;

-- comments: anyone may READ; only a signed-in, approved user may INSERT, and
-- only as themselves. No public UPDATE/DELETE (you delete via the dashboard).
drop policy if exists comments_select on public.comments;
create policy comments_select on public.comments
  for select using (true);

drop policy if exists comments_insert on public.comments;
create policy comments_insert on public.comments
  for insert to authenticated
  with check (
    author_email = auth.email()
    and public.is_email_approved(auth.email())
  );

-- applications: anyone may INSERT (apply); nobody may read through the API.
drop policy if exists applications_insert on public.applications;
create policy applications_insert on public.applications
  for insert to anon, authenticated
  with check (true);

-- approved_emails: no policies at all => no API access. Dashboard/service-role only.

-- ---------------------------------------------------------------------------
-- Grants (RLS still restricts rows; these just expose the tables to the API roles)
-- ---------------------------------------------------------------------------
grant usage on schema public to anon, authenticated;
grant select on public.comments to anon, authenticated;
grant insert on public.comments to authenticated;
revoke delete on public.comments from authenticated;   -- soft-delete only, via RPC below
grant insert on public.applications to anon, authenticated;
grant execute on function public.is_email_approved(text) to anon, authenticated;

-- ---------------------------------------------------------------------------
-- Threaded replies + author self-delete (safe to re-run on an existing DB)
-- ---------------------------------------------------------------------------
-- parent_id links a reply to the comment it answers. Deleting a comment
-- cascades to its replies (the whole sub-thread goes with it).
alter table public.comments
  add column if not exists parent_id uuid references public.comments(id) on delete cascade;
create index if not exists comments_parent_id_idx on public.comments (parent_id);

-- ---------------------------------------------------------------------------
-- Soft delete (safe to re-run). "Deleting" keeps the row so nested replies stay
-- intact; the body is blanked and the UI shows a tombstone. A user may delete
-- their OWN comment; an owner/moderator (approved_emails.is_owner = true) may
-- delete ANY comment. Done through a SECURITY DEFINER RPC so clients can't run
-- arbitrary UPDATEs (which would let them edit/spoof).
-- ---------------------------------------------------------------------------
drop policy if exists comments_delete on public.comments;   -- remove old hard-delete path
alter table public.comments        add column if not exists deleted    boolean not null default false;
alter table public.comments        add column if not exists deleted_by text;   -- 'user' (self) or 'author' (moderator)
alter table public.approved_emails add column if not exists is_owner   boolean not null default false;

-- A soft-deleted comment keeps an empty body (the UI shows a tombstone), so the
-- 1–5000 char length check must not apply to deleted rows.
alter table public.comments drop constraint if exists comments_body_check;
alter table public.comments add constraint comments_body_check
  check (deleted or char_length(body) between 1 and 5000);

drop function if exists public.soft_delete_comment(uuid);
create function public.soft_delete_comment(cid uuid)
returns integer
language plpgsql
security definer
set search_path = public
as $$
declare affected integer;
begin
  update public.comments
     set deleted = true, body = '', author_name = null,
         -- 'user' when the commenter removes their own; 'author' when a moderator/owner does it
         deleted_by = case when author_email = auth.email() then 'user' else 'author' end
   where id = cid and deleted = false
     and (
       author_email = auth.email()
       or exists (select 1 from approved_emails where email = auth.email() and is_owner)
     );
  get diagnostics affected = row_count;
  return affected;   -- 1 = deleted; 0 = not allowed (or already deleted)
end $$;

grant execute on function public.soft_delete_comment(uuid) to authenticated;

-- Lets the client learn whether the signed-in user is a moderator/owner, so the UI
-- can show "delete any comment" controls to real moderators only (matches the RPC).
create or replace function public.is_moderator()
returns boolean
language sql
security definer
set search_path = public
as $$ select exists (select 1 from approved_emails where email = auth.email() and is_owner) $$;

grant execute on function public.is_moderator() to authenticated;

-- ---------------------------------------------------------------------------
-- Display names (safe to re-run on an existing DB)
-- The name shown on a comment is chosen once at application time and set by YOU
-- when approving — never chosen per-comment by the user. A trigger stamps it
-- server-side so it can't be spoofed.
-- ---------------------------------------------------------------------------
alter table public.applications    add column if not exists display_name text;
alter table public.approved_emails add column if not exists display_name text;

create or replace function public.set_comment_author_name()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  new.author_name := (select display_name from approved_emails where email = auth.email());
  return new;
end $$;

drop trigger if exists comments_set_author_name on public.comments;
create trigger comments_set_author_name
  before insert on public.comments
  for each row execute function public.set_comment_author_name();

-- ============================================================================
-- After running this:
--   1. Authentication → Providers → Email: make sure it's enabled.
--   2. Authentication → URL Configuration → Site URL + Redirect URLs:
--        http://localhost:4000/**
--        https://sc103121.github.io/**
--   3. Project Settings → API: copy the Project URL and the anon public key
--      into _config.yml (supabase: url / anon_key).
--   4. To approve someone: Table Editor → approved_emails → insert a row with their
--      email and the display_name from their application (that name shows on their
--      comments). Set is_owner = true on YOUR OWN row so you can moderate/delete any
--      comment.
-- ============================================================================
