caterium-app/ops/sql/SUPABASE-SETUP.sql

-- Sun Catering Cloud v2
-- Run this entire file once in Supabase -> SQL Editor.
-- Safe to re-run: objects are created with IF NOT EXISTS where possible.

create extension if not exists pgcrypto;

create table if not exists public.sun_workspaces (
  id uuid primary key default gen_random_uuid(),
  name text not null check (char_length(trim(name)) between 1 and 120),
  created_by uuid not null references auth.users(id) on delete cascade,
  created_at timestamptz not null default now()
);

create table if not exists public.sun_workspace_members (
  workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  role text not null default 'manager' check (role in ('owner','manager','viewer')),
  created_at timestamptz not null default now(),
  primary key (workspace_id, user_id)
);

create table if not exists public.sun_app_state (
  workspace_id uuid primary key references public.sun_workspaces(id) on delete cascade,
  payload jsonb not null default '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb,
  revision bigint not null default 0,
  client_id text,
  updated_by uuid references auth.users(id) on delete set null,
  updated_at timestamptz not null default now()
);

create table if not exists public.sun_workspace_invites (
  token uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
  role text not null default 'manager' check (role in ('manager','viewer')),
  created_by uuid not null references auth.users(id) on delete cascade,
  created_at timestamptz not null default now(),
  expires_at timestamptz not null default (now() + interval '7 days'),
  used_by uuid references auth.users(id) on delete set null,
  used_at timestamptz
);

create index if not exists sun_workspace_members_user_idx on public.sun_workspace_members(user_id);
create index if not exists sun_workspace_invites_workspace_idx on public.sun_workspace_invites(workspace_id);

create or replace function public.sun_member_role(p_workspace uuid)
returns text
language sql
stable
security definer
set search_path = public
as $$
  select m.role
  from public.sun_workspace_members m
  where m.workspace_id = p_workspace
    and m.user_id = auth.uid()
  limit 1;
$$;

revoke all on function public.sun_member_role(uuid) from public;
grant execute on function public.sun_member_role(uuid) to authenticated;

create or replace function public.sun_touch_app_state()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  new.updated_at := now();
  if tg_op = 'UPDATE' then
    new.revision := old.revision + 1;
  end if;
  new.updated_by := auth.uid();
  return new;
end;
$$;

drop trigger if exists sun_app_state_touch on public.sun_app_state;
create trigger sun_app_state_touch
before insert or update on public.sun_app_state
for each row execute function public.sun_touch_app_state();

revoke all on function public.sun_touch_app_state() from public;
revoke all on function public.sun_touch_app_state() from anon;
revoke all on function public.sun_touch_app_state() from authenticated;

create or replace function public.sun_create_workspace(p_name text default 'Солнце Кейтеринг')
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_user uuid := auth.uid();
  v_workspace uuid;
