-- Migration 012: atomic create_collective(name, emoji) RPC.
--
-- Background: creating a collective via two separate REST calls —
--   1. INSERT INTO collectives ...
--   2. INSERT INTO collective_members (role='admin') ...
-- — hits two RLS dead-ends:
--
--   a) The first INSERT, when chained with `.select().single()` (the idiomatic
--      supabase-js pattern), triggers a SELECT policy check on the returned
--      row. The SELECT policy is `is_member(id)`, and the creator isn't a
--      member yet — step 2 hasn't happened — so the whole INSERT fails.
--   b) The second INSERT is gated by `is_admin(collective_id)`, and nobody
--      is admin of the newly-created collective yet, so this also fails.
--
-- The intended semantics are "the creator becomes the first admin atomically"
-- — which is exactly what a SECURITY DEFINER function is for.
--
-- This RPC replaces the two-call pattern in the onboarding flow. It runs as
-- the function owner (supabase_admin via search_path = public default grants)
-- so it bypasses RLS for both inserts, and returns the new collective row.

CREATE OR REPLACE FUNCTION public.create_collective(
    p_name  text,
    p_emoji text
)
RETURNS public.collectives
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
    v_user_id    uuid := auth.uid();
    v_collective public.collectives%ROWTYPE;
BEGIN
    IF v_user_id IS NULL THEN
        RAISE EXCEPTION 'unauthenticated' USING ERRCODE = '28000';
    END IF;

    IF p_name IS NULL OR length(btrim(p_name)) = 0 THEN
        RAISE EXCEPTION 'name_required' USING ERRCODE = '22023';
    END IF;

    INSERT INTO public.collectives (name, emoji, created_by)
    VALUES (btrim(p_name), coalesce(p_emoji, '🏠'), v_user_id)
    RETURNING * INTO v_collective;

    INSERT INTO public.collective_members (collective_id, user_id, role)
    VALUES (v_collective.id, v_user_id, 'admin');

    RETURN v_collective;
END;
$$;

REVOKE ALL ON FUNCTION public.create_collective(text, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.create_collective(text, text) TO authenticated;