CREATE SCHEMA IF NOT EXISTS private;
REVOKE ALL ON FUNCTION public.is_workspace_member(uuid, uuid) FROM PUBLIC, anon, authenticated;
REVOKE ALL ON FUNCTION public.has_role(uuid, uuid, public.app_role[]) FROM PUBLIC, anon, authenticated;
REVOKE ALL ON FUNCTION public.complete_onboarding(text,text,text,text,text) FROM PUBLIC, anon, authenticated;

CREATE OR REPLACE FUNCTION private.is_workspace_member(_workspace_id uuid, _user_id uuid DEFAULT auth.uid())
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public, private AS $$
  SELECT EXISTS (SELECT 1 FROM public.user_roles WHERE workspace_id = _workspace_id AND user_id = _user_id)
$$;
CREATE OR REPLACE FUNCTION private.has_role(_user_id uuid, _workspace_id uuid, _roles public.app_role[])
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public, private AS $$
  SELECT EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = _user_id AND workspace_id = _workspace_id AND role = ANY(_roles))
$$;
REVOKE ALL ON FUNCTION private.is_workspace_member(uuid, uuid) FROM PUBLIC, anon;
REVOKE ALL ON FUNCTION private.has_role(uuid, uuid, public.app_role[]) FROM PUBLIC, anon;
GRANT USAGE ON SCHEMA private TO authenticated, service_role;
GRANT EXECUTE ON FUNCTION private.is_workspace_member(uuid, uuid) TO authenticated, service_role;
GRANT EXECUTE ON FUNCTION private.has_role(uuid, uuid, public.app_role[]) TO authenticated, service_role;

DROP POLICY "workspaces_member_read" ON public.workspaces;
DROP POLICY "workspaces_admin_update" ON public.workspaces;
DROP POLICY "customers_workspace_access" ON public.customers;
DROP POLICY "invoices_workspace_access" ON public.invoices;
DROP POLICY "payments_workspace_access" ON public.payments;

CREATE POLICY "workspaces_member_read" ON public.workspaces FOR SELECT TO authenticated USING (private.is_workspace_member(id));
CREATE POLICY "workspaces_admin_update" ON public.workspaces FOR UPDATE TO authenticated USING (private.has_role(auth.uid(), id, ARRAY['owner','admin']::public.app_role[]));
CREATE POLICY "customers_workspace_access" ON public.customers FOR ALL TO authenticated USING (private.is_workspace_member(workspace_id)) WITH CHECK (private.is_workspace_member(workspace_id));
CREATE POLICY "invoices_workspace_access" ON public.invoices FOR ALL TO authenticated USING (private.is_workspace_member(workspace_id)) WITH CHECK (private.is_workspace_member(workspace_id));
CREATE POLICY "payments_workspace_access" ON public.payments FOR ALL TO authenticated USING (private.is_workspace_member(workspace_id)) WITH CHECK (private.is_workspace_member(workspace_id));

DROP FUNCTION public.complete_onboarding(text,text,text,text,text);
DROP FUNCTION public.is_workspace_member(uuid, uuid);
DROP FUNCTION public.has_role(uuid, uuid, public.app_role[]);