begin
  if v_user is null then
    raise exception 'Authentication required';
  end if;

  insert into public.sun_workspaces(name, created_by)
  values (coalesce(nullif(trim(p_name),''),'Солнце Кейтеринг'), v_user)
  returning id into v_workspace;

  insert into public.sun_workspace_members(workspace_id, user_id, role)
  values (v_workspace, v_user, 'owner');

  insert into public.sun_app_state(workspace_id, payload, client_id)
  values (v_workspace, '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb, 'bootstrap')
  on conflict (workspace_id) do nothing;

  return v_workspace;
end;
$$;

revoke all on function public.sun_create_workspace(text) from public;
grant execute on function public.sun_create_workspace(text) to authenticated;

create or replace function public.sun_create_invite(p_workspace uuid, p_role text default 'manager')
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_token uuid;
  v_role text := lower(coalesce(p_role,'manager'));
begin
  if public.sun_member_role(p_workspace) <> 'owner' then
    raise exception 'Owner role required';
  end if;
  if v_role not in ('manager','viewer') then
    raise exception 'Invalid role';
  end if;

  insert into public.sun_workspace_invites(workspace_id, role, created_by)
  values (p_workspace, v_role, auth.uid())
  returning token into v_token;

  return v_token;
end;
$$;

revoke all on function public.sun_create_invite(uuid,text) from public;
grant execute on function public.sun_create_invite(uuid,text) to authenticated;

create or replace function public.sun_accept_invite(p_token uuid)
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_user uuid := auth.uid();
  v_invite public.sun_workspace_invites%rowtype;
begin
  if v_user is null then
    raise exception 'Authentication required';
  end if;

  select * into v_invite
  from public.sun_workspace_invites
  where token = p_token
  for update;

  if not found then raise exception 'Invite not found'; end if;
  if v_invite.used_at is not null then raise exception 'Invite already used'; end if;
  if v_invite.expires_at < now() then raise exception 'Invite expired'; end if;

  insert into public.sun_workspace_members(workspace_id, user_id, role)
  values (v_invite.workspace_id, v_user, v_invite.role)
  on conflict (workspace_id, user_id)
  do update set role = excluded.role;

  update public.sun_workspace_invites
  set used_by = v_user, used_at = now()
  where token = p_token;

  return v_invite.workspace_id;
end;
$$;

revoke all on function public.sun_accept_invite(uuid) from public;
grant execute on function public.sun_accept_invite(uuid) to authenticated;

alter table public.sun_workspaces enable row level security;
alter table public.sun_workspace_members enable row level security;
alter table public.sun_app_state enable row level security;
alter table public.sun_workspace_invites enable row level security;

-- Remove overly broad browser grants, then grant only what authenticated clients need.
revoke all on public.sun_workspaces from anon;
revoke all on public.sun_workspace_members from anon;
revoke all on public.sun_app_state from anon;
revoke all on public.sun_workspace_invites from anon;

grant select on public.sun_workspaces to authenticated;
grant select on public.sun_workspace_members to authenticated;
grant select, insert, update on public.sun_app_state to authenticated;
grant select on public.sun_workspace_invites to authenticated;

-- Recreate policies idempotently.
drop policy if exists sun_workspaces_read on public.sun_workspaces;
create policy sun_workspaces_read on public.sun_workspaces
for select to authenticated
using (public.sun_member_role(id) is not null);

drop policy if exists sun_members_read on public.sun_workspace_members;
create policy sun_members_read on public.sun_workspace_members
for select to authenticated
using (user_id = auth.uid() or public.sun_member_role(workspace_id) = 'owner');

drop policy if exists sun_state_read on public.sun_app_state;
create policy sun_state_read on public.sun_app_state
for select to authenticated
using (public.sun_member_role(workspace_id) is not null);

drop policy if exists sun_state_insert on public.sun_app_state;
create policy sun_state_insert on public.sun_app_state
for insert to authenticated
with check (public.sun_member_role(workspace_id) in ('owner','manager'));

drop policy if exists sun_state_update on public.sun_app_state;
create policy sun_state_update on public.sun_app_state
for update to authenticated
using (public.sun_member_role(workspace_id) in ('owner','manager'))
with check (public.sun_member_role(workspace_id) in ('owner','manager'));

drop policy if exists sun_invites_read on public.sun_workspace_invites;
create policy sun_invites_read on public.sun_workspace_invites
for select to authenticated
using (public.sun_member_role(workspace_id) = 'owner');

-- Private media bucket. The first folder is always the workspace UUID.
insert into storage.buckets(id, name, public, file_size_limit, allowed_mime_types)
values (
  'sun-media',
  'sun-media',
  false,
  15728640,
  array['image/jpeg','image/png','image/webp','image/gif']
)
on conflict (id) do update set
  public = excluded.public,
  file_size_limit = excluded.file_size_limit,
  allowed_mime_types = excluded.allowed_mime_types;

-- Storage policies.
drop policy if exists sun_media_read on storage.objects;
create policy sun_media_read on storage.objects
for select to authenticated
using (
  bucket_id = 'sun-media'
  and public.sun_member_role(((storage.foldername(name))[1])::uuid) is not null
);

drop policy if exists sun_media_insert on storage.objects;
create policy sun_media_insert on storage.objects
for insert to authenticated
with check (
  bucket_id = 'sun-media'
  and public.sun_member_role(((storage.foldername(name))[1])::uuid) in ('owner','manager')
);

drop policy if exists sun_media_update on storage.objects;
create policy sun_media_update on storage.objects
for update to authenticated
using (
  bucket_id = 'sun-media'
  and public.sun_member_role(((storage.foldername(name))[1])::uuid) in ('owner','manager')
)
with check (
  bucket_id = 'sun-media'
  and public.sun_member_role(((storage.foldername(name))[1])::uuid) in ('owner','manager')
);

drop policy if exists sun_media_delete on storage.objects;
create policy sun_media_delete on storage.objects
for delete to authenticated
using (
  bucket_id = 'sun-media'
  and public.sun_member_role(((storage.foldername(name))[1])::uuid) in ('owner','manager')
);

-- Realtime publication for the single workspace-state row.
do $$
begin
  if not exists (
    select 1 from pg_publication_tables
    where pubname = 'supabase_realtime'
      and schemaname = 'public'
      and tablename = 'sun_app_state'
  ) then
    alter publication supabase_realtime add table public.sun_app_state;
  end if;
end $$;